SQL 응용 데이터베이스 기본 2/3
트랜잭션
DDL
데이터 정의어(DDL, Data Definition Language)의 개념
데이터를 담는 그릇을 정의하는 명령어
DDL의 대상
도메인(Domain) : 하나의 속성이 가질 수 있는 원자값들의 집합
스키마(Schema) : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
- 외부 스키마(External Schema) : 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조
- 개념 스키마(Conceptual Schema) : 모든 사용자 관점에서 보는 데이터베이스의 전체적인 논리적 구조
- 내부 스키마(Internal Schema) : 물리적 저장 장치의 관점에서 보는 데이터베이스 구조
테이블(Table) : 데이터 저장 공간
뷰(View) : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index) : 검색을 빠르게 하기 위한 데이터 구조
테이블
테이블의 개념
- 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체
- 하나의 데이터베이스 내에 여러 개의 테이블로 구성될 수 있고, 릴레이션(Relation) 혹은 엔티티(Entity)라고도 불림
테이블의 용어
튜플(Tuple)/행(Row) : 테이블 내의 행을 의미하며 레코드(Record)라고도 함
애트리뷰트(Attribute)/열(Column) : 테이블 내의 열을 의미
식별자(Identifier) : 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각 구분할 수 있는 논리적인 개념
카디널리티(Cardinality) : 튜플의 개수
차수(Degree) : 애트리뷰트의 개수
도메인(Domain) : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합
뷰
뷰의 개념
- 논리 테이블로서 사용자에게(생성 관점 아닌 사용 관점) 테이블과 동일
- 실제 데이터를 갖고 있진 않지만, 테이블이 수행하는 임무를 수행하기 때문에 ‘가상 테이블(Virtual Table)’이라고도 불림
뷰의 특징
논리적 데이터 독립성 제공 : 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
보안 기능(접근제어) 제공 : 특정 필드만을 선택해 뷰를 생성할 때 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가
뷰 변경 불가 : 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음(정의할 때는 CREATE 문 사용, 제거할 때는 DROP 문 사용)
뷰의 목적
- 주된 이유는 단순 질의어를 사용할 수 있기 때문
- FROM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성
- 테이블의 중요 데이터 일부만을 제공할 수 있는 장단점
뷰의 장점
논리적 독립성 제공 : 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용 가능
데이터 보안의 용이 : 중요 보안 데이터를 저장 중인 테이블에는 접근 불허
뷰의 단점
뷰 자체 인덱스 불가 : 논리적으로 존재하는 뷰에 물리적인 데이터를 대상으로 하는 인덱스를 생성할 수 없음
뷰 정의 변경 불가 : 정의를 변경하려면 삭제 후 재작성해야 함
데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음
인덱스
인덱스의 개념
- 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
- 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조
- 테이블의 특정 레코드 위치를 알려 주는 용도로 사용
인덱스의 특징
- 기본 키(PK, Primary Key) 컬럼은 자동으로 인덱스 생성
- 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
- 테이블 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용 검색(테이블 전체 스캔, Table Full Scan)
- 인덱스가 생성되어 있을 때, 데이터를 빠르게 찾을 수 있음(인덱스 범위 스캔, Index Range Scan)
- 조건절에 ‘=’로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음
인덱스의 종류
순서 인덱스(Ordered Index) : 데이터가 정렬된 순서로 생성되는 인덱스
해시 인덱스(Hash Index) : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
비트맵 인덱스(Bitmap Index) : 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
함수 기반 인덱스(Functional Index) : 수식이나 함수를 적용하여 만든 인덱스
단일 인덱스(Singled Index) : 하나의 컬럼으로만 구성한 인덱스
결합 인덱스(Concatenated Index) : 두 개 이상의 컬럼으로 구성한 인덱스
클러스터드 인덱스(Clustered Index) : 기본 키(PK) 기분으로 레코드를 묶어서 저장하는 인덱스
DDL 명령어
CREATE : 데이터베이스 오브젝트 생성
ALTER : 데이터베이스 오브젝트 변경
DROP : 데이터베이스 오브젝트 삭제
TRUNCATE : 데이터베이스 오브젝트 내용 삭제
TABLE 관련 DDL
CREATE TABLE
-- 테이블 생성
CREATE TABLE 테이블명
(
컬럼명 데이터타입 [제약조건],
...
);
CREATE TABLE 제약조건
PRIMARY KEY(기본 키) : 테이블의 기본 키 정의, 유일하게 테이블의 각 행을 식별
FOREIGN KEY(외래 키) : 테이블의 외래 키 정의, 참조 대상을 테이블(컬럼명)로 명시, 열과 참조된 테이블의 열 사이의 외래 키 관계를 적용하고 설정
UNIQUE : 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건
NOT NULL : 해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약조건
CHECK : 개발자가 정의하는 제약조건으로 참(TRUE)이어야 하는 조건을 지정
DEFAULT : 데이터를 INSERT 할 때 해당 컬럼의 값을 넣지 않는 경우 기본값으로 설정해 주는 제약조건
ALTER TABLE
-- 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];
-- 컬럼 수정
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];
-- 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
DROP TABLE
-- 테이블 삭제
DROP TABLE 테이블명 [CASCADE|RESTRICT];
-- CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
-- RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
TRUNCATE TABLE
-- 테이블 내 데이터 삭제
TRUNCATE TABLE 테이블명;
VIEW 관련 DDL
CREATE VIEW
-- 뷰 생성
CREATE VIEW 뷰이름 AS
조회쿼리;
CREATE OR REPLACE VIEW
-- 뷰 교체
CREATE OR REPLACE VIEW 뷰이름 AS
조회쿼리;
DROP VIEW
-- 뷰 삭제
DROP VIEW 뷰이름;
INDEX 관련 DDL
CREATE INDEX
-- 인덱스 생성
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
ALTER INDEX
-- 인덱스 수정
ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
DROP INDEX
-- 인덱스 삭제
DROP INDEX 인덱스명;