[정보처리기사] 3과목 데이터베이스 구축 : 3장. SQL 응용

1. SQL의 개념
2. DDL
3. DCL
4. DML
5. DML - SELECT-1 ***
6. DML - JOIN

 

 

1. SQL의 개념

 

SQL의 개요

- 국제 표준 데이터베이스 언어이며, 많은 회사에서 관계형 데이터베이스를 지원하는 언어로 채택하고 있음

- 관계대수와 관계해석을 기초로 한 혼합 언어

- 질의어지만 질의 기능만 있는 것이 아니라 데이터 구조의 정의, 데이터 조작, 데이터 제어 기능을 모두 갖추고 있음

​

SQL의 분류

1) DDL

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

- 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의

- CREATE, ALTER, DROP

2) DML

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

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

- SELECT, INSERT, DELETE, UPDATE

3) DCL

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

- COMMIT, ROLLBACK, GRANT, REVOKE

 

 

 

2. DDL

 

DDL의 개념

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

- 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 테이블로서 저장됨

​

1) CRAETE SCHEMA

- 스키마의 식별을 위해 스키마 이름과 소유권자나 허가권자를 정의

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;

 

 

2) CREATE DOMAIN

- 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때, 사용자는 그 값의 범위를 도메인으로 정의할 수 있음

CREATE DOMAIN 도메인명 [AS] 데이터_타입 [DEFALUT 기본값] [CONSTRAINT 제약조건명 CHECK (범위값)];

 

3) CREATE TABLE

- 테이블을 정의하는 명령문

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

- FOREIGN KEY ~ REFERENCES ~

(1) ON DELETE: 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항 지정 (NO ACTION, CASCADE, SET NULL, SET DEFAULT)

(2) ON UPDATE: 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항 지정 (NO ACTION, CASCADE, SET NULL, SET DEFAULT)

​

4) CREATE VIEW

- SELECT문을 서브 쿼리로 이용하여 SELECT 문의 결과로서 뷰를 생성

- UNION이나 ORDER BY 절을 사용할 수 없음

CREATE VIEW 뷰명[(속성명[, 속셩명, ...])] 
AS SELECT문;

5) CREATE INDEX

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

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

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

​

6) ALTER TABLE

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

7) DROP

- 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거

DROP SCHEMA 스키마명 [CASCADE|RESTRICTED]; 
DROP DOMAIN 도메인명 [CASCADE|RESTRICTED]; 
DROP TABLE 테이블명 [CASCADE|RESTRICTED]; 
DROP VIEW 뷰명 [CASCADE|RESTRICTED]; 
DROP INDEX 인덱스명 [CASCADE|RESTRICTED]; 
DROP CONSTRAIN 제약조건명;

- CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거

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

 

 

 

3. DCL

 

DCL의 개념

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

​

1) GRANT / REVOKE

- GRANT: 권한 부여를 위한 명령어

- REVOKE: 권한 취소를 위한 명령어

​

(1) 사용자등급 지정 및 해제

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

- 사용자등급: DBA, RESOURCE, CONNECT

​

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

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: 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

​

2) COMMIT

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

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

​

3) ROLLBACK

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

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

​

4) SAVEPOINT

- 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어

- 저장점을 지정할 때는 이름을 부여하며, ROLLBACK 시 지정된 저장점까지의 트랜잭션 처리 내용이 취소됨

 

 

 

4. DML

 

DML의 개념

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

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

​

1) 삽입문

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

2) 삭제문

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

3) 갱신문

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

 

 

 

5. 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]];

 

- PREDICATE

(1) ALL: 모든 튜플을 검색할 때 사용하며 주로 생략함

(2) DISTINCT: 중복된 튜플이 있으면 그 중 첫 번째 한 개만 검색

(3) DISTINCTROW: 중복된 튜플을 제거하고 한 개만 검색하지만 선택된 속성의 값이 아닌, 튜플 전체를 대상으로 함

​

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

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

(1) PARTITION BY: WINDOW 함수가 적용될 범위로 사용할 속성을 지정

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

​

그룹 함수

- COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIANCE

- ROLLUP: 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수

- CUBE: 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함

​

WINDOW 함수

- ROW_NUMBER(): 윈도우별로 각 레코드에 대한 일련 번호를 반환

- RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 반영

- DENSE_RANK(): 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여

​

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

SELECT 속성명1, 속성명2, ... 
FROM 테이블명 
UNION | UNION ALL | INTERSECT | EXCEPT 
SELECT 속성명1, 속성명2, ... 
FROM 테이블명 
[ORDER BY 속성명 [ASC|DESC]];

 

 

 

6. DML - JOIN

 

JOIN의 개념

- 2개의 테이블에 대해 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환

- 일반적으로 FROM절에 기술하지만, 릴레이션이 사용되는 어느 곳에서나 사용할 수 있음

​

1) INNER JOIN

(1) EQUI JOIN

- JOIN 대상 테이블에서 공통 속성을 기준으로 동등 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법

- JOIN 조건이 '='일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL 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(속성명);

(2) NON-EQUI JOIN

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

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

​

2) OUTER JOIN

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

(1) 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.속성명(+);

(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.속성명;

 

(3) FULL OUTER JOIN

- LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐놓은 것

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

​

3) SELF JOIN

- 같은 테이블에서 2개의 속성을 연결하여 EQUI JOIN을 하는 JOIN 방법

SELECT [별칭1.]속성명, [별칭1.]속성명, ... 
FROM 테이블명1 [AS] 별칭1 JOIN 테이블명1 [AS] 별칭2 
ON 별칭1.속성명 = 별칭2.속성명; 

SELECT [별칭1.]속성명, [별칭1.]속성명, ... 
FROM 테이블명1 [AS] 별칭1, 테이블명1 [AS] 별칭2 
WHERE 별칭1.속성명 = 별칭2.속성명;