[데이터베이스 기초] SQL DDL문 정리 (CREATE, DROP, ALTER)

DDL이란 데이터 정의어로 간단히 말하면 테이블을 만들고(CREAET) 삭제하고(DROP) 수정(ALTER)하는 역할을 하는 언어이다. 

 

용어 정리

스키마 :  데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것을 말한다.

스키마 이름, 관리자 아이디, 엘리먼트 설명 정보 ( 테이블, 제약조건, 뷰, 도메인, 사용자 권한 등)이 포함된다.

CREATE SCHMA COMPANY AUTHORIZATION 'Jsmith'

 

카탈로그: 스키마들의 정보를 모아둔 것

특별한 형태의 스키마를 갖는다. INFORMATINO_SCHEMA 라는 스키마 정보들을 갖는 스키마를 갖는다. (디비가 관리하는 모든 엘리먼트 정보를 갖고 있음) -> 시스템 카탈로그

 

데이터 타입 종류

Numeric

• INTEGER, SMALLINT, FLOAT or REAL, DOUBLE PRECISION, DECIMAL(i,j), or DEX(i,j) or NUMERIC(i,j) 

Character string

• CHAR(n), VARCHAR(n) or CHAR VARYING(n), CLOB(Character Large OBject) 

Bit string

• BIT(n), BIT VARYING(n), BLOB(Binary Large OBject) ex) BLOB(30G)

 

CREATE 명령어

테이블 또는 도메인을 생성하는 명령어이다.

ex) CREATE TABLE EMPLOYEE, CREATE DOMIN SSN_TYPE AS CHAR(9)

*Domain을 정의함으로서 동일한 도메인을 갖는다는 것을 좀 더 명시적으로 표현해줄 수 있다. 시스템에 따라 사용하지 못하는 경우도 있음

CREATE TABLE EMPLOYEE
(
    Fname		VARCHAR(15)		NOT NULL,
    Minit		CHAR,
    Lname		VARCHAR(15)		NOT NULL,
    Ssn			CHAR(9)			NOT NULL,
    Bdate		DATE,
    Address		VARCHAR(30),
    Sex			CHAR,
    Salary		DECIMAL(10,2),
    Super_ssn		CHAR(9),
    Dno			INT			NOT NULL,
    PRIMARY KEY(Ssn),
    UNIQUE (Salary),
    FORIGN KEY (Super_ssn) REFERENCES EMPLOYEES(Ssn),
    FORIGN KEY (Dno) REFERENCES DEPARTMENRT(Dnumber)
);

DECIMAL(10,2) -> 10자리 중 2자리는 소숫점 아래

기본키는 NOT NULL 선언이 없어도 자동으로 설정된다.

컬럼 순서는 CREATE 문에서 나타나는 순서대로 나타난다.

 

Constraints 명세하는 방법

1. NOT NULL :  null 허용을 막는다.

Dnumber INT NOT NULL 
	CHECK (Dnumber > 0 AND Dnumber > 21),

2. DEFAULT <value> : null 이 들어오면 지정해준 value로 값을 지정한다.

3  CHECK 절 : 값의 범위나 제약 조건을 명세한다. 도메인 선언할 때 사용할 수 있음

CREATE DOMAIN D_num AS INTEGER
	CHECK (D_num > 0 AND D_num < 21);

CHECK 절을 여러 애트리뷰트에 대해서도 적용 가능하다.

CREATE TABLE
(
    create_date	DATE,
    start_date	DATE
   
    ...
    CHECK(create_date <= start_date)
);

table constraints 라 하며 테이블에 삽입되거나 수정될 때 마다 체크된다.

Key에 대한 제약 조건을 주는 방법

1. PRIMARY KEY 절 : PK를 지정해주며 단일 속성이라면 속성을 선언하면서 옆에 쓸 수 있다.

2. UNIQUE 절 : 속성에 중복된 값을 막는다. 

3. FOREIGN KEY 절 

4. Constraint 에 이름을 부여할 수 있음 -> 제약 조건을 후에 수정할 수있다. 이름을 주지 않으면 나중에 수정할 방법이 없다.

COSTRAINT EMPSUPERFK
	FORIGN KEY (Super_ssn) REFERENCES EMPLOYEE (Ssn)
    	ON DELETE SET NULL ON UPDATE CASCADE,

해석: EMPLOYEE 튜플이 삭제됐을 때 참조하고 있던 튜플의 Super_ssn은 null, 업데이트 됐을 때는 같이 바꾼다.

* 제약 조건이 위배됐을 때 대응하기 위한 액션을 지정할 수 있다. -> referential triggered action

(ON DELETE와 ON UPDATE에 대해서 SET NULL, CASCADE, SET DEFAULT 3가지 옵션이 있다. )

 

 

 

 

DROP 명령어

테이블이나 스키마를 지우는 명령어이다.

뒤에 명령어를 아래와 같은 옵션을 줄 수 있다.

RESTRICT: 참조되고 있으면 지울 수 없다. 

CASCADE: 상관없이 지워 버린다. 

DROP TABLE DEPENDENT CASCADE;
DROP SCHEMA COMPANY CASCADE;

 

ALTER 명령어 

기존 테이블에 컬럼 추가, 삭제, 정의 변경, 제약조건 추가, 변경한다.

ALTER TABLE EMPLOYEE ADD Job VARCHAR(12); -> 속성 추가
ALTER TABLE EMPLOYEE DROP Address CASCADE; -> 속성 삭제
ALTER TABLE DEPARTMENT ALTER Mgr_ssn DROP DEFAULT; -> 속성 정의 삭제
ALTER TABLE DEPARTMENT ALTER Mgr_ssn SET DEFAULT "33333333";-> 속성 정의 추가
ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE; -> 제약 조건을 삭제