[SQLD] #013 SQL 기본 - DDL

목차

1. 데이터 유형

2. CREATE TABLE

3. ALTER TABLE

4. RENAME TABLE

5. DROP TABLE

6. TRUNCATE TABLE

 

 

---

 

 

 

1. 데이터 유형

데이터 유형 : 테이블에 특정 자료를 입력할 때, 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준 

 

 

 

VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다. 따라서 길이가 다양한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다. 저장 측면에서도 CHAR 유형보다 작은 영역에 저장할 수 있다. 

 

CHAR 에서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다. 우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다. 그리고 앞에서부터 한 문자씩 비교한다. 그렇기 때문에 끝의 공백만 다른 문자열은 서로 같다고 판단한다. 

하지만 VARCHAR 유형에서는 맨 처음부터 한 문자씩 비교하고, 공백도 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

 

 

영문이름의 경우, 주민등록번호처럼 자료들이 고정된 길이의 문자열을 가지지 않으므로 VARCHAR 유형을 적용하는게 좋다. 

 

 

 

 

 

 

 

 

 

 

2. CREATE TABLE

1. 테이블과 칼럼 정의

테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(FK) 중에 하나를 선정하여 기본키 칼럼으로 지정한다. 기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 만들어질 수 있다. 

그리고 테이블과 테이블간에 정의된 관계는 PK와 FK를 활용하여 설정하도록 한다. 

 

 

 

 

 

2. CREATE TABLE

테이블을 생성하는 구문 형식은 다음과 같다. 

CREATE TABLE 테이블 이름 
( 칼럼명1 DATATYPE [DEFAULT 형식],
  칼럼명2 DATATYPE [DEFAULT 형식]
);

 

다음은 테이블 생성시 주의해야 할 몇가지 규칙이다. 

 

- 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다.

- 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

- 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다. 

- 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어 지정한다. 

- 각 칼럼들은 콤마로 구분되고, 테이블 생성문의 끝은 항상 세미콜론으로 끝난다.

- 칼럼에 대해서는 다른 테이블까지 고려하여 DB 내에서는 일관성있게 사용하는 것이 좋다. 

- 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다. 

- 테이블명과 칼럼명은 반드시 문자로 시작해야하고, 벤더별로 길이에 대한 한계가 있다. 

- 벤더에서 사전에 정의한 예약어는 쓸 수 없다.

- A-Z, a-z, 0-9, _, $, # 문자만 허용된다. 

- 테이블 생성시 대소문자 구분은 하지 않는다. 하지만 기본적으로 대문자를 사용한다.

- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다. 

- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다. 

 

 

 

 

 

 

3. 제약조건 (CONSTRAINT)

제약조건 : 사용자가 원하는 조건의 데이터만 유지하기 위한 보편적인 방법으로, 테이블의 특정 칼럼에 설정하는 제약

 

테이블 생성시 제약조건을 반드시 기술할 필요는 없지만, 이후에 ALTER TABLE을 이용해서 추가, 수정하는 경우 처리 과정이 쉽지 않으므로 초기 테이블 생성시에 충분한 검토를 해야 한다. 

 

 

DEFAULT 의 의미 : 데이터 입력시에 칼럼의 값이 지정되어있지 않을 경우 DEFAULT를 사전에 설정할 수 있다. 데이터 입력시 명시된 값을 지정하지 않은 경우에 NULL 값이 입력되고, DEFAULT 값을 정의했다면 해당 칼럼에 NULL 값이 입력되지 않고 사전에 정의된 기본 값이 자동으로 입력된다. 

 

 

 

 

 

 

 

 

 

 

 

4. 생성된 테이블 구조 확인

Oracle : "DESCRIBE 테이블명;" 또는 "DESC 테이블명;"

SQL Server : "sp_help 'dbo.테이블명'" 

 

 

 

 

 

 

 

 

 

5. SELECT 문장을 통한 테이블 생성 사례

DML 문장중에 SELECT 문을 활용해서 테이블을 생성할 수 있는 방법(CTAS)이 있다. 기존 테이블을 활용한 CTAS 방법을 이용할 수 있으면 칼럼별로 데이터 유형을 다시 재정의하지 않아도 되는 장점이 있다. 그러나 CTAS 기법 사용시 주의할 점은 기존 테이블의 제약조건중에 NOT NULL만 새로운 복제 테이블에 적용이 되고, 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 없어진다는 점이다. 

 

제약조건을 추가하기 위해서는 뒤에 나오는 ALTER TABLE 기능을 사용해야한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

3. ALTER TABLE

테이블을 생성하면 처음 생성한 테이블 구조를 그대로 유지하는 것이 최선이지만, 테이블을 사용하는 도중에 변경해야 할 일들이 발생할 수도 있다. 이 경우 주로 칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업을 진행하게 된다. 

 

1. ADD COLUMN

다음은 기존 테이블에 필요한 칼럼을 추가하는 명령이다. 

주의할 것은 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며, 칼럼의 위치를 지정할 수는 없다. 

ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

 

 

 

 

 

2. DROP COLUMN

DROP COLUMN은 테이블에서 필요 없는 칼럼을 삭제할 수 있으며, 데이터가 있거나 없거나 모두 삭제 가능하다. 

한번에 하나의 칼럼만 삭제가 가능하고 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야한다. 삭제된 칼럼은 복구가 불가능하다.

ALTER TABLE 테이블명 DROP COLUMN 삭제할칼럼명;

 

 

 

 

 

 

3. MODIFY COLUMN

테이블에 존재하는 칼럼에 대해서 ALTER TABLE 명령을 이용해 칼럼의 데이터 유형, 디폴트값, NOT NULL 제약조건에 대한 변경을 포함할 수 있다. 

다음은 테이블의 칼럼에 대한 정의를 변경하는 명령이다. 

-- Oracle
ALTER TABLE 테이블명 MODIFY
( 칼럼명1 데이터유형 [DEFAULT] [NOT NULL],
  칼럼명2 데이터유형 ...
);


-- SQL Server
ALTER TABLE 테이블명 ALTER 
( 칼럼명1 데이터유형 [DEFAULT] [NOT NULL],
  칼럼명2 데이터유형 ...
);

 

 

칼럼 변경 시 고려사항

- 해당 칼럼의 크기를 늘릴 수는 있지만 줄일 수는 없다. (기존 데이터의 훼손 우려)

- 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

- 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다. 

- 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다. 

 

 

 

 

RENAME COLUMN

테이블을 생성하면서 만들어졌던 칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우에 사용할 수 있는 명령어다.

-- ORACLE
ALTER TABLE 테이블명 RENAME COLUMN 변경전컬럼명 TO 변경후컬럼명;

-- SQL Server
sp_rename 'dbo.테이블명.변경전컬럼명', '변경후컬럼명', 'COLUMN';

 

 

 

 

 

 

4. DROP CONSTRAINT

테이블 생성 시 부여했단 제약조건을 삭제하는 명령어 

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

 

 

 

 

 

5. ADD CONSTRAINT

테이블 생성 후 특정 칼럼에 제약조건을 추가하는 명령어

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

 

 

 

 

 

 

 

 

 

4. RENAME TABLE

RENAME 명령어를 사용하여 테이블의 이름을 변경할 수 있다. 

-- ORACLE
RENAME 변경전테이블명 TO 변경후테이블명;

-- SQL Server
sp_rename 변경전테이블명, 변경후테이블명;

 

 

 

 

 

 

 

 

 

5. DROP TABLE

DROP TABLE은 불필요한 테이블을 삭제하는 명령어다. 

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

 

 

 

 

 

 

 

 

6. TRUNCATE TABLE

TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 

TRUNCATE TABLE 테이블명;

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.