軟件技術(shù)基礎(chǔ)數(shù)據(jù)庫技術(shù).ppt

《軟件技術(shù)基礎(chǔ)數(shù)據(jù)庫技術(shù).ppt》由會員分享，可在線閱讀，更多相關(guān)《軟件技術(shù)基礎(chǔ)數(shù)據(jù)庫技術(shù).ppt（30頁珍藏版）》請在裝配圖網(wǎng)上搜索。

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