[정보처리기사 필기] 3과목 - DDL, DML, DCL

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

SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어

• 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의
• 데이터베이스 관리자나 데이터베이스 설계자가 사용
• 유형

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

 

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

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용되는 언어

• 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공
• 유형

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플을 검색
INSERT	테이블에 새로운 튜플을 삽입함
DELETE	테이블에서 조건에 맞는 튜플을 삭제함
UPDATE	테리블에서 조건에 맞는 튜플의 내용을 변경함

 

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

DCL은 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는 데 사용되는 언어

• 데이터베이스 관리자가 데이터 관리를 목적으로 사용함
• 종류

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

 

CREATE TABLE

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

• PRIMARY KEY : 기본키로 사용할 속성 또는 속성의 집합을 지정
• UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을  지정, 중복 허용 X
• FOREIGN KEY ~ REFERENCES ~ : 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정함
  • 외래키 지정 시 참조 무결성의 CASCADE 법칙이 적용
  • ON DELETE : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정함
  • ON UPDATE : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정함
  • ON DELETE, ON UPDATE 옵션에는 NO ACTION, CASCADE, SET NULL, SET DEFAULT가 있음

 

• CONSTRAINT : 제약 조건의 이름을 지정함, 이름을 지정할 필요가 없으면 CHECK절만 사용하여 속성 값에 대한 제약 조건을 명시
• CHECK : 속성 값에 대한 제약 조건을 정의

 

ALTER TABLE

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

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

 

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

 

GRANT/REVOKE

// 사용자등급 지정 및 해제
GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

// 테이블 및 속성에 대한 권한 부여 및 취소
GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

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

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

 

COMMIT

트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성(Consistency) 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영해야 할 때 사용하는 명령

• COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있음

 

ROLLBACK

아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어

• 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency)인 상태를 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(Rollback)되어야 함

 

삽입문(INSERT INTO ~)

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

• 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 함
• 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있음
• SELECT문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있음

 

삭제문(DELETE FROM ~)

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

• 모든 레코드 삭제 시 WHERE 절을 생략
• 테이블 구조는 남아 있기 때문에 테이블을 완전히 제거하는 DROP과는 다름

 

갱신문(UPDATE ~ SET ~)

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

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

 

검색문(SELECT ~ FROM ~)

형식 1

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

// Ex
SELECT * FROM student;
SELECT * FROM student where name="홍길동";

- PREDICATE : 불러올 튜플 수를 제한할 명령오를 기술

• ALL : 모든 튜플을 검색할 때 지정, 주로 생략함
• DISTINCT : 중복된 튜플이 있으면 그중 첫 번째 한 개만 ㄱ머색함
• DISTINCTROW : 중복된 튜플을 제거하고 한 개 만 검색하지만 선택된 속성의 값이 아닌, 튜플 전체를 대상으로 함

 

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

• '*' : 모든 속성
• 두 개 이상의 테이블을 대상으로 검색할 때는 '테이블명.속성명'으로 표현

- AS : 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용

- ORDER BY : 특정 속성을 기준으로 정렬하여 검색할 때 사용

• 속성명 : 정렬의 기준이 되는 속성명을 기술
• [ASC | DESC] : 정렬방식을 설정, ASC는 오름차순, DESC는 내림차순, 생략 시 오름차순

 

연산자

 

LIKE 연산자 : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용

// 성이 '김'인 사람의 튜플 검색
SELECT * FROM 사원 WHERE 이름 LIKE “김%”;
// 성이 '김'인 사람 중 이름이 외자인 사람의 튜플 검색
SELECT * FROM 사원 WHERE 이름 LIKE “김_”;

 

하위 질의

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

// 취미가 힙합인 사원의 이름과 주소 검색
SELECT 이름, 주소 FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '힙합')
// 단 이 쿼리문은 <여가활동> 테이블의 기본키가 <사원> 테이블의 외래키여야 함

 

형식 2

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, …]
[,그룹함수(속성명)[AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY 속성명1, 속성명2, … ORDER BY 속성명3, 속성명4, …) [AS 별칭]]
FROM 테이블명[, 테이블명, …]
[WHERE 조건]
[GROUP BY 속성명, 속성명, …]
[HAVING 조건];

- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술

• COUNT(속성명) : 그룹별 튜플 수를 구하는 함수
• SUM(속성명) : 그룹별 합계를 구하는 함수
• AVG(속성명) : 그룹별 평균을 구하는 함수
• MAX(속성명) : 그룹별 최댓값을 구하는 함수
• MIN(속성명) : 그룹별 최솟값을 구하는 함수
• STDDEV(속성명) : 그룹별 표준편차를 구하는 함수
• VARIANCE(속성명) : 그룹별 분산을 구하는 함수

 

- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값을 집계할 함수를 기술

• PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정함
• ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정

 

- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 사용, 일반적으로 그룹 함수와 함께 사용

- HAVING절 : GROUP BY와 함께 사용, 그룹에 대한 조건을 지정

// <상여금> 테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수
SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

 

집합 연산자 : 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함

SELECT * FROM 사원
UNION
SELECT * FROM 직원;

SELECT * FROM 사원
INTERSECT
SELECT * FROM 직원;

 

INNER JOIN

조건 없는 INNER JOIN을 수행하려면 CROSS JOIN과 동일한 결과를 얻을 수 있음

EQUI JOIN

• JOIN 대상 테이블에서 공통 속성을 기준으로 '='(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 방법
• 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 대상 테이블에서 공통 속성을 기준으로 (>, >=, <, <= 등) 비교가 아닌 것에 의해 행을 연결하여 결과를 생성하는 방법
• 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용됨

SELECT 테이블1.컬럼명, 테이블2.컬럼명,...
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼명1 BETWEEN 테이블2.컬럼명1 AND 테이블2.컬럼명2;

 

트리거(Trigger)

데이터베이스 시스템에서 데이터의 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

• 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용
• 구문에는 DCL을 사용할 수 없고, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류 발생
• 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의가 필요함

 

DBMS 접속 기술

DBMS에 접근하기 위해 사용하는 API, API의 사용을 편리하게 도와주는 프레임워크 등

 

JDBC(Java Database Connectivity)

• Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API
• 1997년 2월 썬 마이크로시스템에서 출시
• 접속하려는 DBMS에 대한 드라이버가 필요

 

ODBC(Open Database Connectivity)

• 데이터베이스에 접근하기 위한 표준 개방형 API, 개발 언어에 관계없이 사용 가능
• 1992년 9월 마이크로소프트에서 출시
• DBMS에 맞는 드라이버가 필요하나, 접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용해 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해 주므로 DBMS의 종류를 몰라도 됨

 

MyBatis

• JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크
• JDBC를 사용할 때 다양한 메서드를 호출하고 해제하는 기능을 간소화하고 접속 기능을 강화함
• SQL 문장을 분리하여 XML 파일을 만들고 Mapping을 통해 SQL을 실행
• SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 많이 사용

 

ORM(Object-Relational Mapping)

객체지향 프로그래밍의 객체와 관계형 데이터베이스의 데이터를 연결하는 기술

• 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결
• 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적으므로 재사용 및 유지보수가 용이
• SQL 코드를 직접 입력하지 않고 선언문이나 할당 같은 부수적인 코드가 생략되기 때문에 직관적이고 간단하게 데이터 조작 가능

 

쿼리 성능 최적화의 개요

데이터 입, 출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것

• 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM을 사용하여 최적화할 쿼리를 선정해야 함
• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성

 

RBO(Rule Based Optimizer) vs CBO(Cost Based Optimizer)

	RBO	CBO
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙련도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡함
고려사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성

 

데이터 전환의 정의

데이터 전환이란 운영 중인 기존 정보 시스템에 축적되어 있는 데이터를 추출(Extraction)하여 새로 개발할 정보 시스템에서 운영 가능하도록 변환(Transformation) 한 후, 적재(Loading)하는 일련의 과정

• 데이터 전환을 ETL(Extraction, Transformation, Load)라고 함(추출, 변환, 적재 과정)
• 데이터 전환을 데이터 이행(Data Migration) 또는 데이터 이관이라고도 함

 

데이터 검증

원천 시스템의 데이터를 목적 시스템의 데이터로 전환하는 과정이 정상적으로 수행되었는지 여부를 확인하는 과정

• 데이터 전환 검증은 검증 방법과 검증 단계에 따라 분류할 수 있음

 

오류 데이터 정제

오류 관리 목록의 각 항목을 분석하여 원천 데이터를 정제하거나 전환 프로그램을 수정하는 것

 

오류데이터 분석

• 오류 관리 목록의 오류 데이터를 분석하여 오류 상태, 심각도, 해결 방안을 확인 및 기재
• 상태

Open	오류가 보고만 되고 분석되지 않은 상태
Assigned	오류의 영향 분석 및 수정을 위해 개발자에게 오류를 전달한 상태
Fixed	개발자가 오류를 수정한 상태
Closed	수정된 오류에 대해 테스트를 다시 했을 때 오류가 발견되지 않은 상태
Deferred	오류 수정을 연기한 상태
Classified	보고된 오류를 관련자들이 확인했을 때 오류가 아니라고 확인된 상태