[SQLD] 2과목 - 3장) 3절. DDL

2과목. SQL 기본 및 활용

3장. 관리구문

3절. DDL

 

# DDL(Data Definition Language)

- 데이터 정의어

- 데이터 구조 정의(객체 생성, 삭제, 변경) 언어

- CREATE(객체 생성),  ALTER(객체 변경), DROP(각체 삭제), TRUNCATE(데이터 삭제)

- AUTO COMMIT(명령어 수행하면 즉시 저장, 원복 불가)

 

# CREATE

- 테이블이나 인덱스와 같은 객체를 생성하는 명령어

- 테이블 생성 시 테이블명, 컬럼명, 컬럼순서, 컬럼크기, 컬럼의 데이터타입 정의 필수

- 테이블 생성 시 각 컬럼의 제약조건 및 기본값은 생략 가능

- 테이블 생성 시 소유자 명시 가능(생략 시 명령어 수행 계정 소유)

- 숫자컬럼의 경우 컬럼 사이즈 생략 가능(날짜 컬럼은 사이즈 명시X)

 

- 문법

① CREATE TABLE [소유자.] 테이블명(

컬럼1 데이터타입 [DEFAULT 기본값] [제약조건],

컬럼2 데이터타입 [DEFAULT 기본값] [제약조건],

...

);

 

② CREATE TABLE 테이블명

AS

SELECT * FROM 복제테이블명;

 

- 특징

  · 복제테이블의 컬럼명과 컬럼의 데이터타입이 복제됨

  · SELECT 문에서 컬럼별칭 사용 시 컬럼별칭 이름으로 생성

  · CREATE 문에서 컬럼명 변경 가능

  · NULL 속성도 복제됨

  · 테이블에 있는 제약조건, INDEX 등은 복제되지 X

 

# 테이터타입

데이터 타입	설명
CHAR(n)	고정형 문자 타입으로 사이즈 전달 필수 사이즈 만큼 호가정형 데이터가 입력됨(빈자리수는 공백으로 채워짐)
VARCHAR2(n)	가변형 문자타입으로 사이즈 전달 필수 사이즈보다 작은 문자값이 입력되더라도 입력값 그대로 유지
NUMBER(p, s)	숫자형 타입으로 자리수 생략 가능 소수점 자리 제한 시 s 전달(p 는 총 자리수)
DATE	날짜타입으로 사이즈 전달 불가

 

# ALTER

- 테이블 구조 변경(컬럼명, 컬럼 데이터타입, 컬럼사이즈, default 값, 컬럼삭제, 컬럼추가, 제약조건)

- 컬럼순서 변경 불가(재생성으로 해결)

① 컬럼 추가

- 새로 추가된 컬럼위치는 맨 마지막(절대 중간 위치에 추가 불가)

- 컬럼 추가 시 데이터타입 필수, default 값, 제약조건을 명시할 수 있음

- 여러 컬럼 동시 추가 가능(괄호 반드시 사용)

 

- 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT] [제약조건];

 

② 컬럼(속성) 변경

- 컬럼 사이즈, 데이터타입, default 값 변경 가능

- 여러 컬럼 동시 변경 가능

 

- 문법

ALTER TABLE 테이블명 MODITY(컬럼명 DEFAULT 값) 

  * 괄호는 생략 가능

 

  · 컬럼 사이즈 변경

    - 컬럼 사이즈 증가는 항상 가능

    - 컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한(데이터가 있는 경우 데이터의 최대 사이즈 만큼 축소

    - 동시 변경 가능(괄호 생략 불가능)

 

  · 데이터 타입 변경

    - 빈 컬럼일 경우 데이터 타입 변경 가능 

    - CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능

 

  · DEFAULT 값 변경

    - DEFAULT 값이란 특정 컬럼에 값이 생략될 경우(입력 시 언급되지 않을 경우) 자동으로 부여되는 값

    - INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력할 때는 DEFAULT 값이 아닌 NULL이 입력됨 

    - 이미 데이터가 존재하는 테이블에 DEFAULT 값 선언 시 기존 데이터 수정 안됨(이후 입력된 데이터부터 적용)

    - DEFAULT 값 해제 시 DEFAULT 값을 NULL로 선언

 

③ 컬럼 이름 변경

- 항상 가능

- 동시 여러 컬럼 이름 변경 불가(괄호 전달 불가)

- ALTER ... RENAME 명령어로 처리

 

- 문법 

ALTER TABLE TABLE_NAME RENAME COLUMN 기존컬럼명 TO 새컬럼명;

 

 

#DROP

- 객체(테이블, 인덱스 등) 삭제

- DROP 후에는 조회 불가

 

- 문법

DROP TABLE 테이블명 [PURGE];

 

 

# TRUNCATE

- 구조 남기고 데이터만 즉시 삭제, 즉시 반영(AUTO COMMIT)

- RECYCLEBIN 에 남지 않음

 

- 문법

 TRUNCATE TABLE 테이블명;

 

# DELETE / DROP / TRUNCATE 차이

- DELETE: 데이터 일부 또는 전체 삭제, 롤백 가능

- TRUNCATE: 데이터 전체 삭제만 가능(일부 삭제 불가), 즉시 반영(롤백 불가)

- DROP: 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가)

 

 

# 제약조건

- 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치

- 테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약조건만 추가 가능

 

 

# PRIMARY KEY(기본키)

- 유일한 식별자(각 행을 구별할 수 있는 식별자 기능)

- 중복 허용X, NULL 허용 X ➪ UNIQUE + NOT NULL

- 특정 컬럼에 PRIMARY KEY 생성하면 NOT NULL 속성 자동 부여(CTAS로 테이블 복사 시 복사되지 X)

- 하나의 테이블에 여러 기본키 생성 불가능

- 하나의 기본키를 여러 커럼을 결합하여 생성 가능

- PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성

- 문법

  · 테이블 생성 시 제약조건 생성

    CREATE TABLE [소유자] 테이블명(

    컬럼1 데이터타입 [DEFAULT 기본값] [제약조건],

    컬럼2 데이터타입 [DEFAULT 기본값] [제약조건],

    ...

    );

 

  · 컬럼 추가 시 제약 조건 생성

    ALTER TABLE 테이블명 ADD  컬럼명 데이터타입 [DEFAULT 기본값] [제약조건];

 

  · 이미 생성된 컬럼에 제약조건만 추가

    ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건종류;

 

  · 제약 조건 삭제

    ALTER TABLE 테이블명 DROP CONTSTRAINT 제약조건명;

 

# UNIQUE

- 중복 허용X

- NULL은 허용

- UNIQUE INDEX 자동 생성

 

# NOT NULL

- 다른 제약조건과 다르게 컬럼의 특징을 나타냄 ➪ CTAS로 복제 시 따라감

- 컬럼 생성 시 NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성됨

- 이미 만들어진 컬럼에 NOT NULL 선언 시 제약조건 생성이 아닌 컬럼 수정(MODIFY)으로 해결

 

# FOREIGN KEY

- 참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성

- 반드시 참조(부모)테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY를 가져야 함

 

- 문법 

CREATE TABLE 테이블명(

컬럼1  데이터타입  [DEFAULT값] REFERENCES 참조테이블(참조키),

...

);

 

# FOREIGN KEY 옵션(생성 시 정의, 변경 불가 ➪ 재생성)

- ON DELETE CASCADE: 부모 데이터 삭제 시 자식 데이터 함께 삭제

- ON DELETE SET NULL: 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL로 수정

 

# CHECK

- 직접적으로 데이터의 값 제한(양수, (1,2,3,4) 중 하나)

 

 

# 기타 오브젝트

① 뷰(VIEW)

- 저장공간을 가지지는 않지만 테이블처럼 조회 및 수정이 가능한 객체

- 종류

  · 단순뷰: 하나의 테이블 조회 뷰

  · 복합뷰: 둘 이상의 테이블 조회 뷰

 

- 특징

  · 기본 테이블로부터 유도된 테이블이기에 기본 테이블과 같은 형태의 구조를 가지고 있으며, 조작도 기본 테이블과 거의 같음

  · 가상의 테이블이기에 물리적으로 구현되어 있지 않기 때문에 저장공간을 차지하지 않음

  · 데이터를 안전하게 보호가능

  ·  이미 정의되어 있는 뷰는 다른 뷰의 정의에 기초가 될 수 있음

  ·  기본 테이블이 삭제되면 그 테이블을 참조하여 만든 뷰 역시 삭제됨

 

- 장점

  · 논리적 독립성 제공

  ·  데이터의 접근을 제어함으로써 보안유지

  ·  사용자의 데이터 관리 단순화

  ·  데이터의 다양한 지원가능

 

- 문법

CREATE [OR REPLACE] VIEW 뷰이름

AS

조회쿼리;

 

- 뷰의 삭제

DROP VIEW 뷰명;

 

- 뷰 조회

SELECT * 

FROM 뷰명;

 

# 시퀸스(SEQUENCE)

- 자동으로 연속적인 숫자를 부여해주는 객체

 

- 문법

CREATE SEQUENCE 시퀸스명

INCREMENT BY        ➪ 증가값(DEFAULT: 1)

START WITH             ➪ 시작값(DEFAULT: 1)

MAXVALUE              ➪ 마지막값(증가시퀸스), 재사용시 시작값(감소시퀸스)

MINVALUE                ➪ 재사용시 시작값(증가시퀸스), 마지막값(감소시퀸스)

CYCLE  |  NOCYCLE  ➪ 스퀸스 번호 재사용(DEFAULT: NOCYCLE)

CACHE N                  ➪ 캐시값(DEFAULT: 20);

 

# 시노님(SYNONYM)

- 테이블 별칭 생성

 

- 문법

CREATE [OR REPLACE] [PUBLIC] SYNONYM 별칭 FOR 테이블명; 

* OR REPLACE: 기존에 같은 이름으로 시노님이 생성되어 있는 경우 대체 

* PUBLIC: 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM의 반대(누구나 사용 가능) 

* PUBLIC 으로 생성한 시노님은 반드시 PUBLIC으로 삭제해야 함