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

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