[3과목 데이터베이스 구축] SQL 응용 - 108. ⭐ DDL (Data Define Language) ~ 112. ⭐ DML - SELECT 2

1. ⭐ DDL (Data Define Language)

1. DB구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
2. 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러개의 테이블로서 저장됨

 

종류	설명
CREATE SCHEMA	스키마 정의 스키마 이름과 소유권자나 허가권자 정의
CREATE DOMAIN	도메인 정의 일반적인 데이터 타입처럼 사용
CREATE TABLE	테이블 정의 속성명, 속성 데이터 타입, 기본 값, NOT NULL여부 지정
CREATE VIEW	뷰를 정의 SELECT문을 서브쿼리로 사용해 SELECT문 결과로서 뷰를 생성 서브쿼리인 SELECT문에는 UNION이나 ORDER BY절 사용 불가 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됨 PRIMARY KEY : 기본키 설정 UNIQUE : 중복값 X FOREIGN KEY ~ REFERENCES ~ : 외래키 속성 지정 CONSTRAINT : 제약 조건 이름 CHECK : 속성에 대한 제약 조건 정의
CREATE INDEX	인덱스 정의 UNIQUE : 사용시 중복 값이 없는 속성으로 인덱스 생성 정렬 : ACS, DESC, 생략은 ACS CLUSTER : 인덱스가 클러스터드 인덱스로 설정됨 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식 검색 속도 빠르지만 삽입, 삭제 시 순서를 유지하기 위해 데이터를 재정렬함
ALTER TABLE	테이블에 대한 정의 변경 ADD : 새로운 열 추가 ALTER : 특정 속성의 DEFAULT 값을 변경 DROP COLUMN : 특정 속성 삭제
DROP	스키마, 도메인, 기본 데이블, 뷰 테이블, 인덱스, 제약 조건 등 제거 CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거 RESTRICT : 다른 개체가 제거할 요소를 참조 중일 떄는 제거를 취소

 

2. ⭐ DCL (Data Control Language)

1. 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용
2. 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용

 

종류	설명
GRANT	권한 부여 GRANT ~ ON ~ TO 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등 WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
REVOKE	권한 취소 REVOKE ~ ON ~ FROM GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소 CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자게에 부여한 권한도 연쇄적으로 취소
COMMIT	트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성(Consistency) 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영 DDL문은 자동 COMMIT DML문이 실패하면 자동으로 ROLLBACK
ROLLBACK	아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전상태로 되돌리는 명령어 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일광성(Inconsisteny)인 상태를 가질 수 있기에 일부분만 완료된 트랜잭션은 롤백되어야 함 실패하면 해당 실행 전체 롤백
SAVEPOINT	트랜잭션 내에 ROLLBACK할 위치인 저장점을 지정하는 명령어

 

3. ⭐ DML (Data Manipulation Language)

1. 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
2. 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공

 

종류	설명
SELECT	테이블에서 튜플 검색 SELECT ~ FROM ~ WHERE
INSERT	테이블에 새로운 튜플 삽입 속성과 데이터 개수, 데이터 유형이 일치해야함 SELECT문을 사용하여 다른 테이블의 검색 결과 삽입 가능 INSERT INTO ~ VALUES ~
DELETE	테이블에서 튜플 삭제 모든 레코드 삭제시 WHERE절 생략 테이블 구조는 남아있음 DELETE ~ FROM ~ WHERE
UPDATE	테이블에서 튜플 갱신 UPDATE ~ SET ~ WHERE

 

4. ⭐ DML - SELECT 1

종류	설명
PREDICATE	ALL : 모든 튜플 DISTINCT : 중복제거 DISTINCTROW : 튜플 전체를 대상으로 중복제거
IS NULL / IS NOT NULL	SELECT * </br> FROM 사원 </br> WHERE 주소 IS NULL;
NOT IN	SELECT * </br> FROM 사원 </br> WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);
EXISTS	SELECT 부서 </br> FROM 사원 </br> WHERE EXISTS (SELCT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원이름);

 

5. ⭐ DML - SELECT 2

종류	설명
WINDOW 함수	PARTITION BY : WINDOW 함수가 적용될 범위로 사용할 속성을 지정 ORDER BY : PARTITION 안에서 정렬 기순으로 사용할 속성 지정 ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련 번호를 반환 RANK() : 원도우별 순위를 반환, 공동 순위 반영 DENSE_RANK() : 윈도우별 순위를 반환, 공동 순위 무리하고 순위를 부여
그룹 함수	ROLLUP(속성명, 속성명 ...) : 인수로 주어신 속성을 대상으로 그룹별 소계를 구하는 함수 함수의 개수가 n개이면 n+1 레벨까지, 하위레벨에서 상위레벨 순으로 데이터가 집계됨 CUBE(속성명, 속성명...) : ROLLUP과 유사한 형태 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구하는 함수 속성의 개수가 n개이면 2<sup>n</sup>레벨까지, 상위레벨에서 하위레벨 순으로 데이터가 집계됨

 

EX) WINDOW 함수 이용 검색

-- <상여금> 테이블에서 "상여내역"별로 "상여금"에 대한 일련 번호 구하기

SELECT 상여내역, 상여금,
ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

 

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

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

 

 

 

📖 Reference

2023 시나공 정보처리기사 필기 : 네이버 도서