[정보처리기사 실기 요약정리] 6. SQL 응용

데이터베이스 기본

트랜잭션

 

1) 트랜잭션

 

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

 

특성

• 원자성 (Atomicity): 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 - Commit / Rollback
• 일관성 (Consistency): 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 완료 후의 상태가 같아야 하는 성질 - 무결성 제약조건
• 고립성 (Isolation): 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질
  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable
• 영속성 (Durability): 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

---

트랜잭션 제어 ( TCL)

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

---

병행 제어

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

 

병행 제어 미보장 시 문제점

• 갱신 손실
• 현황 파악오류
• 모순성
• 연쇄복귀

병행 제어 기법의 종류

• 로킹 (Locking): 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
• 낙관적 검증 (Optimistic Validation): 어떠한 검증도 수행하지 않고 일단 수행하고, 종료 시 검증을 수행하여 반영하는 기법
• 타임 스탬프 순서 (Time Stamp Ordering): 실행 시작하기 전에 타임 스탬프 부여하여 부여된 시간에 따라 작업 수행
• 다중버전 동시성 제어( Mutli Version Concurrency Control): 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전 선택해 접근

---

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

개념: 트랜잭션 수행 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업

 

회복 기법 종류

• 로그 기반 회복 기법
  • 지연 갱신 회복 기법
  • 즉각 갱신 회복 기법
• 체크 포인트 회복 기법
• 그림자 페이징 회복 기법: 복제본 이용해 복구

---

2) DDL 데이터 정의어

 

개념: 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부른다

 

DDL 대상

• 도메인
• 스키마
  • 외부 스키마: 사용자나 개발자 관점에서 필요로 하는 데이터베이스의 논리적 구조
  • 개념 스키마: 전체적인 논리적 구조 (개체 간의 관계, 제약조건, 접근권한, 무결성, 보안)
  • 내부 스키마: 물리적 저장장치의 관점에서 보는 데이터베이스 구조
• 테이블
• 뷰
• 인덱스

---

인덱스

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

 

인덱스의 종류

• 순서 인덱스: 데이터 정렬된 순서로 생성 , B-Tree 알고리즘 활용
• 해시 인덱스: 해시 함수에 의해 직접 데이터에 키 값으로 접근
• 비트맵 인덱스: 각 컬럼에 적은 개수 값이 저장된 경우 선택
• 함수기반 인덱스: 수식이나 함수 적용
• 단일 인덱스: 하나의 컬럼으로만 구성한 인덱스
• 결합 인덱스: 두 개 이상의 컬럼으로 구성한 인덱스
• 클러스터드 인덱스: 기본 킼 기준으로 레코드로 묶어서 저장하는 인덱스

 

인덱스 스캔 방식

• 인덱스 범위 스캔
• 인덱스 전체 스캔
• 인덱스 단일 스캔
• 인덱스 생략 스캔

---

DDL 명령어

• 생성 - CREATE
• 수정 -  ALTER
• 삭제 
  • DROP
  • TRUNCATE

 

CREATE TABLE 제약조건

• PRIMARY KEY: 테이블 기본 키
• FOREIGN KEY: 외래 키
• UNIQUE: 테이블 내에서 얻은 유일한 값 갖도록 하는 제약조건
• NOT NULL: 해당 컬럼은 null 값을 포함하지 않도록
• CHECK: 개발자가 정의하는 제약조건
• DEFAULT: insert 할 때 컬럼의 값을 넣지 않는 경우 기본값으로 설정해주는 제약조건

AlTER TABLE

• 컬럼 추가 - ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 제약조건 ; 
• 컬럼 수정 - ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 제약조건;
• 컬럼 삭제 - ALTER TALBLE 테이블명 DROP 컬럼명;

 

DROP TABLE

• DROP TABLE 테이블명;
• 옵션
  • CASCADE: 참조하는 테이블까지 연쇄적으로 제거
  • RESTRICT: 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

TRUNCATE TABLE

• TRUNCATE TABLE 테이블명; (테이블 내의 데이터들을 삭제)

 

---

3) DML (데이터 조작어)

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어이다. (CRUD)

 

유형

• SELECT
• INSERT
• UPDATE
• DELETE

 

SELECT 명령어

• SELECT -> 기본이 ALL 뒤에 DISTINCT 붙이면 중복 제거
• FROM -> 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
• WHERE -> 검색할 조건을 기술
  • 비교: = , !=, <, >
  • 범위: between -> 사용법:  컬럼 between 값1 and 값2 은 다음과 같은 의미이다  컬럼 >= 값1 and 컬럼 <= 값2
  • 집합: in, not in -> 사용법:  컬럼 in (값1, 값2)  컬럼이 in 안에 포함된 경우의 데이터 조회 not in은 반대이다.
  • 패턴: like ->  사용법: 컬럼 like '패턴'
    
    • % : 0개 이상의 문자열과 일치
    • [ ] : 1개의 문자와 일치
    • [^] : 1개의 문자와 불일치
    • _ : 특정 위치의 1개의 문자와 일치
  • NULL: is null, is not null -> 사용법: 컬럼 is null 은 컬럼이 null인 데이터 조회
  • 복합조건: and, or, not -> 사용법: 조건1 and 조건 2 / 조건1 or 조건 2 / not 조건
• GROUP BY -> 속성값을 그룹으로 분류하고자 할 때 사용
• HAVING -> group by 에 의해 분류한 후 그룹에 대한 조건 지정
• ORDER BY -> 속성값을 정렬하고자 할 때 사용 (ASC: 오름차순 DESC: 내림차순)

 

조인 (Join)

개념: 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다

 

유형

• 내부 조인 (inner join) : 공통 존재 컬럼의 값이 같은 경우 추출
• 외부 조인 (outer join)
  • left outer join: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
  • right outer join : 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
  • full outer join : 양쪽의 모든 데이터 추출
• 교차 조인 (cross join) : 조인 조건이 없는 모든 데이터 조합 추출
• 셀프 조인 (self join) : 자기 자신에게 별칭 지정한 후 다시 조인

사용법 : from 테이블 1 A join 테이블 2 B on 조인조건

 

서브쿼리

개념: sql 문 안에 포함된 또 다른 sql 문이다

 

유형

• from 절 서브쿼리
• where 절 서브쿼리

집합 연산자

• UNION: 중복 행이 제거된 쿼리 결과를 반환
• UNION ALL: 중복 행이 제거되지 않은 쿼리 결과 반환
• INTERSECT: 두 쿼리 결과에 공통적으로 존재하는 결과 반환 
• MINUS: 첫 쿼리에 있고 두 번째 쿼리에는 없는 결과 반환

 

---

INSERT 명령어 (데이터 삽입)

 

예시 

insert into 테이블명(속석명 1, .... ) values (데이터 1, ...);

---

UPDATE 명령어 (데이터 변경)

 

예시

update 테이블명 set 속석명 = 데이터 where 조건;

---

DELETE 명령어 (데이터 삭제)

 

예시

delete from 테이블명 where 조건;

 

---

4) DCL (데이터 제어어)

개념: 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 ㄱ관리자가 사용하는 제어용 언어이다

 

유형

• GRANT: 사용 권한 부여 (grant 권한 on 테이블 to 사용자)
• REVOKE: 사용 권한 취소 ( revoke 권한 on 테이블 from 사용자)

---

응용 SQL 작성하기

집계성 SQL 작성하기

1) 데이터 분석 함수 종류

• 집계 함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
• 그룹 함수: 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터 산출하는 함수
• 윈도 함수: 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 sql에 추가된 기능

 

집계 함수 종류

• COUNT
• SUM
• AVG
• MAX
• MIN
• STDDEV (표준편차)
• VARIAN (분산)