22. 테이블의 내용 추가, 수정, 삭제 : DML(2)

[ dml ]

1. insert
2. update

** 문법
단일 컬럼 수정)
update 테이블명
   set 수정할컬럼 = 수정할값         -- 서브쿼리 사용 가능
 where 수정할조건;                        -- 서브쿼리 사용 가능

 

 

다중 컬럼 수정)
update 테이블명
   set 수정할컬럼1 = 수정할값1, 수정할컬럼2 = 수정할값2, ...
 where 수정할조건;              

update 테이블명
   set (수정할컬럼1, 수정할컬럼2) = (select 수정할값1, 수정할값2
                               from 테이블명
                                ...)
 where 수정할조건;              

 

* 주의
수정할값은 반드시 각 행별로 하나의 값으로 정의되어야 함

 

예제) emp_2를 emp와 동일하게 생성 후
    전체 직원의 급여를 각 부서의 평균급여로 수정

sol)

create table emp_2 as select * from emp;

update emp_2 e1
   set sal = (select round(avg(sal))
                from emp_2 e2
               where e2.deptno = e1.deptno);
               
select * from emp_2;
commit;


예제) emp_3 테이블 생성 후 평균급여보다 낮은 급여를 받는 직원의 급여를 평균급여로 수정

sol)

create table emp_3 as select * from emp;

update emp_3
   set sal = (select avg(sal) from emp_3)
 where sal < (select avg(sal) from emp_3);
 
select * from emp_3;
commit;


예제) emp_4 생성 후 allen의 급여를 원래 급여의 10% 인상된 값으로(정수반올림),
     comm은 기존값에 500을 더한 값으로 동시 수정

sol)

create table emp_4 as select * from emp;

 

잘못된 풀이:
update emp_4
   set (sal, comm) = (round(sal*1.1), comm + 500)    -- error
 where ename = 'ALLEN';

정상 풀이:
update emp_4
   set sal = round(sal*1.1), comm = comm + 500
 where ename = 'ALLEN';

select * from emp_4;
commit;


** 참고
update emp_4
   set (sal, comm) = (300, 500)         -- error(update ... set 표현식은 하위 질의여야 합니다)
 where ename = 'ALLEN';
 
update emp_4
   set (sal, comm) = (select 300, 500
                        from dual)      -- 정상
 where ename = 'ALLEN';
 

예제) emp_4 테이블에서 MANAGER 직급의 직원들의 급여와 comm을
     전체직원 중 최대급여와 최대comm으로 동시수정

sol)
update emp_4
   set (sal, comm) = (select max(sal), max(comm)
                        from emp_4)
 where job = 'MANAGER';

select * from emp_4;
commit;


3. delete 
- 행 단위 삭제 언어

** 문법
delete [from] 테이블명
 where 삭제할 조건;         -- where절 생략 시 전체 행 삭제
                                         -- 서브쿼리 사용 가능
                        

예제) 전체 행 삭제                        
select * from emp_4;

 

delete emp_4;
select * from emp_4;    -- 0건 출력
 

rollback;
select * from emp_4;    -- 다시 복원됨

 

예제) 급여가 3000 미만인 직원 정보 삭제

sol)
delete emp_4
 where sal < 3000;
commit;

예제) emp_3에서 급여가 전체 평균 급여보다 낮은 급여를 받는 직원 삭제

sol)
select * from emp_3;

delete emp_3
 where sal < (select avg(sal) 
                from emp_3);

commit;                


예제) student_3 테이블 생성 후 학년별로 평균키보다 작은키를 갖는 친구 삭제

sol)

create table student_3 as select * from student;

delete student_3 s1
 where height < (select avg(height)
                   from student_3
                  where grade = s1.grade);
                  
select * from student_3;
commit;



** 중요 : drop / truncate / delete의 차이
- drop은 구조 삭제, truncate와 delete는 데이터만 삭제
- drop은 recyclebin으로 복원 가능, delete는 삭제 정보를 undo에 기록, rollback하여 원복 가능
  truncate는 삭제 정보를 어디에도 기록하지 않기 때문에 복원 불가
- drop, truncate은 빠른 속도로 처리 가능, delete는 데이터가 클수록 매우 느림
- drop, delete는 저장공간 즉시 반환하지 X, truncate만 저장공간 즉시 반환



4. merge
- 병합문
- 특정 테이블의 데이터와 같은 데이터로 만드는 작업

** 문법
merge into 수정할테이블명 old
using 참조테이블명 new
   on (연결조건)                      -- 괄호 필수
 when matched then  
      update 
         set col1 = new.col1
      delete (삭제조건)               -- 괄호 생략 가능
 when not matched then  
      insert values(new.col1, new.col2, ....)
;


예제) 아래 테이블 생성 후 menu_old를 menu_new에 맞게 변경(merge)
drop table menu_old purge;
create table menu_old(
no      number,
name    varchar2(10),
price   number);

create table menu_new(
no      number,
name    varchar2(10),
price   number);

insert into menu_old values(1,'아메리카노',1000);
insert into menu_old values(2,'라떼',2000);

insert into menu_new values(1,'아메리카노',1500);
insert into menu_new values(2,'라떼',3000);
insert into menu_new values(3,'모카',4000);
commit;


select * from menu_old;
select * from menu_new;

merge into menu_old m1
using menu_new m2
   on (m1.no = m2.no)
 when matched then
      update
         set m1.price = m2.price
 when not matched then        
      insert values(m2.no, m2.name, m2.price);
      
commit;



[ 제약조건 ]
- 데이터 무결성을 위해 만드는 객체(올바른 데이터가 입력/수정되도록 관리하기 위한 객체)
- 독립적으로 생성 불가(테이블의 각 컬럼별로 정의)

1. primary key(pk, 기본키)
- 테이블에서의 고유 식별자를 지정하는 제약조건
   예) 학생 테이블의 학번, 사원 테이블의 사번, 게시글 테이블에 게시글번호 등
- 중복 불가, null 허용 X => unique + not null
- 한 테이블에 기본키는 반드시 한 개만 허용
- pk를 구성하는 컬럼은 여러개일 수 있음

예) PK를 구성하는 컬럼이 여러개인 경우
[구매 테이블] : PK(구매번호 + 상품번호)

구매번호 상품번호  수량  주문날짜
1000   1      2  2024-01-01
1000   2      1  2024-01-01
1000   3      1  2024-01-01
1001   1      2  2024-01-02
1001   2      1  2024-01-02

2. unique(고유키)
- 중복을 허용하지 않는 제약조건
  예) 전화번호, 주민번호
- null을 허용** 
   
3. not null
- null을 허용하지 않는 속성
- 다른 제약조건과는 다르게 컬럼의 속성을 변경하여 not null 설정 가능(제약조건 추가가 아님)
    
4. check
- 컬럼값의 범위를 정하고 싶을때 사용

5. foreign key(fk, 외래키)
- 참조테이블의 참조키(reference key)의 값을 참조하는 제약조건
- 부모-자식 관계 성립
- 자식쪽에 외래키 생성, 이에 대응하는 부모쪽에 참조키 생성
- foreign key 생성 전제 조건은 참조테이블의 참조키에 반드시 기본키 또는 고유키 생성되어 있어야 함!!!!

                 자식                               부모
                <emp>                            <dept>
       empno ename .... deptno          deptno    dname    loc
                     (foreign key) (reference key)



     * 자식쪽 제약 : insert, update 제한
     * 부모쪽 제약 : delete, update 제한
        



[ 제약조건 생성 ]
1. 테이블 생성 시 정의

 

* 문법1
create table 테이블명(
컬럼1     데이터타입   [default 값] [제약조건],
컬럼2     데이터타입   [default 값] [제약조건],
...
);

* 문법2
create table 테이블명(
컬럼1     데이터타입   [default 값] [constraint 제약조건명 제약조건],
컬럼2     데이터타입   [default 값] [constraint 제약조건명 제약조건],
...
);

* 문법3
create table 테이블명(
컬럼1     데이터타입   [default 값] ,
컬럼2     데이터타입   [default 값] ,
[constraint 제약조건명] 제약조건(컬럼1, 컬럼2, ..),
[constraint 제약조건명] 제약조건(컬럼1, 컬럼2, ..),
...
);


예제) emp_test100과 dept_test100 테이블 생성
drop table dept_test100 purge;

create table dept_test100(
deptno      number          primary key,
dname       varchar2(10)    unique,    
loc         varchar2(10)    not null);

 

create table emp_test100(
empno       number          primary key,                       -- pk
ename       varchar2(10)    not null,                          -- nn
jumin       char(13)        unique,                            -- uk
hiredate    date            default sysdate not null,          -- nn
sal         number          check(sal > 0),                    -- check
deptno      number          references dept_test100(deptno));  -- fk



예제) emp_test100과 dept_test100 테이블에 각 3건씩 데이터 입력
insert into dept_test100 values(10, '인사팀', '서울');
insert into dept_test100 values(20, '총무팀', '서울');
insert into dept_test100 values(30, 'IT팀', '과천');
commit;

 

insert into emp_test100 values(1000, '홍길동', '1111111111111',sysdate,5000,10);
insert into emp_test100 values(1000, '홍길동', '1111111111111',sysdate,5000,10); -- error
 -> 제약조건 이름이 없는 경우 어떤 문제로 데이터 삽입 오류가 발생했는지 파악하기 어려움

 

 

예제) emp_test100 테이블 재생성(제약조건마다 이름 갖도록 설정)
drop table emp_test100 purge;

create table emp_test100(
empno       number          constraint empt100_empno_pk primary key, 
ename       varchar2(10)    constraint empt100_ename_nn not null, 
jumin       char(13)        constraint empt100_jumin_uk unique,   
hiredate    date            default sysdate not null,    
sal         number          check(sal > 0),              
deptno      number          constraint empt100_deptno_fk references dept_test100(deptno));  

insert into emp_test100 values(1000, '홍길동', '1111111111111',sysdate,5000,10);
insert into emp_test100 values(1000, '홍길동', '1111111111111',sysdate,5000,10); -- error
 -> 제약조건 이름이 있는 경우 어떤 문제로 데이터 삽입 오류가 발생했는지 파악하기 쉬움(empno 컬럼에 pk 때문)



예제) col1 ~ 5를 갖는 test100 테이블 생성, col1, col2, col3에 pk 설정
create table test100(
col1    number      primary key,
col2    number      primary key,  -- error(테이블에는 하나의 기본 키만 가질 수 있습니다)
col3    number      primary key,
col4    number,
col5    number);

create table test100(
col1    number,
col2    number,
col3    number,
col4    number,
col5    number,
constraint test100_col1_col2_col3_pk primary key(col1, col2, col3));  -- 정상

2. 생성
1) 테이블 생성 시 정의 가능
create table table1
(
  col1    number  [default 값] [constraint 제약조건명] primary key,
  col2    date    not null
);


2) 이미 생성된 테이블에 제약조건 추가
alter table 테이블명 add [constraint 제약조건명] 제약조건종류(컬럼1, ...);

예)
alter table 테이블명 add col1 number;                   -- 컬럼 추가

 

pk 추가)
alter table 테이블명 add [constraint 제약조건명] primary key(컬럼1, 컬럼2, ....); 

uk 추가)
alter table 테이블명 add [constraint 제약조건명] unique(컬럼); 

check 추가)
alter table 테이블명 add [constraint 제약조건명] check(sal > 0); 

fk 추가)
alter table 테이블명 add [constraint 제약조건명] foreign key(자식컬럼) references 부모테이블(부모컬럼); 

nn 추가)
alter table 테이블명 modify 컬럼 not null;


[ 연습문제 ]
emp와 dept와 동일한 테이블 emp_cons1, dept_cons1을 생성 후 각각 필요한 제약조건 추가
create table emp_cons1
(
    empno       number(4)       ,   --pk
    ename       varchar2(10),       --not null
    job         varchar2(9),
    mgr         number(4),
    hiredate    date,
    sal         number(7,2),
    comm        number(7,2),
    deptno      number(2)            --fk
);

create table scott.dept_cons1
(
    deptno  number(2),               --pk
    dname   varchar2(14),            --uk
    loc     varchar2(13)
);

alter table emp_cons1 add constraint emp_cons1_empno_pk primary key(empno);
alter table emp_cons1 modify ename not null;
alter table emp_cons1 add constraint emp_cons1_deptno_fk 
                      foreign key(deptno) references dept_cons1(deptno); -- error
                      
alter table dept_cons1 add constraint dept_cons1_deptno_pk primary key(deptno);
alter table emp_cons1 add constraint emp_cons1_deptno_fk 
                      foreign key(deptno) references dept_cons1(deptno); -- 정상
alter table dept_cons1 add constraint dept_cons1_dname_uk unique(dname);


[ 외래키 제약 테스트 ]
insert into dept_cons1 select * from dept;
insert into emp_cons1 select * from emp;
commit;

자식 테이블 제약)
desc emp_cons1;
insert into emp_cons1(empno, ename, deptno) values(9999,'홍길동',50);  -- 에러(insert 제한)

update emp_cons1
   set deptno = 50
 where deptno = 10;  -- 에러(update 제한)

delete emp_cons1;    -- 정상(delete는 제한없음)
rollback;

부모 테이블 제약)
insert into dept_cons1 values(50, 'IT부', '서울');   -- 정상(insert 제한없음)
update dept_cons1
   set deptno = 50
 where deptno = 10;  -- 에러(update 제한)
 
delete dept_cons1
 where deptno = 10;  -- 에러(delete 제한)



4. 삭제
alter table emp_cons1 drop constraint 제약조건명 [CASCADE];


예제) BOARD에 있는 제약조건이름 확인 후 pk 제약조건 삭제
select *
  from user_constraints
 where table_name in ('BOARD','MEMBER3');

alter table member3 drop constraint member3_userid_pk;  -- error
 -> 자식 테이블의 foreign key가 부모 테이블의 reference key를 참조하고 있기 때문에
    reference key에 걸린 제약조건을 삭제할 수 없음
   
alter table member3 drop constraint member3_userid_pk cascade;  -- 정상

select *
  from user_constraints
 where table_name in ('BOARD','MEMBER3');
 -> BOARD 테이블에 있는 foreign key도 동시에 삭제됨을 확인!

 
5. 조회 **
1) 제약조건 조회
select table_name,         -- 테이블이름
       constraint_name,    -- 제약조건이름
       constraint_type,    -- 제약조건종류(P:Primary key, U:Unique, C:Check/Not null, R:Foreign key)
       search_condition,   -- not null 여부 확인
       r_constraint_name   -- reference key 제약조건명*
  from user_constraints
 where table_name in ('EMP','DEPT');      

2) 제약조건 구성 컬럼 조회
select table_name,         -- 테이블명
       constraint_name,    -- 제약조건명
       column_name,        -- 컬럼명 
       position            -- 컬럼순서(제약조건 구성 컬럼이 여러개일 경우 중요)
  from user_cons_columns
 where table_name in ('EMP','DEPT');

 

 

[ foreign key 옵션 ]
- foreign key 생성 시 옵션 정의 가능

** 옵션)
1) on delete cascade : 부모 데이터 삭제 시 자식 데이터 동시에 삭제하는 옵션
2) on delete set null : 부모 데이터 삭제 시 자식 데이터를 null로 수정하는 옵션


예제) dept_cons1 데이터 삭제 시도
1) 제약조건 조회
select *
  from user_constraints
 where table_name in ('DEPT_CONS1', 'EMP_CONS1')
 order by table_name;

2) dept_cons1 데이터 삭제 시도
delete from dept_cons1
 where deptno = 10;      -- error(자식 레코드가 발견되었습니다)
 
3) foreign key 재생성(on delete cascade 옵션)
alter table emp_cons1 drop constraint emp_cons1_deptno_fk;
alter table emp_cons1 add constraint emp_cons1_deptno_fk
                      foreign key(deptno) references dept_cons1(deptno) on delete cascade;
                      
                      
4) dept_cons1 데이터 삭제 시도
delete from dept_cons1 where deptno = 10;      -- 정상
select * from dept_cons1;                      -- 삭제됨
select * from emp_cons1;                       -- 10번 부서원 함께 삭제됨
commit;

5) foreign key 재생성(on delete set null 옵션)
alter table emp_cons1 drop constraint emp_cons1_deptno_fk;
alter table emp_cons1 add constraint emp_cons1_deptno_fk
                      foreign key(deptno) references dept_cons1(deptno) on delete set null;
                      
6) dept_cons1 데이터 삭제 시도
delete from dept_cons1 where deptno = 20;      -- 정상
select * from dept_cons1;                      -- 삭제됨
select * from emp_cons1;                       -- 20번 부서원은 삭제되지 않고 deptno만 null로 변경
commit;