Transcript PowerPoint 프레젠테이션

3.5 SQL (Structured Query Language)
1. 개념
- IBM의 관계형 테이터베이스 언어인 SEQUEL에서 유래된 데이터 종속어
- 미국표준연구소(ANSI)와 국제표준기구(ISO)에서 관계 데이터베이스 표준언어로 채택
- 데이터 정의어(DDL), 데이터 조작어(DML), 데이터 제어어(DCL) 포함
- 데이터의 검색 및 삽입, 삭제, 수정 기능 제공
- 비절차적 언어
- 테이블, 행, 열과 같은 일반적 용어 사용
2. 데이터 정의어(DDL)
- 스키마(scheme), 도메인(domain), 테이블(table), 뷰(view), 인덱스(index) 정의에 사용
(1) CREATE 문
① 스키마 정의
CREATE SCHEMA 스키마_이름 AUTHORIZATION 사용자_이름 ;
② 도메인 정의
CREATE DOMAIN 도메인_이름 데이터_타입 [묵시적_정의] [도메인_제약조건] ;
- 테이터 타입
. 숫자 : integer(int), smallint, float, real, double, precision
. 문자 : char(n), varchar(n)
. 비트 : bit, bit varying(n)
. 날짜 : date
. 시간 : time
- 묵시적_정의 : default 값, null
- 도메인_제약조건 : 모든 열에 정의될 무결성 제약조건
③ 기본 테이블 생성
CREATE TABLE 테이블_이름
{ 열이름 데이터_타입 [NOT NULL] [DEFULT 값], }
[ PRIMARY KEY (열이름_리스트)] [UNIQUE (열이름_리스트) ]
{ FOREIGN KEY (열이름_리스트)
REPERENCES 기본테이블[(열이름_리스트)] };
- NOT NULL : NULL값을 허용하지 않으려면 반드시 선언
- PRIMARY KEY : 한 개만 선언해야 하는 것이 아님
- UNIQUE : 대체키
- FOREIGN KEY : 참조무결성 유지
④ 인덱스 테이블 생성
CREATE INDEX 인덱스_이름 ON 테이블_이름(열이름_리스트);
(2) DROP 문
- 데이터베이스, 스키마, 도메인, 테이블, 뷰, 인덱스의 삭제
DROP DATABASE 데이터베이스_이름 [CASCADE | RESTRICT] ;
DROP SCHEMA 스키마_이름 [CASCADE | RESTRICT] ;
DROP TABLE 테이블_이름 CASCADE ;
참조하는 테이블도 자동 삭제
DROP TABLE 테이블_이름 RESTRICT ;
참조중이면 삭제되지 않음
(3) ALTER 문
① 기본 테이블 변경
- 열 추가
ALTER TABLE 테이블이름 ADD 열이름 데이터타입 ;
- 열 삭제
ALTER TABLE 테이블이름 DROP 열이름 ;
- 디폴트 값 변경
ALTER TABLE 테이블이름 ALTER 열이름 SET DEFAULT 디폴트_값 ;
3. 데이터 조작어(DML)
- 검색(SELECT), 삽입(INSERT), 삭제(DELETE), 변경(UPDATE)
- 데이터베이스 테이블 예 (테이블_이름 : sky)
no
name
sex
milage
grade
100
김경아
F
3500
B
101
권태수
M
4500
A
102
권현정
F
2600
C
103
조효숙
F
2800
C
104
김윤환
M
3100
B
(1) SELECT 문
SELECT 열_리스트
FROM 테이블_리스트
[WHERE 조건] ;
- sky테이블에서 성별이 여자이고 마일리지가 3000이상인 사람의
no, name, grade을 추출하는 SQL문
SELECT no, name, grade
FROM sky
where sex=‘F’ AND milage >= 3000 ;
SELECT [ALL|DISTINCT] 열_리스트
FROM 테이블_리스트
[WHERE 조건]
[GROUP BY 열_리스트 [HAVING 조건]]
[ORDER BY 열_리스트 [ASC|DESC]];
- 테이블의 열 전부를 검색
SELECT *
FROM sky ;
- 테이블의 중복된 값 제거
SELECT DISTINCT sex
FROM sky ;
- 그룹을 이용한 검색 (성별이 여자인 사람들의 마일리지 평균값)
SELECT AVG(milage)
FROM sky
GROUP BY sex HAVING sex=‘F’ ;
- 정렬하여 표시 (여자승객을 마일리지는 오름차순으로 레벨은 내림차순으로 정렬)
SELECT name, milage
FROM sky
WHERE sex = ‘F’
ORDER BY milage ASC, grade DESC
- 산술식과 문자열 추가
SELECT name, ‘는 마일리지가’, milage+2000
FROM sky ;
- 집계함수 사용 (COUNT, SUM, AVG, MAX, MIN)
. sky 테이블의 레코드 수 검색
SELECT COUNT(*)
FROM sky ;
. 마일리지의 평균값
SELECT AVG(milage)
FROM sky ;
- 부분매치 (성이 김씨인 사람의 이름과 마일리지를 검색)
SELECT name, milage
FROM sky
WHERE name LIKE ‘김%’
(2) INSERT 문
INSERT INTO 테이블_이름 [열_리스트]
VALUE (열값_리스트)
INSERT INTO sky (no, name, milage, sex, grade)
VALUE (105, ‘권정숙’, 3600, ‘F’, ‘B’)
(3) UPDATE 문
UPDATE 테이블_이름 SET 열이름=산술식{, 열이름=산술식}
WHERE 조건식
UPDATE sky SET milage=2600, grade=‘C’
WHERE name=‘권정숙’
(4) DELETE 문
DELETE FROM 테이블_이름 [WHERE 조건식]
DELETE FORM sky WHERE milage <= 2000
4. 데이터 제어어(DCL)
(1) GRANT 문 : 기존 사용자나 그룹의 특정 권한을 허용
(2) REVOKE 문 : 기존 사용자나 그룹의 특정 권한을 해제
(3) BEGIN TRANSACTION 문 : 트랜잭션의 시작을 명시적으로 표시
(4) COMMIT 문 : 트랜잭션을 종료하고 데이터 변경을 확정
(5) ROLLBACK 문 : 트랜잭션을 취소하고 데이터의 변경을 이전 상태로 복구
5. 내장 SQL
- 응용프로그램 속에 삽입되는 SQL (SQL문에 ‘EXEC SQL’을 추가하여 사용)
- 호스트 변수(:NAME)를 참조 가능하며, 이것은 SQL에서는 상수로 간주
- 호스트 변수와 데이터베이스 필드의 타입은 일치
- 호스트 변수와 데이터베이스 필드 이름은 별개로 관리/사용되고, 중복 사용이 가능
(이름은 같아도 된다)
- SQL CODE 값이 음수이면 에러, 양수이면 경고, 0이면 정상 수행임을 의미
3.6 SQL 뷰(view)
- 기존 테이블로부터 유도되어 만들어진 가상 테이블
. 뷰 위에 다른 뷰를 정의할 수 있다.
. 기존 테이블이 제거되면 뷰도 자동으로 제거된다.
- 논리적인 독립성 제공 (외부 스키마는 뷰의 기본 테이블의 정의로 구성)
- 데이터의 접근을 제어함으로 보안 제공
- 사용자 측면에서 데이터 관리 간단 (필요한 데이터만 뷰로 만들어서 처리)
- 여러 사용자의 상이한 응용이나 요구를 지원 (하나의 테이블로 여러 개의 상이한 뷰를 정의)
- 뷰의 단점
. 독자적인 인덱스를 가질 수 없다.
. 뷰의 정의는 변경 불가 (ALTER 문 사용 불가)
. 검색은 자유로우나, 삽입, 삭제, 갱신 연산에는 많은 제한
- SQL 뷰의 생성
CREATE VIEW 마일리지(name, mailage)
AS SELECT name, mailage
FROM sky
- SQL 뷰의 삭제
DROP VIEW 뷰_이름
3.7 시스템 카탈로그 (System Catalog)
- 시스템 자체의 여러 가지 객체에 대한 정보가 들어있는 시스템 데이터베이스
- 기본테이블, 뷰, 인덱스, 사용자, 응용계획, 접근 권한 등의 정보를 포함
- 데이터 사전(data dictionary)
- 테이블로 구성
- SELECT는 가능하나 UPDATE, DELETE, INSERT 문으로 직접 갱신하는 것은 불가능
◆ 다시 한 번
1. SQL
① 데이터 정의어(DDL) : CREATE, DROP, ALTER
② 데이터 조작어(DML) : SELECT, INSERT, UPDATE, DELETE
③ 데이터 제어어(DCL) : GRANT, REVOKE, BEGIN TRANSACTION, COMMIT, ROLLBACK
2. SQL 뷰
- 기존 테이블로부터 유도되어 만들어지는 가상테이블
- 논리적 독립성과 보안 제공
- 사용자 측면에서 관리 간단
- 인덱스 가질 수 없으며, 뷰의 정의 변경이 불가능하고, 삽입/삭제/갱신 제한
3. 시스템 카탈로그
- 시스템 자체의 여러 가지 객체에 대한 정보가 있는 시스템 데이터베이스
- 데이터 사전
기출 . 예상 문제
1. SQL의 기술이 옳지 않은 것은?
① SELECT FROM WHERE
② INSERT INTO VALUES
③ UPDATE TO WHERE
④ DELETE FROM WHERE
2. SQL 언어의 데이터 제어어(DCL)에 해당하는 것은?
① SELECT
② INSERT
③ UPDATE
④ GRANT
3. SQL 언어의 데이터 조작어(DML)에 속하지 않는 것은?
① INSERT
② SELECT
③ UPDATE
④ ADD
기출 . 예상 문제
4. 스키마, 도메인 테이블, 뷰, 인덱스의 제거시에 사용되는 SQL 정의어는?
① CREATE
② DROP
③ ALTER
④ CLOSE
5. SQL 뷰의 생성을 위한 정의어는?
① CREATE
② ALTER
③ UPDATE
④ DROP
6. SQL 문의 DROP문에 관한 설명 중 잘못된 것은?
① 해당 테이블에 삽입된 tuple들도 없어진다.
② 해당 테이블에 대해 만들어진 index가 없어진다.
③ 해당 테이블에 대해 만들어진 view가 없어진다.
④ 해당 테이블에 참조관계가 있는 테이블이 없어진다.
기출 . 예상 문제
7. SQL 언어의 CREATE TABLE문에 포함될 수 없는 것은?
① 속성의 NOT NULL 제약조건
② 속성의 타입 변경
③ 속성의 초기값 설정
④ CHECK 제약 조건의 정의
8. SQL 문장에서 GROUP BY 절에 의해 선택된 그룹의 탐색조건을 지정할 수 있는 것은?
① HAVING
② ORDER BY
③ JOIN
④ UNION
9. 수강 테이블에서 과목명이 ‘DB’인 모든 튜플을 성적으로 정렬하여 보려고 한다. (기말성적은
내림차순으로 하고, 중간성적은 오름차순으로 한다.) 밑줄 친 내용으로 맞는 것은?
SELECT * FROM 수강 WHERE 과목명=‘DB’
① ORDER BY 기말성적 DESC 중간성적 ASC
② ORDER BY 기말성적 ASC 중간성적 DESC
③ HAVING 기말성적 DESC 중간성적 ASC
④ HAVING 기말성적 ASC 중간성적 DESC
기출 . 예상 문제
10. 하나 또는 둘 이상의 기본 테이블로부터 유도되어 만들어지는 가상 테이블은?
①뷰
② 시스템 카탈로그
③ 스키마
④ 데이터 디렉토리
11. 데이터베이스 관리자가 기본 테이블에서 임의로 유도하여 만드는 테이블로서 사용자에게 접근이
허용된 자료만을 제한적으로 보여주기 위한 테이블을 무엇이라 하는가?
① 임시 테이블(temporary table)
② 뷰 테이블(view table)
③ 색인 테이블(index table)
④ 기본 테이블(base table)
12. 뷰(view)에 대한 설명 중 가장 거리가 먼 것은?
① 뷰는 원칙적으로 하나 이상의 기본 테이블로부터 유도된 이름을 가진 가상의 테이블이다.
② 기본 테이블은 물리적으로 구현되어 데이터가 실제로 저장되지만 뷰는 물리적으로 구현
되어 있지 않다.
③ 뷰는 기본 테이블로부터 유도되지만 일단 정의된 뷰는 또 다른 뷰의 정의에 사용될 수 있다.
④ 뷰의 정의만 시스템 내에 저장하였다가 필요시 테이블을 구축하므로 검색에 있어 뷰와 기본
테이블 사이에는 약간의 차이가 있다.
기출 . 예상 문제
13. 데이터베이스 내용에 대한 전체적인 뷰라고 볼 수 있는 스키마는?
① 외부스키마
② 개념스키마
③ 내부스키마
④ 서브스키마
14. 뷰(view)의 장점으로 거리가 먼 것은?
① 데이터의 논리적 독립성을 보장한다.
② 데이터에 대한 보안이 제공된다.
③ 삽입, 삭제, 갱신 연산에 유연성을 제공한다.
④ 같은 데이터를 다양한 방법으로 볼 수 있게 한다.
15. 시스템 카탈로그에 대한 설명으로 부적합한 것은?
① 데이터베이스 시스템에 따라 상이한 구조를 가진다.
② 사용자도 SQL을 이용하여 검색할 수 있다.
③ 테이블, 데이터베이스, 뷰, 접근 권한 등에 대한 정보를 포함하고 있다.
④ 사용자 데이터베이스이다.