10  데이터 생성과 변형

지금까지 우리는 데이터베이스에서 정보를 추출하는 방법에 대해서만 살펴보았다. 그 이유는 정보를 추출하는 것이 정보를 추가하는 것보다 더 자주 발생하기 때문이며, 또한 대부분의 다른 작업들은 쿼리가 이해된 후에야 의미를 갖기 때문이다.

survey.db 데이터베이스 Person, Survey, Site, Visited 테이블을 지금까지 사용했다. 이제 새로운 데이터베이스를 구축해야 하기 때문에, 만약 여전히 SQLite 인터랙티브 세션에 있다면, 그 세션을 종료한다.

.exit

SQLite3를 실행하고 새로운 데이터베이스를 생성한다. 데이터베이스를 newsurvey.db라고 명명한다. 기존 survey.db 데이터베이스와 혼동을 피하기 위해 다른 이름을 사용한다.

$ sqlite3 newsurvey.db

.sqliterc 파일을 사용하지 않는 경우 .mode column과 .header on 명령을 다시 실행한다. (SQLite3를 종료하고 다시 시작한 경우 설정이 기본값으로 돌아갈 것임을 주의하라)

.mode column
.header on

데이터를 생성하고 수정하고 싶다면, 두 가지 명령어 짝을 알아야 한다.

첫번째 명령어 쌍은 CREATE TABLE과 DROP TABLE이다. 두 단어로 작성되지만, 사실 하나의 단일 명령어다. 첫번째 명령어는 새로운 테이블을 생성한다. 인자는 테이블 칼럼의 이름과 형식이다. 예를 들어, 다음 문장은 survey 데이터베이스에 테이블 4개를 생성한다. 

CREATE TABLE Person(id text, personal text, family text);
CREATE TABLE Site(name text, lat real, long real);
CREATE TABLE Visited(id integer, site text, dated text);
CREATE TABLE Survey(taken integer, person text, quant text, reading real);

다음 명령어를 사용하여 테이블 중의 하나를 제거할 수도 있다.

DROP TABLE Survey;

데이블을 제거할 때 매우 주의하라. 대부분의 데이터베이이스는 변경사항을 되돌리는 기능을 제공하지만, 이러한 기능에 의존하지 않는 것이 더 낫다.

데이터베이스 시스템 마다 테이블 칼럼의 다른 자료형도 지원하지만, 대부분 다음 자료형을 제공한다.

자료형 설명
INTEGER 부호가 있는 정수
REAL 부동 소수점 숫자
TEXT 문자열
BLOB “바이너리 대형 객체”, 예를 들어 이미지 등

데이터베이스 대부분 부울(boolean)과 날짜/시간 자료형도 지원한다. SQLite는 부울값을 정수 0 과 1 을 사용하고, 날짜/시간은 앞서 논의된 방식으로 표현한다. 점점 더 많은 데이터베이스가 지리적 데이터 유형, 예를 들어 위도와 경도를 지원하고 있다. 특정 시스템이 무엇을 제공하며, 다른 데이터 유형에 어떤 이름을 부여하는지 추적하는 것은 끝없는 시스템 이식성(포터빌리티) 문제이다.

테이블을 생성할 때, 그 칼럼에 대해 다양한 제약 조건을 지정할 수 있다. 예를 들어, Survey 테이블에 대한 좀더 좋은 정의는 다음과 같다.

CREATE TABLE Survey(
    taken   integer not null, -- where reading taken
    person  text,             -- may not know who took it
    quant   text not null,    -- the quantity measured
    reading real not null,    -- the actual reading
    primary key(taken, person, quant),    -- key is taken + person + quant
    foreign key(taken) references Visited(id),
    foreign key(person) references Person(id)
);

다시 한번, 어떤 제약 조건들이 사용 가능하고, 어떻게 불려지는지는 어떤 데이터베이스 관리자를 사용하느냐에 따라 다르다.

10.1 삽입

테이블이 생성되면, INSERT, UPDATE, DELETE와 같은 다른 명령집합을 사용하여 레코드를 추가, 변경 및 제거할 수 있다.

다음은 Site 테이블에 행을 삽입하는 예시다.

INSERT INTO Site (name, lat, long) VALUES ('DR-1', -49.85, -128.57);
INSERT INTO Site (name, lat, long) VALUES ('DR-3', -47.15, -126.72);
INSERT INTO Site (name, lat, long) VALUES ('MSK-4', -48.87, -123.40);

또한, 다른 테이블에서 직접 값을 테이블에 삽입할 수도 있다.

CREATE TABLE JustLatLong(lat real, long real);
INSERT INTO JustLatLong SELECT lat, long FROM Site;

10.2 갱신

기존 레코드를 수정하는 것은 UPDATE 문을 사용하여 수행된다. 이 작업을 수행하기 위해 데이터베이스에 어떤 테이블을 업데이트할 것인지, 필드의 값들을 어떻게 변경할 것인지, 어떤 조건에서 값들을 업데이트할 것인지를 지정해줘야 한다.

예를 들어, 앞서 마지막 INSERT 문에서 위도와 경도 값을 잘못 입력했다면, 업데이트를 통해 이를 수정할 수 있다.

UPDATE Site SET lat = -47.87, long = -122.40 WHERE name = 'MSK-4';

WHERE 절을 잊지 않도록 주의하라. 그렇지 않으면 업데이트 문이 Site 테이블 모든 레코드를 수정하게 된다.

10.3 삭제

레코드를 삭제하는 것은 데이터베이스를 내부적으로 일관성 있게 유지해야 하기 때문에 좀 더 복잡하다. 테이블이 하나로 단순하다면, 제거하고자 하는 레코드와 일치하는 WHERE 절과 함께 DELETE 명령을 한다. 예를 들어, 프랭크 댄포스(Frank Danforth)가 어떠한 조사측정 업무도 수행하지 않았다는 것을 확인하고 반영한다면, 다음과 같이 Person 테이블에서 프랭크 댄포스를 제거한다.

DELETE FROM Person WHERE id = 'danforth';

그러나 앤더슨 레이크(Anderson Lake)를 대신 제거한다면 어떨까? Survey 테이블은 그가 수행한 측정의 7개 레코드를 여전히 포함할 것이다. 그러나 이러한 일은 절대 일어나서는 안 되는 일이다. Survey.person은 Person 테이블로 외래 키로, 작성된 모든 쿼리는 전자의 모든 값이 매칭되는 후자의 행이 있을 거라고 가정한다.

이 문제를 참조 무결성(referential integrity)이라고 부른다. 테이블 간 모든 참조가 항상 올바르게 해결될 수 있도록 해야 한다. 이를 해결하는 한 가지 방법은 기본 키로 사용되는 레코드를 삭제하기 전에 외래 키로 lake를 사용하는 모든 레코드를 삭제하는 것이다. 데이터베이스 관리자가 지원한다면, 계단식 삭제(cascading delete)를 자동화할 수 있다. 하지만, 이 기법은 여기서 다루는 SQL 기본영역 밖이다.

하이브리드 저장 모델

많은 응용 프로그램들이 모든 것을 데이터베이스에 저장하는 대신 하이브리드 저장 모델을 사용한다. 실제 데이터(예: 천문학적 이미지)는 파일로 저장되며, 데이터베이스는 파일의 이름, 수정 날짜, 하늘의 어느 지역을 커버하는지, 그들의 분광 특성 등을 저장한다. 대부분의 음악 재생기(MP3 플레이어) 소프트웨어가 작성되는 방식이기도 하다. 응용프로그램 내부 데이터베이스는 MP3 파일을 기억하고 있지만, MP3 파일 자체는 디스크에 있다.

연습문제

객관식

  1. 문제: SQL에서 테이블을 생성할 때 사용하는 명령어는 무엇입니까?
    1. INSERT INTO
    2. CREATE TABLE
    3. UPDATE TABLE
    4. ALTER TABLE
  1. 문제: SQL 테이블 생성 시 필수적으로 명시해야 하는 것은 무엇입니까?
    1. 데이터베이스 이름
    2. 테이블의 최대 크기
    3. 열(컬럼)의 이름과 데이터 유형
    4. 인덱스
  1. 문제: PRIMARY KEY 제약 조건의 역할은 무엇입니까?
    1. 데이터 정렬
    2. 중복 데이터 제거
    3. 데이터 무결성 보장
    4. 데이터베이스 최적화

NULL 치환

Survey.person의 null인 모든 사용자를 문자열 'unknown'으로 대체하는 SQL문을 작성하세요.

SQL 백업

SQLite는 SQL 표준에서 벗어난 몇가지 관리 명령어을 가지고 있다. 그 중 하나는 .dump로, 데이터베이스를 다시 생성하는 데 필요한 SQL 명령을 출력한다. 또 다른 하나는 .read로, .dump에 의해 생성된 파일을 읽고 데이터베이스를 복원한다. 당신의 동료는 덤프 파일(텍스트 형식)을 버전 관리에 저장하는 것이 데이터베이스의 변경을 추적하고 관리하는 좋은 방법이라고 생각한다. 이 접근 방식의 장단점은 무엇인가? (힌트: 레코드는 특정한 순서로 저장되지 않는다.)