3-2. SQL 활용

2020 기출 ' ' 
2021 기출 ' ' 
수제비 데일리 ' ' 

기본 SQL 작성

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

데이터 정의어는 데이터를 정의하는 언어로서 '데이터를 담는 그릇을 정의하는 언어'이다.

 

데이터 정의어(DDL) 대상 (도스테뷰인)

DDL 대상	설명
도메인(Domain)	하나의 속성이 가질 수 있는 원자값들의 집합
스키마(Schema)	데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
테이블(Table)	데이터 저장 공간
뷰(View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

 

데이터 정의어(DDL) 명령어

구분	DDL 명령어	설명
생성	CREATE	데이터베이스 오브젝트 생성
수정	ALTER	데이터베이스 오브젝트 변경
삭제	DROP	데이터베이스 오브젝트 삭제
	TRUNCATE	데이터베이스 오브젝트 내용 삭제

 

---

(2) 관계형 데이터 모델(Relation Data Model)

• 관계형 데이터 모델은 보편적인 데이터 모델로, 계층 모델과 망 모델의 복잡한 구조를 단순화시킨 모델이다.
• 관계 모델의 대표적 언어로는 SQL이 있다.
• 데이터 간의 관계를 기본 키와 이를 참조하는 외래 키로 표현한다.
• 테이블 간 관계를 1:1, 1:N, M:N 관계로 목적에 맞게 표현한다.

 

(3) 트랜잭션(Transaction)

• 트랜잭션은 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위이다.
• 트랜잭션은 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이다.

 

트랜잭션의 특징 (ACID)

특징	설명
원자성(Atomicity)	트랜잭션 연산은 데이터베이스에 모두 반영되든지 아니면 전혀 반영되지 않아야 함
일관성(Consistency)	트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환
격리성(Isolation)	트랜잭션 실행 중 실행하는 연사으이 중간 결과를 다른 트랜잭션이 접근 불가
영속성(Durability)	성공적으로 완료된 트랜잭션의 결과는 시스템이 고장 나더라도 영구적으로 반영되어야 함

 

트랜잭션 연산(원자성 주요 기법)

연산	설명
커밋(Commit)	하나의 트랜잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있거나 하나의 트랜잭션이 끝났을 때 사용하는 연산
롤백(Rollback)	하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산

 

병행 제어(일관성 주요 기법)

병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법이다.

 

병행 제어의 목적

• 데이터베이스의 공유를 최대화한다.
• 시스템의 활용도를 최대화한다.
• 데이터베이스의 일관성을 유지한다.
• 사용자에 대한 응답시간을 최소화한다.

 

병행 제어 미보장 시 문제점

갱신 손실(Lost Update) / 현황 파악오류(Dirty Read) / 모순성(Inconsistency) / 연쇄복귀(Cascading Rollback)

 

병행 제어 기법의 종류

• 로킹(Locking)
• 낙관적 검증
• 타임 스탬프 순서(Time Stamp Ordering)
• 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)

 

로킹(Locking)의 특징

• 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있다.
• 로킹 단위가 작아지면 데이터베이스 공유도, 로킹 오버헤드가 증가한다.
• 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 한다.

 

데이터베이스 고립화 수준(격리성 주요 기법)

고립화 수준은 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도이다.

 

고립화 수준 종류

Read Uncommitted / Read Committed / Repeatable Read / Serializable Read

 

회복 기법(영속성 주요 기법)

회복 기법은 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업이다.

 

회복 기법 종류

• 로그 기반 회복 기법
  • 지연 갱신 회복 기법(Deferred Update)
  • 즉각 갱신 회복 기법(Immediate Update)
• 체크 포인트 회복 기법(Checkpoint Recovery)
• 그림자 페이징 회복 기법(Shadow Paging Recovery)

 

트랙잰션의 상태 변화

트랙잰션의 상태전이도

• 활동 상태(Active)
• 부분완료 상태(Partially Committed)
• 완료 상태(Committed)
• 실패 상태(Failed)
• 철회 상태(Aborted)

 

---

(4) 테이블(Table)

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

 

테이블 구성 조건

• 테이블에 포함된 행들은 유일해야하고 중복된 행이 존재하지 않아야 한다.
• 테이블에 포함된 행들 간에는 순서가 존재하지 않는다.
• 테이블을 구성하는 열들 간에는 순서가 존재하지 않는다.

 

테이블 관련 용어

용어	설명
튜플(Tuple) / 행(Row)	테이블 내의 행을 의미하며 레코드(Record)라고도 함 튜플은 릴레이션(Relation)에서 같은 값을 가질 수 없음
애트리뷰트(Attribute) / 열(Column)	테이블 내의 열을 의미 열의 개수를 디그리(Degree)라고함
식별자(Identifier)	여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분 할 수 있는 논리적인 개념
카디널리티(Cardinality)	튜플(Tuple)의 개수 2개의 릴레이션에서 카디널리티는 *
차수(Degree)	애트리뷰트(Attribute)의 개수 2개의 릴레이션에서 차수는 +
도메인(Domain)	하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합

 

---

고급 SQL 작성

(1) 뷰(View)

• 뷰는 논리 테이블이다.
• 뷰는 저장장치 내에 물리적으로 존재하지 않는다
• 뷰 테이블은 ALTER문으로 변경이 불가능하다. 변경이 필요한 경우 DROP하고 CREATE한다.
• 뷰가 정의된 기본 테이블이 제거되면 뷰도 자동적으로 제거된다.

 

뷰의 특징

논리적 데이터 독립성 제공 / 데이터 조작 연산 간소화 / 보안 기능(접근제어) 제공 / 뷰 변경 불가

 

뷰의 목적

뷰를 사용하는 주된 이유는 단순 질의어를 사용할 수 있기 때문이다.

SELECT * FROM 뷰이름;

 

뷰의 장점과 단점

장점

• 논리적 독립성 제공
• 사용자 데이터 관리 용이
• 데이터 보안의 용이

단점

• 뷰 자체 인덱스 불가
• 뷰 정의 변경 불가
• 데이터 변경 제약 존재

 

뷰 명령어

뷰 생성

CREATE VIEW 뷰 이름 컬럼 목록 AS 데이터 조회 쿼리;

뷰 삭제

DROP VIEW 뷰 이름;

 

---

(2) 인덱스(Index)

• 인덱스는 데이터를 빠르게 찾을 수 있는 수단이다.
• 인덱스는 자동으로 생성되지 않는다.
• 기본키(PK; Primary Key) 컬럼은 자동으로 생성된다.

 

인덱스 종류

유형	설명
순서 인덱스 (Ordered Index)	데이터가 정렬된 순서로 생성되는 인덱스 B-Tree 알고리즘 활용(오름차순/내림차순 지정가능)
해시 인덱스 (Hash Index)	해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
비트맵 인덱스 (Bitmap Index)	각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
함수기반 인덱스 (Functional Index)	수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Singled Index)	하나의 컬럼으로만 구성한 인덱스, 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	두 개 이상의 컬럼으로 구성한 인덱스, WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clutered Index)	기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스

 

인덱스 조작

인덱스 생성

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

인덱스 삭제

DROP INDEX 인덱스명;

인덱스 변경

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

 

인덱스 스캔 방식 (범전단생)

범위 스캔 / 전체 스캔 / 단일 스캔 / 생략 스캔

 

---

(3) 집합 연산자(Sep Operator)

집합 연산자는 두 개 이상의 질의 결과를 하나의 결과로 결합하는 연산자이다.

 

집합 연산자 유형

집합 연산자	설명
UNION	중복 행이 제거된 쿼리 집합
UNION ALL	중복 행이 제거되지 않은 쿼리 결과 집합
INTERSECT	두 쿼리 결과에 공통적으로 존재하는 집합
MINUS	첫 쿼리에 있고 두 번째 쿼리에는 없는 집합

 

(4) 조인(Join)

조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

 

조인	유형	설명
논리적 조인	내부 조인 (Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출
	외부 조인 (Outer Join)	Left Outer Join: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
		Right Outer Join: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
		Full Outer Join: 양쪽의 모든 데이터를 추출
	교차 조인 (Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출
	셀프 조인 (Self Join)	자기 자신에게 별칭을 지정한 후 다시 조인
물리적 조인	중첩 반복 조인 (Nested-Loop Join)	2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 조인 방식
	정렬 합병 조인 (Sort-Merge Join)	조인의 대상 범위가 넓을 경우 발생하는 임의 접근(Random Access)을 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인 방식
	해시 조인 (Hash Join)	해싱 함수를 활용하여 테이블 간 조인을 수행하는 방식

 

---

(5) 서브쿼리(Sub-Query)

• 서브쿼리는 SQL 문 안에 포함된 또 다른 SQL 문을 의미한다.
• 서브쿼리의 용도는 알려지지 않은 기준을 위한 검색을 위해 사용된다.

 

서브쿼리 유형(동작 방식 기준)

서브쿼리 종류	설명
비연관 서브쿼리 (Un-Correlated Sub-Query)	서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태 메인쿼리에 서브쿼리에서 실행된 결과 제공하는 용도로 사용
연관 서브쿼리 (Correlated Sub-Query)	서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태 메인쿼리가 먼저 수행되어 얻은 데이터를 서브쿼리의 조건에 맞는지 확인하는 용도로 사용

 

서브쿼리 유형(데이터 형태 기준)

서브쿼리 종류	설명
단일 행(Single Row) 서브쿼리	결과가 항상 1건 이하인 서브쿼리 단일 행 비교 연산자(=, <, <=, >, >=, <>) 사용
다중 행(Multiple Row) 서브쿼리	실행 결과과 여러 건인 서브쿼리 다중 행 비교 연산자 사용 (IN, ALL, ANY, EXIST)
다중 컬럼(Multiple Column) 서브쿼리	결과가 여러 컬럼으로 반환되는 서브쿼리

 

서브쿼리 유형(위치 기준)

SELECT 절 서브쿼리 / FROM 절 서브쿼리 / WHERE 절 서브쿼리

 

---

(6) 스키마(Schema)

종류	설명
개념 스키마	사용자와 데이터베이스 관리자 관점의 스키마 데이터베이스에 실제로 어떤 데이터가 저장되었으며, 데이터 간의 관계는 어떻게 되는지를 정의하는 스키마 전체 관점으로 한 개만 존재하며 접근권한, 보안 및 무결성 등에 대한 정의를 포함
내부 스키마	저장장치와 데이터베이스 설계자 및 개발자 관점의 스키마 개념 스키마를 물리적 저장장치에 구현하는 방법을 정의하는 데 사용하고 물리적 구조 및 내부 레코드의 물리적 순서 등을 표현
외부 스키마	사용자 관점의 스키마 사용자 또는 프로그램 입장에서의 논리적 구조로 여러 개가 존재