SQL-17) DML - Insert, update, delete, merge : SQL, SQLD, SQLD 자격증, Oracle

안녕하세요 이승혁니다.

이번 시간에는 DML(Data Manipulate Language,데이터 조작어)에 대해 알아보도록 하겠습니다

 

※ DML 문장

 

     데이터를 조작하는 언어입니다.

 

     1. Insert : 데이터 입력 구문

     2. update : 데이터 수정 구문

     3. delete : 데이터 삭제 구문

     4. merge : 입력, 수정, 삭제를 한 번에 수행하는 명령어

 

---

ㆍ DML - insert

 

     데이터를 테이블에 삽입할 때 사용합니다.

 

     insert into 테이블이름(칼럼1,칼럼2,칼럼3...) values('데이터1','데이터2','데이터3',to_date('날짜형데이터'))

 

     테이블 이름 뒤 칼럼명을 기재하지 않을 경우 입력 데이터를 칼럼 순서에 맞춰주어야 합니다.

 

실습 1. DML - insert , dept 테이블에 (50,'SEOUL','RESEARCH') 자료 입력해보기

insert into dept(deptno,loc,dname) values(50,'SEOUL','RESEARCH');

select *
 from dept;

---

ㆍDML - update

 

     데이터를 수정할 때 사용합니다.

 

     update 테이블이름

     set 변경 칼럼 내용

     where 변경 칼럼 조건

 

실습 2. DML - update , SCOTT의 월급을 5000으로 변경해보기

update emp
set sal=5000
where ename='SCOTT';

select *
 from emp
 where ename='SCOTT';

 

※ update 사용시 주의사항 !

 

    where절을 주의해서 사용해야 한다.

    아래 코드에서 세미콜론을 실수해서 set절에 적는다면

    모든 데이터가 변경될 수 있다.

    exit은 임시적으로 commit(변경 내용 저장)을 수행하기 때문에 주의해야 한다.

 

    update emp
    set sal=5000;
    where ename='SCOTT';

   

---

 

ㆍ DML - delete

 

     데이터를 삭제할 때 사용합니다.

 

     delete from 테이블이름 where 조건

 

실습 3. DML-delete , 사원번호가 7788인 사원을 삭제하기

delete from emp where empno=7788;
select *
from emp;

 

※ 실수로 delete & commit 했을 때 ?

 

되돌리기(timestamp 사용)

commit을 했더라도 사용 가능하다.

 

alter table emp enable row movement;  - emp 테이블이 falshback이 가능하도록 설정하는 명령어

flashback table emp to timestamp

(systimestamp - interval '5' minute)       - emp 테이블을 현재 시간에서 5분 전으로 되돌리는 명령어

 

15분 이내로 수행해야 합니다.

 

삭제했던 사원번호 7788의 삭제 데이터 복구, 월급도 5분 전의 값으로 복구되었다. 

 

※ 데이터를 삭제하는 3가지 방법

 

1. delete from emp

2. truncate table emp

3. drop table emp

	delete	truncate	drop
데이터	삭제	삭제	삭제
저장공간	유지	삭제	삭제
저장구조	유지	유지	삭제
falshback	가능	불가능	가능

 

truncate 명령어는 저장 구조 , 즉 테이블의 형태만 남기고 모두 삭제합니다.

falshback이 불가능하기 때문에 사용 전 주의해야 합니다.

 

※ 휴지통 확인하는 방법

select *

from user_recyclebin;

 

※ 휴지통에서 복구하는 방법

flashback table emp to before drop;

 

 

※ 휴지통 비우기

purge recyclebin;

휴지통을 비우고 난 후에는 복구하는 방법(falshback) 써도 복구가 안된다.

 

---

ㆍDML - merge 

 

     insert , update , delete를 동시에 수행하는 DML문장

    SQL 튜닝을 위해 많이 사용하는 SQL

 

    merge into 테이블이름

    using 테이블2이름

    on ( 조건 )

    when matched then

    update set 변경내용

 

 

실습 4. 칼럼 추가하기 , 사원 테이블에 등급(grade) 칼럼 추가하기

alter table emp
add grade number(5);

실습 5. DML - merge, 해당 사원의 급여 등급으로 값을 갱신하기

                             급여등급(salgrade 테이블의 급여 등급 사용)

merge into emp e
using salgrade s
on (e.sal between s.losal and s.hisal)
when matched then
update set e.grade=s.grade;

select *
from emp;

 

merge into emp e                           <- emp 테이블을 변경한다. 테이블 별칭 e

using salgrade s                             <- 변경에 salgrade 테이블을 사용한다. 테이블 별칭 s

on ( e.sal between s.losal and s.hisal ) <- emp 테이블의 sal칼럼이 s의 losal / hisal의 사이인 조건을 사용하고

when matched then                        <- 조건에 맞는다면

update set e.grade = s.grade             <- emp 테이블의 grade는  s 테이블의 grade로 갱신한다.

 

실습 6 . DML - merge & update, emp 테이블에 sumsal 칼럼을 추가하고, 해당 부서의 토탈월급으로 갱신한다.

alter table emp
add sumsal number(20);

merge into emp e
using (select deptno,sum(sal) s
        from emp
        group by deptno) e2
on (e.deptno=e2.deptno)
when matched then
update set e.sumsal=e2.s;

select *
from emp;

실습 7. DML - merge insert & update , emp 테이블 loc 칼럼 생성, dept 번호가 없으면 (1111,null,deptno)로 emp 테이블에 데이터 삽입해보기

alter table emp
add loc varchar(20);

merge into emp e
using dept d
on (e.deptno = d.deptno)
when matched then
update set e.loc=d.loc
when not matched then
insert(e.empno,e.ename,e.deptno) values(1111,null,d.deptno);

select *
from emp;

emp.deptno = dept.deptno 맞다면 (when matched then) update 절 수행,

맞지 않다면 ( when matched then ) insert절을 수행합니다.

 

d.deptno의 40번은 emp 테이블에 없기 때문에 insert절을 수행해 새로운 행이 하나 삽입됩니다.

 

※ merge를 사용하지 않고 작업을 수행 ? 

update emp e 
set loc = (select loc
            from dept d
            where e.deptno = d.deptno);

SQL은 부서위치를 14번 갱신하게 된다.

갱신해야 할 데이터가 많으면 많을수록 성능이 느려진다.

merge문은 한 번에 갱신한다.    

그림에서 알 수 있듯이 열을 통째로 한 번에 갱신한다.

=> 속도적 측면에서 훨씬 이득이다.

 

---

ㆍDML - insert ( Subquery )

 

     서브쿼리를 사용한 insert 문

     1. emp 테이블과 똑같은 구조의 테이블 생성

create table test_table
as
select *
from emp;

truncate table test_table; --> 저장 구조만 남기고 데이터 삭제

select * from test_table;

 

    2. insert + 서브쿼리를 통해 test_table의 내용을 emp 테이블로 삽입하기

insert into test_table
select *
from emp;

select * from test_table;

---

ㆍDML - update ( Subquery )

 

    update ----- 서브쿼리 가능

    set      ----- 서브쿼리 가능

    where  ----- 서브쿼리 가능

 

실습 8. SCOTT의 월급을 KING의 월급으로 update하기

update emp
set sal=(select sal            -- KING의 월급을 선택하는 서브쿼리
          from emp
          where ename='KING')
where ename='SCOTT';

select *
from emp
where ename in ('SCOTT','KING');

---

ㆍDML - delete ( Subquery )

 

    update ----- 서브쿼리 가능

    set      ----- 서브쿼리 가능

    where  ----- 서브쿼리 가능

 

실습 9 . JAMES보다 늦게 입사한 사원들을 지워보기

delete
from emp
where hiredate > ( select hiredate
                    from emp
                    where ename = 'JAMES');

 

※ 데이터를 다시 복구하기 위해 테이블 생성 쿼리를 다시 수행합니다.

https://lsh-story.tistory.com/2?category=952228

SQL-2) Query

위 페이지의 실습 테이블 생성 쿼리를 실행하시면 됩니다.

 

다음 포스팅에서는 TCL(Transaction Control Language)에 대한 실습을 진행해 보도록 하겠습니다.

감사합니다.