| 니앙팽이 - DB | 7 | SQL - DDL(데이터 정의어)

7. DDL 데이터 정의어

---

1. CREATE

CREATE [SCHEMA, DOMAIN, TABLE, VIEW, INDEX]

1. SCHEMA

/******************************************************
* 기본형
******************************************************/
CREATE  SCHEMA _SCHEMA_NAME_ AUTHORIZATION _USERID_;

/******************************************************
* DBADM 권한을 갖는 사용자로서, 
* 사용자 "김민수"을 소유자로 갖는 
* "인벤토리" 라는 스키마를 작성하고, 
* "파츠" 라는 테이블을 만들고 (PARTNO : INTEGER, DESCR : VARCHAR(24), QUANTITY INTEGER) 행튜레를 가지며
* PARTNO는 널값을 가지지 않는다. 그리고 PARTNO에 대해 PART라는 인덱스를 오름차순으로 만들어라
******************************************************/

CREATE SCHEMA INVENTORY AUTHORIZATION 김민수
    CREATE TABLE PART   (
                            PARTNO      INTEGER     NOT NULL,
                            DESCR       VARCHAR(24),
                            QUANTITY    INTEGER
                        )
    CREATE INDEX PARTINDEX ON PART (PARTNO)

2. DOMAIN
테이블 CoFiAtt 에서 원자값으로 사용되는 사용자 정의 데이터 타입을 정의한다.

/******************************************************
* 기본형
******************************************************/

CREATE DOMAIN _DOMAIN_NAME_ [AS] _데이터타입(SQL에서 지원하는 데이터 타입)_
    [DEFAULT _데이터를 입력하지 않았을떄 자동 입력값_]
    [CONSTRAINT _제약조건이름_ { NOT NULL | NULL | CHECK (표현식:TURE, FALSE, UNKNOW) } ]

/******************************************************
* 성별을 남, 여와 같이 정해진 문자 1개의 문자로 표현하는 도메인 SEX를 정의하는 SQL
******************************************************/

CREATE DOMAIN SEX CHAR(1)
    DEFAULT '-'
    CONSTRAINT VALID-SEX CHECK(VALUE IN ('-', '남', '여'));

/******************************************************
* 우편 번호 자료를 저장하는 테이블 만들기
* 기본값은 없다.
******************************************************/

CREATE DOMAIN us_postal_code TEXT
CHECK   (
            VALUE ~ '^\d{5}$' OR VALUE ~ '^\d{5}-\d{4}$'
        );

3. TABLE
CREATE 테이블에 모든 표기형식은 괄호에 들어가야 한다. 그리고 나서 ; 을 붙인다.
제약 조건의 이름과 제약조건의 정의는 붙어다닌다.

• CONSTRAINT 제약조건_이름 CHECK (COFIATT < 400000)

/******************************************************
* 기본형
******************************************************/

CREATE TABLE _TABLE_NAME_
    (
        COFIATT1 INTEGER NOT NULL,
        COFIATT2 VARCHAR(10) DEFAULT "홍길동",
        COFIATT3 SEX NOT NULL,
        COFIATT4 us_postal_code NOT NULL,
        COFIATT5 DATE,
        ...
        PRIMARY KEY(_기본키로 사용할 COFIATT_)
        UNIQUE (_대체키로 사용할 COFIATT_)
        FOREIGN KEY(_왜래키로 사용할 COFIATT_)
            REFERENCES _참조테이블_(기본키COFIATT, ...)
            [ON DELETE {CASCADE, SET NULL, SET DEFAULT}]
            [ON UPDATE {CASCADE, SET NULL, SET DEFAULT}]
        [CONSTRAINT _제약조건명_]
        [CHECK (조건식)]
    )

/******************************************************
* 제약 조건
******************************************************/
ON DELETE {CASCADE, SET NULL, SET DEFAULT};
ON UPDATE {CASCADE, SET NULL, SET DEFAULT};

___ NOT NULL,
___ UNIQUE,
___ DEFAULT '_VALUE_',

CONSTRAINT 제약조건_이름
    CHECK (COFIATT < 400000),
CONSTRAINT 제약조건_이름
    CHECK (COFIATT IN (1,2,3,4)) 

/******************************************************
* 다양한 예시
******************************************************/

CREATE TABLE EMPLOYEE
    (
        EMPNO   INTEGER NOT NULL,
        EMPNAME CHAR(10),
        TITLE   CHAR(10),
        MANAGER INTEGER,
        SALARY  INTEGER,
        DNO     INTEGER,
        PRIMARY KEY (EMPNO),
        FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DEPTNO)
    );

CREATE TABLE DEPARTMENT
    (
        DEPTNO      INTEGER NOT NULL, 
        DEPTNAME    CHAR(10), 
        FLOOP       INTEGER, 
        PRIMARY KEY (DEPTNO)
    );

/******************************************************
* 이름, 학번, 전공, 성별, 생년월일로 구성된 <학생> 테이블 정의
    *   이름 : NOT NULL
        학번 : 기본키
        전공 : <학과> 테이블에 학과 코드를 참조하는 외래키
                학과 테이블에서 삭제가 일어나면 전공값을 NULL
                학과 코드가 변경되면 전공 값도 전공값도 같은값으로 변한다
        성별 : SEX 도메인 사용
        생년월일 1980-01-01 이후 데이터만 저장 가능하고, 제약 조건의 이름은 생년월일제약으로 
******************************************************/
CREATE TABLE 학생
    (
        이름 VARCHAR(7) NOT NULL, 
        학번 VARCHAR(8),
        전공 VARCHAR(8),
        성별 SEX,
        생년월일, DATE
        PRIMARY KEY(학번)
        FOREIGN KEY(전공) REFERENCES 학과(학과코드)
            ON DELETE SET NULL
            ON UPDATE CASCADE
        CONSTRAINT 생년월일제약
            CHECK (생년월일 >= '1980-01-01');
    );

4. VIEW

이름을 갖는 가상 테이블을 생성할 수 있다. 마치 테이블의 변수 저장하듯이 사용해보자

/******************************************************
* 기본형
******************************************************/
CREATE VIEW _VIEW_NAME_ AS 
    (
        SELECT FROM WHERE
    );

5. INDEX
테이블 행튜레의 검색 시간을 단축시키기위해 만든 보조적인 데이터 구조

/******************************************************
* 기본형
******************************************************/
CREATE [UNIQUE] INDEX _INDEXT_NAME_
ON _TABLE_NAME_(COFIATT1[ASC|DESC], COFIATT2[ASC|DESC], ...)

/******************************************************
* 몬스터 테이블에서 유니크한 특성을 갖는 몬스터ID를 가지고 오름차순 몬스터인덱스를 정의해라
******************************************************/

CREATE UNIQUE INDEX MONSTER_INDEX
ON MONSTER_TABLE (MONSTER_ID ASC);

---

2. ALTER

1. TABLE
테이블의 COFIATT을 추가하거나 삭제한다.
혹은 COFIATT의 옵션을 바꿀때 사용한다,{NOT NULL, SET DEFAULT, SET NULL}

ALTER TABLE EMPLOYEE ADD PHONE CHAR(13);

ALTER TABLE EMPLOYEE DROP COLUMN COFIATT [CASCADE];

ALTER TABLE EMPLOYEE ALTER COFIATT [SET DEFAULT '기본값']

---

3. DROP

DROP SCHEMA _SCHEMA_NAME_ [CASCADE | RESTRICT]
DROP DOMAIN _DOMAIN_NAME_ [CASCADE | RESTRICT]
DROP TABLE _TABLE_NAME_ [CASCADE | RESTRICT]
DROP VIEW _VIEW_NAME_ [CASCADE | RESTRICT]
DROP INDEX _INDEX_NAME_ [CASCADE | RESTRICT]
DROP CONSTRAINT _제약조건명_

---

참조

1. https://starrykss.tistory.com/1640
2. https://www.postgresql.kr/docs/10/sql-createdomain.html
3. https://www.w3schools.com/sql/sql_create_table.asp