- 데이터베이스시스템 2013. 09. 05 가천대학교 IT 대학 컴퓨터미디어융합학과
목차 4.1 개요 4.2 자료정의 4.3 자료질의 4.4 자료갱신 4.5 스키마변경 4.6 무결성제약조건 4.7 익힘문제
4.1 개요 SQL structured query language 상업용 RDB의표준언어, IBM 설계및구현. SQL1: 86년 ANSI와 ISO의합작 SQL2: 92년확장 SQL-99: 99년확장. SQL3: 93년객체지향기능확장기능자료정의, 질의, 갱신. 뷰정의, 보안과권한관리, 무결성, 트랜잭션제어, 내장언어및대화식언어. 3
4.1 개요 SQL 장점 - 모든 RDB가 SQL 사용 - 이식성 portability: 모든벤더에서변경없이 SQL 문수행 - 단일언어 : 모든 DB 연산 - 내장언어사용 - DB 엔진과 SQL 언어의분리가능 4
4.1 개요 SQL-92의구성 DDL table, view 정의, 생성, 수정, 삭제무결성제약조건정의, 접근권한, 특권명령어제공 DML 질의, 투플삽입, 삭제, 수정 Embedded SQL 호스트언어안에서 SQL 코드호출 Trigger DB 의특정내용이변경되면실행하는동작 active DB, 자동 화 5
4.2 자료정의 DDL SQL 자료정의문 comment on create schema create table create view create index create domain 6
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); 7
4.2 자료정의 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)); 8
4.2 자료정의 View create view v as <query expression> create VIEW SN_customer as (select b_name, c_name from deposit where address = 성남 ) Index create index v on base_table_name(attribute name ASC[DESC]) create unique INDEX name_inx on customer(name asc); Drop INDEX name_inx; 9
4.2 자료정의 Index 이필요할때 1. 속성이광범위한값을가질때 2. 속성이 null 값을많이가질때 3. 한두개의속성이 where절에서자주사용될때 4. 테이블이크고대부분의질의가투플의 2-4% 이하를검색할때 Index가불필요할때 1. 테이블이작을때 2. 속성들이질의에서조건으로자주사용되지않을때 3. 대부분의질의가테이블의투풀의 2-4% 이상을검색할때 4. 테이블이자주갱신될때 5. 색인속성이수식의부분으로참조될때 10
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); 11
4.2 자료정의 스키마변경 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); 12
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 = 성남 ); 13
4.2 자료정의 예제 4.2 - 고객번호, 이름, 주소, 전화를가진 deposit과 borrow 테이블을생성하시오. - 각테이블에자료를삽입하시오. - 각테이블에 amount 필드를추가하시오. - 각 amount 필드에자료값을넣으시오. - deposit 테이블에서 cid, name으로구성된 view 테이블을만드시오. 이 view 테이블에서자료를검색하시오. - deposit 의 name 의길이를 char(25) 에서 char(35) 로변경하시 오. 14
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 15
4.3 자료질의 터플변수 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 _ 동 % ; 16
4.3 자료질의 4.3.2 집합연산 Oracle9i는 INTERSECT Oracle9i는 MINUS 조건 : 속성리스트와자료형과자료길이가같아야,,, 합집합교집합차집합 select name from deposit union (select name from borrow); select name from deposit intersection (select name from borrow); select name from deposit except (select name from borrow); 17
4.3 자료질의 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); 18
4.3 자료질의 4.3.4 집단함수 1. avg 2. min 3. max 4. sum 5. count select SUM(asset), AVG(asset) from customer where address = 성남 select count(*) from customer select name, addr from deposit where asset = ( select MAX(asset) from deposit) 19
4.3 자료질의 4.3.5 group by와 having 절 group by: 그룹별로집단연산을할때, having : 그룹연산할때의조건 select b-name, avg(balance) from deposit group by b-name; select b-name, avg(balance) from deposit where asset >= 50000 group by b-name having COUNT(*) > 10; 20
4.3 자료질의 4.3.6 Nested Query: subquery select name from borrow where name in ( select name from deposit); select D.name from borrow as B, deposit as D where B.cid = D.cid; select name from borrow where name not in ( select name from deposit); 21
4.3 자료질의 4.3.7 빈관계에대한연산 Oracle9i 가능 select name, address from CUSTOMER where exists // intersect (select * from CLIENT where CUSTOMER.cid = CLIENT.cid ) select name, address from CUSTOMER where not exists // minus (select * from CLIENT where CUSTOMER.cid = CLIENT.cid ); 22
4.4 자료갱신 Delete delete from customer where cid = 215; delete from order where cid in (select cid from customer where address = 성남 ); 23
4.4 자료갱신 Update update customer set address = 수원 오 where cid = 200 // Oracle9i: 싱글쿼트에주의하시 update customer set name = 김기일 where name = 김기익 update customer set did = MAX(did) + 1 where cid = 200 24
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로갱신하시오. 25
4.4 자료갱신 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); 26
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, 27
4.6 무결성제약조건 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, 28
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) 29
4.6 무결성제약조건 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); 30
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)); 31
4.6.5 Trigger DB 의특정내용이변경되면실행되는프로시저. 트리거구성 - event: DB가변경되어트리거가가동하는사건 - condition: 트리거가가동될수있는조건 - action: 트리거가가동하고조건이참일때수행되는프로시저 32
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 33
4.7 익힘문제 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원이상대출받은고객은? 지점별로대출금현황은? 34
4.7 익힘문제 3. 다음질의를 SQL로작성하시오고객테이블을생성하시오. 고객테이블의 name을 index로추가하여설정하시오. 고객에서이름과주소만으로새로운 view 테이블을생성하시오. 고객 kim 씨의주소를수원으로갱신하시오. 고객의이름을 char(25) 에서 char(30) 으로수정하시오. 고객테이블에 income 필드 (real) 를추가하시오. income의범위가 500에서 5000 범위밖이면오류처리하시오 deposit 테이블에서게좌번호 340번을제거하시오 borrow 테이블자체를제거하시오. 저축테이블에서잔고가 100원이하가되면경고메시지를인쇄하시오. 35
4.7 익힘문제 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로작성하시오 인사부 에근무하는직원을찾으시오. 기계 를전공하는직원이근무하는부서의이름은? 성남 에근무하는직원들의이름과주소는? 토목부 에소속된공사현장소장의이름은? 36
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) 성남 에사는고객의이름은? 고객테이블 고객번호성명 주소 1 2 김가천 성남 3 주문테이블 주문번호일자 주요품목 금액 고객번호 3 2013-03-03 감자외 1000 2 4) 1000 원어치주문한고객의이름을찾으시오. 주문내역테이블 주문번호품명단가수량금액 5) 감자 를구매한일자를찾으시오. 6) 감자 를구매한고객의이름을검색하시오. 3 감자 20 10 200 3 배추 40 20 800 7) 주문금액의합계와평균금액을계산하시오. 37
4.7 익힘문제 7. SQL 연습과제 - 대구농원 의상품을구매한고객의주소를찾아라 고객테이블 고객번호 성명 주소 총금액 1 2 김가천 성남 3 주문테이블 주문번호 일자 주요품목 금액 고객번호결제일자 3 2013-03-03 감자외 1000 2 주문내역테이블 주문번호 상품명 단가 수량 금액 상품번호 3 감자 30 10 300 4 3 배추 35 20 700 11 상품재고테이블 상품번호 상품명 모델 공급자 단위 재고수량 원가 1 2 3 4 감자 호일15 대구농원 상자 20 30 38