數(shù)據(jù)庫系統(tǒng)概論P(yáng)PT教程第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL(續(xù)2)

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)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言sql 3.1 sql概述概述 3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫 3.3 數(shù)據(jù)定義數(shù)據(jù)定義 3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢 3.5 數(shù)據(jù)更新數(shù)據(jù)更新 3.6 視圖視圖 3.7 小結(jié)小結(jié) an introduction to data

2、base system 3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù) 3.5.2 修改數(shù)據(jù)修改數(shù)據(jù) 3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) an introduction to database system 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù) v兩種插入數(shù)據(jù)方式 1. 插入元組 2. 插入子查詢結(jié)果 可以一次插入多個(gè)元組 an introduction to database system 一、插入元組一、插入元組 v語句格式 insert into (，) values ( ， ) v功能 n將新元組插入指定表中 an introduction to database system 插入元

3、組（續(xù)）插入元組（續(xù)） v into子句 n屬性列的順序可與表定義中的順序不一致 n沒有指定屬性列 n指定部分屬性列 v values子句 n 提供的值必須與into子句匹配 值的個(gè)數(shù) 值的類型 an introduction to database system 學(xué)生學(xué)生-課程課程 數(shù)據(jù)庫數(shù)據(jù)庫 v學(xué)生-課程模式 s-t : 學(xué)生表：student(sno,sname,ssex,sage,sdept) 課程表：course(cno,cname,cpno,ccredit) 學(xué)生選課表：sc(sno,cno,grade) an introduction to database system 插入

4、元組（續(xù)）插入元組（續(xù)） 例1 將一個(gè)新學(xué)生元組（學(xué)號：200215128；姓名：陳 冬；性別：男；所在系：is；年齡：18歲）插入到 student表中。 insert into student (sno，sname，ssex，sdept，sage) values (200215128，陳冬，男，is，18)； an introduction to database system 插入元組（續(xù)）插入元組（續(xù)） 例2 將學(xué)生張成民的信息插入到student表中。 （學(xué)號： 200215126 ；姓名：張成民；性別：男；所在系： cs；年齡：18歲） insert into student va

5、lues (200215126， 張成民， 男，18， cs); an introduction to database system 插入元組（續(xù)）插入元組（續(xù)） 例3 插入一條選課記錄( 200215128，1 )。 insert into sc(sno，cno) values ( 200215128 ， 1 )； rdbms將在新插入記錄的grade列上自動(dòng)地賦空值。 或者： insert into sc values ( 200215128 ， 1 ，null)； an introduction to database system 二、插入子查詢結(jié)果二、插入子查詢結(jié)果 v語句格式 i

6、nsert into ( ， ) 子查詢； v功能 將子查詢結(jié)果插入指定表中 an introduction to database system 插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)） v into子句(與插入元組類似) v 子查詢 nselect子句目標(biāo)列必須與into子句匹配 值的個(gè)數(shù) 值的類型 an introduction to database system 插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)） 例4 對每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果 存入數(shù)據(jù)庫。 第一步：建表 create table dept_age (sdept char(15) /* 系名*/ avg_age

7、smallint)； /*學(xué)生平均年齡*/ an introduction to database system 插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)） 第二步：插入數(shù)據(jù) insert into dept_age(sdept，avg_age) select sdept，avg(sage) from student group by sdept； an introduction to database system 插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)） rdbms在執(zhí)行插入語句時(shí)會(huì)檢查所插元組是 否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 參照完整性 用戶定義的完整性 not null約束

8、unique約束 值域約束 an introduction to database system 3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù) 3.5.2 修改數(shù)據(jù)修改數(shù)據(jù) 3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) an introduction to database system 3.4.2 修改數(shù)據(jù)修改數(shù)據(jù) v語句格式 update set =，= where ； v功能 n修改指定表中滿足where子句條件的元組 an introduction to database system 修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)） nset子句 指定修改方式 要修改的列 修改后取值 nwhere子句

9、指定要修改的元組 缺省表示要修改表中的所有元組 an introduction to database system 修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)） v三種修改方式 1. 修改某一個(gè)元組的值 2. 修改多個(gè)元組的值 3. 帶子查詢的修改語句 an introduction to database system 1. 修改某一個(gè)元組的值修改某一個(gè)元組的值 例5 將學(xué)生200215121的年齡改為22歲 update student set sage=22 where sno= 200215121 ； an introduction to database system 2. 修改多個(gè)元組的值修改多

10、個(gè)元組的值 例6 將所有學(xué)生的年齡增加1歲 update student set sage= sage+1； an introduction to database system 3. 帶子查詢的修改語句帶子查詢的修改語句 例7 將計(jì)算機(jī)科學(xué)系全體學(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ù)）修改數(shù)據(jù)（續(xù)） rdbms在執(zhí)行修改語句時(shí)會(huì)檢查修改操作 是否破壞表上已定

11、義的完整性規(guī)則 n實(shí)體完整性 n主碼不允許修改 n用戶定義的完整性 not null約束 unique約束 值域約束 an introduction to database system 3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù) 3.5.2 修改數(shù)據(jù)修改數(shù)據(jù) 3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) an introduction to database system 3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) v 語句格式 delete from where ； v 功能 n刪除指定表中滿足where子句條件的元組 v where子句 n指定要?jiǎng)h除的元組 n缺省表示要?jiǎng)h除表中的全部元組，表的定義仍

12、在字典中 an introduction to database system 刪除數(shù)據(jù)（續(xù)）刪除數(shù)據(jù)（續(xù)） v三種刪除方式 1. 刪除某一個(gè)元組的值 2. 刪除多個(gè)元組的值 3. 帶子查詢的刪除語句 an introduction to database system 1. 刪除某一個(gè)元組的值刪除某一個(gè)元組的值 例8 刪除學(xué)號為200215128的學(xué)生記錄。 delete from student where sno= 200215128 ； an introduction to database system 2. 刪除多個(gè)元組的值刪除多個(gè)元組的值 例9 刪除所有的學(xué)生選課記錄。 dele

13、te from sc； an introduction to database system 3. 帶子查詢的刪除語句帶子查詢的刪除語句 例10 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 delete from sc where cs= (selete sdept from student where student.sno=sc.sno)； an introduction to database system 第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言sql 3.1 sql概述概述 3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫 3.3 數(shù)據(jù)定義數(shù)據(jù)定義 3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢 3.5 數(shù)據(jù)更

14、新數(shù)據(jù)更新 3.6 視圖視圖 3.7 小結(jié)小結(jié) an introduction to database system 3.6 視視 圖圖 視圖的特點(diǎn) v 虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表 v 只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù) v 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨 之改變 an introduction to database system 3.6 視視 圖圖 基于視圖的操作 v 查詢 v 刪除 v 受限更新 v 定義基于該視圖的新視圖 an introduction to database system 3.6 視視 圖圖 3.6.1 定義視圖定義視圖 3.6.

15、2 查詢視圖查詢視圖 3.6.3 更新視圖更新視圖 3.6.4 視圖的作用視圖的作用 an introduction to database system 3.6.1 定義視圖定義視圖 v建立視圖 v刪除視圖 an introduction to database system 一、建立視圖一、建立視圖 v語句格式 create view ( ，) as with check option； v組成視圖的屬性列名：全部省略或全部指定 v子查詢不允許含有order by子句和distinct 短語 an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)）

16、 vrdbms執(zhí)行create view語句時(shí)只是把視圖定 義存入數(shù)據(jù)字典，并不執(zhí)行其中的select語句。 v在對視圖查詢時(shí)，按視圖的定義從基本表中將數(shù) 據(jù)查出。 an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） 例1 建立信息系學(xué)生的視圖。 create view is_student as select sno，sname，sage from student where sdept= is； an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） 例2建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操

17、作 時(shí)仍需保證該視圖只有信息系的學(xué)生 。 create view is_student as select sno，sname，sage from student where sdept= is with check option； an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） 對is_student視圖的更新操作： v 修改操作：自動(dòng)加上sdept= is的條件 v 刪除操作：自動(dòng)加上sdept= is的條件 v 插入操作：自動(dòng)檢查sdept屬性值是否為is 如果不是，則拒絕該插入操作 如果沒有提供sdept屬性值，則自動(dòng)定義sdept為

18、is an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） v基于多個(gè)基表的視圖 例3 建立信息系選修了1號課程的學(xué)生視圖。 create view is_s1(sno，sname，grade) as select student.sno，sname，grade from student，sc where sdept= is and student.sno=sc.sno and sc.cno= 1； an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） v基于視圖的視圖 例4 建立信息系選修了1號課程且

19、成績在90分以上的學(xué) 生的視圖。 create view is_s2 as select sno，sname，grade from is_s1 where grade=90； an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） v帶表達(dá)式的視圖 例5 定義一個(gè)反映學(xué)生出生年份的視圖。 create view bt_s(sno，sname，sbirth) as select sno，sname，2000-sage from student； an introduction to database system 建立視圖（續(xù)）建立視圖（續(xù)） v分組視

20、圖 例6 將學(xué)生的學(xué)號及他的平均成績定義為一個(gè)視圖 假設(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ù)）建立視圖（續(xù)） v不指定屬性列 例7將student表中所有女生記錄定義為一個(gè)視圖 create view f_student(f_sno，name，sex，age，dept) as select * from student where ssex=女； 缺點(diǎn)： 修改基表stu

21、dent的結(jié)構(gòu)后，student表與f_student視圖的映象關(guān)系被 破壞，導(dǎo)致該視圖不能正確工作。 an introduction to database system 二、刪除視圖二、刪除視圖 v語句的格式： drop view cascade； 該語句從數(shù)據(jù)字典中刪除指定的視圖定義 如果該視圖上還導(dǎo)出了其他視圖，使用cascade級聯(lián) 刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除 刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯 式地使用drop view語句刪除 an introduction to database system 刪除視圖刪除視圖(續(xù)）續(xù)） 例8 刪除視圖bt_s：

22、 drop view bt_s； 刪除視圖is_s1：drop view is_s1； 拒絕執(zhí)行 級聯(lián)刪除： drop view is_s1 cascade; an introduction to database system 3.6 視視 圖圖 3.6.1 定義視圖定義視圖 3.6.2 查詢視圖查詢視圖 3.6.3 更新視圖更新視圖 3.6.4 視圖的作用視圖的作用 an introduction to database system 3.6.2 查詢視圖查詢視圖 v 用戶角度：查詢視圖與查詢基本表相同 v rdbms實(shí)現(xiàn)視圖查詢的方法 視圖消解法（view resolution） 進(jìn)行有

23、效性檢查 轉(zhuǎn)換成等價(jià)的對基本表的查詢 執(zhí)行修正后的查詢 an introduction to database system 查詢視圖（續(xù)）查詢視圖（續(xù)） 例9 在信息系學(xué)生的視圖中找出年齡小于20歲的 學(xué)生。 select sno，sage from is_student where sage20； is_student視圖的定義 (參見視圖定義例1) an introduction to database system 查詢視圖（續(xù)）查詢視圖（續(xù)） 視圖消解轉(zhuǎn)換后的查詢語句為： select sno，sage from student where sdept= is and sage=90

24、； an introduction to database system 查詢轉(zhuǎn)換查詢轉(zhuǎn)換 錯(cuò)誤： select sno，avg(grade) from sc where avg(grade)=90 group by sno； 正確： select sno，avg(grade) from sc group by sno having avg(grade)=90； an introduction to database system 3.6 視視 圖圖 3.6.1 定義視圖定義視圖 3.6.2 查詢視圖查詢視圖 3.6.3 更新視圖更新視圖 3.6.4 視圖的作用視圖的作用 an introdu

25、ction to database system 更新視圖（續(xù)）更新視圖（續(xù)） 例12 將信息系學(xué)生視圖is_student中學(xué)號200215122的學(xué) 生姓名改為“劉辰”。 update is_student set sname= 劉辰 where sno= 200215122 ； 轉(zhuǎn)換后的語句： update student set sname= 劉辰 where sno= 200215122 and sdept= is； an introduction to database system 更新視圖（續(xù)）更新視圖（續(xù)） 例13 向信息系學(xué)生視圖is_s中插入一個(gè)新的學(xué)生記錄： 200215129，趙新，20歲 insert into is_student values(95029，趙新，20)； 轉(zhuǎn)換為對基本表的更新： insert into student(sno，sname，sage，sdept) values(200215129 ，趙新，20，is )； an introduction to database system 更