[정보처리기사][실기]2과목 10 SQL – DDL

1. DDL의 개념

- 스키마, 도메인, 테이블, 뷰, 인덱스를 정의, 정의 변경, 제거

- DDL로 정의된 내용은 메타데이터가 됨-> 시스템 카탈로그에 저장(데이터사전)

    스키마!! 가 메타데이터.. 데이터 사전에 저장된 정보임

CREATE	스키마, 도메인, 테이블, 인덱스, 뷰, 트리거를 정의
ALTER	테이블에 대한 정의를 변경
DROP	스키마, 도메인, 테이블, 인덱스, 뷰, 트리거를 제거(CONSTRAINT 도 제거..제약조건)

 

2. CERATE SCHEMA

- 스키마 : 하나의 응용(사용자)에 속하는 테이블과 기타 구성 요소 등을 그룹 짓기 위한 것

- 스키마 이름, 해당 스키마 소유권자나 허가권자를 정의-> 스키마 식별

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;

CREATE SCHEMA 대학명 AUTHORIZATION 홍길동;

 

3. CERATE DOMAIN

- 도메인 :	하나의 속성이 취할 수 있는 동일한 타입의 원자 값들의 집합.
	특정 속성에서 사용 할 데이터 범위를 사용자가 정의하는 사용자 정의 데이터 타입
	임의의 속성에서 취할수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때 그 값의 범위를 도메인으로 정의할 수 있음

- 정의된 도메인 명은 일반적인 데이터 타입처럼 사용

(SEX 도메인은 이미 도메인이 CREATE 되어 있어 SEX로만 쳐도 도메인으로 바로 사용 가능)

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

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

* 데이터 타입

정수	INT(4Byte) SMALLINT(2Byte)	고정길이 비트열	BIT(n)
실수	FLOAT REAL DOUBLE PRECISION	가변길이 비트열	VARBIT(n)
형식화된 숫자	DEC(i ,j) i:전체 자릿수, j:소수부 자릿수	날짜	DATE
고정길이 문자	CHAR(n) n:문자 수	시간	TIME
가변길이 문자	VARCHAR(n) n:최대 문자 수

 

4. CERATE TABLE

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

기본 테이블에 포함될 모든 속성에 대해 속성명, 속성의 데이터 타입, NOT NULL(생략가) 지정 PRIMARY KEY : 기본키 UNIQUE : 대체키 FOREIGN KEY ~ REFERENCES ~ : 외래키, 참조 테이블. 외래키 지정되면 참조 무결성의 CASCADE 법칙 적용 됨

FOREIGN KEY ~ 에서의 옵션 - NO ACTION - CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경 - SET NULL - SEL DEFAULT	->	ON DELETE 옵션 : 참조 테이블 튜플 삭제 시 기본테이블이 취해야 할 사항 ON UPDATE 옵현 : 참조 테이블의 속성 값이 변경되었을 때 기본 테이블이 취해야 할 사항
- CONSTRAINT : 제약조건의 이름 지정. 이름 지정할 필요 없으면 CHECK절만 사용해 속성 값에 대한 제약조건 명시 - CHECK : 속성 값에 대한 제약 조건 지정

CREATE TABLE 학생 (이름 VARCHAR(15) NOT NULL, 학번 CHAR(8), 전공 CHAR(5), 성별 SEX,-- 앞에서 미리 정의해 둔 도메인 SEX-- 생년월일 DATE, PRIMARY KEY(학번), FOREIGN KEY(전공) REFERENCES 학과(학과코드) ON DELETE SET NULL ON UPDATE CASCADE CONSTRAINT 생년월일제약 CHECK(생년월일>=1980-01-01));

기본키 지정하는 다른 방법 선언 시 속성 옆에 예약어 PRIMARY KEY를 추가   FOREIGN 절에 CONSTRAINT 절 사용 외래키에 이름 달기 가능 CONSTRAINT 외래키 이름 FOREIGN KEY (외래키 속성명) REFERENCES 참조테이블(기본키)

 

5. CERATE VIEW

- 뷰 : 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블

CREATE VIEW 뷰명[속성명[, 속성명,, ...])] AS SELECT문 ;	- SELECT 문을 서브쿼리로 사용해서 SELECT문의 결과로서 뷰를 생성 속성명을 기술하지 않으면 SELECT문의 속성명 자동 사용
CREATE VIEW 안산고객(성명, 전화번호) AS SELECT 성명, 전화번호 FROM 고객 WHERE 주소 = ‘안산시’ ;

 

6. CERATE INDEX

- 인덱스 : 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명({속성명 [ASC | DESC] [, 속성명 [ASC | DESC]]}) [CLUSTER] ;	- UNIQUE : 사용되면 중복 값 없는 속성으로 인덱스 생성 / 사용 안 되면 중복 값 있는 속성으로 생성 ASC : 오름차순 정렬 (생략 시 기본) DESC : 내림차순 정렬 CLUSTER : 지정된 키에 따라 튜플들을 그룹으로 지정
CREATE UNIQUE INDEX 고객번호_IDX ON 고객(고객번호 DESC) ;

 

  

7. CERATE TRIGGER

- 트리거 :	데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때 마다 자동적으로 수행되는 사용자 정의 프로시저
	SQL의 제약조건 방법을 통해 명시할 수 없는 무결성 제약조건을 구현, 관련 테이블의 데이터 일치시킬 때 사용
CREATE TRIGGER 트리거명 [동작시기][동작옵션] ON 테이블명 REFERENCING [NEW|OLD] TABLE AS 테이블명 FOR EACH ROW WHEN 조건식 트리거 BODY		동작 시기 옵션 : 트리거가 실행 될 때 지정 AFTER : 테이블 변경 된 후 BEFORE : 테이블 변경 전 동작 옵션 : 트리거 실행(발동)시킬 작업 종류 INSERT / DELETE / UPDATE 테이블 선택 옵션 : 트리거가 적용될 테이블 종류 NEW : 새로 추가되거나 변경에 참여할 튜플 들의 집합 OLD : 변경된 튜플들의 집합(테이블)에 트리거 적용
WHEN : 트리거가 실행되면서 지켜야 할 조건 트리거 BODY : 트리거의 본문 코드 입력 부분 BEGIN 으로 시작 ~ END 로 끝 적어도 하나 이상의 SQL구문 있어야 함(없음 오류 발생) 변수 치환 시 예약어 SET 사용
CREATE TRIGGER 학년정보_TRI BEFORE INSERT ON 학생 REFERENCING NEW TABLE AS new_table FOR EACH ROW WHEN new_table.학년 = ‘’ BEGIN SET new_table.학년 = ‘신입생’ END ;

 

8. ALRTER TABLE

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

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT ‘기본값’]; ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT ‘기본값’]; ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];	ADD : 새 속성 추가 ALTER : 속성 기본 값 변경 DROP COLUMN : 속성 제거
ALTER TABLE 학생 ADD 학년 VARCHAR(3);

 

9. DROP

- 스키마, 도메인, 테이블, 뷰, 인덱스, 트리거, 제약조건(CONSTRAINT) 제거

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

CASCADE : 제거할 개체를 참조하는 다른 모든 개체 함께 제거 참조 무결성 제약 조건을 설정하기 위해 사용 RESTRICT : 다른 개체가 제거할 개체를 참조중일 경우 제거가 취소됨