[개미의 걸음 SQLD 2과목] SQL종류② DDL(Data Definition Language)

DDL[Data Definition Language]

테이블, 뷰, 인덱스, 시퀀스, 동의어 등의 데이터베이스 객체들을 생성*삭제*수정하는데 사용되는 SQL문

• 데이터를 저장할 구조를 정의하는 언어

* CREATE  : 객체 생성   * DROP    : 객체 삭제   * ALTER    :  객체 변경   * RENAME : 객체 이름 변경   * TRUNCATE TABLE : 테이블 내의 데이터 전부 삭제[복구 불가]

 

테이블 생성*변경*삭제

Create Table	새로운 테이블을 생성 테이블을 생성할 때 기본키, 외래키, 제약사항 등을 설정
Alter Table	해당 테이블을 변경 칼럼을 추가하거나 변경*삭제 기본키를 설정하거나 외래키를 설정 가능
Drop Table	해당 테이블을 삭제 테이블의 데이터 구조 뿐만 아니라 저장된 데이터도 모두 삭제

 

1. Create Table

CREATE TABLE 테이블 명 (칼럼명1 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식], 칼럼명3 DATATYPE [DEFAULT 형식]);

• 데이터베이스를 사용하기 위해서는 테이블을 먼저 생성해야함
• 테이블에 삽입할 칼럼명들을 테이블명 뒤의 ( )안에 기입하고 세미콜론[;]으로 끝냄
• 테이블에 생성되는 칼럼이름과 데이터 타입을 입력
      → 칼럼 이름은 영문, 한글, 숫자 모두 가능
• 각 칼럼은 콤마[,]로 구분
• 칼럼에 대한 제약 조건이 있는 경우, CONSTRAINT를 이용해 추가 가능
• 기본키를 지정할 때는 "칼럼명 테이터타입 PRIMARY KEY"를 입력
• 다른 테이블을 참조할 때는 "참조할칼럼명 데이터타입 REFERENCES 현테이블명(칼럼명) ON조건절"
      → "ON DELETE CASCADE"는 참조한 모든 데이터 삭제
      → "ON DELETE SET NULL"은 참조한 칼럼의 값이 NULL값을 가짐 
• SELECT 문을 활용한 테이블 생성[CTAS: Create Table ~ As Select ~ FROM ~]
      → 기존 테이블을 이용하므로 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점
      → 기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용
      → 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 없어짐
          (제약조건을 추가하기 위해서는 ALTER TABLE 기능을 사용해야 함)

 

테이블명 생성시 주의사항

1. 객체를 의미할 수 있는 적절한 이름 사용

2. 가능한 단수형을 사용

3. 테이블명은 다른 테이블의 이름과 중복되지 않아야 함

4. 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없음

5. 테이블명과 칼럼명은 반드시 문자로 시작해야 함

6. 사전에 정의한 예약어[Reseved Word]는 사용할 수 없다.

7. A~Z, a~z, 0-9, _, $, #만 사용 가능 [특수문자 사용 불가]

※ 기본적으로 테이블명이나 칼럼명은 대문자로 만들어짐

 

CONSTRAINT[제약조건]

종    류	설    명
기본키 [PRIMARY KEY]	기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성    * 하나의 테이블은 하나의 기본키만 가짐         →한 릴레이션에서 특정 레코드를 유일하게 구별할 수 있는 속성    * 기본키로 정의된 필드(속성)에는 동일한 값이 중복되어 저장될 수 없음    * 기본키는 NULL값을 가질 수 없음
외래키 [FOREIGN KEY]	다른 테이블의 기본키를 외래키로 가져와 테이블 간의 관계를 정의하기 위해 사용     * 참조 무결성 제약 옵션 선택 가능
고유키 [UNIQUE KEY]	테이블에 저장된 행 데이터를 고유하게 식별하기 위해 사용     * 단, NULL은 고유키 제약 대상이 아님[여러개의 NULL값을 가질 수 있음]
NOT NULL	NULL값 입력 금지     * 일반적으로 칼럼은 DEFAULT 상태에서 NULL값을 가짐     * NOT NULL 제약을 지정하면 해당 칼럼은 입력이 필수!
CHECK	데이터의 무결성을 유지하기 위해 테이블의 특정 칼럼에 설정하는 제약     * 입력할 수 있는 값의 범위 등을 제한하기 위해 사용     * TRUE/FALSE로 평가할 수 있는 논리식을 CHECK 제약으로 사용

사용자가 원하는 조건의 데이터만 유지하기 위한 데이터베이스의 보편적인 방법

• 테이블의 특정 칼럼에 제약을 설정해 데이터 무결성 유지

FK키 참조 무결성 제약 옵션① DELETE(/MODIFY) ACTION

CASCADE	Parent Table에서 데이터 삭제시 참조한 Child Table 데이터도 함께 삭제     ex> ON DELETE CASCADE 옵션을 줄 경우, 삭제시 참조한 테이블의 데이터까지 자동으로 삭제     → 참조 무결성 보장
SET NULL	Parent Table에서 데이터 삭제시 참조한 Child Table 데이터가 NULL값으로 변경됨
SET DEFAULT	Parent Table에서 데이터 삭제시 참조한 Child Table 데이터가 DEFAULT값으로 변경됨
RESTRICT	Child Table 데이터에 PK값이 없는 경우만 Parent Table에서 데이터 삭제 허용
NO ACTION	참조무결성을 위반하는 삭제/수정 action을 취하지 않음

FK키 참조 무결성 제약 옵션② Insert ACTION

AUTOMATIC	Parent Table에 PK가 없는 경우, PK 생성 후 Child Table 데이터 입력
SET NULL	Parent Table에 PK가 없는 경우, Child Table 외부키를 NULL값으로 입력
SET DEFAULT	Parent Table에 PK가 없는 경우, Child Table 외부키를 지정된 기본값으로 입력
DEPENDENT	Parent Table 데이터에 PK가 있을 때만 Child Table에서 데이터 입력 허용     → Parent Table 데이터에 PK가 없으면 Child Table에서 데이터 입력 못함
NO ACTION	참조무결성을 위반하는 입력 action을 취하지 않음

 

NULL & DEFAULT

NULL	‘아직 정의되지 않은 미지의 값’이거나 ‘현재 데이터를 입력하지 못하는 경우’를 의미     → 공백(BLANK, ASCII 코드 32번)이나 숫자 0(ZERO, ASCII 48)과는 전혀 다른 값     → 조건에 맞는 데이터가 없을 때의 공집합과도 다른 값
DEFAULT	데이터 입력시 기본적으로 설정되는 값     → 일반적으로 명시된 값을 지정하지 않은 경우에 NULL 값이 입력     → DEFAULT 값을 정의한 경우, NULL 값이 아닌 사전에 정의된 기본 값이 자동으로 입력

 

DESC

DESC 테이블명;

생성된 테이블의 구조를 확인할 때 사용

 

2. Alter Table

ALTER TABLE 테이블명 ADD 추가할칼럼명 데이터유형;
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);
ALTER TABLE 테이블명 MODIFY (칼럼명 새로운데이터타입/길이 제약조건);
ALTER TABLE 테이블명 DROP COLUMN 칼럼명;
ALTER TABLE 테이블명 RENAME 새로운테이블명;
ALTER TABLE 테이블명 RENAME COLUMN 칼럼명 TO 새로운칼럼명;

• 테이블에 삽입할 칼럼명들을 테이블명 뒤의 ( )안에 기입하고 세미콜론[;]으로 끝냄
• 테이블에 생성되는 칼럼이름과 데이터 타입을 입력
      → 칼럼 이름은 영문, 한글, 숫자 모두 가능
• 각 칼럼은 콤마[,]로 구분
• 칼럼에 대한 제약 조건이 있는 경우, CONSTRAINT를 이용해 추가 가능
• 기본키를 지정할 때는 칼럼 옆에 Primary key를 입력
• 외래키를 지정할 때는 FOREIGN KEY를 사용
      → ALTER TABLE 테이블명 ADD COSTRAINT 추가할 외래키 칼럼명 FOREIGN KEY (키본키 칼럼명) REFERENCES 테이블명(기본키 칼럼명);
• SQL Server에서는 여러 개의 칼럼을 동시에 수정하지 못하므로 하나의 칼럼마다 변경
      → SQL Server에서는 괄호를 사용하지 않음
            ex> ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터타입 default값;
• 칼럼 제거할 때는 " ALTER TABLE 테이블명 DROP COLUMN 제거할칼럼명;"
• 테이블명 변경할 때는 "ALTER TABLE 테이블명 RENAME 새로운 테이블명;"
      → ANSI 표준을 기준으로 하면 "RENAME 테이블명 TO 새로운 테이블명;"

 

3. Drop Table

DROP TABLE 테이블명;
DROP TABLE 테이블명 CASCADE CONSTRAINT;
TRUNCATE TABLE 테이블명;

• CASCADE CONSTRAINT를 함께 사용하면 해당 테이블의 데이터를 외래키로 참조된 제약사항도 모두 삭제
• TRUNCATE TABLE을 사용하면 해당 테이블에 들어있던 값들만 제거[저장공간 재사용 가능]
      → 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 사용

DELETE FROM	TRUNCATE	DROP
DML	DDL(일부 DML 성격을 가짐)	DDL
Commit이전 ROLLBACK 가능	ROLLBACK 불가능	ROLLBACK불가능
사용자 Commit	Auto Commit	Auto Commit
데이터만 삭제 로그 기록 남음 테이블 구조 유지	데이터 삭제 로그 기록 삭제[용량 초기화] 테이블 구조 유지	데이터 삭제 로그 기록 삭제 테이블 구조 삭제

 

뷰[VIEW]

테이블로부터 유도된 가상의 테이블을 "뷰"라고 함

• 실제 데이터를 가지고 있지 않음
• 테이블을 참조해서 원하는 칼럼만을 조회
      → 참조한 테이블이 변경되면 뷰도 변경됨
      → 특정 칼럼만 조회시켜 보안성을 향상시킴
• Data Dictionary에 SQL문 형태로 저장하되 실행 시에 참조됨
• 한번 생성된 뷰는 변경할 수 없음[변경하고 싶으면 삭제 후 재생성해야 함]
      → CREATE VIEW문을 사용해 뷰 생성
      → ALTER문을 사용해 뷰를 변경할 수 없음
      → SELECT문을 사용해 일반 테이블처럼 뷰 조회 가능
      → DROP VIEW르르 사용하면 뷰 삭제 [참조했던 테이블이 삭제된는 것은 아님]
• 뷰에 대한 입력, 수정, 삭제할 때는 제약이 있음

뷰의 장점	뷰의 단점
하나의 테이블에 여러 개의 뷰 생성 가능 특정 칼럼만 조회 가능[보안 기능] 데이터 관리가 간단 SELECT문이 간단	독자적인 인덱스 생성 불가 삽입, 수정, 삭제 연산에 제약 데이터 구조 변경 불가