[SQL 쿡북] Chapter 4 : 삽입, 갱신, 삭제

4.1 새로운 레코드 삽입하기 

테이블에 새로운 레코드를 삽입하려고 한다. 

insert into dept (deptno, dname, loc)
values (1, 'A', 'B'),
	   (2, 'B', 'C')

이렇게 한번에 여러 행을 삽입할 수도 있고, 간단한 테이블에서는 열 목록을 생략하여 select * 쿼리에 대한 열 표시 순서대로 값을 제공할 수 도 있다. 모든 열에 값을 주지 않으면, null인 행이 생성될수 있으므로 유의해야 한다.

 

4.2 기본값 삽입하기 

테이블을 생성할 때 기본값 행을 삽입할 수 있다. 

create table D (id integer default 0)

id 열만 default 값으로 행 하나를 넣어주려면 다음과 같이 한다. 

insert into D (id) values (default)

select * from D

MYSQL

모든 열에 기본값이 정의된 경우 비어 있는 값 목록을 지정할 수 있고, 기본값으로 설정된다. 

insert into D values()

PostgreSQL, SQL Server

insert into D default values

 

기본값이 있는 열과 없는 열이 혼합되어 있을 때 결과는 다음과 같다. 

create table D (id integer default 0, foo varchar(10));
insert into D(id) values (default);
insert into D default values;
insert into D(foo) values('Bar');

select * from D

 

4.3 null로 기본값 오버라이딩하기

기본값을 오버라이딩하여 null 값을 삽입할 수 있다. 마지막 줄처럼 열에 대한 값을 지정하지 않으면, 기본값이 들어간다. null 방지하고자 제약조건이 있지 않은 이상 기본값이 있어도 null을 삽입할 수 있다는 뜻이다.

create table D (id integer default 0, foo VARCHAR(10));
insert into D (id, foo) values (null, 'Brighten');
insert into d (foo) values ('Brighten');

 

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

dept 테이블에서 dept_east 테이블로 행을 복사하려고 한다. 같은 구조로 생성되어 있으며 비어있는 경우이다. 

CREATE TABLE dept_east
    ("deptno" int, "dname" varchar(10), "loc" varchar(8))
;

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

select * from dept_east

INSERT문에 원하는 행을 반환하는 쿼리와 함께 이어쓰면 된다. 단, select 목록의 값 순서에 유의해야 한다. 

 

4.5 테이블 정의 복사하기

기존 테이블과 같은 열 집합을 가진 새 테이블을 만드려고 한다. 레코드 (행)은 복사하지 않고 구조만 복사하는 것이다. 

 

DB2

create table dept_2 like dept

 

Oracle, MySQL, PostgreSQL

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

행을 반환하지 않도록 서브쿼리를 사용하는 것이 포인트다.

 

SQL Server

select *
	into dept_2
	from dept
where 1 = 0

 

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

SQL Server

INSERT ALL 또는 INSERT FIRST 문을 사용한다. 쿼리에서 반환된 행을 가져와서 여래 대상 테이블에 조건에 따라서 삽입하는 과정이다. 

INSERT ALL은 조건이 참으로 평가되더라도 모든 조건을 평가하고, INSERT FIRST는 평가에서 빠져나온다. 따라서 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 (deptnom dname, loc) values (deptno, dname, loc)
	else
		intp dept_west (deptnom dname, loc) values (deptno, dname, loc)
	select deptno, dname, loc
		from dept

DB2

삽입할 테이블에 제약조건을 두고, UNION ALL을 이용하여 정의 된 뷰에 삽입한다. 

create table dept_east
( dept integer,
  dname varchar(10),
  loc varchar(10) check (loc in ('NEW YORK', 'BOSTON')))

create table dept_mid
( dept integer,
  dname varchar(10),
  loc varchar(10) check (loc = 'CHICAGO'))

create table dept_west
( dept integer,
  dname varchar(10),
  loc varchar(10) check (loc = 'DALLAS'))

insert into
(
 select * from dept_west union all
 select * from dept_east union all
 select * from dept_mid
)select * from dept

 

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

삽입할 열만 노출되는 뷰를 만든후에, 뷰에 삽입하면 데이터베이스 서버는 삽입 내용을 기본 테이블로 변환한다. 

create view new_emps as
select empno, ename, job
	from emp

insert into new_emps
	(empno, ename, job)
values (1, 'Jonathan', 'Editor')

이를 이용하면, 사용자들에게 emp 테이블 삽입 액세스 권한은 주지않고, 뷰에 대한 엑세스 권한만 부여한뒤에, 뷰에 정의 된 열에 대한 값만 제공하게 하여 특정 열만 삽입할 수 있게 하는 것이다. 

 

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

update emp
  set sal = sal*1.10
 where deptno = 20

위와 같이 update문을 이용하여, 기존 행을 수정할 수 있다. 

 

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

select empno, ename
	from emp_bonus

update 문의 where 절에서 서브쿼리를 사용하여 다른 테이블의 사원 정보를 찾는다. IN 또는 EXISTS를 사용할 수 있다. 

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

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

 

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

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

//DB2, Oracle
update emp e set (e.sal, e.comm) = (select ns.sal, ns.sal/2
										from new_sal ns
									where ns.deptno = e.deptno)
where exists (select * 
				from new_sal ns 
			where ns.deptno = e.deptno)

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

 

4.11 레코드 병합하기

EMP_COMMISSION 테이블에 일치하는지에 따라 update 또는 insert를 시행하려고 한다. 만약 update 결과 때문에 더 높아진 커미션이 있으면 delete를 실행하려고 한다. 

merge into emp_commission emp_commission ec
using (select * from emp ) emp
	on (ec.empno = emp.empno)
 when matched then
 	  update set ec.comm = 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)

 

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

delete from emp

 

4.13 특정 레코드 삭제하기 

delete from emp where deptno = 10

 

4.14 단일 레코드 삭제하기 

delete from emp where emptno = 7782

 

4.15 참조 무결성 위반 삭제하기 

delete from emp
 where not exists (
 	select * from dept
 	 where dept.deptno = emp.deptno 
 )


delete from emp
 where deptno not in (select deptno from dept)

 

4.16 중복 레코드 삭제하기 

MIN과 같은 집계 함수를 이용하여 남겨둘 행을 임의로 선택하여 중복 레코드를 삭제할 수 있다. 다음과 같은 경우는 ID값이 가장 작은 NAME 열만 삭제되지 않는다.

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

MYSQL의 경우 삭제 시, 같은 테이블을 두번 참조 할 수 없어 아래와 같은 구문을 사용한다.

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

 

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

서브쿼리와 집계함수 COUNT를 사용하여 세 번 이상 사고가 발생한 부서를 찾아 해당 부서에 일하는 모든 사원을 삭제한다.

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