[정보처리기사] SQL 응용

DDL

• DDL(Data Define Language, 데이터 정의어)은 DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어이다.
• DDL의 3가지 유형
  CREATE, ALTER, DROP

CREATE INDEX

CREATE INDEX는 인덱스를 정의하는 명령문이다.
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명[ASC|DESC], 속성명[ASC|DESC]])
[CLUSTER];

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

ALTER TABLE

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

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

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

DROP

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(Data Control Language, 데이터 제어어)은 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어이다.
• DCL은 데이터베이스 관리자가 데이터 관리를 목적으로 사용한다.
  COMMIT, ROLLBACK, GRANT, REVOKE

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

DML

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

삽입문(INSERT INTO ~)

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

삭제문(DELETE FROM ~)

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

갱신문(UPDATE ~ SET ~)

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

SELECT

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

• SELECT절
  • PREDICATE: 검색할 튜플 수를 제한하는 명령어를 기술함
  • DISTINCT: 중복된 튜플이 있으면 그 중 첫 번째 한 개만 표시함

하위 질의

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

그룹 함수

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

명령어	기능
COUNT(속성명)	그룹별 튜플 수를 구하는 함수
SUM(속성명)	그룹별 합계를 구하는 함수
AVG(속성명)	그룹별 평균을 구하는 함수
MAX(속성명)	그룹별 최대값을 구하는 함수
MIN(속성명)	그룹별 최소값을 구하는 함수
STDDEV(속성명)	그룹별 표준편차를 구하는 함수
VARIANCE(속성명)	그룹별 분산을 구하는 함수

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

집합 연산자를 사용하여 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문의 조회 결과를 제외한 행을 출력함	차집합

JOIN

• JOIN은 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
• JOIN은 일반적으로 FROM절에 기술하지만, 릴레이션이 사용되는 곳 어디에나 사용할 수 있다.
• JOIN은 크게 INNER JOIN과 OUTER JOIN으로 구분된다.
  • INNER JOIN: THETA JOIN, EQUI JOIN, NATURAL JOIN, NON-EQUI JOIN
  • OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

EQUI JOIN(동등 조인)

• JOIN에 참여하는 두 릴레이션의 속성 값을 비교하여 조건을 만족하는 튜플만 반환하는 조인을 THEA JOIN(세타 조인)이라고 하며, 조인에 사용되는 조건에는 =, ≠, <, ≤, >, ≥가 있다.

• EQUI JOIN(동등조인)은 조인에 사용되는 조건 중 =(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 방법이다.

• EQUI JOIN에서 JOIN 조건이 '='일 때 동일한 속성이 두 번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN(자연 조인)이라고 한다.

• EQUI JOIN에서 연결 고리가 되는 공통 속성을 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(속성명);

OUTER JOIN

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

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

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

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

트리거

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