자격증/정보처리기사

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

윤규헌 2023. 7. 20. 13:49

1. DDL

DDL(Data Define Language, 데이터 정의어)

  • DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
  • 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장된다.
  • DDL의 3가지 유형
    • CREATE : SCHEMA, DOMAIN, TABLE, VIEW,INDEX를 정의한다.
    • ALTER : TABLE에 대한 정의를 변경하는 데 사용한다.
    • DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제한다.

CREATE SCHEMA

  • 스키마를 정의하는 명령문
  • 스키마
    • 데이터베이스와 구조와 제약 조건에 관한 전반적인 명세를 기술한 것
    • 데이터 개체, 속성, 관계 및 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.
  • 표기 형식
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;

예제 : 소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문
CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

CREATE DOMAIN

  • 도메인을 정의하는 명령문
  • 도메인
    • 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
    • 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 사용자 정의 데이터 타입
  • 표기 형식
CREATE DOMAIN 도메인명 [AS] 데이터_타입
    [DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];
  • 데이터타입 : SQL에서 지원하는 데이터 타입
  • 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값
예제 : '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현하는 도메인 SEX를 정의하는 SQL문
CREATE DOMAIN SEX CHAR(1)
	DEFAULT '남'
	CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여'));

CREATE TABLE

  • 테이블을 정의하는 명령문
  • 테이블
    • 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션이라 부르고, 조작이나 검색 시에는 테이블이라고 부른다.
    • 대부분은 테이블과 릴레이션을 구분 없이 사용한다.
CREATE TABLE 테이블명
    (속성명 데이터_타입 [DEFAULT 기본값][NOT NULL], ...
    [, PRIMARY KEY(기본키_속성명, ...)]
    [, UNIQUE(대체키_속성명, ...)]
    [, FOREIGN KEY(외래키_속성명, ...)]
            [REFERENCES 참조테이블(기본키_속성명, ...)]
            [ON DELETE 옵션]
            [ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명][CHECK (조건식)];
  • 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정한다.
  • PRIMARY KEY : 기본키로 사용할 속성을 지정함
  • UNIQUE
    • 대체키로 사용할 속성을 지정한다.
    • 중복값을 가질 수 없다.
  • FOREIGN KEY ~ REFERENCES : 외래키로 사용할 속성을 지정함
    • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제 되었을 때 기본 테이블에 취해야할 사항을 지정한다.
    • ON UPDATE 옵션 : 참조 속성 값이 변경 되었을 때 기본테이블에 취해야할 사항을 지정한다.
  • CONSTRAINT : 제약 조건의 이름을 지정함
  • CHECK : 속성 값에 대한 제약 조건을 정의함

CREATE VIEW

  • 뷰를 정의하는 명령문
  • 뷰
    • 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블
    • 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않는다.
      • 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행된다.
  • 표기형식
CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;
예제 : <고객> 테이블에서 '주소'가 안산시인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하시오.
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

CREATE INDEX

  • 인덱스를 정의하는 명령문
    • 인덱스 : 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조
  • 표기형식
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC|DESC][,속성명 [ASC|DESC]]
[CLUSTER];
  • UNIQUE
    • 사용된 경우 : 중복 값이 없는 속성으로 인덱스 생성
    • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스 생성
  • 정렬 여부 지정
    • ASC : 오름차순 정렬
    • DESC : 내림차순 정렬
    • 생략된 경우 : 오름차순으로 정렬됨
  • CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정된다.
    • 클러스터드 인덱스
      • 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
      • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
      • 하지만 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재졍렬해야 한다.
    • 넌 클러스터드 인덱스
      • 인덱스의 키 값만 정렬되어 있을 뿐, 실제 데이터는 정렬되지 않는 방식
      • 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로, 클러스터드 인덱스에 비해 검색 속도가 떨어진다.
예제 : <고객> 테이블에서 UINQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하시오.
CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

ALTER TABLE

  • 테이블에 대한 정의를 변경하는 명령문
  • 표기 형식
ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];
  • ADD : 새로운 속성(열)을 추가할 때 사용한다.
  • ALTER : 특정 속성의 DEFAULT 값을 변경할 때 사용한다.
  • DROP : 특정 속성을 삭제할 때 사용된다.
예제 : <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고, NULL값이 입력되지 않도록 변경하시오.
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

DROP

  • 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건을 제거하는 명령문
  • 표기 형식
DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

 

  • CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거함
  • RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소함
예제 : <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오.
DROP TABLE 학생 CASCADE;

2. DCL

DCL(Data Control Language, 데이터 제어어)

  • 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어
  • 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.
  • DCL의 종류
    • COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장
    • ROLLBACK : 데이터베이스 조작작업이 비정상적으로 종료되었을 때 원래 상태로 복구
    • GRANT : 데이터베이스 사용자에게 사용권한을 부여
    • REVOKE : 데이터베이스 사용자의 사용권한을 취소

GRANT / REVOKE

  • 데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어

사용자 등급 지정 및 해제

GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

테이블 및 속성에 대한 권한 부여 및 취소

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
  • 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
  • WITH GRANT OPTION : 부여 받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
  • GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
  • CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

COMMIT

  • 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
  • COMMIT명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있다.

ROLLBACK

  • 변경되었으나 아직 COMMIT 되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
  • 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백 되어야 한다.

SAVEPOINT

  • 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
  • 저장점을 지정할 때는 이름을 부여한다.
  • ROLLBACK 할 때 지정된 저장점까지의 트랜잭션 처리내용이 모두 취소된다.

TCL(Transaction Control Language)

  • COMMIT, ROLLBACK, SAVEPOINT는 트랜잭션을 제어하는 용도로 사용되므로 TCL로 분류하기도 한다.
  • 하지만 기능을 제어하는 명령이라는 공통점으로 DCL의 일부로 분류하기도 한다.

3. DML

DML(Data Manipulation Language, 데이터 조작어)

  • 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
  • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • DML의 유형
    • SELECT : 테이블에서 튜플을 검색
    • INSERT : 테이블에 새로운 튜플을 삽입
    • DELETE : 테이블에서 튜플을 삭제
    • UPDATE : 테이블에서 튜플의 내용을 갱신

삽입문(INSERT INTO ~ )

  • 새로운 튜플을 삽입할 때 사용한다.
  • 일반 형식
INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES(데이터1, 데이터2);
  • 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
  • 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
  • SELECT 문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.

삭제문(DELETE FROM ~ )

  • 특정 튜플을 삭제할 때 사용한다.
  • 일반 형식
DELETE FROM 테이블명 [WHERE 조건];
  • 모든 레코드를 삭제할 때 WHERE절을 생략한다.
  • 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기에 디스크에서 테이블을 완전히 제거하는 DROP과는 다르다.

갱신문(UPDATE ~ SET ~)

  • 특정 튜플의 내용을 변경할 때 사용한다.
  • 일반 형식
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

4. DML - SELECT

SELECT 일반 형식

SELECT [DISTINCT] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];
  • SELECT 절
    • PREDICATE : 검색할 튜플 수를 제한하는 명령어를 기술한다.
      • DISTINCT : 중복된 튜플이 있으면, 그 중 첫번째 한 개만 표시한다.
    • 속성명 : 검색하여 불러올 속성 또는 속성을 이용한 수식을 지정한다.
    • AS : 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용한다.
  • FROM 절 : 검색할 데이터가 들어있는 테이블 이름을 기술한다.
  • WHERE 절 : 검색할 조건을 기술한다.
  • ORDER BY 절 : 데이터를 정렬하여 검색할 때 사용한다.
    • 속성명 : 정렬의 기준이 되는 속성명을 기술한다.
    • [ASC | DESC] : 정렬 방식
      • ASC : 오름차순
      • DESC : 내림차순
      • 생략하면 오름차순으로 정렬
  • 그룹 함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술한다.
    • WINDOW 함수 : GROUP BY 절을 이용하지 않고, 속성의 값을 집계할 함수를 기술한다.
      • PARTITION BY : WINDOW함수의 적용 범위가 될 속성을 지정한다.
      • ORDER BY : PARTITION안에서 정렬 기준으로 사용할 속성을 지정한다.
    • GROUP BY 절
      • 특정 속성을 기준으로 그룹화하여 겁색할 때 사용한다.
      • 일반적으로 GROUP BY절은 그룹 함수화 함께 사용된다.
    • HAVING 절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정한다.

조건 연산자

  • 비교 연산자
    • = : 같다
    • <> : 같지 않다
    • > : 크다
    • < : 작다
    • >= : 크거나 같다
    • <= : 작거나 같다
  • 논리 연산자
    • NOT, AND, OR
  • LIKE 연산자
    • 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용
      • % : 모든 문자를 대표
      • _ : 문자 하나를 대표
      • # : 숫자 하나를 대표

기본 검색

  • SELECT 절에 원하는 속성을 지정하여 검색한다.
SELECT * FROM 사원;
SELECT 사원.* FROM 사원;

조건 지정 검색

  • WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색한다.
SELECT *
FROM 사원
WHERE 부서 = '기획';

정렬 검색

  • ORDER BY절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.
SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

하위 질의

  • 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.
SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름
		FROM 여가활동
    		WHERE 취미 = '게임');

복수 테이블 검색

  • 여러 테이블을 대상으로 검색을 수행한다.
SELECT 사원.이름, 사원.부서, 여가활동.취미
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

그룹 함수

  • GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.
  • 종류
    • COUNT : 튜플의 수를 구하는 함수
    • SUM : 합계를 구하는 함수
    • AVG : 평균을 구하는 함수
    • MAX : 최대값을 구하는 함수
    • MIN : 최솟값을 구하는 함수
    • STDDEV : 표준편차를 구하는 함수
    • VARIANCE : 분산을 구하는 함수
    • ROLLUP : 그룹별 소계를 구하는 함수, 하위 레벨에서 상위 레벨 순으로 데이터가 집계된다.
    • CUBE : ROLLUP과 유사하지만 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구한다. 상위레벨에서 하위레벨 순으로 데이터가 집계된다.

WINDOW 함수

  • GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성의 값을 집계한다.
  • WINDOW 함수
    • ROW_NUMVER() : 윈도우별 각 레코드에 대한 일련번호를 반환한다.
    • RANK() : 윈도우별 순위를 반환하며, 공동 순위를 반영한다.
    • DENSE_RANK() : 윈도우별 순위를 반환하며, 공동 순위를 무시하고 순위를 부여한다.

WINDOW 함수 이용 검색

  • 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.
예제 : <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오( 단, 순서는 내림차순이며 속성은 'NO'로 할것)
SELECT 상여내역, 상여금, ROW_NUMVER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

그룹 지정 검색

  • GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화 하여 검색한다.
예제 : <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오.
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

집합연산자를 이용한 통합 질의

  • 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다.
  • 표기 형식
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];
  • 2개의 SELECT 문에 기술한 속성들은 개수와 데이터의 유형이 서로 동일해야한다.
  • 집합 연산자의 종류
    • UNION : 합집합, 중복된 행은 한 번만 출력한다.
    • UNION ALL : 합집합, 중복된 행도 그대로 출력한다.
    • INTERSECT : 교집합
    • EXCEPT : 차집합

5. DML - JOIN

JOIN

  • 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
  • 종류
    • INNER JOIN
      • 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분된다.
        • 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 덕을 수 있다.
          • CROSS JOIN(교차 조인)
            • 조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다.
            • 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
    • OUTER JOIN
      • JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
        • LEFT OUTER JOIN
        • RIGHT OUTER JOIN
        • FULL OUTER JOIN

EQUI JOIN

  • JOIN  대상 테이블에서 공통 속성을 기준으로 '='비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN방법이다.
  • 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 한다.
  • EQUI JOIN 에서 연결고리가 되는 공통 속성을 JOIN속성이라고 한다.
WHERE 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

NATURAL JOIN 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;

JOIN ~ USING 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

NON-EQUI JOIN

  • JOIN 조건에 '='를 제외한 나머지 비교 연산자를 사용하는 JOIN 방법
  • 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

LETF OUTER JOIN

  • INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 향의 릴레이션에 있는 튜플들에 NULL값을 붙여서 INNER JOIN결과에 추가한다.
  • 표기 형식
# 1
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
# 2
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

RIGHT OUTER JOIN

  • INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 향의 릴레이션에 있는 튜플들에 NULL값을 붙여서 INNER JOIN결과에 추가한다.
  • 표기 형식
# 1
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
# 2
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

FULL OUTER JOIN

  • LEFT와 RIGHT를 합쳐 놓은 것
  • 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

6. 프로시저 (출제될 확률이 적습니다.)

프로시저

  • SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때 마다 저장한 작업을 수행하도록 하는 절차형 SQL
  • 데이터베이스에 저장되어 수행되기 때문에 Stored 프로시저라고도 불린다.
  • 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용된다.

프로시저의 구성

  • DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
  • BEGIN / END : 프로시저의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
  • SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
  • EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
  • TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

프로시저 생성

  • CREATE PROCEDURE 명령어를 사용한다.
  • 표기 형식
CREATE [OR REPLACE] PROCEDURE 프로시저명
(파라미터명 [IN | OUT | INOUT] 데이터타입, ...)
IS
	변수선언
BEGIN
	명령어;
[COMMIT | ROLLBACK]
END;
  • OR REPLACE
    • 선택적인 예약어
    • 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있다.
  • 프로시저명 : 생성하려는 프로시저의 이름을 지정
  • 파라미터
    • IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
    • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
    • INPUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정
    • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
    • 자료형 : 변수의 자료형을 지정
  • 프로시저 BODY
    • 프로시적의 코드를 기록하는 부분
    • BEGIN에서 시작하여 END로 끝나며 적어도 하나의 SQL문이 있어야 한다.

프로시저 실행

  • 프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC로 사용하기도 한다.
  • 표기 형식
EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

프로시저 제거

  • 프로시저를 제거하기 위해서 DROP PORCEDURE 명령어를 사용한다.
  • 표기 형식
DROP PROCEDURE 프로시저명;

7. 트리거(Trigger) (출제될 확률이 적습니다.)

트리거

  • 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다.
  • 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력등의 목적으로 사용된다.
  • 트리거의 구문에는 DCL을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생한다.

트리거의 구성

  • DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
  • EVENT : 트리거가 실행되는 조건을 명시
  • BEGIN / END : 트리거의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리된다.
  • SQL : DML이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행한다.
  • EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.

트리거의 생성

  • CREATE TRIGGER 명령어를 사용한다.
  • 표기 형식
CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERNCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
	트리거 BODY;
END;
  • OR REPLACE
    • 선택적인 예약어
    • 동일한 트리거의 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있다.
  • 동작시기 옵션 : 트리거가 실행될 때를 지정한다.
    • AFTER : 테이블이 변경된 후에 트리거가 실행
    • BEFORE : 테이블이 변경되기 전에 트리거가 실행
  •  동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정
    • INSERT : 새로운 튜플을 삽입할 때
    • DELETE : 튜플을 삭제할 때
    • UPDATE : 튜플을 수정할 때
  • NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정
    • NEW : 추가되거나 수정에 참여할 튜플들의 집합을 의미
    • OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합을 의미
  • FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미
  • WHEN 조건식
    • 선택적인 예약어
    • 트리거를 적용할 튜플의 조건을 지정
  • 트리거 BODY
    • 트리거의 본문 코드를 입력하는 부분
    • 적어도 하나 이상의 SQL문이 있어야 한다.

트리거의 제거

  • DROP TRIGGER 명령어를 사용한다.
  • 표기 형식
DROP TRIGGER 트리거명;

8. 사용자 정의 함수

사용자 정의 함수

  • 프로시저와 유사하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL이다.
  • DML 문의 호출에 의해 실행된다
  • 예약어 RETURN을 통해 단일 값을 반환하며, 출력 파라미터가 없다.

사용자 정의 함수의 구성

  • DECLARE : 사용자 정의 함수의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
  • BEGIN / END : 사용자 정의 함수의 시작과 종료
  • CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
  • SQL : SELECT문이 삽입되어 데이터 조회 작업을 수행한다.
  • EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의한다.
  • RETURN : 호출 프로그램에 반환할 값이나 변수를 정의한다.

사용자 정의 함수 생성

  • CREATE FUNCTION 명령어를 사용한다.
  • 표기 형식
CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
	사용자 정의함수 BODY;
   	RETURN 반환값;
END;
  • OR REPLACE
    • 선택적인 예약어
    • 동일한 사용자 정의 함수 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있다.
  • 파라미터  
    • IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
    • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
    • 자료형 : 변수의 자료형을 지정
  • 사용자 정의 함수 BODY
    • 사용자 정의 함수의 코드를 기록하는 부분
    • BEGIN에서 시작하여 END로 끝나며 적어도 하나의 SQL문이 있어야 한다.
  • RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출하는 프로그램으로 돌려준다.

사용자 정의 함수 실행

  • 사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.
  • 표기 형식
SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES(사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

사용자 정의 함수 제거

  • DROP FUNCTION 명령어를 사용한다.
  • 표기 형식
DROP FUCTION 사용자 정의 함수명;

9. 제어문

제어문

  • 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
  • IF, LOOP등이 있다.

IF문

  • 조건에 따라 실행할 문장을 달리하는 제어문
  • 조건이 참일때
IF 조건 THEN
	실행할 문장1;
    	실행할 문장2;
    	    ...
END IF;
  • 조건이 참일 때와 거짓일 때 실행할 문장이 다르다.
IF 조건 THEN
	실행할 문장1;
ELSE
	실행할 문장2;
END IF;

LOOP 문

  • 조건에 따라 실행할 문장을 반복 수행하는 제어문
  • 형식
LOOP
	실행할 문장;
    EXIT WHEN 조건;
END LOOP;

10. 커서

커서(Cursor)

  • 쿼리문의 처리 결과가 저장되어있는 메모리 공간을 가리키는 포인터
  • 커서의 수행은 열기, 패치, 닫기의 세 단계로 진행된다.

묵시적 커서

  • DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
  • 커서의 속성을 조회하여 사용된 쿼리정보를 열람하는 것이 가능하다.
  • 속성의 종류
    • SQL%FOUND : 쿼리 수행의 결과로 패치된 튜플 수가 1개 이상이면 TRUE
    • SQL%NOTFOUND : 쿼리 수행의 결과로 패치된 튜플 수가 0개면 TRUE
    • SQL%ROWCOUNT : 쿼리 수행의 결과로 패치된 튜플 수를 반환
    • SQL%ISOPEN
      • 커서가 열린 상태면 TRUE
      • 묵시적 커서는 항상 자동으로 닫혀서 항상 FALSE

명시적 커서

  • 사용자가 직접 정의해서 사용하는 커서
  • 커서는 기본적으로 열기, 패치, 닫기 순으로 이뤄지기에 명시적 커서를 사용하기 위해서는 열기 단계 전에 선언해야 한다.

선언(Declare) 형식

CURSOR 커서명(매개변수1, 매개변수2, ...)
IS
SELECT문;

열기 형식

OPEN 커서명(매개변수1, 매개변수2, ...);

패치 형식

FETCH 커서명 INTO 변수1, 변수2, ...;

닫기 형식

CLOSE 커서명;

11. DBMS 접속

DBMS 접속

  • 응용 시스템을 이용하여 DBMS에 접근하는 것

DBMS 접속 기술

  • DBMS에 접근하기 위해 사용하는 API 또는 API 사용을 편리하게 도와주는 프레임워크
  • DBMS 접속 기술의 종류
    • JDBC
      • JAVA 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API
    • ODBC
      • 개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개방형 API
    • MYBATIS
      • JDBC 코드를 단순화하여 사용할 수 있는 SQL MAPPING 기반 오픈 소스 접속 프레임워크

동적 SQL(DYNAMIC SQL)

  • SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
  • 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있다.
  • 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.

12. ORM(Object-Relational Mapping)

ORM

  • 객체(OBJECT)와 관계형 데이터베이스(RELATIONAL DATABASE)의 데이터를 연결(MAPPING)하는 기술

ORM 프레임 워크

  • ORM을 구현하기 위한 여러 기능들을 제공하는 소프트웨어
  • ORM 프레임워크의 종류
    • Java : JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
    • C++ : ODB, QxOrm 등
    • Python : Django, SQLAchemy, Storm 등
    • .NET : NHibernate, DatabaseObjects, Dapper등
    • PHP : Doctrine, Propel, RedBean 등

13. 쿼리 성능

쿼리 성능 최적화

  • 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화 하는것이다.
  • 쿼리 성능을 최적화하기 전에 성능 축정 도구인 APM을 사용하여 최적화할 쿼리를 선정해야 한다.
    • APM(Application Performance Management / Monitioring)
      • 애플리케이션의 성능관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

옵티마이저

  • 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
  • RBO(Rule based Optimizer) 와 CBO(Cost Based Optimizer)
    • RBO 
      • 데이터베이스 관리자가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
    • CBO
      • 입출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플 개수 등을 종합하여 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용 기반 옵티마이저

실행 계획

  • DBMS의 옵티마이저가 수립한 SQL 코드의 실행절차와 방법
  • EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현된다.

쿼리 성능 최적화 방법

  • SQL문이 더 빠르고 효율적으로 작동하도록 SQL코드와 인덱스를 재구성하는 것을 의미
    • SQL 코드 재구성
      • WHERE 절 추가
      • WHERE 절에 연산자 사용 제한
      • IN을 EXISTS로 대체
      • 힌트로 액세스 경로 및 조인 순서 변경
    • 인덱스 재구성
      • 조회되는 속성과 조건을 고려하여 인덱스 구성
      • 인덱스 추가 및 기존 인덱스의 열 순서 변경
      • 테이블을 참조하는 다른 SQL문으로의 영향 고려
      • IOT 구성 고려
      • 불필요한 인덱스 제거
  • EXISTS
    • 데이터의 존재 여부가 확인되면 검색이 종료 되므로 IN보다 처리 속도가 빠르다.
  • 힌트
    • SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 실행계획에 영향을 줄 수 있는 문장이다.
  • IOT(Index-Organized Table)
    • 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략되어 빠른 조회가 가능하다.
저작자표시 (새창열림)