240214 DB - DML 2, 트랜잭션

데이터조작어(DML)

1. insert 구문

테이블에 새로운 행 추가

insert into 테이블명[(컬럼명1, 컬럼명2, ....)]
value (값1,값2,...);

 

※desc 테이블명 => 컬럼 만든 순서

 

-- 컬럼 리스트 생략 시 기본 컬럼 순서대로 값을 넣어줘야함
insert into departments
values(280, 'Java', 107, 1700);

-- 컬럼 리스트의 순서가 기억 나지 않을 때 컬럼명과 값을 짝을 맞춰서 넣어줘야함
insert into departments(department_name, location_id, manager_id, department_id)
values('Jsp', 1700, 108, 290);

-- null값 삽입 1(자동 null값 삽입)
insert into departments(department_id, department_name)
values(300, 'Mysql');

-- null값 삽입 2
insert into departments
valuse (310, 'Oracle', null, null);

 

※오라클은 auto_commit 기능이 기본적으로 권장하지 않기 때문에 꺼져있음.

-- 4번의 insert가 저장됨
commit;

 

※ insert into로 Mysql에서는 한 번에 여러 행(아래 예시)을 삽입할 수 있었지만,

   오라클에서는 불가능. 즉 한 번에 한 행 씩만 가능함. subquery를 사용하면 가능함

-- 실습

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

insert into copy_emp
select * from employees;

commit;

 

2. UPDATE 구문

update 테이블명
set 컬럼 = 값, [컬럼= 값,...]
[where 조건]

 

update employees
set department_id = 50
where employee_id = 113;

 

-- update구문에서 where없이 사용하면 컬럼의 값이 모두 변경됨
update copy_emp
set department_id = 110;

-- 되돌리기
rollback;

-- 모든 행을 작업해야하는 경우 예시(where절이 필요없음)
update copy_emp
set salary = salary * 1.1;

update members
set point = point + 10000;

update student
set grade = grade + 1;

 

-- subquery 사용된 update
update copy_emp
set job_id = (select job_id from copy_emp where employee_id = 205),
	salary = (select salary from copy_emp where employee_id = 205)
where employee_id = 113;

commit;

 

 

3. DELETE 구문

 

※행을 삽입 insert

특정 데이터를 수정 update

특정 행을 삭제 delete

 

delete [from] 테이블명
[where 조건]

 

where절이 없으면 모든 행을 삭제!

자식이 있는 부모행이 먼저 삭제 될 수 없음

delete from departments
where department_id = 260;

 

-- 테이블 안의 행을 모두 삭제
delete from copy_emp;

rollback;

---

트랜잭션

• 하나의 논리적인 작업 단위. 화면에 따로 나타나지 않음. 정해진 규모도 없음.
  판단을 어떻게 할까? dml 처음 시작 시 트랜잭션이 시작됨. commit이나 rollback하는 순간 트랜잭션이 종료,
• 여러 DML이 모여 하나의 트랜잭션이 구성됨(insert, update, delete)
• 하나의 DDL 구문이 하나의 트랜잭션을 구성함(create, alter, drop, truncate)
• 하나의 DCL 구문이 하나의 트랜잭션을 구성함(grant, revoke)
• DML 작업 후 commit, rollback으로 트랜잭션을 종료해야함

트랜잭션 제어 명령어(TCL)

• commit : 트랜잭션을 영구히 저장
• rollback : 트랜잭션 처음으로 되돌림
• savepoint : 트랜잭션 진행 중 되돌아갈 지점을 생성함. 부분 취소가 가능

트랜잭션 시작 및 종료

• 시작 : 첫 번째 DML 구문 실행 시
• 종료 : commit 또는 rollback 실행 시, ddl 또는 dcl 구문 실행 시(autocommit), sql plue 정상 종료 시(autocommit),
            시스템 장애 시(autorollback)

※ dml + ddl또는 dcl => autocommit 발생

그림 1.

update copy_emp
set salary = 28000
where employee_id = 100;
-- DML 시작, 트랜잭션 시작

select employee_id, salary
from copy_emp
where employee_id = 100;

savapoint sp1;

update copy_emp
set salary = 24000
where employee_id = 102;

select employee_id salary
from copy_emp
where employee_id = 102;

rollback to sp1;

select employee_id, salary
from copy_emp
where employee_id in (100, 102);

commit
-- 트랜잭션 종료