第五章_數(shù)據(jù)庫(kù)教案SQL語(yǔ)言(1)

在18歲以上水手中,對(duì)于每個(gè)rating級(jí)別中最少有兩個(gè)水手以上的組中最年輕水手的年齡,查找每條紅色船只被租用的次數(shù),在三個(gè)關(guān)系連接之上的一個(gè)分組操作 如果去掉顏色選擇條件,查詢結(jié)果如何? 如果去掉Sailors和與S.sid相關(guān)的條件,情況會(huì)怎么樣?,在18歲以上水手中,對(duì)于每個(gè)rating級(jí)別中最少有兩個(gè)水手以上的組中最年輕水手的年齡(子查詢),Having子句中也可以包含子查詢 Having子句可以為Count(*) 1,SQL數(shù)據(jù)操縱功能(更新),數(shù)據(jù)插入 數(shù)據(jù)刪除 數(shù)據(jù)修改,數(shù)據(jù)插入,單記錄插入 insert into Students values(95020, 陳東, 男, 18,IS) insert into SC(Sno, Cno) values(95020, 1) 多記錄插入(插入子查詢結(jié)果) 假定數(shù)據(jù)庫(kù)中有表: deptage(Sdept char(15), Avgage smallint) insert into deptage ( sdept, avgage ) select Sdept, avg( Sage ) from Students group by Sdept,數(shù)據(jù)刪除,delete from Students where Sno = 95019 delete from SC 帶有子查詢的刪除 delete from SC where CS = ( select Sdept from Students where Students.Sno = SC.Sno ) 王立同學(xué)因病休學(xué),請(qǐng)將王立的選課記錄刪去 (思考題),數(shù)據(jù)修改,update Students set Sage = 22 where Sno= 95001 update Students set Sage = Sage + 1 帶有子查詢的修改 update SC set G = 0 where MA = ( select Sdept from Students where Sno=SC.Sno ),更新操作與數(shù)據(jù)庫(kù)的一致性,更新操作只對(duì)一個(gè)表操作, 但實(shí)際中可能: 例如: 請(qǐng)刪除學(xué)號(hào)為95019的學(xué)生, 隱含將其所有選課記錄刪去 delete from students where Sno=95019; delete from SC where Sno=95019; 如果建表時(shí), 說(shuō)明SC參照Students存在, 則此刪除失敗,更新操作與數(shù)據(jù)庫(kù)的一致性,數(shù)據(jù)庫(kù)提供事務(wù)概念處理這類問(wèn)題 如果建表時(shí), 說(shuō)明SC參照Students存在, 且說(shuō)明on delete cascade, 則只要 delete from students where Sno=95019; 便可將95019的選課記錄全部刪去,視圖,視圖是從一個(gè)或幾個(gè)基本表（視圖）導(dǎo)出的表 視圖是虛表：數(shù)據(jù)庫(kù)中只存放視圖的定義(存放于數(shù)據(jù)字典中)，不存放視圖對(duì)應(yīng)的數(shù)據(jù) 視圖也稱動(dòng)態(tài)窗口： 視圖可以和基本表一樣被查詢，被刪除 視圖的更新是有一定限制的,視圖定義,例子：建立信息系學(xué)生的視圖 create view IS_Students as select * from Students where Sdept=IS create view IS_Students(No, Name, Dept) as select Sno, Sname, Sdept from Students where Sdept=IS 行列子集視圖：從單個(gè)基本表導(dǎo)出，去掉了一些行和列，但保留了碼,?視圖的屬性名,13,14,視圖定義,視圖的屬性或者全部指定，或者全部不指定 換名 某個(gè)目標(biāo)列是集函數(shù)或列表達(dá)式 多表連接時(shí)有幾個(gè)同名列需要區(qū)分 例子：將學(xué)生的學(xué)號(hào)及它的平均成績(jī)定義為一個(gè)視圖 create view avgGrade(Sno, avgG) as select Sno, avg(Grade) from SC group by Sno,虛擬列,視圖定義,例子：建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖 Create view IS_Students(Sno, Sname, Grade) as select Students.Sno, Sname, Grade from Students, SC where Students.Sno=SC.Sno and Cno=1 and Grade90; with check option子句(問(wèn)題) create view IS_Students as select * from Students where Sdept=IS with check option;,刪除視圖,語(yǔ)句格式：DROP VIEW ； 將視圖定義從數(shù)據(jù)字典中刪除，且由該視圖導(dǎo)出的視圖均失效，要從數(shù)據(jù)字典中將失效的視圖都刪除，以免用戶使用錯(cuò)誤 例如： DROP VIEW IS_Student; 思考：若刪除了基本表，由基本表導(dǎo)出的視圖又如何？,查詢視圖,視圖查詢執(zhí)行過(guò)程: 把定義中的子查詢和用戶查詢結(jié)合起來(lái), 轉(zhuǎn)化成等價(jià)的對(duì)基本表的查詢 例子: 在信息系的學(xué)生中找到年齡小于20歲的學(xué)生 select * from IS_Students where Sage20; 轉(zhuǎn)換后的查詢?yōu)?系統(tǒng)完成): select * from Students where Sdept=IS and Sage20; 查詢信息系選修了1號(hào)課程的學(xué)生姓名和成績(jī) select Sname, Grade from IS_Students, SC where IS_Students.Sno=SC.Sno and Cno=1;,查詢視圖,對(duì)視圖的查詢有些是不能直接進(jìn)行轉(zhuǎn)換的 例子: 在avgGrade視圖中, 查詢平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和平均成績(jī) select * from avgGrade where avgG=90; 轉(zhuǎn)換: select Sno, avg(Grade) from SC where avg(Grade90) group by Sno;,?,視圖更新,對(duì)視圖更新最終轉(zhuǎn)換成對(duì)基本表的更新 將信息系學(xué)生學(xué)號(hào)為95002的學(xué)生姓名改為“劉辰” update IS_Students set Sname=劉辰 where Sno=95002; 通過(guò)視圖IS_Students插入一信息系的學(xué)生記錄 insert into IS_Students values(95029, 趙新, 女, 20); with check option子句,視圖更新,刪除信息系學(xué)號(hào)為95029的記錄 delete from IS_Students where Sno=95029; 行列子集視圖是可以更新的, DB2例子p127 視圖的作用: 簡(jiǎn)化用戶的操作 視圖使用戶能以多種角度看待同一數(shù)據(jù) 視圖可以對(duì)機(jī)密數(shù)據(jù)提供一定的安全保護(hù),視圖的作用,視圖對(duì)數(shù)據(jù)庫(kù)重構(gòu)提供了一定程度的邏輯獨(dú)立性 例如: Students(Sno, Sname, Ssex, Sage, Sdept) 分為: S1(Sno, Sname, Sage) S2(Sno, Ssex, Sdept) 但在Students上已有開發(fā)了多個(gè)應(yīng)用, 為了達(dá)到不改變應(yīng)用程序的目的, 可以建視圖Students: create view Students(Sno, Sname, Ssex, Sage, Sdept) as select S1.Sno, S1.Sname, S2.Ssex, S1.Sage, S2.Sdept from S1, S2 where S1.Sno=S2.Sno;,視圖更新,由兩個(gè)以上基本表導(dǎo)出的視圖不允許更新； 來(lái)自于字段表達(dá)式或常數(shù)的視圖字段不允許進(jìn)行insert和update操作，但允許delete操作； 不允許更新集函數(shù)視圖字段； 不允許更新含有g(shù)roup by或di