[DB] 데이터 조작(DML)
<DML>
=데이터 조작
테이블에 새로운 행 추가(INSERT), 행 수정(UPDATE), 행 제거(DELETE) 등..
DML문장의 모음을 트랜잭션이라고 한다.( 트랜잭션=작업의 단위. )
<CTAS>
=Create table as select. 테이블 복제.
NOT NULL제약조건만 복사된다.
Create table 테이블명 as select 테이블생성서브쿼리
예제
테이블 구조, 데이터 생성
create table emp1 as select * from employees;
구조만 있는 테이블 생성
- where 1=2는 항상 FALSE이므로 구조만 가지게 된다.
create table emp2 as select * from employees where 1=2;
<INSERT>
=테이블에 새로운 행을 추가
🍏insert 구조
insert into table [ ( ) ] values ( )
🍏테이블 구조 확인
describe table.
DESC table1;
🍏새로운 행 삽입
ex) departments 테이블에 행 추가하기
--WAY1
insert into departments
values(280,'Data Analytics', null, 1700);-- 순서와 타입이 맞아야한다.
--WAY2
insert into departments(department_id, department_name, location_id)
values(280,'Data Analytics', 1700);
+) 확인하기 : 280번 부서정보가 입력되었는지 확인하기
select * from departments where department_id=280;
+) 삽입 취소
ROLLBACK;
→ INSERT, UODATE,DELETE,MERGE문은 트랜잭션을 COMMIT;으로 종료해줘야지 작업이 적용된다.
우리는 임시로 본 것이지 깨문에 ROLLBACK;으로 작업을 취소하였다.
🍏다른 테이블로부터 행 복사
value절 사용하지 않고 subquery결과를 복사하여 대입
서브쿼리 열 수와 insert 절 열 수 같아야 한다.
구조
insert into table[ ( ) ] select_sub_query;
예제
managers table생성(구조만 있는 테이블 생성)
create table managers as
select employee_id, first_name, job_id, salary, hire_date
from employees
where 1=2;
employees테이블에서 직무가 매니저인 사원들을 Managers테이블에 저장
insert into managers(employee_id, first_name, job_id, salary, hire_date)
select employee_id, first_name, job_id, salary,hire_date
from employees
where job_id like '%MAN';
<UPDATE>
=기존의 하나 이상의 행 갱신
🍏UPDATE 구조
update 테이블명
set 컬럼=값, 컬럼=값, 컬럼=값...
where 조건
where절에서는 단일행 식별하기 위해 primary key를 사용한다.
🍏UPDATE 예제
+) employees 테이블 사본 생성
create table emps as select * from employees;
alter table emps
add
(
constraint emps_emp_id_pk primary key (employee_id),
constraint emps_manager_fk foreign key (manager_id)
references emps(employee_id)
);
테이블 행 갱신 ( 단일 행 갱신 )
ex) 103번 사원 급여를 10% 인상하라
--변경 전 값 확인
select employee_id, first_name, salary
from emps
where employee_id=103;
--변경. 급여 인상
update emps
set salary=salary*1.1
where employee_id=103;
서브쿼리로 다중 열 갱신
ex) Daniel의 직무,급여, 매니자를 108번 사원의 직무, 급여와 매니저 로 변경시켜라
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>
=기존 행 제거
+ 참조 무겨성 제약 조건 주의!
다른 곳에서 참조하고 있으면 삭제할 수 없다.
ex) 103번 사원은 누군가의 manager 이다.
그런데 103번 사원을 지워버리면 참조 무결성 제약 조건이 제거된다.
그렇기에 103번 사원의 경우 무조건 지울 수 없다
DELETE 구조
where 절 안넣으면 모든 행 제거한다.
delete [from] 테이블명
[where 조건];
DELETE 예제
+) employees 임시 사본 생성
create table emps as select * from employees;
alter table emps
add
(
constraint emps_emp_id_pk primary key (employee_id),
constraint emps_manager_fk foreign key (manager_id)
references emps(employee_id)
);
행 삭제
ex) 104번 사원의 정보 삭제
delete from emps where employee_id=104;
서브쿼리로 행 삭제
ex) 부서 이름이 Shipping인 부서의 모든 사원 정보 삭제
delete from emps
where department_id=(select department_id from depts where department_name="Shpping");
RETURNING 절
INSERT, UPDATE, DELETE구문에 영향 받는 행을 검색 (알아두기)
variable emp_name varchar2(50);
variable emp_sal number;
delete emps where employee_=109
returning first_name, salary INTO:emp_name, :emp_sal;
print emp_name;
print emp_sal;
<MERGE>
=두개의 테이블을 합친다
MERGE 구조
MERGE INTO 테이블명
USING
ON join조건
WHERE MATCHED THEN UPDATE SET 컬럼=값, 컬럼=값
WHEN NOT MATCHED THEN INSERT 컬럼리스트 VLUES 값리스트;
on절 조건에 맞으면 UPATE를 돕고, 아니면 insert를 수행한다.
MERGE 예제
+) 임시 테이블 EMPS_IT생성
create table emps_it as select * from employees where 1=2;
insert into emps_it
(employees_id, first_name, last_name, email, hire_date, job_id)
values
(105,'David','Kim','DAVIDKIM','06/03/04'. 'IT_PROG');
ex) emp_it와 employees테이블 병합.
IT_PROG인사원들의 정보를 employees 테이블에서 조회 후 정보가 있다면 갱신하고 없다면 INSERT 수행한다.
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 = b.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);
b에는 새로운 사용자가 있거 기존 사용자가 업데이트 되어있다.
a에는 원래 사용자만 있다. (legacy table=예전에 있던 테이블)
<MULTIPLE INSERT>
=하나의 insert문으로 여러 테이블의 하나의 행 동시에 입력
MULTIPLE INSERT 구조
INSERT [ALL|FIRST]
WHEN 조건 THEN INTO 테이블 VALUES ( )
WHEN 조건 THEN INTO 테이블 VALUES ( )
ELSE INTO 테이블 VALUES ( )
서브쿼리;
Unconditional insert all
=조건 상관 없이 여러개의 테이블에 데이터 입력
서브쿼리에서 한번에 하나의 행 반환받아 각각 insert절 수행
into, values절의 열 개수와 게이터 타입은 같아야 하다. 없을 시 inset
ex) emp1테이블과 emp2테이블에정보 저장
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.3402.7902',
TO_DATE('2015-06-20','YYYY-MM-DD'),'IT_PROG',5500, null,100,90)
SELECT * FROM dual;
ex) EMPLOYEES테이블의 데이터를 열 단위로 나누어 저장하기 예제
예제를 위해 EMP_SALARY, EMP_HIRE_DATE 구조만 생성
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;
데이터를 emp_salary, emp_hire_date 테이블에 나누어 저장
Conditional insert all
=조건에 맞는 행을 원하는 테이블에 나누어 삽입
when, then으로 동등비교하여 테이블 데이터 나눈다.(행 단위)
insert all
when department_id=10 then
into emp_10 values(employee_id, first_name,....)
when departmen_id=20 then
into emp_20 values(employee_id, first_name,....)
select * from employees;
Conditional insert first
when then절이 여러개 있을 때
첫번째 true가 되는 것을 확인한 후에는 뒤에 것을 검사하지 않음
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;
UNPIVOTING INSERT
=여러개의 into절 사용할 수 있지만 into절에 한개의 테이블만 올 수 있다.
비관계형 데이터베이스를 관계형 데이터베이스구조로 만들 떄 사용
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)
.....
select employee_id, week_id, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri
from sales;