11. 데이터 조작 언어(DML문)

■ 데이터 조작 언어

■DML

*DML 문이란?

 Data         Manipulation Language

 데이터를   조작하는    언어

 

 

*DML 문의 종류?

  1. insert    : 데이터 입력

  2. update  : 데이터 수정

  3. delete   : 데이터 삭제

  4. merge   : 데이터 입력, 수정, 삭제를

                   한번에 수행하는 것

 

■ insert 문

예:  insert  into  emp(empno, ename, sal)

                          values ( 1902,  'JACK',  4500);

 

**문자, 날짜만 싱글쿼테이션 붙임.

※ 설명:

 insert 절에는 컬럼명 리스트를 나열 하고

 values절에는 컬럼명 리스트에 맞게

                  데이터를 나열한다

 데이터를 작성할 때는 숫자와는 다르게

                              문자와 날짜는

 양쪽에 싱글 쿼테이션 마크를 써야한다.

 그런데

 만약에 아래와 같이 컬럼명 리스트를

                           안 적게 되면

insert into emp

                   values(1923, 'JACK', 4000, 'SALESMAN', ................)

테이블 안에 있는

퀄럼 수 만큼 순서대로 다 적어줘야함.

 

 

 

문제288.

아래의 데이터를 EMP 테이블에 입력하시오

사원번호 9345

사원이름 jane

월급      4600

입사일 2108년 10월 24일

 

insert into emp( empno, ename, sal, hiredate)

             values(9345, 'jane', 4600, to_date('2018/10/24', 'rrrr/mm/dd') );

 

 

 

 

문제289.

아래의 데이터를 입력하시오

사원번호   3458

사원이름   jone

월급         5600

입사일     오늘날짜

 

insert into emp(empno, ename, sal, hiredate)

  values(3458, 'jone', 5600, sysdate);

 

**sysdate를 안 쓰는게 좋음

그 이유는 다음 문제로

 

문제290.

오늘 입사한 사원의 이름과 입사일을

출력하시오

select ename, hiredate

from emp

where hiredate= sysdate;

값이 없다고 나옴

 

sysdate 는 입력할 때

시:분:초 가 같이 입력됨.

그래서 시분초를 써줘야 값이 나온다.

근데 어떻게 알아 ;;;;;;;;;

어떻게든 나오게 하려면

like 나 between 을 써야 한다

 

 

문제291. sysdate(2018/10/24 에 입력함) 로 입력한 사원을 뽑아라.

select ename, hiredate

from emp

where to_char(hiredate, 'rrrr/mm/dd')

 = '2018/10/24' ;

 

이러면 나온다아아

근데 좌변 가공됬으니 악성...이구나아...

 

select ename, hiredate

from emp

where hiredate between

to_date('2018/10/24', 'rrrr/mm/dd')

                          and

to_date('2018/10/25', 'rrrr/mm/dd');

 

또는

 

 

select ename, hiredate

from emp

where hiredate between

to_date('2018/10/24', 'rrrr/mm/dd')

                          and

to_date('2018/10/24', 'rrrr/mm/dd')+1 ;

 

**근데 이렇게 하면

24, 25일로 구간을 잡아주게 된다.

2018/10/24 00:00~

2018/10/24 00:00 + 1day=

                       2018/10/25 00:00

 

***to_date('2018/10/24', 'rrrr/mm/dd')

이 뜻이 년/월/일, 00시 00분 00초 를 뜻한다.

 

 

between  and 말고 다른 연산자로 하면

 

select ename, hiredate

from emp

where hiredate >= to_date('2018/10/24', 'rrrr/mm/dd')

                and           

       hiredate < to_date('2018/10/24', 'rrrr/mm/dd')+1;

 

중요한건

 <= 쓰면 안된다.

00시00분00초 부터 12시59분59초의

범위가 필요한 거니까.

 

문제291.(오늘의 마지막 문제)

아래의 데이터를 입력하고

이름이 null이 아니고, 공백도 아닌

사원의 이름과 월급을

출력하시오

(trim 써서)

 

insert into emp(empno, ename, sal)

 values( 2919, '     ', 5600);

 

select ename, sal

from emp

where ename not like '%null%'

 and

          ename not like '%   %';

 

*******like는 단일행만 되나봥

그래서

like ('%null%', '%     %');

이 안먹어

 

다른답

select ename, sal

from emp

where trim(ename) is not null;

근데 악성이래

좌변 건든거라서.

 

튜닝해야함

인덱스 배울 때 알려주신다함~

 

 

 

 

 

문제292.

아래의 데이터를 사원 테이블에 입력하시오

사원번호 3821

사원이름 Biff

월급       4500

입사일    오늘날짜 2018/10/25

부서번호 30

직업       SALESMAN

 

insert into emp(empno, ename, sal, hiredate, deptno, job)

values(3821, 'Biff', 4500, to_date('2018/10/25', 'rrrr/mm/dd'), 30, SALESMAN);

commit;  <<<<---데이터를 database에 저장하겠다

                    라는 뜻

**sysdate 를 쓰면 안되는 이유 알고 있어야 함

 

■null 을 입력하는 방법 2가지

 1. 암시적 입력 방법

   예: insert into emp(empno, ename, sal)

       values(2919, 'allen', 4000) ;

 emp 테이블에 위의 3개의 컬럼외에는

다 null이 자동으로 입력된다

(mgr, hiredate 같은건 null 이 된다.)

 

**sqlplus 는 null 이라고 안나오고 그냥 비어있고

 sqlgate 는 null 이라고 나온다.

 

 

2. 명시적 입력 방법

    - null

    - ''  <<싱글 쿼테이션 붙여서 두번쓰면 null,

          ***싱글 쿼테이션 띄어서 쓰면 빈공간.

 예:

 insert into emp(empno, ename, sal)

 values(3945, null, 4500);

 

insert into emp(empno, ename, sal)

values(3946, '', 4600);u

 

 

 

문제293.

아래의 데이터를 사원 테이블에 입력하시오

사원번호  2912

사원이름  Anneena

월급       5000

커미션    null

직업      ANALYST

부서번호 20

 

insert into emp(empno, ename, sal, comm, job, deptno)

values(2912, 'Anneena', 5000, null, 'ANALYST', 20);

 

 

 

■ update 문

예:

 update emp     -------------------->바꾸겠다

   set sal = 8000 ------------------->월급을 8000으로

   where ename= 'KING';--------->킹의 것을

select ename, sal from emp;

 

 

 

 

문제294.

부서번호가 10번인 사원들의 커미션을 9000 으로

수정하시오

 

update emp

 set comm=9000

 where deptno = 10;

 

 

 

문제295.

SCOTT의 월급을 4700으로 변경하고

커미션을 5600으로 변경하시오

 

update emp

set sal=4700, comm=5600

where ename='SCOTT';

 

콤마써서 이중지정 가능

 

 

문제296.

월급이 1000 에서 3000 사이인 사원들의

부서번호를 50번으로 변경하시오

 

update emp

set deptno=50

where sal between 1000

              and       3000;

 

이제

rollback;

 

commit  안 한건 지워진당 ㅎ

 

 

 

문제297.

sk의 월정액을 60000 으로 변경하시오

(telecom_price 테이블에서)

 

update telecom_price

set month_price = 60000

where telecom_name = 'sk';

 

rollback 하시고

 

■ delete 문

 delete from emp

  where ename = 'SCOTT';

 

emp 테이블에서 scott 을 지우겠다

라는 뜻~

 

 

문제298.

직업이 SALESMAN 인 사원들을 삭제하시오

delete from emp

where job = 'SALESMAN';

 

rollback~

 

실수로 데이터 지웠어도

commit 안했으면

rollback 으로 다시 돌아온다

 

 

delete from emp;

commit;

rollback;

select * from emp;

그럼 테이블은 있어도

내용 다 사라진걸 볼 수 있다.

 

회사에서 쓰는 db 는 좋은거라서

플래시밸? 이라는걸 쓰면

실수로 지우고 커밋한걸 다시 복구 할 수 있다

근데 골든타임이 10분이라서 재빨리 해야한다.

 

 

 

■ subquery 사용한 DML 문

 

*subquery 를 사용한 insert 문

예:

 우리반 테이블과 똑같은 테이블을 emp2_backup2

로 생성하시오

 

create table emp2_backup2

as

 select *

   from emp2;

 

select * from emp2_backup2;

 

 

문제299.

우리반 테이블 데이터를 전부 삭제하고

커밋하시오

delete from emp2;

commit;

 

 

 

화면 캡처: 2018-10-25 오전 10:58

 

******* 별(*) 안들어감

 

 

문제300.

백업받은 emp2_backup2 테이블의 데이터를

emp2 테이블로 로드하시오

emp2_backup2 -------------------->emp2

                              data

 

 

insert into emp2

select *

from emp2_backup2;

 

**서브쿼리를 이용한 insert into 한거라 함.

***create 에서  as  쓰는거 빼고는 다 똑같음.

 

 

문제301.

아래와 같이 emp2_backup3 라는 테이블을

생성하시오

(데이터는 가져오지 않고

테이블 구조만 가져오는 문법)

 

***이미 있다고 나오면

drop table 테이블명***

 

create table emp2_backup3

as

  select *

   from emp2

     where 1=2;

1=2 는 false 라서

내용없이

테이블구조만 생성한다.

 

원래 컬럼이하 값 다 넣어서 테이블생성하는거

create table emp2_backup2

as

 select *

   from emp2;

 

 

문제302.

emp2_backup3 테이블에

우리반 테이블의 통계학과와 심리학과 학생들의

데이터를 모두 입력하시오

 

insert into emp2_backup3

select *

 from emp2

 where major like '%통계%'

                   or

        major like '%심리%';

 

왜 and는 안되지

통계이면서 심리학과인 학생들이니까

 

*subquery 를 사용한 update 문

 

문제303.

JONES 보다  더 많은 월급을 받는 사원들의

커미션을 7000 으로 변경하시오

update emp

set comm=7000

where sal >

 (select sal from emp

 where ename ='JONES');

 

 

문제304.

관리자인 사원들의 월급을 9000 으로 변경하시오

(자기 밑에 직속부하가 한명이라도 있는 사원들)

 

update emp 관리자

set sal=9000

where empno in

 (select mgr from emp 사원

where 사원.mgr= 관리자.empno);

>>>>>>

같은 행 테이블 조인일때나 별칭지정 하는거임

 

이렇게 해도 됨

update emp

 set sal =9000

where empno in(select mgr from emp)

 

 

 

 

문제 305.

관리자가 아닌 사원들의 월급을 2000 으로

변경하시오

(자기밑에 직속부하가 한명도 없는 사원들)

 

update emp

 set sal = 2000

 where empno not in

  (select nvl(mgr, -1) from emp);

 

*not in 쓸 때 주의사항

**null 처리 해야함

8회수업 246번 설명 참고

 

 

문제 306.

SMITH의 월급을 KING의 월급으로 변경하시오

 

update emp

set sal= (select sal

           from emp

           where ename = 'KING')

where sal ='SMITH';

 

 

 

■ update 문에 subquery 를 쓸 수 있는 절

update --------------서브쿼리 가능 (튜닝할 때 사용)

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

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

 

 

 

 

이런 튜닝기법을 알리기 위한 책이 있음

'최상위 1%만 아는 튜닝의 비밀'

 (로지컬 옵티마이져의 원리)

 

 

 

문제307.

이름과 부서위치를 출력하시오

select E.ename, D.loc

from emp E, dept D;

 

 

 

 

문제308.

사원 테이블에 sal2 라는 컬럼을 추가하시오

alter table emp

 add sal2 number(10);

 

확인

select ename, sal2, sal

from emp;

 

 

문제309.

사원테이블의 sal2 를 sal 데이터로 수정하시오!

 

update emp

set sal2=sal;

 

 

 

 

문제310.

사원 테이블의 emp_loc 라는 컬럼을 추가하시오

 

alter table emp

 add emp_loc vachar2(20);

 

select ename, emp_loc

from emp;

 

 

 

문제311.

이름, emp_loc, 부서위치를 출력하는데

부서위치의 컬럼명을 dept_loc 라는 이름으로

출력하시오

 

select E.ename, E.emp_loc, D.loc as dept_loc

from emp E, dept D

where E.deptno= D.deptno;

 

 

 

 

문제312.(점심시간 문제)

emp 테이블에 job2 라는 컬럼을 추가하고

job 의 데이터를

job2 에 update 하시오

 

alter table emp

 add job2 varchar2(20);

 

update emp

set job2=job;

 

■ update 문에서 서브쿼리 쓸 수 있는 절

update 테이블

set 바꾸고자 하는 값

where 조건

다 가능

 

 

문제313.

emp 테이블에 loc_emp 컬럼 하나 만드시오.

그리고

dept 테이블의 loc 컬럼을

loc_emp 컬럼에 갱신시키시오

(merge 이용. 제약 걸어야함)

 

311번의

select E.ename, E.emp_loc, D.loc as dept_loc

from emp E, dept D

where E.depno= D.deptno;

 

 

update

         (select E.ename, E.emp_loc, D.loc as dept_loc

                from emp E, dept D

                where E.deptno= D.deptno)

set emp_loc=dept_loc ;

 

 

update  ( ) <<< 하나의 테이블을 내가 설정한다.

 

 

***에러난다

emp 테이블에 있는 부서번호가

dept 테이블에 있는 부서번호여야

update 를 할 수 있지

emp 테이블에 있는 부서번호가

dept 테이블에 있는 부서번호가 아니면

update 를 할 수 없다.

그러니 나에게 믿음을 줘라, 못믿겠다

라고 컴퓨터가 말하는거;;;;

그러므로

강제로 emp 테이블에 10,20,30,40 부서번호 외에는

다른데이터가 못 들어가게=입력안되게

제약을 걸어라.

 

방법은

부모 ----------------------            자식

dept                                         emp

10                                              10

20                                               .

30                                               .

40                                              20

                                                    .

                                                    .

                                                  30

부모 자식으로 설정하면

자식은 부모숫자(10,20,30,40) 안에서의 숫자만 있으면

어떻게 있어도 상관없다.

 

 

 

 

 

문제314.

아래의 데이터를 emp 테이블에

입력하시오

 

사원번호 8291

사원이름 jack

월급       3000

 부서번호 70

 

insert into emp(empno, ename, sal, deptno)

values (8291, 'jack', 3000, 70);

 

이렇게 우리는

부서번호를 70번도, 다른 여러번호도 넣을 수 있다

그래서 컴퓨터가 데이터를 못 믿는다.

(>>>>update 에서 deptno에 대한

          2개테이블 조인 못시키게함)

 

 

 

 

문제315.

emp 테이블에 지금 방금 입력한 부서번호 70번

사원의 데이터를

지우시오

 

delete from emp

where ename = 'jack';

 

 

 

 

 

문제316.

강제로 emp 테이블에 10,20,30,40 부서번호외에는

다른 데이터가 입력안되게 제약을 걸어라

 

-dept 테이블에 부모키를 생성

alter table dept

 add constraint dept_deptno_pk primary key(deptno);

 

 

-emp 테이블에 deptno 에 자식키를 걸면서

dept 테이블에 deptno 를 참조해라

alter table emp

 add constraint emp_deptno_fk foreign key(deptno)

 references dept(deptno);

 

--70번 부서번호를 emp 테이블에 입력시도

insert into emp(empno, ename, sal, deptno)

values (8291, 'jack', 3000, 70);

 

 

부모 ----------------------            자식

dept                                         emp

10                                              10

20                                               .

30                                               .

40                                              20

                                                    .

                                                    .

                                                  30

 

부모, 자식관계의

제약을 걸어놔서 10,20,30,40 은 입력되도

부서번호 70번의 jack 데이터는 입력안된다.

 

그럼 이제 313번의 문제가 해결 된다

update

         (select E.ename, E.emp_loc, D.loc as dept_loc

                from emp E, dept D

                where E.deptno= D.deptno)

set emp_loc=dept_loc ;

 

 

 

문제317.

우리반 테이블에

month_price 컬럼을 추가하시오

alter table emp2

 add month_price number(10);

 

 

 

문제318.

우리반 테이블의 telecom 에 sk 를

대문자로 되어져 있는 데이터를

소문자 sk 로 변경하시오

 

update emp2

set telecom = 'sk'

where telecom = 'SK';

 

 

 

문제319.(아직 안배웠어요~~)

아래와 같이 telecom_price 를 부모테이블로 두고

emp2 를 자식 테이블로 만드시오

(telecom  과  telecom_name   에 대해서)

 

alter table telecom_price

 add constraint t_pk primary

key(telecom_name);

 

alter  table  emp2

 add  constraint  t_fk  foreign  key(telecom)

 references  telecom_price(telecom_name);

 

 

문제320.

우리반 테이블의 month_price 컬럼을

telecom_price 테이블의 month_price 데이터로

변경하시오

( merge 를 이용하시오)

 

 update

      (select e.month_price as emp_price,

                  t.month_price as t_price

        from emp2 e, telecom_price t

        where e.telecom = t.telecom_name)

set emp_price = t_price;

 

 

merge 이용한거

merge into emp2 e

    using telecom_price T

 on(e.telecom = T.telecom_name)

 when matched then

 update set e.month_price = T.month_price;  

 

 

문제321.

 SCOTT  보다 월급을 많이 받는 사원들을

삭제하시오

delete emp

where sal > (select sal from EMP

                    where ename = 'SCOTT');

 

 

문제322.

ALLEN 보다 늦게 입사한 사원들을 삭제하시오

 

delete emp

 where hiredate>(select hiredate from emp 

                          where ename = 'ALLEN');

 

 

 

문제323.

DALLAS 에서 근무하는 사원들을 삭제하시오

 

delete from dept

where loc= 'DALLAS'

 

 

문제324.

KING 에게 보고하는 사원들을 삭제하시오

 

delete from emp

where mgr IN (select empno from emp

                      where ename= 'KING');

 

 

 

화면 캡처: 2018-10-29 오후 7:22

 

KING EMPNO = 7839

KING 은 MGR 이 있을까?

 

제약 다 삭제하겠다

drop table dept cascade constraints;

***cascade 화학용어:  동위원소분리

 

 

■DML 문

 1. insert

 2. update

 3. delete

 4. merge

 

■merge 문

"insert , update, delete 를 하나의 문장으로

 한 번에 수행하는 SQL"

 

 

문제325.

사원테이블에 emp_loc 컬럼을 추가하시오

 

alter table emp

 add emp_loc varchar2(20);

 

 

 

문제326.

merge 문을 이용해서

emp_loc 컬럼을

dept 테이블의 loc 컬럼의 데이터로

update 하시오

 

merge into emp e

using dept d

on(e.deptno = d.deptno)

when matched then

update set e.emp_loc = d.loc ;

 

세상에나

제약 안 걸어도 된다.........................

 

※설명

merge into emp e     -----> emp 테이블에 merge 하겠다

using dept d     --------------->조인해라 dept테이블이랑.

on(e.deptno = d.deptno)---> on 절에다가

                                 deptno 연결고리 해주고.

when matched then---------> when 매치 then

update --------------------------->update(변경) 해라

                                       (*이때 테이블 안씀)

 set e.emp_loc = d.loc ; -----> set 얻고자 하는 값

 

merge 참 많이 씁니다.

공공기관 같은 경우 제약 못 걸게 한다.

(3번째 책에 있다. 아직 안받았음)

 

 

문제327.

emp 테이블에 dname 컬럼을 추가하고

dept 테이블의 dname 데이터로 merge 하시오

 

alter table emp

 add dname varchar2(20);

 

merge into emp e

        using  dept d

       on(e.deptno = d.deptno)

when matched then

update set e.dname = d.dname;

 

 

문제328.

emp 테이블과 salgrade 테이블을 조인해서

이름과 월급과 grade(급여등급) 를 출력하시오

 

select e.ename, e.sal, s.grade

from emp e, salgrade s

where e.sal between losal

                      and

                                      hisal;

 

 

문제329.

emp 테이블에 grade 컬럼을 추가하시오

alter table emp

add grade number(10);

 

 

문제330.

emp 테이블의 grade 컬럼을

salgrade 테이블의 grade 컬럼의 데이터를

변경하시오

 

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;

 

 

문제331.

부서번호, 부서번호별 토탈월급을 출력하시오

 

select deptno, sum(sal)

from emp

group by deptno;

 

 

문제332.

부서테이블에 sumsal 이라는 컬럼을 추가하시오

(함수주의)

alter table dept

 add sumsal number(10);

 

select * from dept;

 

 

문제333.

부서테이블에 sumsal 컬럼에

해당 부서번호의 토탈월급으로 값을

갱신하시오

(merge 문 사용)

 

답

merge into dept d

 using (select deptno, sum(sal) as S

        from emp

        group by deptno) tmp

on(d.deptno = tmp.deptno)

when matched then

update set d.sumsal = tmp.S;

 

 

 

문제334.

부서테이블에 deptno_cnt 라는 컬럼을 추가하시오

alter table dept

add deptno_cnt number(10);

 

 

 

문제335.

부서번호, 부서번호별 인원수를 출력하시오

select deptno, count(*)

from emp

group by deptno;

 

 

 

문제336.

부서 테이블에 deptno_cnt 라는 컬럼에

해당 부서번호의 인원수로 값을 갱신하시오

 

merge into dept d

 using (select deptno, count(*) as C

             from emp

            group by deptno) DC

on(d.deptno = DC.deptno)

when matched then

update set d.deptno_cnt = DC.C;

 

 

■ DML 문 4가지

1. insert

2. update

3. delete

4. merge