[SQL 20] DML(INSERT, UPDATE, DELETE), TRUNCATE

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

▣ DML(Date Manipulation Language)

데이터베이스에 데이터를 입력, 수정, 삭제하는 명령어이다.

데이터베이스의 내용을 변경하는 작업을 수행합으로 주의 깊게 실행해야 한다.

No. 01
INSERT

기본 구조

INSERT INTO 테이블 [(컬럼, 컬럼, …)]

VALUES (값, [값, …]);

• 하나의 행 만을 삽입한다.
• 각 컬럼과 VALUES 절의 값은 반드시 1:1 대응된다.
• 테이블의 모든 컬럼에 값을 입력하는 경우 컬럼 목록을 생략 할 수 있다.
• INSERT문장에서 생략된 컬럼은 널 값이 입력된다.
• 값 대신 DEFAULT를 기술하면 테이블에 정의된 DEFAULT 값이 입력된다.
• 입력되는 값이 숫자가 아닌 경우 단일 인용부호(')를 사용한다.

◈ 예제

insert into departments(department_id, department_name, manager_id, location_id)

values (300, 'Game', 100, 1700);

insert into departments(manager_id, location_id, department_id, department_name)

values (100, 1700, 300, 'Game');

1 행 이(가) 삽입되었습니다.

위 두 예제는 동일한 INSERT문장이다.

컬럼 목록의 순서대로 쓰지 않아도 상관없지만, 컬럼 목록과 VALUES절의 값은 1:1 대응되어야 한다.

commit;

커밋 완료.

select * from departments order by department_id desc;

INSERT 문장이 성공적으로 완료됐음을 볼 수 있다.

물론 COMMIT 하지 않아도 현재 메모리상에서 SELECT 되지만, COMMIT하지 않고 데이터베이스를 껐다가 키면 검색되지 않을 것이다.

▶ 모든 컬럼에 값을 입력하는 경우

insert into departments

values (301, 'Game_1', 100, 1700);

1 행 이(가) 삽입되었습니다.

commit;

커밋 완료.

모든 컬럼에 값을 입력하는 경우 컬럼 목록을 생략 할 수 있다.

VALUES절에 기술하는 값의 순서는 DESC명령으로 확인된 테이블의 컬럼 순서와 일치해야 한다.

desc departments;

▶ 일부 컬럼의 생략

insert into departments(department_id, department_name)

values (302, 'DB_Executive');

1 행 이(가) 삽입되었습니다.

commit;

커밋 완료.

위의 결과를 보기 위해서 검색해보면

생략했던 manager_id, location_id 컬럼의 값이 자동으로 널 값이 입력된 것을 볼 수 있다.

▶ 특별한 값의 삽입

insert into employees(employee_id, last_name, email, hire_date, job_id)

values (207, 'Lee', 'KeepCool', sysdate, 'IT_PROG');

1 행 이(가) 삽입되었습니다.

select * from employees order by department_id desc;

위의 경우에는 hire_date 컬럼에 날짜대신 SYSDATE 함수를 기술하였다. 검색해보면 알겠지만 함수의 기능대로 현재의 날짜가 삽입되었다.

VALUES절의 값 대신 함수를 사용할 수 있다. 단 컬럼의 Type에 맞게 기술해야 한다.

No. 02
UPDATE

기본 구조

UPDATE 테이블

SET 컬럼 = 값 [, 컬럼 = 값, …]

[WHERE 조건];

• 컬럼의 값을 수정한다. (기존의 행의 값 수정)
• 여러 행의 값을 동시에 수정할 수 있다.
• WHERE절을 생략하면 지정된 컬럼의 모든 행의 값이 수정된다.
• 값 대신에 DEFAULT를 기술하면 테이블에 정의된 DEFAULT 값으로 변경된다.

◈ 예제

update departments

set location_id = 1800

where department_id = 302;

1 행 이(가) 업데이트되었습니다.

select * from departments order by department_id desc;

아까 삽입한 302번 부서의 location_id는 널 값이었지만, 1800으로 수정된 것을 볼 수 있다.

추가로 여기서 ROLLBACK을 실행하고 결과를 확인해보자.

rollback;

롤백 완료.

select * from departments order by department_id desc;

ROLLBACK을 통해 수정 작업을 취소했음으로 데이터가 다시 복원된다.

[SQL 19]에서도 설명했듯이 ROLLBACK은 트랜잭션 시작전으로 돌아가므로 COMMIT 되지 않은 다른 명령까지 다시 복원되므로 주의하도록 하자.

▶ 모든 행의 수정

update departments

set manager_id = 100;

30개 행 이(가) 업데이트되었습니다.

select * from departments order by department_id desc;

WHERE 절을 생략할 경우 지정된 컬럼의 모든 행의 값이 수정된다.

rollback;

롤백 완료.

▶ 여러 행의 수정

update employees

set job_id = 'ST_CLERK', salary = 5000

where employee_id = 114;

1 행 이(가) 업데이트되었습니다.

사원번호 114 사원의 job_id, salary를 수정했다. 위 예제와 같이 여러 컬럼의 행의 값을 수정할 수 있다.

select employee_id, job_id, salary from employees where employee_id = 114;

rollback;

롤백 완료.

No. 03
DELETE

기본 구조

DELETE FROM 테이블

[WHERE 조건];

• WHERE 절의 조건에 해당하는 행단위로 데이터를 삭제한다.
• 조건이 없는 경우 테이블의 모든 행을 삭제한다.
• 테이블을 삭제해도 테이블의 물리적인 구조는 변하지 않는다.

◈ 예제

delete from departments

where department_id = 301;

1 행 이(가) 삭제되었습니다.

commit;

커밋 완료.

▶ 조건이 없는 경우

현재의 테이블은 제약조건 때문에 전체데이터를 삭제하지 못하기 때문에 임시로 만들어서 해보도록 하겠다. 나중에 배울 것이기 때문에 지금은 일단 따라서 테이블을 만들어 보도록 하자. 테이블을 만드는 문장은 아직은 이해할 필요는 없다. 간단히만 설명하자면, DEPARTMENTS 테이블과 같은 구조지만 제약조건이 없는 테이블을 만들고 DEPARTMENTS 테이블의 모든 데이터를 삽입한 것이다.

create table copy_departments ( department_id number(4,0),

  department_name varchar2(30 byte),

  manager_id number(6,0),

  location_id number(4,0));

Table COPY_DEPARTMENTS이(가) 생성되었습니다.

insert into copy_departments

select * from departments;

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

commit;

커밋 완료.

select * from copy_departments;

---

delete from copy_departments;

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

select * from copy_departments;

아무것도 검색되지 않는다.

조건을 사용하지 않으면 테이블의 모든 행이 삭제된다.

착각하지 말아야 할 것은 테이블은 삭제되는 것이 아니고, 테이블은 그대로 남아있지만 테이블의 데이터만 삭제된 것이다!

테이블이 남아 있다는 것은 데이터를 다시 삽입할 수 있다는 뜻이다.

rollback;

롤백 완료.

No. 04
TRUNCATE

기본 구조

TRUNCATE TABLE 테이블;

• 테이블의 모든 행이 삭제되지만, 테이블의 구조는 변하지 않는다.
• DML 문장이므로, ROLLBACK으로 복원되지 않는다.
• 조건이 없는 DELETE 문장보다 속도가 빠르다. (Rollback 정보를 만들지 않기 때문에)

◈ 예제

truncate table copy_departments;

Table COPY_DEPARTMENTS이(가) 잘렸습니다.

select * from copy_departments;

조건이 없는 DELETE문과 마찬가지로 아무것도 검색되지 않지만, 테이블 구조는 남아있다.

rollback;

롤백 완료.

select * from copy_departments;

Rollback 했음에도 불구하고 DELETE문과 다르게 데이터가 복원되지 않았다. 이유는 TRUNCATE가 DDL문장이기 때문이다. 전에도 말했지만, DML 문장을 제외하고는 COMMIT, ROLLBACK의 사용이 무의미하다. DML 문장을 제외한 나머지는 문장이 실행됨과 동시에 COMMIT 된 상태와 같다.

▣ DELETE vs. TRUNCATE

TRUNCATE 문장은 조건이 없는 DELETE 문장과 동일한 역할을 한다. 그렇다면 테이블 전체가 아닌 행의 삭제도 가능한 DELETE 만 쓰면되지 왜 TRUNCATE를 쓰는지 의문이 들것이다.

DELETE와 TRUNCATE는 내부적으로 조금 다르다. 테이블에 행이 추가될 때 실제디스크 공간은 추가되는 행의 데이터 크기만큼 할당된다. 100개의 행을 가지고 있는 테이블이 있고 데이터들이 필요없어져서 테이블은 남겨놓고, 데이터들만 모두 삭제했다고 치자. 이 때, 삭제를 DELETE 문을 사용하여 삭제한 경우와 TRUNCATE로 삭제한 경우 두 가지로 볼 수 있다. 각 경우는 테이블에 다시 데이터를 삽입했다고 한다면 다음과 같을 것이다.

위의 그림을 하드디스크 상태라고 가정하면, DELETE로 데이터를 삭제한 테이블은 100개의 행이 사용했떤 디스크 공간이 그대로 할당된채로 남아 있고, TRUNCATE로 삭제한 경우는 할당되었던 디스크 공간까지 다시 반환하고, 깨끗한 상태로 다시 시작하게 된다. 새로 추가되는 행의 개수가 100개 이상이라면 상관없겠지만 예를 들어 20개만 추가되었다고 한다면 SELECT 문으로 검색하면 성능의 저하를 일으킬 수 있다. 성능의 저하를 일으키는 이유는 실제 SELECT문으로 데이터를 검색하면 디스크는 테이블이 할당되어 있는 디스크 공간을 모두 검색하게 되는데, DELETE를 사용하여 삭제한 테이블은 데이터가 없음에도 불구하고 1번부터 100번까지 모두 검색한 후 검색 결과를 사용자에게 보여주지만, TRUNCATE를 사용하여 삭제한 경우는 추가된 20개의 행에 해당하는 디스크 공간만 검색하게 된다. 데이터의 양이 많지않은 데이터베이스에서는 성능차이는 거의 없겠지만, 데이터의 양이 몇 년씩 쌓여있는 데이터베이스에서는 성능차이가 심할 것이다.

결론적으로 말하자면, 테이블의 데이터를 모두 삭제하는 경우 성능의 향상을 위해 TRUNCATE를 권하지만 Rollback으로 복원할 수 없으니 신중하게 사용하도록 하자.

물론 Rollback이 아닌 실제 복구를 통해서 데이터를 되살릴 수 있지만, 이 같은 방법은 반드시 복구를 보장하는 것은 아니다.