[데이타베이스론] CH6. SQL

SQL의 역사

-SEQUEL(Structured English Query Language)

• 1974년, IBM 연구소에서 발표
• IBM은 ‘SYSTEM R’의 인터페이스로 설계 구현
• 실험적 관계 데이터베이스 시스템의 인터페이스

-SQL(Structured Query Language)

-표준 SQL

• 1986년: SQL-86 OR SQL1
• 1992년 개정: SQL/92, SQL-92 OR SQL2
• 1999년: SQL3, SQL-99
• 2003년: SQL4, SQL-2003, 객체 개념 지원하는 기능 추가

-현재

• 미국 표준 연구소(ANSI)와 국제 표준 기구(ISO)에서 관계 데이터베이스의 표준 언어로 채택
• 상용 DBMS인 IBM의 DB2와 SQL/DS의 데이터 언어로 사용
• ORACLE, INFORMIX, SYBASE 등과 같은 모든 주요 제품에서 채택

SQL(Structured Query Language)

의미

구조화 질의어

-종합 데이터베이스 언어 역할

• 단순히 검색만을 위한 데이터 질의어가 아님

데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL) 기능 모두 제공

특징

관계 대수 + 확장된 투플 관계 해석 기초

고급, 비 절차적 데이터 언어_사용자 친화적인 인터페이스 제공

-SQL의 표준화

• 상용 DBMS간의 전환이 용이
• 여러 관계 데이터베이스를 접근하는 데이터베이스 응용 프로그램의 작성을 지원

터미널 ~> 대화식 질의어로 사용

응용 프로그램에 삽입된 형태로도 사용 가능

ㄴJava, COBAL, C, C++ 등과 같은 범용 프로그래밍 언어로 된 응용 프로그램

개개의 레코드 단위로 처리 < 레코드 집합 단위로 처리

선언적 언어

ㄴSQL 명령문에는 데이터 처리를 위한 접근 경로(access path)에 대한 명세 불필요

*Notes

관계 모델의 공식 용어 대신 일반 용어 사용

릴레이션 - 테이블/ 투플 - 행/ 애트리뷰트 - 열

SQL 데이터 정의문

스키마(Schema)

하나의 응용(사용자)에 속하는 테이블과 기타 구성요소 등의 그룹

포함 내용: 스키마 이름(schema name), 스키마를 소유하는 허가권자(authorization identifier), 테이블, 뷰, 도메인, 기타 내용에 대한 기술자(descriptor)

-CREATE SCHEMA UNIVERSITY AUTHORIZAITON SHLEE;

• CREARE SCHEMA 대신 CREATE DATABSE 명령문도 사용 가능
• 스키마는 DBA로부터 허가를 받은 사용자만 소유 가능

카탈로그(Catalog)

SQL 시스템 내에서의 한 스키마 집합

하나의 특별한 스키마 Information_schema를 포함

ㄴ그 카탈로그에 있는 모든 스키마에 대한 정보 제공

도메인 정의문

일반 형식

CREATE DOMAIN 도메인_이름 데이터_타입
[기정 값_정의]
[도메인_제약조건_정의리스트];

ex.

CREATE DOMAIN DEPT CHAR(4)
	DEFAULT ‘???’
		CONSTRAINT VALID-DEPT
		CHECK(VALUE IN
			(’COMP’, ‘ME’, ‘EE’, ‘ARCH’, ‘???’));

-ALTER DOMAIN 도메인_이름 <변경 내용>

-DROP DOMAIN 도메인_이름 RESTRICT | CASCADE;

• RESTRICT: 다른 곳에서 이 도메인을 참조하고 있지 않을 때 삭제
• CASCADE: 이 도메인을 참조하고 있는 뷰나 제약조건도 삭제

데이터 타입

-숫자

• INT(EGER), SMALLINT: 정수
• FLOAT(n), REAL, DOUBLE PRECISION: 실수
• DECIMAL(i, j), NUMERIC(i, j): 정형 숫자

-문자 스트링

• CHAR(n): 고정 길이 문자
• VARCHAR(n): 가변 길이 문자

-비트 스트링

• BIT(n), BIT VARYING(n)

-날짜

• DATE: YY-MM-DD

-시간

• TIME: hh:mm:ss
• TIMESTAMP: DATE와 TIME 포함
• INTERVAL: DATE, TIME, TIMESTAMP 포함

기본 테이블의 생성

종류

-기본 테이블(base table)

• CREATE TABLE 문으로 만들어지는 테이블
• DBMS의 화일로 생성되고 저장

-가상 테이블(virtual table)

• CREATE VIEW 문으로 만들어지는 테이블
• 어떤 기본 테이블로부터 유도되어 만들어지는 테이블
• 독자적으로 존재 불가

-임시 테이블(temporary table)

• 질의문 처리 과정의 중간 결과로 만들어지는 테이블
• DDL 문으로 만들어지는 것X

일반형식

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에서는 참조된 테이블의 후보 키도 외래 키로 될 수 있음

ON DELETE SET NULL: 참조 투플(열 값) 삭제 → NULL 설정

ON UPDATE CASCADE: 참조 투플(열 값) 갱신 → 갱신된 값 파극적으로 갱신

CHECK: 제약조건을 명세

ex.

CREATE TABLE ENROL
	(Sno INTEGER NOT NULL,
	Cno CHAR(6) NOT NULL,
	PRIMARY KEY (Sno, Cno),
	Grade INTEGER,
	FOREIGN KEY(Sno) REFERENCES STUDENT(Sno)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY(Cno) REFERENCES COURSE
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	CHECK(Grade ≥ 0 AND Grade ≤ 100));

 

기본 테이블의 제거와 변경

기본 테이블의 제거

-일반 형식

DROP TABLE 기본_테이블_이름
	{ RESTRICT | CASCADE };

• RESTRICT: 참조하는 뷰나 제약조건이 있으면 실행 실패
• CASCADE: 참조 뷰나 제약조건도 모두 삭제

DROP TABLE COURSE CASCADE;

-스키마 제거

일반 형식

DROP SCHEMA 스키마_이름
	{ RESTRICT | CASCADE };
DROP SCHEMA UNIVERCITY CASCADE;

기본 테이블의 변경

-일반 형식

ALTER TABLE 기본_테이블_이름

ALTER TABLE 기본_테이블_이름;
	([ADD 열_이름 데이터_타입] [DEFAULT 기정 값] |
	[DROP 열_이름] [CASCAD] |
	[ALTER 열_이름 (DROP DEFAULT | SET DEFAULT 기정 값)]);

ex.

ALTER TABLE ENROL
	ADD Final CHAR DEFAULT ‘F’;
ALTER TABLE ENROL
	DROP Grade CASCADE;

-DROP 열_이름

• RESTRICT: 참조하는 뷰나 제약조건이 있으면 실행 실패
• CASCADE: 참조 뷰나 제약조건도 모두 삭제

SQL 데이터 조작문

데이터 검색

-기본 구조

SELECT 열_리스트
FROM 테이블_리스트
WHERE 조건;

ex.

SELECT Sname, Sno
FROM STUDNET
WHERE Dept = ‘컴퓨터’;

SELECT STUDNET.Sname, STUDENT.Sno
FROM STUDENT
WHERE STUDENT.Dept = ‘컴퓨터’;

데이터 검색

-폐쇄 시스템(closed system)

• 테이블 처리 결과가 또 다시 테이블이 되는 시스템
• 중첩 질의문(nested query)을 구성할 수 있는 이론적 기초

-SQL과 이론적 relational model 차이점_SQL 테이블

• 한 테이블 내에 똑같은 레코드(행) 중복 가능
• 기본 키를 반드시 가져야 하는 것은 아님
• 이론상 SQL의 테이블은 투플의 집합이 아님
• 같은 원소의 중복을 허용하는 다중 집합(multisest) OR 백(bag)

→ DISTINCT 명세: 집합과 같은 결과를 만듦

-일반적인 형식

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;

3. 조건 검색

[질의] 학생(STUDENT) 테이블에서 학과(Dept)가 ‘컴퓨터’이고 학년(Year)이 4인 학생의 학번(Sno)과 이름(Sname)을 검색하라.

SELECT Sno, Sname
FROM STUDENT
WHERE Dept = ‘컴퓨터’ AND Year = 4;

4. 순서 영세 검색

[질의] 등록(ENROL) 테이블에서 중간 성적(Midterm)이 90점 이상인 학생의 학번(Sno)과 과목 번호(Cno)를 검색하되 학번(Sno)에 대해서는 내림차순으로, 또 같은 학번에 대해서는 과목 번호(Cno)의 오름차순으로 검색하라.

SELECT Sno, Cno
FROM ENROL
WHERE Midterm ≥ 90
ORDER BY Sno DESC, Cno ASC;

5. 산술식, 문자 스트링, 새로운 열 이름 명세

[질의] 등록(ENROL) 테이블에서 과목 번호(Cno)가 ‘C312’인 중간 성적(Midterm)에 3점을 더한 점수를 ‘학번’, ‘중간성적 =’ 이란 텍스트 내용을 ‘시험’, 그리고 ‘점수’라는 열 이름으로 검색하라.

SELECT Sno AS 학번, ‘중간시험 =‘ AS 시험,
Midterm + 3 AS 점수
FROM ENROL
WHERE Cno = ‘C312’;

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’;

7. 자기 자신의 테이블에 조인하는 검색

[질의] 같은 학과 학생의 학번을 쌍으로 검색하라. 단, 첫 번째 학번은 두 번째 학번보다 작게 하라.

SELECT S1.Sno, S2.Sno
FROM STUDENT S1, STUDENT S2
WHERE S1.Dept = S2.Dept AND S1.Sno < S2.Sno;

• FROM 절에 조인 명세

1. 테이블1 JOIN 테이블2 ON 조건식

SELECT Sname, Dept, Grade
FROM STUDENT JOIN ENROL ON(STUDENT.Sno = ENROL.Sno)
WHERE ENROL.Cno = ‘C413’

2. 테이블1 JOIN 테이블2 USING(열_리스트)

SELECT Sname, Dept, Grade
FROM STUDENT JOIN ENROL USING(Sno)
WHERE ENROL.Cno = ‘C413’;

3. 테이블1 NATURAL JOIN 테이블2

SELECT Sname, Dept, Grade
FROM STUDENT NATURAL JOIN ENROL
WHERE ENROL.Cno = ‘C413’;

8. 집계 함수 이용 검색

-집계 함수(aggregate function)

• COUNT: 값의 개수
• SUM: 값의 총계
• AVG: 평균 값
• MAX: 최대 값
• MIN: 최소 값

[질의] 학생 테이블에 학생 수가 얼마인가를 검색하라.

SELECT COUNT(*) AS 학생수
FROM STUDNET;

[질의] 학번(Sno)dl 300인 학생이 등록한 과목(Cno)은 몇 개인가?

SELECT COUNT(DISTINCT Cno)
FROM ENROL
WHERE Sno = 300;

[질의] 과목 ‘C413’에 대한 중간 성적의 평균은 얼마인가?

SELECT AVG(Midterm) AS 중간평균
FROM ENROL
WHERE Cno = ‘C413’;

9. GROUP BY 이용 검색

[질의] 과목별 기말 성적(Final)의 평균을 검색하라.

SELECT Cno, AVG(Final) AS 기말평균
FROM ENROL
GROUP BY Cno;

10. HAVING 사용 검색

[질의] 3명 이상 등록한 과목의 기말 평균 성적을 검색하라.

SELECT Cno, AVG(Final) AS 평균
FROM ENROL
GROUP BY Cno
HAVING COUNT(*) ≥ 3;

11. 부속 질의문(Subquery) 사용 검색

-부속 질의문

• 다른 질의문에 중첩(nested)되어 사용된 검색문
• 형태 : SELECT-FROM-WHERE-GROUP BY-HAVING
• 중첩 질의문: 부속 질의문을 포함하고 있는 질의문
• IN 다음에 사용: 집합의 멤버십 연산자(∈)로 해석

[질의] 과목 번호(Cno) ‘C413’등록한 학생 이름(Sname)을 검색하라.

SELECT Sname
FROM STUDENT
WHERE Sno In
	(SELECT Sno
	FROM ENROL
	WHERE Cno = ‘C413’);

[질의] 과목 번호 ‘C413’에 등록하지 않은 학생의 이름을 검색하라.

SELECT Sname
FROM STUDENT
WHERE Sno NOT IN
	(SELECT Sno
	FROM ENROL
	WHERE Cno = ‘C413’);

[질의] 학생 ‘정기태’와 같은 학과에 속하는 학생의 이름과 학과를 검색하라.

SELECT Sname, Dept
FROM STUDENT
WHERE Dept =
	(SELECT Dept
	FROM STUDNET
	WHERE Sname = ‘정기태’);

[질의] 등록(ENROL) 테이블에서 학번이 500인 학생의 모든 기말 성적보다 좋은 학기말 성적을 받은 학생의 학번과 과목번호를 검색하라.

SELECT Sno, Cno
FROM ENROL
WHERE Final > ALL
	(SELECT Final
	FROM ENROL
	WHERE Sno = 500);

데이터 삽입

일반 형식

INSERT
INTO 테이블 [ (열_이름_리스트)]
VALUES (열값_리스트);

INSERT
INTO 테이블 [ (열_이름_리스트)]
SELECT 문;

레코드의 직접 삽입

INSERT
INTO STUDENT(Sno, Sname, Year, Dept)
VALUES (600, ‘박상철’, 1, ‘컴퓨터’);

INSERT
INTO STUDENT
VALUES (600, ‘박상철’, 1, ‘컴퓨터’);

INSERT
INTO STUDENT(Sno, Sname, Year)
VALUES (600, ‘박상철’, 1);