#7 DML (insert, insert all, update, delete, update join, 트랜잭션)

1. INSERT

1) INSERT 를 사용하여 1행씩 입력하기

INSERT INTO 테이블명 [(삽입할 컬럼명 list)]

VALUES (value_list);

 

- 컬럼에 값을 부여하지 않으면 자동으로 null 값이 들어감

 

2) INSERT와 서브쿼리를 사용하여 여러 행 입력하기

INSERT INTO 테이블명

SELECT * FROM 테이블명;

 

- WHERE 절을 사용하여 조건에 맞는 행만 넣을 수도 있다.

 

3) INSERT ALL

여러 테이블에 여러 행 입력하기

- 다른 테이블의 데이터를 가져와서 입력하기

EX)

테이블에서 교수번호가 1000 - 1999 번까지인 교수 번호&교수 이름은 prof_3 테이블에 입력

교수번호가 2000 - 2999 번까지인 교수 번호와 이름은 prof_4 에 입력

> INSERT ALL

WHEN profno BETWEEN 1000 AND 1999 THEN

    INTO prof_3 VALUES( profno, name )

WHEN profno BETWEEN 2000 AND 2999 THEN

    INTO prof_4 VALUES( profno, name )

SELECT profno, name

FROM professor;

 

- 다른 테이블에 동시에 같은 데이터 입력하기

ex)

prof_3 과 prof_4 테이블 데이터 truncate로 삭제 후 professor 테이블에서 교수번호가 3000 -3999 번 교수들의 교수번호&이름을 동시에 입력

> INSERT ALL

    INTO prof_3 VALUES ( profno, name )

    INTO prof_4 VALUES ( profno, name )

SELECT profno, name

FROM professor

WHERE profno between 3000 and 3999;

2. UPDATE

- 데이터 변경하기

UPDATE 테이블

SET 컬럼 = 값

WHERE 조건 ;

- 테이블에서 조건이 맞는 행에 대해 컬럼 값을 지정한 값으로 변경한다

 

EX) 서브쿼리 사용

> UPDATE professor

SET pay = pay*1.15

WHERE position = ( SELECT position

                           FROM professor

                           WHERE name = 'Sharon Stone' )   --서브쿼리

AND pay < 250 ;

 

3. DELETE

DELETE FROM 테이블명

WHERE 조건 ;

- 조건에 해당하는 행을 삭제

- 할당되어있던 공간은 유지됨

 

4. MERGE

여러 테이블의 데이터를 합치는 병합을 의미

 

[ 문법 ]

MERGE INTO 테이블1

USING 테이블2

ON ( 병합 조건절 )

WHEN MATCHED THEN

    UPDATE SET 업데이트내용

or DELETE WHERE 조건

WHEN NOT MATCHED THEN

    INSERT VALUES ( 컬럼 이름 );

 

- 테이블2를 이용하여 테이블1을 MERGE 해서 테이블1에 모은다. ( ON 조건에 따라 )

- 3행의 조건을 만족하면 ( WHEN MATCHED THEN ) UPDATE나 DELETE가 수행되고

- 만족하지 않으면 ( WHEN NOT MATCHED THEN ) 테이블2의 내용이 테이블1에 신규 행으로 삽입된다.

 

* 집계 테이블에 데이터가 많아질 수록 MERGE 작업의 수행속도는 늦어진다.

- MERGE 작업을 조금이라도 빨리 수행하려면 위 문법에서 3행의 조건절에 인덱스가 잘 만들어져 있어야 한다.

(아니면 속도가 느려짐)

 

* MERGE 주의사항

- ON 절에 해당되는 조건의 컬럼은 중복이 있으면 안된다. (기본키 | UNIQUE INDEX 를 설정 )

   중복되는 값들 중 어떤 값과 비교하여 MERGE를 수행해야 할지 모르기 때문이다.

- 부하가 많이 걸림, 

 

5. UPDATE 조인

- UPDATE 문에 조인이 발생하는 경우

1) WHERE 절에만 다른 테이블과 조인하는 경우

2) WHERE 절, SET 절 모두 다른 테이블과 조인을 하는 경우

(SET 이나 WHERE 절 속에 다른 테이블과 조인이 발생하는 것)

 

★ set 절에만 Join을 걸면 안된다.

- 이 경우 set에서 join에 걸리지 않은 컬럼 값은 NULL로 값이 변경된다.

 

- 일반적인 UPDATE는 단일 테이블

UPDATE JOIN

 

- WHERE절에서 다른 테이블과 조인

EX)

부서가 DALLAS에 위치하는 부서에서 근무하는 사원의 급여를 10% 인상

UPDATE emp e

SET sal = (sal + sal*0.1)

WHERE EXISTS

(

SELECT 1 FROM dept d

where d.loc = 'DALLAS' and e.deptno =d.deptno

) ;

 

- WHERE EXISTS : 서브쿼리가 참일 때 출력

- select 1 : 어떤 조건을 만족하는지만 알고싶을 때 사용, 서브쿼리를 만족하면 1을 출력하고 아니면 null

 

update dept_hist d
set d.deptno = (
select e.deptno from emp e
where e.empno = d.empno
);

 

 

emp 테이블에 존재하지 않는 empno의 deptno 까지 null 로 변경됨

 

update dept_hist d
set d.deptno = (
select e.deptno from emp e
where e.empno = d.empno)
where exists ( 
select 1 from emp e
where e.empno = d.empno
);

- where exists 로 update의 범위를 e.empno가 있는 행들로 지정

- empno가 emp 테이블에 존재하지 않으면 업데이트 범위에 포함되지 않음

-> emp테이블을 두번 읽어야 함 => merge를 사용하는 것이 낫다.

 

MERGE INTO dept_hist d

using emp e

on ( d.empno = e.empno )

when matched then

update set d.deptno = e.deptno;

 

6. 트랜잭션 관리하기

DML 명령 ( UPDATE, INSERT, DELETE )을 수행한 후에는 COMMIT 이나 ROLLBACK 해야 작업이 마무리 된다.