[DBMS] Transaction, Select

DCL : Data Control Language
- commit : 작업결과를 물리적 디스크에 저장.  manipulation 작업이 정상적으로 완료
- rollback : 원래의 데이터 상태로 복구

Transaction

- 기본적으로 MySQL 은 auto-commit 이다
- DML 은 실행 즉시 물리적인 데이터에 반영된다.

- commit 을 사용하려면 auto commit 부터 비활성화 해야 한다.
- rollback 하기 전까진, 마지막으로 commit 한 지점으로의 복구 가능하다.

 

-- 테이블과 데이터 준비
DESC phonebook;
SELECT * FROM phonebook ;

DROP TABLE IF EXISTS phonebook; 

CREATE TABLE phonebook(
	id int PRIMARY KEY AUTO_INCREMENT,
	name varchar(80) NOT NULL,
	phonenum varchar(20) DEFAULT '010-0000-0000',
	email varchar(100),
	regdate datetime DEFAULT now()
);

INSERT INTO phonebook (name, phonenum, email)
	VALUES
		('아이언맨', '111-111-1111', 'ironman@mail.com')
		, ('캡틴아메리카', '222-2222-2222', 'captain@mail.com')
		, ('토르', '3333-3333-333', 'thor@mail.com')
		;


-- 기본적으로 MySQL 은 auto-commit 이다
-- DML 은 실행 즉시 물리적인 데이터에 반영된다.

-- MySQL 은 기본적으로 auto commit 이다.
-- commit 을 사용하려면 auto commit 부터 비활성화 해야 한다.

SELECT @@autocommit;  -- 현재 auto COMMIT 활성화 여부.  1:활성화, 0:비활성화

SET @@autocommit = 0;  -- auto COMMIT 비활성화


SELECT * FROM phonebook; 

DELETE FROM phonebook WHERE id > 20;

-- rollback 하기 전까진, 마지막으로 commit 한 지점으로의 복구 가능하다.
ROLLBACK;

INSERT INTO phonebook (name, phonenum, email)
VALUES('헐크', '4444-4444', 'hulk@incredible.com');

SELECT * FROM phonebook ;

SAVEPOINT A;  -- A  라는 이름의 SAVEPOINT 지정.  나중에 A 지점으로 ROLLBACK 가능

UPDATE phonebook SET name = '블랙위도우' WHERE name = '캡틴아메리카';

SAVEPOINT B;

SELECT * FROM phonebook; 

DELETE FROM phonebook WHERE regdate < '2022-02-21';

SELECT * FROM phonebook ;

ROLLBACK TO SAVEPOINT B;

ROLLBACK TO SAVEPOINT A;

SELECT * FROM phonebook ;

COMMIT;  -- 모든 동작 마무리후에는 COMMIT 하여 dB 에 저장.

SET @@autocommit = 1; 

SELECT - 데이터 조회/질의

SELECT [컬럼명 또는 표현식] FROM [테이블명, 뷰명] ;

1. 모든 컬럼 조회
예) SELECT * FROM t_emp;

2. 원하는 컬럼만 조회
예) SELECT empno, ename FROM t_emp;

3.  표현식 사용하여 출력
예) SELECT  name, '교수님~배고파요' FROM t_professor;

4. 컬럼 별칭(alias) 사용하여 출력
예) SELECT studno '학번',  name '이름' FROM t_student;
SELECT studno AS 학번, name AS 이름 FROM t_student;
SELECT studno "학번", name "이름" FROM t_student;
SELECT studno AS "학생 학번", name AS "학생 이름" FROM t_student;

 

-- t_emp 테이블에서  empno 를 '사원번호',  
-- ename을 '사원명',  
-- job을 '직업'으로 별명을 설정하여 출력
SELECT 	empno 사원번호, ename 사원명, job 직업 
FROM t_emp;

-- t_dept 테이블을 사용하여 deptno를 '부서#', dname을 '부서명', loc를 '위치' 로 별명을 설정하여 출력
SELECT deptno "부서#", dname "부서명", loc "위치"
FROM t_dept;

DISTINCT - 중복값 제거하고 출력
SELECT DISTINCT [컬럼명 또는 표현식] FROM [테이블명, 뷰명] ;

CONCAT() 함수 :  열 연결 

-- DISTINCT
SELECT deptno FROM t_emp;
SELECT DISTINCT deptno FROM t_emp;

-- 문자열 연결 concat()
SELECT concat('hello', 'mysql');

SELECT concat(name, ' ', position) AS 교수님목록 FROM t_professor;

산술연산자
+, - , *, /

# 산술연산자

-- t_emp 테이블
-- 직원이름(ename), 급여(sal), 급여10%인상분

SELECT ename, sal, sal * 1.1 "10% 인상분"
FROM t_emp;

SELECT ename, empno, sal FROM t_emp WHERE ename = 'Scott';  -- MySQL 은 = 연산자로 문자열 비교시 대소문자 불문

WHERE : 원하는 조건만 검색
SELECT [컬럼명 또는 표현식] FROM [테이블명, 뷰명]  WHERE [조건절] ;
t_emp 테이블에서 10번 부서에 근무하는 사원의 이름과 급여와 부서번호 출력
SELECT ename, sal, deptno FROM emp WHERE deptno = 10;

t_emp 테이블에서 급여(sal) 가 4000보다 큰 사람의 이름과 급여를 출력하세요
SELECT ename, sal FROM emp WHERE sal > 4000;

t_emp 테이블에서 이름이 SCOTT인 사람의 이름과 사원번호, 급여 출력
SELECT ename, empno, sal FROM emp WHERE ename = ‘SCOTT’;

-- WHERE 조건절
SELECT * FROM t_emp;
SELECT ename, sal, deptno FROM t_emp WHERE deptno = 10;

SELECT ename, sal FROM t_emp WHERE sal > 4000;

SELECT ename, empno, sal FROM t_emp WHERE ename = 'SCOTT';
SELECT ename, empno, sal FROM t_emp WHERE ename = 'Scott';  -- MySQL 은 = 

다양한 연산자 활용

Like 와 같이 쓰는 와일드 카드  %, _

% : 글자수 제한 없고 어떤 글자가 와도 됨

_ : 글자수는 한글자만 와도 되고 어떤 글자 와도 좋음

ex)  Like ‘%SAW_%’     <-  중간에 SAW 있어야 하고 그 뒤에 한문자는 와야 된다

-- LIKE 와 같이 쓰는 와일드 카드  % ,  _
-- % : 글자수 제한 없고 어떤 글자가 와도 됨
-- _ : 글자수는 한글자가 반드시 와야 되고 어떤 글자 와도 좋음

-- 교수님 중에서 김씨 성을 가진 교수님의 이름만 출력 (LIKE 사용)
SELECT name FROM t_professor
WHERE name LIKE '김%';

-- 연습
-- 직원 테이블(t_emp)에서 직원이름 (ename) 중에
-- 두번째 글자가 'A' 인 사람의 이름(ename)만 출력
SELECT ename FROM t_emp
WHERE ename LIKE '_A%';

ORDER BY : 정렬하여 출력하기

 

SELECT [컬럼명 또는 표현식] 
FROM [테이블명, 뷰명]  
WHERE [조건절]  
ORDER BY [정렬열] [ASC/DESC];

student 테이블을 사용하여 1학년생의 이름과 키와 몸무게를 출력하세요, 단 키는 작은 사람부터 출력하고, 몸무게는 많은 사람부터 출력하세요

 

-- ORDER BY
SELECT name, grade, height, weight FROM t_student
ORDER BY grade ASC, height DESC
;

-- 직원(t_emp)중 이름에 L 이 들어간 사람의 이름을 사전오름차순으로 출력하기
SELECT ename FROM t_emp
WHERE ename LIKE '%L%'
ORDER BY ename ASC
;

-- t_student 테이블에서 키(height)가 180 보다 크거나 같은 사람 이름, 키 출력
SELECT name, height 
FROM t_student 
WHERE height >= 180;

-- t_student 테이블에서 몸무게(weight)가 
-- 60~80 인 사람의 이름과 체중 출력
SELECT name, weight 
FROM t_student 
WHERE weight BETWEEN 60 AND 80;

-- t_student 테이블에서는 제1전공이 101번 학과(deptno1) 학생과 201번 학과 학생들을 모두 출력하세요 (in 연산자 활용)
SELECT name, deptno1 
FROM t_student 
WHERE 
	deptno1 IN (101, 201);
	-- deptno1 = 101 OR deptno1 = 201 (O)
	-- deptno1 = 101 OR 201 (X)

-- t_student 테이블에서 성이 ‘김’ 씨인 사람 조회
-- LIKE 연산자 사용:  
SELECT name 
FROM t_student 
WHERE name LIKE '김%';

-- t_student 테이블에서 4학년 학생중에 키 가 170cm 이상인 사람의 
-- '이름'과 '학년'과 '키'를 조회하세요
SELECT name, grade, height 
FROM t_student 
WHERE grade = 4 AND height >= 170;

-- t_student 테이블에서 1학년이거나 또는 몸무게가 80kg 이상인 학생들의 
-- '이름'과 '키'와 '학년'과 '몸무게'를 출력하세요
SELECT name, height, grade, weight 
FROM t_student 
WHERE grade = 1 OR weight >= 80;

-- t_student 테이블을 사용해서 2학년 중에서 키가 180cm 보다 크면서 
-- 몸무게가 70kg 보다 큰 학생들의 
-- 이름과 학년과 키와 몸무게를 출력하세요
SELECT name, grade, height, weight 
FROM t_student 
WHERE grade = 2 AND height > 180 AND weight > 70;

-- t_student 테이블: 2학년 학생중에서 키가 180cm보다 크거나 
-- 또는 몸무게가 70kg 보다 큰 학생들의 
-- 이름과 학년과 키와 몸무게를 출력하세요
SELECT name, grade, height, weight 
FROM t_student 
WHERE grade = 2 AND (height > 180 OR weight > 70);

-- t_emp 테이블에서 고용일(hiredate)이 1992년 이전인 사람들의 
-- 이름(ename) 과 고용일을 출력하세요
SELECT ename, hiredate 
FROM t_emp 
WHERE hiredate < '1992-01-01';

-- t_student 테이블: 1학년 학생의 이름과 생일과 키와 몸무게를 출력하세요, 
-- 단 생일이 빠른 사람 순서대로 정렬하세요.
SELECT name, birthday, height, weight 
FROM t_student 
WHERE grade = 1 
ORDER BY birthday ASC;

-- student 테이블: 1학년 학생의 이름과 키를 출력하세요, 
-- 별명은 ‘이름’, ‘키’ 로 출력.  
-- 단 이름은 오름차순으로 정렬하세요
SELECT name 이름, height 키 
FROM t_student 
WHERE grade = 1 
ORDER BY name ASC;

SELECT name 이름, height 키, grade
FROM t_student
ORDER BY grade DESC, name ASC;  -- 정렬조건 복수개 가능

-- t_emp2 직원 테이블에서
-- 생일(birthday) 가 1980년대생인 사람들의 이름과 생일만 출력하세요
-- 즉 1980/01/01 ~ 1989/12/31

SELECT name, birthday
FROM t_emp2
WHERE '1980-01-01' <= birthday AND birthday < '1990-01-01';