컴퓨터미디어융합학과 제 4 장 SQL - 데이터베이스 시스템 –

(1)

제4장 SQL

2013. 09. 05

가천대학교 IT대학

컴퓨터미디어융합학과

(2)

4.1 개 요 4.2 자료 정의 4.3 자료 질의

4.4 자료 갱신 4.5 스키마 변경 4.6 무결성 제약조건

4.7 익힘 문제

(3)

3

4.1 개요

SQL structured query language

상업용 RDB의 표준 언어, IBM 설계 및 구현.

SQL1: 86년 ANSI와 ISO의 합작 SQL2: 92년 확장

SQL-99: 99년 확장.

SQL3: 93년 객체지향 기능 확장

기능

자료 정의, 질의, 갱신. 뷰 정의, 보안과 권한관리, 무결성, 트랜 잭션 제어, 내장언어 및 대화식 언어.

(4)

4

SQL 장점

- 모든 RDB가 SQL 사용

- 이식성 portability: 모든 벤더에서 변경 없이 SQL 문 수행 - 단일 언어: 모든 DB 연산

- 내장 언어 사용

- DB 엔진과 SQL 언어의 분리 가능

(5)

5

4.1 개요

SQL-92의 구성

DDL

table, view 정의, 생성, 수정, 삭제

무결성 제약조건 정의, 접근권한, 특권 명령어 제공 DML

질의, 투플 삽입, 삭제, 수정 Embedded SQL

호스트 언어 안에서 SQL 코드 호출 Trigger

DB의 특정 내용이 변경되면 실행하는 동작  active DB, 자동 화

(6)

6

SQL 자료 정의문 comment on

create schema create table create view create index

create domain

(7)

7

4.2 자료 정의 DDL

Create Table table_name

( (속성이름 data type data length;)* )

Data type char(n) varchar(n) int

smallint

numeric(p,d) real

DATE, TIME

제약조건

numer int not null check (numer > 0 and number < 11);

(8)

8

RDB: 관계, 투플, 속성  SQL: 테이블, 행, 열

create TABLE customer ( cid INT,

name char(30), BOD DATE,

PRIMARY KEY (cid));

create TABLE order ( oid INT,

cid INT,

items char(20),

amount DECIMAL(15), PRIMARY KEY (oid),

FOREIGN KEY (cid) REFERENCES customer(cid));

(9)

9

4.2 자료 정의

View

create view v as <query expression>

create VIEW SN_customer as (select b_name, c_name

from deposit

where address = ‘성남’)

create index v on base_table_name(attribute name ASC[DESC])

create unique INDEX name_inx on customer(name asc);

Drop INDEX name_inx;

Index

(10)

10

Index이 필요할 때

1. 속성이 광범위한 값을 가질 때 2. 속성이 null 값을 많이 가질 때

3. 한 두 개의 속성이 where절에서 자주 사용될 때

4. 테이블이 크고 대부분의 질의가 투플의 2-4% 이하를 검색할 때

Index가 불필요할 때 1. 테이블이 작을 때

2. 속성들이 질의에서 조건으로 자주 사용되지 않을 때

3. 대부분의 질의가 테이블의 투풀의 2-4% 이상을 검색할 때 4. 테이블이 자주 갱신될 때

5. 색인 속성이 수식의 부분으로 참조될 때

(11)

11

4.2 자료 정의

뷰 View:

base table을 감추어주는 가상적인 테이블

create view best_customer as (select name

from deposit

where balance > 50,000,000);

create view all_customer as ( select name

from borrow union

( select name

from deposit);

(12)

12

스키마 변경

Drop Table

drop TABLE customer;

Alter Table

alter TABLE customer

ADD (telephone char(30));

alter TABLE customer

MODIFY (telephone char(35)); //data type, length 수정

alter TABLE customer DROP (telephone);

(13)

13

4.2.b 자료 입력

Insert

insert into customer

values (145, “kim”, “성남”);

insert into customer

(cid, address, telephone)

values (200,’서울’,’123-1234’);

insert into customer values

(select cid, name, major from client

where address = ‘성남’);

(14)

14

예제 4.2

- 고객번호, 이름, 주소, 전화를 가진 deposit과 borrow 테이블을 생성하시오.

- 각 테이블에 자료를 삽입하시오.

- 각 테이블에 amount 필드를 추가하시오.

- 각 amount 필드에 자료 값을 넣으시오.

- deposit 테이블에서 cid, name으로 구성된 view 테이블을 만드 시오.

이 view 테이블에서 자료를 검색하시오.

- deposit 의 name 의 길이를 char(25)에서 char(35)로 변경하시 오.

(15)

15

4.3 자료 질의

4.3.1 기본 질의문

Select ALL|DISTINCT <속성 리스트>

From <테이블 리스트>

Where <조건식>

group by <그룹화 속성>

having <그룹 조건>

order by <속성 리스트 ASC|DESC>

조건식: 속성1 관계연산자 속성2

관계연산자: and, or, not

집합 연산자: UNION, INTERSECTION, EXCEPT

비교 연산자: =, >, <, =<, =>, <>, between x and y,

기타 연산자: IN, NOT IN, ANY, ALL, SOME, EXIST

(16)

16

터플 변수

select C.name, C.address

from customer as C, order as O

where C.cid = O.cid and O.items = ‘사과’;

부분 문자열 연산

select name, address from customer

where name like ‘김%’ OR address like ‘_동%’;

(17)

17

4.3 자료 질의

4.3.2 집합 연산

Oracle9i는 INTERSECT Oracle9i는 MINUS

• 조건: 속성 리스트와 자료형과 자료 길이가 같아야,,,

• 합집합 교집합 차집합 select name

from deposit intersection (select name from borrow);

select name from deposit union

(select name from borrow);

select name from deposit except

(select name from borrow);

(18)

18

4.3.3 집합 비교 연산

Oracle9i 가능

select name from deposit

where balance > any ( select balance

from deposit

where name = “kim”);

select name from deposit

where balance >= ALL ( select balance

from deposit);

(19)

19

4.3 자료 질의

4.3.4 집단 함수

1. avg

2. min

3. max

4. sum

5. count

select count(*)

from customer select name, addr from deposit

where asset =

( select MAX(asset) from deposit)

select SUM(asset), AVG(asset) from customer

where address = “성남”

(20)

20

group by:그룹별로 집단 연산을 할 때, having : 그룹 연산할 때의 조건

select b-name, avg(balance) from deposit

where asset >= 50000 group by b-name

having COUNT(*) > 10;

select b-name, avg(balance) from deposit

group by b-name;

(21)

21

4.3 자료 질의

4.3.6 Nested Query: subquery

select name from borrow

where name in ( select name from deposit);

select name from borrow

where name not in ( select name from deposit);

select D.name

from borrow as B, deposit as D where B.cid = D.cid;

(22)

22

Oracle9i 가능

select name, address from CUSTOMER

where not exists // minus (select *

from CLIENT

where CUSTOMER.cid = CLIENT.cid );

select name, address from CUSTOMER

where exists // intersect (select *

from CLIENT

where CUSTOMER.cid = CLIENT.cid )

(23)

23

4.4 자료 갱신

Delete

delete from customer where cid = 215;

delete from order where cid in

(select cid

from customer

where address = ‘성남’);

(24)

24

update customer

set address = ‘수원’ // Oracle9i: 싱글 쿼트에 주의하시 오

where cid = 200

update customer

set name = ‘김기일’

where name = ‘김기익’

update customer

set did = MAX(did) + 1 where cid = 200

(25)

25

4.3 자료 질의

예제 4.3

Customer에는 cid, name, juso, phone, credit, amount가 있다.

- 자료를 적절하게 입력하시오.

- customer 에서 가장 amount가 큰 고객은?

- customer 의 평균 amount는?

- customer 에 branch 속성을 추가하고 자료 값을 넣으시오.

branch 별 amount 합계를 구하시오.

- cid가 5번인 고객의 투플을 삭제하시오.

- cid가 5번인 고객의 credit을 9로 갱신하시오.

(26)

26

Update

update orders set amount =

(select sum(amount) from o_item

where orders.oid = o_item.oid);

update customer set amount =

(select sum(amount) from orders

where customer.cid = orders.cid);

(27)

27

4.6 무결성 제약조건

4.6.1 속성 제약조건

- not null

- default

- unique: 후보 키

account_number char(10) not null balance numeric(10,2) not null

cid integer NOT NULL DEFAULT 999,

(28)

28

4.6.2 키와 참조 제약조건

cid INT PRIMARY KEY PRIMARY KEY(cid)

constraint EMPkey PRIMARY KEY(cid) - unique: 후보 키

unique(dname), primary key(eid),

foreign key(did) references DEPT(did) ON DELETE NO ACTION,

foreign key(super_eid) references EMPLOYEE(eid))

ON DELETE SET NULL ON UPDATE CASCADE,

(29)

29

4.6 무결성 제약조건

4.6.3 CHECK 문

degree char(15),

check (degree in (‘Bacheors’,’Master’,’Doctorate’))

cname char(30),

check (cname in (select cname

from CUSTOMER)) : Oracle에서 불가

amount numeric(10,2), check (amount >= 10000)

(30)

30

4.6.4 ASSERTION 제약조건의 선언적 주장을 명시

CREATE ASSERTION Order_constraint CHECK ( NOT EXISTS

(SELECT *

FROM ORDER O

WHERE (SELECT sum(qty)

FROM ORDER_BOOKS B

WHERE O.oid = B.oid ) > O.Qty);

(31)

31

4.6 무결성 제약조건

4.6.4 ASSERTION

CREATE ASSERTION salary_constraint CHECK ( NOT EXISTS

(SELECT *

FROM employee e, employee m, department d WHERE e.salary >m.salary and e.dno=d.dno and d.mgr_ssn= m.ssn));

(32)

32

DB의 특정 내용이 변경되면 실행되는 프로시저.

트리거 구성

- event: DB가 변경되어 트리거가 가동하는 사건 - condition: 트리거가 가동될 수 있는 조건

- action: 트리거가 가동하고 조건이 참일 때 수행되는 프로시저

(33)

33

4.6.5 Trigger

Create TRIGGER in_count BEFORE INSERT on Student :event

DECLARE

count INTEGER;

begin

count = 0; :action

end

Create TRIGGER i_count AFTER INSERT on Student :event

when (new.age < 19) :condition

for each row begin

count = count + 1; :action end

(34)

34

1. 다음은 은행의 여수신 관리용 관계 스키마이다. ERD를 작성하시오 customer(cid, name, addr, credit)

deposit(d#, cid, bid, balance) bid: branch id borrow(b#, cid, bid, amount)

2. 앞의 관계 스키마를 보고 다음 질의를 SQL로 작성하시오

credit이 8이상 되는 고객의 이름은? (credit은 1부터 10까지) 대출받은 고객의 명단은?

“5,000원 이상 저축한 고객의 이름과 주소는?

“5,000원 이상 저축하고 10,000원 이상 대출받은 고객은?

지점별로 대출금 현황은?

(35)

35

4.7 익힘 문제

3. 다음 질의를 SQL로 작성하시오 고객 테이블을 생성하시오.

고객 테이블의 name을 index로 추가하여 설정하시오.

고객에서 이름과 주소만으로 새로운 view 테이블을 생성하시오.

고객 “kim”씨의 주소를 수원으로 갱신하시오.

고객의 이름을 char(25)에서 char(30)으로 수정하시오.

고객 테이블에 income 필드(real)를 추가하시오.

income의 범위가 500에서 5000 범위 밖이면 오류 처리하시오 deposit 테이블에서 게좌번호 340번을 제거하시오

borrow 테이블 자체를 제거하시오.

저축 테이블에서 잔고가 100원 이하가 되면 경고 메시지를 인쇄하시오.

(36)

36

4. 다음은 건설회사 공사를 위한 인력관리용 관계 스키마이다. ERD를 작성 하시오

Employee(eid,e_name,address,major,did, mid) mid: manager id Department(did,d_name,mid,budget)

Project(pid, p_name,did, hid, amount, location) hid: head id 5. 앞의 관계 스키마를 보고 다음 질의를 SQL로 작성하시오

“인사부”에 근무하는 직원을 찾으시오.

“기계”를 전공하는 직원이 근무하는 부서의 이름은?

“성남”에 근무하는 직원들의 이름과 주소는?

“토목부”에 소속된 공사 현장 소장의 이름은?

(37)

37

4.7 익힘 문제

6. SQL 연습 과제

인터넷서점을 위한 DB를 구축하려고 한다.

1) 고객 테이블, 주문 테이블, 주문내역테이블을 생성하시오.

customer(cid, name, address)

orders(oid, sdate, items, amount, cid) o_items(oid, name, price, qty, amount)

2) 각 테이블에 적절한 자료를 입력하시오.

3) ‘성남’에 사는 고객의 이름은?

4) 1000원 어치 주문한 고객의 이름을 찾으시오.

5) ‘감자’를 구매한 일자를 찾으시오.

6) ‘감자’를 구매한 고객의 이름을 검색하시오.

7) 주문 금액의 합계와 평균 금액을 계산하시오.

고객 테이블 고객번호 성명 주소

1

2 김가천 성남

3

주문 테이블

주문번호 일자 주요품목 금액 고객번호 3 2013-03-03 감자 외 1000 2

주문내역 테이블

주문번호 품명 단가 수량 금액

3 감자 20 10 200

3 배추 40 20 800

(38)

38

- ‘대구농원’의 상품을 구매한 고객의 주소를 찾아라

3

주문 테이블

주문번호 일자 주요품목 금액 고객번호 결제일자 3 2013-03-03 감자 외 1000 2

주문내역 테이블

주문번호 상품명 단가 수량 금액 상품번호

3 감자 30 10 300 4

3 배추 35 20 700 11

상품재고 테이블

상품번호 상품명 모델 공급자 단위 재고수량 원가 1

2 3

4 감자 호일15 대구농원 상자 20 30