3장 - SQL응용 | 섹션19. SQL의 개념, 섹션20. DDL, 섹션21. DCL

2024년도 시나공 필기 책 내용 정리

---

섹션19. SQL의 개념

 

1. SQL(Structured Query Language)의 개요

 

• 1974 IBM연구소에서 개발한 SEQUEL에서 유래
• 국제 표준 데이터베이스 언어이며 많은 회사에서 관계형 데이터베이스(RDB)를 지원하는 언어로 채택하고 있음
• 관계대수와 관계해석을 기초로 한 혼합 데이터 언어
• 질의어(Query Language)지만 질의 기능만 있는 것이 아니라 데이터 구조의 정의, 데이터 조작, 데이터 제어 기능을 모두 갖추고 있음
• DDL(데이터 정의어), DML(데이터 조작어), DCL(데이터 제어어)로 나뉨

---

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

 

• SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어
• 논리적 데이터 구조와 물리적 데이터 구조의 사상을 정의
• 데이터베이스 관리자나 데이터베이스 설계자가 사용

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

---

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

 

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

명령어	기능
SELECT	테이블에서 조건에 맞는 튜플을 검색
INSERT	테이블에 새로운 튜플을 삽입
DELETE	테이블에서 조건에 맞는 튜플을 삭제
UPDATE	테이블에서 조건에 맞는 튜플의 내용을 변경

---

4. DCL(Data Control Language, 데이터 제어어)

 

• 데이터의 보안, 무결성, 회복, 병행 수행 제어 등을 정의하는데 사용되는 언어
• 데이터베이스 관리자가 데이터 관리를 목적으로 사용

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

---

섹션20. DDL

 

1. DDL(Data Define Language, 데이터 정의어)의 개요

 

• DB구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
• 번역할 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러개의 테이블로서 저장됨
• CREATE SCHEMA, CREATE DOMAIN, CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE, DROP 등이 있음

---

2. CREATE SCHEMA

 

• 스키마를 정의하는 명령문
• 스키마의 식별을 위해 스키마의 이름과 소유권자나 허가권자를 정의

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

---

3. CREATE DOMAIN

 

• 도메인을 정의하는 명령문
• 임의의 속성에서 취할 수 있는 값의 범위가 SQL에서 지원하는 전체 데이터 타입의 값이 아니고 일부분일 때 사용자는 그 값의 범위를 도메인으로 정의할 수 있음
• 정의된 도메인명은 일반적인 데이터타입처럼 사용함
• 데이터 타입: SQL에서 지원하는 데이터 타입
• 기본값: 데이터를 입력하지 않았을 때 자동으로 입력되는 값

CREATE DOMAIN 도메인명 [AS] 데이터_타입
        [DEFAULT 기본값]
        [CONSTRAINT 제약조건명 CHECK (범위값)];
# []의 명령어들을 생략이 가능

 

* SQL에서 지원하는 기본 데이터 타입

 

• 정수(Integer): INTEGER(4Byte 정수), SMALLINT(2Byte 정수)
• 실수(Float): FLOAT, REAL DOUBLE PRECISION
• 형식화된 숫자: DEC(i, j) i: 전체 자릿수, j: 소수부 자릿수
• 고정길이 문자: CHAR(n), CHARACTER(n) n: 문자수
• 가변길이 문자: VARCHAR(n), CHARACTER VARYING(n) n: 최대문자수
• 고정길이 비트열(Bit String): BIT(n)
• 가변길이 비트열: VARBIT(n)
• 날짜: DATE
• 시간: TIME

---

4. CREATE TABLE

 

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

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

 

• 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정
• PRIMARY KEY: 기본키로 사용할 속성 또는 속성의 집합을 지정
• UNIQUE: 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 UNIQUE로 지정한 속성은 중복된 값을 가질 수 없음
• FOREIGN KEY ~ REFERENCES ~
  
  
  • 참조할 다른 테이블과 그 테이블을 참조할 때 사용할 외래키 속성을 지정
  • 외래키가 지정되면 참조 무결성의 CASCADE법칙이 적용됨
    * CASCADE법칙 : 참조 무결성 제약이 설정된 기본 테이블의 어떤 데이터를 삭제할 경우 그 데이터와 밀접하게 연관되어 있는 다른 테이블의 데이터들도 도미노처럼 자동으로 삭제되는법칙
  • ON DELETE 옵션: 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항을 지정, 옵션에는 NO ACTION, CASCADE, SET NULL, SET DEFAULT가 있음
    
    
    • NO ACTION: 참조 테이블에 변화가 있어도 기본 테이블에는 아무런 조치를 취하지 않음
    • CASCADE: 참조 테이블의 튜플이 삭제되면 기본 테이블의 관련 튜플도 모두 삭제되고, 속성이 변경되면 관련 튜플의 속성 값도 모두 변경됨
    • SET NULL: 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성 값을 NULL로 변경
    • SET DEFAULT: 참조 테이블에 변화가 있으면 기본 테이블의 관련 튜플의 속성값을 기본값으로 변경
  • ON UPDATE 옵션: 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정, 옵션값을 ON DELETE의 옵션값과 동일
• CONSTRAINT: 제약조건의 이름을 지정, 이름을 지정할 필요가 없으면 CHECK절만 사용하여 속성 값에 대한 제약조건을 명시
• CHECK: 속성 값에 대한 제약 조건을 명시

예제: '이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL문을 작성
제약조건

• 이름은 NULL이 올 수 없고 학번은 기본키
• 전공은 <학과>테이블의 학과코드를 참조하는 외래키로 사용됨
• <학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만듦
• <학과> 테이블에서 학과코드가 변경되면 전공 값도 같은 값으로 변경
• 생년월일은 1980-01-01 이후의 데이터만 저장할 수 있음
• 제약 조건의 이름은 생년월일제약
• 각 속성의 데이터타입은 적당하게 지정, 단 성별은 도메인 SEX를 사용

CREATE TABLE 학생	              # 학생 테이블 정의
    (이름 VARCHAR(15) NOT NULL, # 이름 속성은 최대 15자로 NULL값을 갖지 않음
    학번 CHAR(8),               # 학번의 속성은 문자(8)
    전공 CHAR(5),               # 전공의 속성은 문자(5)
    성별 SEX,                   # 성별의 속성은 SEX 도메인을 자료형으로 사용
    생년월일 DATE,               # 생년월일 속성은 DATE자료형
    PRIMARY KEY (학번),         # 학번을 기본키로 정의
    
    # 전공 속성은 <학과> 테이블의 학과코드 속성을 참조하는 외래키
    FOREIGN KEY (전공) REFERENCES 학과(학과코드), 
        ON DELETE SET NULL  # 학과 테이블에서 튜플이 삭제되면 관련된 모든 튜플의 전공 속성의 값을 NULL로 변경
        ON UPDATE CASCADE   # 마찬가지로 학과코드가 변경되면 관련된 모든 튜플의 전공 속성의 값도 같은 값으로 변경
    
    CONSTRAINT 생년월일제약                # 제약 조건의 이름은 생년월일제약
        CHECK(생년월일 >= '1980-01-01')); # 생년월일 속성에는 1980-01-01 이후의 값만을 저장

 

다른 테이블을 이용한 테이블 정의

 

• 기존 테이블의 정보를 이용해 새로운 테이블을 정의 할 수 있음

CREATE TABLE 신규테이블명 AS SELECT 속성명[, 속성명, ...] FROM 기존테이블명;

# <학생> 테이블의 학번,이름,학년 속성을 이용하여 <재학생> 테이블을 정의하는 SQL문 작성
CREATE TABLE 재학생 AS SELECT 학번, 이름, 학년 FROM 학생;

• 기존 테이블에서 추출되는 속성의 테이터 타입과 길이는 신규 테이블에 그대로 적용됨
• 기존 테이블의 NOT NULL의 정의는 신규 테이블에 그대로 적용됨
• 기존 테이블의 제약 조건은 신규 테이블에 적용되지 않으므로 신규 테이블을 정의한 후 ALTER TABLE 명령을 이용해 제약 조건을 추가해야 함
• 기존 테이블의 일부 속성만 신규 테이블로 생성할 수 있으며 기존 테이블의 모든 속성을 신규 테이블로 생성할 때는 속성명 부분에 *을 입력

---

5. CREATE VIEW

 

• 뷰를 정의하는 명령문

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

-- <고객> 테이블에서 주소가 안산시인 고객들의 성명과 전화번호를 안산고객이라는 뷰로 정의
CREATE VIEW 안산고객(성명,전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시'

• SELECT문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰를 생성
• 서브 쿼리인 SELECT문에는 UNION이나 ORDER BY절을 사용할 수 없음
• 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용됨

*서브 쿼리(Sub Query): 조건절에 주어진 질의, 상위 질의에 앞서 실행되며 그 검색 결과는 상위 질의의 조건절의 피연선자로 사용됨

---

6. CREATE INDEX

 

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

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC|DESC] [,속성명 [ASC|DESC]]) #ASC,DESC를 생략하지 않을경우 둘중 한개를 선택해야 함
[CLUSTER];

/*
<고객>테이블에서 UNIQUE한 특성을 갖는 고객번호 속성에 대해 내림차순으로 정렬하여
고객번호_idx라는 이름으로 인덱스를 정의 */
CREATE UNIQUE INDEX 고객번호_idx
ON 고객 (고객번호 DESC);

• UNIQUE: 사용된 경우 중복값이 없는 속성으로 인덱스를 생성하고, 생략된 경우 중복 값을 허용함
• 정렬 여부 지정: ASC는 오름차순, DESC는 내림차순이며 생략되면 오름차순으로 정렬됨
• CLUSTER: 사용하면 인덱스가 클러스터드 인덱스로 설정됨
  
  
  • 클러스터드 인덱스(Clustered Index): 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식, 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있으나 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬 해야함
  • 넌 클라스터드 인덱스 (Clustered Indix): 인덱스의 키 값만 정렬되고 실제 데이터는 정렬되지 않는 방식, 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로 클러스터드 인덱스에 비해 검색 속도가 떨어짐

---

7. ALTER TABLE

 

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

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

# <학생> 테이블에 최대 3문자로 구성되는 '학년'속성을 추가
ALTER TABLE 학생 ADD 학년 VARCHAR(3);

-- <학생> 테이블의 학번 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경
ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

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

---

8. DROP

 

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

DROP SCHEMA 스키마명 [CASCADE | RESTRICT]; # 스키마를 제거
DROP DOMAIN 도메인명 [CASCADE | RESTRICT]; # 도메인을 제거
DROP TABLE 테이블명 [CASCADE | RESTRICT];  # 테이블을 제거
DROP VIEW 뷰명 [CASCADE | RESTRICT];      # 뷰를 제거
DROP INDEX 인덱스명 [CASCADE | RESTRICT];  # 인덱스를 제거
DROP CONSTRAINT 제약조건명;                 # 제약조건을 제거

# <학생>테이블을 제거하되 <학생>테이블을 참조하는 모든 데이터를 함께 제거
DROP TABLE 학생 CASCADE;

• CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거, 주 테이블의 데이터 제거 시 각 외래키와 관계를 맺고 있는 모든 데이터를 제거하는 참조 무결성 제약 조건을 설정하기 위해 사용됨
• RESTRICT: 다른 개체가 제거할 요소를 참조중일 때는 제거를 취소

---

섹션21. DCL

 

1. DCL (Data Control Language, 데이터 제어어)의 개요

 

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용
• 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용함
• GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 있음

---

2. GRANT / REVOKE

 

• 데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어
• GRANT: 권한 부여
• REVOKE: 권한 취소
• 사용자 등급 지정 및 해제

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

/* 사용자 등급
DBA: 데이터베이스 관리자
RESOURCE: 데이터베이스 및 테이블 생성 가능자
CONNECT: 단순 사용자 */

# 사용자 ID가 NABI인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문 작성
GRANT RESOURCE TO NABI;

# 사용자 ID가 STRT인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문 작성
GRANT CONNECT TO STAR;

• 테이블 및 속성에 대한 권한 부여 및 취소
  
  • 권한종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
  • WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여
  • GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소
  • CASCADE: 권한 취소 시 권한을 부여 받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

/* 사용자 ID가 NABI인 사람에게 고객 테이블에 대한 모든 권한과
다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문 작성 */
GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

/* 사용자 ID가 STAR인 사람에게 부여한 <고객> 테이블에 대한 권한 중
UPDATE권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문 작성 */
REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

---

3. COMMIT

 

• 트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성(Consistency) 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영하여야 하는데 사용하는 명령
• COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT되고 DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있음
• Auto Commit
  
  
  • Oracle : set autocommit on(설정) | off(해제);, show autocommit;(확인)
  • MySQL :  set autocommit = true(설정) | false(헤제);, select @@autocommit;(확인)

---

4. ROLLBACK

 

• 아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는명령
• 트랜잭션 전체가 성공적으로 끝나지 못하면 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency)인 상태를 가실 수 있기 때문에 일부분만 완료 된 트랜잭션은 롤백(Rollback)되어야 함

---

5. SAVEPOINT

 

• 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
• 저장점을 지정할 때는 이름을 부여하며, ROLLBACK시 지정된 저장점까지의 트랜잭션 처리 내용이 취소됨

<사원>

사원번호	이름	부서
10	김기획	기획부
20	박인사	인사부
30	최재무	재무부
40	오영업	영업부

# <사원>테이블에서 사원번호가 40인 사원의 정보를 삭제한 후 COMMIT을 수행
DELETE FROM 사원 WHERE 사원번호 = 40;
COMMIT;

# 사원번호가 30인 사원의 정보를 삭제
DELETE FROM 사원 WHERE 사원번호 = 30;

# SAVEPOINT S1을 설정하고 사원번호가 20인 사원의 정보를 삭제
SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호 = 20;

# SAVEPOINT S2를 설정하고 사원번호가 10인 사원의 정보를 삭제
SAVEPOINT S2;
DELETE FROM 사원 WHERE 사원번호 = 10;

# SAVEPOINT S2까지 ROLLBACK 수행
ROLLBACK TO S2;

# SAVEPOINT S1까지 ROLLBACK 수행
ROLLBACK TO S1;

# SAVEPOINT 없이 ROLLBACK 수행
ROLLBACK;