카테고리 없음

SQL DDL

noddu 2023. 8. 23. 22:10
728x90
반응형
반응형

 

 

SQL 정의
  1. DBMS에서 데이터베이스로부터 데이터를 뽑아내서 사용 가능한 형식으로 나타내어 주는 소프트웨어 툴, 함수
  2. 독자적이고 상호 작용 형태로 터미널에서 많이 사용하고 있는 비절차적 언어이며, 고급명령어 형태의 독립된 데이터 조작어

 

SQL 분류
종류 내용
DDL(정의어) 데이터베이스 생성 · 제거
DCL(제어어) 데이터베이스 관리 · 제어
DML(조작어) 데이터베이스 검색 · 사용

 

DDL

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

종류 내용
CREATE SCHEMA, DOMAIN, TABLE, VIEW, INDEX 정의
ALTER TABLE 정의 변경
DROP SCHEMA, DOMAIN, TABLE, VIEW, INDEX 삭제

 

CREATE SCHEMA

스키마를 정의하는 명령문
스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의함

CREATE SCHEMA 스키마이름 AUTHORIZATION 사용자ID;

스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의함


DDL로 정의된 내용은 Meta-data가 되며, 시스템 카탈로그에 저장
시스템 카탈로그 - 시스템 그 자체에 관련이 있는 다양한 객체들에 관한 정보를 포함하는 시스템 데이터베이스 테이블


CREATE DOMAIN

도메인을 정의하는 명령문
정의된 도메인명은 일반적인 데이터 타입처럼 사용함

CREATE DOMAIN 도메인이름 Data-type

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


CREATE DOMAIN SEX CHAR(1)
DEFAULT '여'    // 도메인 무결성을 위해
CONSTRINT VALID-SEX CHECK ( VALUE IN ('남', '여', '?'));

무결성을 위해 기본(입력x)은 '여'로 설정하고
남, 여, ? 중 정의하게 제약을 준다


 

SQL에서 지원하는 기본 데이터 타입
구분 내용
정수(Integer) INT(4 Byte 정수), SMALLINT(2 Byte 정수)
실수(Float) FLOAT, REAL, DOUBLE PRECISION
형식화된 숫자 DEC(i, j)  단, i : 전체 자리수, j : 소수부 자리수
고정길이 문자 CHAR(n)  단, n : 문자수
가변길이 문자 VARCHAR(n)  단, n : 최대 문자수
고정길이 비트열(Bit String) BIT(n)
가변길이 비트열 VARBIT(n) 날짜 DATE, 날짜 데이터는 YYYY-MM-DD의 10자리로 표기
시간 TIME, 시간 데이터는 HH:MM:SS의 6자리로 표기

 

CREATE TABLE

테이블을 정의

CREATE TABLE 테이블이름 {
(속성명 DATA-TYPE),
PRIMARY KEY(기본키 속성명),
UNIQUE(대체키 속성명),
FOREIGN KEY(외래키 속성명),
REFERENCES 참조테이블(기본키 속성명),
CHECK(조건식);
};

예) 이름, 학번, 전공, 성별, 생년월일로 구성된 <학생> 테이블을 정의하는 SQL문을 작성하시오. 단, 제약 조건은 다음과 같다


이름 VARCHAR(18) NOT NULL,
PRIMARY KEY(학번)

이름은 NULL이 올 수 없고, 학번은 기본키다


FOREIGN KEY(전공) REFERENCES 학과(학과코드)

전공은 속성<학과> 테이블의 학과코드를 참조하는 외래키로 사용된다.


ON DELETE SET NULL

<학생> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만든다.


ON UPDATE CASCADE

학생 테이블에서 학과코드가 변경되면 전공 값도 같은 값으로 변경한다.

 

ON UPDATE 옵션 - 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블이 취해야할 사항을 지정함

구분 내용
NO ACTION 참조 테이블에 변화가 있어도 기본 테이블에는 아무런 조치를 취하지 않음
CASCADE 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경됨
SET NULL 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경함
SET DEFAULT 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 기본값으로 변경함

 

 

CREATE TABLE 학생 {
    이름 VARCHAR(18) NOT NULL,
    학번 CHAR(8),
    성별 SEX,
    생년월일 DATE,
    PRIMARY KEY(학번), UNIQUE(생년월일),
    FOREIGN KEY(전공) REFERENCES 학과(학과코드)
    ON DELETE SET NULL,
    ON UPDATE CASCADE,
    CONSTRAINT 생년월일제약
    CHECK(생년월일 >= '1994-12-24');
};

생년월일제약으로 1994-12-24 이후의 데이터만 저장할 수 있음


 

CREATE VIEW

뷰는 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블(virtual table)로, CREATE VIEW는 뷰를 정의하는 명령문

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

SELECT문을서브쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성함

서브쿼리 - 조건절에 주어진 질의로서, 상위 질의에 앞서 실행되며 그 검색 결과는 상위 질의의 조건절의 피연산자로 사용됨

서브쿼리인 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없음

속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됨


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

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


 

CREATE INDEX

인덱스는 검색을 빠르게 하기 위해 만든 보조적인 데이터 구조이며, CREATE INDEX는 인덱스를 정의하는 명령문

UNIQUE 옵션

사용하는 경우 생략하는 경우
기본키나 대체키 같은 중복되는 값이 없는 속성으로 인덱스를 생성할 때 사용함 중복 값을 허용하는 속성으로 인덱스를 생성할 때 사용

 

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

<고객> 테이블에서 중복을 허용하지 않는 특성을 갖는 고객번호 속성에 대해 내림차순 정렬하여

'고객번호_Idx'라는 이름으로 인덱스를 정의


CREATE TRIGGER

레코드의 삽입, 삭제, 갱신 등으로 테이블에 변동사항이 생겼을 때 자동으로 수행되는 것으로 저장 프로시저의 일종

트리거는 무결성을 강화하고 관련 테이블의 데이터를 일치시킬 때 주로 사용

 

프로그램 단위의 하나인 트리거는 테이블, 뷰, 스키마, 데이터베이스에 관련된 프로시저와 관련된 특정 사건(event)이 발생할 때마다 묵시적(자동)으로 실행이 이루어짐(fire)

 

트리거는 명시적인 호출(실행)을 할 수 없으며, 특정 사건(event)에 의해서만 묵시적인 자동실행(fire)이 이루어짐

데이터베이스 트리거를 너무 많이 생성하게 되면 관련 오브젝트에 대한 복잡한 종속관계가 구성되므로 성능저하를 가져올 수 있음(순환적이거나 상호 참조가 이루어지지 않도록 트리거에 대한 설계를 신중히)

CREATE OR REPLACE TRIGGER secure_emp
    BEFORE INSERT ON emp
BEGIN
    IF (TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN')) OR
       (TO_CHAR(SYSDATE, 'HH24:MI') NOT
       BETWEEN '08:00' AND '18:00') THEN
       RAISE_APPLICATION_ERROR(-20500, 'You may insert
       into EMP table only during business hours.');
    END IF;
END

생성된 트리거는 EMP 테이블에 INSERT문이 실행될 때에만 자동 호출

INSERT문이 성공했다면, 정상적인 시간대에 작업한 것
ORA-20500 예외가 발생했다면, 비정상적인 시간대에 작업한 결과일 것

트리거의 실패는 INSERT문의 실패와 직결

트리거가 비정상 종료되면 관련 DML문도 실패함(한 테이블에 대해 INSERT문에 관련된 트리거가 여러 개 있을 경우 INSERT문의 성공은 관련 트리거가 모두 성공해야만 함)


CREATE TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] TABLE AS 테이블명
FOR EACH ROW
WHEN 조건식
트리거 BODY

 

동작시기 옵션 - 트리거가 실행될 때를 지정

구분 내용
AFTER 테이블이 변경된 후에 트리거가 실행됨
BEFORE 테이블이 변경되기 전에 트리거가 실행됨 



CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생

 

동작 옵션 - 트리거가 실행되게 할 작업의 종류를 지정

구분 내용
INSERT 테이블의 레코드를 삽입할 때 트리거가 실행됨
DELETE 테이블의 레코드를 삭제할 때 트리거가 실행됨
UPDATE 테이블의 레코드를 수정할 때 트리거가 실행됨

 

 

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW TABLE AS new_table

 

테이블 선택 옵션 - 트리거가 작동될 테이블의 종류를 지정

구분 내용
NEW 새로 추가되거나 변경에 참여할 튜플들의 집합(테이블)에 트리거가 적용
OLD 변경된 튜플들의 집합(테이블)에 트리거가 적용 



WHEN new_table.학년 = ' '
BEGIN
    SET new_table.학년 = '신입생';
END;

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;

<학생> 테이블에 새로운 레코드가 삽입될 때,

삽입되는 레코드에 학년 정보가 누락되었으면 학년 필드에 '신입생'을 치환하는 트리거를 '학생정보_tri'라는 이름으로 정의


ALTER TABLE

테이블에 대한 정의를 변경

구분 내용
추가 ALTER TABLE 테이블이름 ADD 속성이름 DATA-TYPE [DEFAULT 값]; 
수정 ALTER TABLE 테이블이름 ALTER 속성이름 DATA-TYPE [SET DEFAULT 값];
삭제 ALTER TABLE 테이블이름 DROP 속성이름 DATA-TYPE [CASCADE]; 



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

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


ALTER TABLE 학생 ALTER 성별 SET DEFAULT '남';
ALTER TABLE 학생 DROP 생년월일;

<학생> 테이블에 성별 기본값은 남, 생년월일 속성을 DROP


DROP

스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 트리거를 제거하는 명령문

구분 내용
CASCASE 제거할 개체를 참조하는 다른 모든 개체를 함께 제거함
RESTRICT 다른 개체가 제거할 개체를 참조 중일 경우 제거가 취소됨

 

DROP TABLE 학생 CASCADE;

<학생> 테이블을 제거하는 SQL문 (참조하는 모든 데이터도 함께 제거)

 

DROP TABLE 학생 RESTRICT;

<학생> 테이블을 제거하는 SQL문 (참조하고 있으면 제거되지 않는다)

반응형
저작자표시 비영리 변경금지