- SQL(Structured Query Language) 데이터 언어는 원래 1974년에 IBM 연구소에서 발표한 SEQUEL(Structred English QUEry Language)에 연유한다.
- IBM은 처음 이 언어를 실험적 관계 데이터베이스 시스템, 'SYSTEM R'의 인터페이스로 설계 구현하였다.
- SQL의 의미는 구조화 질의어지만 단순히 검색만을 위한 데이터 질의어가 아니라 종합 데이터베이스 언어 역할을 한다. 즉, 데이터 정의어(DDL), 데이터 조작어(DML) 그리고 데이터 제어어(DCL)의 기능을 모두 제공하고 있다.
- SQL의 특징은 온라인 터미널을 통해 대화식 질의어를 사용하는 것은 물론이고 Java, COBOL, C, C++ 등과 같은 범용 프로그래밍 언어로 쓰여진 응용 프로그램에 삽입된 형태로도 사용이 가능하다는 것이다.
- SQL은 개개의 레코드 단위로 처리하기 보다는 레코드 집합 단위로 처리하며 SQL 명령문에는 데이터 처리를 위한 접근 경로(access path)에 대한 명세가 필요하지 않으므로 선언적 언어라고 할 수 있다.
- 한 가지 유의할 것은 SQL은 릴레이션(relation), 튜플(tuple), 애트리뷰트(attribute)와 같은 관계 모델의 공식적 용어 대신 이에 대응하는 테이블(table), 행(row), 열(column)과 같은 일반적인 용어를 사용하고 있다.
6.1 SQL 데이터 정의문
6.1.1 스키마 카탈로그
- SQL2에서는 SQL 스키마 개념을 도입했는데 이것은 하나의 응용(사용자)에 속하는 테이블과 기타 구성 요소들을 그룹 짓기 위한 것이다.
- SQL 스키마는 스키마 이름(schema name)으로 식별되는데 그 스키마의 소유자나 계정을 나타내는 허가권자(authorization identifier)와 그 스키마의 각 요소에 대한 기술자(descriptor)를 포함한다.
- 스키마 요소에는 테이블, 뷰, 도메인, 그리고 기타 허가권이나 무결성 등에 관한 요소들이 있다.
- 예를 들어, 다음 명령문은 이름이 UNIVERSITY이고 허가권자가 SHLEE인 스키마를 생성한다.
CREATE SCHEMA UNIVERSITY AUTHORIZATION SHLEE;- 아무나 스키마 요소를 생성할 수 있는 것은 아니다. 시스템 관리자나 DBA를 통해 권한이 부여된 사용자만 이런 권한을 갖게 된다.
- SQL은 스키마 개념과 더불어 카탈로그 개념도 정의하고 있는데 여기서의 카탈로그는 한 SQL 시스템 내애서의 스키마들의 집합을 말한다.
- 각 카탈로그는 반드시 INFORMATION_SCHEMA라고 하는 특별한 스키마를 포함하게 되어 있는데 이것은 그 카탈로그에 포함되어 있는 모든 스키마들에 대한 정보와 이 스키마들에 있는 모든 요소들에 대한 정보를 제공하기 위한 것이다.
6.1.2 정의문
- SQL 도메인을 정의하기 위한 명령문 형식은 다음과 같다.
CREATE DOMAIN 도메인_이름 데이터_타입
[기정_값_정의]
[도메인_제약조건_정의리스트]- "[ ]"로 묶여진 것은 생략할 수 있는 부분을 나타낸다.
- 다음은 DEPT라는 이름의 도메인 정의문의 예이다.
CREATE DOMAIN DEPT CHAR(4)
DEFAULT '???'
CONSTRAINT VALID-DEPT
CHECK(VALUE IN ('COMP', 'ME', 'EE', 'ARCH', '???'));- 일단 정의된 도메인은 ALTER DOMAIN 명령문으로 어느 때고 변경시킬 수 있다.
- 일단 만들어진 도메인은 다음과 같이 DROP DOMAIN 명령문으로 삭제할 수도 있다.
DROP DOMAIN 도메인_이름 옵션;- 여기 옵션에는 RESTRICT나 CASCADE가 있다. 만일 RESTRICT로 명세 되면 이 도메인을 참조하고 있는 곳이 없을 때만 삭제된다. 만일 CASCADE로 명세 되면 이 도메인을 참조하고 있는 뷰나 제약조건도 함께 삭제되지만 참조 열(referencing column)은 삭제되지 않고 다른 형태의 데이터 타입으로 변경된다.
6.1.3 기본 테이블 생성
- 기본 테이블(base table)은 CREATE TABLE 문으로 만들어지는 테이블로서 독자적으로 존재하는 테이블이다.
- 테이블과 행(튜플)들이 DBMS에 의해 하나의 파일로 저장된다는 의미이다.
- 가상 테이블(virtal table)은 CREATE VIEW 문으로 만들어지는 테이블로서 독자적으로 존재하지 못하고 궁극적으로 어떤 기본 테이블로부터 유도되어 만들어지는 테이블이다.
- 임시 테이블(temporary table)은 어떤 DDL문으로 만들어지는 것이 아니고 질의문 처리 과정의 중간 결과로 만들어지는 테이블을 말한다.
- 기본 테이블을 정의하기 위한 정의문의 형식은 다음과 같다.
CREATE TABLE 기본테이블
({열_이름 데이터_타입 [NOT NULL] [DEFAULT 값],}+
[PRIMARY KEY (열_이름_리스트),]
{[UNIQUE (열_이름_리스트),]}+
{[FOREIGN KEY(열_이름_리스트)
REFERENCES 기본테이블[(열_이름_리스트)]
[ON DELETE 옵션]
[ON UPDATE 옵션],]}*
[CONSTRAINT 이름] [CHECK(조건식)]);- PRIMARY KEY : 기본키와 개체 무결성 제약조건을 명세
- UNIQUE : 대체 키를 명세, 후보키
- NOT NULL : 애트리뷰트 값 제약조건
- FOREIGN KEY : 외래 키와 참조무결성 제약조건을 명세
→ SQL에서 참조된 테이블의 후보 키도 외래키로 될 있다. - 옵션에는 SET NULL, SET DEFAULT, CASCADE가 있다.
- ON DELETE SET NULL : 참조 튜플(열 값)이 삭제되면 NULL로 설정
- ON UPDATE CASCADE : 참조 튜플(열 값)이 갱신되면 갱신된 값이 파급적으로 갱신됨
- CHECK : 제약조건을 명세
6.1.4 기본 테이블의 제거와 변경
- 정의된 기존의 기본 테이블은 DROP TABLE 명령문으로 어느 때고 삭제할 수 있다. 삭제를 할 때에는 CASCADE나 RESTRICT 중의 어느 하나가 명세 되어야 한다. 예를 들어, COURSE 테이블을 삭제하는 명령문은 다음과 같다.
DROP TABLE 기본_테이블_이름{RESTRICT|CASCADE};
DROP TABLE COURSE CASCADE;- 이렇게 CASCADE가 명세된 명령이 실행되면 COURSE 테이블을 참조하는 다른 뷰 정의나 제약조건이 있으면 이들도 함께 자동으로 삭제된다.
- 만약 RESTRICT가 명세 되었다면 이 테이블이 다른 뷰 정의에서나 제약조건에서 참조되고 있는 경우에는 실행되지 않는다.
- 기존의 스키마도 DROP SCHEMA 명령문으로 삭제할 수 있다. 이때 CASCADE나 RESTRICT가 명세될 수도 있는데 CASCADE가 명세되면 이 스키마뿐만 아니라 연관된 객체들도 모두 삭제된다. 그러나 RESTRICT가 명세되면 스키마가 공백인 경우에만 삭제된다.
DROP SCHEMA 스키마_이름 {RESTRICT|CASCADE};
DROP SCHEMA UNIVERSITY CASCADE;
- 기존의 기본 테이블은 ALTER TABLE 명령문으로 어느 때고 변경할 수 있다.
ALTER TABLE 기본_테이블_이름
([ADD 열_이름_데이터_타입][DEFAULT 기정값][DROP 열_이름]
[CASCADE][ALTER 열_이름(DROP DEFAULT|SET DEFALT 기정값)]);
ALTER TABLE ENROL
ADD Final CHAR DEFAULT 'F';
- 기존의 테이블에서 열을 삭제할 때는 DROP문을 CASCADE나 RESTRICT와 함께 명세하면 된다.
- 다음 명령문은 ENROL 테이블에서 Grade 열을 삭제시키는 명령문의 예이다.
ALTER TABLE ENROL DROP Grade CASCADE;
- RESTRICT로 명세된 경우에 이 열을 뷰 정의에서나 제약조건에서 참조하고 있다면 실행되지 못한다. 대신 CASCADE로 명세되면 이 열을 참조하는 뷰 정의나 제약조건도 함께 삭제된다.
- 기정 값을 변경하는 명령문의 예이다.
ALTER TABLE ENROL ALTER Grade DROP DEFAULT;
ALTER TABLE ENROL ALTER Grade SET DEFALT '0';
- 기존에 명세한 무결성 제약 조건을 삭제할 때에도 DROP 문을 사용할 수 있는데 이런 경우에는 삭제할 제약 조건의 이름을 명세할 수 있도록 미리 제약조건에 이름이 정의되어 있어야 한다.
DROP CONSTRAINT 이름6.2 SQL 데이터 조작문
6.2.1 데이터 검색
SQL 검색문의 기본적인 구조
SELECT 열_리스트
FROM 테이블_리스트
WHERE 조건
예를 들어, '컴퓨터과 학생의 이름(Sname)과 학번(Sno)을 검색하라.'는 검색문은 다음과 같이 표현한다.
SELECT Sname, Sno
FROM STUDENT
WHERE Dept = '컴퓨터';
이 검색문의 실행 결과는 다음과 같이 된다.
- 기존의 테이블을 처리하면 또 다른 테이블이 만들어지는 것이다. 이런 성질을 가진 시스템은 폐쇄 시스템(closed system)이라 한다.
SELECT STUDENT.Sname, STUDENT.Sno
FROM STUDENT
WHERE STUDENT.Dept = '컴퓨터';이 예는 앞에 기술한 검색 문과 동일하다.
- SQL은 한 테이블 내에 똑같은 레코드(행)가 중복될 수 있다. 즉, SQL의 테이블은 일반적으로 기본 키를 반드시 갖아야 하는 것은 아니다. 따라서 이론상 SQL의 테이블은 튜플의 집합이 아니다.
- SQL의 테이블은 같은 원소의 중복을 허용하는 다중 집합(multiset) 또는 백(bag)이다.
- 집합과 같은 결과를 만들기 위해서는 SELECT문에 DISTINCT를 명세해야 된다. 이 DISTINCT를 명세하지 않으면 ALL을 명세한 것과 같이 중복된 레코드가 나타나더라도 제거되지 않는다.
- SQL 검색문의 일반적인 형식은 다음과 같다.
SELECT [ALL|DISTINCT] 열_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 열_리스트
[HAVING 조건]]
[ORDER BY 열_리스트 [ASC|DESC]];
성질
(1) 검색 결과에 중복 레코드의 제거
[질의] 학생(STUDENT) 테이블에 어떤 학과(Dept)들이 있는지 검색하라.
SELECT DISTINCT Dept
FROM STUDENT;결과 :
(2) 테이블의 열 전부를 검색하는 경우
[질의] 학생(STUDENT) 테이블 전부를 검색하라
SELECT *
FROM STUDENT;테이블 전체를 검색할 때는 열 이름을 일일이 전부 나열할 필요 없이 *(asterisk)로 표시하면 된다.
다음 검색문과 위의 검색문은 동등하다.
SELECT Sno, Sname, Year, Dept
FROM STUDENT;결과 : 현재의 학생(STUDENT) 테이블 전체 튜플
| Sno | Sname | Year | Dept |
| 100 | 나수영 | 4 | 컴퓨터 |
| 200 | 이찬수 | 3 | 전기 |
| 300 | 정기태 | 1 | 컴퓨터 |
| 400 | 송병길 | 4 | 컴퓨터 |
| 500 | 박종화 | 2 | 산공 |
(3) 조건 검색
[질의] 학생(STUDENT) 테이블에서 학과(Dept)가 '컴퓨터'이고 학년(Year)이 4인 학생의 학번(Sno)과 이름(Sname)을 검색하라
SELECT Sno, Sname
FROM STUDENT
WHERE Dept='컴퓨터' AND Year=4;결과 :
| Sno | Sname |
| 100 | 나수영 |
| 500 | 박종화 |
(4) 순서를 명세하는 검색
[질의] 등록(ENROL) 테이블에서 중간 성적(Midterm)이 90점 이상인 학생의 학번(Sno)과 과목 번호(Cno)를 검색하되 학번(Sno)에 대해서는 내림차순으로 또 같은 학번에 대해서는 과목 번호(Cno)의 오름차순으로 검색하라.
SELECT Sno, Cno
FROM ENROL
WHERE Midterm ≥ 90
ORDER BY Sno DESC, Cno ASC;결과 :
| Sno | Cno |
| 400 | C312 |
| 400 | C324 |
| 300 | C312 |
| 300 | C413 |
| 100 | C413 |
| 100 | E412 |
- 사용자가 검색 결과의 순서를 오름차순(ASC)이나 내림차순(DESC)을 명세할 수 있다.
(5) 산술식, 문자 스트링, 새로운 열 이름이 명세된 검색
[질의] 등록(ENROL) 테이블에서 과목 번호(Cno)가 'C312'인 중간 성적(Midterm)에 3점을 더한 점수를 '학번', '중간성적 ='이란 텍스트를 내용 '시험', 그리고 '점수'라는 열 이름으로 검색하라.
SELECT Sno AS 학번, '중간시험 =' AS 시험, Midterm+3 AS 점수
FROM ENROL
WHERE Cno = 'C312';결과 :
| 학번 | 시험 | 점수 |
| 300 | 중간 시험 = | 93 |
| 400 | 중간 시험 = | 93 |
| 500 | 중간 시험 = | 88 |
(6) 복수 테이블로부터의 검색
[질의] 과목 번호(Cno) 'C413'에 등록한 학생의 이름(Sname), 학과(Dept), 성적(Grade)을 검색하라.
SELECT S.Sname, S.Dept, E.Grade
FROM STUDENT S, ENROL E
WHERE S.Sno = E.Sno AND E.Cno = 'C413';결과 :
| Sname | Dept | Grade |
| 나수영 | 컴퓨터 | A |
| 정기태 | 컴퓨터 | A |
| 송병길 | 컴퓨터 | B |
(7) 자기 자신의 테이블에 조인하는 검색
[질의] 같은 학과 학생들의 학번을 쌍으로 검색하라. 단, 첫번째 학번은 두 번째 학번보다 작게 하라.
SELECT S1.Sno, S2.Sno
FROM STUDENT S1, STUDENT S2
WHERE S1.Dept=S2.Dept
AND S1.Sno < S2.Sno;결과 :
| Sno | Sno |
| 100 | 300 |
| 100 | 400 |
| 300 | 400 |
(8) 집계 함수를 이용한 검색
- COUNT : 값으 개수
- SUM : 값의 총계
- AVG : 평균 값
- MAX : 최대 값
- MIN : 최소 값
[질의] 학생 테이블에 학생 수가 얼마인가를 검색하라.
SELECT COUNT(*) AS 학생수
FROM STUDENT;결과 :
| 학생수 |
| 5 |
(9) GROUP BY를 이용한 검색
[질의] 과목별 기말 성적(Final)의 평균을 검색하라.
SELECT Cno, AVG(Final) AS 기말평균
FROM ENROL
GROUP BY Cno;결과 :
| Cno | 기말평균 |
| C413 | 90 |
| E412 | 85 |
| C123 | 80 |
| C312 | 90 |
| C324 | 82 |
(10) HAVING을 사용한 검색
[질의] 3명 이상 등록한 과목의 기말 평균 성적을 검색하라.
SELECT Cno, AVG(Final) AS 평균
FROM ENROL
GROUP BY Cno
HAVING COUNT(*) ≥ 3;결과 :
| Cno | 평균 |
| C413 | 90 |
| C312 | 90 |
(11) 부속 질의문(Subquery)을 사용한 검색
[질의] 과목 번호(Cno) 'C413' 등록한 학생 이름(Sname)을 검색하라
SELECT Sname
FROM STUDENT
WHERE Sno IN
(SELECT Sno
FROM ENROL
WHERE Cno='C413');결과 :
| Sname |
| 나수영 |
| 정기태 |
| 송병길 |
- 부속 질의문(subquery)은 다른 질의문에 중첩(nested)되어 사용되는 검색문으로서 SELECT-FROM-WHERE-GROUP BY-HAVING의 형태를 취한다.
- 부속 질의문을 포함하고 있는 질의문을 중첩 질의문(nested query)이라 한다.
- IN은 집합의 멤버십 연산자(∈)로 해석할 수 있다.
SELECT Sname
FROM STUDENT
WHERE Sno IN(100, 300, 400);위 원래의 질의문은 조인 연산을 사용하는 질의문으로 표현할 수도 있다.
SELECT STUDENET.Sname
FROM STUDENT, ENROL
WHERE STUDENT.Sno = ENROL.Sno
AND ENROL.Cno = 'C413';
(12) LIKE를 사용하는 검색
[질의] 과목번호(Cno)가 C로 시작하는 과목 번호와 과목이름(Cname)을 검색하라
SELECT Cno, Cname
FROM COURSE
WHERE Cno LIKE 'C%';결과 :
| Cno | Cname |
| C123 | 프로그래밍 |
| C312 | 자료구조 |
| C324 | 파일구조 |
| C413 | 데이터베이스 |
- LIKR 프레디킷은 서브 스트링 패턴(substring pattern)을 비교하는 비교 연산자로 사용된다.
- 이 예에서 사용된 %는 서브 스트링 패턴을 명세하는 것으로 'C'로 시작하기만 하면 어떤 길이의 어떤 문자 스트링도 관계없다는 뜻이다.
(13) NULL을 사용한 검색
[질의] 학과(Dept)가 NULL인 학생의 학번과 이름을 검색하라.
SELECT Sno, Sname
FROM STUDENT
WHERE Dept IS NULL;결과 :
| Sno | Sname |
| 600 | 김길동 |
(14) EXISTS를 사용하는 검색
[질의] 과목 'C413'에 등록한 학생의 이름을 검색하라.
SELECT Sname
FROM STUDENT
WHERE EXISTS
(SELECT *
FROM ENROL
WHERE Sno = STUDENT.Sno AND Cno = 'C413');결과 :
| Sname |
| 나수영 |
| 정기태 |
| 송병길 |
- EXISTS는 존재 정량자(existential quantifier)로서 EXISTS 다음에 나오는 검색문의 실행 결과로 검색된 튜플이 존재하는가를 검사한다. 따라서 이 부속 질의분은 EXISTS(SELECT * FROM) 검색문을 실행한 뒤 그 결과가 공집합이 아니면 참이 되고, 공집합이면 거짓으로 된다.
[질의] 과목 'C413'에 등록하지 않은 학생의 이름을 검색하라.
SELECT Sname
FROM STUDENT
WHERE NOT EXISTS
(SELECT *
FROM ENROL
WHERE Sno = STUDENT.Sno
AND Cno='C413';결과 :
| Sname |
| 이찬수 |
| 박종화 |
(15) UNION이 관련된 검색
[질의] 3학년이거나 또는 과목 'C324'에 등록한 학생의 학번을 검색하라
SELECT Sno
FROM STUDENT
WHERE Year=3
UNION
SELECT Sno
FROM ENROL
WHERE Cno='C324';결과 :
| Sno |
| 200 |
| 300 |
| 400 |
UNION은 일반 집합론의 합집합과 같다. 따라서 UNION이 사용되면 결과 테이블에서 중복되는 튜플은 제거된다.
6.2.2 데이터의 갱신
기존 레코드 열의 값을 변경하기 위해서는 다음과 같은 UPDATE 명령문을 사용한다.
UPDATE 테이블
SET {열_이름 = 산술식}'+
[WHERE 조건];(1) 하나의 레코드 변경
[질의] 학번이 300인 학생의 학년을 2로 변경하라.
UPDATE STUDENT
SET Year=2
WHERE Sno=300;(2) 복수의 레코드 변경
[질의] '컴퓨터'과 과목의 학점(Credit)을 1학점씩 증가시켜라.
UPDATE COURSE
SET Credit=Credit+1
WHERE Dept='컴퓨터';(3) 부속 질의문을 이용한 변경
[질의] '컴퓨터'과 학생의 기말 성적을 5점씩 가산하라.
UPDATE ENROL
SET Final=Final+5
WHERE Sno IN
(SELECT Sno
FROM STUDENT
WHERE Dept='컴퓨터');[질의] 모든 4학년 학생의 학과를 '데이터베이스' 과목을 개설한 학과로 갱신하라.
UPDATE STUDENT
SET Dept = (SELECT Dept
FROM COURSE
WHERE Cname='데이터베이스')
WHERE Year=4;6.2.3 데이터의 삽입
기존 테이블에 행을 삽입할 경우에는 다음과 같은 형식의 INSERT문을 사용한다.
INSERT
INTO 테이블[(열_이름_리스트)]
VALUES(열_값_리스트);또는
INSERT
INTO 테이블[(열_이름_리스트)]
SELECT 문;
(1) 레코드 직접 삽입
[질의] 학번 : 600, 이름 : '박상철', 학년 : 1, 학과 : '컴퓨터'인 학생을 삽입하라.
INSERT
INTO STUDENT(Sno, Sname, Year, Dept)
VALUES(600, '박상철', 1, '컴퓨터');(2) 부속 질의문을 이용한 레코드 삽입
[질의] 학생 테이블에서 '컴퓨터'과 학생의 학번, 이름, 학년을 검색하여 테이블 컴퓨터(COMPUTER)에 삽입하라.
INSERT
INTO COMPUTER(Sno, Sname, Year)
SELECT Sno, Sname, Year
FROM STUDENT
WHERE Dept='컴퓨터';6.2.4 데이터의 삭제
SQL 테이블에서의 삭제는 기본적으로 튜플을 대상으로 한다. 기존 테이블의 레코드를 삭제할 때는 다음 형식의 DELETE문을 사용한다.
DELETE
FROM 테이블
[WHERE 조건];
(1) 하나의 레코드 삭제
[질의] 학번 100인 학생을 삭제하라.
DELETE
FROM STUDENT
WHERE Sno=100;(2) 복수의 레코드 삭제
[질의] 등록(ENROL) 테이블의 모든 행을 삭제하라.
DELETE
FROM ENROL;(3) 부속 질의문을 사용한 삭제
[질의] 과목 'C413'의 기말 성적이 60점 미만인 '컴퓨터'과 학생을 등록 테이블에서 삭제하라.
DELETE
FROM ENROL
WHERE Cno='C413' AND FINAL<60
AND ENROL.Sno IN (SELECT Sno
FROM STUDENT
WHERE Dept='컴퓨터');6.3 SQL 뷰
- 뷰(view)는 기본적으로 다른 테이블로부터 유도된 이름을 가진 가상 테이블(virtual table)을 말한다.
- 즉 기본 테이블(base table)은 물리적으로 구현되어 데이터가 실제로 저장되지만 뷰는 반드시 물리적으로 구현되어 있는 것은 아니다.
6.3.1 뷰의 생성
SQL에서 뷰를 생성할 때는 다음과 같은 형식의 CREATE VIEW문을 사용한다.
CREATE VIEW 뷰_이름[(열_이름_리스트)]
AS SELECT문
[WITH CHECK OPTION];AS SELECT문은 일반 검색문과 같은데 다만 UNION이나 ORDER BY를 사용할 수 없다. 예를 들어, STUDENT 테이블의 '컴퓨터'과 학생들로 구서된 컴퓨터과 학생 즉 'CSTUDENT'라는 뷰를 생성한다면 그 정의문은 다음과 같다.
CREATE VIEW CSTUDENT(Sno, Sname, Year)
AS SELECT Sno, Sname, Year
FROM STUDENT
WHERE Dept='컴퓨터'
WITH CHECK OPTION;다음 CREATE VIEW 정의문은 위의 정의문과 동등하다.
CREATE VIEW CSTUDENT
AS SELECT Sno, Sname, Year
FROM STUDENT
WHERE Dept='컴퓨터'
WITH CHECK OPTION;- 뷰는 사실상 학생 기본 테이블을 들여다보는 유리창(window)과 같다.
- 뷰는 실행시간에만 구체화되는 특수한 테이블이다.
- WITH CHECK OPTION; 절은 이 뷰에 대한 갱신이나 삽입 연산을 실행할 때 뷰 정의 조건(Dept='컴퓨터')을 위반하면 실행이 거부된다는 것을 명세하는 것이다.
- 뷰 정의문에서 열 이름이 명세되지 않으면 AS SELECT문에 나오는 열의 이름을 그대로 상속받는다.
- 정의하는 뷰의 어떤 열이 집계 함수나 산술식 또는 상수로부터 유도되어 열의 이름을 상속받을 수 없는 경우거나 열 이름을 상속받았을 때 열이 이름이 중복되는 경우에는 뷰의 정의문에 새로운 열의 이름을 반드시 명세하여야 한다.
CREATE VIEW DEPTSIZE(Dept, Size)
AS SELECT Dept, COUNT(*)
FROM STUDENT
GROUP BY Dept;- 이 DEPTSIZE 뷰 정의문에서 두 번째 열은 집계 함수로부터 유도되기 때문에 열의 이름을 상속받을 수 없어 Size로 명세 하였다.
- 이 DEPTSIZE 뷰는 또한 기본 테이블의 단순한 행과 열로 된 부분 집합이 아니라 기본 테이블의 통계적 요약 테이블로 볼 수 있다. 이 뷰는 다음과 같이 정의할 수도 있다.
CREATE VIEW DEPTSIZE
AS SELECT Dept, COUNT(*) AS Size
FROM STUDENT
GROUP BY Dept;뷰는 다음과 같이 두 개 이상의 테이블을 조인해서 정의할 수도 있다.
CREATE VIEW HONOR(Sname, Dept, Grade)
AS SELECT STUDENT.Sname, STUDENT.Dept, ENROL.Final
FROM STUDENT, ENROL
WHERE STUDENT.Sno=ENROL.Sno
AND ENROL.Final>90;또 일단 정의된 뷰를 이용해서 또 다른 뷰를 정의하는 예는 다음과 같다.
CREATE VIEW COMHONOR
AS SELECT Sname
FROM HONOR
WHERE Dept='컴퓨터';6.3.2 뷰의 제거
뷰의 정의는 기본 테이블처럼 ALTER문을 이용하여 변경할 수 없다. 다만 필요 없는 뷰는 제거하고 필요한 뷰를 정의하면 된다. 뷰를 제거할 때는 다음과 같은 형식의 DROP문을 사용한다.
DROP VIEW 뷰_이름{RESTRICT|CASCADE};6.3.3 뷰의 조작 연산
- 뷰는 테이블과 다름없이 검색(SELECT)문을 사용할 수 없다. 그러나 삽입(INSERT)이나 삭제(DELETE), 갱신(UPDATE)문을 뷰에 사용하는 데는 상당한 제한이 있다.
- 기본적으로 모든 뷰가 삽입, 삭제, 갱신의 대상이 되는 것은 아니다.
- 예를 들어, 다음과 같이 정의된 두 개의 뷰가 있다고 하자.
CREATE VIEW STUDENT_VIEW1
AS SELECT Sno, Dept
FROM STUDENT;
CREATE VIEW STUDENT_VIEW2
AS SELECT Sname, Dept
FROM STUDENT;- 이 두 뷰 가운데 STUDENT_VIEW1은 이론적으로 삽입, 삭제, 갱신, 검색이 가능하지만 STUDENT_VIEW2는 이론적으로 그렇지 않다.
- STUDENT_VIEW1에 레코드를 하나 삽입한다면 이것은 곧 STUDENT 기본 테이블에 삽입하는 것이 된다. 이때 뷰에 나타나지 않은 열의 값은 널(NULL)로 된다. 또 뷰 STUDENT_VIEW1에서 어떤 레코드를 삭제하면 STUDENT 테이블에서 삭제하는 것이 되고 갱신한다면 기본 테이블 STUDENT의 레코드를 갱신하는 결과가 된다.
- STUDENT_VIEW2에 대해서는 레코드를 삽입할 수 없다. 왜냐하면 STUDENT_VIEW2가 기초로 하고 있는 기본 테이블 STUDENT의 학번(Sno)은 NOT NULL로 명세되어 널(NULL)값을 가질 수 없는데 만일 STUDENT_VIEW2에 대한 레코드 삽입은 학번(Sno)의 값이 널 값으로 되기 때문이다.
- 이 뷰 STUDENT_VIEW1과 STUDENT_VIEW2의 중요한 차이는 STUDENT_VIEW1은 하나의 기본 테이블 STUDENT에서 유도된 뷰로서 기본 키를 포함하는 행의 부분 집합으로 구성되었지만 STUDENT_VIEW2는 기본 키를 포함하고 있지 않다는 것이다.
변경이 허용되지 않는 경우
- 뷰의 열이 상수나 산술 연산자 또는 함수가 사용된 산술 식으로 만들어지면 변경이 허용되지 않는다.
- 집계 함수(COUNT, SUM, AVG, MAX, MIN)가 관련되어 정의된 뷰는 변경할 수 없다
- DISTINCT, GROUP BY 또는 HAVING이 사용되어 정의된 뷰는 변경할 수 없다.
- 두 개 이상의 테이블이 관련되어 정의된 뷰는 변경할 수 없다.
- 변경할 수 없는 뷰를 기초로 정의된 뷰는 저의할 수 없다.
6.3.4 뷰의 장단점
장점
- 뷰는 데이터의 논리적 독립성을 어느 정도 제공할 수 있다. 뷰가 정의된 기본 테이블이 확장된다든지 뷰가 속해 있는 데이터베이스에 테이블이 더 늘어난다고 하더라도 기존의 뷰를 사용하는 프로그램이나 사용자는 아무런 영향을 받지 않는다.
- 뷰는 데이터의 접근을 제어함으로써 보안을 제공할 수 있다. 뷰를 통해서만 데이터를 접근하게 하면 뷰에 나타나지 않은 데이터를 안전하게 보호하는 효율적인 기법으로 사용할 수 있다.
- 사용자의 데이터 관리를 간단하고 쉽게 해 준다. 필요한 데이터만 뷰로 정의해서 처리할 수 있기 때문에 관리가 용이하고 질의문이 간단해진다.
- 여러 사용자의 상이한 응용이나 요구를 지원해 줄 수 있다. 하나의 테이블로 여러 개의 상이한 뷰를 정의하여 사용자의 요구에 따라 활용하게끔 한다.
단점
- 뷰는 그 정의를 변경할 수 없다.
- 뷰는 삽입, 삭제, 갱신 연산에 많은 제한을 가지고 있는 것 등이다.
6.4 삽입 SQL
- 삽입 SQL은 근본적으로 이중 모드(dual-mode) 원리를 가지고 있다. 이것은 터미널에서 대화식으로 사용할 수 있는 SQL문은 모두 응용 프로그램에서도 사용할 수 있다는 것이다.
6.4.1 응용 프로그램의 특징
- 응용 프로그램에서 삽입 SQL문은 명령문 앞에 'EXEC SQL'을 붙여 다른 호스트 언어의 명령문과 쉽게 구별할 수 있게 한다. 또한 삽입 SQL문 끝은 세미콜론(;)과 같은 특별한 종료 심벌을 붙여 표현한다.
- 삽입 SQL 실행문은 호스트 언어의 실행문이 사용되는 곳이면 어디나 나타낼 수 있다. 대화식 SQL과 달리 삽입 SQL문에는 실행문과 비 실행문이 있는데 DECLARE CURSOR, BEGIN, END, DECLARE SECTION과 같은 SQL문은 비 실행문이다.
- 삽입 SQL문은 호스트 변수(host variable) 즉 호스트 프로그램 변수를 포함할 수 있다. SQL문 속에서 이 호스트 변수는 다른 SQL 필드 이름과 구별하기 위해 콜론(:)을 앞에 붙인다. 호스트 변수는 대화식 SQL 조작문에서 상수 대신 사용할 수 있다. 이 호스트 변수는 또한 검색(SELECT, FETCH) 결과를 저장하는 장소를 나타내기 위해서 INTO절에 나타날 수 있다.
- SQL 문에서 사용할 호스트 변수는 사용하기 전에 반드시 삽입 SQL 선언인 BEGIN/END DECLARE SELECTION 속에서 선언되어야 한다. 이 삽입 SQL 선언부는 여러 개 있어도 무방하다.
- 모든 삽입 SQL 프로그램은 SQLSTATE라는 5문자로 된 스트링 타입의 호스트 변수를 포함한다. SQL문이 실행되면 실행 상태(성공, 실패, 오류) 표시가 이 SQLSTATE 변수를 통해 프로그램에 전달된다. 전달된 SQLSTATE 변수 값이 "00000"이면 SQL문이 성공적으로 실행된 것이고, "02000"이면 실행은 했지만 아무런 데이터도 검색하지 못했다는 것을 의미한다. 이 SQLSTATE 변수를 사용하지 않고 SQLCODE라는 정소 변수를 사용할 수도 있지만 SQLSTATE 변수를 사용하는 것이 더 표준화된 형식이다.
- 삽입 SQL문의 호스트 변수의 데이터 타입은 이에 대응하는 데이터베이스 필드의 SQL 데이터 타입과 일치해야 된다. 또 값을 전달받아야 될 필드가 너무 작으면 지정문에서 처음 숫자들이나 문자들이 탈락되고 에러 표시가 프로그램에 전달된다.
- 호스트 변수와 데이터베이스 필드의 이름은 같아도 된다. 왜냐하면 호스트 변수는 앞에 콜론을 붙여서 쉽게 구별되기 때문이다.
- 앞에서 언급했듯이 SQL문을 실행하고 나면 SQLSTATE 변수에 반환된 값을 검사해야 된다. 이 과정을 간단히 하기 위해 다음과 같은 WHENEVER문을 사용할 수 있다.
EXEC SQL WHENEVER <조건> <행동>;
6.4.2 커서가 필요 없는 데이터 조작
(1) 단일 레코드 검색
학번이 호스트 변수 :sno로 주어지는 학생의 이름과 학과를 검색하라.
EXEC SQL SELECT Sname, Dept
INTO :sname, :dept
FROM STUDENT
WHERE Sno = :sno;(2) 갱신
과목 'C413'에 등록한 학생의 기말 성적(Final)을 호스트 변수 :new 값만큼 증가시켜라.
EXEC SQL UPDATE ENROL
SET Final=Final+ :new
WHERE Cno ='C413';(3) 삭제
호스트 변수 :sno의 값을 가진 학생의 모든 등록(ENROL) 레코드를 삭제하라.
EXEC SQL DELETE
FROM ENROL
WHERE Sno = :sno(4) 삽입
호스트 변수 :sno, :sname, :dept들로 값이 주어지는 학생 레코드 하나를 학생 테이블에 삽입하라.
EXEC SQL INSERT
INTO STUDENT(Sno, Sname, Dept)
VALUES(:sno, :sname, :dept);6.4.3 커서를 이용하는 데이터 조작
커서(cursor)
- SELECT문과 호스트 프로그램 사이를 연결
- SELECT문으로 검색되는 여러개의 레코드(튜플)를 대상으로 정의됨
활동 세트(active set) : SELECT문으로 검색된 레코드 집합 실행 시에 활동 세트에 있는 레코드 해를 지시
6.4.4 다이내믹 SQL
- 다이나믹 SQL(dynamic SQL)은 특별히 일반 대화식 온라인 응용을 실행 시간(runtime)에 구성할 수 있는 삽입 SQL들로 되어 있다.
- 온라인 응용은 간단히 말해 온라인 터미널로 데이터베이스 접근을 지원하는 응용 프로그램이다.
- 따라서 이 기능은 온라인 응용 프로그램을 작성해야만 되는 사람들에게 특별히 필요한 것이다.
- 통상적인 온라인 응용의 수행 과정은 다음과 같다.
- 터미널로부터 명령문을 접수
- 입력된 명령문의 분석
- 데이터베이스에 대한 적절한 SQL 명령문으로 지시
- 터미널로 메시지나 결과를 전송
'임용 > 데이터베이스' 카테고리의 다른 글
| Chapter 08 데이터 모델링 (0) | 2023.04.20 |
|---|---|
| Chapter 07 데이터 종속성과 정규화 (0) | 2023.04.18 |
| Chapter 05 관계 대수와 관계 해석 (1) | 2023.04.13 |
| Chapter 04 관계 데이터베이스 (0) | 2023.04.12 |
| Chapter 03 데이터베이스 시스템의 구성 (0) | 2023.04.12 |