[정보처리기사 실기 / 내용정리] 07. SQL 응용

정보처리기사 실기 07. SQL 응용

반응형

데이터베이스 기초 활용하기

* 데이터베이스(Database) : 다수의 인원, 시스템 또는 프로그램이 사용할 목적으로 통합하여 관리되는 데이터의 집합

* 데이터베이스 정의
 - 통합된 데이터 : 자료의 중복을 배제한 데이터의 모임
 - 저장된 데이터 : 저장 매체에 저장된 데이터
 - 운영 데이터 : 조직의 업무를 수행하는 데 필요한 데이터
 - 공용 데이터 : 여러 애플리케이션, 시스템들이 공동으로 사용하는 데이터
* 데이터 베이스 특성
 - 실시간 접근성 : 쿼리에 대하여 실시간 응답이 가능해야 함
 - 계속적인 변화 : 새로운 데이터의 삽입, 삭제 갱신으로 항상 최신의 데이터를 유지
 - 동시 공용 : 다수의 사용자가 동시에 같은 내용의 데이터를 이용할 수 있어야 함
 - 내용 참조 : 사용자가 요구하는 데이터 내용으로 데이터를 찾음
* 데이터베이스 종류
 - 파일 시스템(File System) : 파일에 이름을 부여하고 저장이나 검색을 위해 논리적으로 그것들을 어디에 위치시켜야 하는지 등을 정의한 뒤 관리하는 데이터베이스 전 단계의 데이터 관리 방식
 - 관계형 데이터베이스 시스템(RDBMS) : 관계형 모델을 기반
   + 종류 : Orcle, SQL Server, MySQL, Mari DB 등
 - 계층형 데이터베이스 시스템(HDBMS) : 데이터를 상하 종속적인 관계로 계층 화화여 관리
    + 종류 : IMS, System2000 등
 - 네트워크 데이터베이스 관리시스템(NDBMS) : 데이터를 네트워크 상의 망상 형태로 표현한 데이터 모델
   + 종류 : IDS, IDMS 등
* DBMS(Database Management System): 데이터 관리의 복잡성을 해결하는 동시에 데이터 추가, 변경, 검색, 삭제 및 백업, 복구, 보안 등의 기능을 지원하는 소프트웨어
* DBMS 유형
 - 키-값(Key-Value) DBMS : Unique 한 키에 하나의 값을 가지고 있는 형태
 - 컬럼 기반 데이터 저장(Column Family Data Store) DMBS : Key안에(Column, Value) 조합으로 된 여러 개의 필드를 갖는 DBMS
 - 문서 저장(Document Store) DBMS : 값(Value)의 데이터 타입이 문서(Documnet)라는 타입을 사용하는 DBMS
 - 그래프(Graph) DBMS : 시맨틱 웹과 온톨로지 분야에서 활용되는 그래프로 데이터를 표현하는 DBMS

빅데이터(BIg Data)

* 시스템, 서비스, 조직(회사) 등에서 주어진 비용, 시간 내에 처리 가능한 수십 페타바이트(PB) 크기의 비정형 데이터
* 빅데이터 특성 : 데이터의 양, 데이터의 다양성, 데이터의 속도,

* 데이터 수집, 저장, 처리 기술
 - 비정형/반정형 데이터 수집 : 내/외부 정제되지 않은 데이터를 확보하여 수집 및 전송하는 기술
 - 정형 데이터 수집 : 내/외부 정제된 대용량 데이터의 수집 및 전송 기술
 - 분산 데이터 저장/처리 : 대용량 파일의 효과적인 부산 저장 및 분산 처리 기술
 - 분산 데이터 베이스 : HDFS 칼럼 기반 데이터베이스로 실시간 랜덤 조회 및 업데이트 가능
 - HDFS : 대용량 데이터의 집합을 처리하는 응용 프로그램에 적합하도록 설계된 하둡 분산 파일 시스템

NoSQL(Not Only SQL)

* 데이터 저장에 고정된 테이블 스키마가 필요하지 않고 조인 연산을 사용할 수 없으며, 수평적으로 확장이 가능한 DBMS
* NoSQL 특성
 - Basically Available : 언제든지 데이터 접근할 수 있는 속성
 - Soft-State : 외부에서 전송된 정보를 통해 결정되는 속성
 - Eventually Consistency : 일관성이 유지되는 속성
* NoSQL 유형
 - Key-Value Store : Unique 한 키에 하나의 값을 가지고 있는 형태
 - Column Family Data Store : Key안에(Column, Value) 조합으로 된 여러 개의 필드를 갖는 DB
 - Document Store : 값(Value)의 데이터 타입이 문서(Documnet)라는 타입을 사용하는 DB
 - Graph DBMS : 시맨틱 웹과 온톨로지 분야에서 활용되는 그래프로 데이터를 표현하는 DBMS
 - 시맨틱 웹 : 온톨로지를 활용하여 서비스를 기술하고, 온톨로지의 의미적 상호 운용성을 이용해서 서비스 검색, 조합, 중재 기능을 자동화하는 웹
 - 온톨로지 : 실세계에 존재하는 모든 개념들과 개념들의 속성, 그리고 개념들 간의 관계 정보를 컴퓨터가 이해할 수 있도록 서술해 놓은 지식베이스

데이터 마이닝(Data Minning)

* 대규모로 저장된 데이터 안에서 체계적이고 자동적으로 통계적 규칙이나 패턴을 찾아내는 기술
* 데이터 마이닝 절차 : 목적 설정 → 데이터 준비 → 가공 → 마이닝 기법 적용 → 정보 검증
* 데이터 마이닝 주요 기법
 - 분류 규칙 : 과거 데이터를 토대로 새로운 레코드의 결과 값을 예측하는 기법
 - 연관 규칙 : 데이터 안에 항목들 간의 종속관계를 찾아내는 기법
 - 연속 규칙 : 연관 규칙에 시간 관련 정보가 포함된 형태의 기법
 - 데이터 군집화 : 대상 레코드들을 유사한 특성을 지닌 몇 개의 소그룹으로 분할하는 작업

데이터 베이스 기본

* 트랜잭션(Transaction) : 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성, 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
* 트랜잭션 특성
 - 원자성(Atomicity) : 트랜잭션의 연산 전체가 성공 또는 실패되어야 하는 성질(All or Nothinig)
 - 일관성(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(삭제)

CREATE DOMAIN 도메인명 [AS] 데이터_타입
	   [DEFAULT 기본값]
       [CONSTRAINT 제약조건명 CHECK (범위값)];
       
CREATE TABLE 테이블명
	   (속성명 데이터_타입 [DEFAULT 기본값][NOT NULL], ...
       [, PRIMARY KEY(기본키_속성명)]
       [, UNIQUE(대체키_속성명)]
       [, FOREIGN KEY(외래키_속성명)]
       		    [REFERENCES 참조테이블(기본키_속성명)]
                [ON DELETE 옵션]
                [ON UPDATE 옵션]
       [, CONSTRAINT 제약조건명][CHECK (조건식)];
// CHECK : 속성 값에 대한 제약 조건을 정의
// CONSTRAINT : 제약조건의 이름을 지정한다.
       
CREATE VIEW 뷰명[(속성명[,속성명, ...])]
AS SELECT문;

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [속성명 [ASC | DESC])]
[CLUSTER];
//UNIQUE:사용시 중복값이 없는 속성으로 인덱스 생성
// ASC : 오름차순
// DESC: 내림차순
//CLUSTER:사용하면 인덱스가 클러스터드인덱스로 설정됨

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFQULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
//ADD : 새로운 속성을 추가할 때 사용
//ALTER : 특정 속성의 Default값을 변경할 때 사용한다.
//DROP COLUMN : 특정속성을 삭제할 때 사용

DROP SCHEMA 스키마명 [CASCADE|RESTRICTED];
DROP DOMAIN 도메인명 [CASCADE|RESTRICTED];
DROP TABLE 테이블명 [CASCADE|RESTRICTED];
DROP VIEW 뷰명 [CASCADE|RESTRICTED];
DROP INDEX 인덱스명 [CASCADE|RESTRICTED];
DROP CONSTRAINT 제약조건명;
// CASCADE : 제거할 요소를 참조하는 다른 모든 객체를 함께 제거
// RESTRICT : 다른 개체가 제거할 요소를 참조 중일 때는 제거를 취소

* 데이터 조작어 (DML : Data Manipulation language) : 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어
 - DML 유형 : SELECT(조회), INSERT(삽입), UPDATE(수정), DELETE(삭제)

INSERT INTO 테이블명([속성명1],[속성명2],...)
VALUES (데이터1, 데이터2,...)

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

UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

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

 //GRANT : 사용권한 부여 [ON TO WITH]
 //REVOKE : 사용권한 취소 [ON FROM CASCADE]
 
 //사용자등급지정 및 해제
 GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
 REVOKE 사용자등급 FROM 사용자_ID_리스트;
 
 //테이블 및 속성에 대한 권한 부여 및 취소
 GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
 REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

 

응용 SQL 작성하기

* 데이터 분석 함수 종류
 - 집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수 
   + GROUP BY문 사용
   + COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN 함수 사용 - 그룹 함수 : 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
   + ROLLUP : 소계, 중간 집계 값 산출
   + CUBE : 다차원 집계 생성
   + GROUPING SET : 컬럼에 대한 개별 집계

 - 윈도 함수 : 데이터 베이스를 사용한 오라인 부석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능  
   + SELECT 함수명(파라미터) OVER PARTITION BY ORDER BY

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][,[테이블명.]속성명,...]
[, 그룹함수(속성명) [AS별칭]]
[, Window함수 OVER (PARTITION BY 속성명1, 속성명2, ...
				ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명[,테이블명,...]
[WHERE 조건]
[GROYP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];

* 조건 연산자/연산자 우선순위

* 비교 연산자

연산자	=	<>	>	<	>=	<=
의미	같다	같지 않다	크다	작다	크거나 같다	작거나 같다

* 논리 연산자 : NOT, AND, OR

대표 문자	%	_	#
의미	모든 문자를 대표함	문자 하나를 대표함	숫자 하나를 대표함

* LIKE 연산자 : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용됩니다.

     = 순위 함수 : RANK(), DENSE_RANK(), ROW_NUMBER()
     = 행 순서 함수 : FIRST_VALUE, LAST_VALUE
     = 그룹 내 비율 함수 : RATIO_TO_REPORT, PERENT_RANK * JDBC : 자바언어를 이용하여 DBMS에 질의하고 데이터를 조작하는 API 제공
* MyBatis : DBMS에 질의하기 위해 SQL 쿼리를 별도의 파일에 분리하고 Mapping을 통해 SQL 실행
 - 복잡한 JDBC코드 단순화
 - SQL문 거의 그대로 사용 가능
 - 우수한 성능

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

* 제어 SQL 

* 제어문

IF 조건 THEN
	실행할 문장1;
    실행할 문장2;
    	.
        .
        .
END IF;

LOOP
	실행할 문장;
    EXIT WHEN 조건;
END LOOP;

FOR 변수 IN 초기값..종료값
//..은 예약어 그대로 적는다.

WHILE 조건
LOOP
	실행할 문장;
END LOOP;

CONTITNUE WHEN 조건;

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

 

절차형 SQL 활용하기

* 절차형 SQL(Procedural SQL) : SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어
 - 절차형 SQL종류
   + 프로시저 (Procedure) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 커리의 집합, 절차형 SQL문을 활용하여 사전에 정의해놓은 SQL문을 호출할 경우 특정 기능을 수행하는 트랜잭션
     = CREATE OR REPLACE : 프로시저 생성 SQL문

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

   + 사용자 정의 함수 (User-Defined Function) : SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

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

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

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW|OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
	트리거 BODY;
END;
//NEW : 추가되거나 수정에 참여할 튜플들의 집합
//OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합
//FORE EACH ROW : 각 튜플마다 트리거를 적용

//제거
DROP TRIGGER 트리거명;

    = 행 트리거 : 데이터 변환 시 실행
    = 문장 트리거 : 트리거에 의해 한번 실행

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

 

데이터 조작 프로시저

* 저장된 프로시저, 저장된 함수, 저장된 패키지, 트리거
* 프로시저 : SQL을 이용해 생성된 데이터를 조작하는 프로그램
 - PL/SQL : 표준 SQL을 기반으로 Oracle에서 개발한 데이터 조작 언어
 - JDBC : 자바에서 DB를 사용할 수 있도록 연결해주는 인터페이스
* 프로시저 생성 SQL문

CREATE [OR REPLACE]PROCEDURE 프로시저명(파라미터)
[지역변수 선언[
BEGIN
   프로시저 BODY;
END;

//실행
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

//제거
DROP PROCEDURE 프로시저명;

 - DBMS_OUTPUT : 메시지를 버퍼에 저장하고 버퍼로부터 읽어오기 위한 인터페이스 패키지
 - SEVEROUPUT ON : PL/SQL 처리 결과를 출력
 - SHOW ERRORS : PL/SQL 오류 발생 출력 * APM (Application Performance Monitoring) : 안정적인 시스템 운영을 위한 성능 모니터링 도구

데이터 조작 프로시저 최적화

* 쿼리 성능 개선 : 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
 - SQL 성능 개선 절차 : 문제 있는 SQL 식별 -> 옵티마이저 통계 확인 -> SQL문 재구성 -> 인덱스 재구성 -> 실행계획 유지관리
* 옵티마이저(Optimizer) : SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
 - 옵티마이저 유형
   + 규칙 기반 옵티마이저(RBO) : 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
   + 비용 기반 옵티마이저 (CBO) : 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용 기반 옵티마이저

* 힌트(Hint) : 실행하려는 SQL문에 사전에 정보를 주어서 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 문법
 - 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.
 - 옵티마이저는 명시적인 힌트를 통해 실행 계획을 변경한다.