關(guān)系數(shù)據(jù)庫語言SQL（續(xù)2）,概述 SQL的數(shù)據(jù)定義 SQL的數(shù)據(jù)操縱 SQL的視圖定義 SQL的數(shù)據(jù)控制,3.5 SQL的數(shù)據(jù)控制(1 of 8),SQL的數(shù)據(jù)控制也叫數(shù)據(jù)保護(hù)。包括數(shù)據(jù)的安全性、完整性、并發(fā)控制和恢復(fù)功能。 數(shù)據(jù)庫的安全性是指保護(hù)數(shù)據(jù)庫，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。 SQL語言為定義和回收存取控制權(quán)利提供了手段。 具體辦法：進(jìn)行存取控制，即規(guī)定不同用戶對(duì)于不同數(shù)據(jù)對(duì)象所允許執(zhí)行的操作，并控制各用戶只能存取他有權(quán)存取的數(shù)據(jù)。,授權(quán)機(jī)制(2 of 8),授權(quán)機(jī)制:大的DBMS中有一個(gè)超級(jí)用戶DBA，其他用戶能否存在、對(duì)某類數(shù)據(jù)具有何種操作權(quán)力是由DBA決定的，系統(tǒng)提供授權(quán)機(jī)制。 執(zhí)行過程： （1）用數(shù)據(jù)控制語言把授權(quán)決定告知系統(tǒng)； （2）系統(tǒng)把授權(quán)的結(jié)果存入數(shù)據(jù)字典； （3）當(dāng)用戶提出 操作請(qǐng)求時(shí)，系統(tǒng)根據(jù)授權(quán)情況進(jìn)行檢查， 以決定是否執(zhí)行。,例如：SYBASE系統(tǒng)中，有 四類用戶: 三級(jí)管理：,系統(tǒng)管理員SA,數(shù)據(jù)庫對(duì)象屬主,一般用戶,授權(quán)某用戶建庫,可再授權(quán)某用戶建立數(shù)據(jù)庫對(duì)象,可再授權(quán)某用戶對(duì)數(shù)據(jù)庫對(duì)象的訪問,（根據(jù)權(quán)限對(duì)數(shù)據(jù)庫對(duì)象進(jìn)行操作）,授權(quán)機(jī)制(3 of 8),數(shù)據(jù)庫屬主,授權(quán)機(jī)制(4 of 8),又如：SQL Server通過角色將用戶分為不同的類： 固定服務(wù)器角色 固定數(shù)據(jù)庫角色,權(quán)力的授予與收回(5 of 8),授予：,GRANT 權(quán)力 ，權(quán)力 ON 對(duì)象類型 對(duì)象名 TO 用戶名 ，用戶名 WITH GRANT OPTION ；,若干權(quán)力,操作對(duì)象,獲得權(quán)力的用戶,有此項(xiàng)，被授權(quán)用戶可再授權(quán)給其他用戶,收回：,REVOKE 權(quán)力 ，權(quán)力 ON 對(duì)象類型 對(duì)象名 FROM 用戶名 ，用戶名 ；,誰定義？DBA和表的建立者（即表的屬主）,SQL中的用戶權(quán)限(6 of 8),Example (7 of 8),例1：把查詢學(xué)生表的權(quán)限授予給用戶U1,例2：把對(duì)Student和Course表的全部權(quán)限授予給用戶U2和U3.,GRANT ALL PRIVILEGES ON TABLE Student, Course TO U2,U3,例3：把查詢Student表和修改學(xué)生學(xué)號(hào)的權(quán)限授予給用戶U4.,GRANT UPDATE (Sno), SELECT TABLE ON Student TO U4,例4：把對(duì)SC表的INSERT權(quán)限授予給用戶U5，并允許他再將此權(quán)限授予給其他用戶。,GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION,例5：把U4對(duì)Student表修改學(xué)生學(xué)號(hào)的權(quán)限收回.,REVOKE UPDATE (Sno) ON TABLE Student FROM U4,GRANT SELECT ON TABLE Student TO U1,SQL的安全性控制(8 of 8),數(shù)據(jù)庫的屬主DBA、表的屬主以及其他數(shù)據(jù)庫對(duì)象的屬主在他創(chuàng)建的對(duì)象上具有一切可能的權(quán)力，其他用戶得不到主人的授權(quán)就不能在該對(duì)象上操作； SQL的授權(quán)機(jī)制十分靈活，各種系統(tǒng)又作了適當(dāng)?shù)难a(bǔ)充， 使用十分方便； SQL的安全性控制除了上述授權(quán)機(jī)制外，還可設(shè)置口令進(jìn)一步保密。,SQL應(yīng)用舉例,應(yīng)用：數(shù)據(jù)庫中有三個(gè)關(guān)系（基本表）： S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER) 試用SQL語句表達(dá)下列查詢和更新語句。,思考中,1、檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與成績(jī),SELECT S#, GRADE FROM SC WHERE C#=C2,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名,方法一：聯(lián)接查詢 SELECT S#, SNAME FROM S, SC WHERE S.S#=SC.S# AND C#=C2,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名,方法二：嵌套查詢 SELECT S#, SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C#=C2),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名,方法三：嵌套查詢 SELECT S#, SNAME FROM S WHERE C2 IN (SELECT C# FROM SC WHERE S#=S.S#),“相關(guān)子查詢”,子查詢中查詢條件依賴于外層查詢中的某個(gè)值,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),2、檢索學(xué)習(xí)課程為C2的學(xué)生學(xué)號(hào)與姓名,方法四：存在量詞 SELECT S#, SNAME FROM S WHERE EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND C#=C2),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER), (內(nèi)層查詢結(jié)果非空),3、檢索不學(xué)課程C2的學(xué)生學(xué)號(hào)與姓名,方法一：存在量詞 SELECT S#, SNAME FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND C#=C2),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),3、檢索不學(xué)課程C2的學(xué)生學(xué)號(hào)與姓名,方法二：嵌套查詢 SELECT S#, SNAME FROM S WHERE S# NOT IN (SELECT S# FROM SC WHERE C#=C2),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),4、檢索學(xué)習(xí)課程名為Maths的學(xué)生學(xué)號(hào)與姓名,方法一：多層嵌套 SELECT S#, SNAME FROM S WHERE S# IN (SELECT S# FROM SC WHERE C# IN (SELECT C# FROM C WHERE CNAME=Mathes),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),4、檢索學(xué)習(xí)課程名為Maths的學(xué)生學(xué)號(hào)與姓名,方法二：聯(lián)接查詢 SELECT S.S#, SNAME FROM S, SC, C WHERE S.S#=SC.S# AND SC.C#=C.C# AND CNAME=Maths,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),5、檢索學(xué)習(xí)課程號(hào)為C2或C4的學(xué)生學(xué)號(hào),SELECT S# FROM SC WHERE C#=C2 OR C#=C4,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),6、檢索至少學(xué)習(xí)課程號(hào)為C2和C4的學(xué)生學(xué)號(hào),SELECT X.S# FROM SC AS X, SC AS Y WHERE X.S#=Y.S# AND X.C#=C2 AND Y.C#=C4,同一關(guān)系SC在一層中出現(xiàn)兩次，分別引入別名X和Y,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),7、檢索學(xué)習(xí)全部課程的學(xué)生姓名。,SELECT SNAME FROM S WHERE NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),8、檢索所學(xué)課程包含學(xué)生S3所學(xué)課程的學(xué)生學(xué)號(hào),SELECT DISTINCT S# FROM SC AS X WHERE NOT EXISTS (SELECT * FROM SC AS Y WHERE Y.S#=S3 AND NOT EXISTS (SELECT * FROM SC AS Z WHERE Z.S#=X.S# AND Z.C#=Y.C#),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),9、統(tǒng)計(jì)每一年齡選修課程的學(xué)生人數(shù),SELECT AGE, COUNT(DISTINCT S.S#) FROM S, SC WHERE S.S#=SC.S# GROUP BY AGE,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),10、檢索平均成績(jī)最高的學(xué)生學(xué)號(hào),SELECT S# FROM SC GROUP BY S# HAVING AVG（GRADE） =ALL （ SELECT AVG（GRADE） FROM SC GROUP BY S#）,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),SQL中，不允許對(duì)集函數(shù)進(jìn)行復(fù)合運(yùn)算因此 不能寫成”SELECT MAX(AVG(GRADE)”形式,11、檢索至少有一門成績(jī)超過學(xué)生S4 一門成績(jī)的學(xué)生 學(xué)號(hào)。,SELECT DISTINCT S# FROM SC WHERE GRADE ANY （ SELECT GRADE FROM SC WHERE S#=S4,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),12、求男同學(xué)每一年齡組（超過50人）有多少？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同時(shí)按年齡降序排列。,SELECT AGE, COUNT(S#) FROM S WHERE SEX=M GROUP BY AGE HAVING COUNT(*)50 ORDER BY 2, AGE DESC,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),13、在關(guān)系SC中，把平均成績(jī)大于80分的男學(xué)生的學(xué)號(hào)和平均成績(jī)插入到另一個(gè)關(guān)系S_GRADE(S#, AVG_GRADE)中。,INSERT INTO S_GRADE(S#, AVG_GRADE) SELECT S#, AVG(GRADE) FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX=M) GROUP BY S# HAVING AVG(GRADE)80,SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),14、把C4課程中小于該課程平均成績(jī)的成績(jī)從關(guān)系SC中刪除。,DELETE FORM SC WHERE C#=C4 AND GRADE(SELECT AVG(GRADE) FROM SC WHERE C#=C4),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),15、把C4課程中低于該門課程平均成績(jī)的成績(jī)提高5。,UPDATE SC SET GRADE=GRADE*1.05 WHERE C#=C4 AND GRADE(SELECT AVG(GRADE) FROM SC WHERE C#=C4),SQL應(yīng)用,S(S#, SNAME, AGE, SEX) SC(S#, C#, GRADE) C(C#, CNAME, TEACHER),