테이블 생성과 조작

목표

• 테이블 생성, 변경 그리고 삭제하는 방법에 대해 알아보자

목차

1. [테이블 생성](#테이블 생성)
2. [테이블 변경](#테이블 변경)
3. [테이블 삭제](#테이블 삭제)
4. [테이블 이름 변경](#테이블 이름 변경)

테이블 생성

일반적으로 데이터베이스 테이블을 생성하는 데는 두 가지 방법이 있다.

• 대다수 DBMS는 데이터베이스 테이블을 생성하고 관리할 수 있는 대화형 관리 툴을 제공한다.
• SQL 문으로 테이블을 직접 생성하고 관리한다.

테이블을 생성하려면 CREATE TABLE 문법을 사용한다. DBMS에서 제공하는 대화형 관리 툴을 사용하더라도 실제로는 SQL 문을 사용한다는 것을 기억할 필요가 있다.

 

이 경우 SQL문을 직접 쓰는 대신에 관리 툴 인터페이스가 SQL문을 생성하고 실행해준다.(테이블을 변경할 때도 마찬가지이다.)

 

테이블 생성 문법은 SQL 실행 환경마다 매우 다르기 때문에 정확한 문법과 지원하는 기능에 대해서는 DBMS 매뉴얼을 살펴보자

기본 테이블 생성

CREATE TABLE을 사용해서 테이블을 생성할 때는 다음 규칙이 준수되어야 한다.

• CREATE TABLE 문 뒤에 새로운 테이블 이름을 적는다.
• 테이블 이름과 정의를 콤마(,)로 구분하여 적는다.
• 일부 DBMS에서는 테이블 위치도 명시해야 한다.(테이블을 생성할 특정 데이터베이스)

-- --------------------- -- Create Products table -- --------------------- 
CREATE TABLE Products (  
prod_id    char(10)      NOT NULL ,   
vend_id    char(10) 
NOT NULL ,   prod_name  char(255)     NOT NULL ,   
prod_price decimal(8,2)  NOT NULL ,
prod_desc  text          NULL  
);  

DB2 에서는 null을 지워야 한다.

SQL 문은 공백 문자를 무시하기 때문에 SQL 문을 하나의 긴 문장으로 작성하거나, 여러 줄로 나눠서 입력할 수 있다. 테이블을 생성할 때 존재하는 테이블 이름을 사용하면 안 된다.

실수로 덮어쓰는 것을 막기 위해, SQL은 테이블을 직접 삭제하고 다시 만들도록 유도한다. 따라서 이미 존재하는 테이블 이름을 사용하여 테이블을 덮어쓸 수는 없다.

NULL 값 사용하기

모든 테이블 열은 NULL이나 NOT NULL 열이고, 그것은 테이블이 생성되는 시점에 테이블 정의에서 결정된다.

-- ------------------- -- Create Orders table -- ------------------- 
CREATE TABLE Orders (   
order_num  int      NOT NULL ,   
order_date datetime NOT NULL ,   
cust_id    char(10) NOT NULL  
);

Orders의 세열은 모두 필수이기 때문에 각 열에 NOT NULL이라는 키워드가 적혀 있다.

이 키워드는 값이 없는 열이 삽입되는 것을 막고, 누군가 값 없이 삽입하려고 하면 에러를 발생시킨다.

-- -------------------- -- Create Vendors table -- -------------------- 
CREATE TABLE Vendors (   
vend_id      char(10) NOT NULL ,   
vend_name    char(50) NOT NULL ,   
vend_address char(50) NULL ,   
vend_city    char(50) NULL ,   
vend_state   char(5)  NULL ,   
vend_zip     char(10) NULL ,   
vend_country char(50) NULL  
);

대부분의 DBMS는 NOT NULL이 없으면 NULL로 인식하지만, 모든 DBMS가 다 그런 것은 아니다. 일부 DBMS는 NULL 키워드를 쓰지 않으면 에러가 발생한다.

[!NOTE]
NULL 값을 허용하지 않는 열만 기본 키로 사용할 수 있다.
아무런 값을 가지지 않을 수 있는 열은 고유한 구별자로 사용할 수 없기 때문이다.

NULL 이해하기

NULL 값과 빈 문자열을 혼동해서는 안된다. NULL은 아직 값이 없는 것이지 빈 문자열이 아니기 때문이다. 두 개의 작은따옴표(’ ', 둘 사이에 아무것도 없는 작은따옴표)는 NOT NULL 열에 허용되는 값이다.

 

빈 문자열은 유효한 값이므로 값이 없다는 의미가 아니다.

NULL 값은 NULL 키워드로 명시하지만, 빈 문자열로는 표시하지 않는다는 점에 주의하자.

기본값 지정하기

SQL에서는 행을 삽입할 때 값이 없으면 자동으로 들어가는 기본값을 지정할 수 있다. 기본 값은 CREATE TABLE 문에서 열을 정의할 때 DEFAULT 키워드를 사용해서 지정한다.

-- ----------------------- -- Create OrderItems table -- ----------------------- 
CREATE TABLE OrderItems (   
order_num  int          NOT NULL ,   
order_item int          NOT NULL ,   
prod_id    char(10)     NOT NULL ,   
quantity   int          NOT NULL DEFAULT 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’)

테이블 변경

테이블 정의를 업데이트할 때 ALTER TABLE 문을 사용한다. 모든 DBMS가 ALTER TABLE을 지원하지만, 바꿀 수 있는 항목은 DBMS에 따라 다르다.

 

다음은 ALTER TABLE을 사용할 때 고려해야 할 점이다.

• 기본적으로 데이터가 있는 테이블은 변경해서는 안된다. 테이블을 설계할 때 시간을 충분히 할애하여, 미래의 필요를 예측하도록 하자. 나중에 많은 변경이 필요하지 않도록 해야 한다는 뜻이다.
• 모든 DBMS는 존재하는 테이블에 열을 추가한느 것은 허용하지만, 추가하는 열의 데이터형에 몇 가지 제약을 둔다.
• 다수의 DBMS는 테이블에 있는 열을 제거하거나 변경하는 것을 허용하지 않는다.
• 대부분의 DBMS는 열 이름의 변경은 허용한다.
• 다수의 DBMS는 데이터가 있는 열을 변경하는 것은 제한하고, 데이터가 없는 열을 변경하는 것에는 제한 사항을 많이 두지 않는다.

이처럼 존재하는 테이블을 변경하는 것은 간단하지도, 일관성이 있지도 않다. 어떤 항목을 바꿀 수 있는지 정확히 확인하려면, 사용하는 DBMS의 매뉴얼을 살펴보자

 

ALTER TABLE을 이용하여 테이블을 변경하기 위해서는 다음 정보를 반드시 적어야 한다.

• ALTER TABLE 문 뒤에 변경할 테이블 이름을 적는다.
  (테이블이 존재하지 않으면 에러가 발생한다.)
• 변경할 사항을 나열한다.

존재하는 테이블에 열을 추가하는 것은 모든 DBMS가 지원하는 유일한 작업이다.

ALTER TABLE Vendors ADD vend_phone CHAR(20); 

열을 수정하거나 삭제하는 것 또는 제약 사항이나 키를 추가하는 등의 다른 변경 작업도 비슷한 문법을 사용한다.

아래 예제는 모든 DBMS에서 작동하지 않는다.

ALTER TABLE Vendors DROP COLUMN vend_phone; 

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

1. 새로운 열 구조를 가진 새 테이블을 생성한다.
2. INSERT SELECT 문을 사용하여 이전의 테이블에 있는 데이터를 새로운 테이블에 복사한다. 필요하다면 변환 함수나 계산 필드를 사용한다.
3. 새로운 테이블에 원하는 데이터가 있는지 확인한다.
4. 이전 테이블의 이름을 변경한다(혹은 과감히 삭제)
5. 새로운 테이블을 이전에 사용한 테이블 이름으로 변경한다.
6. 필요하다면 트리거, 저장 프로시저, 인덱스, 외래 키 등을 다시 생성한다.

[!NOTE] ALTER TABLE과 SQLite

SQLite는 ALTER TABLE을 사용하여 수행하는 작업에 제한을 둔다.
중요한 제한 중 한 가지는 기본 키나 외래 키로 정의한 열에는 ALTER TABLE을 사용할 수 없다는 점이다.
기본 키나 외래 키 열은 최초 테이블을 생성하는 시점에 정의되어야 한다.

ALTER TABLE 문을 사용할 때는 매우 신중해야 한다.
작업 전에 완벽히 백업(스키마와 데이터 모두)했는지 확인한다. 데이터베이스 테이블 변경은 되돌릴 수 없는 작업이다.

테이블 삭제

내용을 삭제하는 게 아닌 실제 테이블 전체를 삭제하는 것은 매우 간단하다.

DROP TABLE CustCopy; 

테이블을 삭제하는게 간단한 만큼 실수로 테이블을 삭제하지 못하도록 관계 규칙을 사용할 필요가 있다.

많은 DBMS가 여타 다른 테이블과 관련된 테이블은 삭제하지 못하게 규칙을 정할 수 있도록 하고, 규칙을 설정하면 그 관계를 삭제하기 전까지 DBMS가 관련된 테이블을 삭제하지 못하게 막는다.

테이블 이름 변경

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

이 작업에는 어려운 것도 빠른 표준도 없다 Db2 MariaDB MySQL Oracle PostgreSQL 을 사용한다면 RENAME 문자를 사용할 수 있고 SQL Server 사용자는 sp_rename이라는 저장 프로시저를 사용할 수 있다. SQLite는 ÀLTER TABLE문으로 테이블 이름을 바꿀 수 있다.

 

테이블 이름을 바꾸기 위한 기본 문법은 이전의 테이블 이름과 새로운 테이블 이름을 모두 적는 것이지만 DBMS마다 다르므로 지원하는 매뉴얼을 살펴보자.

추가

ALTER TABLE Vendors 
ADD vend_web CHAR(100);  

UPDATE Vendors 
SET vend_web = 'https://google.com/' 
WHERE vend_id = 'DLL01';

---