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

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

• DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
• 종류
  • CREATE : Schema, Domain, Table, View, Index 정의
  • ALTER : Table에 대한 정의를 변경하는 데 사용
  • DROP : Schema, Domain, Table, View, Index 삭제
    • CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
    • RESTRICT : 다른 개체가 제거할 요소를 참조 중일 때는 제거 취소

 

스키마(Schema)

• 데이터베이스와 구조와 제약 조건에 관한 전반적 명세를 기술한 것
• 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의

 

도메인(Domain)

• 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
• 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 자용자 정의 데이터 타입

 

테이블(Table)

• 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션(Relation)이라 부르고, 조작이나 검색 시에는 테이블(Table)이라 부른다.

 

뷰(View)

• 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블
• 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않는다.

 

인덱스(Index)

• 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조

 

 

데이터 제어어(DCL, Data Control Language)

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
• 종류
  • COMMIT : 트랜잭션 처리가 정상적으로 완료된 후, 트랜잭션이 수행한 내용을 데이터베이스에 반영
  • ROLLBACK : 데이터베이스 조작 작업이 비정상적으로 종료되었을 때, 원래의 상태로 복구
    • SAVEPOINT : 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
  • GRANT : 데이터베이스 사용자에게 사용 권한을 부여
  • REVOKE : 데이터베이스 사용자의 사용 권한을 취소

 

트랜잭션 제어어(TCL; Transaction Control Language)

• COMMIT, ROLLBACK, SAVEPOINT는 트랜잭션을 제어하는 용도로 사용되므로 TCL로 분류하기도 한다.

 

 

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

• 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
• 종류 : SELECT(조회), INSERT(추가), DELETE(삭제), UPDATE(갱신)

 

 

그룹 함수

• GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용
• 종류
  • COUNT() : 튜플의 수
  • SUM() : 합계
  • AVG() : 평균
  • MAX() : 최대값
  • MIN() : 최소값
  • STDDEV() : 표준편차
  • VARIANCE() : 분산
  • ROLLUP(X, Y, ...) : 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수
  • CUBE(X, Y, ...) : ROLLUP과 유사한 형태이지만, CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함

 

WINDOW 함수

• GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성의 값을 집계
• 종류
  • ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환
  • RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영
  • DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여

 

 

JOIN

• 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환

 

INNER JOIN

• EQUI JOIN과 NON-EQUI JOIN으로 구분
• 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
• CROSS JOIN(교차 조인)
  • 조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환
  • 교차 조인의 결과로 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
• EQUI JOIN
  • JOIN 대상 테이블에서 공통 속성을 기준으로 =(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성
  • 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 한다.
  • 연결 고리가 되는 공통 속성을 JOIN 속성이라고 한다.
• NON-EQUI JOIN
  • JOIN 조건에 = 조건이 아닌 나머지 비교 연산자(>, <, <>, >=, <=) 연산자를 사용하는 JOIN 방법

 

OUTER JOIN

• 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
• LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

 

 

프로시저(Procedure)

• SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
• 데이터베이스에 저장되어 수행되기 때문에 저장 프로시저(Stored Procedure)라도고 불린다.
• 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다.
• 구성
  • DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
  • BEGIN / END : 프로시저의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
  • SQL : DML, DCL이 삽입되어 데이터 관리를 위한 CRUD 작업 수행
  • EXCEPTION : BEGIN - END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법 정의
  • TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

 

 

트리거(Trigger)

• 데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
• 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용
• DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류 발생
• 구성
  • DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
  • EVENT : 트리거가 실행되는 조건 명시
  • BEGIN / END : 터리거의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
  • SQL : DML문이 삽입되어 데이터 관리를 위한 CRUD 작업 수행
  • EXCEPTION : BEGIN - END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법 정의

 

 

사용자 정의 함수

• 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL
• 데이터베이스에 저장되어 조회, 삽입, 삭제, 갱신 등 DML 문의 호출에 의해 실행
• 예약어 RETURN을 통해 단일값을 반환하며, 출력 파라미터가 없다.
• 구성
  • DECLARE : 사용자 정의 함수의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
  • BEGIN / END : 사용자 정의 함수의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
  • SQL : SELECT 문이 삽입되어 데이터 조회 작업 수행
  • EXCEPTION : BEGIN - END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법 정의
  • RETURN : 호출 프로그램에 반환할 값이나 변수 정의

 

 

제어문

• 위에서 아래로 차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
• 종류 : IF, LOOP 등

 

 

커서(Cursor)

• 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)
• 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행

 

묵시적 커서(Implicit Cursor)

• DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
• 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능
• 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용
• 종류
  • SQL%FOUND : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 1개 이상이면 참
  • SQL%NOTFOUNT : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 0개이면 참
  • SQL%ROWCOUNT : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수 반환
  • SQL%ISOPEN 
    • 커서가 열린(Open) 상태이면 참
    • 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 거짓

 

명시적 커서(Explicit Cursor)

• 사용자가 직접 정의해서 사용하는 커서
• 쿼리문의 결과에 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지
• 명시적 커서를 사용하기 위해서는 열기(Open) 단계 전 선언(Declare)해야 한다.

 

 

동적 SQL(Dynamic SQL)

• 다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
• 사용자로부터 SQL 문의 일부 또는 전부를 입력받아 실행할 수 있다.
• 응용 프로그램 수행 시 SQL이 변형될 수 있으므로, 프리컴파일(Precompile) 할 때 구문 분석, 접근 권한 확인 등이 불가
• 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발 가능

 

 

SQL 테스트

• SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정
• 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인

 

단문 SQL 테스트

• SQL 코드를 직접 실행한 후 결과를 확인, 간단히 테스트 가능
• DDL, DML, DCL이 포함되어 있는 SQL과 TCL을 테스트하는 것
• DDL이 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션 등 확인 가능
• DML로 변경한 데이터는 SELECT 문으로 데이터의 정상적인 변경 여부 확인 가능
• DCL로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 조회하여 확인 가능

 

절차형 SQL 테스트

• 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 접합성 여부 검증 및 실행을 통해 결과 확인
• 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인하고 문제 수정

 

 

ORM(Object-Relational Mapping)

• 객체 지향 프로그래밍의 객체(Object)와 관계형 데이터베이스의 데이터를 연결하는 기술
• 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터 연결
• ORM으로 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적으로 재사용 및 유지보수 용이
• 한계
  • 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인 필요
  • 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우, 프로젝트가 크고 복잡해질수록 ORM 적용이 어렵다.

 

 

쿼리 성능 최적화

• 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
• 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화할 쿼리를 선정
• 최적화할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스 재구성

 

옵티마이저(Optimizer)

• 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
• 종류
  • RBO(Rule Based Optimizer) : 데이터베이스 관리자(DBA)가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • CBO(Cost Based Optimizer) : 입출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플 개수 등을 종합하여 각 DBMS 마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용 기반 옵티마이저