17장 테이블 생성과 조작

테이블 생성

SQL은 테이블 생성과 변경 같은 테이버테이스와 테이블 작업 수행에도 사용 가능하다. 일반적으로 데이터베이스 테이블을 생성하는 데는 두 가지 방법이 있다.

• DBMS에서 제공하는 대화형 관리 툴

• SQL 문으로 테이블을 직접 생성하고 관리

대화형 관리 툴은 간편하다. 하지만 실질적으로 두 방법 모두 SQL 문을 이용해 데이터베이스 테이블을 생성한다. 따라서 SQL 문으로 만드는 방법을 알아두자.

기본 테이블 생성

테이블 생성은 CREATE TABLE 문을 사용한다. 다음의 규칙을 준수해야 한다.

• CREATE TABLE 문 뒤 새로운 테이블 이름 적기

• 테이블 이름과 정의를 콤마로 구분하여 적기

• 일부 DBMS에서는 테이블 위치도 명시하기

(DBMS 마다 CREATE TABLE 문이 다르기에 사용할 DBMS의 매뉴얼을 알아두자)

예시를 보자

CREATE TABLE Products
(
    prod_id    CHAR(10)    NOT NULL,
    vend_id    CHAR(10)    NOT NULL,
    prod_name    CHAR(254)    NOT NULL,
    prod_price    DECIMAL(8,2)    NOT NULL,
    prod_desc    VARCHAR(1000)    NULL
);

CREATE TABLE 키워드 바로 뒤에 테이블 이름 Products가 나오고, 그 뒤 괄호 안에 열에 대한 내용이 들어있다. 열은 열 이름과 데이터형에 대한 정보가 담기고 각 열은 콤마로 구분된다.

존재하는 테이블 이름을 사용하면 안된다.

새로운 테이블을 생성할 때, 존재하는 테이블 이름을 사용하면 에러가 발생한다.

NULL 값 사용하기

모든 테이블 열은 NULL이나 NOT NULL의 특징을 갖는다. 이는 테이블이 생성되는 시점에 테이블 정의에서 결정된다.

위의 예시를 보면 각 열이 NOT NULL과 NULL로 되어있다. 여기서 NULL은 비어있는 값 NULL을 허용한다는 의미이고 그 열에 아무런 값을 넣지 않아도 행 삽입이 허용된다. 반면 NOT NULL은 값이 필수적으로 포함되야 함을 의미한다.

일반적으로 NULL은 기본값으로 지정되어 테이블 정의에 적지 않아도 된다.

기본값 지정하기

SQL에서는 행 삽입시 값이 없으면 자동으로 들어가는 기본값을 지정할 수 있다.

CREATE TABLE 문에서 DEFAULT 키워드를 사용해 지정한다.

CREATE TABLE OrderItems
(
    order_num    INTEGER    NOT NULL,
    order_item    INTEGER    NOT NULL,
    prod_id    CHAR(10)    NOT NULL,
    quantity    INTEGER    NOT NULL DEFAULT 1, -- 기본값으로 1을 설정함
    item_price    DECIMAL(8,2)    NOT NULL
)

기본값은 날짜나 시간 열에서 자주 사용한다. 예를들어 시스템 날짜를 가져오는 함수나 변수를 사용해 행을 삽입하는 시점의 날짜와 시간을 기본값으로 지정한다.

DBMS	함수 / 변수
Db2	CURRENT_DATE
MySQL	CURRENT_DATE() Now()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	GETDATE()
SQLite	date('now')

DBMS별 시스템 날짜를 가져오는 함수/변수

테이블 변경하기

테이블 정의를 업데이트할 때는 ALTER TABLE 문을 사용한다. 모든 DBMS가 지원하지만 변경가능한 항목은 DBMS에 따라 다르다. ALTER TABLE 사용시 주의점을 잘 읽어보자.

• 기본적으로 데이터가 있는 테이블은 변경해서는 안된다. 테이블 설계시 충분한 시간과 예측으로 변경을 최소화해라.

• 모든 DBMS는 존재하는 테이블에 열을 추가하는 것은 허용하지만, 추가하는 열의 데이터형에는 몇 가지 제약을 둔다.

• 다수의 DBMS는 테이블에 있는 열을 제거하거나 변경하는 것을 허용하지 않는다.

• 대부분의 DBMS는 열 이름 변경을 허용한다.

• 다수의 DBMS는 데이터가 있는 열을 변경하는 것은 제한하고, 데이터가 없는 열을 변경하는 것에는 제한 사항을 많이 두지 않는다.

이처럼 존재하는 테이블을 변경하는 것은 간단하지 않고, 일관성도 없다. 처음에 잘짜자 !

ALTER TALBE을 이용하여 테이블 변경시 다음 정보를 반드시 적어야 한다.

• ALTER TABLE 문 뒤 변경할 테이블 이름을 적는다.

• 변경 사항을 나열한다.

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

위 예시는 Vendors 테이블에 vend_phone 열을 추가한다. 위와 같이 데이터형을 반드시 지정해야 한다.

복잡한 테이블 구조를 변경하려면 보통 다음 순서대로 진행한다.

1. 새로운 열 구조를 가진 새 테이블 생성

2. INSERT SELECT 문을 사용해 이전 테이블에 있는 데이터를 새 테이블에 복사한다. 필요시 변환 함수나 계산 필드 사용

3. 새 테이블에 원하는 데이터가 있는지 확인

4. 이전 테이블의 이름 변경 OR 삭제

5. 새 테이블을 이전 테이블의 이름으로 변경

6. 필요시 트리거, 저장 프로시저, 인덱스, 외래 키 등 다시생성

ALTER TABLE 문은 신중히 사용하라.

데이터베이스 테이블 변경은 되돌릴 수 없는 작업이다. 잘못 추가했을 때는 삭제하면 되지만 삭제한 열은 되돌릴 수 없다.

테이블 삭제하기

DROP TABLE 문을 사용해 테이블을 삭제한다.

DROP TABLE CustCopy

실수 방지를 위해 관계 규칙을 만들어라.

다른 테이블과 관련된 테이블은 삭제하지 못하게 규칙을 정할 수 있다. 그 규칙을 삭제하기 전까지 DBMS는 테이블을 삭제하지 못한다. 가능하면 규칙을 사용해 실수로 삭제하는 경우를 방지하자.

테이블 이름 바꾸기

테이블 이름 변경은 DBMS마다 다르게 지원한다.

RENAME - Db2, MariaDB, MySQL, Oracle, PostgreSQL

sp_nename - SQL Server

ALTER TABLE - SQLite

테이블 이름을 바꾸는 기본 문법은 이전 테이블 이름과 새 테이블 이름을 모두 적는 것이다. 하지만 DBMS마다 다르므로 확인하자.

정리하기

• CREATE TABLE 문으로 새로운 테이블 생성하기

• ALTER TABLE 문으로 테이블 정의를 변경하기

• DROP TABLE 문으로 테이블 삭제하기

도전 과제

1. Vendors 테이블에 웹사이트 열을 추가하기. 필드는 URL이 충분히 들어갈 수 있을 정도

1000자면 되것지?

Vendors에 추가된 vend_web

1. Vendors 테이블을 업데이트해 웹사이트 추가하기.

1개만 업데이트해보자 vend_id가 BRE02인 행에 값을 추가한다.

BRE02에 값이 업데이트 되었다.