6-1.삽입

1.INSERT

현실 세계의 데이터는 끊임없이 변한다. DB가 현재성을 확보하려면 실데이터의 변화에 따라 지속적으로 수정 및 관리해야 한다. 새 데이터는 삽입하고 필요 없어진 데이터는 삭제하며 값이 바뀌면 최신 정보로 변경한다. 이 장에서는 데이터를 관리하는 명령에 대해 알아본다.

새로운 레코드를 추가하는 명령은 INSERT이다. 앞서 SQL 실습을 시작하기 전에 실습용 데이터를 쿼리문으로 입력해 넣을 때 사용한 명령이 바로 INSERT이다. INSERT 문을 스크립트로 작성해 놓으면 많은 데이터를 순차적으로 입력할 수 있어 편리하다. 기본 형식은 다음과 같다.

INSERT INTO 테이블 (필드목록) VALUES (값목록)

명령어가 두 단어로 되어 있는데 INSERT는 삽입하라는 명령어이고 INTO는 삽입 대상을 명시하는 전치사이다. 구문 해석상 전치사 INTO가 꼭 필요한 것은 아니지만 SQL 명령을 자연어인 영어와 유사하게 보이도록 한다. 그러나 비영어권에서는 별 설득력이 없고 명령어만 길어진다.

그래서 SQL Server와 MariaDB는 편의상 전치사 INTO를 생략하는 것을 허용한다. 하지만 SQL 표준에는 INTO가 필수로 되어 있으며 오라클을 포함한 일부 DBMS는 INTO를 생략하면 에러로 처리한다. 호환성을 위해 INTO를 꼭 붙여야 하며 아예 INSERT INTO가 한 명령이라고 생각하는 것이 좋다.

INTO 다음에 새 레코드를 삽입할 테이블 이름을 적는다. 필드 목록에 삽입할 필드의 이름을 콤마로 구분하여 나열하고 VALUES 절의 값 목록에는 필드에 대입할 값을 나열한다. tCity 테이블에 서울 레코드를 삽입하는 원론적인 쿼리문은 다음과 같다.

INSERT INTO tCity (name, area, popu, metro, region)

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

필드 목록, 값 목록 모두 5개여서 개수가 딱 맞다. 두 목록이 순서대로 대응되어 name에 '서울'을 저장하고 area에 605를 대입하는 식이다.

대응관계가 분명해 헷갈릴 여지가 없다. 그러나 필드 목록과 값 목록이 반복되어 명령문이 길고 필드명을 일일이 순서에 맞춰 입력하는 것도 귀찮은 일이다. 모든 필드를 선언 순서대로 삽입할 때는 필드 목록을 생략할 수 있다. 다음과 같이 더 짧게 써도 된다.

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

필드 목록이 없어도 '서울'이 name이고 605가 area임을 명확히 알 수 있다. 다른 도시도 이 방식대로 삽입하면 된다. 서울은 이미 있어 중복 삽입할 수 없으므로 테이블에 없는 평택을 삽입해 보자.

INSERT INTO tCity VALUES ('평택', 453, 51, 'n', '경기');

값을 조사하는 명령이 아니어서 질의 결과창에 별다른 출력은 없고 스크립트 출력창에 "1 행 이(가) 삽입되었습니다." 메시지로 잘 삽입했음을 보고한다. SELECT로 확인해 보면 평택이 목록의 제일 아래쪽에 나타난다. SQL Server와 MariaDB는 이름순으로 정렬하여 춘천과 홍천 사이에 평택이 끼어든다.

INSERT INTO 뒤에 필드 목록이 없어도 테이블을 정의할 때 선언한 순서대로 값과 필드의 짝을 지으면 되니 애매하지 않다. '평택'이 name 필드에 저장되고 area에 453을 대입한다.

그러나 간단한 방법은 아무래도 한계가 있기 마련이다. 필드 목록이 없는 대신 값 목록이 완전해야 하며 순서도 반드시 지켜야 한다. 까딱 잘못해서 다음과 같이 썼다고 해 보자.

INSERT INTO tCity VALUES ('평택', 51, 453, 'n', '경기'); -- area와 popu가 바뀜

INSERT INTO tCity VALUES ('평택', 453, 'n', 51, '경기'); -- popu와 metro 순서가 바뀜

INSERT INTO tCity VALUES ('평택', 453, 'n', '경기'); -- popu 필드값 누락

인구와 면적을 반대로 지정하면 평택은 51제곱 킬로미터에 450만명이 사는 복잡한 도시가 되어 버린다. 그나마 윗줄은 타입이 같으니 들어 가지만 인구와 광역시 필드의 순서를 바꾸면 정수와 문자열이 호환되지 않아 에러 처리된다. 필드 하나를 누락해도 역시 에러이다.

INSERT INTO는 여러 개의 대입문을 모아 놓은 것이어서 개수와 타입이 일치해야 한다. 그래서 필드 목록을 나열하는 것이 정석이다. 필드 목록을 밝히면 꼭 선언 순서에 맞추지 않아도 원하는 순서대로 나열할 수 있다.

INSERT INTO tCity (area, popu, metro, region, name)

VALUES (453, 51, 'n', '경기', '평택');

name 필드를 제일 뒤로 보냈지만 필드 목록에 의해 짝을 정확히 찾을 수 있다. 또한 필드 목록을 적으면 테이블의 구조 변화에 대한 유연성이 향상된다. 새로운 필드를 추가하거나 필드의 순서를 바꾸어도 필드 목록이 있는 문장은 항상 잘 실행된다.

요약하자면 필드 목록을 생략하지 말든가, 생략했다면 값 목록을 완벽하게 적든가 둘 중 하나를 선택해야 한다. 신체의 편안함을 위해 뭔가를 생략하려니 복잡해진다. 처음 배울 때는 원론적으로 필드 목록을 일일이 적고 익숙해지면 짧게 쓰도록 하자.

2.COMMIT

데이터베이스에 저장하는 정보는 굉장히 민감하고 소중하다. 반면 SQL 명령은 너무 강력해서 단 한 줄의 쿼리로도 치명적인 결과를 초래할 수 있다. DBMS는 실수를 원천적으로 방지하기 위해 삽입, 삭제, 변경한 레코드를 테이블에 직접 적용하지 않고 임시 영역에 대기시켜 놓는다. tCity에 평택을 삽입했을 때 DB의 내부 구조는 다음과 같다.

평택은 아직 tCity에 기록되지 않고 임시 영역에서 삽입 대기중이다. SELECT 명령은 테이블의 레코드뿐만 아니라 임시 영역의 레코드까지 합쳐서 같이 보여 준다. 그래서 삽입 직후 조사해 보면 평택이 테이블에 들어간 것처럼 보인다.

임시 영역의 변경 사항이 이상 없다고 판단한 후 테이블에 영구 적용하는 것을 커밋(Commit)이라고 한다. 반면 잘못 넣은 것 같다면 변경 사항을 취소할 수 있는데 이를 롤백(RollBack)이라고 한다. 오라클은 명시적인 명령을 내리지 않으면 임시 영역을 그대로 유지한다.

평택을 삽입만 해 놓은 상태에서 SQL Developer를 종료해 보자. DB의 동작을 이해하기 위해 한 번 정도는 이 실험을 꼭 해 볼 필요가 있다. 임시 영역에 변경 사항이 있을 경우 임의 처리할 수 없어 어떻게 처리할 것인지 사용자에게 의사를 물어 본다.

디폴트는 커밋이지만 여기서는 롤백을 선택해 보자. 이 경우 임시 영역의 삽입 기록을 테이블에 적용하지 않고 삭제해 버린다. 스크립트는 굳이 저장하지 않아도 상관없다. SQL Developer를 다시 시작한 후 tCity 테이블을 확인해 보면 평택이 사라지고 없다.

아무리 새 레코드를 삽입하고 삭제하고 변경을 가해도 임시 영역에만 기록할 뿐이며 커밋하지 않으면 테이블에 반영하지 않는다. 과연 그런지 평택을 다시 삽입해 보자.

INSERT INTO tCity VALUES ('평택', 453, 51, 'n', '경기');

SELECT * FROM tCity;

이 상태에서 테이블을 조회하면 평택이 분명히 삽입되어 있다. 하지만 아직까지는 임시 영역에 있을 뿐이다. 롤백하면 삽입은 언제든 취소된다.

ROLLBACK;

롤백 후 tCity를 다시 조회해 보면 평택은 없다. 레코드를 삽입한 후 확정하려면 COMMIT 명령으로 임시 영역의 변경 사항을 확정해야 한다.

COMMIT;

이 명령은 임시 영역의 평택을 테이블에 영구적으로 기록한다. 이제 SQL Developer를 종료했다가 다시 실행해도 평택이 보인다. 테이블을 조작할 때는 제대로 했는지 확인을 거친 후 적용 여부를 결정한다. 자주 쓰는 명령이어서 툴바에 있으며 조합키도 아닌 단독 단축키까지 지정되어 있다.

그렇다면 SQL 명령을 내린 후 항상 이 두 명령중 하나를 실행해야 하는가 하면 그렇지는 않다. 테이블을 읽기만 하는 SELECT 명령은 임시 영역을 쓰지 않아 커밋이나 롤백이 필요 없다. 다행히 우리가 쓰는 SQL 명령의 90%는 SELECT이다.

오라클과는 달리 SQL Server와 MariaDB는 자동으로 커밋 명령을 실행하는 오토 커밋(Auto Commit) 모드가 디폴트이다. 삽입하는 즉시 테이블에 기록하므로 굳이 COMMIT 명령을 내리지 않아도 상관없다. 명령을 내리는 즉시 실행되어 편리하지만 대신 아차 싶어도 롤백할 수 없다.

연습 문제

1.도시 목록에 용인을 삽입하라. 면적 293에 인구 98만이되 데이터는 임의값을 써도 상관없다.

2.직원 목록에 자신의 신상을 삽입해 보아라.

3.확장 INSERT 문

INSERT 명령을 나열해 놓으면 여러 개의 행을 한 번에 삽입할 수 있어 편리하다. 레코드가 많아도 순식간에 삽입한다. tCity 테이블로 이런 저런 실습을 하다가 원래대로 초기화하고 싶다면 다음 명령으로 테이블을 싹 비운다. 그리고 이미 작성해 놓은 스크립트만 실행하면 깜쪽같이 복구한다.

TRUNCATE TABLE tCity;

그런데 각 행마다 개별적으로 INSERT INTO 명령을 일일이 작성하면 쿼리문이 길어 번잡스럽다. 같은 테이블에 소속된 행은 당연히 필드 목록이 같다. 행을 삽입할 때마다 똑같은 필드 목록을 일일이 다시 나열하는 것은 낭비이다.

필드 목록을 생략하더라도 INSERT INTO tCity VALUES 구문이 반복되는 것은 마찬가지이다. 거대한 테이블에 한꺼번에 수만 개의 레코드를 삽입하면 반복이 더 심해진다. 명령문과 필드 목록은 딱 한 번만 밝히고 실제 삽입할 데이터만 나열하면 어떨까?

INSERT INTO tCity (name, area, popu, metro, region) VALUES

('서울',605,974,'y','경기'),

('부산',765,342,'y','경상'),

('오산',42,21,'n','경기'),

('청주',940,83,'n','충청'),

('전주',205,65,'n','전라'),

('순천',910,27,'n','전라'),

('춘천',1116,27,'n','강원'),

('홍천',1819,7,'n','강원');

꼭 필요한 정보만 밝히니 문장 길이가 대폭 줄어든다. 이렇게 해도 최초의 필드 목록대로 값을 순서대로 대응시키면 아무 문제가 없다. 필드 목록도 테이블의 필드 선언 순서와 같으면 생략할 수 있다. 대량의 데이터를 삽입할 때는 이 방법이 더 간편하다.

그러나 안타깝게도 이 문장은 아직 표준이 아니다. 최초 MySQL에서 이 문법을 도입했는데 짧아서 입력하기 편하고 엔진이 해석할 구문이 줄어 속도도 빠르다. 후계자인 MariaDB도 이 문법을 지원하며 SQL Server, DB2, PostgreSQL 등 웬만한 DBMS 제작사도 다 지원한다.

누가 봐도 괜찮은 문법은 이런 식으로 서로 참조해 가며 표준을 발전시키고 있다. 그러나 선두 업체인 오라클은 아직 이 문법을 지원하지 않아 INSERT INTO 명령을 매번 반복하는 수밖에 없다. 언젠가는 지원하겠지만 그때까지는 확장 INSERT 문을 범용적으로 쓸 수 없다.

연습 문제

3.도시 목록에 이천(461Km, 21만)과 대구(883Km, 248만), 영월(1127Km, 4만)을 삽입하라.

4.INSERT SELECT

INSERT 명령은 한 번에 하나의 레코드만 삽입한다. 필드의 값을 일일이 지정해야 하니 어쩔 수 없다. 하지만 다른 테이블 또는 자기 자신에게 이미 저장되어 있는 대량의 정보를 복사할 때는 조회한 결과셋을 한꺼번에 삽입할 수 있다. 이때는 INSERT SELECT 명령을 사용한다.

INSERT INTO 대상테이블(필드목록) SELECT 필드목록 FROM 원본테이블

레코드를 삽입하는 INSERT INTO와 기본 형식이 비슷하지만 필드의 값을 VALUES 절로 지정하지 않고 SELECT 명령으로 다른 테이블에서 읽어온다는 점이 다르다. 별도의 문법이라기보다는 INSERT INTO에 SELECT 명령이 포함된 형식이다.

SELECT 오른쪽의 원본 필드 목록과 왼쪽의 목적지 필드 목록은 1:1로 대응되며 순서와 타입이 일치하거나 적어도 호환되어야 한다. 목적 테이블의 모든 필드를 다 나열할 필요는 없지만 생략된 필드는 NULL을 허용하거나 기본값이라도 있어야 한다.

다음 명령은 tCity 테이블에서 경기도 소속의 도시 정보를 읽어 tStaff 테이블에 신입 직원으로 삽입한다. name 필드는 공통적이고 부서는 지역값을 써 넣고 성별에 광역시 여부, 월급은 면적, 성취도는 인구수 필드에 대응시켰다. 논리적 의미는 맞지 않지만 필드 타입은 다 일치한다.

INSERT INTO tStaff(name, depart, gender, joindate, grade, salary, score)

SELECT name, region, metro, '20210629', '신입', area, popu FROM tCity WHERE region = '경기';

이 명령을 실행하면 경기도에 있는 서울과 오산을 새로운 신입 사원으로 tStaff 테이블에 한꺼번에 삽입한다. 조건을 바꾸면 더 많은 도시를 삽입해 넣을 수도 있다.

테이블간의 복사는 잘 실행되었지만 필드끼리의 논리적인 관계도 맞지 않고 예가 좀 억지스럽다. 도시가 직원이 된다니 말도 안되는 소리이지만 현재 예제 테이블이 두 개밖에 없어 어쩔 도리가 없다. 테이블을 새로 만들기는 번거로우니 가상의 테이블로 현실적인 예를 들어 보자.

tCandidate 테이블은 입사 지원서를 낸 후보생의 목록이며 신상 정보와 함께 이력서, 면접 결과, 합격 여부 등의 정보가 저장되어 있다. 직원 테이블과 구조가 다르지만 사람의 신상 정보라는 면에서 비슷하다. 최종 선발이 끝나면 다음 명령으로 합격한 후보생을 직원 테이블로 복사한다.

INSERT INTO tStaff(name, depart, gender, joindate, grade, salary, score)

SELECT name, 지원부서, gender, 오늘, '수습', 230, score * 0.1 FROM tCandidate

WHERE result = '합격';

후보생 테이블에서 합격 판정을 받은 예비 직원을 조회한다. 이름이나 성별은 바뀔 리 없으니 신상명세의 정보를 복사하고 지원한 부서에 그대로 넣어 준다. 입사 날짜는 오늘이고 방금 들어 왔으니 직급은 수습이며 초봉 230부터 시작한다. 성취도는 입사 시험의 10%를 쳐 주기로 했다. 복사할 건 복사하고 값이 없으면 적당한 디폴트를 취하고 필요 없는 정보는 버린다.

다른 테이블의 정보를 재활용하는 경우는 흔하며 심지어 같은 테이블의 정보를 재활용하기도 한다. 예를 들어 쇼핑 목록에서 고객이 같은 상품을 재주문한다면 이전 쇼핑 목록의 레코드를 그대로 복사하되 주문 날짜를 오늘로 바꾸고 배송 상태만 배송준비중으로 바꾸면 된다. 이럴 때 쓰는 명령이 바로 INSERT SELECT이다.

5.CREATE SELECT

CREATE SELECT 명령은 INSERT SELECT와 유사하되 기존 테이블에 레코드를 삽입하는 것이 아니라 새로 테이블을 만들어 결과셋을 삽입한다는 점이 다르다.

CREATE TABLE 대상테이블 AS SELECT 필드목록 FROM 원본테이블

테이블을 새로 만들면서 기존 테이블의 일부 필드와 레코드를 가져와 삽입하는 명령이어서 대상 테이블이 존재해서는 안된다. 만약 대상 테이블이 이미 있다면 덮어쓸 수 없어 에러 처리되므로 그 전에 대상 테이블을 지워야 한다.

원본을 손상시키지 않고 특수한 작업을 하려면 사본을 따로 만드는 것이 좋다. 이럴 때 CREATE SELECT 명령이 유용하다. 다음 쿼리는 경기도 지역의 도시만으로 tSudo 테이블을 생성한다. 모든 필드를 다 포함할 필요 없이 이름, 면적, 인구만 포함했다.

CREATE TABLE tSudo AS SELECT name, area, popu FROM tCity WHERE region = '경기';

SELECT 문만 실행하면 tCity의 일부 데이터를 조회한다. 그 결과셋을 그대로 tSudo 테이블의 새 데이터로 사용하여 삽입한다. 이 명령을 실행한 후 SELECT * FROM tSudo; 쿼리로 조회하면 수도권에 있는 도시만 나타난다.

SELECT * FROM tSudo;

새로 생성한 테이블의 구조는 SELECT 절의 결과셋과 같으며 필드의 타입이나 길이도 같다. 필드 목록에 *를 적고 WHERE 절을 생략하면 모든 필드와 모든 레코드를 다 복사하는 셈이어서 완전히 똑같은 사본을 만든다. 다음 명령은 tCity의 현재 정보를 그대로 tCityCopy 테이블로 복사한다.

SELECT * INTO tCityCopy FROM tCity;

단, 사본은 데이터만 복사할 뿐 기본키나 외래키 같은 제약 조건까지 복사하지는 않는다. SELECT 절의 결과셋만으로는 기본키에 대한 정보나 제약 조건까지 알아낼 수 없기 때문에 어찌 생각하면 당연하다.

사본 테이블은 학습용이나 예비 처리의 타당성 점검 등 자유로운 시도를 마음껏 해 볼 수 있다. 삶아 먹든 볶아 먹든 원본을 건드리는 것은 아니어서 원하는대로 쪼물딱거린 후 지워 버리면 된다. 원본에 대해 중요한 작업을 하기 전에 백업을 떠 놓는 용도로 적합하다. tStaff 테이블에 대량의 변경을 가해야 하는데 뭔가 불안하다면 다음 명령으로 백업을 뜬다.

CREATE TABLE tStaff_8월20일 AS SELECT * FROM tStaff;

백업은 여러 번 뜰 수 있어 관행상 날짜를 명시한다. 혹시 잘못 조작해서 tStaff이 왕창 파괴되었다면 백업에서 데이터를 다시 가져오면 된다. 물론 이상 없이 작업을 마쳤다면 적당한 때에 백업을 삭제하면 그만이다.

호환성체크

SQL Server는 CREATE SELECT 명령 대신 똑같은 동작을 수행하는 SELECT INTO 명령을 제공한다. SELECT 명령 중간에 INTO 절이 있어 결과셋을 화면 대신 새 테이블로 출력한다. 수도권 도시만 골라 tSudo 테이블을 생성하는 명령은 다음과 같다.

SELECT name, area, popu INTO tSudo FROM tCity WHERE region = '경기';

구문이 약간 다를 뿐 오라클의 CREATE SELECT와 결과는 같다. 대상 테이블이 없어야 하고 구조만 복사할 뿐 기본키나 제약 조건을 복사하지 않는 특성도 동일하다.

연습 문제

4.성취도가 80점 이상인 직원만 골라 이름과 월급에 대한 보고서를 별도의 테이블로 작성하라.