SQL 응용 | 데이터베이스 기본 _DDL

적자생존

01. 데이터베이스 기본

 

1. 트랜잭션

 

(2) 데이터 정의어 (DDL ; Data Definition Language)

- 데이터 정의어는 데이터를 정의하는 언어로 '데이터를 담는 그릇'을 정의하는 언어
- 테이블과 같은 데이터 구조 정의에 사용되는 명령어
- 특정 구조 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들

[1] DDL 대상

 

• 스키마 (Schema) - 시험 출제!!

- 데이터베이스 구조, 제약조건 등의 정보를 담고 있는 기본적 구조
- 외부/개념/내부 3계층으로 구성

• 스키마의 구성

1. 외부 스키마 (External Schema)
- 사용자나 개발자 관점에서 필요로 하는 데이터베이스의 논리적 구조
- 사용자 뷰를 나타냄
- 서브 스키마로 불림

2. 개념 스키마 (Conceptual Schema)
- 데이터베이스의 전체적인 논리적 구조
- 전체적인 뷰를 나타냄
- 개체 간 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의

3. 내부 스키마 (Internal Schema)
- 물리적 저장 장치 관점에서 보는 데이터베이스 구조
- 실제 데이터베이스에 저장될 레코드 형식 정의하고 저장 데이터 항목 표현 방법, 내부 레코드 물리적 순서 등 표현

• 테이블 (Table)

- 데이터를 저장하는 항목인 필드(Field) 들로 구성된 데이터 집합체
- 하나의 DB 내 여러 개 테이블로 구성될 수 있고, 릴레이션(Relation) 혹은 엔티티(Entity)라고 불림

• 튜플 (Tuple) / 행 (Row) : 테이블 내 행을 의미, 레코드 (Record)라고도 함,  튜플은 테이블에서 같은 값 가질 수 X
• 속성(Attribute) / 열(Column) : 테이블 내 열을 의미, 열의 개수를 차수(Degree) 라고 함
• 카디널리티 (Cardinality) : 튜플의 개수
• 차수 (Degree) : 속성의 개수
• 도메인 (Domain): 하나의 속성이 취할 수 있는 같은 타입 원자값들의 집합

 

• 뷰 (View)

- 논리 테이블로 사용자에게 ( 생성 관점이 아닌 사용 관점에서) 테이블과 동일
- '테이블 A'와 '테이블 B'는 물리 테이블을 의미, '뷰 C'는 두 개의 테이블을 이용해서 생성한 뷰를 의미

- 뷰는 '테이블 A'와 같은 하나의 물리 테이블로부터 생성 가능
- 다수의 테이블 또는 다른 뷰를 이용해 만들 수 있음
- 뷰와 동일한 결과를 만들기 위해 '조인' 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어 사용 가능

조인 (Join)

결합을 의미, 관계형 데이터베이스에서 조인은 교집합 결과를 가지는 결합 방법을 의미

• 뷰의 장단점

장점

1. 논리적 독립성 제공
- 데이터베이스에 영향 없이 애플리케이션이 원하는 형태로 데이터에 접근

2. 데이터 조작 연산 간소화
- 애플리케이션이  원하는 형태의 논리적 구조 형성하여 데이터 조작 연산 간소화
- 복수 테이블에 존재하는 여러 종류 데이터에 대한 단순 질의어 사용 가능

3. 보안 기능 (접근 제어) 제공
- 특정 필드만 선택해 뷰 생성할 경우, 애플리케이션은 선택되지 않은 필드 조회 및 접근이 불가
- 중요 보안 데이터 저장 중인 테이블이나 컬럼에 접근 불허

단점

1. 뷰 자체 인덱스 불가
- 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스 가지지 못함

2. 뷰 변경 불가
- 뷰 정의를 변경 시, 뷰 삭제하고 재생성
- 뷰 정의는 ALTER 문 이용하여 변경할 수 없음  >  뷰는 CREATE 문을 사용하여 정의, 뷰 제거 시에는 DROP 문 사용

3. 데이터 변경 제약 존재
- 뷰 내용에 대한 삽입, 삭제, 변경 제약 있음

• 인덱스 (Index)

- 인덱스는 검색 연산 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
- 데이터를 빠르게 찾을 수 있는 수단으로, 테이블에 대한 조회 속도를 높여 주는 자료 구조
- 테이블 특정 레코드 위치 알려 주는 용도로 사용

 

데이터베이스 파일 구조

1. 순차 방법
- 레코드들의 물리적 순서가 레코드들의 논리적 순서와 같게 순차적으로 저장하는 방법

2. 인덱스 방법
- 인덱스가 가리키는 주소를 따라 원하는 레코드에 접근할 수 있도록 하는 방법

<키 값, 주소> 쌍으로 구성

3. 해싱 방법
- 키값을 해시 함수 (Hash Function)에 대입시켜 계산한 결과를 주소로 사용하여 레코드에 접근할 수 있게 하는 방법


=> 인덱스는 파일 구조 중 인덱스 방법에 해당

 

• 인덱스 특징

- 기본 키(PK : Primary Key) 컬럼은 자동 인덱스 생성
- 연월일이나 이름 기준 인덱스는 자동 생성되지 않음
- 테이블 컬럼에 인덱스 없는 경우, 테이블 전체 내용 검색 > 테이블 전체 스캔 = Table Full Scan
- 인덱스 생성되었을 때, 데이터 빠르게 찾을 수 있음 > 인덱스 범위 스캔 = Index Range Scan
- 조건절에 '='로 비교되는 컬럼 대상으로 인덱스 생성하면 검색 속도 높일 수 있음

[3] DDL 명령어

 

• 생성

- CREATE : 데이터베이스 오브젝트 생성 명령어

• 수정

- ALTER : 데이터베이스 오브젝트 변경 명령어

• 삭제

- DROP : 데이터베이스 오브젝트 삭제 명령어
- TRUNCATE : 데이터베이스 오브젝트 내용 삭제 명령어

DDL 명령어

크알드트

CREATE / ALTER / DROP /TRUNCATE

> 크리스마스를 위한 계란 알 두 트럭 (ZZZZZZ...)

 

[4] TABLE 관련 DDL  - 시험 출제!! (2022.3)

• CREATE TABLE

- 테이블 생성 명령어
- 하나의 칼럼(속성)에 대해 ' 컬럼명 데이터타입 제약 조건 ' 으로 구성

* 책에 수록된 문법은 국제 표준 ISO/IEC 9075 기준

-- CREATE TABLE 기본 문법

CREATE TABLE 테이블명
(
   컬럼명 데이터타입 [DEFAULT 값] [NOT NULL]
   [, PRIMARY KEY (기본키, ...)]
   [, UNIQUE (컬럼명, ...)]
   [, FOREIGN KEY (외래키, ...) REFERENCES 참조테이블 (기본키)
   [, CONSTRAINT 제약조건명 CHECK (조건식) ]
);

-- CREATE TABLE 기본 문법 예시 

CREATE TABLE 사원
(
   사번 VARCHAR(10) NOT NULL,
   부서번호 VARCHAR(20),
   생년월일 DATE NOT NULL,
   성별 CHAR(1),
   PRIMARY KEY(사번),
   FOREIGN KEY(부서번호) REFERENCES 부서(부서코드),
   CONSTRAINT 성별제약 CHECK 성별 IN('M', 'F')
);

예제 속 사원 테이블은

VARCAHR(10) 타입인 사번 컬럼 : NULL X
VARCHAR(20) 타입 부서번호 컬럼
DATE 타입 생년월일 컬럼 : NULL X
CHAR(1) 타입 성별 컬럼

존재

기본 키 : 사번 컬럼
외래키 : 부서 번호 > 부서 테이블의 부서코드 컬럼 참조

제약조건 : 성별 제약 > 성별 컬럼은 'M', 'F' 값만 허용

 

• CREATE TABLE 제약조건

1. PRIMARY KEY
- 테이블 기본 키 정의
- 유일하게 테이블 각 행 식별

2. FOREIGN KEY
- 외래 키 정의
- 참조 대상을 테이블 (컬럼명)로 표시
- 열과 참조된 테이블의 열 사이의 외래 키 관계 적용하고 설정

3. UNIQUE
- 테이블 내에서 얻은 유일한 값 갖도록 하는 제약조건

4. NOT NULL
- 해당 칼럼 NULL 값 포함하지 않게 하는 제약조건

5. CHECK
- 개발자가 정의하는 제약조건
- 참(TURE)이어야 하는 조건 지정

6. DEFAULT
- 데이터 INSERT 할 때, 해당 컬럼 값 넣지 않는 경우, 기본값으로 설정하는 제약조건

* DDL 하나에 모든 제약조건 적용 X
-> 속성 특징에 따라 필요 제약조건만 골라서 사용

 

• ALTER TABLE

- 테이블 수정 명령

• ALTER TABLE 컬럼 추가

- 테이블에 필요한 컬럼 추가하는 문법
- CREATE TABLE 컬럼에 사용되는 제약조건인 PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT 를 ALTER TABLE에서도 사용 가능

-- ALTER TABLE 컬럼 추가 문법

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

-- ALTER TABLE 컬럼 추가 문법

ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE;
# 사원 테이블에 타입은 VARCHAR(11)이면서 유일한 값을 가지는 전화번호 컬럼 추가

 

• ALTER TABLE 컬럼 수정

- 테이블에 필요한 컬럼 수정하는 문법
- 테이블 생성을 위한 CREATE 문에 제약조건 명시 후 ALTER 문으로 테이블 제약조건 변경 가능

-- ALTER TABLE 컬럼 수정 문법

ALTER TABLE 테이블명 ALTER 컬럼명 데이터타입 [제약조건];

-- ALTER 테이블 컬럼 수정 문법 

ALTER TABLE 사원 ALTER 이름 VARCHAR(30) NOT NULL;

# 사원 테이블의 이름 칼럼을 타입은 VARCHAR(30)이며 NULL 값을 가지지 않도록 수정

 

• DROP TABLE

- 테이블 삭제 명령

-- DROP TABLE 문법

DROP TABLE 테이블명 [CASCADE | RESTRICT];

-- DROP TABLE 문법 예시 

DROPT TABLE 사원;
# 사원 테이블 삭제 

- DROP TABLE 명령 옵션에 CASCADE와 RESTRICT 존재
- CASECADE와 RESTRICT 의 경우, 외래 키 (FOREIGIN KEY) 걸려 있을 때 해당

• DROP TABLE 명령어 옵션

- CASCADE : 참조 테이블까지 연쇄적으로 제거하는 옵션
- RESTRICT : 다른 테이블이 삭제할 테이블 참조 중인 경우, 제거하지 않는 옵션

• TRUNCATE TABLE

- 테이블 내 데이터들 삭제하는 명령

-- TRUNCATE TABLE 문법

TRUNCATE TABLE 테이블명;

-- TRUNCATE TABLE 문법

TRUNCATE TABLE 사원;
# 사원 테이블 내 모든 데이터 삭제

[5] VIEW 관련 DDL

 

• CREATE VIEW

- 뷰 생성 명령

-- CREATE VIEW 문법

CREATE VIEW 뷰이름 AS
조회쿼리;

-- CREATE VIEW 문법 

# 사원 테이블에 사번, 업무, 이름, 생년월일, 성별, 입사일 컬럼 있을 때 뷰 테이블 작성

CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
  FROM 사원
 WHERE 성별 ='M';
 
# 사원 테이블에서 성별 값이 'M'인 행의 사번, 이름 컬럼 값으로 구성된 '사원뷰' 라는 이름의 뷰 생성

- VIEW 테이블 SELECT 문에는 UNION 이나 ORDER BY 절 사용 불가
- 컬럼명 기술하지 않으면 SELECT 문 컬렴명이 자동 사용

- UNION

집합연산자로 중복 행이 제거된 쿼리 결과 집합

- ORDER BY 절

속성값 정렬할 때 사용

> ASC = 오름차순, DESC = 내림차순  ( 키워드 생략 시, ASC 오름차순 정렬)  

 

• CREATE OR REPLACE VIEW

- 뷰 교체 명령
- OR REPLACE 키워드 추가하는 것 제외, CREATE VIEW와 사용 방법 동일

-- CREATE OR REPLACE VIEW 기본문법

CREATE OR REPLACE VIEW 뷰이름 AS
조회쿼리;

 

* 뷰는 수정 (ALTER) 불가

삭제 (DROP) 후에 다시 생성 (CREATE) 하거나
CREATE OR REPLACE VIEW 명령 통해 기존 뷰 교체

 

• DROP VIEW

- 뷰 삭제 명령어

-- DROP VIEW 기본 문법

DROP VIEW 뷰이름;

 

[6] INDEX 관련 DDL

 

• CREATE INDEX

- 인덱스 생성 명령
- UNIQUE는 생략 가능, 인덱스 걸린 컬럼은 중복 값 허용 X
- 복수 컬럼을 인덱스로 걸 수 있음

-- CREATE INDEX 문법

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2, ...);

-- CREATE INDEX 문법 예시 

CREATE INDEX 사번인덱스 ON 사원(사번);
# 사원 테이블의 사번 컬럼에 대해 '사번인덱스'라는 인덱스 명으로 인덱스 생성

 

• ALTER INDEX

- 인덱스 수정 명령어
- 일부 DBMS는 ALTER INDEX 제공 X
- 기존 인덱스 삭제하고 신규 인덱스 생성하는 방식 사용 권고

-- ALTER INDEX 문법

ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2, ...);

-- ALTER INDEX 문법 

ALTER INDEX 사번인덱스 ON 사원(사번);
# 사원테이블의 사번 컬럼을 '사번인덱스'라는 인덱스 명으로 인덱스 수정

 

• DROP INDEX

- 인덱스 삭제 명령

-- DROP INDEX 문법

DROP INDEX 인덱스명;

-- DROP INDEX 문법 

DROP INDEX 사번인덱스;
# 사번인덱스라는 인덱스 명 가지는 인덱스 삭제