정보처리기사 실기 8장 SQL 응용 요점 정리

1. SQL - DDL

● DDL(Data Define Language, 데이터 정의어) : DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

- 번역한 결과 데이터 사전이라는 특별한 파일에 여러 개의 테이블로서 저장

● CREAT SCHEMA : 스키마 정의하는 명령어

● CREAT DOMAIN : 도메인 정의하는 명령어, 값의 범위가 SQL의 일부분일 때 그 값의 범위를 도메인으로 정의 가능

● CREAT TABLE : 테이블 정의하는 명령어

- 속성의 NOT NULL 제약 조건, 속성의 초기값 지정, CHECK 제약 조건의 정의

● CREAT VIEW : 뷰를 정의하는 명령문

● CREAT INDEX : 인덱스 정의하는 명령문

● ALTER TABLE : 테이블에 대한 정의 변경하는 명령어

● DROP : 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령어, 테이블 자체 제거 명령어

- CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거, 즉 주 테이블의 데이터 제거 시 각 외래 키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용

- RESTRICTED : 다른 개체가 제거할 요소를 참조 중 일 때는 제거를 취소

- TUPLE, INDEX, VIEW 제거, 테이블은 CASCADE 옵션일 때 제거

​

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

- 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 언어

- 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용

● GRANT : DB 사용자에게 사용 권한을 부여

● REVOKE : DB 사용자의 사용 권한 취소

● COMMIT : 트랜잭션이 성공적으로 끝나면 DB가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 DB에 반영해야 하는데 이때 사용하는 명령어

● ROLLBACK : 아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어

- 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만을 DB에 반영되는 비일관성인 상태 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백되어야 한다

● SAVEPOINT : 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

 

3. DML(Data Manipulation Language, 데이터 조작어)

- DB 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 관리하는 데 사용하는 언어

● 삽입문 : INTSERT INTO 테이블명(속성명1, 속성명2, ···) VALUE (데이터1, 데이터2,···)

- 테이블에 새로운 튜플 삽입, 대응하는 속성과 데이터의 개수와 데이터 유형 일치해야 함

- 기본 테이블의 모든 속성 사용 시 속성명을 생략 가능

- SELECT문 사용하여 다른 테이블의 검색 결과 삽입 가능

● 삭제문 : DELETE FROM 테이블명 WHERE 조건

- 특정 튜플 삭제 시 사용, 모든 레코드 삭제 시 WHERE절 생략, 모든 레코드 삭제해도 테이블은 남아있음(DROP과 대조)

● 갱신문 : UPDATE 테이블명 SET 속성명 = 데이터 WHERE 조건

- 특정 튜플 내용 변경 시 사용

 

4. DML - SELECT-1​

● 기본 검색 : 사원 테이블에 있는 모든 튜플을 검색하는 SQL문

- SELECT * FROM 사원;

- DISTICNT : 중복을 제거하여 첫 번째 한 개만 검색

● 조건 지정 검색 : 사원 테이블에서 부서가 개발인 튜플 검색 SQL문

- SELECT * FROM 사원 WHERE 부서 = "개발";

- 날짜 데이터 : 숫자로 취급, ' ' or # #으로 묶어 줌

- NULL이 아닌 값 검색 : IS NOT NULL

● 정렬 검색 : ORDER BY 절에 특정 속성 지정 ASC : 오름차순, DESC : 내림차순

● 하위 질의 : 조건절에 다시 SELECT문을 넣어 그 결과를 조건으로 검색

- Not In ( ) : 포함되지 않는 데이터 의미

● 복수 테이블 검색 : 여러 테이블을 대상으로 검색을 수행

 

5. DML - SELECT-2

● 그룹 함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집게 할 함수를 기술

- GROUP BY : 그룹 지정 검색, 일반적으로 GROUP BY 절은 그룹 함수와 함께 사용

- 사원 테이블에서 부서별 경력의 평균을 구하는 SQL문 => SELECT 부서, AVG(경력)AS 평균 FROM 상여금 GROUP BY 부서;

● WINDOW 함수 : GROUP BY 절을 이용하지 않고 속성의 갑을 집계할 함수를 기술

- PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정

- ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정

● HAVING 절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정

● 집합 연산자를 이용한 통합 질의 : 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합

- 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함

- UNION : 두 SELECT문의 결과를 통합하고 중복된 행 한 번만 출력

- UNION ALL : 두 SELECT문의 결과를 통합하고 중복된 행도 그대로 출력

- INTERSECT : 두 SELECT문의 결과 중 공통된 행만 출력

- EXCEPT : 첫 번째 SELECT문의 결과에서 두 번째 SELECT문의 결과를 제외한 행 출력

 

6. 프로시저

- 절차형 SQL을 활용하여 특정 기능 수행하는 일종의 트랜잭션 언어

- 호출 통해 실행되어 미리 저장해 놓은 SQL 작업 수행, 여러 프로그램에서 호출하여 사용

- DB에 저장되어 수행되기 때문에 스토어드 프로시저라고 불림, 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 쓰임

● 구성

- DECLARE(필수) : 프로시저의 명칭, 변수, 인수, 데이터 타입 정의하는 선언부

- BEGIN / END(필수) : 프로시저의 시작과 종료 의미

- CONTROL : 조건문 or 반복문이 삽입되어 순차적으로 처리, 실행 흐름 제어

- SQL : DML, DCL이 삽입되어 데이터 관리 위한 조회, 추가, 수정, 삭제 작업 수행

- EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법 정의

- TRANSACTION : 수행된 데이터 작업들을 DB에 저장할지 취소할지 결정, 적용 여부 결정

● 프로시저의 생성 : CREATE PROCEDURE 명령어 사용

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역번수 선언]

BEGIN

프로시저 BODY;

END;

- OR REPLACE : 선택적인 예약어, 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저 대체

- 파라미터

* IN : 호출 프로그램이 프로시저에게 값 전달

* OUT : 프로시저가 호출 프로그램에게 값 전달

* INOUT : 호출 프로그램이 프로시저에게 값 전달하고, 프로시저 실행 후 호출 프로그램에게 값 반환 시 지정

* 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정

* 자료형 : 변수의 자료형을 지정

- 프로시저 BODY

- 프로시저 코드 기록, BEGIN 과 END 사이에 적어도 하나의 SQL문이 존재

● 프로시저 실행 : EXECUTE 프로시저명;, EXEC 프로시저명;, CALL 프로시저명;

● 프로시저 제거 : DROP PROCEDURE 프로시저명;

 

7. 트리거

: 삽입, 갱신, 삭제 등의 이벤트 발생 시마다 관련 작업이 자동으로 수행되는 절차형 SQL

- DB에 저장되며 데이터의 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용

- DCL 사용 불가능, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생

- 트리거에 오류 있는 경우 트리거가 처리하는 데이터 영향 미침, 외부에서 필드나 데이터 주고받지 않음​

● 구성

- DECLARE(필수)

- EVENT(필수) : 트리거가 실행되는 조건 명시

- BEGIN / END(필수)

- CONTROL

- SQL

- EXCEPTION

● 트리거 생성 : CREAT TRIGGER 명령어 사용

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명

REFERENCING [NEW | OLD] AS 테이블명

FOR EACH ROW

[WHEN 조건식]

BEGIN

트리거 BODY;

END;

- 동작 시기 옵션 : 트리거가 실행될 때를 지정

* AFTER : 테이블이 변경된 후 트리거 실행

* BEFORE : 테이블 변경 전 트리거 실행

- 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정

* INSERT : 새로운 튜플 삽입

* DELETE : 튜플 삭제

* UPDATE : 튜플 수정

- NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정

* NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미

* OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미

- FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미

- WHEN 조건식 : 선택적인 예약어, 트리거를 작용할 튜플의 조건을 지정

● 트리거의 제거 : DROP TRIGGER 트리거명;

- 트리거는 데이터 변경 시 자동으로 수행되므로 호출문 존재 X

​

8. 사용자 정의 함수

- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하여 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL

- DB에 저장되어 DML문의 호출에 의해 실행, 예약어 RETURN을 통해 반환되기 때문에 출력 파라미터 없음

- 테이블 조작(DML) 불가능, SELECT를 통한 조회만 가능, 프로시저 호출 사용 불가능,

프로시저 VS 사용자 정의 함수

● 사용자 정의 함수의 구성

- 프로시저의 구성과 유사, 프로시저의 구성 + RETURN(호출 프로그램에 반환할 값이나 변수 정의)

● 사용자 정의 함수 생성 : CREAT FUNCTION

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)

[지역변수 선언]

BEGIN

사용자 정의 함수 BODY;

RETURN 반환값;

END;

- OR REPLACE

- 파라미터

* IN

* 매개변수명

* 자료형

- 사용자 정의 함수 BODY

- RETURN 반환값

● 사용자 정의 함수 실행 : DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

● 사용자 정의 함수 제거 : DROP FUNCTION 사용자 정의 함수명;

 

9. 제어문

● 개요 : 절차형 SQL은 '절차형'이라는 말 그대로 SQL 명령어가 서술된 순서에 따라 위에서 아래로 차례대로 실행되는데, 제어문은 이러한 진행 순서를 변경하기 위해 사용하는 명령문

- IF, LOOP, GOTO

● IF문

- 형식1

IF 조건 THEN

   실행할 문장1;

   실행할 문장2;

          ·

          ·

END IF

- 형식2

IF 조건 THEN

   실행할 문장1;

ELSE

   실행할 문장2;

END IF;

- 형식3

IF 조건1 THEN

   실행할 문장1;

ELSEIF 조건2 THEN

   실행할 문장2;

ELSEIF 조건3 THEN

   실행할 문장3;

          ·

          ·

ELSE

   실행할 문장4;

END IF

● LOOP문

- 기본형 : EXIT WHEN 조건이 참일 때 반복 종료LOOP   실행할 문장;   EXIT WHEN 조건;

END LOOP;

- FOR LOOP : 초기값부터 종료 값까지 1씩 증가하면서 실행할 문장을 반복 수행

FOR 변수 IN 초기값..종료값

LOOP

   실행할 문장;

END LOOP;

- WHILE LOOP : 조건이 참인 동안 실행할 문장 반복 수행WHILE 조건

LOOP

   실행할 문장;

END LOOP;

● CONTINUE

- 반복문의 실행을 제어하기 위해 사용하는 예약어

- CONTINUE 이후의 문장은 실행하지 않고 제어를 반복문의 처음으로 옮김- 형식CONTINUE WHEN 조건;

● GOTO문

- 원하는 위치로 이동하여 명령문을 수행하기 위해 사용하는 제어문

- GOTO문은 원하는 문장으로 쉽게 이동할 수 있지만 많이 사용하면 프로그램의 이해와 유지 보수 어려워져 거의 사용 X

- 형식GOTO 레이블;<<레이블>>실행할 문장;

 

10. 커서(Cursor)

● 개념 : 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)

- 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행

● 묵시적 커서(Implicit Cursor)

: DBMS 자체적으로 열리고(Open) 패치(Fetch)되어 사용이 끝나면 닫히지만(Close) 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것 가능

- 각 단계 자동으로 수행, 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용

- 속성 

* SQL%FOUND : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 1개 이상이면 TRUE  * SQL%NOTFOUND : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 0개이면 TRUE 

* SQL%ROWCOUNT : 쿼리 수행의 결과로 패치(Fetch)된 튜플 수를 반환 

* SQL%ISOPEN : 커서가 열린(Open) 상태이면 TRUE, 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

● 명시적 커서(Explicit Cursor)

: 명시적 커서는 사용자가 직접 정의해서 사용하는 커서, 주로 절차형 SQL에서 SELECT문의 결과로 반환되는 여러 튜플들을 제어하기 위해 사용됨

- 커서는 기본적으로 열기(Open) - 패치(Fetch) - 닫기(Close) 순으로 이루어지며, 명시적 커서로 사용하기 위해서는 열기 전에 선언(Declare)을 해야 함

- 선언(Declare) 형식CURSOR 커서명(매개변수1, 매개변수2, ···)IS

SELECT 문;- 열기(Open) 형식OPEN 커서명(매개변수1, 매개변수2, ···)- 패치(Fetch)형식FETCH 커서명 INTO 변수1, 변수2, ···;

- 닫기(Close)형식CLOSE 커서명;