정보처리기사 필기 3주차 Day-1 정리

Chapter 01. SQL 응용

1. 절차형 SQL 작성

(1) 트리거

트리거의 개념

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

 

트리거의 구성 = DEBCSE

구성요소	설명
선언부 (DECLARE)	트리거의 명칭을 정의 부분
이벤트부 (EVENT)	트리거가 실행되는 타이밍, 이벤트를 명시하는 부분
시작/종료부 (BEGIN/END)	트리거의 시작과 종료를 표현하는 데 필수적이며, 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	기본적으로 순차적으로 처리 비교 조건에 따라 블록 또는 문장을 실행
SQL	DML을 주로 사용하고, 자주 사용되지 않지만 DDL을 사용
예외부 (EXCEPTION)	BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부

 

트리거 작성 시 주의사항

TCL 사용 불가

오류에 주의

 

(2) 이벤트

이벤트의 개념

이벤트는 특정 시간에 특정한 쿼리, 프로시저, 함수 등을 실행시키는 기능이다.

 

(3) 사용자 정의함수

사용자 정의함수(User-Defined Function) 개념

사용자 정의함수는 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일값으로 반환할 수 있는 함수이다.

 

사용자 정의함수 특징

일반적으로 사용자 정의함수는 호출을 통해 실행되며, 반환되는 단일 값을 조회, 삽입, 수정 작업에 이용한다.

 

사용자 정의함수 구성 = DBCSER

구성요소	설명
선언부 (DECLARE)	프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의 부분
시작/종료부 (BEGIN/END)	다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	기본적으로는 순차적으로 처리 비교 조건에 따라 블록 또는 문장을 실행
SQL	조회 용도로 SELECT문을 사용
예외부 (EXCEPTION)	BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리방법을 정의하는 처리부
반환부 (RETURN)	호출문에 대한 함수 값을 반환

 

(4) SQL 문법

SQL 문법의 분류 = 정조제

분류	설명
데이터 정의어(DDL)	데이터 정의어는 데이터를 정의하는 언어 CREATE, ALTER, DROP, TRUNCATE 문이 있음
데이터 조작어(DML)	데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어 SELECT, INSERT, UPDATE, DELETE 문이 있음
데이터 제어어(DCL)	데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 DBA가 사용하는 제어용 언어 GRANT, REVOKE 문이 있음

 

WHERE 조건

구분	연산자	사례
비교	=, <>, <, <=, >, >=	PRICE < 50000 가격이 50000 미만
범위	BETWEEN	PRICE BETWEEN 50000 AND 80000 가격이 50000보다 크거나 같고 80000보다 작거나 같음
집합	IN, NOT IN	PRICE IN (40000, 50000, 60000) 가격이 40000 또는 50000 또는 60000
패턴	LIKE	NAME LIKE '정보%' 이름이 '정보'로 시작되는 문자열
NULL	IS NULL, IS NOT NULL	PRICE IS NULL 가격이 NULL 값인 경우
복합조건	AND, OR, NOT	(PRICE < 50000) AND (NAME LIKE '정보%') 가격이 50000 미만이고 이름이 '정보'로 시작되는 문자열

 

LIKE와 같이 사용하는 와일드 문자

와일드 문자	설명	사례
+	문자열을 연결	'축구'+'감독' : '축구감독'
%	0개 이상의 문자열과 일치	LIKE '키워드%' % 용법 : 키워드로 시작하는 문자열 검색
[ ]	1개의 문자와 일치	'[0-8]%' : 0-8 사이 숫자로 시작하는 문자열
[ ^ ]	1개의 문자와 불일치	'[^0-8]%' : 0-8 사이 숫자로 시작하지 않는 문자열
_	특정 위치의 1개의 문자와 일치	'_동%' : 두 번째 위치에 '동'이 들어가는 문자열

 

주석 처리

주석 기호	설명
--	'--'이 시작하는 위치부터 해당 라인 끝까지 실행이 되지 않도록 함
/* 문장 */	'/*'이 시작되는 부분부터 '*/'이 나타날 때까지 여러 라인에 걸쳐 실행이 되지 않도록 함

 

힌트의 사용

힌트	설명
--+ 힌트 명(파라미터)	'--+'이 시작되는 위치부터는 힌트로 인식함
/* + 힌트 명(파라미터)*/	'/*+'이 시작되는 부분부터 '*/' 사이를 힌트로 인식함

 

2. 응용 SQL 작성

(1) 데이터 조작어(DML)

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

 

데이터 조작어의 유형

유형	동작
SELECT	데이터 조회
INSERT	데이터 생성
UPDATE	데이터 변경
DELETE	데이터 삭제

 

데이터 조작어 명령어 = SFWGHO

구분	설명
SELECT절	ALL : 모든 튜플을 검색할 때 사용, SELECT 뒤에 명시하지 않을 경우 ALL로 인식 DISTINCT : 중복된 속성이 조회될 경우 그중 한 개만 검색(SELECT뒤에 명시된 속성이 중복될 경우 한 개만 검색) DISTINCTROW : SELECT 뒤에 속성들과 상관없이 튜플 전체가 중복된 튜플을 제거
FROM절	질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
WHERE절	검색할 조건을 기술
GROUP BY절	속성값을 그룹으로 분류하고자 할 때 사용
HAVING절	GROUP BY에 의해 분류한 후 그룹에 대한 조건을 기술
ORDER BY절	ASC : 오름차순 정렬 DESC : 내림차순 정렬

 

(2) 데이터 제어어(DCL)

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

 

데이터 제어어 기능 = 보무병회

기능	설명
데이터 보안	불법적인 사용자로부터 데이터를 보호하는 기능
무결성 유지	데이터의 정확성과 일관성을 유지하는 기능
병행수행 제어	여러 트랜잭션을 수행할 때 트랜잭션들이 데이터베이스의 일관성을 파괴하지 않도록 트랜잭션 간의 상호작용을 제어하는 기능
회복	데이터베이스 장애가 발생할 경우, 데이터베이스를 장애 발생 이전의 상태로 복원하는 기능

 

데이터 제어어의 유형

유형	명령어	동작
DCL	GRANT	사용 권한 부여
	REVOKE	사용 권한 취소
DCL/TCL	COMMIT	트랙잰션 확정
	ROLLBACK	트랜잭션 취소
	SAVEPOINT (=CHECK POINT)	저장 시기 설정

 

데이터 제어어 명령어

① GRANT(권한 부여) 명령어

권한	구문
시스템 권한	GRANT 권한 TO 사용자;
객체 권한	GRANT 권한 ON 테이블 TO 사용자;

 

② REVOKE(권한 취소) 명령어

권한	구문
시스템 권한	REVOKE 권한 FROM 사용자;
객체 권한	REVOKE 권한 ON 테이블 FROM 사용자;

 

③ TCL 명령어

명령문	구분
트랜잭션 확정	COMMIT;
트랜잭션 취소	ROLLBACK;
세이브 포인트 지정	SAVEPOINT 이름;
세이브 포인트 롤백	ROLLBACK TO SAVEPOINT 이름;

 

(3) 윈도 함수

윈도 함수의 개념

윈도 함수는 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수이다. 

 

윈도 함수의 분류 = 집 순행비

분류	설명
집계 함수	여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수
순위 함수	레코드의 순위를 계산하는 함수 RANK, DENSE_RANK, ROW_NUMBER 함수가 존재
행 순서 함수	레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 존재
그룹 내 비율 함수	백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수 RATIO_TO_REPORT, PERCENT_RANK 함수가 존재

 

① 집계 함수

집계 함수	설명
COUNT	복수 행의 줄 수를 구하는 함수
SUM	복수 행의 해당 컬럼에 대한 합계를 구하는 함수
AVG	복수 행의 해당 컬럼 간의 평균에 대한 평균을 구하는 함수
MAX	복수 행의 해당 컬럼 중 최댓값을 구하는 함수
MIN	복수 행의 해당 컬럼 중 최솟값을 구하는 함수
STDDEV	복수 행의 해당 컬럼에 대한 표준편차를 구하는 함수
VARIAN	복수 행의 해당 컬럼에 대한 분산을 구하는 함수

 

② 순위 함수

순위 함수	설명
RANK	특정 항목에 대한 순위를 구하는 함수 동일 순위의 레코드 존재 시 후순위는 넘어감 (2위가 3개인 레코드인 경우 : 2위, 2위, 2위, 5위, 6위)
DENSE_RANK	레코드의 순위를 계산하는 함수 동일 순위의 레코드 존재 시에도 후순위로 넘어가지 않음 (2위가 3개인 레코드인 경우 : 2위, 2위, 2위, 3위, 4위)
ROW_NUMBER	레코드의 순위를 계산하는 함수 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여 (2위가 3개인 레코드인 경우 : 2위, 3위, 4위, 5위, 6위)

 

③ 행 순위 함수

행 순위 함수	설명
FIRST_VALUE	파티션별 윈도에서 가장 먼저 나오는 값을 반환하는 함수 집계 함수의 MIN과 동일한 결과 출력
LAST_VALUE	파티션별 윈도에서 가장 늦게 나오는 값을 반환하는 함수 집계 함수의 MAX와 동일한 결과 출력
LAG	파티션별 윈도에서 이전 로우의 값을 반환하는 함수
LEAD	파티션별 윈도에서 이후 로우의 값을 반환하는 함수

 

④ 그룹 내 비율 함수

그룹 내 비율 함수	설명
RATIO_TO_REPORT	주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수 결과값은 0~1의 범위 값을 가짐
PERCENT_RANK	주어진 그룹에 대해 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수 결과값은 0~1의 범위 값을 가짐

 

윈도 함수 활용

① OLAP(On-Line Analytical Processing) 개념

OLAP은 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 의사결정 지원 시스템이다.

 

② OLAP 연산

연산	설명
Roll-Up	분석할 항목에 대해 구체적인 데이터로부터 요약된 형태의 데이터로 접근하는 연산
Drill-Down	분석할 항목에 대해 요약된 형태의 데이터로부터 구체적인 데이터로 접근하는 연산
Slicing	온라인 분석처리를 위한 자료 구조인 데이터 큐브의 한 조각을 볼 수 있게 해주는 연산
Dicing	고정된 다차원 값에 대한 연산
Pivoting	다차원 분석 테이블인 크로스 테이블에서 차원 변경을 위해 사용되는 연산

 

(4) 그룹 함수

그룹 함수의 개념

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

 

그룹 함수의 유형

ROLLUP, CUBE, GROUPING SETS

 

① ROLLUP(소그룹 간 소계 출력)

지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간 집계 값이 생성된다.

 

② CUBE(GROUP BY 항목들과 다차원 소계 출력)

연산이 많아 시스템에 부담을 준다.

 

③ GROUPING SETS(특정 항목에 대한 소계 출력)

GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있다.

ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있다.

 

(5) 오류 처리

오류 처리의 개념

오류 처리는 프로그램 코드상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정이다. 

 

핸들러(HANDLER) 선언 구문 = 액상명

구문 요소	설명
액션	해당 오류 발생 시 핸들러 처리 방법 CONTINUE : 명령어 부분을 계속 실행 EXIT : 명령어 부분을 한 번 실행
상태 값	핸들러가 수행되기 위한 에러 코드 SQLWARING : 에러가 아닌 경고가 발생 NOTFOUND : 다음 레코드를 가져오지 못했을 때 SQLEXCEPTION : 에러가 발생
명령문	핸들러가 호출될 때 실행되는 명령

 

https://book.naver.com/bookdb/book_detail.nhn?bid=15910265

수제비 정보처리기사 필기