정보처리산업기사 - SQL 활용 - DDL

※ 공부 내용의 복습 개념으로 정리된 글입니다. - 출처 시나공 

 

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

DDL(데이터 정의어)는 DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어입니다.

• DDL은 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러개의 테이블로서 저장됩니다.
• DDL에는 CREATE SCHEMA, CREATE DOMAIN, CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE, DROP 등이 있습니다.

 

CREATE SCHEMA

CREATE SCHEMA는 스키마를 정의하는 명령문입니다.

• 스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의합니다.

 

표기 형식

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

 

예제

소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문은 다음과 같습니다.

CREATE SCHEMA 대학교 AUTHORIZATION 홍길도;

 

 

※ 스키마(Schema)

스키마는 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세(Specitication)를 기술(Description)한 것으로 데이터 개체(Entity), 속성(Attribute), 관계(relationship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의합니다.

 

CREATE DOMAIN

CREATE DOMAIN은 도메인을 정의하는 명령문입니다.

• 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의할 수 있습니다.
• 정의된 도메인명은 일반적인 데이터 타입처럼 사용합니다.

표기 형식

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

※ 구문에서 대괄호([])의 의미

SQL문에서 [AS] 처럼 대괄호로 묶은 명령어들은 생략이 가능하다는 의미입니다.

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

 

예제

'성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현되는 도메인 SEX를 정의하는 SQL문은 다음과 같습니다.

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

 

SQL에서 지원하는 기본 데이터 타입

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

 

※ 도메인(Domain)

도메인이란 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합을 의미합니다.

예를 들어 학년 속성의 데이터 타입이 정수형이고 해당 속성에서 취할 수 있는 값의 범위가 1 ~ 4까지라면, 1 ~ 4라는 범위는 해당 속성에 지정된 정수형의 모든 범위가 아니라 일부분이므로 사용자는 1 ~ 4까지의 범위를 해당 속성의 도메인으로 정의해서 사용할 수 있다는 의미입니다. 쉽게 말하면 도메인은 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 사용자 정의 데이터 타입입니다.

 

 

CREATE TABLE

CREATE TABLE은 테이블을 정의하는 명령문입니다.

 

표기 형식

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

• 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본 값, NOT NULL 여부를 지정합니다.
• PRIMARY KEY : 기본키로 사용할 속성 또는 속성의 집합을 지정합니다.
• UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 UNIQUE로 지정한 속성은 중복된 값을 가질 수 없습니다.
• FOREIGN KEY ~ REFERENCES ~
  • 참조할 다른 테이블과 그 테이블을 참조할 대 사용할 외래키 속성을 지정합니다.
  • 외래키가 지정되면 참조 무결성의 CASCADE법칙이 적용됩니다.
  • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정합니다. 옵션에는 NO ACTION, CASCADE, SET NULL, SET DEFAULT가 있습니다.
  • ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정합니다. 옵션에는 NO ACTION, CASCADE, SET NULL, SET DEFAULT가 있습니다.
  • NO ACTION : 참조 테이블에 변화가 있어도 기본 테이블에는 아무런 조취를 취하지 않습니다.
  • CASCADE : 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경됩니다.
  • SET NULL : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경합니다.
  • SER DEFAULT : 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 기본값으로 변경합니다.
• CONSTRAINT : 제약 조건의 이름을 지정합니다. 이름을 지정할 필요가 없으면 CHECK절만 사용하여 속성 값에 대한 제약 조건을 명시합니다.
• CHECK : 속성 값에 대한 제약 조건을 정의합니다.

예제

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

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

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

 

다른 테이블을 이용한 테이블 정의

기존 테이블의 정보를 이용해 새로운 테이블을 정의할 수 있습니다.

 

표기 형식

CREATE TABLE 신규테이블명 AS SELCET 속성명[, 속성명, ...] FROM 기존테이블명;

• 기존 테이블에서 추출되는 속성의 데이터 타입과 길이는 신규 테이블에 그대로 적용됩니다.
• 기존 테이블의 NOT NULL의 정의는 신규 테이블에 그대로 적용됩니다.
• 기존 테이블의 제약 조건은 신규 테이블에 적용되지 않으므로 신규 테이블을 정의한 후 ALTER TABLE 명령을 이용해 제약 조건을 추가해야 합니다.
• 기존 테이블의 일부 속성만 신규 테이블로 생성할 수 있으며, 기존 테이블의 모든 속성을 신규 테이블로 생성할 때는 속성명 부분에 '*'를 입력합니다.

예제

<학생> 테이블의 '학번', '이름', '학년' 속성을 이용하여 <재학생> 테이블을 정의하는 SQL문을 작성하시오.

CREATE TABLE 재학생 AS SELECT 학번, 이름, 학년 FROM 학생;

 

 

※ 테이블(Table)

테이블은 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션(Relation)이라 부르고, 조작이나 검색 시에는 테이블이라고 부릅니다. 그러나 대부분은 테이블과 릴레이션을 구분  없이 사용하니 두 의미가 같다는 것만 알아두세요.

 

※ NOT NULL

NULL이란 모르는 값 또는 적용할 수 없는 값을 의미하는 것으로, NOT NULL은 특정 속성이 데이터 없이 비어 있어서는 안 된다는 것을 지정할 때 사용합니다.

 

※ CHAR과VARCHAR

CHAR은 항상 지정된 크기만큼 기억 장소가 확보되고, VARCHAR은 기억 장소의 크기가 지정되어도 필드에 저장된 데이터만큼만 기억 장소가 확보됩니다.

예를 들어 '이름' 속성의 자료형을 CHAR(15)로 지정하면 '이름'에 한 글자가 저장되어도 항상 15바이트가 기억 장소로 확보되지만, VARCHER(15)로 지정하면 저장된 한 글자 크기만큼만 기억 장소가 확보됩니다.

 

728x90

 

CREATE VIEW

CREATE VIEW는 뷰(View)를 정의하는 명령문입니다.

 

표기 형식

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

• SELECT문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성합니다.
• 서브 쿼리인 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없습니다.
• 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됩니다.

예제

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

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

 

※ 뷰(View)

뷰는 하나 이상의 기본 테이블로 부터 유도되는 이름을 갖는 가상 테이블(virtual Table)입니다. 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않습니다. 즉 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행됩니다.

 

※ 서브 쿼리(Sub Query)

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

 

CREATE INDEX

CREATE INDEX는 인덱스를 정의하는 명령문입니다.

 

표기 형식

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

• UNIQUE
  • 사용된 경우 : 중복 값이 없는 속성으로 인덱스를 생성합니다.
  • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성합니다.
• 정렬 여부 지정
  • ASC : 오름차순 정렬
  • DESC : 내림차순 정렬
  • 생략된 경우 : 오름차순으로 정렬됩니다.
• CULLSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됩니다.

예제

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

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

 

※ 인덱스(Index)

인덱스는 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조입니다.

 

※ [ASC | DESC]

대괄호([])는 생략할 수 있다는 것을 표시하고, 대괄호 안의 '|'는 선택을 의미합니다.

즉 [ASC | DESC]는 생략이 가능하지만, 생략하지 않을 경우에는 'ASC'와 'DESC' 중 에서 하나만 선택할 수 있다는 의미입니다.

 

※ 클러스터드 인덱스(Clustered Index)

인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식입니다.

실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있습니다. 하지만 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 합니다.

 

※ 넌 클러스터드 인덱스(Non-Clustered Index)

인덱스의 키 값만 정렬되어 있을 뿐 실제 데이터는 정렬되지 않는 방식입니다.

데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어집니다.

 

 

ALTER TABLE

ALTER TABLE은 테이블에 대한 정의를 변경하는 명령문입니다.

 

표기 형식

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

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

예제 1

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

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

 

예제2

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

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

 

DROP

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

 

표기 형식

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

• DROP SCHEMA : 스키마를 제거합니다.
• DROP DOMAIN : 도메인을 제거합니다.
• DROP TABLE : 테이블을 제거합니다.
• DROP VIEW : 뷰를 제거합니다.
• DROP INDEX : 인덱스를 제거합니다.
• DROP CONSTRAINT : 제약 조건을 제거합니다.
• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거합니다.
  즉 주 테이블의 데이터 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용됩니다.
• RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소합니다.

예제

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

DROP TABLE 학생 CASCADE;