[DB] 데이터 조작(DML)

개발/database

[DB] 데이터 조작(DML)

yun000 2024. 12. 18. 11:13

<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;