[SQL] DB 스터디 - 07

집합 연산자, 데이터 조작 명령문(DML)

1. 집합 연산자

- UNION/UNION ALL

-> UNION : 두 테이블을 합칠 때 중복되는 부분은 생략하여 합쳐서 출력

-> UNION ALL : 위 SELECT문을 출력 후 아래 SELECT문을 출력

- INTERSECT

-> 두 테이블 간의 교집합을 출력

- MINUS

-> A MINUS B 라고 되어 있으면 A - B라는 차집합의 결과를 출력

- 앞 SELECT문의 결과에 합/차 집합을 만들 수 있음

- UNION ALL을 제외한 나머지 연산자는 검색이 될 때 중복이 되는 행을 제거하여 출력

- 중복되는 값을 제거하기 위해서 내부적으로 정렬을 시킴

-> 기본적으로 SORT가 이루어 지면 성능의 문제를 일으킬 수 있음

-> 잘 사용하지 않음

- 보통 JOIN과 Subquery를 이용해서 만들거나 EXISTS와 NOT EXISTS를 사용함

- UNION ALL은 SORT가 이루어 지지 않아서 사용 되기는 함

-> 서로 다르게 GROUP BY절을 사용한 SELECT문 2개를 합칠 때 사용 하기도 함

-> 하나의 Query결과에 2개의 결과를 합쳐서 출력할 때 사용

-> 두 Query의 컬럼의 수가 같아야 함

-> 결과가 나오는 컬럼의 데이터 타입도 같아야 함

-> 컬럼의 AS를 이용해 별칭을 사용하면 처음 실행된 SELECT문의 별칭을 따름

-> ORDER BY절은 중간 SELECT문에는 나올 수 없음

---

2. 데이터 조작

- DML : INSERT, UPDATE, DELETE, MERGE

-> 값을 추가, 수정, 삭제를 하는 적업들

-> 테이블에 새 행 추가

-> 테이블의 기존 행 수정

-> 테이블에서 기존 행 제거

- TCL : COMMIT, ROLLBACK, SACEPOINT

-> 논리적 작업 단위를 형성하는 DML문의 모음으로 구성

---

3. INSERT 문 구문

- 테이블에 새로운 행을 추가하는 작업

- INSERT INTO table[column...] VALUES (value...) ;

- 테이블의 모든 컬럼에 값을 입력 한다면 테이블 명 뒤에 컬럼 명을 지칭하지 않아도 됨

- 필요한 컬럼만 입력 하려면 table 뒤에 컬럼 명을 입력 하면 됨

-> 하지만 그에 따른 value수와 데이터 타입을 맞춰서 입력 해야 함

- VALUES 절대신 Subquery를 작성해사 행의 내용을 복사할 수 있음

-> 검색되는 컬럼의 수와 입력 대상의 컬럼 수가 같아야 함

-> 데이터 타입 또한 같아야 함

예)

->

-- 기본 INSERT문 작성 법

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (9509, 'KJH', 'MANAGER', 7893, TRUNC(sysdate), 9999.99, 9999.99, 30);

-- 컬렴 명 생략

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (9509, 'KJH', 'MANAGER', 7893, TRUNC(sysdate), 9999.99, 9999.99, 30);

-- 제약 조건에 위배되지 않도록 입력 해야 함

-- departments에 값을 입력

INSERT INTO departments

(department_id, department_name, manager_id, location_id)

VALUES (70, 'Public Relations', 100, 1700);

-- null 값을 가진 행 삽입

INSERT INTO departments 

(department_id, department_name)

VALUES (30, 'Purchasing'); -- 2컬럼 외의 값은 NULL로 채워짐

-- 명시적으로 VALUES에서 입력 하는 방법

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

---

4. UPDATE문

- 기존의 값을 수정할 때 사용

- UPDATE table SET column = value[,  ... ] [WHERE 조건]

- SET절에서 '='의 의미는 비교 연산자가 아닌 할당 연산자

-> 크다 작다의 의미가 아님

- SELECT문과 마찬 가지로 WHERE절이 없다면 모든 행의 값이 모두 수정 됨

-> WHERE절이 들어가면 조건에 맞는 행만 수정 시킴

- WHERE절에 따라 1개 또는 모든 행의 값을 수정할 수 있음

- UPDATE문의 수정을 잘못 하였을 경우 'ROLLBACK'이라는 명령어로 UPDATE전의 상태로 되돌림

- Subquery를 이용한 값으로 수정할 수 있음

예)

->

-- 위에서 입력한 empno의 9509라는 사원의 정보 수정

UPDATE emp

SET sal = 5000, comm = 1000, hiredate = TO_DATE('20190101', 'YYYYMMDD')

-- 하나 이상의 수정하는 컬럼 이름과 값을 입력

WHERE empno = 9509;

-- Subquery를 이용한 UPDATE

-- 113번 사원의 job_id와 salary를 205번 사원과 동일하게 만드는 것

UPDATE employees

SET 

job_id = (SELECT job_id FROM employees WHERE employee_id = 205),

salary = (SELECT salary FROM employees WHERE employee_id = 205)

WHERE employee_id = 113;

-- 2개 이상의 SELECT절을 가진 경우 아래와 동일하게 사용 가능

UPDATE employees

SET

(job_id, salary) 

= (SELECT job_id, salary FROM employees WHERE employee_id = 205)

WHERE employee_id = 113;

---

5. DELETE문

- 테이블에서 기존의 행을 제거할 수 있음

- DELETE [FROM] table [WHERE 조건]

- WHERE절을 이용하면 조건에 만족하는 행 만을 삭제

- 조건이 없으면 해당 태이블의 모든 정보를 삭제

- Subquery도 WHERE절에서 동일하게 사용 가능

- TRUNCATE TABLE은 DELETE명령어에 WHERE절이 없는 것과 비슷한 효과

-> 하지만 명령어 수행과 동시에 COMMIT이 이루어 짐

---

6. ROLLBACK, COMMIT 명령어

- ORACLE에서 사용되는 ROLLBACK

-> 여러 DML명령어를 사용해 데이터를 조작 하면 해당 명령어에 맞게 실행

-> 하지만 사용자의 실수로 잘못된 테이블이 존재할 수 있음

-> 이런 부분을 해결하기 위해 Undo Data를 만들어 둠

-> COMMIT라는 명령어를 사용 해 실제 디스크에 저장

- TRUNCATE TABLE명령어는 실행과 동시에 COMMIT이 이루어 짐

-> 때문에 Undo Data가 만들어 지지 않음

-> DELETE 명령문과 다르게 속도가 빠름

-> 아주 주의를 요하는 명령어

-> 현업에서 사용 할 때는 해당 작업을 시킨 사람에게 증빙 자료를 받아서 본인은 시켜서 했다는 것을 입증 해야 함...

- Undo Data를 확인하는 하는 법

-> Flash Back Memory에서 정보를 가져 옴

SELECT * 

FROM salgrade AS OF TIMESTAMP (SYSDATE - INTERVAL '10' MINUTE);

-- 10분 전에 있던 salgrade의 데이터를 보여달라는 명령어

---

7. 데이터베이스 트랜잭션: 시작과 종료

- 여러가지 상황에서 명령어들이 모두 올바르게 수행된다는 보장이 없음

- 한 쪽에서는 INSERT를 하고 해당 작업 이후에 UPDATE되야하는 경우 두 작업이 모두 수행 되야 함

- 이처럼 둘 이상의 명령어가 정상적으로 수행이 되야 하는 단위를 트랜잭션이라 함

- 이러한 트랜잭션이 정확하게 수행이 되면 COMMIT를, 수행 도중 에러가 발생하면 ROLLBACK시켜야 함

- ORACLE DB에서는 첫 번째 DML명령어가 수행이 되면 자동 적으로 트랜잭션을 실행 시킴

트랜잭션 OPEN -> INSERT ....... UPDATE ....... DELETE ...... -> 트랜잭션 CLOSE -> COMMIT or ROLLBACK;

-> 데이터들을 수정하는 모든 명령어(DML)는 하나의 단위의 트랜잭션 단위가 됨

-> 실행된 트랜잭션은 V$TRANSACTION을 조회하면 알 수 있음

-> COMMIT 혹은 ROLLBACK명령어를 실행하면 위의 테이블에서 아무런 데이터도 조회 되지 않음

-> DML명령어를 실행 중에 TRANCATE TABLE 명령어 혹은 DDL, DCL 명령어가 실행 전에 자동으로 COMMIT를 수행 함

- DML명령어와 DCL 명령어는 절대 함께 사용하면 안됨

-> AUTO COMMIT 때문에 DML명령어의 실수를 ROLLBACK할 수 없음

- 실행 중이던 DB의 CLIENT가 강제 종료 되면 DML중의 트랜잭션은 모두 ROLLBACK으로 종료 됨

-> 시스템이 비 정상적으로 종료되는 상황 포함

---

8. COMMIT과 ROLLBACK의 이점

- 데이터의 일관성 보장

-> 변경이 제대로 되면 COMMIT, 안되면 ROLLBACK

- 변경이 이루어지기 전에 데이터를 검토할 수 있음

-> COMMIT가 되지 않으면 실제 데이터는 저장되어있지 않음

-> 수정하는 값을 확인할 수 있음

-> 수정이 정확하게 이루어지지 않으면 ROLLBACK할 수 있음

- 논리적으로 관련된 작업의 그룹화

- 다른 유저는 현재 유저가 실행한 DML문의 결과를 볼 수 없음

-> 기본적으로 COMMIT된 데이터 만을 사용자에게 보여줌

-> 데이터의 일관성을 보장

- 수정 중이던 행은 LOCK상태여서 다른 사용자가 해당 행을 수정할 수 없음

-> 하나의 행에 DML 명령어를 사용할 수 있는 사용자는 한 사용자 뿐임

-> 값의 일관성을 보장 해 줌

-> 트랜잭션의 종료를 해 주어야 LOCK이 풀릴 수 있음

-> DML 명령어를 사용중인 행에서 다른 컬럼을 수정 할 때에도 LOCK에 의해 첫 번째 사용자만 수정할 수 있음

---

9. SAVEPOINT

- 첫 번째 유효한 DML명령문이 실행 되면 트랜잭션이 열림

-> 저장을 하고 싶다면 COMMIT

-> 저장을 취소하고 싶다면 ROLLBACK

- ROLLBACK로 DML명령문을 취소 할 때는 모든 DML명령어가 취소 됨

- 부분적으로 DML 명령어가 취소되도록 하고 싶다면 SAVEPOINT name ;로 ROLLBACK로 name의 시점 까지만 ROLLBACK 시킴

-> ROLLBACK TO name; 라고 ROLLBACK명령어를 사용

-> name 라는 포인트 전의 DML명령어는 아직 유효 함

예)

->

-- UPDATE중 INSERT 명령문만 취소하고 싶을 때

UPDATE ...

SAVEPOINT update_done;

INSERT ....

ROLLBACK TO update_done;

---

10. COMMIT/ROLLBACK 후의 데이터 상태

- COMMIT

-> 데이터 변경 상항이 데이터베이스에 저장 됨

-> 이전의 데이터 상태를 겹쳐 씀

-> 모든 사용자가 결과를 확인할 수 있음

-> Lock이 해제되어 해당 행을 다른 사용자가 조작할 수 있음

-> 모든 SAVEPOINT가 지워짐

- ROLLBACK

-> 데이터 변경 사항이 취소됨

-> 이전의 데이터 상태가 복원 됨

-> 영향 받는 행의 Lock이 해제 됨

- 저장 되어있던 Undo Data가 지워 짐

---

※ 추가

- SELECT명령문은 기본적으로 Lock가 걸리지 않음

- SELECT명령 문으로 검색되는 행을 Lock을 걸고 싶다면 SELECT 명령문 끝에 FOR UPDATE;를 붙혀서 Lock을 걸음

예)

->

-- emp테이블의 모든 행에 Lock을 걸어두는 SELECT 명령문

SELECT *

FROM emp

FOR UPDATE;