[정보처리기사 필기] SQL 응용 - 080. DDL 데이터 정의어

1. DDL의 개요

• DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
• 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로서 저장
• DDL의 종류 : CREATE SCHEMA, CREATE DOMAIN, CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE, DROP 등

2. CREATE SCHEMA

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;

• 스키마를 정의하는 명령문
• 스키마의 식별을 위해 스키지마 이름과 소유권자나 허가권자를 정의

3. CREATE DOMAIN

CREATE DOMAIN 도메인명 [AS] 데이터_타입
               [DEFAULT 기본값]
               [CONSTRAINT 제약조건명 CHECK (범위값)];

(*SQL문에서 [대괄호]로 묶은 명령어들은 생략이 가능하다는 의미)

• 도메인을 정의하는 명령문
• 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의할 수 있음
• 정의된 도메인 명은 일반적인 데이터 타입처럼 사용

<SQL에서 지원하는 기본 데이터 타입>

- 정수(Integer) : INTEGER(4Byte 정수), SMALLINT(2Byte 정수)
- 실수(Float) : FLOAT, REAL, DOUBLE PRECISION
- 형식화된 숫자 : DEC(i. j) - i : 전체 자릿수, j : 소수부 자릿수
- 고정길이 문자 : CHAR(n), CHARACTER(n) - n : 문자수
- 가변길이 문자 : VARCHAR(n), CHARACTER VARYING(n) - n : 최대 문자수
- 고정길이 비트열(Bit String) : BIT(n)
- 가변길이 비트열 : VARBIT(n)
- 날짜 : DATE
- 시간 : TIME

<CHAR과 VARCHAR의 차이>

- CHAR : 항상 지정된 크기만큼 기억 장소가 확보
- VARCHAR : 기억 장소의 크기가 지정되어도 필드에 저장된 데이터만큼만 기억 장소가 확보

4. CREATE TABLE

CREATE TABLE 테이블명
               (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
               [, PRIMARY KEY(기본키_속성명, ...)]
               [, UNIQUE(대체키_속성명, ...)]
               [, FOREIGN KEY(외래키_속성명, ...)]
                            [REFERENCES 참조테이블(기본키_속성명, ...)]
                            [ON DELETE 옵션]
                            [ON UPDATE 옵션]
               [, CONSTRAINT 제약조건명] [CHECK (조건식)]);

• 테이블을 정의하는 명령문
• 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정
• CREATE TABLE 명령어의 종류
  • PRIMARY KEY : 기본키로 사용할 속성, 속성의 집합을 지정
  • UNIQUE : 대체키로 사용할 속성, 속성의 집합을 지정하는 것으로 UNIQUE로 지정한 속성은 중복된 값을 가질 수 없음
  • FOREIGN KEY ~ REFERENCES ~
    • 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정
    • 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용
    • ON DELETE / UPDATE
      • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야할 사항을 지정
      • ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야할 사항을 지정
    • ON  DELETE / UPDATE 의 하위 옵션 
      • NO ACTION : 참조 테이블에 변화가 있어도 기본 테이블에는 아무런 조취를 취하지 않음
      • CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경
      • SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경
      • SET DEFAULT : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 기본값으로 변경
  • CONSTRAINT : 제약 조건의 이름을 지정, 이름을 지정할 필요가 없을 경우 CHECK절만 사용하여 속성 값에 대한 제약 조건을 명시
  • CHECK : 속성 값에 대한 제약 조건을 정의
• 다른 테이블을 이용한 테이블의 정의

CREATE TABLE 신규테이블명 AS SELECT 속성명[, 속성명, ...] FROM 기존테이블명;

• 기존 테이블에서 신규 테이블에 그대로 적용되는 유형
  • 추출되는 속성의 데이터 타입과 길이
  • NOT NULL의 정의
• 기존 테이블에서 신규 테이블에 적용되지 않는 유형
  • 제약 조건 : 신규 테이블에 적용되지 않으므로 신규 테이블을 정의한 후 ALTER TABLE 명령을 이용해 제약 조건을 추가
• 기존 테이블의 일부 속성만 신규 테이블로 생성할 수 있음 : 기존 테이블의 모든 속성을 신규 테이블로 생성할 때는 속성명 부분에 '*'를 입력

5. CREATE VIEW

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

• 뷰 (View)를 정의하는 명령문
  • 뷰 : 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블(Virtual Table)
  • 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않음
  • 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행
• SELECT 문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성
• 서브 쿼리인 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없음
• 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용

6. CREATE INDEX

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [, 속성명 [ASC | DESC]])
[CLUSTER];

• 인덱스를 정의하는 명령문
• UNIQUE
  • 사용된 경우 : 중복 값이 없는 속성으로 인덱스를 생성
  • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성
• 정렬 여부 지정
  • ASC : 오름차순 정렬
  • DESC : 내림차순 정렬
  • 생략된 경우 : 오름차순으로 정렬
• CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨

7. ALTER TABLE

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• 테이블에 대한 정의를 변경하는 명령문
• ADD : 새로운 속성(열)을 추가할 때 사용
• ALTER : 특정 속성의 Default 값을 변경할 때 사용
• DROP COLUMN : 특정 속성을 삭제할 때 사용

8. DROP

DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

• 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문
• DROP SCHEMA : 스키마를 제거
• DROP DOMAIN : 도메인을 제거
• DROP TABLE : 테이블을 제거
• DROP VIEW : 뷰를 제거
• DROP INDEX : 인덱스를 제거
• DROP CONSTRAINT : 제약 조건을 제거
• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거, 주 테이블의 데이터 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용
• RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소