[정보처리기사 실기] 7장 SQL 응용

 

정보처리기사 수제비 2022 실기 문제집을 요약하며 공부했습니다! 😁 

 

---

 

7-1. 데이터베이스 기본

7-1-1. 트랜잭션

 

📌 트랜잭션 특성 🌟

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

• 원자성(Atomicity): 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질
• 일관성(Consistency): 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
• 격리성=고립성(Isolation): 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않어야 한다는 성질
• 영속성(Durability): 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

 

📌 트랜잭션 상태 변화

• 활동 상태(Active): 초기 상태. 트랜잭션이 실행 중일 때 가지는 상태
• 부분 완료 상태(Partially Commited): 마지막 명령문이 실행된 후에 가지는 상태
• 완료 상태(Commited):  트랜잭션이 성공적으로 완료된 후 가지는 상태
• 실패 상태(Failed): 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
• 철회 상태(Aborted): 트랜잭션이 취소되고, 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

*TCL 명령어: 커밋(확정), 롤백(취소), 체크포인트(저장 시기 설정)

 

📌 병행 제어(Concurrency Control)

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

- ✏️ 미 보장시 문제점

• 갱신 손실(Lost Update): 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
• 현황 파악 오류(Dirty Read): 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류 
• 모순성(Inconsistency): 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
• 연쇄복귀(Cascading Rollback): 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

 

- ✏️ 병행 제어 기법의 종류

• 로킹(Locking): 접근하려는 데이터를 다른 트랜잭션이 접근하지 못하도록 잠그는(lock) 직렬화 기법(순차적 진행 보장)
• 낙관적 검증(Optimistic Validation):  트랜잭션 수행 동안은 어떠한 검사를 하지 않고, 트랜잭션이 종료된 이후에 일괄적으로 검사하는 방식
• 타임 스탬프 순서(Time Stamp Ordering): 데이터에 접근하는 시간(Timestamp)을 미리 정해두어 부여된 시간 순서대로 데이터에 접근
• 다중 버전 동시성 제어(MVCC; Multi Version Concurrency Control): 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하는 기법

 

📌 데이터 베이스 고립화 수준

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

 

- ✏️고립화 수준 종류

• Read Uncommited(연산중인 데이터 읽기 허용)
• Read Commited(완료 될 때까지 읽기 제한)
• Repeatable Read(종료시까지 갱신,삭제 제한)
• Serializable Read(영역 전체에 대한 접근 제한)

 

📌 회복 기법

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

 

- ✏️회복기법 종류

• 로그 기반 회복 기법
  • 지연 갱신 회복 기법(Deferred Update): 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
  • 즉각 갱신 회복 기법(immediate Update): 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영

 

• 체크 포인트 회복 기법: 장애 발생시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원 
• 그림자 페이징 회복 기법(Shadow Paging Recovery): 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

 

📌 DDL(Data Definition Language)

- 데이터 정의어: 데이터를 정의하는 언어, 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어

1. 테이블: 카디널리티(튜플의 개수), 차수(애트리뷰트의 개수)

2. 뷰: 뷰는 ALTER문으로 변경할 수 없음

3. 인덱스: 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조

 ✏️인덱스 종류: 순서 인덱스, 해시 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 단일 인덱스, 결합 인덱스, 클러스터드 인덱스

 

4. TABLE 관련 DDL

// 테이블 생성
CREATE TABLE 테이블명
(
	컬럼명 데이터 타입 [제약조건]

)

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

// 컬럼 수정
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건]

// 컬럼 삭제 
ALTER TABLE 테이블명 DROP 컬럼명;

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

//테이블 내 데이터 삭제
TRUNCATE TABLE 테이블명;

- CASCADE: 참조하는 테이블까지 연쇄 제거

- RESTRICT: 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

 

5. VIEW 관련 DDL

// 뷰 생성
CREATE VIEW 뷰이름 AS 조회쿼리

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

//뷰 삭제
DROP VIEW 뷰이름;

 

6. INDEX 관련 DDL

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

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

//인덱스 삭제
DROP INDEX 인덱스명;

 

 

📌 DML(Data Manipulation Language)

- 데이터 조작어: 데이터베이스에 저장된 자료들을 입력,수정,삭제, 조회하는 언어

//SELECT

SELEC [ALL | DISTICT] 속성명1, 속성명2, ...
FROM 테이블명,
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC]];

 

- WHERE 절 문법

• LIKE
  • % : 0개 이상의 문자열과 일치
  • [] : 1개의 문자와 일지
  • [^]: 1개의 문자와 불일치
  • _ :  특정 위치의 1개의 문자와 일치

 

✏️ 조인

• 내부 조인(Inner Join): 공통 존재 컬럼의 값이 같은 경우 추출

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
FROM 테이블1 A [INNER] JOIN 테이블2 B
[WHERE 검색조건];

 

• 외부 조인(Outer Join) 
  • 왼쪽 외부 조인(Left Outer Join): 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출 
  • 오른쪽 외부 조인(Right Outer Join): 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
  • 완전 외부 조인(Full Outer Join): 양쪽의 모든 데이터를 추출하는 기법

//왼쪽 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];

//오른쪽 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];

//완전 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];

 

• 교차 조인(Cross Join): 조인 조건이 없는 모든 데이터 조합을 추출하는 기법
• 셀프 조인(Self Join):  자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

//교차 조인
SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 CROSS JOIN 테이블2

//셀프 조인
SELECT A.컬럼1, A.컬럼2, ... , B.컬럼1, B.컬럼2, ...
FROM 테이블1 A [INNTER] JOIN 테이블1 B ON 조인조건 
[WHERE 검색조건]

 

✏️ 집합 연산자

- UNION(중복 제거된 합집합), UNION ALL(A+B), INTERSECT(교집합), MINUS(A-B)

 

✏️ INSERT,UPDATE, DELETE

//데이터 삽입
INSERT INTO 테이블명(속성명1, ..)
VALUES(데이터1, ..);

//데이터 변경
UPDATE 테이블명 SET 속성명 = 데이터, ...
WHERE 조건;

//데이터  삭제 
DELETE FROM 테이블명 WHERE 조건;

 

 

📌 DCL(Data Control Language)

- 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어

// 사용 권한 부여
GRANT 권한 ON 테이블 TO 사용자;

// 사용 권한 회수
REVOKE 권한 ON 테이블 FROM 사용자;

 

---

 

7-2. 응용 SQL 작성하기

7-2-1. 집계성 SQL 작성

 

📌 집계함수

- 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]

 

✏️집계 함수 종류

• COUNT, SUM, AVG, MAX, MIN, STDDEV(표준편차), VARIAN(분산)

 

📌 그룹 함수

-  테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

 

1. ROLLUP 함수

- 소그룹의 중간 집계 값을 산출. 지정 컬럼 수 보다 1단계 더 큰 레벨만큼의 중간 집계 값이 생성됨

SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] ROLLUP 컬럼
[HAVING...]
[ORDER BY...]

 

2. CUBE 함수

 - 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수. 연산이 많아 시스템에 부하를 줌

SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] CUBE(컬럼명a, ...)
[HAVING...]
[ORDER BY...]

 

3. GROUPING SETS 함수

- 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, 순서와 무관한 결과를 얻을 수 있음

SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] GROUPING SETS(컬럼명1, ...)
[HAVING...]
[ORDER BY...]

 

📌 윈도 함수

- 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수 = OLAP 함수

SELECT 함수명(파라미터)
OVER
([PARTITION BY 컬럼1, ...])
[ORDER BY 컬럼A, ...])
FROM 테이블명

 

- 순위 함수

• RANK: 동일 순위 레코드 존재 시 후순위는 넘어감(2위, 2위, 2위, 5위, 6위)
• DENSE_RANK: 동일 순위 레코드 존재 시 후순위를 넘어가지 않음(2위, 2위, 2위, 3위, 4위)
• ROW_NUMBER: 동일 순위 레코드 존재 시 이와 무관하게 연속 번호 부여(2위, 3위, 4위, 5위)

SELECT NAME, SALARY
RANK() OVER (ORDER BY SALARY DESC) AS A
FROM EMPLOYEE;

 

---

 

7-3. 절차형 SQL 작성하기

7-3-1. 절차형 SQL

 

📌 절차형 SQL 종류

- 프로시저, 사용자 정의함수, 트리거

 

📌 프로시저

- 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

- DECLARE(선언부), BEGIN/END(선언부/종료부), CONTROL(제어부), SQL, EXCEPTION(예외), TRANSACTION(실행부)

- 모드(IN - 운영체제에서 프로시저로 값 전달 모드, OUT- 프로시저에서 처리한 결과를 운영체제로 전달하는 모드, INOUT - 두가지 기능 모두 수행하는 모드)

 

📌 사용자 정의 함수

- 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환(RETURN)할 수 있는 절차형 SQL

- 조회용도로 SELECT 문을 사용(INSERT, UPDATE, DELETE 사용 불가)

- 모드는 IN 만 가능 (운영체제에서 사용자 정의 함수로 값 전달 모드) 

- 호출문에 대한 함수값을 반환

 

📌 트리거

- 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 반환값이 없음. EVENT가 존재

CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE | AFTER] 유형 ON 테이블명
[FOR EACH ROW]
BEGIN
END;

 

- BEFORE: 테이블에 대한 INSERT/UPDATE/DELETE를 수행하기 전에 트리거 실행 (AFTER는 수행 후 트리거 실행)

- FOR EACH ROW: 매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어

- COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용 불가

 

 

---

 

7-4. 데이터 조작 프로시저 최적화 

7-4-1. 데이터 조작 프로시저 성능 개선

 

📌 쿼리 성능 개선

- 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

 

📌 옵티마이저

- SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진.

- 옵티마이저가 생성한 SQL 처리 경로를 실행계획이라고 부름

 

✏️ 유형

• RBO(Rule Based Optimizer): 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저로 규칙 우선 기반
• CBO(Cost Based Optimizer): 통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저로 비용 기반

 

📌 힌트

- SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는대로 변경할 수 있게 한다. 명시적인 힌트를 통해 실행계획을 변경한다.