1、an introduction to database system數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論an introduction to database system第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言sql sql (續(xù)續(xù)2）an introduction to database system第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言sql3.1 sql概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式sql3.8 小結(jié) an introduction to database system3.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.

2、4.3 刪除數(shù)據(jù) an introduction to database system3.4.1 插入數(shù)據(jù)l三種插入數(shù)據(jù)方式 插入單個元組 插入子查詢結(jié)果 插入新表an introduction to database system1. 插入單個元組l 語句格式insertinto (，)values ( ， )l 功能 將新元組插入指定表中。an introduction to database system插入單個元組（續(xù)）例1 將一個新學(xué)生記錄（學(xué)號：200215128；姓名：陳冬；性別：男；所在系：is；年齡：18歲）插入到student表中。 insert into student

3、values (200215128，陳冬，男，is，18)；an introduction to database system插入單個元組（續(xù)）例2 插入一條選課記錄( 200215128，1 )。 insert into sc(sno，cno) values ( 200215128 ， 1 )； 新插入的記錄在grade列上取空值an introduction to database system插入單個元組（續(xù)）l into子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入

4、的元組在其余屬性列上取空值l values子句 提供的值必須與into子句匹配值的個數(shù)值的類型an introduction to database system2. 插入子查詢結(jié)果l語句格式 insert into ( ， ) 子查詢；l功能 將子查詢結(jié)果插入指定表中an introduction to database system插入子查詢結(jié)果（續(xù)）例3 對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。第一步：建表 create table deptage (sdept char(15) /* 系名*/ avgage smallint)； /*學(xué)生平均年齡*/ an introduct

5、ion to database system插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù) insert into deptage(sdept，avgage) select sdept，avg(sage) from student group by sdept；an introduction to database system插入子查詢結(jié)果（續(xù)）into子句(與插入單條元組類似)l指定要插入數(shù)據(jù)的表名及屬性列l(wèi)屬性列的順序可與表定義中的順序不一致l沒有指定屬性列：表示要插入的是一條完整的元組l指定部分屬性列：插入的元組在其余屬性列上取空值 子查詢lselect子句目標(biāo)列必須與into子句匹配 值的個數(shù)

6、 值的類型an introduction to database system插入子查詢結(jié)果（續(xù)）dbms在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則實體完整性參照完整性用戶定義的完整性l對于有not null約束的屬性列是否提供了非空值l 對于有unique約束的屬性列是否提供了非重復(fù)值l 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)an introduction to database system3.向新表中插入數(shù)據(jù)使用使用selectselect語句：語句：l格式格式select into where an introduction to database sy

7、stem3.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) an introduction to database system3.4.2 修改數(shù)據(jù)l語句格式 update set =，= where ；l功能修改指定表中滿足where子句條件的元組an introduction to database system修改數(shù)據(jù)（續(xù)）l三種修改方式修改某一個元組的值修改多個元組的值帶子查詢的修改語句an introduction to database system1. 修改某一個元組的值例4 將學(xué)生200215121的年齡改為22歲。 update studen

8、t set sage=22 where sno= 200215121 ； an introduction to database system2. 修改多個元組的值例5 將所有學(xué)生的年齡增加1歲。 update student set sage= sage+1；an introduction to database system修改多個元組的值(續(xù))例6 將信息系所有學(xué)生的年齡增加1歲。 update student set sage= sage+1 where sdept= is ；an introduction to database system3. 帶子查詢的修改語句例7 將計算機科學(xué)

9、系全體學(xué)生的成績置零。 update sc set grade=0 where cs= (selete sdept from student where student.sno = sc.sno)；an introduction to database system修改數(shù)據(jù)（續(xù)）set子句 指定修改方式 要修改的列 修改后取值where子句指定要修改的元組缺省表示要修改表中的所有元組an introduction to database system修改數(shù)據(jù)（續(xù)）dbms在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則實體完整性主碼不允許修改用戶定義的完整性l not null約束

10、l unique約束l 值域約束an introduction to database system3.4 數(shù) 據(jù) 更 新 3.4.1 插入數(shù)據(jù)3.4.2 修改數(shù)據(jù)3.4.3 刪除數(shù)據(jù) an introduction to database system3.4.3 刪除數(shù)據(jù) delete from where ； 功能w 刪除指定表中滿足where子句條件的元組 where子句w 指定要刪除的元組w 缺省表示要修改表中的所有元組an introduction to database system刪除數(shù)據(jù)（續(xù)）l三種刪除方式刪除某一個元組的值刪除多個元組的值帶子查詢的刪除語句an introdu

11、ction to database system1. 刪除某一個元組的值例8 刪除學(xué)號為200215128的學(xué)生記錄。 delete from student where sno= 2002151218；an introduction to database system2. 刪除多個元組的值例9 刪除2號課程的所有選課記錄。 delete from sc； where cno=2；例10 刪除所有的學(xué)生選課記錄。 delete from sc；an introduction to database system3. 帶子查詢的刪除語句例11 刪除計算機科學(xué)系所有學(xué)生的選課記錄。 delete

12、 from sc where cs= (selete sdept from student where student.sno=sc.sno)；an introduction to database system刪除數(shù)據(jù)(續(xù))dbms在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則 參照完整性 不允許刪除 級聯(lián)刪除an introduction to database system更新數(shù)據(jù)與數(shù)據(jù)一致性dbms在執(zhí)行插入、刪除、更新語句時必須保證數(shù)據(jù)庫一致性l必須有事務(wù)的概念和原子性l完整性檢查和保證an introduction to database system第三章 關(guān)系數(shù)據(jù)

13、庫標(biāo)準(zhǔn)語言sql3.1 sql概述3.2 數(shù)據(jù)定義3.3 查詢3.4 數(shù)據(jù)更新3.5 視圖3.6 數(shù)據(jù)控制3.7 嵌入式sql3.8 小結(jié) an introduction to database system3.5 視 圖視圖的特點l虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表l只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余l(xiāng)基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變an introduction to database system3.5 視 圖基于視圖的操作l 查詢l 刪除l 受限更新l 定義基于該視圖的新視圖an introduction to database system3.5 視 圖

14、3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用an introduction to database system1. 建立視圖l語句格式 create view ( ，) as with check option；an introduction to database system 建立視圖（續(xù)）dbms執(zhí)行create view語句時只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的select語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。an introduction to database system組成視圖的屬性列名全部省略或全部指定省略:

15、由子查詢中select目標(biāo)列中的諸字段組成明確指定視圖的所有列名:(1) 某個目標(biāo)列是集函數(shù)或列表達式(2) 多表連接時選出了幾個同名列作為視圖的字段(3) 需要在視圖中為某個列啟用新的更合適的名字an introduction to database system行列子集視圖 例1 建立信息系學(xué)生的視圖。 create view is_student as select sno，sname，sage from student where sdept= is；從單個基本表導(dǎo)出,只是去掉了基本表的某些行和某些列,保留了碼an introduction to database system 建立視

16、圖（續(xù)）lwith check option透過視圖進行增刪改操作時，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達式）an introduction to database system with check option的視圖例2 建立信息系學(xué)生的視圖，并要求透過該視圖進行的更新操作只涉及信息系學(xué)生。 create view is_student as select sno，sname，sage from student where sdept= is with check option；an introduction to database system對is_student視圖的更

17、新操作l 修改操作：dbms自動加上sdept= is的條件l 刪除操作：dbms自動加上sdept= is的條件l 插入操作：dbms自動檢查sdept屬性值是否為is 如果不是，則拒絕該插入操作如果沒有提供sdept屬性值，則自動定義sdept為isan introduction to database system基于多個基表的視圖例4 建立信息系選修了1號課程的學(xué)生視圖。 create view is_s1(sno，sname，grade) as select student.sno，sname，grade from student，sc where sdept= is and stu

18、dent.sno=sc.sno and sc.cno= 1；an introduction to database system基于視圖的視圖例5 建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。 create view is_s2 as select sno，sname，grade from is_s1 where grade=90；an introduction to database system帶表達式的視圖例6 定義一個反映學(xué)生出生年份的視圖。 create view bt_s(sno，sname，sbirth) as select sno，sname，2000-sage f

19、rom student設(shè)置一些派生屬性列, 也稱為虛擬列sbirth，帶表達式的視圖必須明確定義組成視圖的各個屬性列名an introduction to database system 建立分組視圖例7 將學(xué)生的學(xué)號及他的平均成績定義為一個視圖 假設(shè)sc表中“成績”列g(shù)rade為數(shù)字型 creat view s_g(sno，gavg) as select sno，avg(grade) from sc group by sno；an introduction to database system 建立視圖（續(xù)）l一類不易擴充的視圖以 select * 方式創(chuàng)建的視圖可擴充性差，應(yīng)盡可能避免an

20、 introduction to database system 建立視圖（續(xù)）例8將student表中所有女生記錄定義為一個視圖 create view f_student1(stdnum，name，sex，age，dept) as select * from student where ssex=女；缺點：修改基表student的結(jié)構(gòu)后，student表與f_student1視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。an introduction to database system 建立視圖（續(xù)） create view f_student2 (stdnum，name，sex，age

21、，dept) as select sno，sname，ssex，sage，sdept from student where ssex=女；為基表student增加屬性列不會破壞student表與f_student2視圖的映象關(guān)系。an introduction to database system（總結(jié)）常見的視圖形式行列子集視圖with check option的視圖基于多個基表的視圖基于視圖的視圖帶表達式的視圖分組視圖an introduction to database system2. 刪除視圖ldrop view ；該語句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)

22、據(jù)字典中，但已不能使用，必須顯式刪除刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除an introduction to database system刪除視圖(續(xù)） 例9 刪除視圖is_s1 drop view is_s1； an introduction to database system3.5 視 圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用an introduction to database system3.5.2 查詢視圖l 從用戶角度：查詢視圖與查詢基本表相同l dbms實現(xiàn)視圖查詢的兩種方法實體化視圖（view materializ

23、ation）l有效性檢查：檢查所查詢的視圖是否存在l執(zhí)行視圖定義，將視圖臨時實體化，生成臨時表l查詢視圖轉(zhuǎn)換為查詢臨時表l查詢完畢刪除被實體化的視圖(臨時表)newan introduction to database system查詢視圖（續(xù)）視圖消解法（view resolution）l進行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義l把視圖定義中的子查詢與用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢l執(zhí)行修正后的查詢an introduction to database system查詢視圖（續(xù)）例1 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

24、select sno，sage from is_student where sage20；is_student視圖的定義 (視圖定義例1)： create view is_student as select sno，sname，sage from student where sdept= is；an introduction to database system查詢視圖（續(xù)）視圖實體化法（略）視圖消解法轉(zhuǎn)換后的查詢語句為：select sno，sage from studentwhere sdept= is and sage=90where gavg=90；s_g視圖定義： create vi

25、ew s_g (sno，gavg) as select sno，avg(grade)from scgroup by sno；an introduction to database system查詢轉(zhuǎn)換錯誤：select sno，avg(grade)from scwhere avg(grade)=90group by sno；正確：select sno，avg(grade)from scgroup by snohaving avg(grade)=90；an introduction to database system3.5 視 圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.

26、5.4 視圖的作用an introduction to database system3.5.3 更新視圖（更新內(nèi)容）l 用戶角度：更新視圖與更新基本表相同對視圖的更新的最終結(jié)果是對基本表的更新！對視圖的更新的最終結(jié)果是對基本表的更新！l dbms實現(xiàn)視圖更新的方法視圖實體化法（view materialization）視圖消解法（view resolution）l 指定with check option子句后， dbms在更新視圖時會進行檢查，防止用戶通過視圖對不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進行更新an introduction to database system更新視圖（續(xù)）例1 將信息系

27、學(xué)生視圖is_student中學(xué)號95002 的學(xué)生姓名改為“劉辰”。update is_studentset sname= 劉辰where sno= 2002151212；轉(zhuǎn)換后的語句：update studentset sname= 劉辰where sno= 95002 and sdept= is；an introduction to database system更新視圖（續(xù)）例2 向信息系學(xué)生視圖is_s中插入一個新的學(xué)生記錄：200215129，趙新，20歲insertinto is_studentvalues(200215129，趙新，20)；轉(zhuǎn)換為對基本表的更新：insertin

28、to student(sno，sname，sage，sdept)values(95029，趙新，20，is )；an introduction to database system更新視圖（續(xù)）例3 刪除視圖cs_s中學(xué)號為200215129的記錄deletefrom is_studentwhere sno= 200215129 ；轉(zhuǎn)換為對基本表的更新：deletefrom studentwhere sno= 200215129 and sdept= is；an introduction to database system更新視圖的限制l 一些視圖是不可更新的，因為對這些視圖的更新不能唯一地

29、有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新(對兩類方法均如此)例：視圖s_g為不可更新視圖。 create view s_g (sno，gavg) as select sno，avg(grade)from scgroup by sno；an introduction to database system更新視圖（續(xù)）對于如下更新語句：update s_gset gavg=90where sno= 2002151291；無論實體化法還是消解法都無法將其轉(zhuǎn)換成對基本表sc的更新an introduction to database system實際系統(tǒng)對視圖更新的限制l 允許對行列子集視圖進行更新l 對其他

30、類型視圖的更新不同系統(tǒng)有不同限制db2對視圖更新的限制：(1) 若視圖是由兩個以上基本表導(dǎo)出的，則此視圖不允許更新。(2) 若視圖的字段來自字段表達式或常數(shù)，則不允許對此視圖執(zhí)行insert和update操作，但允許執(zhí)行delete操作。an introduction to database system更新視圖（續(xù)）(3) 若視圖的字段來自集函數(shù)，則此視圖不允許更新。(4) 若視圖定義中含有g(shù)roup by子句，則此視圖不允許更新。(5) 若視圖定義中含有distinct短語，則此視圖不允許更新。(6) 若視圖定義中有嵌套查詢，并且內(nèi)層查詢的from子句中涉及的表也是導(dǎo)出該視圖的基本表，則此

31、視圖不允許更新。(7) 一個不允許更新的視圖上定義的視圖也不允許更新。an introduction to database system更新視圖（續(xù)）例：視圖good_sc(修課成績在平均成績之上的元組) create view good_sc as select sno，cno，grade from sc where grade (select avg(grade) from sc)；這個視圖就不能更新。（p128）l更新視圖更新視圖更新視圖結(jié)構(gòu)：更新視圖結(jié)構(gòu)：alter viewan introduction to database system3.5 視 圖3.5.1 定義視圖3.5.2 查詢視圖3.5.3 更新視圖3.5.4 視圖的作用an introduction to database system1. 視圖能夠簡化