[정보처리기사 요약 8] 데이터베이스 SQL 완벽 가이드 (JOIN, 트리거 활용법 포함)

본문에 오류가 있을 수 있음을 감안하고 봐주시길 바랍니다.
# 문제 풀이 중 오답노트 하면서 나온 내용을 정리한 것

 

데이터 정의어(Data Definition Language, DDL): DB 구조·데이터 형식·접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어이다. 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장된다.

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의함
ALTER	TABLE에 대한 정의를 변경하는 데 사용함
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함

 

 

CREATE

• 스키마(SCHEMA): 데이터베이스의 구조와 제약 조건에 관한 전반적인 명세를 기술하는 것

CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_ID;

• 도메인(DOMAIN): 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합을 의미한다.

CREATE DOMAIN 도메인_이름 AS 데이터_타입
               [DEFAULT 기본값]
               [CONSTRAINT 제약조건명 CHECK ( VALUE IN( 값1, 값2, ... 또는 범위 ) )];

[ ~ ]: 생략 가능

• 테이블(TABLE): 데이터베이스의 설계 단계에서는 릴레이션이라 부르고, 조작이나 검색 시에는 테이블이라고 부른다.

CREATE TABLE 테이블_이름
               ( 속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
                 [, PRIMARY KEY(기본키_속성명, ...)]  # 기본키 설정
                 [, UNIQUE(대체키_속성명, ...)]  # 대체키 설정
                 [, FOREIGN KEY(외래키_속성명, ...) REFERENCES 참조테이블(기본키_속성명, ...)  # 외래키 설정
                                                                           [ON DELETE 옵션] [ON UPDATE 옵션] ]
                 [, CONSTRAINT 제약조건명 CHECK(조건식) ] );  # 제약 조건 이름 지정 및 제약 조건 정의

        - ON DELETE 옵션: 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정함

            - ON DELETE SET NULL: 참조 테이블 삭제시 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경

            - ON DELETE  SET DEFAULT: 참조 테이블 삭제시 기본 테이블의 관련 튜플의 속성 값을 기본값으로 변경

            - ON DELETE NO ACTION: 참조 테이블의 튜플이 삭제되더라도 기존 테이블의 튜플은 삭제되지 않는다.

        - ON UPDATE 옵션: 참조 테이블의 참조 속성값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정함(예: CASCADE)

• 뷰(VIEW): 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블

CREATE VIEW 뷰_이름[(속성명1, 속성명2, ...)] AS SELECT문 [With Check Option];
# With Check Option: 수정(UPDATE)하거나 삽입(INSERT)할 때, SELECT문의 조건에 위배를 막아준다.

• 인덱스(INDEX): 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조, 문헌의 색인, 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조, 테이블에 붙여진 색인으로 데이터 검색 시 처리속도 향상에 도움이 된다. 대부분의 데이터베이스에서 테이블을 삭제하면 인덱스도 같이 삭제된다.

CREATE [UNIQUE] INDEX 인덱스_이름  # UNIQUE: 중복 값이 없는 속성으로 인덱스 생성
                ON 테이블_이름(속성명1 [ASC | DESC] [, 속성명2 [ASC | DESC], ... ] ) [CLUSTER];

         - CLUSTER: 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식

 

ALTER TABLE: 테이블에 대한 정의를 변경하는 명령문이다.

ALTER TABLE 테이블_이름 ADD 속성명 데이터_타입 [DEFAULT 기본값];  # 새로운 속성을 추가
ALTER TABLE 테이블_이름 ALTER 속성명 [SET DEFAULT 기본값];  # 특정 속성의 기본값 변경
ALTER TABLE 테이블_이름 MODIFY 속성명 새로운_데이터_타입;  # 특정 속성의 데이터 타입 및 크기 변경
ALTER TABLE 테이블_이름 DROP COLUMN 속성명 [CASCADE | RESTRICT];  # 특정 속성명 삭제

         - CASCADE: 제거할 요소를 참조하는 다른 모든 개체도 같이 삭제한다.

         - RESTRICT: 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소한다.

 

DROP

DROP SCHEMA 스키마_이름 [CASCADE | RESTRICT];
DROP DOMAIN 도메인_이름 [CASCADE | RESTRICT];
DROP TABLE 테이블_이름 [CASCADE | RESTRICT];
DROP VIEW 뷰_이름 [CASCADE | RESTRICT];
DROP INDEX 인덱스_이름 [CASCADE | RESTRICT];
ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약조건명;
                         # 특정 규칙(PRIMARY KEY, FOREIGN KEY, CHECK 등)만 골라서 해제

        - TRUNCATE TABLE 테이블_이름;  # 모든 데이터는 삭제되지만 테이블 구조는 남아있음, DELETE에 비해 빠르게 데이터를 제거하는 것이 가능하다, ROLLBACK으로 삭제한 데이터를 살릴 수 없다.

 

 

 

데이터 제어어(Data Control Language, DCL): 데이터의 보안, 무결성, 회복, 병 제어 등을 정의하는 데 사용하는 언어이다. 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구함
GRANT	데이터베이스 사용자에게 사용 권한을 부여함
REVOKE	데이터베이스 사용자의 사용 권한을 취소함

 

 

GRANT/REVOKE: 데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어이다. 권한 부여를 위한 명령어(GRANT)와 권한 취소를 위한 명령어(REVOKE)가 있다.

• 사용자 등급 지정 및 해제

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

         - 사용자 등급: DBA(관리자), RESOURCE(테이블 생성 가능자), CONNECT(사용자)

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

GRANT 권한_리스트 ON 테이블_이름 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 테이블_이름 FROM 사용자 [CASCADE];

         - 권한 종류: ALL, SELECT, INSERT, DELETE, UPDATE 등

         - WITH GRANT OPTION: 부여받은 권한을 다른 사용자들에게 다시 부여할 수 있는 권한을 부여함

         - GRANT OPTION FOR: 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함(해당 부여 권한만 취소함)

 

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

 

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

 

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

• Savepoint P1;   ->   Rollback to P1;(P1으로 돌아감)

 

 

데이터 조작어(Data Manipulation Language, DML): 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는 데 사용되는 언어이다. 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.

명령어	기능
SELECT	테이블에서 튜플을 검색함
INSERT	테이블에 새로운 튜플에 삽입함
DELETE	테이블에서 튜플을 삭제함
UPDATE	테이블에서 튜플의 내용을 갱신함

 

 

삽입문(INSERT INTO ~ ): 기본 테이블에 새로운 튜플을 삽입할 때 사용한다.

INSERT INTO 테이블_이름[( 속성명1, 속성명2, ... )] VALUES ( 데이터1, 데이터2, ... );

        - 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.

        - 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.

        - VALUES 대신에 SELECT ~ FROM 문의 출력을 삽입 가능

 

삭제문(DELETE FROM ~ ): 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용한다.

DELETE FROM 테이블_이름 [ WHERE 조건 ];

        - 모든 레코드 삭제는 WHERE 조건문을 생략한다.

        - 모든 레코드를 삭제하더라도 테이블 구조는 남아있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP과 다르다.

 

갱신문(UPDATE ~ SET ~ ): 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 사용한다.

UPDATE 테이블_이름 SET 속성명 = 데이터 [, 속성명 = 데이터, ... ] [ WHERE 조건 ];

        - WHERE 조건문을 생략할 시 해당 속성의 모든 값들이 해당 데이터로 변경된다.

 

SELECT

SELECT [DISTINCT] [테이블_이름] 속성명 [AS 별칭] [, 테이블명, 속성명, ... ]
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER ([PARTITION BY 속성명1, 속성명2, ...] ORDER BY 속성명3, ... ) ]
FROM 테이블_이름[, 테이블_이름, ... ]
[WHERE 조건]
[GROUP BY 속성명1, 속성명2, ... ]
[HAVING 조건1, ... ]
[ORDER BY 속성명 [ASC | DESC] ];

        - DISTINCT: 중복된 튜플이 있으면 그 중 첫 번째 한 개만 표시함

        - 속성명: 검색하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정함

        - AS(Alias): 속성이나 연산의 이름을 다른 이름(별칭)으로 표시하기 위해 사용함

        - WINDOW 함수: GROUP BY를 쓰지 않고도 데이터의 순위나 집계를 계산할 때 사용함

           -- PARTITION BY: WINDOW 함수의 적용 범위(그룹)를 지정함

           -- ORDER BY: PARTITION 안에서 정렬 기준으로 사용할 속성을 지정함

        - FROM 절: 검색할 데이터가 들어있는 테이블을 지정함

        - WHERE 절: 검색할 조건식을 기술함

        - GROUP BY 절: 특정 속성을 기준으로 데이터를 그룹화하여 검색할 때 사용함. (COUNT, SUM 등의 그룹 함수와 함께 사용)

        -  HAVING 절: GROUP BY로 묶인 그룹에 대한 조건을 지정함

        - ORDER BY 절: 최종 출력될 데이터를 정렬할 때 사용함

           -- 속성명: 정렬의 기준이 되는 속성명을 기술함

           -- [ASC | DESC]: ASC는 오름차순(생략 시 기본값), DESC는 내림차순 정렬

 

 

WHERE 조건 연산자

• 비교 연산자

연산자	=	<>	>	<	>=	<=
의미	같다	같지 않다	크다	작다	크거나 같다	작거나 같다

• 논리 연산자: NOT, AND, OR
• LIKE 연산자: 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는지 튜플 검색시 사용한다.

대표 문자	%	_	#
의미	모든 문자를 대표함	문자 하나를 대표함	숫자 하나를 대표함

 

• WHERE 조건 < ALL ( ~ ): ~ 값 중 최솟값보다 작은 값을 출력하시오.
• WHERE 조건 > ALL ( ~ ): ~ 값 중 최댓값보다 큰 값을 출력하시오.
• WHERE 조건 < ANY ( ~ ): ~ 값 중 최댓값보다 작은 값을 출력하시오. # ANY = SOME
• WHERE 조건 > ANY ( ~ ): ~ 값 중 최값보다 큰 값을 출력하시오.
• WHERE 조건 = ANY ( ~ )   ==   WHERE 조건 IN ( ~ )
• WHERE 조건 <> ALL ( ~ )   ==   WHERE 조건 NOT IN ( ~ )
• 속성(열) BETWEEN 범위1 AND 범위 2 == 속성(열) >= 범위 1 AND 속성(열) <= 범위2

 

 

 

하위 질의: 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.

SELECT 속성명1[, 속성명2, ... ] FROM 테이블_이름
                                                 WHERE 조건 (SELECT 속성명1[, 속성명2, ... ] FROM 테이블_이름);

 

 

GROUP BY 함수: 그룹별로 속성의 값을 집계할 때 사용한다.

함수	기능
COUNT(속성명)	그룹별 튜플의 수를 구하는 함수(*: NULL 값도 COUNT, 속성명: NULL 값은 제외후 COUNT)
SUM(속성명)	그룹별 합계를 구하는 함수
AVG(속성명)	그룹별 평균을 구하는 함수
MAX(속성명)	그룹별 최댓값을 구하는 함수
MIN(속성명)	그룹별 최솟값을 구하는 함수
STDDEV(속성명)	그룹별 표준편차를 구하는 함수
VARIANCE(속성명)	그룹별 분산을 구하는 함수
ROLLUP(속성명1, 속성명2, ...)	인수로 주어진 속성을 대상으로 그룹별로 소계를 구하는 함수
CUBE(속성명1, 속성명2, ...)	인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수
GROUPING SETS(속성명1, 속성명2, ...)	인수로 주어진 속성들에 대한 개별적인 그룹별 집계를 구하는 함수

 

 

WINDOW 함수: GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계한다. 함수의 인수로 집계할 범위가 되는데 이를 윈도우(WINDOW)라고 부른다.

• ROW_NUMBER(): 윈도우별로 각 레코드에 대한 일련번호를 반환한다.
• RANK(): 윈도우별로 순위를 반환하며 공동 순위를 반영한다.(순위 중복 O)
• DENSE_RANK(): 윈도우별로 순위를 반환하며 공동 순위는 무시하고 순위를 부여한다.

 

 

집합 연산자를 이용한 통합 질의: 2개 이상의 테이블의 데이터를 하나로 통합한다.

SELECT 속성명1[, 속성명2, ...] FROM 테이블_이름
[ UNION | UNION ALL | INTERSECT | EXCEPT ]
SELECT 속성명1[, 속성명2, ...] FROM 테이블_이름;

        - 두 개의 SELECT 문에 기술한 속성의 개수와 데이터 유형이 같아야 한다.

집합 연산자	설명	집합 종류
UNION	두 SELECT문의 조회 결과를 통합하여 모두 출력하고 중복된 행은 한 번만 출력한다.	합집합
UNION ALL	두 SELECT문의 조회 결과를 통합하여 모두 출력하고 중복된 행도 그대로 출력한다.	합집합
INTERSECT	두 SELECT문의 조회 결과 중 공통된 행만 출력한다.	교집합
EXCEPT	첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행을 출력함	차집합

 

 

JOIN: JOIN은 주로 FROM 절에서 2개 이상의 테이블을 결합할 때 사용한다.

• INNER JOIN: 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분되며 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 얻을 수 있다.
  • THETA JOIN: JOIN에 참여하는 두 릴레이션의 속성 값을 비교하여 조건을 만족하는 튜플만 반환하는 조인
  • EQUI JOIN: 조인에 사용되는 조건 중 '=' 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성(동등 조인)

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1, 테이블_이름2[, ...]
WHERE 테이블_이름1.속성명 = 테이블_이름2.속성명;

• • NATURAL JOIN: JOIN 조건이 '='일 때 동등한 속성이 두 번 나타나게 되는데 이 중복된 속성을 제거하여 같은 속성을 한번만 표기하는 조인(자연 조인)

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 NATURAL JOIN 테이블_이름2  # 동일한 속성명이 1개일 경우

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 JOIN 테이블_이름2 USING(속성명)  # 동일한 속성명이 2개 이상일 경우

• • NON-EQUI JOIN: JOIN 조건에 '=' 조건이 아닌 나머지 비교 연산자(즉, <, >, <=, >=, <>)

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1, 테이블_이름2[, ...]
WHERE NON-EQUI JOIN 조건;

• OUTER JOIN: 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법이다.
  • LEFT OUTER JOIN: INNER JOIN의 결과를 구한 후 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 LEFT OUTER JOIN 테이블_이름2 ON 테이블_이름1.속성명 = 테이블_이름2.속성명;

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 ,테이블_이름2
WHERE 테이블_이름1.속성명 = 테이블_이름2.속성명(+);

• • RIGHT OUTER JOIN: INNER JOIN의 결과를 구한 후 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플에 NULL 값을 붙여서 INNER JOIN의 결과를 추가한다.

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 RIGHT OUTER JOIN 테이블_이름2 ON 테이블_이름1.속성명 = 테이블_이름2.속성명;

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 ,테이블_이름2
WHERE 테이블_이름1.속성명(+) = 테이블_이름2.속성명;

• • FULL OUTER JOIN: LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것이다.

SELECT [테이블_이름1.]속성명1, [테이블_이름2.]속성명1[, ...]
FROM 테이블_이름1 FULL OUTER JOIN 테이블_이름2 ON 테이블_이름1.속성명 = 테이블_이름2.속성명;

 

 

 

트리거(Trigger): 데이터베이스 시스템에서 데이터의 삽입·삭제·갱신 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다. 트리거는 데이터베이스에 저장되며 데이터 변경 및 무결성 유지·로그 메시지 출력 등의 목적으로 사용된다. 트리거 구문에는 DCL을 사용할 수 없다.

• 트리거의 생성

CREATE [OR REPLACE] TRIGGER 트리거_이름 [동작시기 옵션] [동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블_이름
FOR EACH ROW
[WHERE 조건식]
BEGIN
    트리거 BODY;
END

        - OR REPLACE: 동일한 트리거 이름이 이미 존재하는 경우 기존의 트리거를 대체할 수 있다.

        - 동작시기 옵션: 트리거가 실행될 때를 지정한다.

           -- AFTER: 테이블이 변경된 후에 트리거가 실행된다.

           -- BEFORE: 테이블이 변경되기 전에 트리거가 실행된다.

        - 동작 옵션: 트리거가 실행되기 위한 작업의 종류를 지정한다.

           -- INSERT: 테이블에 새로운 튜플이 삽입할 때 트리거가 실행된다.
           -- DELETE: 테이블에 튜플이 삭제될 때 트리거가 실행된다.
           -- UPDATE: 테이블에 튜플이 수정될 때 트리거가 실행된다.

        - NEW | OLD: 트리거가 적용될 테이블의 별칭을 지정한다.

           -- NEW: 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미한다.

           -- OLD: 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미한다.

        - FOR EACH ROW: 각 튜플마다 트리거를 적용한다는 의미이다.

        - WHERE 조건식: 트리거를 적용할 튜플의 조건을 지정한다.

        - 트리거 BODY: 트리거의 본문 코드를 입력하는 부분이다. 적어도 하나 이상의 SQL문이 있어야 한다.

 

• 트리거의 제거

DROP TRIGGER 트리거_이름;

 

 

옵티마이저(Optimizer): SQL의 실행 속도와 효율성을 위해 최적의 실행 계획을 수립하는 모듈이다. 데이터 사전의 통계 정보를 활용하여 비용(Cost)을 계산하는 비용 기반(CBO) 방식이 주로 사용된다.