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

DDL(DATA DEFINE LANGUAGE, 데이터 정의어)

DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

• DDL의 3가지 유형
  • CREATE : SCHEMA, DOMAIN, TABLE, VIEW,INDEX를 정의함
  • ALTER : TABLE에 대한 정의를 변경하는 데 사용
  • DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함

DDL - CREATE DOMAIN

도메인을 정의하는 명령문

CREATE DOMAIN 도메인명 [AS] CHAR(1)
    [DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];

• 데이터타입 : SQL에서 지원하는 데이터 타입
• 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값

CREATE DOMAIN 직위 VARCHAR2(10)
DEFAULT '사원'
CONSTRAINT VALID-직위 CHECK (
        VALUE IN ('사원', '대리', '과장', '부장', '이사', '사장'));

DDL - CREATE TABLE

테이블을 정의하는 명령문

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

• PRIMARY KEY : 기본키로 사용할 속성을 지정함
• UNIQUE : 대체키로 사용할 속성을 지정함, 중복값 가질 수 없음
• FOREIGN KEY ~ REFERENCES : 외래키로 사용할 속성을 지정함
  • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제 되었을 때 기본 테이블에 취해야할 사항을 지정
  • ON UPDATE 옵션 : 참조 속성 값이 변경 되었을 때 기본테이블에 취해야할 사항을 지정
• CONSTRAINT : 제약 조건의 이름을 지정함
• CHECK : 속성 값에 대한 제약조건을 정의함

DDL - CREATE VIEW

뷰를 정의하는 명령문

• <고객> 테이블에서 '주소'가 안산시인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하시오.

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

DDL - CREATE INDEX

인덱스를 정의하는 명령문

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC|DESC][,속성명 [ASC|DESC]]
[CLUSTER];

• UNIQUE
  • 사용된 경우 : 중복값이 없는 속성으로 인덱스 생성
  • 생략된 경우 : 중복값을 허용하는 속성으로 인덱스 생성
• 정렬 여부 지정
  • ASC : 오름차순 정렬
  • DESC : 내림차순 정렬
  • 생략된 경우 : 오름차순으로 정렬됨
• CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨

DDL - ALTER TABLE

테이블에 대한 정의를 변경하는 명령문

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• ADD : 새로운 속성(열)을 추가할 때 사용
• ALTER : 특정 속성의 DEFAULT 값을 변경할 때 사용
• DROP : 특정 속성을 삭제할 때 사용함

DDL - 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 : 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소함

DCL

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

DCL - GRANT/REVOKE

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

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE 등
• WITH GRANT OPTION : 부여 받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
• CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

DCL - ROLLBACK

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

DCL - SAVEPOINT

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

DML

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

DML - 삽입문 INSERT INTO

삽입문은 기본적으로 테이블에 새로운 튜플을 삽입할 때 사용한다

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

DML - 삭제문 DELETE FROM

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

DELETE FROM 테이블명 [WHERE 조건];

DML - 갱신문 UPDATE ~ SET ~

갱신문은 기본 테이블에 있는 튜플 중에서 특정 튜플의 내용을 변경할 때 사용한다.

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

DML - SELECT

SELECT [DISTINCT] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC];

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

• UNION : 합집합(중복행 제거)
• UNION ALL : 합집합(중복된 행 그대로 출력)
• INTERSECT : 교집합
• EXCEPT : 차집합

JOIN

2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.

• INNER JOIN
• OUTER JOIN

트리거

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

커서

• 쿼리문의 처리 결과가 저장되어있는 메모리 공간을 가리키는 포인터(POINTER)
• 묵시적 커서 : DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
• 명시적 커서 : 사용자가 직접 정의해서 사용하는 커서
• 커서의 속성을 조회하여 사용된 쿼리정보를 열람하는 것이 가능하다.
• 속성의 종류
  • SQL%FOUND : 쿼리 수행의 결과로 패치(FETCH)된 튜플 수가 1개 이상이면 TRUE
  • SQL%NOTFOUND : 쿼리 수행의 결과로 패치(FETCH)된 튜플 수가 0개면 TRUE
  • SQL%ROWCOUNT : 쿼리 수행의 결과로 패치(FETCH)된 튜플 수를 반환
  • SQL%ISOPEN : 커서가 열린(OPEN) 상태면 TRUE, 묵시적 커서는 항상 자동으로 닫혀서 항상 FALSE

DBMS 접속 기술의 종류

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

동적 SQL(DYNAMIC SQL)

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

ORM(OBJECT-RELATIONAL MAPPING)

• 객체(OBJECT)와 관계형 데이터베이스(RELATIONAL DATABASE)의 데이터를 연결(MAPPING)하는 기술을 의미한다.
• JAVA : JPA, HIBERNATE, ECLIPSELINK, DATANUCLEUS, EBEAN
• C++ : ODB, QXORM
• PYTHON : DJANGO, SQLALCHEMY, STORM
• .NET : NHIBERNATE, DATABASEOBJECTS, DAPPER
• PHP : DOCTRINE, PROPEL, REDBEAN

쿼리 성능 관련 용어

• APM(APPLICATION PERFORMANCE MANAGEMENT/MONITORING)
  애플리케이션의 성능관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구
• 옵티마이저
  • 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아주는 모듈
  • RBO(RULE BASED OPTIMIZER) : 데이터베이스 관리자가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • CBO(COST BASED OPTIMIZER) : 입출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플 개수 등을 종합하여 각 DBMS마다 고유의 알고리즘에 따라 산출되는 '비용'으로 최적의 경로를 찾는 비용 기반 옵티마이저
• 실행 계획(EXECUTION PLAN)
  DBMS의 옵티마이저가 수립한 SQL 코드의 실행절차와 방법이다.
• 힌트(HINT)
  SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 실행계획에 영향을 줄 수 있는 문장이다.
• IOT(INDEX-ORGANIZED TABLE)
  인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정을 생략하여 빠른 조회가 가능하도록 구성한 테이블이다.