데이터베이스 구축(SQL 활용)

1. 기본 SQL 작성

(1)데이터 정의어DDL Data Definition Language : 데이터를 정의하는 언어로서 ‘데이터를 담는 그릇을 정의하는 언어’ (도인스뷰테 : ①도메인 ②인덱스 ③스키마 ④뷰 ⑤테이블) -도메인 : 하나의 속성을 가질 수 있는 원자값들의 집합 -인덱스 : 검색을 빠르게 하기 위한 데이터 구조 (목차, 찾아보기) -스키마 : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 (뼈대) 1. 외부 스키마 External Schema : 사용자나 개발자 관점 2. 개념 스키마 Conceptual Schema : 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의 3. 내부 스키마 Internal Schema : 물리적 저장장치의 관점 (저장은 내) -뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블 (Derived Table) -테이블 : 테이블 저장 공간 (2)데이터 정의어 DDL의 명령어 (크알드트) ①Create생성(정의) ②Alter변경(추가,수정,삭제) : 컬럼 추가 ADD, 컬럼 수정 MODIFY, 컬럼 삭제DROP, 컬럼명 수정RENAME COLUMN ③Drop삭제 : 데이터베이스 오브젝트 삭제(테이블 전체 없어진다), ㄴCASCADE 참조하는 테이블까지 연쇄제거, RESTRICT 테이블을 참조 중이면 제거 하지 않는다. ④Truncate내용삭제) : 데이터베이스 오브젝트 내용 삭제 (테이블 형태는 있고, 내용만 삭제) (3)트랜잭션(Transaction) :일련의 연산 집합으로 데이터베이스의 상태를 변환시키기 위하여 논리적 기능을 수행하는 작업 단위 (ACID) 프로시저 : 데이터베이스에 어떤 쿼리를 저장을 해놓고 그 쿼리를 일련의 집합을 실행시키는 게 프로시저. ①Atomicity 원자성 : A가 B에게 100원 계좌이체 도중 정전이 되었다. 100원을 성공 또는 실패, 둘 중 하나가 되어야 한다. ②Consistency 일관성 : A의 계자는 -100원, B의 계좌는 +100원인 상태가 일관된 상태가 유지가 돼야 한다. ③Isolation 격리성 : A가 B에게 계좌 이체를 하는 동안, 동시에 C가 B계좌로 이체 시 C는 끼어들면 안 된다. ④Durability 영속성 : 이체된 상태가 영속적, 지속적으로 데이터베이스에 남아야 된다. (4)트랜잭션 연산 ①커밋Commot : 데이터베이스 일관성 있는 상태에 있거나 하나의 트랜잭션이 끝났을 때 사용하는 연산 ②롤백Rollback : 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작, 연산 취소하는 연산 (5)병행제어 목적 (Concurrency Control) :  다수의 사용자가 여러 트랜잭션 수행 시, 데이터베이스 일관성 유지 (최소화X) ①데이터베이스 공유를 최대화 ②시스템의 활용도 최대화 ③데이터베이스의 일관성 유지 ④사용자에 대한 응답시간 최소화 (6)병행제어 미보장 시 문제점 (갱연모연) ①갱신손실:덮어쓰기 오류 ②현황파악오류 : 다른 트랜잭션이 참조 오류 ③모순성 : 동시에 실행되어 일관성이 결여 ④연쇄복귀 (7)병향제어 기법의 종류 (로낙타다) ①로킹Locking : 테이블이나 특정 컬럼, 일부분을 Lock을 설정 (락 많다. 락이 적다에 따라 판단) ②낙관적 검증 : 어떠한 검증도 수행하지 않고 일단, 트랜잭션을 수행하고 트랜잭션 종료 시 검증을 수행 ③타임 스탬프 순서 Time Stamp : 트랜잭션 실행 전에 타임스탬프(Time Stamp)부여하여 부여된 시간에 따라 트랜잭션 작업 수행 ④다중버전 동시성 제어 MVCC : (8)회복기법 : 트랜잭션 수행하는 도중 장애로 인해 손상된 데이터베이스 손상 되기 이전의 정상적인 상태로 복구 (로체그) ①로그기반 회복 기법 : 로그 파일에 로그를 남긴 다음에 그 로그를 읽어 들여서 회복하는 기법 1. 지연 갱신 회복 기법 : 트랜잭션 완료 전까지 데이터에 기록하지 않는 기법 2. 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법 ②체크 포인트 회복 : 검사점 이후에 처리된 프랜잭션에 대해서만 장배 발생 이전의 상태로 복원 ③그림자 페이징 회복 기법: 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이용 (9)테이블 용어

2. 고급 SQL 작성

(1)뷰 View : 뷰는 논리적 테이블로서 사용자에게 (사용자 관점)에서 테이블과 동일하다. ①뷰를 사용하는 주된 이유는 단순 질의어(쿼리)를 사용 : SELECT * FROM 뷰이름; ②뷰 삭제/변경 DROP VIEW 뷰 이름; ③뷰 생성 CREATE VIEW 뷰 이름 컬럼 목록 AS 데이터 조회 쿼리; (2)인덱스 Index ①인덱스 생성 CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명; ②인덱스 삭제 DROP INDEX 인덱스명; ③인덱스 변경 ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명; (3)집합 연산자 (유유인마) ①UNION : 중복 행이 제거된 쿼리 (중복 레코드 제외) ②UNION ALL : 중복 행이 제거되지 않은 쿼리 (중복 레코드 허용) ③INTERSECT(교차하다) : 두 쿼리 결과에 공통적으로 존재하는 집합 (중복 레코드만 포함) 교집합 ④MINUS : 첫 쿼리에 있고 두 번째 쿼리에는 없는 집합 (비교 레코드 제외) 차집합 (4)조인 (Join) : 두개 이상의 테이블을 연결하여 데이터를 검색 (정규화 : 데이터를 저장할 때 중복 저장되지 않기 위해 데이터를 나눈다. 다시 원래 상태로 돌리기 위해 JOIN사용) ①논리적 조인 유형 (내외교셀) 1.내부 조인Inner Join : 공통 컬럼의 값 추출 2.외부 조인Outer Join (왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인) 3.교차 조인Cross Join : 조인 조건이 없는 경우 4.셀프 조인Self Join : 자기 자신에게 별칭 지정한 후 조인 ②물리적 조인 유형 네소해 1.중첩 반복 조인 Nested-Loop Join : 하나의 집합을 기준으로 순차적으로 상대방 ROW를 결합하여 원하는 결과 조합 2.정렬 합병 조인 Sort-Merge Join : 임의 접근(Random Access)을 줄이기 위한 경우 3.해시조인 Hash Join : 해싱 함수를 활용하여 테이블 간 조인을 수행하는 방식 (5)서브쿼리 : SQL문 안에 포함된 또 다른 SQL문을 의미한다. ①SELECT절 서브쿼리 ②FROM절 서브쿼리 ③WHERE절 서브쿼리