정처기 실기 7과목 SQL 응용

1 데이터베이스 기본

1) 트랜잭션

[트랜잭션의 개념]
트랜잭션 : 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

[트랜잭션의 특성]
원자성 : 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질
일관성: 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 후의 상태가 같아야 하는 성질
고립성: 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질
영속성: 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

[트랜잭션의 상태 변화]
활동 상태 - 부분 완료 상태 - 완료 상태 - 실패 상태 - 철회 상태

[트랜잭션 제어]
커밋: 트랜잭션을 메모리에 영구적으로 저장하는 명령어
롤백: 트랜잭션 내역을 저장 무효화시키는 명령어
체크포인트: 롤백을 위한 시점을 지정하는 명령어

[병행 제어]
병행 제어: 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법(일관성 주요 기법)

로킹: 하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호배제기능을 제공하는 기법
- 로킹 단위가 작아지면 데이터베이스 공유도 증가, 로킹 오버헤드 증가
낙관적 검증: 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서: 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중버전 동시성 제어: 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
2PC: 데이터베이스 동시성 제어 기술 중 하나로, 여러 개의 분산 데이터베이스 시스템에서 트랜잭션의 일관성을 유지하기 위한 기법

[회복 기법]
REDO: 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업하는 기법
UNDO: 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작은 있지만 완료에 대한 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법

로그 기반 회복 기법: 지연 갱신 회복, 즉각 갱신 회복 기법이 있음
체크 포인트 회복 기법: 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법
그림자 페이징 회복 기법: 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

2) DDL(Data Definition Language)

[DDL의 대상]
스키마: 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조

- 외부 스키마: 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조
- 개념 스키마: 데이터베이스의 전체적인 논리적 구조
- 논리 스키마: 물리적 저장 장치의 관점에서 보는 데이터베이스 구조

테이블: 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체

- 튜플/행: 튜플은 테이블에서 같은 값을 가질 수 없음
- 속성/열: 열의 개수를 Degree(차수)라고 함
- Cardinality: number of Tuple(행)
- Degree: number of attribute(열)
- Domain: 하나의 속성이 취할 수 있는 같은 타입의 원자값 들의 집합

뷰: 논리 테이블로서 사용자에게는 테이블과 동일

- 장점: 논리적 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근제어) 제공
- 단점: 뷰 자체 인덱스 불가, 뷰 변경 불가, 데이터 변경 제약 존재

인덱스: 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조

- 기본 키 column은 자동으로 인덱스가 생성됨
- 연원일, 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
- 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있음

[DDL 명령어]
CREATE: DB 오브젝트 생성
ALTER: DB 오브젝트 변경
DROP: DB 오브젝트 삭제
TRUNCATE: DB 오브젝트 내용 삭제

[TABLE 관련 DDL]
CREATE TABLE

CREATE TABLE 사원(
 사번 VARCHAR(10) NOT NULL,
 부서번호 VARCHAR(20),
 생년월일 DATE NOT NULL,
 성별 CHAR(1),
 PRIMARY KEY(사번),
 FOREIGN KEY(부서번호) REFERENCES 부서(부서코드),
 CONSTRAINT 성별제약 CHECK 성별 IN('M', 'F')
 );

 

ALTER TABLE

#컬럼 추가
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE; 

#컬럼 수정
ALTER TABLE 사원 ALTER 이름 VARCHAR(30) NOT NULL;

#컬럼 삭제
ALTER TABLE 사원 DROP COLUMN 생년월일;

 

DROP TABLE

#참조하는 테이블까지 삭제
DROP TABLE 사원 CASCADE;

#참조 중이면 삭제 X
DROP TABLE 사원 RESTRICT;

 

TRUNCATE TABLE

#사원 테이블 내의 모든 데이터 삭제
TRUNCATE TABLE 사원;

 

[VIEW 관련 DDL]

CREATE VIEW

CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
 FROM 사원
WHERE 성별 ='M';

 

DROP VIEW

DROP VIEW 뷰이름;

 

[INDEX 관련 DDL]

CREATE INDEX

#사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 생성
CREATE INDEX 사번인덱스 ON 사원(사번);

 

ALTER INDEX

#사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 수정
ALTER INDEX 사번인덱스 ON 사원(사번);

 

DROP INDEX

#사번인덱스라는 인덱스 명을 가지고 있는 인덱스를 삭제
DROP INDEX 사번인덱스;

 

(3) DML(Data Manipulation Language)

[데이터 조작어의 개념]
DML: 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

[DML 명령어]
SELECT: 테이블 내 칼럼에 저장된 데이터를 조회
INSERT: 테이블 내 칼럼에 데이터를 추가
UPDATE: 테이블 내 칼럼에 저장된 데이터를 수정
DELETE: 테이블 내 칼럼에 저장된 데이터를 삭제

[SELECT 명령어]
- 데이터의 내용을 조회할 때 사용하는 명령어

SELECT */DISTINCT 속성명1
 FROM 테이블명,
WHERE 조건
GROUP BY 속성명1
HAVING 그룹조건
ORDER BY 속성 [ASC | DESC];

 

[INSERT 명령어]
- 데이터의 내용을 삽입할 때 사용하는 명령어

INSERT INTO 학생(학번, 성명, 학년)
VALUES (6677, '장길산', 3);

[UPDATE 명령어]
- 데이터의 내용을 변경할 때 사용하는 명령어

UPDATE 학생
	SET 주소='인천'
  WHERE 이름='장길산';

[DELETE 명령어]
- 데이터의 내용을 삭제할 때 사용하는 명령어(테이블 구조는 남아있음)

DELETE FROM 학생
WHERE 이름='장길산';

 

(4) DML 상세

[조인]
- 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법

교차조인: 조인 조건이 없는 모든 데이터 조합을 추출하는 조인

SELECT A.column1, A.column2, ...
	   B.column1, B.column2, ...
 FROM table1 A CROSS JOIN table2 B;

 

세타조인: 두 테이블에서 조건을 만족하는 튜플만 반환하는 조인

동등 조인: 두 테이블의 값이 정확히 같을 때만 데이터를 가져오는 조인

자연 조인: 동등 조인에서 조인에 참여한 속성이 두 번 나오지 않도록 중복된 속성은 하나만 남긴 결과를 반환하는 조인

외부 조인: 자연 조인 시 조인에 실패한 튜플을 모두 보여주되 값이 없는 속성은 NULL 값을 채워서 반환하는 조인
 - 왼쪽 외부 조인: 왼쪽테이블 모두 X 오른쪽 테이블
 - 오른쪽 외부 조인: 왼쪽 테이블 X 오른쪽 테이블 모두
 - 완전 외부 조인: 양쪽의 모든 데이터를 추출하는 조인

SELECT
 FROM table1 LEFT/RIGHT/FULL JOIN table2
 	ON 조인조건
WHERE 검색조건;

세미 조인: 자연 조인을 한 후 두 테이블 중 한쪽 테이블의 결과만 반환하는 조인

셀프 조인: 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

[서브쿼리]
- SQL 문 안에 포함된 또 다른 SQL 문

[집합 연산자]
- 여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용

UNION: 중복 행이 제거된 쿼리 결과를 반환하는 집합 연산자
UNION ALL: 중복 행이 제거되지 않은 쿼리 결과를 반환하는 집합 연산자
INTERSECT: 두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자
MINUS: 차집합

(5) DCL(Data Control Language)

[데이터 제어어의 개념]
- 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위한 관리자가 사용하는 제어용 언어

GRANT: 관리자가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
REVOKE: 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어

GRANT UPDATE ON 학생 TO 장길산;
REVOKE UPDATE ON 학생 FROM 장길산;

2 응용 SQL

(1) 다중 행 연산자

[다중 행 연산자의 개념]
- 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중행 서브쿼리에서 사용되는 연산자

[다중 행 연산자 종류]
IN: 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
ANY: 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참
ALL: 값을 서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야만 참
EXISTS: 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

(2) 집계 함수

[집계 함수의 개념]
- 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수

[집계 함수 구문]

SELECT
 FROM table
WHERE condition
GROUP BY column1, column2,...
HAVING 조건식(집계함수 포함)

 

[집계 함수의 종류]
COUNT: 복수 행의 줄 수를 반환하는 함수
SUM:
AVG:
MAX:
MIN:
STDDEV:
VARIANCE:

 

3 SQL 활용 및 최적화

(1) 절차형 SQL

[절차형 SQL 개념]
- 일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

[절차형 SQL 종류]
프로시저: 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
사용자 정의 함수: 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거: 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

(2) SQL 최적화

튜닝: 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

옵티마이저: SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진
- 규칙기반/비용기반 옵티마이저