[DB] TIL 020 - 23.08.08

05_DML(Data Manipulation Language)
★ Select, Insert, Update, Delete 중요 ★

-- *** DML (Data Manipulation Language) : 데이터 조작 언어

-- 테이블에 값을 삽입하거나(INSERT), 수정하거나(UPDATE), 삭제(DELETE) 하는 구문

-- 주의 : 혼자서 COMMIT, ROLLBACK 하지 말 것!

-- 테스트용 테이블 생성
CREATE TABLE EMPLOYEE2 AS SELECT * FROM EMPLOYEE;
CREATE TABLE DEPARTMENT2 AS SELECT * FROM DEPARTMENT;

SELECT * FROM EMPLOYEE2;
SELECT * FROM DEPARTMENT2;

-- 테이블 삭제 구문
--DROP TABLE DEPARTMENT2;


-------------------------------------------------------------------------

-- 1. INSERT

-- 테이블에 새로운 행을 추가하는 구문

-- 1) INSERT INTO 테이블명 VALUES(데이터, 데이터, ...)
-- 테이블에 있는 모든 컬럼에 대한 값을 INSERT 할 때 사용
-- INSERT 하고자 하는 컬럼이 모든 컬럼인 경우 컬럼명 생략 가능.
-- 단, 컬럼의 순서를 지켜서 VALUES에 값을 기입해야 함.

INSERT INTO EMPLOYEE2
VALUES(900, '장채현', '901230-2345678', 'jank_ch@kh.or.kr', '01012341234', 'D1', 'J7', 'S3', 4300000, 0.2, 200,
SYSDATE, NULL, 'N');

SELECT * FROM EMPLOYEE2
WHERE EMP_ID = 900;

ROLLBACK;

DELETE FROM EMPLOYEE2
WHERE EMP_ID = 900;

COMMIT;

-----------------------------------------------------------------------------------------

-- 2) INSERT INTO 테이블명(컬럼명1, 컬럼명2, 컬럼명3 ...)
--     VALUES(데이터1, 데이터2, 데이터3,...)
-- 테이블에 내가 선택한 컬럼에 대한 값만 INSERT 할 때 사용
-- 선택 안된 컬럼은 값이 NULL 이 들어감 (DEFAULT 존재 시 DEFAULT 값으로 삽입됨)

INSERT INTO EMPLOYEE2(EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE, JOB_CODE, SAL_LEVEL, SALARY)
VALUES (900, '장채현', '901123-2345678', 'jank_ch@kh.or.kr', '01012341234', 'D1', 'J7', 'S3', 4300000);

SELECT * FROM EMPLOYEE2
WHERE EMP_ID = 900;

ROLLBACK;


------------------------------------------------------------------------------------------

-- (참고) INSERT시 VALUES 대신 서브쿼리 사용 가능

CREATE TABLE EMP_01(
	EMP_ID NUMBER,
	EMP_NAME VARCHAR2(30),
	DEPT_TITLE VARCHAR2(20)
);

SELECT * FROM EMP_01;

SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE2
LEFT JOIN DEPARTMENT2 ON (DEPT_CODE = DEPT_ID);


INSERT INTO EMP_01 
( SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE2
LEFT JOIN DEPARTMENT2 ON (DEPT_CODE = DEPT_ID) );

SELECT * FROM EMP_01;

-- 서브쿼리(SELECT) 결과를 EMP_01 테이블에 INSERT
--> SELECT 조회 결과의 데이터 타입, 컬럼 개수가 
-- INSERT 하려는 테이블의 컬럼과 일치해야함.

-- 2. UPDATE (내용을 바꾸거나 추가해서 최신화, 새롭게 만드는 것)

-- 테이블에 기록된 컬럼의 값을 수정하는 구문

-- [작성법]
-- UPDATE 테이블명 SET 컬럼명 = 바꿀값
-- [WHERE 컬럼명 비교연산자 비교값]

-- DEPARTMENT2 테이블에서 DEPT_ID가 'D9'인 부서 정보 조회
SELECT * FROM DEPARTMENT2
WHERE DEPT_ID = 'D9';

-- DEPARTMENT2 테이블에서 DEPT_ID가 'D9'인 행의 DEPT_TITLE을 전략기획팀 으로 수정

UPDATE DEPARTMENT2
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';

SELECT * FROM DEPARTMENT2;

COMMIT;

-- EMPLOYEE2 테이블에서 BONUS를 받지 않는 사원의
-- BONUS를 0.1로 변경
UPDATE EMPLOYEE2
SET BONUS = 0.1 
WHERE BONUS IS NULL;

SELECT EMP_NAME, BONUS FROM EMPLOYEE2;


-----------------------------------------------------

-- * 조건절을 설정하지 않고 UPDATE 구문 실행 시 모든 행의 컬럼값이 변경.

SELECT * FROM DEPARTMENT2;

UPDATE DEPARTMENT2
SET DEPT_TITLE = '기술연구팀';


ROLLBACK;


----------------------------------------------------

-- * 여러 컬럼을 한번에 수정 시 콤마(,)로 컬럼을 구분하면 됨.
-- D9 / 전략기획팀 ->    D0 / 전략기획2팀 수정

UPDATE DEPARTMENT2 SET 
DEPT_ID = 'D0',
DEPT_TITLE = '전략기획2팀'
WHERE DEPT_ID = 'D9'
AND DEPT_TITLE = '전략기획팀';


SELECT * FROM DEPARTMENT2;


----------------------------------------------------

-- * UPDATE 시에도 서브쿼리를 사용 가능

-- [작성법]
-- UPDATE 테이블명 SET 컬럼명 = (서브쿼리)

-- EMPLOYEE2 테이블에서
-- 평소에 유재식 사원을 부러워하던 방명수 사원의
-- 급여와 보너스율을 유재식 사원과 동일하게 변경해 주기로 했다.
-- 이를 반영하는 UPDATE문을 작성하시오.

-- 유재식 급여
SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '유재식';

-- 유재식 보너스
SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '유재식';


-- 방명수 급여, 보너스 수정
UPDATE EMPLOYEE2 SET 
SALARY = (SELECT SALARY FROM EMPLOYEE2 WHERE EMP_NAME = '유재식'),
BONUS = (SELECT BONUS FROM EMPLOYEE2 WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';

SELECT EMP_NAME, SALARY, BONUS
FROM EMPLOYEE2
WHERE EMP_NAME IN ('유재식', '방명수');

---------------------------------------------------------------------------------

-- 3. MERGE(병합) (참고만 하세요!)

-- 구조가 같은 두개의 테이블을 하나로 합치는 기능
-- 테이블에서 지정하는 조건의 값이 존재하면 UPDATE
-- 조건의 값이 없으면 INSERT 됨

CREATE TABLE EMP_M01
AS SELECT * FROM EMPLOYEE;

CREATE TABLE EMP_M02
AS SELECT * FROM EMPLOYEE
WHERE JOB_CODE = 'J4';


SELECT * FROM EMP_M01;
SELECT * FROM EMP_M02;

INSERT INTO EMP_M02 VALUES (999, '곽두원', '561016-1234567', 'kk@kh.or.kr',
	'01011112222', 'D9', 'J4', 'S1', 9000000, 0.5, NULL, SYSDATE, NULL, DEFAULT
);

SELECT * FROM EMP_M01; -- 23명
SELECT * FROM EMP_M02; -- 5명 (4명+신규1명)

UPDATE EMP_M02 SET SALARY = 0;


SELECT * FROM EMP_M02;


MERGE INTO EMP_M01 USING EMP_M02 ON(EMP_M01.EMP_ID = EMP_M02.EMP_ID)
WHEN MATCHED THEN
UPDATE SET
	EMP_M01.EMP_NAME = EMP_M02.EMP_NAME,
	EMP_M01.EMP_NO = EMP_M02.EMP_NO,
	EMP_M01.EMAIL = EMP_M02.EMAIL,
	EMP_M01.PHONE = EMP_M02.PHONE,
	EMP_M01.DEPT_CODE = EMP_M02.DEPT_CODE,
	EMP_M01.JOB_CODE = EMP_M02.JOB_CODE,
	EMP_M01.SAL_LEVEL = EMP_M02.SAL_LEVEL,
	EMP_M01.SALARY = EMP_M02.SALARY,
	EMP_M01.BONUS = EMP_M02.BONUS,
	EMP_M01.MANAGER_ID = EMP_M02.MANAGER_ID,
	EMP_M01.HIRE_DATE = EMP_M02.HIRE_DATE,
	EMP_M01.ENT_DATE = EMP_M02.ENT_DATE,
	EMP_M01.ENT_YN = EMP_M02.ENT_YN
WHEN NOT MATCHED THEN
INSERT VALUES(EMP_M02.EMP_ID, EMP_M02.EMP_NAME, EMP_M02.EMP_NO, EMP_M02.EMAIL, 
	         EMP_M02.PHONE, EMP_M02.DEPT_CODE, EMP_M02.JOB_CODE, EMP_M02.SAL_LEVEL, 	  	         EMP_M02.SALARY, EMP_M02.BONUS, EMP_M02.MANAGER_ID, EMP_M02.HIRE_DATE, 
	         EMP_M02.ENT_DATE, EMP_M02.ENT_YN);
	        
SELECT * FROM EMP_M01;

-----------------------------------------------------------------------------------------

-- 4. DELETE 
-- 테이블의 행을 삭제하는 구문

-- [작성법]
-- DELETE FROM 테이블명 WHERE 조건설정
-- 만약 WHERE 조건을 설정하지 않으면 모든 행이 다 삭제됨


SELECT * FROM EMPLOYEE2;


COMMIT;

-- EMPLOYEE2 테이블에서 '장채현'사원 정보 조회
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';


-- EMPLOYEE2 테이블에서 이름이 '장채현'인 사원 정보 삭제
DELETE FROM EMPLOYEE2
WHERE EMP_NAME = '장채현';

-- 삭제 확인
SELECT * FROM EMPLOYEE2
WHERE EMP_NAME = '장채현'; --> 조회 결과 없음


ROLLBACK;

-- EMPLOYEE2 테이블 전체 삭제

DELETE FROM EMPLOYEE2;
--> 24행 삭제

SELECT * FROM EMPLOYEE2;


ROLLBACK;


----------------------------------------------------------------------------------

-- 5. TRUNCATE (DML 아닙니다! DDL)
-- 테이블의 전체 행을 삭제하는 DDL
-- DELETE 보다 수행 속도가 더 빠르다.
-- ROLLBACK을 통해 복구할 수 없다.

-- TRUNCATE 테스트용 테이블 생성
CREATE TABLE EMPLOYEE3
AS SELECT * FROM EMPLOYEE2;

-- 생성 확인
SELECT * FROM EMPLOYEE3;

-- TRUNCATE로 삭제
TRUNCATE TABLE EMPLOYEE3;

-- 삭제 확인
SELECT * FROM EMPLOYEE3;


ROLLBACK;

-- 롤백 후 복구 확인 -> 복구 안됨을 확인!
SELECT * FROM EMPLOYEE3;

-- DELETE : 휴지통 버리기
-- TRUNCATE : 완전 삭제

06_TCL

-- TCL(TRANSACTION CONTROL LANGUAGE) : 트랜잭션 제어 언어
-- COMMIT(트랜잭션 종료 후 저장), ROLLBACK(트랜잭션 취소), SAVEPOINT(임시저장)

-- COMMIT은 신중하게 사용!

-- DML : 데이터 조작 언어로 데이터의 삽입, 수정, 삭제
--> 트랜잭션은 DML과 관련되어 있음.


/* TRANSACTION이란?
 - 개념 : 데이터베이스의 논리적 연산 단위
 
 - 데이터 변경 사항을 묶어 하나의 트랜잭션에 담아 처리함.

 - 트랜잭션의 대상이 되는 데이터 변경 사항 : INSERT, UPDATE, DELETE (DML), MERGE
 
 EX) INSERT 수행 --------------------------------> DB 반영(X)
   
     INSERT 수행 --> 트랜잭션에 추가 --> COMMIT --> DB 반영(O)- T
     
     INSERT 10번 수행 --> 1개 트랜잭션에 10개 추가 --> ROLLBACK --> DB 반영 안됨


    1) COMMIT : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 DB에 반영
    
    2) ROLLBACK : 메모리 버퍼(트랜잭션)에 임시 저장된 데이터 변경 사항을 삭제하고
                 마지막 COMMIT 상태로 돌아감.(DB에 변경 내용 반영 X)
                
    3) SAVEPOINT : 메모리 버퍼(트랜잭션)에 저장 지점을 정의하여
                   ROLLBACK 수행 시 전체 작업을 삭제하는 것이 아닌
                   저장 지점까지만 일부 ROLLBACK 
    
    [SAVEPOINT 사용법]
    
    SAVEPOINT 포인트명1;
    ...
    SAVEPOINT 포인트명2;
    ...
    ROLLBACK TO 포인트명1; -- 포인트1 지점 까지 데이터 변경사항 삭제

*/

SELECT * FROM DEPARTMENT2;

-- 새로운 데이터 INSERT 
INSERT INTO DEPARTMENT2 VALUES('T1', '개발1팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T2', '개발2팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T3', '개발3팀', 'L2');
--전체 실행 : 전체 드래그 후 Alt + X


-- INSERT 확인
SELECT * FROM DEPARTMENT2;
--> DB에 반영된 것 처럼 보이지만
-- SQL 수행 시 트랜잭션 내용도 포함해서 수행된다.
-- (실제로 아직 DB에 반영 X)

-- ROLLBACK 후 확인
ROLLBACK;
SELECT * FROM DEPARTMENT2;


-- COMMIT 후 ROLLBACK이 되는지 확인
INSERT INTO DEPARTMENT2 VALUES('T1', '개발1팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T2', '개발2팀', 'L2');
INSERT INTO DEPARTMENT2 VALUES('T3', '개발3팀', 'L2');

COMMIT;

SELECT * FROM DEPARTMENT2;

ROLLBACK;

SELECT * FROM DEPARTMENT2; --> 롤백 안됨!

-----------------------------------------------------------------------------

-- SAVEPOINT 확인
INSERT INTO DEPARTMENT2 VALUES('T4', '개발4팀', 'L2');
SAVEPOINT SP1;

SELECT * FROM DEPARTMENT2;

INSERT INTO DEPARTMENT2 VALUES('T5', '개발5팀', 'L2');
SAVEPOINT SP2;

INSERT INTO DEPARTMENT2 VALUES('T6', '개발6팀', 'L2');
SAVEPOINT SP3;



ROLLBACK TO SP1;


SELECT * FROM DEPARTMENT2;


DELETE FROM DEPARTMENT2
WHERE DEPT_ID LIKE 'T%';

SELECT * FROM DEPARTMENT2;

-- SP2 지점까지 롤백
ROLLBACK TO SP2;

SELECT * FROM DEPARTMENT2;

-- SP1 지점까지 롤백
ROLLBACK TO SP1;
SELECT * FROM DEPARTMENT2;

ROLLBACK;
SELECT *  FROM DEPARTMENT2;

연습문제

1. 아래와 같이 USER_TEST 테이블을 만들고, 데이터를 삽입하시오.
   

CREATE TABLE USER_TEST (
	ID NUMBER,
	Name VARCHAR2(10),
	ReserveDate DATE,
	RoomNum NUMBER
);

INSERT INTO USER_TEST(ID, Name, ReserveDate, RoomNum) VALUES(1, '홍길동', '2016-01-05', 2014);
INSERT INTO USER_TEST(ID, Name, ReserveDate, RoomNum) VALUES(2, '임꺽정', '2016-02-12', 918);
INSERT INTO USER_TEST(ID, Name, ReserveDate, RoomNum) VALUES(3, '장길산', '2016-01-16', 1208);
INSERT INTO USER_TEST(ID, Name, ReserveDate, RoomNum) VALUES(4, '홍길동', '2016-03-17', 504);
INSERT INTO USER_TEST(ID, Name) VALUES(6, '김유신');

SELECT * FROM USER_TEST;

2. USER_TEST 테이블을 UPDATE/DELETE 구문을 사용하여 아래와 같이 수정하시오.
   

UPDATE USER_TEST SET
RoomNum = '2002'
WHERE ID = 1;

UPDATE USER_TEST SET 
RoomNum = '2002'
WHERE ID = 4;

SELECT * FROM USER_TEST;

DELETE FROM USER_TEST 
WHERE ID = 6;

SELECT * FROM USER_TEST;

3. USER_TEST 테이블을 UPDATE 구문을 사용하여 아래와 같이 수정하시오.
   

UPDATE USER_TEST SET 
RoomNum = '2002'
WHERE ID = 2;

UPDATE USER_TEST SET 
RoomNum = '2002'
WHERE ID = 3;

SELECT * FROM USER_TEST;

4. EMPLOYEE4 테이블을 EMPLOYEE 테이블과 같이 생성하기

CREATE TABLE EMPLOYEE4 AS SELECT * FROM EMPLOYEE;

SELECT * FROM EMPLOYEE4;

5. EMPLOYEE4 테이블에서 고용일이 2000년대 이전인 사람의 월급을 100만원씩 인상해주자.

UPDATE EMPLOYEE4 SET 
SALARY = SALARY + 1000000
WHERE 1 = 1
  AND HIRE_DATE < '2000-01-01';
 
SELECT * FROM EMPLOYEE4;

6. EMPLOYEE4 테이블에서 한국에 근무하는 직원의 BONUS를 0.5로 변경

-- TIP 서브쿼리 사용!
UPDATE EMPLOYEE4
SET BONUS = 0.5
WHERE EMP_NAME IN (
  	SELECT EMP_NAME
  	FROM EMPLOYEE4
  	LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
  	LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
  	LEFT JOIN "NATIONAL" USING(NATIONAL_CODE)
  	WHERE NATIONAL_NAME = '한국');
  
SELECT EMP_NAME, BONUS FROM EMPLOYEE4;