[DB] 데이터베이스 언어 SQL(Structured Query Language) - DDL

SQL 기능에 따른 분류

- 데이터 정의어(DDL : Data Definition Language)

• 테이블 생성, 변경, 제거
• CREATE, ALTER, DROP 문

- 데이터 조작어(DML : Data Manipulation Language)

• 생성된 테이블에 데이터를 검색, 삽입, 수정, 삭제
• SELECT, INSERT, UPDATE, DELETE 문

- 데이터 제어어(DCL : Data Control Language)

• 사용자 접근 제어, 백업과 회복, 동시성 제어 등
• GRANT, REVOKE, COMMIT, ROLLBACK 문

데이터 정의어(DDL)

1. CREATE TABLE 문 : 테이블 생성

- 기본 문법

CREATE TABLE 테이블이름
(
    속성이름 데이터타입 제약조건,
    속성이름 데이터타입 제약조건,
    ....
    속성이름 데이터타입 제약조건
);

 

- 데이터 타입(Oracle 기준)

(1) 문자형

데이터 타입	정의
CHAR(n)	고정 길이 데이터 타입(최대 2000byte) 지정된 길이보다 짧은 데이터가 입력될 시 나머지 공간은 공백으로 채워진다
VARCHAR2(n)	가변 길이 데이터 타입(최대 4000byte) 지정된 길이보다 짧은 데이터가 입력될 시 나머지 공간은 채우지 않는다
LONG	가변 길이 데이터 타입(최대 2Gbyte)
NCHAR(n)	고정 길이 유니코드 데이터 타입(최대 2000byte)
NVARCHAR2(n)	가변 길이 유니코드 데이터 타입(최대 4000byte)

(2) 숫자형

데이터 타입	정의
NUMBER(P, S)	가변숫자(10진수 기준)이고 최대 22byte이다 P(정수 부분) : 1~38 (default = 38) / S(소수 부분) : -84~127 (default = 0)
FLOAT(P)	NUMBER의 하위 타입, 2진수 기준 최대 22byte이다 P : 1~128 (default = 22)

(3) 날짜형

데이터 타입	정의
DATE	BC4712년 1월 1일 ~ 9999년 12월 31일까지 입력 가능 년단위 ~ 초단위까지 입력가능 default = YYYY/MM/DD
TIMESTAMP	년단위 ~ 밀리초단위까지 입력 가능

 

- 제약 조건(Constraints)

제약조건	정의	Example
NOT NULL	값을 무조건 입력 / NULL을 허용하지 않는다	v1 VARCHAR2(10) NOT NULL
UNIQUE	해당 속성에 중복 값을 허용하고 싶지 않을 경우	v2 VARCHAR2(10) UNIQUE
PRIMARY KEY	기본키를 지정 (테이블 당 1개)	v3 VARCHAR2(10) PRIMARY KEY
FOREGIN KEY	외래키를 지정(references 키워드랑 같이 씀)	v4 VARCHAR2(10) REFERENCES 참조할_테이블(참조_테이블 기본키)
CHECK	속성에 입력되는 데이터를 체크해서 특정 조건에 맞는 데이터만 입력받고 싶을 때 사용	1) v5 VARCHAR2(10) CHECK(v5 IN ('A', 'B') 2) v5 VARCHAR2(10) CHECK(v5 BETWEEN 1 AND 10)
DEFAULT	NULL값이 들어올 경우, 디폴트에 지정된 값 입력	v6 VARCHAR2(10) DEFAULT '홍길동'

 

※ Example

CREATE TABLE test(
    id NUMBER(3) PRIMARY KEY,
    name VARCHAR2(10) DEFAULT '홍길동',
    age NUMBER(10) NOT NULL,
    address CHAR(7) NOT NULL CHECK (address LIKE '%-%'),
    jumin CHAR(14) NOT NULL,
    start_date CHAR(10),
    end_date CHAR(10) CHECK (start_date <= end_date),
    value10 VARCHAR2(10) UNIQUE
);

 

- 제약 조건 정의

1. 컬럼 레벨 제약 조건

• 칼럼 별로 제약 조건 정의
• NOT NULL 제약 조건은 컬럼 레벨에서만 가능

CREATE TABLE test2(
    id NUMBER(3),
    jumin CHAR(14) CONSTRAINT non_jumin NOT NULL
);

2. 테이블 레벨 제약 조건 

• 하나 이상의 컬럼을 참조할 경우에 사용
• NOT NULL을 제외한 나머지 제약조건 정의 가능

CREATE TABLE test3(
    pk2 NUMBER(3),
    fk2 NUMBER(3),
    CONSTRAINT pk_pk2
    PRIMARY KEY(pk2)
);

 

2. ALTER TABLE 문 : 테이블 수정

- 생성된 테이블의 속성과 제약조건을 변경

- 테이블에 이미 데이터가 있는 상태에서, 새로운 속성이 추가되면 추가된 속성의 값은 NULL 값이 된다

 

- 기본 문법

ALTER TABLE 테이블이름
[ADD 속성이름 데이터 타입;] -- 새로운 속성 추가
[MODIFY 속성이름 데이터 타입;] -- 속성 데이터타입 변경
[DROP COLUMN 속성이름;] -- 속성 삭제하기
[ADD [CONSTRAINT 제약조건이름] 제약조건 (속성이름);] -- 속성에 제약조건 추가
[DROP CONSTRAINT 제약조건이름;] -- 해당 제약조건 삭제

 

3. DROP TABLE 문 : 테이블 완전 삭제

- 테이블 구조와 데이터 모두 삭제

- 데이터만 삭제하려면 DELETE 문 사용

 

- 기본 문법

DROP TABLE 테이블이름;

 

4. TRUNCATE TABLE 문 : 테이블 투플 완전 삭제

- 테이블 생성 후 INSERT 된 투플 값들을 전부 삭제

 

- 기본 문법

TRUNCATE TABLE 테이블이름;

 

DDL : 무결성 제약조건

- 데이터 무결성 (Data Integrity)

• 데이터를 결함이 없는 상태, 즉 정확하고 유효하게 유지하는 것

- 무결성 제약조건 (Integrity Constraints)

• 데이터의 무결성을 보장하고 일관된 상태로 유지하기 위한 규칙
• 무결성 제약 조건을 만족하는 경우 DB에 저장된 모든 데이터가 정확한 상태로 저장될 수 있음을 보장
• DB의 Data에 어떤 변경이 있을 때, DBMS가 자동적으로 무결성 제약조건을 검사하므로 응용 프로그램은 제약조건을 검사할 필요가 없다

종류	설명	방법
도메일 무결성(Domain Integrity)	속성 값이 특정한 범위의 값만 가능	데이터 타입, NULL, CHECK
개체 무결성(Entity Integrity)	각 투플은 중복되지 않아야 한다	PRIMARY KEY, UNIQUE
참조 무결성(Referential Integrity)	다른 속성과의 관계를 위반하지 않아야 한다	FOREIGN KEY

 

1. 도메인 무결성 (NULL / NOT NULL / CHECK)

- NULL / NOT NULL

• NOT NULL : 필수 입력을 위해, NULL값을 허용하지 않겠다는 의미
• 제약조건을 명시하지 않으면 NULL을 허용하는 것으로 간주

- CHECK

• 데이터를 삽입하거나 수정할 때 열의 값이 정의된 규칙에 부합되는지를 검사 (열에서 허용되는 값을 제한)
• SELECT문의 WHERE에 들어가는 조건을 쓰면 됨 (sub query는 안됨)

※ Example

CREATE TABLE test(
    id NUMBER(3),
    jumin CHAR(14) NOT NULL,
    zip CHAR(7) NOT NULL CHECK(zip LIKE '%-%'),
    start_date CHAR(10),
    end_date CHAR(10),
    CONSTRAINT CHK_dates CHECK (start_date <= end_date)
);

 

2. 개체 무결성 (PRIMARY KEY / UNIQUE)

- PRIMARY KEY (기본키)

• 테이블 당 최대 1개의 기본키 제약조건만 설정
• 기본키의 값은 유일해야 하며, NULL을 허용하지 않는다

- UNIQUE

• 데이터의 유일성을 보장
• 중복되는 데이터 존재 X
• PRIMARY KEY와 유사하지만, NULL을 허용한다
• 한 테이블에 여러 개의 UNIQUE 제약조건 가능
• 주로 기본키로 지정되지 않은 후보키에 적용된다

CREATE TABLE test(
    v1 VARCHAR2(10) PRIMARY KEY,
    v2 VARCHAR2(20) UNIQUE
);

 

3. 참조 무결성 (FOREIGN KEY)

- FOREIGN KEY (외래키)

• 두 릴레이션의 연관된 투플들 사이의 일관성을 유지하는 데 사용
• 외래 키 속성과 그것이 참조하는 기본키 속성의 이름이 달라도 도메인은 같아야 한다
• 외래 키를 가진 릴레이션 = 자식 릴레이션 / 외래 키가 참조하는 기본키를 가진 릴레이션 = 부모 릴레이션

※ FOREIGN KEY : CASCADE

- 참조되는 테이블(부모 테이블)의 데이터가 변경되었을 때 이를 참조하는 쪽(자식 테이블)에서도 자동으로 변동되도록 설정

CREATE TABLE test1(
    pk1 NUMBER(3) PRIMARY KEY,
    up1 NUMBER(3)
)

CREATE TABLE test2(
    pk2 NUMBER(3) PRIMARY KEY,
    fk2 NUMBER(3),
    CONSTRAINT fk_fk2
    FOREIGN KEY (fk2) REFERENCES test1(pk1)
    ON DELETE CASCADE
);