정보처리기사 - SQL 응용 #118

118. SQL - DDL

118.1 DDL(Data Define Language, 데이터 정의어)

DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

• 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로 저장됨
• DDL의 3가지 유형

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의
ALTER	TABLE에 대한 정의를 변경하는 데 사용
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

• ISO/IEC 9075 : 국제 표준화 기구(ISO)와 미국 표준 협회(ANSI)의 관계 데이터베이스 시스템에 대한 SQL 문법을 통합·개정한 것으로, SQL에 대한 국제 표준으로 널리 활용되고 있음

118.2 CREATE SCHEMA

스키마를 정의하는 명령문

• 스키마(Schema) : 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세(Specification)를 기술(Description)한 것으로 데이터 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의
• 표기 형식

CREATE SCHEMA 스키마명 AUTHORRIZATION 사용자_id;

118.2.1 예제

• 소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문은?

CREATE SCHEMA 대학교 AUTHORRIZATION 홍길동;

118.3 CREATE DOMAIN

도메인을 정의하는 명령문

• 도메인(Domain) : 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
• 표기 형식

CREATE DOMAIN 도메인명 [AS] 데이터_타입
       [DEFAULT 기본값]
       [CONSTRAINT 제약조건명 CHECK (범위값)];

• 데이터 타입 : SQL에서 지원하는 데이터 타입
• 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값

118.3.1 예제

• '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문은?

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

1. CREATE DOMAIN SEX CHAR(1) : 정의된 도메인은 이름이 'SEX'이며, 문자형이고 크기는 1자
2. DEFAULT '남' : 도메인 SEX를 지정한 속성의 기본값은 '남'
3. CONSTRAINT VALID-SEX CHECK(VALUE IN ('남', '여')); : SEX를 지정한 속성에는 '남', '여' 중 하나의 값만을 저장할 수 있음

118.4 CREATE TABLE

테이블을 정의하는 명령문

• 테이블(Table) : 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션(Relation)이라고 부르고, 조작이나 검색 시에는 테이블이라고 부름
• 표기 형식

CREATE TABLE 테이블명
       (속성명, 데이터_타입 [DEFAULT 기본값] [NOT NULL], ···
       [, PRIMARY KEY(기본키_속성명, ···)]
       [, UNIQUE(대체키_속성명, ···)]
       [, FOREIGN KEY(외래키_속성명, ···)
              REPERENCES 참조테이블(기본키_속성명, ···)]
              [ON DELETE 옵션]
              [ON UPDATE 옵션]
       [, CONSTRAINT 제약조건명] [CHECK (조건식)]);

• 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정
  • NOT NULL : 특정 속성이 데이터 없이 비어 있어서는 안 된다는 것을 지정할 때 사용
• PRIMARY KEY : 기본키로 사용할 속성을 지정
• UNIQUE : 대체키로 사용할 속성을 지정
  • 증복된 값을 가질 수 없음
• FOREIGN KEY ~ REFERENCE ~ : 외래키로 사용할 속성을 지정
  • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 상황을 저장
  • ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 저장
• CONSTRAINT : 제약 조건의 이름을 지정
• CHECK : 속성 값에 대한 제약 조건을 정의

118.4.1 예제

• '이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL문은?
• 제약 조건

1. '이름'은 NULL이 올 수 없고 '학번'은 기본키
2. '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용
3. <학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만듦
4. <학과> 테이블에서 '학과코드'가 변경되면 전공 값도 같은 값으로 변경
5. '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있음
6. 제약 조건의 이름은 '생년월일제약'으로 함
7. 각 속성의 데이터 타입은 적당하게 지정하나, '성별'은 도메인 'SEX'를 사용

CREATE TABLE 학생
       (이름 VARCHAR(15) NOT NULL,
       학번 CHAR(8),
       전공 CHAR(5),
       성별 SEX,
       생년월일 DATE,
       PRIMARY KEY(학번), 
       FOREIGN KEY(전공) REPERENCES 학과(학과코드)
              ON DELETE SET NULL
              ON UPDATE CASCADE,
       CONSTRAINT 생년월일제약
              CHECK (생년월일>='1980-01-01'));

1. CREATE TABLE 학생 : 학생 테이블을 생성
2. (이름 VARCHAR(15) NOT NULL, : '이름' 속성은 최대 문자 15자로 NULL 값을 갖지 않음
3. 학번 CHAR(8), : '학번' 속성은 문자 8자
4. 전공 CHAR(5), : '전공' 속성은 문자 8자
5. 성별 SEX, : '성별' 속성은 'SEX' 도메인을 자료형으로 사용
6. 생년월일 DATE, : '생년월일' 속성은 DATE 자료형을 가짐
7. PRIMARY KEY(학번), : '학번'을 기본키로 정의
8. FOREIGN KEY(전공) REPERENCES 학과(학과코드) : '전공' 속성은 <학과> 테이블의 '학과코드' 속성을 참조하는 외래키
9. ON DELETE SET NULL : <학과> 테이블에서 튜플이 삭제되면 관련된 모든 튜플들의 '전공' 속성의 값을 NULL로 변경
10. ON UPDATE CASCADE, : <학과> 테이블에서 '학과코드'가 변경되면 관련된 모든 튜플의 '전공' 속성의 값도 같은 값으로 변경
11. CONSTRAINT 생년월일제약 CHECK (생년월일>='1980-01-01')); : '생년월일' 속성에는 1980-01-01 이후의 값만을 저장할 수 있으며, 이 제약 조건의 이름은 '생년월일제약'임

• CHAR : 항상 지정된 크기만큼 기억 장소가 확보됨
• VARCHAR : 기억 장소의 크기가 지정되어도 필드에 저장된 데이터만큼 기억 장소가 확보됨

118.5 CREATE VIEW

뷰를 정의하는 명령문

• 뷰(View) : 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블(Virtual Table)
• 표기 형식

CREATE VIEW 뷰명[(속성명[, 속셩명, ···])]
AS SELECT문;

118.5.1 예제

• <고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하라

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

118.6 CREATE INDEX

인덱스를 정의하는 명령문

• 인덱스(Index) : 검색 시간을 단축시키기 위해 만든 보조적인 데이터 도구
• 표기 형식

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [, 속성명[ASC | DESC]])
[CLUSTER];

• UNIQUE
  • 사용된 경우 : 중복 값이 없는 속성으로 인덱스를 생성
  • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성
• [ASC | DESC] : [ASC | DESC]는 생략이 가능하지만, 생략하지 않을 경우에는 'ASC'와 'DESC' 중에서 하나를 선택할 수 있다는 의미
  • 대괄호([ ])는 생략할 수 있다는 것을 표시하고, 대괄호 안의 '|'는 선택을 의미
• 정렬 여부 지정
  • ASC : 오름차순 정렬
  • DESC : 내림차순 정렬
  • 생략된 경우 : 오름차순으로 정렬
• CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨
• 클러스터드 인덱스(Clustered Index) : 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
  • 실제 데이터가 순서되로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있음
  • 하지만 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 함

118.6.1 예제

• <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하라

CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

118.7 ALTER TABLE

테이블에 대한 정의를 변경하는 명령문

• 표기 형식

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• ADD : 새로운 속성(열)을 추가할 때 사용
• ALTER : 특정 속성의 Default 값을 변경할 때 사용
• DROP COLUMN : 특정 속성을 삭제할 때 사용

118.7.1 예제 1

• <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성을 추가하라

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

118.7.3 예제 2

• <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경하라

ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

118.8 DROP

스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문

• 표기 형식

DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
• RESTRICT : 다른 개체가 제거할 요소를 참조중을 때는 제거를 취소

118.8.1 예제

• <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하라

DROP TABLE 학생 CASCADE;