Skill Tree🌲/SQL

SQL 활용 핵심요약

체다치즈 2022. 4. 5. 15:17
데이터베이스
데이터베이스 개념

 

  • 데이터베이스 정의
    • 데이터베이스는 어느 한 조직의 다양한 응용프로그램들이 공동으로 사용하는 데이터들을 통합하여 저장한 운영 데이터의 집합
    • 데이터베이스는 공용 데이터(Shared Data)임
    • 데이터베이스는 통합된 데이터(Integrated Data)임
    • 데이터베이스는 저장된 데이터(Stored Data)임
    • 데이터베이스의 데이터는 운영 데이터(Operational Data)임
  • 데이터베이스의 특징
    • 실시간 접근성 (Real-time Accessibility)
    • 계속적인 변화 (Continuous Evolution)
    • 동시 공용 (Concurrent Sharing)
    • 내용에 의한 참조 (Content References)
데이터베이스 관리 시스템

 

  • 데이터베이스 관리 시스템의 정의
    • 데이터 정의 기능 : 여러 사용자의 데이터를 통합하여 저장하고 공유할 수 있도록 데이터 모델에 따라서 정의하는 기능
    • 데이터 조작 기능 : 사용자와 데이터베이스 간의 의사소통하는 기능으로, 데이터베이스의 접근 및 조작(삽입, 삭제, 변경 및 검색) 기능을 제공
    • 데이터 제어 기능 : 데이터 일관성(Consistency)와 무결성(Integrity), 보안(Security)를 유지하는 기능
  • 데이터베이스 관리 시스템의 역사
    • 1세대 DBMS
      • IDS(Integrated Data Store) : 최초의 범용 목적의 DBMS
      • IMS(Information Management System) DBMS
    • 2세대 DBMS
      • Relational Data Model, SQL : IBM의 E.F.Codd
      • Commercial DBMS
      • Oracle, DB2, Ingress, Sybase, Infomix
    • 3세대 DBMS
      • 데이터의 복잡성 증가
      • 새로운 데이터 모델의 대두
      • 2세대 DBMS + 3세대 DBMS ORDBMS ➡️ 객체 관계형 데이터베이스 관리 시스템
관계형 데이터 모델
관계형 데이터 모델의 구조와 연산

 

  1. 데이터 모델 ➡️ 모델 D = <S, O, C>
    • 구조 (Structure)
      • 데이터의 구조
      • 정적 성질, 개체 타입과 이들 간의 관계가 명세함
    • 연산 (Operation)
      • 데이터의 동적 성질
      • 개체 인스턴스를 처리하는 작업이 명세함
      • 데이터 조작 기법
    • 제약 조건 (Constraint)
      • 데이터의 논리적 제약
      • 구조로부터 파생, 의미적 제약
      • 데이터 조작의 한계를 표현한 규정
  2. 관계형 데이터 모델의 구조와 연산
    • 관계형 데이터 모델의 구조
      • 릴레이션
      • 2차원 테이블 형태
      • 테이블의 행(Row) → 튜플
      • 테이블의 열(Column) → 속성(Attribute)
    • 일반 집합 연산자
      • 합집합 (UNION, ∪)
      • 교집합 (INTERSECT, ∩)
      • 차집합 (DIFFERENCE, −)
      • 카티션 프로덕트 (CARTESIAN PRODUCT, ⨉)
    • 순수관계 연산자
      • 셀렉트 (SELECT, σ)
      • 프로젝트 (PROJECT, Π)
      • 조인 (JOIN, ⨝)
      • 디비전 (DIVISION, ÷)
관계형 데이터 모델의 제약조건

 

  1. 키
    • 하나의 테이블 내에서 각 튜플의 유일하게 식별할 수 있는 속성 (Attribute)들의 집합
    • 하나의 자물쇠를 열 수 있는 열쇠(키)는 오직 하나다.
    • 후보키 (Candidate Key)
      • 한 릴레이션 R (A1, … , An)에 대한 속성의 집합
      • 유일성, 최소성
    • 수퍼키 (Super Key)
      • 유일성을 만족하는 속성 집합
      • 최소성을 만족하지 않아도 됨
    • 기본키 (Primary Key)
      • 하나의 릴레이션에는 후보키가 여러 개 있을 수 있음
      • 여러 개의 후보키 중 DBA가 지정한 하나의 키
    • 대체키 (Alternative Key)
      • 후보키 중 기본키를 제외한 나머지 후보키
  2. 개체 무결성, 참조 무결성, 도메인 무결성
    • 개체 무결성
      • 의미 : 서로 다른 두 튜플은 같을 수 없음
      • 정의 : 기본키 값은 언제고 어느 때고 NULL값일 수 없음
    • 참조 무결성
      • 외래키 값은 피참조 릴레이션의 기본키 값이거나 NULL 값임
      • 추가 지정을 통해 NULL을 가질 수 없다고 제약을 걸을 수 있음
    • 도메인 무결성
      • 속성(Attribute) 값은 해당 속성 도메인에 속한 값들 중 하나이어야 함
      • DBMS는 데이터베이스의 상태 변화(삽입, 갱신, 삭제)에도 항상 무결성 제약조건을 검사하고 유지시킴
SQL의 개념과 T-SQL
SQL의 개념
  • SQL의 역사
    • SEQUEL(Structured English Query Language)
      • 1974년, IBM San Jose Lab(현재 IBM Almaden 연구소)
      • 최초의 관계형 데이터베이스 관리 시스템 프로토타입인 SystemR을 위한 데이터베이스 언어로 개발됨
    • SQL
      • 1986년 ANSI 에서 관계형 데이터베이스 표준언어로 인증
  • SQL의 특징
    • SQL이란?
      • 종합 데이터베이스 언어
      • 데이터 정의(DDL), 조작(DML), 제어(DCL)
      • 무엇(What)을 표시하며 어떻게(How)는 표시하지 않음
      • 어떻게는 DBMS가 처리함
  • SQL의 기본 구문
    • DDL문 : 데이터 정의문

테이블 생성 : CREATE문

CREATE TABLE 테이블명
 (속성명 속성타입 [제약조건],
 속성명 속성타입,
 ...
)

테이블 삭제 : DROP문

DROP TABLE 테이블명

테이블 구조 변경 : ALTER문

 

▶ 속성 추가

ALTER TABLE 테이블명(ADD 속성명 속성타입)

 

▶ 속성 제거

ALTER TABLE 테이블명 (DROP 속성명 속성타입)

 

▶ 속성 타입 변경

ALTER TABLE 테이블명 (ALTER 속성명 속성타입)

 

T-SQL

 

  • MS-SQL(MS-SQL Editions)
    • MS-SQL Server : Microsoft에서 제공하는 데이터베이스 관리 시스템
    • Edition의 종류
      • Expree, Workgrop, Standard, Enterprise
  • MS-SQL의 설치와 구동
    • MS-SQL구동 
      • Database
System Database
  master : 시스템 관리용
  model : 새 데이터베이스 생성을 위한 틀
  msdb : SQL Sever agent 를 위한 데이터베이스
  tempdb : 질의 임시결과 유지를 위한 테이블

User Database
  SSMS 이나 T-SQL(SQL of MS SQL)을 이용해서 생성
데이터 구조 생성과 변경
테이블 생성

 

  • T-SQL을 사용하는 방법
USE master
CREATE DATABASE test01

 

더보기

master DB를 사용해서, DB이름이 test01인 사용자 DB를 생성한다.

  • DB이름 : test01
  • 사용자 DB를 생성하려면, master DB를 사용해야함

 

  • SSMS를 이용하는 방법 (GUI, 스튜디오로 만든다.)
    • 논리적 DB에 물리적 파일로 저장된다.
데이터 파일 : *.mdf (for data), *.ndf (for large DB - optional)
로그 파일 : *.ldf (for log)

 

 

테이블 변경

 

  1. add, alter, drop 컬림
    • ADD column : 속성 추가
      • datetime형으로 pdate속성을 memberTEST테이블에 추가함
    • ALTER column : 속성 추가
      • name속성의 타입을 varchar(30)으로 변경함
    • DROP column : 속성 제거
  2. drop테이블과 truncate테이블

▶ 테이블 지우기

DROP TABLE table_name

 

▶ 테이블의 모든 내용을 지우기, 단 테이블은 남기기

TRUNCATE TABLE table_name
제약조건
제약조건

 

  • 데이터무결성
    • NOT NULL : NULL값 허용 불가
    • UNIQUE : 하나의 테이블 내에서 한 번만 나옴 / 주로 대체키 설정 시 사용됨
    • PRIMARY KEY : 기본키(UNIQUE + NOTNULL)
    • FOREIGN KEY : 외래키
    • CHECK : 도메인 무결성
  • 제약조건의 설정

▶ 테이블을 만들 때, 속성에 제약조건 지정하기

CREATE TABLE 테이블명
 (속성명 속성타입 [[제약조건명] 제약조건],
 속성명 속성타입,
 ...
)

 

▶ 참조 무결성 제약조건 : 외래키 값은 다른 테이블의 기본키 값들 중에 하나여야함

속성명 [CONSTRANITS 제약조건명]
REFERENCE 참조테이블명(속성명)

 

▶ Check 제약조건 : 도메인 무결성 제약조건 → 입력 값의 제한

속성명 [CONSTRAINTS 제약조건명]
REFERENCE 참조테이블명(속성명)

 

▶ 테이블 수준 제약조건 : 속성 단위로 제약조건 설정은 표현에 있어서 제약이 따름

CREATE TABLE 테이블명(
 속성명 속성타입
 ...
 [CONSTRAINT 제약조건명] 제약조건(속성명)
)

 

제약조건 변경

 

  • 제약조건의 추가 및 제거
    • 테이블을 생성한 후에 제약조건을 추가하거나 제거할 필요성이 있음
    • 제약조건도 테이블의 구조 정보에 속함으로 ALTER TABLE을 이용함
      • 추가 : ADD CONSTRAINT
      • 제거 : DROP CONSTRAINT
    • 제약조건 이름을 지정해 두어야 추가나 제거가 쉬움

▶ 추가

ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건 (속성명)

 

▶ 제거

ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명
데이터 검색
간단한 데이터 검색

 

  • 제약조건의 설정
    • *
      • 모든 속성명을 쓰기 힘들 경우 사용
      • SELECT절에서 *는 모든 속성이란 의미
    • AS 키워드
      • AS 키워드로 속성명을 부여함
    • 무조건 검색
SELECT 속성명1, 속성명2, ...
FROM 테이블명

 

  • 간단한 조건 검색

     1. WHERE 절

     2. 비교 연산자 

= : 같다
!= , <> : 같지 않다
< (작다), > (크다), <= (작거나 같다), >= (크거나 같다)

     

     3. 논리 연산자

WHERE절에서 여러 개의 조건을 결합할 경우
X AND Y : X, Y가 참일 때 참을 반환
X OR Y : X나 Y가 참일 때 참을 반환
NOT X : X가 거짓을 때 참을 반환

 

 

  • DISTINCT
    • SQL은 Bag을 기반으로 함
    • 그래서 중복된 것들도 다 제시된다.
    • 중복된 것들을 제거하고 한 번만 나오게 하기 위해 DISTINCT를 사용한다.

 

복잡한 데이터 검색

 

  • BETWEEN, IN, IS NULL
    • BETWEEN a AND b : 검색 조건의 상한과 하한을 지정함
    • IN(a, b, c, ...) : 속성값이 a, b, c, ... 중 하나라도 일치하면 참
    • IN NULL : NULL 값은 어떤 비교를 하든 거짓임
  • 문자열 검색
    • LIKE 연산자
      • 컬럼에 저장된 문자열 중에서 LIKE 연산자에서 지정한 문자 패턴과 부분적으로 일치하면 참이 되는 연산자
    • ORDER BY절
      • 질의문의 결과는 테이블에 입력된 순서대로 출력
      • 데이터의 출력 순서를 특정 속성값을 기준으로 오름차순 또는 내림차순으로 정렬해야 하는 경우가 자주 발생함
ORDER BY {column_name} [ASC / DESC]

▶ ASC : 오름차순으로, 기본값 (생략가능)

▶ DESC : 내림차순, 생략불가능

데이터 삽입과 변경
INSERT절
  • 다양한 INSERT구문
    • 단일행 입력
      - 한번에 하나의 튜플을 테이블에 입력하는 방법
      INSERT INTO 테이블명 [(속성명, ... , 속성명)]
      VALUES (값, ..., 값)
    • NULL의 입력
      - 데이터를 입력하는 시점에서 해당 속성값을 모르거나, 미확정일 때 사용함
      - NOT NULL 조건이 지정된 경우 입력이 불가능함
    • 서브 쿼리를 이용한 데이터 삽입
      - 한번에 여러 튜플을 넣을 수 있음
      INSERT INTO 테이블명
      subquery
    • 질의 결과 테이블 만들기
      - 질의 결과로 만든 테이블은 기존 테이블의 속성명과 타입을 그대로 적용함
      SELECT 컬럼리스트 INTO 대상테이블
      FROM 테이블
      WHERE 조건
      - NOT NULL조건을 그대로 적용함
      - 다른 제약조건은 적용되지 않음
      - 오라클의 경우 구문이 다소 다름
      CREATE TABLE 테이블명
      AS
      　　SELECT컬럼리스트
      　　FROM 테이블
      　　[WHERE 조건]
    • 테이블 구조의복사
      - 상황에 따라서 기존 테이블과 동일한 구조를 지니는 테이블을 생성할 필요가 있음
    • 테이블의 구조 검색문
      - 오라클　　DESCRIBE[DESC] 테이블명
      - MS SQL　　EXEC sp_help 테이블명
    • VALUES를 이용한 다중행 입력
      - MS-SQL 2008 부터는 서브 쿼리가 아닌 VALUES를 이용해서도 다중행 삽입이 가능함
      INSERT INTO table1
      VALUES (속성값들), (속성값들)

 

UPDATE구문

 

  • 데이터 수정
    • 데이터 수정 : UPDATE 문은 테이블에 저장된 데이터를 수정하기 위한 데이터 조작어임
    • 서브쿼리를 이용한 데이터 수정
      - UPDATE문의 SET절에서 서브 쿼리를 이용함
      - 다른 테이블에 저장된 데이터를 검색하여 한번에 여러 속성값을 수정할 수 있음
      - SET절의 속성명의 서브 쿼리의 속성명과 달라도 됨
    • 복수 속성값 변경
      - SET절에 (속성명 = 값), (속성명 = 값), ... 으로 작성
    • 데이터 삭제
      - DELETE 문의 테이블에 저장된 데이터 삭제를 위한 조작어
      DELETE [FROM] 테이블
      [WHERE 조건]
    • 서브쿼리를 이용한 데이터 삭제
      - WHERE절에서 서브 쿼리를 이용함
      - 다른 테이블에 저장된 데이터를 검색하여 한번에 여러 행을 삭제함
      - WHERE절의 속성명은 서브 커리의 속성명과 달라도 됨
트랜잭션
트랜잭션(Transaction)

 

  • 트랜잭션의 개념
    • 트랜잭션(Transaction) : 논리적인 일의 단위
    • 기본 설정 : 하나의 SQL은 하나의 트랜잭션임
      - 여러 개의 SQL문들이 합쳐서 하나의 트랜잭션이 될 수도 있음
    • 트랜잭션의 활용
      - 동시성 제어 : 여러 사용자나 여러 응용 프로그램들이 동시에 수행되어도 서로 간섭하지 못하도록 보장함
      - 회복 : 데이터베이스를 갱신하는 도중에 시스템 고장 시에도 데이터베이스의 일관성을 유지함
  • 트랜잭션의 특성
    • 원자성 (Atomicity)
      - 한 트랜잭션 내의 모든 연산들이 완전히 수행되거나 전혀 수행되지 않음(All or Nothing)
    • 일관성 (Consistency)
      - 어떤 트랜잭션이 수행되기 전에 데이터베이스가 일관된 상태를 가졌다면 트랜잭션이 수행된 후에 데이터베이스는 또 다른 일관된 상태를 가짐
    • 격리성 (Isolation)  또는 고립성
      - 한 트랜잭션이 데이터를 갱신하는 동안 이 트랜잭션이 완료되기 전에는 갱신 중인 데이터를 다른 트랜잭션들이 접근하지 못하도록 해야 함
      - DBMS는 응용들의 요구사항에 따라 다양한 고립 수준(Isolation Level)을 제공함
    • 영속성 (Durability)
      - 일단 한 트랜잭션이 완료되면 이 트랜잭션이 갱신한 것은 그 후에 시스템에 고장이 발생하더라도 손실되지 않음
  • 트랜잭션의 상태
    • 부분완료 : 마지막 명령문을 실행한 상태
    • 완료 : 모든 트랜잭션 결과를 DB에 반영한 상태
    • 실패 : 트랜잭션의 실패
    • 철회 : 트랜잭션의 모든 결과를 원상태로 돌려 놓은 상태

 

트랜잭션 제어문 (TCL)

 

  • 트랜잭션 제어문
    • COMMIT
      - 트랜잭션의 마지막 명령어가 수행되었음을 나타냄
      - COMMIT된 트랜잭션은 철회가 불가능함
      - COMMIT명령문 실행하기 전에 하나의 트랜잭션 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록 방지하여 일관성을 유지함
    • ROLLBACK
      - 트랜잭션의 변경을 취소하고 트랜잭션 종료
    • SAVEPOINT
      - 현재 트랜잭션에서 ROLLBACK 시킬 위치 지정
  • 트랜잭션 제어문 활용
    • 간단한 트랜잭션 철회
    • 오류발생에 따른 트랜잭션 철회
      - 트랜잭션을 구성하는 명령문들 중에서 오류가 발생되면 트랜잭션을 철회하고 그렇지 않으면 완료하는 것이 필요
    • SAVEPOINT를 이용한 트랜잭션 부분 철회
      - 트랜잭션 내에서 SAVEPOINT의 지정
      SAVE TRAN 저장점명
      - 저장점 위치로 취소
      ROLLBACK TRAN 저장점명
조인
조인

 

  • 조인의 개념
    • 하나의 SQL 질의문에 듸해서 여러 테이블에 저장된 데이터를 한 번에 조회할 수 있는 기능
    • 두 개 이상의 테이블을 '결합' 한다는 의미
    • 조인의 필요성
      -- 하나의 SQL질의문이 하나의 테이블만 검색할 수 있다고 생각하면 너무 불편함
  • 간단한 조인
    • SQL에서 간단한 조인 표기법
      - FROM절에 조인에 참여하는 두 테이블을 기록함
      - WHERE절에 조인 조건을 기술함
  • 조인문 작성 시 유의사항
    ① 컬럼 이름의 모호성
    　- 컬럼 이름 앞에 테이블 이름을 접두사로 사용함
    　- 테이블 이름과 컬럼 이름은 점(.)으로 구분함
    ② 테이블의 이름이 긴 경우 테이블 이름 대신 별명 사용 가능
    　- 하나의 SQL에서 테이블 이름과 별명을 혼용해서 쓸 수 없음
다양한 조인 구문

 

  • 다양한 조인들
    • 카티샨 프로덕트 (Cartesian Product)
      - 두 테이블에 속한 튜플들의 모든 가능한 쌍을 생성함
      - FROM절에 두 개 이상의 테이블명을 기록
      - WHERE절에는 조인 조건을 기술하지 아니함
    • 동등 조인
      - 조인 조건이 " = " 인 경우
    • 자연 조인
      - 조인 조건을 명시하지 않고 조인한다고 할 때 두 테이블에 공통으로 나타나는 속성의 동등조인으로 생각
      - MS-SQL에서는 자연 조인을 명시적으로 지원하지는 않음
    • 쎄타 조인
      - 조인 조건으로 <, >, <=, >=, != 등을 쓸 수 있음
      - 일반적인 조인 조건에 대하여 쎄타 조인이라고 함
    • 셀프 조인
      - 하나의 테이블 내에 있는 컬럼끼리 연관시켜 조인이 필요한 경우
      - 조인 대상 테이블이 두 개 인데 동일한 테이블임
      - 물리적으로 1개이지만 논리적으로 서로 다른 테이블이라고 생각하면 됨
    • 다중 조인
      - 조인 질의의 경우 조인에 참여하는 테이블이 2개로 이를 보통 '2중 조인'(2-way join)이라고 함
  • 다양한 조인들
    • ANSI 조인
      • SQL을 표준화할 때 만든 ANSI 표준 문법
      • 기존 SQL과 차이점은 조인 조건을 WHERE로 표현하지 않고 FROM절에 표현함
      • 크로스 조인 (Cross Join) : 카티샨 프로덕트(Cartesian Product)의 다른 표현법
        FROM 네이블명 CROSS JOIN 테이블명
      • 내부 조인 (Inner Join) : 일반적인 조건의 ANSI 조인 표기법
        FROM 네이블명 INNER JOIN 테이블명 ON 조인조건　 또는 　FROM 네이블명 JOIN 테이블명 ON 조인조건
      • 외부 조인 (Outter Join)
        ▶ 일반적인 조인은 조인 조건을 만족하는 튜플들만이 조인 결과에 나옴
        ▶ 조인 조건을 만족하지 않는 튜플들도 결과로 보고 싶을 경우
        　→ 조인에 참여하는 테이블에 속한 모든 튜플 출력
        ▶ ANSI 조인 표기법 : 명시적 표기법
        　① LEFT OUTER JOIN : 왼쪽 테이블에 있는 튜플들은 다 나옴
        　② RIGHT OUTER JOIN : 오른쪽 테이블에 있는 튜플들은 다 나옴
        　③ FULL OUTER JOIN : 양쪽 테이블에 있는 튜플들은 다 나옴
중첩 질의문
중첩 질의문의 개요

 

  • 중첩 질의문의 개념
    • 하나의 SQL문의 결과를 다른 SQL문에 전달함
    • 두 개의 SQL문을 하나의 SQL로 처리함
    • 이론적으로 중첩 질의문은 조인 구문과 표현능력이 동일함
    • 중첩 질의문의 표현 : SQL문 안에 SQL문이 포함되어 있음
  • 단일행 서브 쿼리와 다중행 서브 쿼리
    • 단일행 서브 쿼리
      - 서브 쿼리의 결과로 하나의 튜플만이 반환됨
    • 다중행 서브 쿼리
      - 서브 쿼리의 결과로 여러 개의 튜플들이 반환됨
    • 단일행 서브 쿼리와 다중행 서브 쿼리를 구분해야하는 이유
      - 일반적인 비교 연산자인 =, <, >, <=, >=, != 등은 속성값 간의 비교 연산임
    • 다중행 비교 연산자
      ① IN
      　- 속성값이 여러 값들 중 하나이기만 하면 참
      　- = OR 의 의미
      ② ANY 또는 SOME
      　- 메인 쿼리 비교 조건에서 서브 쿼리의 결과와 하나라도 일치하면 참
      　- IN과의 차이점은 <, >, <=, >=과 같은 범위 비교와도 같이 사용 가능함
      ③ ALL
      　- 메인 뭐리 비교 조건에서 서브 쿼리의 결과와 모두 일치하면 참
      ④ EXISTS연산자
      　- 서브 쿼리의 결과가 하나라도 존재하면 참이 되는 연산자
      ⑤ NOT EXISTS
      　- EXISTS와 상반되는 연산자
다양한 중첩 질의문

 

  • 다중 컬럼 서브 쿼리
    • 서브 쿼리의 결과가 여러 개의 속성들로 구성되어 주 쿼리의 조건과 비교하는 서브 쿼리임
    • 복수 개의 서브 쿼리들로 구성됨
    • 메인 쿼리와 서브 쿼리의 비교 대상 칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를 출력함
  • 상호 연관 서브 쿼리
    • 비상호 연관 서브 쿼리
      - 서브 쿼리의 결과가 메인 쿼리에서 검사하는 튜플에는 영향 받지 않고 그 결과가 일정함
    • 상호 연관 서브 쿼리
      - 메인 쿼리절과 서브 쿼리 간에 검색 결과를 교환하는 서브 쿼리
      ▷ 주의 사항
      　- 메인 쿼리에서 table1에 속한 튜플을 하나씩 접근하여 WHERE절 수행 시 서브 쿼리가 반복적으로 수행됨으로 성능이 매우 떨어질 수 있음 → 조인 구문을 이용하는 것이 더 효율적
  • 중첩 질의문 작성 시 주의점
    • 중첩 질의문 사용 시 오류가 없도록 IN, ANY, ALL을 기본적으로 사용함
    • 서브 쿼리 내에서는 ORDER BY 절을 사용하면 안 됨
    • 서브 쿼리의 결과가 NULL일 경우, 메인 쿼리의 결과 또한 NULL임
    • 서브 쿼리가 NULL을 반환할 경우, 메인 쿼리에서 결과를 생성하고 싶으면 NOT EXISTS를 사용함
집합 연산자와 집단 연산자
집합 연산자

 

  • 집합 연산자
    • 테이블을 구성하는 튜플 집합에 대한 테이블의 부분 집합을 결과로 반환하는 연산자
      - UNION : 합집합
      - INTERSECT : 교집합
      - EXCEPT : 차집합 (Oracle에서는 MINUS로 사용)
  • UNION과 UNION ALL
    • 집합 연산자를 대상 테이블을 집합으로 봄
    • 따라서 결과도 집합임 → 중복을 허용하지 않음
    • 필요에 따라서 중복된 결과도 보고 싶은경우 UNION ALL을 사용함
  • 외부 합집합
    • 합병 호환성 : ∪, ∩, – 연산의 피연산자(릴레이션)들이 지켜야 할 제약 조건
      - 차수(Degree : 속성의 수)가 같아야 함
      - 대응되는 속성 쌍 별로 타입(또는 도메인)이 같아야 함
      - 대응되는 속성 쌍 별로 의미(Semantic)가 같아야 함
    • 합병 호환성의 불일치
      - 두 질의 결과가 합병 호환성을 만족하지 않을 때, 두 테이블의 합집합을 구하고 싶은 경우
      　외부 합집합(∪+)을 사용한다.
    • SQL에서의 외부 합집합
      - 두 질의 결과의 속성 수와 타입이 일치되게 만듦
      - NULL은 모든 속성에서 사용할 수 있는 속성값임
      ⇨ 이를 이용하여 강제로 동일한 구조가 되게 함
집단 연산자

 

  • 집단 함수
    • 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화 하여, 그 그룹별 통계값을 출력하는 함수.
      (일반적으로 집단 함수는 NULL값을 제외한 속성값들의 통계값을 반환함)
      - SUM : 그룹의 합계
      - AVG : 그룹의 평균
      - COUNT : 그룹의 개수
      - MAX : 그룹의 최댓값
      - MIN : 그룹의 최솟값
      - STDEV : 그룹의 표준편차
      - VAR : 그룹의 분산
    • 분산(VAR)
      - 각 값이 평균과 얼마나 떨어져 있는지에 대한 통계값
      - 각 값과 평균의 차에 대한 차(편차)의 제곱의 평균
      (sumi=1,n(xi-M)2)/N
    • 표준편차(STDEV)
      - 분산의 경우 편차에 대한 제곱으로 나타냅으로 평균과의 단위가 맞지 않음
      - 단위를 맞추기 위해 분산의 제곱근을 표준편차로 사용함
      STDEV = VAR½
    • COUNT (*)
      - 테이블에서 조건을 만족하는 행의 개수를 반환하는 함수
    • COUNT(속성명)
      - 속성값이 NULL이 아닌 속성값의 개수
    • COUNT(DISTINCT 속성명)
      - 속성값이 NULL이 아니며 중복되지 않는 속성값들의 개수
  • GROUP BY와 HAVING
    • GROUP BY
      - 특정 속성을 기준으로 테이블 전체를 그룹으로 나누기 위한 절
      SELECT 컬럼_리스트
      FROM 테이블명
      WHERE 조건
      GROUP BY 컬럼_리스트
      - SELECT절에는 집단 연산자나 GROUP BY에 사용한 속성명 만을 사용할 수 있음
      - 공통되는 속성값으로 그룹핑을 했으므로, 각 그룹에서 개별 튜플을 접근할 수 없음
    • HAVING
      - 각 그룹에 대한 제약 조건을 기술할 때 사용함
      - HAVING절은 GROUP BY절의 종속절임
      - WHERE절은 테이블 전체에 대한 제약 조건을 나타냄
  • ROLLUP과 CUBE
    • 다중 속성 GROUP BY
      - 하나 이상의 속성들을 이용하여 그룹을 나누고, 그룹별로 다시 서브 그룹을 나누고자 할 때
      GROUP BY 컬럼1, 컬럼2, ..., 컬럼n
    • ROLLUP연산자
      - GROUP BY절의 그룹 조건에 따라서 그룹핑하고 각 그룹에 대한 부분합을 구하는 연산자
      - GROUP BY절에 n개의 속성명이 있으면, n+1개의 그룹핑 조합이 나옴
    • CUBE연산자
      - GROUP BY절의 그룹핑하고 각 그룸의 조합에 따른 부분합을 구하는 연산자
      - GROUP BY절에 n개의 속성명이 있으면 2n개의 그룹핑 조합이 나옴
    • GROUPING SETS함수
      - 경우에 따라서 여러 개의 GROUP조건을 표시하고 싶은 경우, 부서, 직급별 합을 보고싶지 않은 경우 GROUPING SETS함수를 이용함
순위계산
순위 함수

 

    • Top 함수
      • 질의 결과 튜플 수의 제한
        - 질의 결과는 ORDER BY절을 이용하여 정렬할 수 있음
        - ORDER BY 정렬 기준에서 특정 등수 / 비율까지만 보고 싶은 경우 ⇨ Top(n)함수를 이용함
        SELECT TOP(n) 속성명
        ...
        ORDER BY 속성명
        - 동률이 있을 때 모두 보고 싶은 경우 ⇨ WITH TIES를 사용함
        SELECT TOP(n) WITH TES 속성명
        ...
        ORDER BY 속성명
      • 정렬 기준 특정 비율까지만 보고 싶은 경우
        - Top(n) PERCENT를 이용함
        - 상위 n%까지만을 출력하게 됨
        - WITH TIES와도 같이 쓸 수 있음
        SELECT TOP(n) PERCENT [WITH TIES] 속성명
        ...
        ORDER BY 속성명
    • RANK함수
      • TOP() 함수를 쓰면 결과 수를 제한함
      • TOP() 함수는 등수를 구할 수 없음
      • RANK 함수 : 각 튜플에 등수를 표시함
        RANK함수 over (order by 속성명 [asc / desc]
      • RANK() 함수
        SELECT 속성명, RANK () OVER (ORDER BY 속성명 [asc / desc])
        - 동률에 대하여 동일 등수 배정
        - 비연속식 등수 배정
      • DENSE_RANK() 함수
        SELECT 속성명, DENSE_RANK () OVER (ORDER BY 속성명)
        - 동률에 대하여 동일 등수 배정
        - 연속식 등수 배정
      • ROW_NUMBER() 함수
        SELECT 속성명, ROW_NUMBER () OVER (ORDER BY 속성명)
        - 동률에 대하여 임의 등수 배정
        - 연속식 등수 배정
      • NTILE(n) 함수
        - 전체 튜플을 num개로 균등 분할하여 순위 지정
그룹별 순위

 

  • 그룹별 순위 지정
    • 기존 RANK 함수 문법
      - 전체 결과에 대한 속성값 기준 등수 지정이 됨
    • PARTITION BY 속성명
      - 튜플들을 속성 값에 따라서 그룹핑함
      - 각 그룹에 대하여 순위 함수를 적용함
      RANK () over (PARTITION BY dno ORDER BY salary desc)
    • 그룹별 특정 등수의 정보를 보고 싶은 경우
      - WHERE절을 같이 활용함
      RANK() over (PARTITION BY dno ORDER BY salary desc) AS 속성명
      ...
      WHERE 속성명 = 등수
  • 그룹별 집단 함수
    • 그룹별 집단 함수의 적용
      SELECT 집단 함수 ~ GROUP BY ~
    • PARTITION BY를 이용해서도 그룹별 집단 함수를 적용할 수 있음
      SELECT 집단함수() OVER (PARTITION BY 속성명)
  • 행 순서 함수
    • 정렬된 대상에서 특정 순위의 튜플들을 추출할 필요가 있을 때, 사용되는 함수
    • FIRST_VALUE 함수
      - 정렬 대상에서 첫번째 데이터 추출
    • LAG / LEAD 함수
      - 지정된 순서에서 선행 / 후행 데이터를 참조하는 함수
인덱스와 뷰
인덱스

 

  • 인덱스의 개념
    • 인덱스의 개념
      - 검색 성능을 향상 시키기 위한 부가적인 자료 구조
      - SQL 명령문의 검색 속도를 향상시키기 위해 칼럼에 대해 생성하는 객체
      - 포인트를 이용하여 테이블에 저장된 데이터를 랜덤 엑세스하기 위한 목적으로 사용함
    • 인덱스가 효율적인 경우
      - WHERE절이나 조인 조건 절에서 자주 사용되는 칼럼의 경우
      - 전체 데이터 중에서 10~15%이내의 데이터를 검색하는 경우
      - 두 개 이상의 칼럼이 WHERE절이나 조인 조건에서 자주 사용되는 경우
      - 테이블에 저장된 데이터의 변경이 드문 경우
    • 색인 생성
      CREATE INDEX 색인명
      ON 테이블명(속성명, 속성명, ...)
    • 색인 삭제
      DROP INDEX 색인명
      ON 테이블명
  • 인덱스의 종류
    • 고유 인덱스
      - 유일 값을 가지는 속성에 대하여 생성하는 색인
      - 각 키 값은 테이블의 하나의 튜플과 연관됨
    • 비교유 인덱스
      - 중복된 값을 가지는 속성에 생성하는 인덱스
      - 키 값은 여러 개의 튜플들과 연관됨
    • 단일 인덱스
      - 하나의 속성만으로 구성된 색인
      - 앞에서 보인 예들은 단일 인덱스들임
    • 결합 인덱스
      - 두 개 이상의 속성들에 대하여 생성된 색인
    • DESCENDING INDEX
      - 일반적인 색인들은 속성값에 대하여 오름차순으로 정렬되어 저장됨
      - 특별히 속성별로 정렬 순서를 지정하여 결합 인덱스를 생성하는 방법
      - 색인 생성 시에 각 속성별로 정렬순서(DESC, ASC)를 정해줌
    • 집중 인덱스
      - 테이블의 튜플이 저장된 물리적 순서 해당 색인의 키값 순서와 동일하게 유지되도록 구성된 색인
      - 기본키에 대해 생성된 색인은 집중 인덱스임
      - 테이블의 튜플들이 기본키에 오름차순으로 정렬되어 저장되어 있고 기본키 색인 또한 기본키에 따라서 오름차순으로 정렬되어 있음
      - 집중 인덱스는 하나의 테이블에 대하여 하나만 생성할 수 있음
    • 비집중 인덱스
      - 집중 인덱스가 아닌 인덱스들
  • 인덱스의 활용
질의 수행 시 인덱스를 사용하는지 확인하기

① 질의 수행 시 인덱스를 강제로 사용하게 하기
② employee테이블의 ename을 가지고 색인 emp_name_idx을 만듦
③ FROM절에 WITH(INDEX = INDEX_NAME)을 추가하여 강제로 특정 색인을 사용하게 함
④ 색인을 사용하는지 질의수행 계획 확인

 

뷰

 

  • 뷰의 개념
    • 뷰의 개념
      - 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블
      - 기본 테이블은 디스크에 공간이 할당되어 데이터를 저장함
      - 뷰는 데이터 딕셔너리(Data Dictionary) 테이블에 뷰에 대한 정의(SQL문)만 저장되어 디스크 저장 공간 할당이 이루어지지 않음
      - 전체 데이터 중에서 일부만 접근할 수 있도록 함
      - 뷰에 대한 수정 결과는 뷰를 정의한 기본 테이블에 적용됨
      - 뷰를 정의한 기본 테이블에서 정의된무결성 제약조건은 그대로 유지됨
    • 뷰의 필요성
      - 사용자 마다 특정 객체만 조회할 수 있도록 할 필요가 있음
      - 복잡한 질의문을 단순화할 수 있음
      - 데이터의 중복성을 최소화할 수 있음
    • 뷰의 장점
      - 논리적 독립성을 제공함
      - 데이터의 접근 제어(보안)
      - 사용자의 데이터 관리 단순화
      - 여러 사용자의 다양한 데이터 요구 지원
    • 뷰의 단점
      - 뷰의 정의 변경 불가
      - 삽입, 삭제, 갱신 연산에 제한이 있음
  • 뷰의 개념
    • 뷰의 생성 구문
      CREATE VIEW 뷰이름
      AS SQL문(select문)
    • 뷰의 삭제 구문
      DROP VIEW 뷰이름
    • 뷰의 종류
      - 단순 뷰: 하나의 기본 테이블 위에 정의된 뷰
      - 복합 뷰 : 두개 이상의 기본 테이블로부터 파생된 뷰
    • 뷰에 대한 갱신 연산
      - 무결성 제약 조건, 표현식, 집단연산, GROUP BY절의 윰에 따라서 DML문의 사용이 제한적임
  • 인라인 뷰
    • 인라인 뷰란?
      - 하나의 질의문 내에서만 생성되어 사용되어지고 질의문 수행 종료 후에는 사라지는 뷰
      - 뷰의 명시적인 선언(즉, Create View문)이 없음
      - FROM절에서 참조하는 테이블의 크기가 클 경우, 필요한 행과 속성만으로 구성된 집합으로 정의하여 질의문을 효율적으로 구성함
      - FROM절에서 서브 쿼리를 사용하여 생성하는 임시 뷰
    • WITH절
      - 인라인 뷰의 또 다른 정의 방법
      - FROM절에 임시 질의 결과를정의하는 대신 WITH절을 이용하여 임시 테이블을 생성함
      WITH 임시테이블명(속성명)
      AS (SELECT ~ FROM ~ WHERE)
    • 뷰의 정의 보기
      - 뷰의 정의 내용을 보고 싶을 경우 SP_HELPTEXT라는 저장 프로시져를 이용함
      - 저장 프로시져를 수행하는 명령문 : EXEC
사용자관리
보안

 

  • 통제
    • 보안
      - 테이블을 구성하는 튜플 집합에 대한 테이블의 부분 집합을 결과로 반환하는 연산자
    • 보안에 대한 통제
      ① 법적, 윤리적 통제
      - 법, 윤리 ⇨ 심리적 보안
      ② 행정, 관리적 통제
      - 오용을 탐지하고 방지함
      ③ 물리적 통제
      - 적극적, 물리적 보안으로 위반을 예방, 탐지함
      ④ 기술적 통제
      - 하드웨어 통제
      - 소프트웨어 통제
      - 데이터베이스 통제 : DBMS보안 서브 시스템 ⇨ 접근 제어
  • 접근 제어
    • 권한이 부여되지 않은 데이터의 검색이나 변경을 방지함
      ① 직접 접근 제어
      - 사용자 신분증 확인(ID)
      - 신분증 본인 확인을 위한 인증(PASSWORD)
      - 요청 데이터 객체에 대한 요청 연산 권한(권한부여)
      ② 간접 접근 제어
      - 한 장소에서 다른 장소로의 데이터 흐름 제어
      - 개인의 비밀 데이터로부터 작성된 통계정보에 대한 추론 제어전송이나 저장 데이터의 암호화 시스템 작동과 사용자 상호작용의 감시
    • 접근 제어 구조
      - 신분증 : 지문, 성문, ID
      - 인증 : 권한 부여 테이블(사용자, 접근 가능한 데이터와 연산)
                 데이터 베이스 정보
                 요구되는 연산(메인 메모리에 있는 권한 부여 테이블, 사용자 활동 로깅)
    • 권한 부여 규정
      - 권한 부여 규정은 DCL로 명세함
      - 명세된 규정은 데이터 딕셔너리에서 관리함
권한 부여

 

  • 뷰 기반 기법
    • 뷰 기반 기법이란?
      - 뷰를 이용한 권한 부여
      - 특정 뷰에 대하여 특정 사용자만 보도록 지정함
      - ㅁㅣㄴ감한 데이터를 권한이 없는 사용자로부터 은닉할 수 있음
      - 릴레이션의 수직적 / 수평적 서브셋을 제한할 수 있음
    • 뷰 기반 기법의 문제점 - 튜플 삽입의 제약
      CREATE VIEW ST1
      AS SELECT SNO, NAME, SAL
      FROM STUDENT
      WHERE YEAR 4
      일 때, 다음과 같은 튜플 삽입의 제약이 있다.
      INSERT INTO S1(SNO, NAME, YEAR) : <'E5', 'LEE', 5>
    • 뷰 기반 기법의 문제점 - 알려진 값의 NULL값
      CREATE VIEW ST2
      AS SELECT SNO, YEAR
      - FROM STUDENT
      WHERE DEPTNO = 12
      INSERT INTO ST2 (SNO, YEAR) : <'E5', 2>
      ST는 DEPTNO가 12인 뷰인데 삽입될 때는 12대신 NULL이 들어감
  • GRANT / REVOKE 기법
    • 특정 데이터와 연산을 특정 사용자만 수행할 수 있도록 권한 부여하는 DCL문
      - GRANT문 : 자신에게 허용된 권한을 다른 사용자에게 부여하는 구문
      - REVOKE문 : 다른 사용자에게 허용한 권한을 철회하는 구문
      - DENY문 : 다른 사요아에게 특정 권한을 불허하는 구문
    • GRANT 구문
      GRANT [권한|ALL] ON 데이터객체 TO 사용자
      - 데이터객체가 테이블 또는 뷰일 경우 : SELECT, INSERT, UPDATE, DELETE, REFERENCE 등 사용 권한
      - 데이터객체가 데이터베이스일 경우 : CREATE [DB, TABLE, VIEW] 등의 권한
      ▶ 주의점 : DROP권한은 일반적으로 생성자(주인)만 가짐
      - ALL : 모든 권한을 말함
    • REVOKE / DENY 구문
      REVOKE 권한 ON 데이터객체 TO 사용자
      DENY 권한 ON 데이터객체 TO 사용자
  • MS-SQL에서의 권한 부여
저장 프로스저와 사용자 정의 함수

 

프로시저

 

  • 프로시저의 개념
    • 자주 사용되는 질의문들을 하나로 묶어서 저장해두고 필요할 때마다 명령문처럼 실행할 수 있도록 해주는 개념
      - 선택적으로 매개변수를 받아 일련의 질의문을 수행해서 결과를 돌려주는 것
      - 범용 언어의 프로시저 / 함수와 유사한 개념임
    • 일반 질의문과의 차이점
      ① 일반 질의문
      - 사용자 또는 응용 프로그램이 실행하고자 하는 SQL문을 DBMS에 전송하고 그 결과를 받음
      - 대량의 복잡한 질의문들이 반복적으로 입력되면 그만큼 시스템에 부담이 됨
      - DBMS에도 처리해야 하는 일이 늘어나 시스템에 더 큰 부담이 됨
      ② 프로시저
      - 프로시저 내용은 DBMS에 포함되어 있고 실행 방안도 미리 작성되어 있음
      - 사용자나 응용프로그램은 프로시저 이름과 매개변수 값(필요시)만을 전송하면 복잡한 SQL문의 단순화가 가능해진다.
    • 프로시저 생성 구문
      CREATE [PROCEDURE|PROC] 프로시저이름
      AS
      BEGIN SQL문 END // BEGIN END는 SQL문이 하나만 있다면 생략 가능
    • 프로시저 실행 문법
      EXEC 프로시저이름
    • 프로시저 수정 문법
      ALTER PROCEDURE 프로시저이름
      AS SQL문
      DROP PROCEDURE 프로시저이름
  • 프로시저의 매개변수
    • 프로시저 실행 시 조건값 등을 변경할 수 없을까?
      - 사원번호 109번에 대한 정보를 추출하는 저장 프로시저를 생성하고 이를 110번에 대한 정보를 추출하도록 저장 프로시저 변경
      - 저장 프로시저 수행 시 사원번호를 입력으로 주어 해당 사원정보를 추출하도록 할 수 없을까? ⇨ 매개 변수를 사용함
      - 저장 프로시저 수행 시 수행 질의문에 특정 값을 매개변수로 전달할 수 있도록 하여 다양한 조근을 하나의 질의문으로 수행할 수 있도록 지원해줌
    • 입력 매개변수의 선언
      - 생성
      CREATE POCEDURE 프로시저이름
      @매개변수명 타입, ...
      AS SQL문
      - 실행
      EXEC 프로시저이름 매개변수값
    • 출력 매개 변수
      - 입력 매개변수와 반대로 프로시저의 처리 결과값을 반환하는 매개변수
      - 출력 매개변수 선언
      CREATE PROCEDURE 프로시저이름
      @매개변수명 타입 OUTPUT, ...
      AS
      SELECT @매개변수명 = 속성명
      FROM ... WHERE ...
      - 출력 매개변수 값 받기
      ▶ 프로시저 실행 전에 매개변수를 선언함(DECLARE문 이용)
      ▶ 선언된 매개변수를 출력함(SELECT문 이용)
      ▶ 프로시저 실행 전에 매개변수를 선언함(DECLARE문 이용)
      DECLARE @매개변수명
      - 프로시저 실행
      EXEC 프로시저명 @매개변수명 OUTPUT
      - 선언된 매개변수를 출력함(SELECT문 이용)
      SELECT @매개변수명
트리거
무결성 규정

 

  • 무결성의 의미
    • 무결성
      - 정밀성, 정확성, 정당성
      - 허가 받은 사용자가 수행하는 갱신 작업에서 의미적 오류를 방지함
      - 의미적 제약의 개념
    • 무결성 서브 시스템
      - 사용자 요청 ⇨ 보안 시스템 ⇨ 갱신연산 ⇨ 무결성 서브시스템 ⇨ 갱신 ⇨ 정확한DB
      - 무결성 규정을 유지 관리함
      - 데이터베이스의 무결성을 유지함
      - 트랜잭션이 수행하는 갱신 연산이 무결성 규정을 위반하지 않는가를 감시함
      ▶ 위반 시에는 거부, 보고, 취소 / 복귀를 수행함
  • 제약 조건
    • 무결성 규정 대상
      - 도메인 : 형식, 타입, 범위
      - 기본키, 외래키 : 개체 무결성(Entity Integrity), 참조 무결성(Referencial Integrity)
      - 종속성 (묵시적 제약조건) : 함수 종속, 다치 종속, 조인 종속
      - 관계 : 내부 관계, 외부 관계
    • 도메인 무결성 대상
      - 도메인 정의 : 도메인 이름, 데이터형
      - 삽입이나 갱신 연산에 적용
    • 릴레이션의 무결성 규정
      - 릴레이션을 조작하는 과정에서의 의미적 제약조건을 명세함
      - 연산 수행 전 / 후에 대한 제약 조건을 규정함
      ▶ 삽입
      ▶ 삭제
      ▶ 갱신
      - 분류
      ▶ 상태 제약과 과도 제약
      ▶ 집합 제약과 튜플 제약
      ▶ 즉시 제약과 지연 제약
    • 상태 제약
      - 릴레이션 상태에 대한 제약
      - 일관성 있는 상태 유지 : 정적 제약
      ▶ 각 릴레이션 상태가 모두 만족해야 하는 규정
      ▶ 데이터베이스 상태의 유효성
      - 키 속성의 제약 : 유일성
      - NULL값의 제약: 이름은 NULL값일 수 없음
      - 관계 제약 : 참조무결성
      - 도메인 제약 : 유효한 값
      - 의미 무결성 제약
    • 과도 제약
      - 동적 제약
      - 데이터베잇의 한 상태에서 다른 상태로 변환되는 과정에서 적용되는 규정
      - 데이터베이스 상태의 변환 전과 후의 비교
      ▶ 변환 전과 후에 모두 적용됨
      (예) 월급은 감소될 수 없음
    • 집합 제약
      - 튜플 집합 전체에 대한 제약
    • 튜플 제약
      - 처리되고 있는 튜플에만 적용됨
    • 즉시 제약
      - 삽입 / 삭제 / 갱신 연산이 수행될 때마다 적용되는 제약 규정
    • 지연 제약
      - 트랜잭션이 완전히 수행된 후에 적용되는 제약 규정
트리거 활용

 

  • 트리거의 개념
    • DBMS에서 특정 사건이 발생 시 자동으로 일련의 과정이 수행되는 프로시저
      - 프로시저
      ▶ 사용자가 직접 EXEC 명령어를 이용하여 프로시저를 수행함
      - 트리거
      ▶ 특정 조건을 만족하면 자동으로 수행되도록 하는 저장 프로시저
      ▶ 특정 사건이 발생될 때만 실행되는 프로시저
      ▶ 사용자가 트리거를 따로 호출할 필요 없음
    • 무결성과 트리거(TRIGGER)
      - 트리거는 데이터의 변경이 발생될 때 수행됨
      ▶ 데이터 변경 시 무결성에 문제가 발생되면 이를 보완할 수 있도록 자동으로 프로시저를 수행하도록 트리거를 정의해 놓으면 무결성을 유지시킬 수 있음
      - 단점
      ▶ 릴레이션 선언 시 정의한 제약조건에 비하여 성능이 저하됨
      - 장점
      ▶ 프로시저와 더불어 데이터베이스 내에 업무 규칙을 구현할 수 있음
    • 수행 기점에 따른 트리거의 분류
      - AFTER 트리거
      ▶ 이벤트(삽입 / 삭제 / 변경) 발생 직후 실행되는 트리거
      ▶ 테이블에 대해서만 작성됨
      - BEFORE 트리거
      ▶ 이벤트(삽입 / 삭제 / 변경) 발생 이전에 실행되는 트리거
      ▶ 일반적으로 BEFORE트리거는 지원되지 않음
      - INSTEAD OF 트리거
      ▶ 이벤트(삽입 / 삭제 / 변경) 발생 시 해당 이벤트 대신 구동되는 트리거
      ▶ 즉, 다른 작업을 수행하는 트리거
      ▶ INSTEAD OF 트리거를 활용하여 BEFORE 트리거 같은 역할을 수행시킬 수 있음
    • inserted와 deleted 테이블
      사건 iserted 테이블 deleted 테이블
      삽입 방금 삽입된 튜플이 복사됨 -
      변경 변경된 튜플이 복사됨 변경 전 튜플을 보관함
      삭제 - 방금 삭제된 튜플을 보관함
  • 트리거의 구동
    • 트리거 생성 문법 
      CREATE TRIGGER 트리거명
ON 테이블명
[for / after / instead of]
[insert / update / delete]
AS
SQL문

      - for는 after와 같은 것임
      - on 테이블에 의해 테이블에 내용이 추가 / 삭제되면 inserted 또는 deleted라는 가상 테이블에 자동으로 추가되고 이를 이용하여 트리거를 수행시키게 됨
    • AFTER트리거 (또는 FOR트리거) 구동 예
      - 새로운 업무 규칙
      ▶ 신입 직원들의 급여는 무조건 100임
      ▶기존 직원들은 이 규칙에 적용 받지 않음
      ▶ Employee테이블에 새로운 튜플이 들어올 때마다 salary속성을 자동으로 100이 되게 하면 됨
      - INSTEAD OF 트리거
      ▶ 뷰나 테이블에 삽입 / 삭제 / 변경 연산에 대응되어 다른 작업을 수행하는 트리거
    • DDL트리거
      - CREATE, ALTER, DROP과 같은 DDL문이 발생시 구동되는 트리거
      - 정의시 ON테이블명 대신 ONDATABASE를 사용함
      - INSTEAD OF 트리거는 지원 안 함
      CREATE TRIGGER 트리거명
ON DATABASE
{for / after} {DROP_TABLE | CREATE_TABLE | ALTER_TABLE}
SQL문

      - 트리거의 변경과 삭제
      ▶ DCL문임으로 DROP과 ALTER문을 씀

      DROP TRIGGER 트리거명

      ALTER TRIGGER 트리거명
저작자표시 변경금지