[SQLD] 13. DDL(Data Definition Language)

제 2절 DDL(Data Definition Language)

1. 데이터유형

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

- 선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 데이터베이스는 에러 발생 

- ANSI / ISO 숫자타입 : NUMERIC Type의 하위개념인 NUMERIC, DECIMAL, DEC, SMALLINT, INTGER, INT, BIGINT, FLOAT, REAL, DOUBLE PRECISION

- SQL Server, Sybase 숫자타입 : 작은 정수형, 정수형, 큰 정수형, 실수형, MONEY, SMALLMONEY 등

- Oracle 숫자타입 : NUMBER

- ANSI / ISO 그 외 데이터타입 : Binary String Type, Binary Large Object String Type, National Character String Type, Boolean Type

- 자주 쓰이는 데이터 유형

VARCHAR 유형은 가변길이이므로 필요한 영역은 실제 데이터 크기 뿐이다. 그렇기 때문에 길이가 다양한 칼럼과 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합하다. 저장 측면에서도 CHAR 유형보다 데이터공간을 절약할 수 있다.

여기서 CHAR와 VARCHAR 유형은 비교 방법의 차이가 있다.

CHAR 유형에서는 문자열을 비교할때 공백(BLANK)을 채워서 비교하는 방법을 사용한다. 공백 채우기 비교에서는 우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한다. 그리고 앞에서부터 한문자씩 비교를 시작한다. 아래는 그 예시를 보여준다.

'AA' = 'AA     '

그에 반해 VARCHAR 유형은 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

'AA' != 'AA    '

2. CREATE TABLE

가. 테이블과 칼럼 정의

테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들 중에 하나를 선정하여 기본키 칼럼으로 지정한다. 선수 테이블은 예를들어 선수 ID 라는 칼럼이 기본키로 적당할 것이다.

기본키는 단일칼럼이 아닌 여러 개의 칼럼으로도 만들어질 수 있다. 그리고 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다. 

나. CREATE TABLE

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

CREATE TABLE 테이블 이름 (

칼럼명 1    데이터타입    [디폴트 형식],

칼럼명 2    데이터타입    [디폴트 형식],

칼럼명 3    데이터타입    [디폴트 형식]

);

- 테이블 명은 적절한 이름을 사용하며 단수형이 좋다.

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

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

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

- 각 칼럼들은 콤마로 구분되고, 마지막 칼럼은 콤마를 쓰지 않는다.

- 테이블 생성문의 끝은 항상 세미콜론으로 끝난다.

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

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

- 테이블명과 칼럼명은 숫자가 아닌 문자로 시작해야하고, 길이에 대한 한계를 가진다.

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

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

- 테이블 생성시 대/소문자 구분은 하지 않고, 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

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

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

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

- 칼럼 LEVEL 정의 방식 : 칼럼의 데이터 유형 뒤에 NOT NULL과 같은 제약조건을 정의

- 테이블 LEVEL 정의 방식 : 테입르 생성 마지막에 모든 제약조건을 기술

- 테이블명이 잘못된 사례 

다. 제약조건(CONSTRAINT)

제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.

테이블을 생성할 때 제약조건을 반드시 기술 할 필요는 없지만, 이후에 ALTER TABLE을 이용해서 추가, 수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기 테이블 생성 시점부터 적합한 제약조건을 설정해줌으로서 에러를 예방하도록 한다.

- 제약조건의 종류

- NULL의 의미

NULL(ASCII 코드 00번)은 공백(BLANK, ASCII 코드 32번)이나 숫자 0(ZERO, ASCII 48)과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다르다. NULL은 아직 정의되지 않은 미지의 값 이거나 현재 데이터를 입력하지 못하는 경우를 의미한다.

- DEFAULT의 의미

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

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

Oracle : " DESCRIBE 테이블명; " or " DESC 테이블명; " 

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

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

DML 문장인 SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법(CTAS, Create Table ~ As Select ~)이 있다.  기존 테이블을 이용한 CTAS 방법을 이용할 수 있다면 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점이 있다.

그러나 이 기법을 사용할 때 기존 테이블의 제약 조건 중 NOT NULL만 새로운 복제 테이블에 적용되고 나머지 제약조건인 기본키, 고유키, 외래키, CHECK 등의 다른 제약조건은 없어진다. ALTER TABLE 기능을 사용하여야한다.

Oracle

CREATE TABLE TEAM_TEMP

AS SELECT * FROM TEAM;

테이블이 생성되었다

SQL Server

SELECT * INTO TEAM_TEMP FROM TEAM;

3. ALTER TABLE

한 번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다. 처음의 테이블 구조를 그대로 유지하는 것이 최선이지만, 업무적인 요구사항이나 시스템 운영상 테이블을 사용하는 도중에 변경해야 할 일들이 발생할 수 있는데 이 때 ALTER 명령어를 쓰게 된다. 

가. ADD COLUMN

ALTER TABLE 테이블명

ADD 추가할칼럼명 데이터유형;

나. DROP COLUMN 

ALTER TABLE 테이블명 

DROP COLUMN 삭제할칼럼명;

다. MODIFY COLUMN

ALTER TABLE 테이블명

MODIFY (칼럼명1 데이터유형 [DEFAULT 식][NOT NULL], 칼럼명2 데이터유형 ...);

- 기존의 데이터가 훼손될 수 있기 때문에 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다.

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

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

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

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

- RENAME COLUMN 

ALTER TABLE 테이블명

RENAME COLUMN 변경해야할칼럼명 TO 새로운칼럼명;

라. DROP CONSTRAINT

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

마. ADD CONSTRAINT

ALTER TABLE 테이블명

ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

4. RENAME TABLE

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

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

SQL Server에서는 sp_rename을 이용하여 테이블 이름을 변경할 수 있다.

sp_rename 변경전 테이블명, 변경후 테이블명;

5. DROP TABLE

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제하게 된다. CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다. SQL Server에서는 CASCADE 옵션이 존재하지 않으며, 테이블을 삭제하기 전에 참조하는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제해야한다. 

6. TRUNCATE TABLE

TRUNCATE TABLE은 테이블 자체가 삭제 되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고, 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다

TRUNCATE TABLE 테이블명;

DROP TABLE의 경우는 테이블 자체가 없어지기 때문에 테이블 구조를 확인할 수 없지만 트렁케이트는 테이블 구조를 그대로 유지한 채 데이터만 전부 삭제하는 기능이다. 

TRUNCATE TABLE의 경우 데이터를 일괄 삭제하는 명령어로 DML로 분류할 수 있지만, 내부 처리방식이나 Auto Commit 특성 등으로 인해 DDL로 분류하였다.

그러나 DELETE와 TRUNCATE는 처리하는 방식이 다르기 때문에 시스템 부하가 적은 TRUNCATE TABLE을 권고한다. 그러나 이 경우 정상적인 복구가 불가능하므로 주의해야 한다.