[정보처리기사 필기] 데이터베이스 구축 - SQL

 

SQL

 

SQL

    COBOL, PL/1 과는 다르다.

    독립적으로 사용, 응용 프로그램과 결합

    관계 대수를 기초로

 

 

내장 sql

    프로그램 언어의 변수와 데이터베이스 속성의 이름 같아도 된다.

    프로그래밍 언어 변수 타입과 데이터베이스 속성의 타입은 같아야 된다.

    분리되어 컴파일

    프로그램 실행될 때 내장 SQL문은 함께 실행

 

 

DDL

    CREATE, ALTER, DROP, TRUNCATE

DML

    INSERT

    UPDATE

    DELETE

    SELECT

DCL

    DCL

        GRANT

        REVOKE

    TCL

        COMMIT

        ROLLBACK

        CHECKPOINT

 

 

NULL 사용

    IS NOT NULL

 

 

CREATE, SELECT 명령으로 새로운 테이블 생성시

    AS를 사용 create table 테이블명 as (select ... )

    제약 조건 추가

    NOT NULL의 정의는 그대로 적용

    필요한 컬럼만 지정하여 테이블 생성 가능

 

 

ALTER : 속성을 변경 삭제 추가 / ALTER TABLE

    ADD , MODIFY, DROP COLUMN

 

 

CLUSTER

    지정된 컬럼 값의 순서대로 행 저장

 

CASCADE

    자동적으로 자식 테이블 해당 레코드 삭제

 

SET NULL

    외래키 참조 무결성 위배 시, NULL 값을 입력

 

RESTRICT

    테이블을 삭제할 때 참조하는 테이블이 있다면 실행취소

 

트랜잭션

    DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위, 일련의 연산

    하나의 트랜잭션은 COMMIT, ROLLBACK

    일반적으로 회복의 단위

    SAVE POINT 는 트랜잭션에 여러번 지정 가능

 

    트랜잭션 특징

        원자성 Atomicity

            트랜잭션 연산은 모두 반영되든지 ,전혀 반영되지 않아야 한다.

        지속성 Durability

            고장에도 손실되지 않아야 한다.

        공유 Share

        일관성 Consistency

            다른 일관된 상태로 바꿔야 한다.

        고립성

            다른 트랜잭션의 간섭 받아서는 안된다.

    

        각 상태

            활동

                초기 상태로 트랜잭션이 실행 시작, 실행 상태

            부분 완료

                트랜잭션 마지막 연산 끝내고, 데이터베이스에 실행 결과를 적용하기 직전의 상태

            완료

                트랜잭션 실행 성공적 완료 연산 수행 상태 COMMIT

            철회

                ROLLBACK 연산 수행 상태

 

 

CRUD 분석

    CRUD 매트릭스를 작성하여 분석

    트랜잭션 부하가 집중되는 DB채널 파악하고 분산 오류 방지 기능

    테이블에 저장되는 테이블 양 유추 가능

---

절차형 SQL

    Procedure

        호출 실행, DML

        특정 기능을 수행할 수 있는 트랜잭션 언어

    User Define Function

        RETURN

        데이터 조작어에 직접 적용 UPDATE

    Trigger

        데이터 변경 이벤트가 발생하면, DBMS에서 자동적으로 수행되는 절차형 SQL

 

    필수요소

        DECLARE , BEGIN, END

 

    프로시저 요소

        DECLARE, SQL, EXCEPTION ..

 

 

분포도 조사

    수정 빈번하지 않은 컬럼 선정

    분포도 10~15%

    평균 행 수 / 테이블 행 수

    자주 조합 사용되는 컬럼은 결합 인덱스로 생성 활용

---

인덱스

    인덱스는 삽입 X, 검색 목록을 생성

    인덱스의 조작 명령, 옵션

        생성 CREATE

        삭제 ALTER .. DROP

        변경 ALTER ON

        조회 SHOW

 

    인덱스 설계 시 고려사항

        너무 많은 인덱스는 오버헤드

        추가적인 저장공간

        추가되는 인덱스는 기존 접근 경로에 영향 미친다.

---

View

    뷰 장점

        뷰 자체 인덱스 X

        데이터 보안 용이

        논리적 독립성 제공

        사용자 데이터 관리 용이

 

    CREATE 문으로 정의 /  CREATE OR REPLACE VIEW

    뷰에 대한 연산 제약(SELECT, DROP)

    뷰는 저장 장치 내에 논리적으로 존재

    뷰 위에 또 다른 뷰 정의

    삽입 갱신 삭제에 제약

    기본 테이블 제거되면, 뷰도 자동적으로 제거

    데이터에 접근하여 제어(선택 검색, 그룹 검색 등)

---

데이터 사전의 태이블

    TABLES

    SCHEMATA

    COLLATIONS

 

 

PL/SQL

    SQL 지원 도구

    ERROR 처리

    블록 내에서 명령어들을 묶을 수 있다.

    IF 문 사용

    SQL : 서버 환경

    비 SQL : 클라이언트 환경

 

 

쿼리 성능을 측정하고 개선하는 도구

    TKPROF

        추적 결과

        Fetch 수

        처리된 튜플 수

        Commit/Rollback

    소스 코드 인스펙션

    EXPLAIN PLAN

 

 

SQL*Plus : SQL을 DBMS 서버에 전송하여 처리할 수 있도록 하는 도구

    여러 행 입력 불가능

    버퍼 사용하지 않는다.

    Oracle

 

 

데이터 사전

    모든 객체에 관한 정의나 명세에 관한 정보를 메타 데이터 형태로 유지 관리

 

 

시스템 카탈로그

    개념

        테이블정보, 인덱스 정보, 뷰 정보 등을 저장하는 시스템 테이블

    시스템 카탈로그 갱신은 DBMS가 자동적으로 수행

    사용자가 접근하여 검색 가능, 직접적인 변경 불가능

    사용자에 의해서 테이블 구조가 변경되면, 시스템 카탈로그는 자동적으로 갱신

    하나의 데이터베이스만 존재할 경우, 데이터 사전과 시스템 카탈로그는 동일

---

로킹

    로킹 단위

        로킹의 대상이 되는 객체의 크기

        데이터베이스, 파일, 레코드

 

        로킹단위 커지면 - 로크 수 적음, 병행성 수준 낮음, 기법 단순 , 오버헤드 감소

    회복 기법

        로그필요 : 즉시 갱신, 지연

    검사시점 : Checkpoint

    그림자 페이징

 

SQL - DML 데이터 조작어 (SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOIN) 기초