6-2.삭제

1.DELETE

레코드를 삭제할 때는 DELETE 명령을 사용한다. 삭제는 항상 레코드 단위로 수행하므로 열에 대한 지정은 없다.

DELETE FROM 테이블 WHERE 조건

INSERT는 전치사 INTO를 쓰는데 비해 DELETE는 전치사 FROM을 쓴다. 오라클과 SQL Server는 FROM의 생략을 허용하지만 MariaDB는 생략시 에러 처리한다. 결국 호환성을 위해 DELETE 명령 다음에는 FROM을 항상 붙이는 것이 좋다.

삭제 동작은 특정 조건에 맞는 레코드를 찾아 제거하는 경우가 대부분이어서 WHERE 절이 항상 뒤따라 온다. 다음 명령은 도시명이 ‘부산’인 레코드를 삭제한다.

DELETE FROM tCity WHERE name = '부산';

부산이 하나밖에 없어 삭제할 레코드는 하나뿐이다. WHERE 조건에 맞는 레코드가 여러 개이면 모두 삭제한다. 다음 명령은 경기도에 있는 모든 도시를 삭제한다.

DELETE FROM tCity WHERE region = '경기';

DELETE 명령은 별도의 확인없이 조건에 맞는 레코드를 즉시 삭제한다. 부산과 경기도 소속의 도시 2개가 진짜로 사라져 버린다. SELECT 문으로 확인해 보자.

오라클은 삭제 사실을 임시 영역에 기록하므로 ROLLBACK 명령으로 삭제를 취소할 수 있다. 한 번의 복구 기회가 있지만 COMMIT까지 해 버리면 사라져 버린 레코드를 다시 복원할 방법은 없다. SQL Server나 MariaDB는 그 조차도 없어 DELETE 즉시 삭제한다.

연습 문제

5.영업부 직원을 전부 해고시켜 보자.

2.삭제 안전 장치

조회만 하는 SELECT 명령은 테이블을 변경하지 않아 실수해도 별 문제 되지 않는다. 조건을 바꿔 다시 조회하면 그만이다. INSERT 명령은 테이블을 변경하지만 혹시라도 잘못 삽입했으면 다시 지우면 되니 그다지 위험하지는 않다.

그러나 잘못 내린 DELETE 명령은 치명적이다. DBMS는 말을 너무 잘 들어 명령을 내리는 즉시 처리해 버린다. 별도의 백업이 없는 한 지워 버린 레코드를 복구하기는 무척 어렵다. 그래서 삭제할 때는 명령을 내리기 전에, 또는 COMMIT 하기 전에 신중히 점검해 봐야 한다.

DELETE 문은 특정 레코드를 삭제하기 위해 웬만하면 WHERE 절과 함께 쓴다. 그러나 실수로 WHERE 절을 생략해 버리면 테이블의 모든 레코드를 날려 버리는 살벌한 사고를 친 것이다. 수년간 수집한 수십만 건의 데이터가 한방에 날아가 버릴 수도 있다. 과연 그런지 다음 명령을 실행해 보자.

DELETE FROM tCity;

별도의 조건이 없으니 tCity의 모든 도시가 몽땅 사라진다. 고분 고분 말을 잘 듣는 이유는 이 정도 명령이라면 별도의 백업 대책이 있거나 오토커밋 모드가 아닌 경우가 대부분이기 때문이다. 쿼리툴에 따라서는 경고를 출력하기도 하는데 DBeaver는 다음 경고문을 보여준다.

WHERE 절이 없는 DELETE 명령인데 정말로 의도한 것이 맞는지, 아니면 단순한 실수인지 확인하는 것이다. 이 메시지도 어디까지나 경고일 뿐 에러는 아니어서 확인 버튼을 누르면 냉큼 지워 버린다. 이런 무시무시한 경고를 보여줄 정도로 WHERE 절 없는 DELETE 명령은 위험하다.

설사 WHERE 절이 있더라도 조건을 잘못 지정하면 엉뚱한 레코드가 삭제될 위험은 여전하다. 조건이 복잡하면 지울 레코드를 제대로 선정하는 것도 쉽지 않다. 인구 50만 초과인 도시를 삭제한다면 다음 절차대로 작업한다. DELETE 명령을 내리기 전에 먼저 SELECT로 삭제 대상 레코드를 고른다.

SELECT * FROM tCity WHERE popu > 50;

인구가 50만 초과인 도시만 조회했다. 만약 WHERE popu < 50으로 부등호를 반대로 썼거나 WHERE popu > 5 따위로 숫자를 잘못 썼다면 다른 결과가 나와 실수를 금방 눈치챌 수 있다. 확실히 삭제할 대상이 맞음을 확인한 후 SELECT *을 DELETE로 바꾸어 실행하면 안전하다.

쿼리창에는 여러 개의 명령을 입력해 놓고 한 줄씩 또는 블록을 선택하여 일부 명령만 실행하는 것이 보통이다. 실수로 블록 선택을 하지 않은 상태로 전체 스크립트를 실행하면 또 다른 사고가 발생한다. 이런 사고를 방지하려면 DELETE 문은 실행 후에 지워 버리거나 아니면 주석으로 감싸 놓는 것이 좋다.

/* DELETE FROM tCity WHERE popu > 50; */

주석으로 감싸 두면 일괄 실행에서 제외되며 다시 실행할 때 주석 안쪽만 블록으로 선택하면 되니 안전하다. DELETE 명령은 워낙 위험해서 야근과 주말 출근의 주범이다. 항상 주의해야 하며 갖가지 안전 대책을 강구하는데 다음은 내가 주로 애용하는 방법이다.

-- DELETE

-- SELECT *

FROM tStaff WHERE grade = '과장';

쿼리창에 이렇게 써 놓고 WHERE 절의 조건을 편집하여 삭제할 대상을 고른다. SELECT 부터 선택하여 실행하면 단순 조회문이다. 맞으면 DELETE부터 선택하여 삭제한다. 가운데 줄의 -- SELECT는 주석이어서 에러가 발생하지 않는다.

블록을 역방향으로 SELECT까지 선택하여 확인하고 한 칸 위로 올리면 DELETE까지 선택한다. DELETE도 주석으로 막아 두면 전체 스크립트를 실행해도 안전하며 블록을 정확히 선택할 때만 삭제하여 더 안전하다.

3.TRUNCATE

WHERE 절 없는 DELETE 명령은 상당히 위험하지만 테이블을 비울 목적이라면 쓸 수도 있다. 엉망이 된 데이터를 말끔히 지우고 처음부터 다시 수집하는 경우도 있다. DELETE FROM tCity; 명령을 내리면 테이블의 모든 레코드를 삭제한다.

그러나 이 명령은 레코드를 논리적인 방법으로 하나씩 지우고 로그도 남기기 때문에 느리다. 수백 개 정도는 큰 차이가 없지만 수만 개의 레코드를 일일이 지우는 것은 서버 입장에서도 벅찬 일이다. 이런 목적이라면 DELETE보다 다음 명령이 효율적이다.

TRUNCATE TABLE 테이블

TRUNCATE TABLE tCity 명령은 tCity 테이블을 완전히 비운다. 물리적인 기억 장소를 깨끗이 비우고 임시 영역에 로그도 남기지 않아 훨씬 빠르다. 수백만 개의 레코드도 순식간에 지워 버린다. 로그를 남기지 않기 때문에 ROLLBACK으로도 취소할 수 없다.

DELETE 명령은 DML에 속하는 데이터 관리 명령인데 비해 TRUNCATE 명령은 테이블을 관리하는 DDL 명령으로 소속이 다르다. TRUNCATE TABLE 명령은 테이블안의 레코드만 삭제할 뿐 테이블 자체는 삭제하지 않는다. 테이블까지 완전히 삭제하는 명령은 DROP TABLE이다.

일반적인 목적으로는 잘 사용하지 않지만 실습 과정에서 테이블을 재초기화할 때 종종 사용한다. 여기까지 실습을 진행하며 tCity에 삽입도 해 보고 지워도 보았다. 앞으로도 여러 가지 실습을 진행하면 점점 엉망이 될 것이다. 이럴 때 다음 명령을 내려 즉시 초기화한다.

TRUNCATE TABLE tCity;

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');

INSERT INTO tCity VALUES ('부산',765,342,'y','경상');

...

완전히 싹 비우고 다시 삽입하여 애초의 상태로 복원한다. 테이블을 DROP한 것은 아니어서 CREATE TABLE은 실행할 필요 없이 INSERT INTO로 데이터만 채워 넣으면 된다. tStaff 테이블도 마찬가지 방법으로 원상 복원한다. 이후의 실습은 부담 없이 진행하되 적당한 때에 샘플 테이블을 알아서 복원하자.