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

전현준·2024년 4월 17일

정처기

정보처리기사 실기

목록 보기

8/12

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

1. 트랜잭션

(1) 트랜잭션

트랜잭션의 개념 : 인가받지 않은 사용자로부터 데이터를 보장하기 위해, DBMS의 특성
트랜잭션의 특성
- 원자성 : 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야함
- 일관성 : 고정요소는 수행 전과 수행 후의 상태가 동일해야함
- 격리성 : 트랜잭션들은 서로 영향을 미치면 안됨
- 영속성 : 트랜잭션의 결과는 영속성으로 DB에 저장되어야함
트랜잭션의 상태변화
- 활성 → 부분완료 → (commit) → 완료
- 활성 → 부분 완료 → 실패 →(Rollback) → 철회
트랜잭션 제어 : 제어 언어는 TCL이라고 하며, 결과를 허용하거나 취소하는 목적
- 커밋 / 롤백 / 체크포인트
병행 제어 : 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 일관성 유지를 위해 상호 작용
- 목적
- 데이터베이스의 공유를 최대화
- 시스템의 활용도를 최대화
- 데이터베이스의 일관성 유지
- 응답시간 최소화
- 종류
- 로킹 : 특정 데이터에 두개 이상의 트랙잭션이 접근 못하도록 상호배제 기능 제공
- 낙관적 검증 : 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증 수행
- 타임 스탬프 순서 : 갱신된 데이터에 대해 타임 스탬프를 부여
- 다중버전 동시성 제어 : 타임스탬프를 비교하여 적절한 버전 선택하여 접근
- 2PC : 여러 분산 DB 시스템에서 트랜잭션의 일관성 유지 기법
- (1단계) 준비 단계 / (2단계) 커밋 단계
데이터베이스 고립화 수준 : 현재에 데이터에 대해 무결성을 해치지 않기 위해 잠금 설정
- Read Uncommited : 아직 커밋 되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용
- Read Commited : 연산이 완료 될 때까지 읽기를 제한, 커밋된건 허용
- Repeatable Read : 트랜잭션 종료시까지 갱신, 삭제 제한
- Serialize Read : 해당 데이터 영역 전체에 대한 접근 제한
회복 기법 : 장애로 인해 손상된 DB를 손상되기 이전으로 되돌리는 기법
- REDO : 최근의 DB으로 복원하고, 저장된 로그를 분석하고, 트랜잭션의 작업을 재작업
- UNDO : Commit 기록이 없는 트랜잭션의 작업 내용을 모두 취소
- 회복 기법 종류
  - 로그 기반 회복 기법
    - 지연 갱신 회복 기법 : 트랜잭션이 완료되기 전까지 DB에 기록하지 않음
    - 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영
  - 체크 포인트 회복 기법 : 장애 발생 시 장애 발생 이전의 상태로 복원
  - 그림자 페이지 회복 기법 : 트랜잭션 수행 시 복제본을 생성, 데이터베이스 장애 시 복구

(2) DDL

데이터 정의어 : 생성, 변경, 삭제, 이름 변경, 데이터 구조 변경
스키마 : 데이터베이스의 구조, 제약 조건을 담고 있는 구조
- 외부 : 사용자 관점 / 개념 : 전체적인 뷰 / 내부 : 물리적 저장 장치
테이블 : 데이터를 저장하는 필드들로 구성된 데이터의 구조
- 튜플, 행 / 속성, 열 / 카디널리티 / 차수 / 도메인
뷰 : 논리 테이블로서 테이블에서 원하는 속성값을 조인하여 구성해 놓은 테이블
- 장점
  - 논리적 독립성 제공 : DB에 영향을 주지 않고, DB에 접근 가능
  - 데이터 조작 연산 간소화 : 원하는 형태의 논리적 구조 형성
  - 보안 기능 제공 : 선택되지 않은 필드의 조회 및 접근 불가
- 단점
  - 뷰 자체 인덱스 불가
  - 뷰 변경 불가 : 삭제하고 재생성 해야함
  - 데이터 변경 제약 존재
인덱스
- 검색 연산 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
- 데이터를 빠르게 찾을 수 있는 수단!
- 특징
  - 기본키는 자동으로 인덱스 생성
  - 인덱스가 없으면 전체 내용을 검색
  - ⭐ 조건절에 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음

테이블 DDL 명령어
- CREATE TABLE : 테이블 생성
  - 기본 키, 외래 키, UNIQUE, NOT NULL, CHECK 요소 IN (”값”), DEFAULT
- ALTER : 테이블 수정
  - ALTER TABLE 테이블 명 ADD 속성 값 데이터 타입 [NOT NULL]
  - ADD, ALTER, DROP
- DROP : DROP TABLE 테이블 명 [CASCADE | RESTRICT]
  - TRUNCATE : 모든 데이터 삭제 TRUNCATE TABLE 테이블명
뷰 DDL 명령어
- CREATE VIEW 뷰 이름 AS SELECT 회원 번호 FROM 회원
- CREATE OR REPLACE VIEW 뷰 이름 AS 조회 쿼리 : 뷰 교체

인덱스 DDL 명령어
- CREATE INDEX 인덱스명 ON 테이블 명 (속성 1, 속성 2 ,,,,)
- ALTER INDEX 인덱스명 ON 테이블 명 (속성 1, 속성 2 ,,,,)
- DROP INDEX 인덱스명 ON 테이블 명 (속성 1, 속성 2 ,,,,)

(3) DML

데이터 조작어 : DB의 자료를 입력, 수정, 삭제, 조회하는 언어 ****
SELECT : SELECT * FROM 회원
- DISTINCT
- WHERE : 조건
- GROUP BY 속성 : NULL값은 제외한 후 산출!
- HAVING 조건 : GROUP에 대한 조건 지정
- ORDER BY 속성 [ASC | DESC]

조인 : 두개의 테이블은 연결하여 데이터 검색
- 내부 조인 : 공통 속성에 대해서 조인
  - ex ) SELECT * FROM 테이블1 A JOIN 테이블2 B ON 조인 조건
- 외부 조인
  - 왼쪽 외부 조인 : 왼쪽 테이블의 모든 데이터에 대해 조인
    - ex ) SELECT * FROM 테이블1 A LEFT JOIN 테이블2 B ON 조인 조건
  - 오른쪽 외부 조인 : 오른쪽 테이블의 모든 데이터에 대해 조인
    - ex ) SELECT * FROM 테이블1 A RIGHT JOIN 테이블2 B ON 조인 조건
  - 완전 외부 조인 : 양쪽의 모든 데이터를 추출하는 기법
    - ex ) SELECT * FROM 테이블1 A FULL JOIN 테이블2 B ON 조인 조건
- 교차 조인 : 조인 조건이 없는 모든 데이터 조합을 추출
  - ex ) SELECT * FROM 테이블1 A CROSS JOIN 테이블2 B
- 셀프 조인 : 자기 자신에게 조인하는 기법
  - ex ) SELECT * FROM 테이블1 A JOIN 테이블1 B ON 조인 조건

서브쿼리 : SQL문 안에 포함된 또 다른 SQL문
- FROM 절 서브쿼리
  - FROM A, (SELECT * FROM 테이블,,) B
- WHERE 절 서브쿼리
  - WHERE 속성 IN (SELECT 속성 FROM 테이블,,)

집합 연산자
- UNION : 합집합
  - SELECT 어쩌고,, UNION SELECT 어쩌고..
- UNION ALL : 합집합인데 중복도 허용
  - SELECT 어쩌고,, UNION ALL SELECT 어쩌고..
- INTERSECT : 교집합
  - SELECT 어쩌고,, INTERSECT SELECT 어쩌고..
- MINUS : 차집합
  - SELECT 어쩌고1,, MINUS SELECT 어쩌고2..
  - 앞의 테이블에서 뒤의 테이블을 뺌
INSERT : INSERT INTO 회원(회원 이름, 회원 번호,,,) VALUES (전, 1)
UPDATE : UPDATE 테이블명 SET 속성명 = “데이터” WHERE 조건;
DELETE : DELETE FROM 테이블 명 WHERE 조건;

(4) DCL

DB 관리자가, 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 언어
GRANT : GRANT 권한 ON 테이블 명 TO 사용자;
REVOKE : REVOKE 권한 ON 테이블 명 TO 사용자;

2. 응용 SQL

1. 집계성 SQL

(1) 다중 행 연산자

다중 행 연산자 : 서브 쿼리의 결과가 여러 개의 튜플을 반환
다중 행 연산자 종류
- IN : 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
  - SELECT * FROM WHERE 속성 IN (서브쿼리)
- ANY : 리턴되는 값에서 조건을 비교해서 하나 이상 만족하면 참
  - SELECT * FROM WHERE 속성 > ANY (서브쿼리)
- ALL : 리턴되는 값에서 모든 값과 조건 값을 비교해서 모든 값을 만족하면 참
  - SELECT * FROM WHERE 속성 > ALL (서브쿼리)
- EXISTS : 만족하는 값이 하나라도 있으면 참
  - SELECT * FROM WHERE EXISTS (서브쿼리)

(2) 집계 함수

COUNT() / SUM() / AVG()
MAX() / MIN()
STDDEV() : 표준편차 계산
VARIANCE() : 분산 계산

3. SQL 활용 및 최적화

1. 절차형 SQL

SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어
절차형 SQL 종류
- 프로시저 : 일련의 쿼리를 하나의 함수 처럼 실행
- 사용자 정의 함수 : 일련의 SQL 처리, 수행 결과를 단일 값으로 반환
- 트리거 : 삽입, 갱신, 삭제의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행됨

2. SQL 최적화

튜닝의 개념
- 튜닝은 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정
옵티마이저
- 옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성
- 유형
  - 규칙기반 옵티마이저 (RBO) : 사전 등록된 규칙에 따라 질의 실행 계획 선택
    - 인덱스 구조, 연산자, 조건절 형태
  - 비용기반 옵티마이저 (CBO) : 모든 접근 경로를 고려한 질의 실행 계획 선택
    - 레코드 개수, 블록 개수, 평균 행 길이 등