[SQL] 8. 데이터 조작(DML)

DML(Data Manipulation Language)

DML 문장 실행 상황

- 테이블에 새로운 행 추가

- 테이블에 있는 기존의 행을 변경

- 테이블로부터 기존의 행 제거

 

DML 종류

- INSERT

- UPDATE

- DELETE

 

예시

- 은행 DB. 저축성 예금 -> 당좌예금으로 전달

- 저축성 예금 감소

- 당좌 예금 증가

- 트랜잭션 일지에 트랜잭션 기록

=> 하나라도 실패시 모두 실패해야함.

=> 트랜잭션 보장 필요

 

CTAS(CREATE TABLE AS SELECT)

CREATE TABLE table AS SELECT statment

현재 있는 테이블 statement와 똑같은 구조를 갖는 tale 생

 

CREATE TABLE emp1 AS SELECT * FROM employees;
SELECT count(*) from emp1;

create table emp2 as select * from employees where 1=2;
select count(*) from emp2;

 

INSERT

INSERT INtO ( col1, col2,...)
VALUES (val1, val2, ...)

- col과 val은 순서를 맞춰야 함.

- 열 이름이 생략될 경우 모든 값을 다 넣어야 함.

- 값을 넣고 싶지 않은 부분은 null을 넣음

1. 테이블 구조 확인

DESC tablename;

2. 새로운 행 삽입

- 각각의 열에 대한 값을 포함하는 새로운 행 삽입

- 테이블에 있는 열의 티폴트 순서로 값 나열

- INSERT 절에서 열을 선택적으로 나열

- 문자와 날짜 값은 단일 따옴표 내에 둠.

INSERT INTO departments
VALUES(280, 'Data Analytics', null, 1700);

=>실행을 취소하려면 ROLLBACK; 사용(커밋했을 때로)

3. 다른 테이블로부터 행 복사

- 서브쿼리로 INSERT 문장 작성

- VALUES 절 사용X

- 서브쿼리의 열 수와 INSERT절의 열 수는 일치해야 함.

INSERT INTO table(col1, col2, ...)
select_sub_query;

CREATE TABLE MANAGERS As
select * from employees where 1=2;

insert into managers
select * from employees where job_id like '%MAN';

 

UPDATE

- update 문장으로 기존의 행 갱신

- 하나 이상의 행을 갱신 가능

create table emps as select * from employees;

alter table emps
add(constraint emps_emp_id_ppk primary key(employee_id),
constraint emps_manager_id_fk foreign key(manager_id)
references emps(employee_id));

1. 테이블 행 갱신

select employee_id, first_name, salary
from emps
where employee_id=103;
update emps
set salary=salary*1.1
where employee_id=103;

 2. 서브쿼리로 다중 열 갱신

UPDATE table
SET (col1, col2, ...) =
(SELECT col1, col2, ...
FROM table
WHERE condition)
WHERE condition;

update emps
set(job_id, salary, manager_id) =
(select job_id, salary, manager_id
from emps
where employee_id = 108)
where employee_id=109;

 

DELETE

DELETE [FROM] table
[WHERE condition];

1. 행 삭제

delete from emps
where employee_id = 104;

delete from emps
where emplyee_id=103;
-- 104번 사원의 매니저는 103이므로 제약조건때문에 삭제되지 않음.

2. 다른 테이블을 이용한 행 삭제

create table depts as
select * from departments;

delete from emps
where department_id=
(select department_id
from depts
where department_name='Shipping');

--45개 행 이(가) 삭제되었습니다.

3. RETURNING

- DML 조작 시 특정 값을 임시로 저장할 수 잇음

- Oracle 10g에 추가

variable emp_name varchar2(50);
variable emp_sal number;
variable;

delete emps
where employee_id=109
returning first_name, salary into :emp_name, :emp_sal;

select * from emps where employee_id=109;

print emp_name;
print emp_sal;

 

MERGE

- INSERT 문과 UPDATE 문을 동시에 사용할 수 있습니다.

- Oracle 9i에 추가되었습니다.

MERGE INTO table[alias]
	USING (target | view | subquery) [alias]
		ON(join_condition)
	WHEN MATCHED THEN
		UPDATE SET col1=val1
	WHEN NOT MATCHED THEN
		INSERT(column_lists) VALUES(value_lists);

create table emps_it as select * from employees where 1=2;

merge into emps_it a
    using (select * from employees where job_id='IT_PROG') b
    on (a.employee_id = b.employee_id)
when matched then
    update set
        a.phone_number =b.phone_number,
        a.hire_date=hire_date,
        a.job_id = b.job_id,
        a.salary=b.salary,
        a.commission_pct = b.commission_pct,
        a.manager_id = b.manager_id,
        a.department_id = b.department_id
when not matched then
insert values
(b.employee_id, b.first_name, b.last_name, b.email,
b.phone_number, b.hire_date, b.job_id, b.salary,
b.commission_pct, b.manager_id, b.department_id);

 

Multiple INSERT

INSERT [ALL | FISRT]
[[WHEN condition1 THEN]
	INTO [table1] VALUES(col1, col2, ...)]
[[WHEN condition2 THEN]
	INTO [table2] VALUES(col1, col2, ...)]   
[ELSE
	INTO [table3] VALUES (col1, col2)]

1. UNCONDITIONAL INSERT ALL

- 조건과 상관없이 기술된 여러 개의 테이블에 데이터 입력

insert all
into emp1
values (300, 'Kildong', 'Hong', 'KHONG', '011.624.7902',
TO_DATE('2015-05-11','yyyy-mm-dd'), 'IT_PROG', 6000, null, 100, 90)
into emp2
values (400, 'Kilseo', 'Hong', 'KSHONG', '011.324.7952',
TO_DATE('2015-06-20','yyyy-mm-dd'), 'IT_PROG', 5500, null, 100, 90)
SELECT * FROM dual;

create table emp_salary as
select employee_id, first_name, salary, commission_pct 
from employees
where 1=2;

create table emp_hire_date as
select employee_id, first_name, hire_Date, department_id
from employees
where 1=2;

insert all
into emp_salary values
(employee_id, first_name, salary, commission_pct)
into emp_hire_date values
(employee_id, first_name, hire_date, department_id)
select * from employees;

--218개 행 이(가) 삽입되었습니다.

 

2. CONDITIONAL INSERT ALL

- 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 난어 삽입

create table emp_10 as select * from employees where 1=2;
create table emp_20 as select * from employees where 1=2;

insert all
when department_id=10 then
into emp_10 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
when department_id=20 then
into emp_20 values
(employee_id, first_name, last_name, email, phone_number,
hire_date, job_id, salary, commission_pct, manager_id, department_id)
select * from employees;
--3개 행 이(가) 삽입되었습니다.

select * from emp_10;
select * from emp_20;

 

3. CONDITIONAL INSERT FIRST

- 첫번째 when 절에서 조건을 만족하는 경우 다음의 when절은 수행하지 않음.

create table emp_sal5000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sa10000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal15000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal20000 as
select employee_id, first_name, salary from employees where 1=2;
create table emp_sal25000 as
select employee_id, first_name, salary from employees where 1=2;

insert first
when salary <=5000 then
into emp_sal5000 values(employee_id, first_name, salary)
when salary <=10000 then
into emp_sal10000 values(employee_id, first_name, salary)
when salary <=15000 then
into emp_sal15000 values(employee_id, first_name, salary)
when salary <=20000 then
into emp_sal20000 values(employee_id, first_name, salary)
when salary <=25000 then
into emp_sal25000 values(employee_id, first_name, salary)
select employee_id, first_name, salary from employees;
--109개 행 이(가) 삽입되었습니다.

select count(*) from emp_sal5000; --49
select count(*) from emp_sal10000; --45
select count(*) from emp_sal15000; --12
select count(*) from emp_sal20000; --2
select count(*) from emp_sal25000; --1

 

4. PIVOTING INSERT

- 여러개의 into절을 사용하지만 그 뒤에 오는 테이블은 모두 동일

- 비관계형DB를 관계형DB로 바꿀 때 사용

insert all
into sales_log
values(employee_id, week_id, 'SALES_MON', sales_mon)
into sales_log
values(employee_id, week_id, 'SALES_TUE', sales_tue)
into sales_log
values(employee_id, week_id, 'SALES_WED', sales_wed)
into sales_log
values(employee_id, week_id, 'SALES_THU', sales_thu)
into sales_log
values(employee_id, week_id, 'SALES_FRI', sales_fri)
select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
from sales;
--10개 행 이(가) 삽입되었습니다.