정보처리기사 실기 정리 - 7. SQL 응용

Chapter01 데이터베이스 기본 (중요도: ★★★)

◆ 트랜잭션(Transaction)

: 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성, 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

◆ 트랜잭션 특성(ACID)

• 원자성(Atomicity): 트랜잭션의 연산 전체가 성공 또는 실패되어야 하는 성질 (All or Nothing)
• 일관성(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(삭제) TRUNCATE(오브젝트 내용 모든데이터 삭제)

• CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
• RESTRICT: 다른 개체가 제거할 요소를 참조중일 떄는 제거를 취소

1) CREATE DOMAIN

2) CREATE SCHEMA

• 스키마를 정의 하는 명령문
  • 스키마란? 데이터베이스와 구조와 제약조건에 관한 전반적인 명세(Specification)를 기술한것으로 데이터 개체(Entity), 속성(Attribute), 관계(Realationship) 및 데이터 조작시 데이터 값들이 갖는 제약조건등에 관해 전반적으로 정의 한다
• 표기형식

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;
#예제 :  소유권자의 사용자ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문
CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

3) CREATE TABLE

4) CREATE VIEW

5) CREATE INDEX

6) ALTER TABLE

7) DROP

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

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

• DML 명령어(셀인업데)

  : SELECT(조회), INSERT(삽입), UPDATE(수정), DELETE(삭제)
  

(1) DML - SELECT -1

(2) DML - SELECT -2

그룹함수

WINDOW 함수

그룹 지정 검색

• GROUP BY ROLLUP(칼럼1,칼럼2 .. ) : 전체합계 맨아래 -> 칼럼1 합계 - > 칼럼 3합계
• GROUP BY CUBE(칼럼1,칼럼2 .. ) : ROLLUP의 반대 맨위에 합계 -> 칼럼2 합계 -> 칼럼1 합계
  
  

집합 연산자를 이용한 통합 질의

(3) DML -JOIN

◆ 데이터 제어어(DCL: Data Control Language)

: 데이터의 보안, 무결성, 회복, 병행 제어등을 정의하는데 사용하는 언어

• 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.

DCL의 종류

삽입문(INSERT INTO ~)

삭제문 (DELETE FORM ~)

갱신문 (UPDATE ~ SET ~)

• DCL 명령어

  - GRANT(그온투): 사용 권한 부여
  • GRANT 권한 ON 테이블 TO 사용자
  - REVOKE(리온프): 사용 권한 취소
  • REVOKE 권한 ON 테이블 FROM 사용자

Chapter02 응용 SQL 작성하기 (중요도: ★)

◆ 데이터 분석 함수 종류

• 집계 함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수 (SUM,MAX,MIN 등등)
• 그룹 함수: 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수 (GROUP BY ROLLUP, CUBE)
• 윈도우 함수: 데이터베이스를 사용한 온라인 분석 처리 (OLAP, Online Analytical Processing)용도로 사용하기 위해서 표준 SQL에 추가된 기능 ( OVER(ORDER BY * DESC) )

Chapter03 절차형 SQL 활용하기 (중요도: ★)

◆ 절차형 SQL(Procedural SQL)

: SQL언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

• 절차형 SQL 종류

• 프로시저(Procedure): 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 커리의 집합
  • 디비컨SET
    

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

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

Chapter04 데이터 조작 프로시저 최적화 (중요도: ★)

◆ 쿼리 성능 개선

: 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

• SQL 성능 개선 절차: 문제있는 SQL식별 → 옵티마이저 통계확인 → SQL문 재구성 → 인덱스 재구성 → 실행계획 유지관리

◆ 옵티마이저(Optimizer)

: SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈

• 옵티마이저 유형

  • 규칙기반 옵티마이저(RBO, Rule Based Optimizer): 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • 비용기반 옵티마이저(CBO, Cost Based Optimizer): 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는
    

◆ 힌트(Hint)

: 실행하려는 SQL문에 사전에 정보를 주어서 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 문법

• 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.
• 옵티마이저는 명시적인 힌트를 통해 실행 계획을 변경한다.

한번에 정리

CREATE DOMAIN 도메인명

CREATE SCHEMA 스키마명


CREATE TABLE 학생
(
    이름 VARCHAR(15) NOT NULL,
    학번 CHAR(8),

    PRIMARY KEY(학번),
    FOREIGN KEY

    CONSTRAINT 생년월일제약 CHECK( )
)



CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호 FROM 고객 WHERE 주소 ='안산시';


CREATE UNIQUE INDEX 인덱스명
ON 테이블명(속성명 DESC);


ALTER TABLE 테이블명 ADD 속성명 VARCHAR(5);
ALTER TABLE 테이블명 MODIFY 속성명 VARCHAR(5);
ALTER TABLE 테이블명 DROP 속성명 VARCHAR(5);


DROP SCHEMA 스키마명 CASCADE \ RESTRICT;  // 삭제가능 도스테뷰인


SELECT DISTINCT 주소 FROM 사원;


SELECT 부서 + '부서의' AS 부서2, 이름 + '이름2'
FROM 사원 ;


SELECT *
FROM 사원
WHERE 부서 = '기획' AND  주소 = '대흥동'; 
WHERE 이름 LIKE '김%';
WHERE 생일 BETWEEN #01/01/01# AND #01/02/02#;
WHERE 생일 IS NULL;
WHERE 생일 NOT NULL;
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 ='나이트댄스');  
WHERE 이름 NOT IN(SELECT 이름 FROM 여가활동);


SELECT *
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;



WINDOW 함수

SELECT 상여금, 상여내역, ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC)AS NO  
FROM 상여금;  // 상여내역 별로 상여금에 대한 일련번호 구해라 12341234


SELECT 상여금, 상여내역, RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC)AS 상여금 순위 
FROM 상여금;  // 상여내역 별로 상여금에 대한 순위 구해라  12245

SELECT 상여금, 상여내역, DENSE_RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC)AS 상여금 순위 
FROM 상여금;  //  12234



SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;



SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) > 2 ; 


SELECT 부서, 상여내역, SUM(상여금) AS 상여금 합계
FROM 상여금
GROUP BY ROLLUP(부서,상여내역);
GROUP BY CUBE(부서,상여내역);



//삽입문
INSERT INTO 테이블명(속성,속성)
VALUES (데이터1, 데이터2);


// 특정 튜플 삭제문
DELETE
FROM 테이블명
WHERE 조건;

DELETE
FROM 사원
WHERE 이름 = '임꺽정';


//갱신문

UPDATE 테이블명
SET 속성명 = 데이터
WHERE 조건

//사원테이블의 홍길동의 주소를 수색동으로 변경
UPDATE 사원
SET 주소 = '수색동'
WHERE 이름 = '홍길동';


//DCL 명령어

GRANT 권한 ON 테이블 TO 사용자
REVOKE 권한 ON 테이블 FROM 사용자