[2020 정보처리기사 필기] SQL 응용 (1)

101. SQL의 개념 ★★

1. SQL(Structured Query Language)의 개요

- 1974년 IBM연구소에서 개발한 SEQUEL에서 유래

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

- 관계대수와 관계해석을 기초로 한 혼합 데이터 언어이다.

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

 

2. SQL의 분류

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

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

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

          - 데이터베이스 관리자나 데이터베이스 설계자가 사용한다.

          - DDL의 세가지 유형 : CREATE(생성), ALTER(변경), DROP(삭제)

 

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

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

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

          -DML의 네가지 유형 : SELECT(검색), INSERT(삽입), DELETE(삭제), UPATE(변경)

 

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

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

          - 데이터베이스 관리자가 데이터 관리를 목적으로 사용한다.

          - COMMIT : 수행된 결과를 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려준다.

          - ROLLBACK : 데이터베이스 조작 작업이 비정상 정료되었을 때 원래의 상태로 복구한다.

          - GRANT : 데이터베이스 사용자에게  사용권을 부여한다.

          - REVOKE : 데이터베이스 사용자의 사용 권한을 취소한다.

 

핵심

- DDL : CREATE, ALTER, DROP

- DML : SELECT, INSERT, DELETE, UPDATE

- DCL : COMMIT, ROLLBACK, GRANT, REVOKE

---

102. DDL ★

1. DDL(Data Define Languag, 데이터 정의어)의 개념

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

- DDL에는 CREATE SCHEMA, CREATE DOMAIN, CREATE TABLE, CREATE VIEW, CREATE INDEX, ALTER TABLE, DROP 등이 있다.

 

2. CREATE SCHEMA

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

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

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

 

3. CREATE DOMAIN

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

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

 

4. CREATE TABLE

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

- CREATE TABLE 테이블명(

[,PRIMARY KEY(기본키_속성명,..)]

[,UNIQUE(대체키_속성명,..)]

[,FOREIGN KEY(외래키_속성명,..)] [REFERENCE 참조테이블(기본키_속성명,..)][ON DELETE 옵션][ON UPDATE 옵션]

[,CONSTAINT 제약조건명] [CHECK(조건식)]);

- PRIMARY KEY : 기본키로 사용할 속성 또는 속성의 집합을 지정

- UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것으로 중복된 값을 가질 수 없음

- FOREIGN KEY ~ REFERENCES ~ : 참조할 테이블과 그 테이블을 참조할 때 사용할 외래키의 속성을 지정

- 외래키가 지정되고 나면 참조 무결성의 CASCADE(어떤 데이터를 삭제하면 연관되어 있는 다른 데이터도 삭제됨) 법칙이 적용된다.

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

- ON UPDATE : 참조 테이블의 참조 속성 값이 변경되었을 때 기본 테이블에 취해야 할 사항을 지정. ON DELETE와 같은 옵션

- CONSTRAINT : 제약 조건의 이름을 지정, 이름 지정할 필요가 없으면 CHECK 절만 사용하여 속성 값에 대한 제약 조건 명시

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

 

5. CREATE VIEW

- 뷰를 정의하는 명령문

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

- SELECT 문을 서브 쿼리로 사용하여 SLEECT 문의 결과로서 뷰를 생성한다.

- 서브 쿼리인 SELECT 문에는 UNION이나 ORDER BY절을 사용할 수 없다.

- 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용된다.

 

6. CREATE INDEX

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

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

- UNIQUE 사용된 경우 중복 값이 없는 속성으로 인덱스를 생성한다.

- 정렬 여부 지정 : ASC(오름차순), DESC(내림차순), 생략(오름차순)

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

 

7. ALTER TABLE

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

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

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

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

 

8. DROP

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

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

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

 

핵심

- CREATE, ALTER, DROP

- CASCADE : 참조하는 것까지 함께 삭제, RESTRICTED : 참조하고 있으면 삭제가 취소

---

103. DCL ★

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

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

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

- GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 있다.

 

2. GRANT/REVOKE

- GRANT : 권한 부여

- REVOKE : 권한 취소

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

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

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

 

3. COMMIT

- 트랜잭션이 성공적으로 끝나면 변경된 모든 내용을 데이터베이스에 반영해야 하는데, 이때 사용하는 명령이 COMMIT 이다.

- DML이 성공하면 자동 COMMIT, 실패하면 자동 ROLLBACK 되도록 Auto Commit 기능을 설정할 수 있다.

 

4. ROLLBACK

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

- 트랜잭션 전체가 성공적으로 끝나지 못하면 비일관성(Inconsistency)를 가질 수 있기 때문에 일부분만 완료된 트랜잭션은 ROLLBACK해야한다.

 

5. SAVEPOINT

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

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

 

핵심

- GRANT, REVOKE, COMMIT, ROLLBACK 기능