[정보처리기사 #8] SQL 응용

[[A]] SQL - DDL ⭐

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

: DB를 구축하거나 수정할 목적으로 사용되는 언어

- 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로 저장됨

 

DDL의 3가지 유형

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

 

CREATE SCHEMA

: 스키마를 정의하는 명령문

 

표기 형식

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

 

CREATE DOMAIN

: 도메인을 정의하는 명령문

 

표기 형식

• CREATE DOMAIN 도메인명 [AS] 데이터_타입

       [DEFAULT 기본값]

       [CONSTRAINT 제약조건명 CHECK (범위값)];

 

CREATE TABLE

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

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

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

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

     (ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정함)

     (ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정함)

- CONSTRAINT : 제약 조건의 이름을 지정함

- CHECK : 속성 값에 대한 제약 조건을 정의함

 

CREATE VIEW

: 뷰(View)를 정의하는 명령문

 

표기 형식

•  CREATE VEIW 뷰명[(속성명[, 속성명, ...])]

        AS SELECT문;

 

CREATE INDEX

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

 

표기 형식

• CREATE [UNIQUE] INDEX 인덱스명

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

       [CLUSTER];

- UNIQUE

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

      생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성함 (=중복 값 허용이 default)

- 정렬 여부 지정

       ASC : 오름차순 정렬 (default)

       DESC : 내림차순 정렬

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

 

ALTER TABLE

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

 

표기 형식

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

        ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];

        ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

- ADD : 새로운 속성(열)을 추가할 때 사용함

- ALTER : 특정 속성의 Default 값을 변경할 때 사용함

- DROP COLUMN : 특정 속성을 삭제할 때 사용함

 

DROP

: 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 제약 조건 등을 제거하는 명령문

 

표기 형식

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

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

 

 

[[A]] SQL - DCL ⭐

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

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

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

 

DCL의 종류

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

 

- WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함 (GRANT와 함께 씀)

- GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함 (REVOKE와 함께 씀)

- CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함

 

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

 

 

[[A]] SQL - DML ⭐

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

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

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

 

DML의 유형 (SIDU) - 메이저

명령문	기능
SELECT	테이블에서 튜플을 검색함
INSERT	테이블에 새로운 튜플을 삽입함
DELETE	테이블에서 튜플을 삭제함
UPDATE	테이블에서 튜플의 내용을 갱신함

 

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

 

조건 연산자

• 비교 연산자 : =, < >(같지 않다), >, <, >=, <=
• 논리 연산자 : NOT, AND, OR
• LIKE 연산자 : 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용됨
  • % : 모든 문자를 대표함
  • - : 문자 하나를 대표함
  • # : 숫자 하나를 대표함

 

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

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

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

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

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

 

그룹 함수

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

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

 

WINDOW 함수

: GROUP BY절을 이용하지 않고 함수의 인수로 지정한 속성의 값을 집계함

• ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환함
• RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영함
• DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여함

 

집합 연산자 (=통합 질의)

집합 연산자	설명	집합 종류
UNION	- 두 SELECT문의 조회 결과를 통합하여 모두 출력함 - 중복된 행은 한 번만 출력함	합집합
UNION ALL	- 두 SELECT문의 조회 결과를 통합하여 모두 출력함 - 중복된 행도 그대로 출력함	합집합

 

JOIN

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

• INNER JOIN
  • EQUI JOIN : '=(equl)' 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN
  • NON-EQUI JOIN : '=(equl)'이 아닌 나머지 비교 연산자 '>, <, < >, >=, <='를 사용하는 JOIN 
• OUTER JOIN ← JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
  • LEFT OUTER JOIN : 좌측 릴레이션이 기준이 되어 좌측 릴레이션에 있는 튜플은 모두 표시하고 우측 릴레이션에서는 관련이 있는 튜플만 표시
  • RIGHT OUTER JOIN : 우측 릴레이션이 기준이 되어 우측 릴레이션에 있는 튜플은 모두 표시하고 좌측 릴레이션에서는 연관된 튜플만 표시
  • FULL OUTER JOIN : LEFT OUTER JOIN + RIGHT OUTER JOIN
    • 좌측 항의 릴레이션의 튜플들에 대해 우측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가함
    • 그리고 유사하게 우측 항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가함

 

 

[[C]] 트리거 (Trigger)

: 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL