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

Ch1 데이터베이스 기본

1. 트랜잭션이란?

: 데이터베이스에서 하나의 논리적 기능ㅇ르 정상적으로 수행하기 위한 작업의 기본 단위

 

- 트랜잭션의 특성

원자성 Atomicity	트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 최소 되어야함	Commit/Rollback 회복성 보장
일관성 Consistency	시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 완료 후의 상태가 같아야함	무결성 제약 조건 동시성 제어
격리성 = 고립성 Isolation	동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야함	Read Uncommitted Read Committed Repeatable Read Serializable
영속성 Durability	성공이 완료된 트랜잭션의 결과는 영속적으로 디비에 저장되어야함	회복기법

 

- 트랜잭션의 상태변화

활성 -> 부분완료 -Commit-> 완료

    |           |

       -> 실패 -Rollback-> 철회

 

- 트랜잭션 제어

트랜잭션 제어언어 == TCL(Transaction Control Language) 트랜잭션의 결과를 허용 or 취소함 

커밋(Commit) : 트랜잭션 확정 / 트랜잭션을 메모리에 영구적으로 저장

롤백(Rollback): 트랜잭션 취소 / 트랜잭션 내역을 저장 무효화

체크포인트(Check Point) : 저장 시기 설정 / rollback을 위한 시점을 지정하는 명령어

 

- 병행 제어(일관성 주요 기법)이란?

: 병행제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때 디비 일관성 유지를 위해 상호 작용을 제어하는 것

 

- 목적 

데이터 베이스 공유 최대화, 시스템의 활용도 최대화, 일관성 유지, 사용자에 대한 응답시간 최소화

 

- 병행 제어 미보장 시 문제점

갱신손실 (Lost Update)	먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸때 발생하는 오류
현황 파악 오류 (Drity Read)	트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
모순성 (Inconsistency)	두 트랜잭션이 동시에 실행되어 디비의 일관성이 결여되는 오류
연쇄복귀 (Cascading Rollback)	복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류 ㅠ

- 병행 제어 기법의 종류

로킹 (Locking)	같은 자원을 엑세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법 로킹단위↓  => 디비 공유도↑, 로킹 오버헤드↑
낙관적 검증 (Optimistic Validation)	트랜잭션이 어떤 검증도 수행 X, 일단 트랜잭션 수행하고 종료시 검증 수행해 디비에 반영
타임 스탬프 순서 (Time Stamp Ordering)	트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전 타임 스탬프를 부여해 부여된 시간에 따라 트랜잭션 수행
다중버전 동시성 제어 (MVCC)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교해 직렬가능성이 보장되는 적절한 버전을 선택해 접근

 

- 데이터베이스 고립화(Isolation) 수준

read uncommitted, read committed, repeatable read, serializable read

 

- 회복기법 (영속성 주요기법)

로그기반 회복기법	지연 갱신 회복기법 : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법 즉각 갱신 회복기법 : 트랜잭션 수행 중 갱신 결과를 바로 디비에 반영하는 기법
체크 포인트 회복 기법	장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원
그림자 페이징 회복 기법	트랜잭션 수행 시 복제본을 생성해 디비 장애시 이를 이용해 복구

 

2. DDL이란?

: 데이터 정의어, 테이블같은 데이터 구조를 정의하는데 사용되는 명령어들/ 생성, 변경, 삭제, 이름을 바꾸는 것들

 

- DDL 대상

도메인, 스키마, 테이블, 뷰, 인덱스

 

- 인덱스종류

순서, 해시, 비트맵, 함수기반, 단일, 결합, 클러스터드 

순해비함단결클 

 

- 인덱스 스캔 방식

인덱스 범위 스캔 : 루트->리프 블록까지 수직적 탐색 후 리프블록을 필요한 범위만 스캔

인덱스 전체 스캔 : 수직적 탐색X 처음부터 끝까지 수평적 탐색

인덱스 단일 스캔 : 수직적 탐색만

인덱스 생략 스캔 : 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용

 

- DDL 명령어 

CREATE

ALTER

DROP : 오브젝트 삭제 

TRUNCATE : 디비 오브젝트 내용 삭제

 

Create table 사원

( 

업무 varchar (20) foreign key references 참조테이블(기본키)

이름 varchar (20) unique,

성별 char(1) check(성별 ='M' or 성별='F')

입사일 date default sysdate -- 현재시간/날짜로 들어감

);

 

- 컬럼 추가 

Alter table 사원 ADD 컬럼명 데이터 타입 [제약조건];

 

- 컬럼 수정

alter table 사원 MODIFY 컬럼명 데이터타입 [제약조건];

 

- 컬럼 삭제 

alter table 사원 DROP 컬럼명;

 

- 테이블 삭제 

Drop table 테이블명 [cascade or restrict];

cascade : 참조하는 테이블까지 연쇄 제거

restrict : 다른 테이블이 삭제할 테이블을 참조 중이면 제거 하지 않음

 

- 테이블 내의 데이터 삭제

Truncate table 테이블명;

 

- view 생성 (order by, union 절 사용 X)

create view 뷰이름 as

조회쿼리; (select 어쩌고 from 어쩌고 where 머쩌고)

 

- create or replace view 뷰이름 as 조회쿼리;

뷰 교체 명령어 

 

- 뷰삭제

drop view 뷰이름; 

 

- 인덱스 생성

 create index 인덱스 on 테이블명 (컬럼명1, 컬럼명2, ...)

컬럼명 1, 2등 복수의 컬럼을 한 인덱스로 엮을 수 있음 but 중복허용X 

 

- 인덱스 수정

Alter index 인덱스명 on 테이블명 (컬럼명1, 컬럼명2, ...)

 

- 인덱스 삭제

drop index 인덱스명

 

3. DML이란?

: 데이터 조작어/입력 수정, 삭제, 조회 가능 / select, insert, update, delete

 

- 조회 (SELECT)

SELECT [ALL or DISTINCT] 속성명1, 속성명2...

FROM 테이블명

[WHERE 조건절]

[GROUP BY 속성명1, ..]

[HAVING BY 그룹조건]

[ORDER BY 속성 [ACS or DESC]];

 

ALL : 그냥 다, select는 all이 디폴트값

DISTINCT : 중복X 

GROUP BY : 속성값을 그룹으로 분류 / 집계함수 쓸 때 사용함

HAVING BY : GROUP BY의 조건절 

ORDER BY : 속성 정렬할 때 ACS는 오름차순, DESC는 내림차순, order by가 여러개면 첫번째 값을 기준으로 정렬->같은 값이면 다음 속성으로 정렬/ 속성같이 여러개이고 하나는 오름차순 하나는 내림차순 가능

 

- where 조건문

=	값이 같을때
<>, !=	값이 다를때
<, <=, >, >=	비교연산
BETWEEN	컬럼 BETWEEN 값1 AND 값2 값1보다 크거나 같고 값2보다 작거나 같은 데이터 == 컬럼 >= 값1 AND 컬럼 <= 값
IN	컬럼 IN(값1, 값2...) 컬럼이 in 안에 포함된 경ㅇ
NOT IN	in안에 포함되어 있지 않은 경우
LIKE	컬럼 LIKE 패턴 % : 0개 이상의 문자열과 일치 ex ) 김% 김으로 시작하는 컬럼 [] : 1개의 문자와 일치 ex) [ABCD]% a or b or c or d or 인 문자열로 시작하는 컬럼 [^] : 위에거랑 반대 1개의 문자와 불일치 _ : 특정위치의 1개의 문자와 일치 ex) ---A 네번째글자가 A인 문자열

 

- 조인 (JOIN)

내부조인 : select 속성, 속성... from 테이블A JOIN 테이블B ON 조인조건 where 조건; / 공통 컬럼의 값이 같을때

외부조인 : LEFT, RIGHT, FULL JOIN이 있음 left는 왼쪽에 있는 테이블을 기준으로 오른쪽 테이블에 동일 데이터 추출 right는 반대, FULL은 두 테이블의 모든 데이터 추출 

교차조인 : 조인 조건이 없는 모든 데이터 조합을 추출 / 두 테이블을 합쳐서 나올수 있는 모든 경우의 튜플 반환

셀프조인 : 나랑 다시 조인 

 

- 집합 연산자 

UNION : 중복행이 제거된 쿼리 반환 (합집합)

UNION ALL : 중복 레코드 허용 (합집합인데 중복혀용)

INTERSECT : 두 쿼리 결과에 공통적으로 존재하는 결과만 (교집합)

MINUS : 첫 쿼리에는 있고 두번째 쿼리에는 없는 결과만 (차집합)

 

- 삽입

INSERT INTO 테이블명 (컬럼명1, 2,,,)

VALUES (데이터1, 2,,);

 

- 변경 (데이터 변경)

UPDATE 테이블명

SET 컬럼명 = 데이터, ...

WHERE 조건;

 

- 삭제 (데이터 삭제)

DELETE FROM 테이블명

WHERE 조건;

 

4. DCL이란?

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

 

- GRANT (사용자에게 테이블에 대한 권한 부여)

GRANT 권한 ON 테이블 TO 사용자;

ex) grant update on 학생 to user;

user의 학생테이블에 대한 udpate권한 부여

 

- REVOKE (사용자로부터 테이블에 대한 권한 회수 )

REVOKE 권한 ON 테이블 FROM 사용자;

ex) revoke update on 학생 from user;

 

Ch2 응용 SQL 작성하기 

1. 집계성 SQL

데이터 분석 함수임

종류

• 집계 함수 : 여러행 또는 테이블 전체 행으로부터 하나의 결괏값 반환
• 그룹 함수 : 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터 산출
• 윈도 함수 : 온라인 분석 처리용

- 집계 함수

SELECT 컬럼1, 컬럼2, ,, 집계함수

FROM 테이블명

WHERE 조건

GROUP BY 컬럼1, 컬럼2,,,

HAVING BY 조건식

 

null값을 가지는 row는 제외 

alias사용X 

where먼저 실행되고 그담에 groupby

 

 

- 그룹함수 

Ch3 절차형 SQL 활용하기 

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