(0과 1의 기록)

7. SQL 응용

정보처리기사 정보처리기사요약 SQL ORACLE 트랜잭션 ACID 데이터베이스고립화 DDL DML DCL 프로시저 옵티마이저

데이터베이스 기본

트랜잭션 :star::star::star:

  • 트랜잭션

    • 개념: 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성, 작업의 기본 단위

    • 특징: 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 영속성(Durability) ACID

    • 상태 변화: 활동 상태, 부분 완료 상태, 완료 상태, 실패 상태, 철회 상태 활부완실철

    • 제어: 커밋, 롤백, 체크포인트 커롤체

    • 병행 제어

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

      • 미보장 시 문제점: 갱신 손실, 현황 파악오류, 모순성, 연쇄복귀 갱현모연

      • 기법의 종류: 로킹, 낙관적 검증, 타임 스탬프 순서, 다중버전 동시성 제어 로 낙타다

    • 데이터베이스 고립화 수준(격리성 주요 기법)

      • 개념: 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도이다.

      • 종류: Read Uncommitted, Read Committed, Repeatable Read, Serializable Read

    • 회복 기법

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

      • 종류: 로그 기반 회복 기법(지연 갱신 회복 기법, 즉각 갱신 회복 기법), 체크 포인트 회복 기법, 그림자 페이징 회복 기법 회로 체크 ( 회복 기법: 로그, 체크 포인트, 그림자 )

  • DDL (데이터 정의어)

    • 대상: 도메인, 스키마, 테이블, 뷰, 인덱스 도스테뷰인

      • 스키마 3계층: 외부 스키마, 개념 스키마, 내부 스키마

      • 테이블 관련 용어: 튜플/행, 애트리뷰트/열, 식별자, 카디널리티, 차수, 도메인

      • 뷰의 특징: 논리적 데이터 독립성 제공, 데이터 조작 연산 간소화, 보안 기능 제공, 뷰 변경 불가

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

        👉 종류: 순서 인덱스, 해시 인덱스, 비트맵 인덱스, 함수기반 인덱스, 단일 인덱스, 결합 인덱스, 클러스터드 인덱스 순해비함 단결클

    • 명령어: CREATE, ALTER, DROP, TRUNCATE

      • 테이블 관련 DDL
        CREATE TABLE emp
  (
      empno NUMBER(10) PRIMARY KEY
      ,ename VARCHAR2(10) UNIQUE
      ,egender CHAR(1) CHECK (egender = 'M' OR egender = 'F')
      ,job VARCHAR2(20) NOT NULL
      ,hiredate DATE DEFAULT SYSDATE
      ,deptno VARCHAR2(20) FOREIGN KEY REFERENCES dept(deptno)
  );
		
  ALTER TABLE emp ADD tel VARCHAR2(15) UNIQUE;
		
  ALTER TABLE emp MODIFY tel NUMBER(15) NOT NULL;
		
  ALTER TABLE emp DROP tel;
		
  DROP TABLE emp [CASCADE | RESTRICT];
		
  TRUNCATE TABLE emp;
      • 뷰 관련 DDL
        CREATE VIEW view_emp AS
  SELECT empno, ename
  FROM emp
  WHERE egender = 'F';
  -- UNION, ORDER BY 절 사용 불가
		
  -- 뷰 교체 명령어
  CREATE OR REPLACE VIEW view_emp AS
  조회쿼리;
		
  DROP VIEW view_emp;
      • 인덱스 관련 DDL
        CREATE INDEX emp_idx ON emp(empno);
		
  ALTER INDEX emp_idx ON emp(empno);
		
  DROP INDEX emp_idx;

  • DML

    • 명령어: SELECT, INSERT, UPDATE, DELETE

      • SELECT 명령어

        👉 조인: Inner Join, Outer Join(Left Outer Join, Right Outer Join, Full Outer Join), Cross Join, Self Join

        👉 집합 연산자: UNION, UNION ALL, INTERSECTION, MINUS

          SELECT [ALL | DISTINCT] 속성명1, 속성명2 ...
  FROM 테이블명1, 테이블명2...
  -- BETWEEN 값1 AND 값2
  -- IN(값1, 값2, ...)
  -- NOT IN(값1, ...)
  -- LIKE 패턴
  -- IS NULL | IS NOT NULL
  -- AND, OR, NOT, !
  [WHERE 조건]
  [GROUP BY 속성명1, ...]
  [HAVING 그룹조건]
  [ORDER BY 속성 [ASC | DESC]];

      • INSERT 명령어: INSERT INTO 테이블명(속성명1, ...) VALUES (데이터1, ...);

      • UPDATE 명령어: UPDATE 테이블명 SET 속성명 = 데이터, ... WHERE 조건;

      • DELETE 명령어: DELETE FROM 테이블명 WHERE 조건;

  • DCL

    • 명령어: GRANT, REVOKE

      • GRANT 명령어: GRANT 권한 ON 테이블 TO 사용자

      • REVOKE 명령어: REVOKE 권한 ON 테이블 FROM 사용자

응용 SQL 작성하기

집계성 SQL 작성 :star::star::star:

  • 데이터 분석 함수

    • 개념: 총합, 평균 등의 데이터 분석을 위한 다중 행 함수이다.

    • 종류: 집계 함수(전체 행), 그룹 함수(소그룹), 윈도 함수(온라인 분석)

  • 집계 함수

    • 종류: COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN

  • 그룹 함수

    • 종류: ROLLUP, CUBE, GROUPING SETS

  • 윈도 함수

    • 순위 함수: RANK, DENSE_RANKE, ROW_NUMBER

절차형 SQL 활용하기

절차형 SQL :star:

  • 개념: SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.

  • 종류: 프로시저, 사용자 정의 함수, 트리거

프로시저 :star::star:

  • 개념: 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

  • 구성: 선언부(DECLARE), 시작/종료부(BEGIN/END), 제어부(CONTROLL), SQL, 예외부(EXCEPTION), 실행부(TRANSACTION) 디비컨 SET

CREATE [OR REPLACE] PROEDURE 프로시저_명
(파라미터_명, [IN | OUT | INOUT] 데이터_타입, ...)
IS
	변수 선언
BEGIN
	명령어;
[COMMIT | ROLLBACK]
END;

사용자 정의 함수 :star:

  • 개념: 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL이다.

  • 구성: 선언부(DECLARE), 시작/종료부(BEGIN/END), 제어부(CONTROLL), SQL, 예외부(EXCEPTION), 반환부(RETURN) 디비컨 SER

CREATE [OR REPLACE] FUNCTION 함수명
(파라미터_명 IN 데이터_타입, ...)
RETURN 데이터_타입
IS
	변수 선언
BEGIN
	명령어;
	RETURN 변수;
END;

트리거 :star:

  • 개념: 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다.

  • 종류: 행 트리거(여러 번), 문장 트리거(한 번)

  • 구성: 선언부(DECLARE), 이벤트(EVENT), 시작/종료부(BEGIN/END), 제어부(CONTROLL), SQL, 예외부(EXCEPTION) 디이비컨 SE

CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE | AFTER] 유형 ON 테이블명
[FOR EACH ROW]
BEGIN
END;

  • 접두어: INSERT, UPDATE, DELETE

  • 주의사항: TCL 사용 불가, 오류에 주의

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

데이터 조작 프로시저 성능 개선 :star:

  • 쿼리 성능 개선(튜닝)

    • 개념: 프로시저에 있는 SQL 실행 계획을 분석, 수정을 최소의 시간으로 원하는 결과를 얻도록 하는 작업이다.

    • 절차: 문제 있는 SQL 식별 → 옵티마이저 통계 확인 → SQL 문 재구성 → 인덱스 재구성 → 실행계획 유지관리

  • 옵티마이저 통계 확인

    • 개념: SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진이다.

    • 유형: 규칙기반 옵티마이저(RBO), 비용기반 옵티마이저(CBO)

    • 역할: 쿼리 변환, 비용 산정, 계획 생성

    • 힌트 사용: 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경한다.

Search