정보처리기사 실기 요약[SQL 응용- Chapter8](정처기)

정보처리기사 실기 정리는 제가 직접 수기로 작성하여 요약한 내용이기 때문에 조금의 오타가 있을 수 있습니다.

Chapter10 - 프로그래밍 언어 활용

Chapter11 - 응용 SW 기초 기술 활용

Chapter2 - 데이터 입출력 구현

본 3파트는 학교 OS, DB 수업과 코딩 테스트를 준비하면서 다른 과목들의 암기 시간을 늘리기 위해 정리(암기)하지 않고 갔습니다.

하지만 양이 많고, 가장 중요하면서 최소 4문제 이상 나오는 파트이기 때문에 준비를 잘해야하는 파트입니다.

시간이 된다면 추후에 추가하겠습니다.

 

SQL 응용

Chapter 8

SQL
RDB에서 사용되는 대표적인 언어, 관계 대수와 관계 해석을기초로 DB작업을 효율적으로 처리하기 위한 고급 데이터베이스 언어

DDL
종류
CREATE, ALTER, DROP
CREATE
테이블, 스키마, 도메인, 인덱스, 뷰 생성
CREATE TABLE 테이블 이름(
속성이름 타입 [NOT NULL,]
 [UNIQUE,]
 [FOREIGN KEY (속성이름) REFERENCES 참조테이블이름(속성이름) ] [ON DELETE | ON UPDATE | CASCASE | SET NULL | SET DEFAULT | NO ACTION]);
CREATE SCHEMA 스키마이름 AUTHORIZATION 사용자;
CREATE DOMAIN 도메인이름  데이터타입
[DEFAULT 기본값]
[CONSTRAINT 제약조건 CHECK(VALUE IN(범위값))];
CREATE [UNIQUE] INDEX 인덱스이름
ON 테이블이름(속성이름 [ASC|DESC])
[CLUSTER];
CREATE VIEW 뷰이름[(뷰속성이름)]
AS SELECT 기본테이블 속성이름
FROM 기본테이블이름
[WHERE 조건]
[WITH CHECK OPTION];
WITH CHECK OPTION 은 뷰에 갱신, 삽입, 수정시 WHERE조건을 따져서 조건에 맞지 않으면 실행하지 않음

데이터 타입
가변길이 문자 - VARCHAR(문자수)
고정길이문자 - CHAR(문자수)

ALTER
ALTER TABLE 테이블이름 ADD 속성이름 데이터타입 [DEFAULT];
ALTER TABLE 테이블이름 ALTER 속성이름 데이터타입 [SET DEFAULT];
ALTER TABLE 테이블이름 DROP 속성이름 데이터타입 [CASECADE | RESTRICT];
ALTER [UNIQUE] INDEX 인덱스이름 ON 테이블명(속성[ASC | DESC]);

DROP
DROP TABLE 테이블이름 [CASECADE | RESTRICT];
DROP SCHEMA 스키마이름 [CASECADE | RESTRICT];
DROP DOMAIN 도메인이름 [CASECADE | RESTRICT];
DROP VIEW 뷰이름 [CASECADE | RESTRICT];
DROP INDEX 인덱스이름;
DROP CONSTRAINT 제약조건이름;

RESTRICT : 삭제할 요소가 사용(참조) 중이면 삭제가 이루어지지 않음
CASECADE : 삭제할 요소가 사용(참조) 중이더라도 삭제, 삭제할 테이블을 참조 중인 다른 테이블도 연쇄 삭제

DML
종류
SELECT, INSERT, UPDATE, DELETE
SELECT
SELECT [DISTINCT] 속성이름
FROM 테이블이름
[WHERE 조건]
[GROUP BY 속성이름 [HAVING 그룹조건]]
[ORDER BY 속성이름 [ASC | DESC]];

NULL 과 비교시 IS NULL 이렇게 IS 사용
속성 매칭은 속성이름 LIKE '%AAAA' 이렇게 사용

INSERT
INSERT INTO 테이블이름(속성들) VALUES (속성들);

UPDATE
UPDATE 테이블이름 SET 속성이름 = 변경내용 [WHERE 조건];

DELETE
DELETE FROM 테이블이름 [WHERE 조건];

DCL
종류
COMMIT, ROLLBACK, GRANT, REVOKE
COMMIT
DB 내 연산이 성공적으로 종료되어 연산에 의한 수정내용을 지속적으로 유지하기 위한 명령어

ROLLBACK
DB 내 연산이 비정상적으로 종료되거나 정상적으로 수행되더라도 수행 이전으로 되돌리기 위해 연산 내용 취소 명령어

GRANT
DBA가 사용자에게 DB 권한을 부여하는 명령어
GRANT 권한 ON 테이블이름 TO 사용자 [WITH GRANT OPTION];
WITH GRANT OPTION : 사용자가 관리자로부터 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한 부여권까지 부여하고자 할때 사용

REVOKE
DBA가 사용자에게 부여한 권한을 취소하기 위해 사용하는 명령어
REVOKE 권한 ON 테이블이름 FROM 사용자 [CASCADE];

인덱스 
수많은 데이터 중에 원하는 자료를 빠르고 효율적으로 검색하기 위해 사용하는 방법
구성
인덱스파일(데이터 위치 저장), 데이터 파일(실제 데이터를 기억)
장점 : 검색속도향상, 시스템 부하 감소, 시스템 전체 성능 향상
단점 : 추가 DB공간 필요, 인덱스 생성 시간 필요, 잦은 변경 작업으로 성능 저하
기본키로 만들면 기본 인덱스, 일반 속성으로 만들면 보조 인덱스
종류
B트리
검색 효율을 높이기 위해 자료 구조를 균형 있는 트리 구조로 나타내는 방법

B+트리
B트리의 변형, 인덱스 세트와 순차 세트로 구성
인덱스 세트는 단말 노드를 찾기 위한 인덱스를 제공, 순차 세트는 단말 노드로만 구성

클러스터 인덱스
테이블에서 하나의 속성을 기준으로 정렬시킨 후, 테이블을 재구성해 인덱스를 만드는 방법
물리적 순서와 인덱스 순서가 동일
하나의 테이블에는 하나의 인덱스만 가능
장점 : 일정 범위를 찾는 경우 속도 향상에 도움

넌 클러스터 인덱스
테이블을 재구성하지 않고, 데이터 주소를 이용해 인덱스를 만들어 주소값을 이용해 검색하는 방법
하나의 테이블에 여러 개의 인덱스 만들수 있음
인덱스 구조보다 다소 복잡해질 수 있음
장점 : 한 개의 특정 값을 찾을때, 많은 양 데이터에서 작은 범위를 찾을때

인덱스 스캔 방식
속성값의 유일성에 따라 : unique, nonunique로 구분
인덱스 구성하는 속성 수 : 단일 인덱스, 결합 인덱스
물리적 구성 방식 : B*Tree, 단일맵(Bitmap), 클러스터

뷰
하나 이상의 테이블로 부터 유도된 가상의 테이블, 임시 테이블
실제 물리 기억공간은 사용안하며, 논리적 독립성을 제공, 데이터 접근제어로 보안성 향상
특징
기본 테이블이 삭제되면, 자동 삭제, 검색은 일반 테이블과 거의 동일, 삽입, 삭제, 갱신등이 제약이 따름, 보안 측면으로 활용 가능, ALTER 문 불가(삭제후 다시만듬)

시스템 카탈로그
개체들에 대한 정보와 정보들 간의 관계를 저장한 것
데이터 사전이라고 불리며 메타 데이터(저장된 데이터에 관한 데이터)를 저장
시스템 카탈로그는 사용자가 SQL 실행하면 시스템에 의해 자동적으로 이루어짐
일반 테이블과 같이 시스템 테이블로 구성됨

서브 쿼리
상위 질의어에 포함되는 하의 질의어
ORDER BY 사용 안하며, 비교 연산자의 오른쪽에 기술, ()안에서 사용
단일 행 서브 쿼리 : =, <>, < , > , >= ,<= 등으로 사용, 서브 쿼리 수행 결과가 오직 하나일때
다중 행 서브 쿼리 : 서브쿼리 반환 결과가 여러 행이 반환될때, 다중 행 연산자와 사용(IN, ANY, ALL, EXISTS)
IN : 메인 쿼리 조건이 서브쿼리 결과 중에 하나라도 일치하면 참
ANY : 메인 쿼리 비교 조건이 서브 쿼리 결과와 하나 이상 일치하면 참
ALL : 메인 쿼리 비교 조건이 서브 쿼리 검색 결과와 모든 값이 일치하면 참
EXISTS : 메인 쿼리 비교 조건이 서브 쿼리 결과 중 만족하는 값이 하나라도 존재하면 참

JOIN
물리적 조인
DB 내부에서 발생한느 테이블 결합 방식, 중첩 반복조인, 정렬 합병 조인, 해시 조인있음
내부 조인
동등 조인 : 동일 컬럼 기준으로 조합하여 나타냄
비동등 조인 : 동일 칼럼 없이 다른 조건 사용
외부조인 : Right Outer Join, Left Outer Join, Full Outer Join

집합 연산자
컬럼 수가 반드시 같아야하고, 컬럼의 자료형이 반드시 같아야 집합 연산 수행가능
UNION : 두질의 결과 행을 합침, 중복 제거
UNION ALL : 두질의 결과 행을 합침, 중복 포함
INTERSECT : 두질의 결과 행의 공통되는 행
MINUS : 첫 번째 질의 겨과에서 두번째 질의 결고에 있는 행을 제거한 값

다중 행 함수
GROUP BY 절에서는 그룹의 기준이 되는 컬러명을 기술, 집계 함수는 SELECT 절과 HAVING절에 사용
(윈도우 함수는 GROUP BY 절에 사용하지 않고 SELECT 절에 사용)

GROUP BY 절을 사용한 그룹 처리 함수
ROLLUP, CUBE, GROUPING SETS 함수
ROLLUP함수
주어진 컬럼별 소그룹 간의 집계와 총계를 계산 후 원래 테이블에 추가해 생성된 결과 테이블 반환
결과레벨 : N+1 (컬럼개수 N)
ex)GROUP BY ROLLUP(DEPT, GRADE);

CUBE함수
주어진 컬럼들 간의 결합 가능한 다차원적인 모든 조합의 그룹 집계와 컬럼별 집계 및 총계를 계산 후 원래 테이블에 추가해 결과 테이블 반환
결과레벨 : 2^N (컬럼개수 N)
ex)GROUP BY CUBE(DEPT, GRADE)
일반적으로 ROLLUP, CUBE 둘다 SELECT 절에 추가되어야 유의미한 결과 분석 가능

GROUPING SETS함수
주어진 컬럼별 집계를 계산후 집계의 결과 튜블만 출력

GROUP BY 사용하지 않은 윈도우 함수
윈도우함수는 분석함수라고도함,SELECT 절에만 사용 가능

순위 분석이 많이 나옴
RANK (), DENSE_RANK(), ROW_NUMBER()
ex) SELECT UNIQUE DEPT, GRADE, COUNT(*) OVER(PARTITION BY DEPT, GRADE) 인원수
       FROM STUDENT
       ODER BY DEPT, GRADE;

RANK() 함수는 전체 또는 윈도우별 행의 순위 구함 공통으로 묶고 다음은 다음 수로, 1이 공동 3명이면 ,111 그다음 4
DENSE_RANK()함수는 그룹 내 비율 함수로 동일한 값의 순위와 상관없이 1 증가
ROW_NUMBER() 함수는 행 순서 함수, 각행의 1부터 절대적으로 증가

절차형 SQL
종류 : 프로시저, 사용자 정의 함수, 트리거
필수 구성 요소 : DECLARE, BEGIN, END

PL/SQL
절차적 언어로 어떻게 처리할 것인지를 중점
프로시저, 함수, 트리거로 구성됨
SQL 단점 : 변수가 없음, 제어문 사용 불가, 예외처리 불가, 한번에 하나의 명령만 사용 가능

DECLARE : 선언부
BEGIN : 실행부로 필수절, 명령문들을 절차적으로 작성
EXCEPTION : 예외처리부, 예외처리 명령문을 기술하며 정상적으로 종료
END; : 실행 종료 예약어, 블록의 마지막 라인에 /를 입력

저장 프로시저
DB에 저장된 사용자가 만든 PL/SQL 명령문들을 말함.
자주 사용하는 복잡한 SQL DML 명령문을 서브 프로그램으로 만들고 필요할 때마다 호출하여 사용
성능향상과, 호환성 문제 해결됨

프로시저 : 호출로 실행, RETURN문 X, 블록내 DCL가능
함수 : 호출로 실행, RETURN문 O, 블록내 DCL 가능
트리거 :  호출로 실행 X, RETURN문 X, 블록내 DCL 불가능

매개변수가 있으며, MODE는 IN,OUT,INOUT으로 구성됨, EXECUTE 명령어로 실행
%TYPE
이미 존재하는 다른 변수가 대상 테이블의컬럼 속성과 동일한 자료형을 지정 
삭제 : DROP PROCEDURE 프로시저_이름;

사용자 정의 함수 생성 구문
RETURN 명령문에 의해 사용자 정의 함수 처리 결과를 단일값을 가지고 호출한 지점으로 반환
ON REPLACE : 기존에 같은 이름으로 사용자 정의 함수를 생성하면 기존 함수는 제거하고 새로운 내용으로 재생성
MODE 는 IN매개변수만 사용가능, RETURN이 반드시 필요
호출
VARIABLE A NUMBER;
EXECUTE :A := BONUS(1100);
PRINT A;
제거
DROP FUNCTION BONUS;

이벤트와 트리거
트리거 : DB에 특정 사건이 발생될 때마다 자동으로 수행되는 프로시저
INSERT, UPDATE, DELETE 가 DB테이블 대상으로 실앻될때 자동으로 수행되는 프룃저
트리거는 별도 DB에 저장
DCL 명령은 불가능
행트리거 : 각 행의 값의 변화가 생길때 마다 트리거 수행 , FOR EACH ROW 옵션
문장트리거 : 이벤트에 의해 단 한번만 수행
BEFORE : 트리거 이벤트가 실행되기 전 트리거가 수행
AFTER : 트리거 이벤트가 실행된 후 트리거 수행

SET SERVEROUTPUT ON :  표준 출력을 화면에 볼 수 있도록 설정.(기본은 OFF)
제거
DROP TRIGGER 트리거이름;