[이론] SQL 정의어(DDL)
※ SQL문?
SQL은 관계 데이터베이스에서 사용되는 대표적인 언어로, 데이터베이스 작업을 보다 효율적이고, 다양하게 표현하고 처리하기 위한 고급 데이터베이스 언어이다. 종류는 크게 정의어(DDL), 조작어(DML), 제어어(DCL)로 나누어진다.
1. SQL 정의어 (DDL)
- 정의어는 관계 데이터베이스에서 사용될 테이블, 스키마, 도메인, 인덱스, 뷰 등을 정의(생성)하거나 수정, 제거하기 위해 사용되는 언어
- CREATE, ALTER, DROP 문
2. CREATE
1) 테이블 정의
: CREATE TABLE 문에 의해 생성된다.
| CREATE TABLE '테이블 이름' | 지정한 '테이블 이름'으로 테이블 생성 |
| {속성_이름 데이터_타입} | - 테이블을 구성하는 속성 수만큼 속성 이름과 데이터 타입 기입 - NOT NULL : 테이블 생성 시 특정 속성 값에 'NULL'이 없도록 지정할 때 사용하며 생략 가능 |
| PRIMARY KEY(속성_이름) | 테이블에서 기본키 속성을 지정 |
| UNIQUE(속성_이름) | - 대체키 지정 시 사용 - 속성의 모든 값이 고유한 값을 가지도록 지정할 때 사용 |
| FOREIGN KEY(속성_이름) REFERENCES 참조 테이블 (속성_이름) | - 외래키 지정 시 사용 - FOREIGN KEY(속성_이름) : 외래키로 사용될 속성 이름 - 참조 테이블(속성_이름) : 참조할 테이블 이름과 속성 이름 기입 |
| CONSTRAINT 제약조건_이름 CHECK(속성_이름=범위 값) | 테이블을 생성할 때 특정 속성에 대해 속성 값의 범위를 지정 |
* 외래 키 지정 시 옵션
| ON DELETE | 참조 테이블의 튜플이 삭제되면 기본 테이블은 어떤 형태로 대처할지 선택 |
| ON UPDATE | 참조 테이블의 튜플이 변경되면 기본 테이블은 어떤 형태로 대처할지 선택 |
| CASCADE | 참조 테이블의 튜플에 삭제, 변화가 있는 경우 기본 테이블도 같이 연쇄적으로 삭제,변화가 되도록 설정 |
| SET NULL | 참조 테이블의 튜플에 삭제, 변화가 있는 경우 기본 테이블의 관련된 속성 값을 NULL로 변경 |
| SET DEFAULT | 참조 테이블의 튜플에 삭제, 변화가 있는 경우 기본 테이블의 관련 속성 값을 기본 값으로 변경 |
| NO ACTION | 참조 테이블의 튜플에 삭제, 변화가 있는 경우 기본 테이블에 아무런 변화가 없도록 지정 |
* 데이터 타입
=> 가변 길이 문자는 지정된 문자 수 안에서 실제 자료에 따라 유동적으로 기억공간을 차지한다. 예를 들어 데이터 타입을 CHAR(10)인 경우와 VARCHAR(10)로 지정했을 때 실제 자료의 문자 수가 5라면 CHAR(10)인 경우는 실제 문자 수와 상관없이 문자 10개를 저장할 공간을 차지하고, VARCHAR(10)의 경우는 문자 5개를 저장할 공간만 차지한다.
데이터 타입 표현 형식 정수 INT 실수 FLOAT 또는 REL 고정길이 문자 CHAR(문자 수) 가변 길이 문자 VARCHAR(문자 수) 시간 TIME 날짜 DATE
예 1 ) 다음 주어진 지시사항에 따라 테이블을 만드는 SQL문을 완성하시오.
- 학번, 성명, 학과, 학년, 학점으로 구성된 학생 테이블을 만들어라.
- 학번과 학년은 숫자형 자료이며, 나머지는 문자형이다.
- 학번을 기본키로 지정한다.
- 성명 속성은 공백이 있을 수 없다.
- 학과 항목을 이용하여 [수강] 테이블의 학과를 참조하도록 외래 키를 지정하며, 참조 테이블에서 삭제가 발생하면 NULL 값으로 하고, 수정이 발생하면 연쇄적으로 수정하도록 한다.
- 학년의 속성 값은 4 이하의 값을 갖도록 'hak' 이름으로 제약한다.
CREATE TABLE 학생 (
학번 INT,
성명 VARCHAR(10) NOT NULL,
학과 VARCHAR(10),
학년 INT,
학점 CHAR(1),
PRIMARY KEY (학번),
FOREIGN KEY(학과) REFERENCES 수강(학과)
ON DELETE SET NULL
ON UPDATE CASCADE,
CONSTRAINT hac CHECK(학년 <=4));
2 ) 스키마 정의
: 시스템 관리자가 일반 사용자에게 스키마에 대한 권한을 주기 위한 스키마를 만들기 위해 사용되며, CREATE SCHEMA 문에 의해 생성된다.
CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자;
예 1 ) 스키마 이름이 'JUNGBO'이고, 허가권자가 '이영진'인 스키마를 정의하시오.
CREATE SCHEMA JUNGBO AUTHORIZATION 이영진;
3) 도메인 정의
: 한 속성 값의 범위를 지정하기 위한 도메인은 CREATE DOMAIN문에 의해 생성되며, 다음과 같은 구문에 따라 만들어진다.
CREATE DOMAIN 도메인_이름 데이터_타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건_이름 CHECK(VALUE IN(범위 값))];
예 ) 속성의 값으로 'T'와 'F' 로만 구성되는 'success'라는 이름의 도메인을 정의하시오. 속성 값이 입력되지 않을 경우 기본 값은 'T'로 한다.
CREATE DOMAIN success CHAR(1)
DEFAULT 'T'
CONSTRAINT v-success CHECK(VALUE IN('T', 'F'));
4) 인덱스 정의
: 데이터베이스 내의 자료를 보다 효율적으로 검색하기 위해 인덱스를 만들고, 시스템에 의해 자동 관리되며, CREATE INDEX문에 의해 생성된다.
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름(속성_이름[ASC|DESC])
[CLUSTER];
| UNIQUE | 중복을 허용하지 않도록 인덱스 생성(생략된 경우 = 중복 허용) |
| ON 테이블_이름(속성_이름) | - 지정된 테이블의 속성으로 인덱스를 만듦 - [ASC|DESC] : 인덱스로 사용될 속성 값의 정렬 방법(오름/내림), 생략된 경우 오름차순으로 정렬됨 |
| CLUSTER | 인접된 튜플들을 물리적인 그룹으로 묶어 저장 시 사용 |
예 ) 학생 테이블의 학과 속성 값을 오름차순 정렬하여, 중복을 허용하지 않도록 'stud_idx'라는 이름의 인덱스를 정의하시오.
CREATE UNIQUE INDEX stud_idx ON 학생(학과 ASC);
3. ALTER
: 기존에 만들어진 테이블에 새로운 속성을 추가하거나 기존 속성을 변경, 삭제할 때 사용하는 명령어
ALTER TABLE 테이블 이름 ADD 속성_이름 데이터_타입 [DEFAULT]; : 새로운 속성 추가 구문
ALTER TABLE 테이블 이름 ALTER 속성_이름 [SET DEFAULT]; : 속성에 대한 항목 변경
ALTER TABLE 테이블 이름 DROP 속성_이름 [CASCADE | RESTRICT]; : 속성 제거 구문
예 1 ) [학생] 테이블에 '주소' 속성을 추가하시오. (단, 주소 항목은 가변 길이 문자열로 하고 30자까지 입력될 수 있다.)
ALTER TABLE 학생 ADD 주소 VARCHAR(30);
예 2) [학적] 테이블에서 '학년' 속성을 제거하시오.
ALTER TABLE 학적 DROP 학년 CASCADE;
4. DROP
: 기존에 사용되던 테이블, 스키마, 도메인, 인덱스, 뷰, 제약조건 등을 제거할 때 사용하는 명령으로 삭제 시 전체가 삭제된다.
DROP TABLE 테이블_이름 [CASCADE | RESTRICT];
DROP SCHEMA 스키마_이름 [CASCADE | RESTRICT];
DROP DOMAIN 도메인_이름 [CASCADE | RESTRICT];
DROP VIEW 뷰_이름 [CASCADE | RESTRICT];
DROP INDEX 인덱스_이름;
DROP CONSTRAINT 제약조건_이름;
예 ) [학적] 테이블을 제거하시오
DROP TABLE 학적 CASCADE;
* CASCADE vs RESTRICT
RESTRICT : 삭제할 요소가 사용(참조) 중이면 삭제가 이루어지지 않는다.
CASCADE : 삭제할 요소가 사용(참조) 중이더라도 삭제가 이루어지며, 삭제할 테이블을 참조 중인 다른 테이블도 연쇄적으로 같이 삭제된다.