정처기 실기 7장 SQL 응용

Chapter01 데이터베이스 기본 (중요도: ★★★)

◆ 트랜잭션(Transaction): 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성, 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

◆ 트랜잭션 특성(ACID)

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

◆ 트랜잭션 제어어(TCL, Transaction Control Language): 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어

TCL 명령어(커롤체)
- COMMIT: 트랜잭션을 메모리에 영구적으로 저장하는명령어
- ROLLBACK: 트랜잭션 내역의 저장을 무효화시키는 명령어
- CHECKPOINT(SAVEPOINT): ROLLBACK을 위한 시점을 지정하는 명령어

◆ 데이터 정의어(DDL: Data Definition Language): DB를 구축하거나 수정할 목적으로 사용하는 언어

◆ DDL 대상(도스테뷰인)

도메인(Domain): 하나의 속성이 가질 수 있는 원자값들의 집합
스키마(Schema): 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
- 외부 스키마, 개념 스키마, 내부 스키마
테이블(Table): 데이터 저장 공간
뷰(View): 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index): 검색을 빠르게 하기 위한 데이터 구조
- 인덱스 종류(순해비함단결클)
  - 순서 인덱스(Ordered Index): 데이터가 정렬된 순서로 생성되는 인덱스
  - 해시 인덱스(Hash Index): 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
  - 비트맵 인덱스(Bitmap Index): bit 값인 0 또는 1로 변호나하여 인덱스 키로 사용하는 인덱스
  - 함수기반 인덱스(Functional Index): 수식이나 함수를 적용하여 만든 인덱스
  - 단일 인덱스(Single Index): 하나의 컬럼으로만 구성한 인덱스
  - 결합 인덱스(Concatenated Index): 두 개 이상의 컬럼으로 구성한 인덱스
  - 클러스터드 인덱스(Clustered Index): 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식 (검색 빠름)
  - 넌클러스터드 인덱스(Non-Clustered Index): 인덱스의 키 값만 정렬되어 있고 실제 데이터는 정렬되지 않는 방식 (데이터 삽입, 삭제 시 데이터 재정렬해야함)

◆ DDL 명령어(크알드트): CREATE(생성), ALTER(수정), DROP(삭제),TRUNCATE

CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
RESTRICT: 다른 개체가 제거할 요소를 참조중일 떄는 제거를 취소

◆ 데이터 조작어(DML: Data Manipulation Language): 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

DML 유형(세인업데): SELECT(조회), INSERT(삽입), UPDATE(수정), DELETE(삭제)

(셀프 웨 구해오) SELECT 명령문 -SELECT / FROM / WHERE / GROUP BY / HAVING / ORDER BY

◆ 데이터 제어어(DCL: Data Control Language): 데이터의 보안, 무결성, 회복, 병행 제어등을 정의하는데 사용하는 언어

DCL 유형
- GRANT: 사용 권한 부여
- REVOKE: 사용 권한 취소

그온투 GRANT 명령어 GRANT 권한 ON 테이블 TO 사용자

리온프 REVOKE 명령어 REVOKE 권한 ON 테이블 FROM 사용자

1) 절차형 SQL

특정 사용자가 실행 순서를 정해놓고 사용하는 SQL문

* 프로시저(서술형)

- 절차형 SQL문을 활용하여 사전에 정의해놓은 SQL문을 호출할 경우 특정 기능을 수행하는 트랜잭션 언어

DECLARE // 프로시저 선언부
BEGIN/END // 프로시저 시작
CONTROL // 제어부(조건/반복), 순차적 처리
SQL // DQL, DML
EXCEPTION // 예외사항 처리
TRANSCATION // DML 수행 취소 여부(ROLLBACK, COMMIT)

- CREATE OR REPLACE : 프로시저 생성 SQL문

* 사용자 정의 함수(서술형)

- 사용자가 정의하여 SQL문 처리 수행 후 단일 값으로 반환하는 절차형 SQL

DECLARE // 프로시저 선언부
BEGIN/END // 프로시저 시작
CONTROL // 제어부(조건/반복), 순차적 처리
SQL // DQL, DML
EXCEPTION // 예외사항 처리
RETURN // 단일 값 반환

* 트리거(서술형)

특정 테이블에 수정, 삭제, 삽입 등의 데이터 이벤트 발생 시 자동으로 DBMS에서 실행되도록 구현된 프로그램

- 종류

: 행 트리거 - 데이터 변환 시 실행

: 문장 트리거 - 트리거에 의해 한 번 실행

DECLARE // 프로시저 선언부
EVENT // 이벤트 부(FOR EACH ROW)
BEGIN/END // 프로시저 시작
CONTROL // 제어부(조건/반복), 순차적 처리
SQL // DQL, DML
EXCEPTION // 예외사항 처리

2) 응용 SQL 작성

* 집계 함수
- 여러 행과 테이블 전체로부터 하나의 결과값을 반환

- GROUP BY문 사용

- COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN 함수 사용

* 그룹 함수

- 소 그룹 간의 중간 합계, 소계를 계산하여 산출하는 함수

- ROLLUP : 소계, 중간 집계 값 산출

- CUBE : 다차원 집계 생성

- GROUPING SET : 컬럼에 대한 개별 집계

* 윈도 함수

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

SELECT 함수명(파라미터) OVER PARTITION BY ORDER BY

순위함수	행 순서 함수	그룹 내 비율 함수
RANK()	FIRST_VALUE	RATIO_TO_REPORT
DENSE_RANK()	LAST_VALUE	PERENT_RANK
ROW_NUMBER()

* JDBC

- 자바 언어를 이용하여 DBMS에 질의하고 데이터를 조작하는 API 제공

* MyBatis

- DBMS에 질의하기 위해 SQL 쿼리를 별도의 파일에 분리하고 Mapping을 통해 SQL 실행

- 복잡한 JDBC 코드 단순화

- SQL문 거의 그대로 사용 가능

- 우수한 성능

* 동적 SQL : 조건에 따라 SQL문 구문 자체를 변경하고 조작이 가능

* DCL

- GRANT : 권한 부여 - ON TO WITH

- REVOKE : 권한 해제 - ON FROM CASCADE

Chapter02 응용 SQL 작성하기 (중요도: ★)

◆ 데이터 분석 함수 종류

집계 함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
그룹 함수: 소그룸 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
윈도 함수: 데이터베이스를 사용한 오라인 부석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

Chapter03 절차형 SQL 활용하기 (중요도: ★)

◆ 절차형 SQL(Procedural SQL): SQL언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

절차형 SQL 종류
- 프로시저(Procedure): 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 커리의 집합
- 사용자 정의 함수(User-Defined FUnction): SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
- 트리거(Trriger): 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

Chapter04 데이터 조작 프로시저 최적화 (중요도: ★)

◆ 쿼리 성능 개선: 최소의 시간으로 우너하는 결과를 얻도록 프로시저를 수정하는 작업

SQL 성능 개선 절차: 문제있는 SQL식별 → 옵티마이저 통계확인 → SQL문 재구성 → 인덱스 재구성 → 실행계획 유지관리

◆ 옵티마이저(Optimizer): SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈

옵티마이저 유형
- 규칙기반 옵티마이저(RBO): 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
- 비용기반 옵티마이저(CBO): 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용기반 옵티마이저

◆ 힌트(Hint): 실행하려는 SQL문에 사전에 정보를 주어서 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 문법

옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.
옵티마이저는 명시적인 힌트를 통해 실행 계획을 변경한다.

정처기 실기 7장 SQL 응용

관련된 글

정처기 실기 9장 소프트웨어 개발 보안 구축

정처기 실기 10장 애플리케이션 테스트 관리

정처기 실기 11장 응용 SW 기초 기술 활용