[SQL] SQL Cookbook - 04.삽입, 갱신, 삭제

 

1. 새로운 레코드 삽입하기

모든 열을 추가할 때는 열 목록을 생략할 수 있다.

# 단일 행 삽입
insert into dept (deptno, dname, loc)
 values (50, 'PROGRAMING', 'BALTIMORE')
 
 # 여러 행 삽입
insert into dept (deptno, dname, loc)
 values (1, 'A', 'B'),
 		(2, 'B', 'C')

 

2. 기본값 삽입하기

default를 명시한 열은 insert시 값을 지정하지 않으면 디폴트 값으로 저장된다.

# default
create table D (id integer default 0, foo varchar(10))

insert into D (name) values ('Bar')

 

3. null로 기본값 오버라이딩하기

default값을 지정한 열에 null값을 insert하면 디폴트 값으로 저장된다.

create table D (id integer default 0, foo varchar(10))

insert into D (id, foo) values (null, 'Brigthen')

 

4. 한 테이블에서 다른 테이블로 행 복사하기

DEPT 테이블에서 DEPT_EAST 테이블로 행 복사.

(두 테이블은 같은 구조여야 한다)

insert into dept_east (deptno, dname, loc)
 select deptno, dname, loc
	from dept
 where loc in ('NEW YORK', 'BOSTON')

 

5. 테이블 정의 복사하기

행을 반환하지 않는 서브쿼리를 사용하여 CREATE TABLE 문을 사용하면 해당 테이블의 구조가 복사된다.

create table dept_2
as
 select *
	from dept
  where 1 = 0

 

6. 한 번에 여러 테이블 삽입하기

INSERT ALL 구문을 사용하여 다중 테이블에 한번에 값을 삽입할 수 있다. *Oracle만 지원

=> dept테이블에서 deptno, dname, loc를 조회하여 loc값에 따라 dept_east, dept_mid, dept_west 테이블로 각각 insert한다.

# INSERT ALL
insert all
 when loc in ('NEW YORK', 'BOSTON') then
  into dept_east (deptno, dname, loc) values (deptno, dname, loc)
 when loc = 'CHICAGO' then
  into dept_mid (deptno, dname, loc) values (deptno, dname, loc)
 else 
  into dept_west (deptno, dname, loc) values (deptno, dname, loc)
 select deptno, dname, loc
  from dept

 

7. 특정 열에 대한 삽입 차단하기

테이블에서 이와같이 특정 열만 노출되는 view를 만들어 모든 삽입 내용이 해당 뷰를 통과하도록 하면 사용자는 뷰에 있는 세개의 필드만 채울 수 있다.

# view 생성
create view new_emps as
 select empno, ename, job
  from emp
  
# insert
insert into
  (select empno, ename, job
   from emp)
values (1, 'Jonathan', 'Editor')

 

8. 테이블에서 레코드 수정하기 

# 부서 20에 속한 사원들의 급여 10%인상
update emp
  set sal = sal * 1.10
 where deptno = 20

 

9. 일치하는 행이 있을 때 업데이트하기

# IN
update emp
 set sal = sal * 1.20
where empno in (select empno from emp_bonus)

# EXISTS
update emp
  set sal = sal * 1.20
where exists (select null
	    from emp_bonus
           where emp.empno = emp_bonus.empno)

 

10. 다른 테이블 값으로 업데이트하기

# MySQL
update emp e, new_sal ns
  set e.sal = ns.sal,
      e.comm = ns.sal/2
 where e.deptno = ns.deptno
 
# PostgreSQL
update emp
 set sal = ns.sal,
   comm = ns.sal/2
from new_sal ns
where ns.deptno = emp.deptno

 

11. 레코드 병합하기

해당 레코드가 이미 있으면 update, 없다면 insert를 한다.

update 후 sal이 2000보다 작다면 해당 레코드를 delete한다.

(Join 성공했다면 레코드 존재)

# MERGE
merge into emp_commission ec
using (select * from emp) emp
  on (ec.empno = emp.empno)
when matched then
   update set ec.com = 1000
   delete where (sal < 2000)
when not matched then
   insert (ec.empno, ec.ename, ec.deptno, ec.comm)
   values (emp.empno, emp.ename, emp.deptno, emp.comm)

 

12. 테이블에서 모든 레코드 삭제하기

delete from emp

 

13. 특정 레코드 삭제하기

delete from emp where deptno = 10

 

14. 단일 레코드 삭제하기

# 기본키 사용으로 단일행 제거
delete from emp where empno = 7782

 

15. 참조 무결성 위반 삭제하기

emp테이블의 deptno가 참조하는 대상이 dept테이블의 deptno에 없을 때 제거

# NOT IN
delete from emp
where deptno not in (select deptno from dept)

 

16. 중복 레코드 삭제하기

name을 Group으로 묶어 각 name 그룹 중 아이디가 제일 작은 행만 살리고 다 제거

delete from dupes
 where id not in (select min(id)
 		    from dupes
             	  group by name)

 

17. 다른 테이블에서 참조된 레코드 삭제하기

 dept_accidents에 사고 이력이 3개 이상인 사원의 레코드를 제거

delete from emp
 where deptno in (select deptno
 		from dept_accidents
                group by deptno
                having count(*) >= 3)

 

 

 

reference.

[SQL Cookbook] - 한빛미디어