데이터베이스 DDL

DDL(Data Define Language)의 개념

DDL은 스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용하는 언어이다.

CREATE

1. 스키마(Schema)

스키마는 하나의 사용자에 속하는 테이블과 기타 구성 요소 등을 그룹짓기 위한 것이다.

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

2. 도메인(Domain)

- 도메인이란 하나의 속성이 취할 수 있는 동일한 타입의 원자값들의 집합이다.

- 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의   값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의할 수 있다.

- 정의된 도메인명은 일반적인 데이터 타입처럼 사용한다.

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

- 데이터 타입 : SQL에서 지원하는 데이터 타입

- 기본 값 데이터를 입력하지 않았을 때 자동으로 입력되는 값

예제] 성별을 '남', '여' 와 같이 정해진 1개의 문자로 표현되는 도메인 
SEX를 정의하는 SQL문을 작성
 
CREATE DOMAIN SEX CHAR(1)s
       DEFAULT '남'
       CONSTRAINT VALD-SEX CHECK (VALUE IN('남'.'여'));

3. 테이블(Table)

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

- 기본 테이블에 포함될 모든 속성에 대하여 속성명, 속성의 데이터 타입, NOT NULL   을 지정한다.

- PRIMARY KEY :  기본키로 사용할 속성 또는 속성의 집합을 지정

- UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 UNIQUE로 

  지정한 속성은 중복된 값을 가질 수 없다.

- FOREIGN KEY ~ REFERENCES ~ : 외래키 속성과 참조 테이블에 관한 정보를 지정

  한다. 외래키가 지정되면 참조 무결성의 CASCADE 법칙이 적용된다

- ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 

  사항을 지정한다. 옵션에는 NO ACTION, CASCADE, SET NULL, SET DEFAULT가 

  있다.

- ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취

  해야 할 사항을 지정한다. 옵션에는 NO ACTION, CASCADE, SET NULL, 

  SET DEFAULT 가 있다.

▶ NO ACTION : 참조 테이블에 변화가 있어도 기본 테이블에는 아무 조취를 취하

    지 않는다.

▶ CASCADE : 참조 테이블의 튜플이 사제되면 기본 테이블의 관련 튜플도 삭제되

    고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경된다.

▶ SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 과련 튜플의 속성 값을 

    NULL로 변경한다.

▶ SET DEFAULT : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 

    값을 기본값으로 변경한다.

- CONSTRAINT : 제약 조건의 이름을 지정한다.

- CHECK : 속성 값에 대한 제약 조건을 지정한다.

예제] 이름, 학번, 전공, 성별, 생년월일로 구성된 <학생>테이블을
정의하는 SQL 문을 작성하시오. 단, 제약 조건은 다음과 같다.
 
- 이름은 NULL이 올 수 없고, 학번은 기본키다.
- 전공은 <학과> 테이블의 학과코드를 참조하는 외래키로 사용된다.
- <학과> 테이블에서 삭제가 일어나면 관련된 튜플의 전공 값을
  NULL로 만든다.
- <학과> 테이블에서 학과코드가 변경되면 전공 값도 같은 값으로
   변경한다.
- 생년월일은 1980-01-01 이후의 데이터만 저장할 수 있다.
- 제약 조건의 이름은 '생년월일제약'으로 한다.
- 각 속성의 테이터형은 적당하게 지정한다. 단 성별은 도메인
  'SEX'를 사용한다.
 
 
CREATE TABLE 학생
       (이름 VARCHAR(15) NOT NULL
       , 학번 CHAR(8)
       , 전공 CHAR(5)
       , 성별 SEX
       , 생년월일 DATA
       , PRIMARY KEY(학번)
       , FOREIGN KEY(전공) REFERENCES 학과(학과코드)
            ON DELETE SET NULL
            ON UPDATE CASCADE 
       , CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01'));

4. 뷰(View)

뷰는 하나 이상의 기본 테이블로 유도되는 이름을 갖는 가상 테이블이다.

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

- SELECT 문을 서브쿼리로 사용하여 SELECT 문의 결과로서 뷰를 생성한다.

- 서브쿼리인 SELECT문에는 UNION, ORDER BY 절을 사용할 수 없다.

- 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용된다.

예제] <고객> 테이블에서 주소가 '안산시'인 고객들의 성명과
전화번호를 '안산고객'이라는 뷰로 정의
 
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

5. 인덱스(Index)

인덱스는 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조이다.

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

- UNIQUE

  ▶ 사용 : 중복 값이 없는 속성으로 인덱스를 생성

  ▶ 생략 : 중복 값을 허용하는 속성으로 인덱스 생성

- 정렬 여부 지정

  ▶ ASC : 오름차순

  ▶ DESC : 내림차순

  ▶ 생략 : 오름차순으로 정렬됨

- CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정하기 위해 사용한다.

6. 트리거(Trigger)

트리거는 데이터베이스 시스템에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때 마다 자동적으로 수행되는 사용자 정의 프로시저다.

CREATE TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] TABLE AS 테이블명
FOR EACH ROW
WHEN 조건식
트리거 BODY

- 동작시기 옵션 : 트리거가 실행될 때를 지정한다. 옵션에는 AFTER와 BEFORE가 있다.

  ▶ AFTER : 테이블이 변경된 후 트리거 실행

  ▶ BEFORE : 테이블이 변경되기 전에 트리거 실행

- 동작옵션 : 트리거가 실행되게 할 작업의 종류를 지정한다.

  ▶ INSERT : 테이블에 새로운 레코드를 삽입할 때 트리거 실행

  ▶ DELETE : 테이블의 레코드를 삭제할 때 트리거 실행

  ▶ UPDATE :  테이블의 레코드를 수정할 때 트리거 실행

- 테이블 선택 옵션 : 트리거가 적용될 테이블의 종류를 지정한다.

  ▶ NEW : 새로 추가되거나 변경에 참여할 튜플들의 테이블에 트리거가 적용

  ▶ OLD : 변경될 튜플들의 테이블에 트리거 적용

- WHEN : 트리거가 실행되면서 지켜야할 조건을 지정한다.

- 트리거 BODY : 트리거의 본문 코드를 입력하는 부분이다.

  ▶ BEGIN으로 시작해서 END로 끝나는데 적어도 하나 이상의 SQL문이 있어야 

      한다. 그렇지 않으면 오류가 발생한다.

  ▶ 변수에 값을 치환할 때에는 예약어 SET를 사용한다.

예제] <학생>테이블에 새로운 레코드가 삽입될 때, 삽입되는 레코드에 
학년 정보가 누락되었으면 학년 필드에 '신입생'을 치환하는 트리거를 
'학년정보_TR' 라는 이름으로 정의
 
CREATE TRIGGER 학년정보_TR BEFORE INSERT ON 학생
REFERENCING NEW TABLE AS new_table
FOR EACH ROW
WHEN new_table.학년 = ''
BEGIN
    SET new_table.학년 = '신입생'
END

ALTER

1. Alter Table

테이블에 대한 정의를 변경하는 명령문이다.

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

- ADD : 새로운 속성을 추가한다.

- ALTER : 속성의 기본값을 변경한다.

- DROP : 속성을 제거한다.

DROP

스키마, 도메인, 테이블, 뷰, 인덱스, 트리거를 제거하는 명령문이다.

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

- DROP CONSTRAINT : 제약조건을 제거한다.

- CASCADE : 제거할 개체를 참조하는 다른 모든 개체를 함께 제거한다.

- RESTRICT 다른 개체가 제거할 개체를 참조중일 경우 제거가 취소된다.