[실기] 8장. SQL 응용

102(A) SQL-DDL

DDL(Data Define Language, 데이터 정의어): DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어

- 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장됨

<DDL의 3가지 유형>

- CREATE: SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의

- ALTER: TABLE에 대한 정의를 변경

- DROP: SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제

 

CREATE TABLE: 테이블을 정의하는 명령문

<표기 형식>

CREATE TABLE 테이블명

(속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...

[, PRIMARY KEY(기본키_속성명, ...) ]

[, UNIQUE(대체키_속성명, ...)]

[, FOREIGN KEY(외래키_속성명, ...)]

REFERENCES 참조테이블(기본키_속성명, ...)]

[ON DELETE 옵션]

[ON UPDATE 옵션]

[, CONSTRAINT 제약조건명] [CHECK (조건식)];

기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정

- PRIMARY KEY: 기본키로 사용할 속성 지정

- UNIQUE: 대체키로 사용할 속성을 지정, 중복된 값 가질 수 X

- FOREIGN KEY ~ REPERENCES ~: 외래키로 사용할 속성을 지정

• ON DELETE 옵션: 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정
• ON UPDATE 옵션: 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정
• CONSTRAINT: 제약 조건의 이름을 지정
• CHECK: 속성 값에 대한 제약 조건을 정의

CREATE INDEX: 인덱스를 정의하는 명령문

<표기 형식>

CREATE [UNIQUE] INDEX 인덱스명

ON 테이블명(속성명 [ASC | DESC] [속성명 [ASC | DESC])

[CLUTER];

UNIQUE

- 사용된 경우: 중복 값이 없는 속성으로 인덱스를 생성

- 생략된 경우: 중복 값을 허용하는 속성으로 인덱스를 생성

정렬 여부 지정

- ASC: 오름차순 정렬

- DESC: 내림차순 정렬

- 생략된 경우: 오름차순으로 정렬

CLUSTER: 사용하면 인덱스가 클러스터드 인덱스로 설정

 

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

 

103(B) SQL - DCL

DCL(Data Control Language, 데이터 제어어): 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어

- DBA(데이터 관리자)가 데이터 관리를 목적으로 사용

<DCL의 종류>

- COMMIT: 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, DB 조작 작업이 정상적으로 완료됐음을 관리자에게 알림

- ROLLBACK: DB 조작 작업이 비정상적으로 종료되었을 때 원래의 상태로 복구

- GRAINT: DB 사용자에게 사용 권한을 부여

 

GRAINT / REVOKE

DB 관리자가 DB 사용자에게 권한을 부여(GRANT)하거나 취소(REVOKE)하기 위한 명령어

<사용자등급 지정 및 해제>

- GRAINT 사용자등급 TO 사용자 ID_리스트 [IDENTIFIED BY 암호];

- REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

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

- GRAINT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];

- REVOKE [GRAINT

 

ROLLBACK: 변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 DB를 이전 상태로 되돌리는 명령어

트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 DB에 반영되는 비일관성 상태가 될 수 있기 때문에 일부분만 완료된 트래잭션은 롤백되어야 함

 

104(A) SQL - DML

DML(Data Manipulation Language, 데이터 조작어): DB 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

- DB 사용자와 DB 관리 시스템 간의 인터페이스를 제공

<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 조건];

 

105(A) DML - SELECT-1

<일반 형식>

SELECT [PREDICATE] [테이블명],속성명 [AS 별칭][, [테이블명,],속성명, ...]

[, 그룹함수(속성명) [AS 별칭]]

[, Window 함수 OVER (PARTITION BY 속성명1, 속성명2, ...                            ORDER BY 속성명3, 속성명4, ...)]

FROM 테이블명[, 테이블명, ...]

[WHERE 조건]

[GROUP BY 속성명, 속성명, ...]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC]];

SELECT절

- PREDICATE: 검색할 튜플 수를 제한하는 명령어를 기술

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

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

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

FROM절: 검색할 데이터가 들어있는 테이블 이름을 기술

WHERE절: 검색할 조건을 기술

ORDER BY절: 데이터를 정렬하여 검색할 때 사용

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

- [ASC | DESC] : 정렬 방식으로, 'ASC'는 오름차순, 'DESC'는 내림차순 / 생략하면 오름차순으로 지정

 

조건 연산자

비교 연산자

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

 

논리 연산자: NOT, AND, OR

 

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

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

 

기본 검색: SELECT 절에 원하는 속성을 지정하여 검색

 

조건 지정 검색: WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색

 

정렬 검색: ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색

 

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

 

106(A) DML - SELECT-2

그룹 함수: GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용

함수	기능
COUNT(속성명)	그룹의 튜플 수를 구하는 함수
SUM(속성명)	그룹의 합계를 구하는 함수
AVG(속성명)	그룹의 평균을 구하는 함수
MAX(속성명)	그룹의 최대값을 구하는 함수
MIN(속성명)	그룹의 최소값을 구하는 함수
STDDEV(속성명)	그룹별 표준편차를 구하는 함수
VARIANCE(속성명)	그룹별 분산을 구하는 함수
ROLLUP(속성명, 속성명, ...)	인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수 속성의 개수가 n개이면, n+1 레벨까지, 하위 레벨에서 상우 레벨 순으로 데이터가 집계됨
CUBE(속성명, 속성명, ...)	ROLLUP과 유사한 형태지만 CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함 속성의 개수가 n개이면, 2제곱 레벨까지, 상위 레벨에서 하위 레벨 순으로 데이터가 집계됨

 

그룹 지정 검색: GROUP BY절에 지정한 속성을 기준으로 자료를 그룹화하여 검색

 

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

<표기 형식>

SELECT 속성명1, 속성명2, ...

FROM 테이블명

UNION | UNION ALL | INTERSECT | EXCEPT

SELECT 속성명1, 속성명2, ...

FROM 테이블명

[ORDER BY 속성명 [ASC | DESC]];

- 두 개의 SELECT문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 함

 

<집합 연산자의 종류(통합 질의의 종류)>

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

 

107(B) DML - JOIN

INNER JOIN - EQUI JOIN, NON-EQUI JOIN으로 구분

조건이 없는 INNER JOIN 수행 시 CROSS JOIN과 동일한 결과

EQUI JOIN

- THETA JOIN(세타 조인): JOIN에 참여하는 두 릴레이션의 속성 값을 비교하여 조건을 만족하는 튜플만 반환하는 조인

- 조인에 사용되는 조건에는 =, ≠, <, ≤, >, ≥

- EQUI JOIN은 조인에 사용되는 조건 중 = 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성

- NATURAL JOIN: EQUI JOIN에서 JOIN 조건이 '='일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법

- JOIN 속성: EQUI JOIN에서 연결 고리가 되는 공통 속성

 

<WHERE절을 이용한 EQUI JOIN의 표기 형식>

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...

FROM 테이블명1, 테이블명2, ...

WHERE 테이블명1, 속성명 = 테이블명2.속성명

 

<NATURAL JOIN절을 이용한 EQUI JOIN의 표기 형식>

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...

FROM 테이블명1 NATURAL JOIN 테이블명2;

 

<JOIN ~ USING절을 이용한 EQUI JOIN의 표기 형식>

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...

FROM 테이블명1 JOIN 테이블명2 USING(속성명);

 

NON-EQUI JOIN

- JOIN 조건에 '=' 조건이 아닌 나머지 비교 연산자 즉, >, <, <>, >=, <= 연산자를 사용

<표기 형식>

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...

FROM 테이블명1, 테이블명2, ...

WHERE (NON-EQUI JOIN 조건);

 

OUTER JOIN: 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법

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

<표기 형식>

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

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

 

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

<표기 형식>

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

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

 

FULL OUTER JOIN: LEFT OUTER JOIN + RIGHT OUTER JOIN

- INNER JOIN 결과 구한 후, 좌측 항 릴레이션의 튜플들에 대해 우측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여 INNER JOIN 결과에 추가

- 그리고 유사하게 우측 항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여 INNER JOIN 결과에 추가

<표기 형식>

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

 

108(C) 트리거(Trigger)