[SW]/정보처리기사 (2024) (완)

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

시원00 2024. 10. 1. 11:24
728x90

VII. SQL 응용

 

01. 데이터베이스 기본

02. 응용 SQL

03. SQL 활용 및 최적화

01. 데이터베이스 기본

- 트랜잭션: 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

- 트랜잭션 특성: ACID

      1) 원자성(Atomicity)

      2) 일관성(Consistency) : 병행제어*

*병행 제어(Concurrency Control): 일관성 주요 기법
- 개념: 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법
- 목적: 데이터베이스 공유 최대화, 시스템 활용도 최대화, 데이터베이스 일관성 유지, 응답시간 최소화
- 종류: 로 낙타다2 (로킹, 낙관적 검증, 타임 스탬프 순서, 다중버전 동시성 제어, 2PC)

      3) 격리성, 고립성(Isolation) : 데이터베이스고립화수준*

*데이터베이스 고립화 수준(Isolation Level): 격리성 주요 기법
- 개념: 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
- 종류: Read Uncommitted, Read Committed, Repeatable Read,Serializable Read

      4) 영속성(Durability) : 회복기법*

*회복 기법(Recovery): 영속성 주요 기법
- 개념: 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업
- 주요 요소: REDO, UNDO
- 종류: 회로체그 (로그 기반 회복 기법, 체크 포인트 회복 기법, 그림자 페이징 회복 기법)

- 트랜잭션의 상태 변화: 활부완실철

      1) 활동 상태, 활성(Active)

      2) 부분 완료 상태(Partially Committed)

      3) 완료 상태(Committed)

      4) 실패 상태(Failed)

      5) 철회 상태(Aborted)

- TCL(Transaction Control Language): 트랜잭션 제어언어

      ▷ 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어

      ▷ TCL 명령어: 커롤체 (커밋, 롤백, 체크포인트)

 

- DDL(Data Definition Language): 데이터 정의어

      ▷ DDL 대상: 스키마, 테이블, 뷰, 인덱스

*스키마(Schema): 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
- 구성: 외부 스키마, 개념 스키마, 내부 스키마
*테이블(Table): 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체. (=릴레이션(Relation)=엔터티(Entity))
- 용어: 튜플(행, 레코드), 속성(Attribute, 열), 카디널리티(튜플의 개수), 차수(Degree, 속성의 개수), 도메인
*뷰(View): 논리 테이블. 사용 관점에서 테이블과 동일
- 장점: 논리적 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근제어) 제공
- 단점: 뷰 자체 인덱스 불가, 뷰 변경 불가, 데이터 변경 제약 존재
*인덱스(Index): 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조. 특정 레코드 위치를 알려주는 용도.
- 테이블 전체 스캔(Table Full Scan), 테이블 범위 스캔(Index Range Scan)

      ▷ DDL 명령어: CREATE, ALTER, DROP, TRUNCATE

*테이블 관련 DDL
   1) CREATE TABLE
   2) ALTER TABLE
   3) DROP TABLE
   4) TRUNCATE TABLE
*뷰 관련 DDL
   1) CREATE VIEW
   2) CREATE OR REPLACE VIEW
   3) DROP VIEW
*인덱스 관련 DDL
   1) CREATE INDEX
   2) ALTER INDEX
   3) DROP INDEX

 

- DML(Data Manipulation Language): 데이터 조작어

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

   ▷ DML 유형: SELECT, INSERT, UPDATE, DELETE

      1) SELECT(데이터 조회) 명령어

SELECT [ALL | DISTINCT] 속성명1, 속성명2 ...
    FROM 테이블명1, ...
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC] ];
*SELECT 명령어: 셀프 웨 구해오
   1) SELECT 절: ALL(기본값) | DISTINCT
   2) FROM 절: 테이블명
   3) WHERE 절: 조건(비교(=, <>, !=, <, <=, >, >=), 범위(BETWEEN), 집합(IN, NOT IN), 패턴(LIKE), NULL(IS NULL, IS NOT NULL), 복합조건(AND, OR, NOT))
   4) GROUP BY 절: 그룹으로 분류
   5) HAVING 절: GROUP BY에 의해 분류한 후 그룹에 대한 조건 지정
   6) ORDER BY 절: ASC(오름차순, 기본값), DESC(내림차순)
*JOIN: 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법
- 유형: 내부 조인, 외부 조인(왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인), 교차 조인, 셀프 조인
*서브쿼리(Sub-Query): SQL 문 안에 포함된 또 다른 SQL 문
- 유형: FROM 절 서브쿼리(=인라인 뷰), WHERE 절 서브쿼리(=중첩 서브쿼리)
*집합 연산자(Set Operator)
- 유형: UNION, UNION ALL, INTERSECT, MINUS

      2) INSERT(데이터 삽입) 명령어: 인인벨(INSERT INTO / VALUES)

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

      3) UPDATE(데이터 변경) 명령어: 업셋웨(UPDATE / SET / WHERE)

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

      4) DELETE(데이터 삭제) 명령어: 델프웨(DELETE FROM / WHERE)

DELETE FROM 테이블명
    WHERE 조건;

 

- DCL(Data Control Language): 데이터 제어어

   ▷ 유형: GRANT, REVOKE

      1) GRANT(권한 부여) 명령어: 그온투(GRANT 권한 ON 테이블 TO 사용자)

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

      2) REVOKE(권한 회수) 명령어: 리온프(REVOKE 권한 ON 테이블 FROM 사용자)

REVOKE 권한 ON 테이블 FROM 사용자

02. 응용 SQL: 집계성 SQL

- 다중 행 연산자: 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자

   ▷ 종류: IN, ANY(=SOME), ALL, EXISTS

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

   ▷ 종류: COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIANCE

SELECT 컬럼1, 컬럼2, ..., 집계함수
    FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]
*GROUP BY 구문: 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별하기 위해 사용
- NULL 값을 가지는 행은 제외 후 산출
*HAVING 구문: GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용
- GROUP BY 및 집계 함수에 대한 WHERE 구문

03. SQL 활용 및 최적화

- 절차형 SQL: 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

   ▷ 종류: 프로시저(Procedure), 사용자 정의 함수(User-Defined Function), 트리거(Trigger)

- SQL 최적화: 튜닝, 옵티마이저

*튜닝(쿼리 성능 개선)
- 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
- SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선 가능
*옵티마이저(Optimizer)
- SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진
- 실행계획(Execution Plan): 옵티마이저가 생성한 SQL 처리경로
- 유형: RBO(Rule Based Optimizer, 규칙(우선순위)기반 옵티마이저), CBO(Cost Based Optimizer, 비용(수행 시간)기반 옵티마이저)

 

 

FIN.

728x90