河北工業(yè)大學(xué)數(shù)據(jù)庫實驗報告

1、數(shù)據(jù)庫原理及應(yīng)用實驗報告題目：SQL語言（視圖、數(shù)據(jù)控制、空值和空集的處理）1.4視圖一 實驗?zāi)康氖煜QL語言支持的有關(guān)視圖的操作，能夠熟練使用SQL語句來創(chuàng)建需要的視圖，對視圖進(jìn)行查詢和取消視圖。二 實驗內(nèi)容(1) 定義常見的視圖形式，包括：· 行列子集視圖。· WITH CHECK OPTION的視圖。· 基于多個基表的視圖。· 帶表達(dá)式的視圖。· 分組視圖。(2) 通過實驗考察WITHCHECKOPTION這一語句在視圖定義后產(chǎn)生的影響，包括對修改操作、刪除操作、插入操作的影響。(3) 討論視圖的數(shù)據(jù)更新情況，對子行列視圖進(jìn)行數(shù)據(jù)更新。

2、(4) 使用DROP語句刪除一個視圖，由該視圖導(dǎo)出的其它視圖定義仍在數(shù)據(jù)詞典中，但已經(jīng)不能使用，必須顯式刪除。同樣原因，刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除。三 實驗題目(1) 定義選課信息和課程名稱的視圖VIEWCcreate view VIEWc asselect CHOICES.cid,ame,sid,tid,scorefrom CHOICES,COURSES where CHOICES.cid=COURSES.cid(2) 定義學(xué)生姓名與選課信息的視圖VIEWScreate view VIEWS asselect STUDENTS.sname,CHOICES.cid,C

3、HOICES.sid,CHOICES.tid,choices.scorefrom CHOICES,STUDENTS where CHOICES.sid=STUDENTS.sid(3) 定義年級低于的學(xué)生視圖（SID，SNAME，GRADE）drop view s1create view S1 asselect STUDENTS.sid ,sname,grade from STUDENTS where grade>1998(4) 查詢學(xué)生為”uxjof”的學(xué)生的選課信息select * from VIEWSwhere sname='uxjof'(5) 查詢選修課程”UML”

4、的學(xué)生的編號和成績select sid,score from VIEWC where cname='UML'(6) 向視圖S1插入記錄（”60000001,Lily,2001”）insert into s1 values('60000001','Lily',2001)(7) 定義包括更新和插入約束的視圖S1，嘗試向視圖插入記錄（“60000001，Lily，1997”），刪除所有年級為1999的學(xué)生記錄，討論更新和插入帶來的影響。drop view s1create view S1 asselect STUDENTS.sid ,sname,gra

5、de from STUDENTS where grade>1998 with check option插入記錄：insert into s1values('60000001','Lily',1997)出現(xiàn)錯誤，原因是目標(biāo)視圖指定了WITH CHECK OPTION，而該操作的一個或多個結(jié)果行又不符合CHECK OPTION 約束。delete from s1 where grade=1999命令成功執(zhí)行。(8) 在視圖VIEWS中將姓名為“uxjof”的學(xué)生的選課成績都加上5分update VIEWS set score=score+5where snam

6、e='uxjof' and score<95(9) 取消以上建立的所有視圖drop view VIEWCdrop view VIEWSdrop view S11.5數(shù)據(jù)控制一 實驗?zāi)康氖煜QL的數(shù)據(jù)控制功能，能夠使用SQL語句來向用戶授予和收回權(quán)限。二 實驗內(nèi)容（1） 使用GRANT語句對用戶授權(quán)，對單個用戶和多個用戶授權(quán)，或使用保留字PUBLIC對所有用戶授權(quán)。對不同的操作對象包括數(shù)據(jù)庫、視圖、基本表等進(jìn)行不同權(quán)限的授權(quán)。（2） 使用WITH GRANT OPTION子句授予用戶傳播該權(quán)限的權(quán)利。（3） 在授權(quán)時發(fā)生循環(huán)授權(quán)，考察DBS能否發(fā)現(xiàn)這個錯誤。如果不能，結(jié)合

7、取消權(quán)限操作，查看DBS對循環(huán)授權(quán)的控制。（4） 使用REVOKE子句收回授權(quán)，取消授權(quán)的級聯(lián)反應(yīng)。三 實驗題目（1） 授予所有用戶對表STUDENTS的查詢權(quán)限。grant select on STUDENTS to public（2） 授予所有用戶對表COURSES的查詢和更新權(quán)限。grant select,update on courses to public（3） 授予USER1對表TEACHERS的查詢，更新工資的權(quán)限，且允許USER1可以傳播這些權(quán)限。grant select,update(salary) on teachers to USER1with grant option（

8、4） 授予USER2對表CHOICES的查詢，更新成績的權(quán)限。grant select,update(score) on choices to USER2（5） 授予USER2對表TEACHERS的除了工資之外的所有信息的查詢。create view TV asselect tid,tname,email from TEACHERSgrant select on TV to USER2（6） 由USER1授予USER2對表TEACHERS的查詢權(quán)限，和傳播此項權(quán)限的權(quán)利。以USER1的身份登錄數(shù)據(jù)庫grant select on teachers to user2 with grant opt

9、ion（7） 由USER2授予USER3對表TEACHERS的查詢權(quán)限，和傳播此項權(quán)限的權(quán)利。再由USER3授予USER2上述權(quán)限，這樣的SQL語句能否得到執(zhí)行？以USER2的身份登錄數(shù)據(jù)庫grant select on teachers to user3 with grant option以USER3的身份登錄數(shù)據(jù)庫grant select on teachers to user2 with grant option命令成功執(zhí)行。（8） 取消USER1對表STUDENTS的查詢權(quán)限，考慮由USER2的身份對表STUDENTS進(jìn)行查詢，操作能否成功，為什么？ revoke select on

10、teachers from user1 操作不成功。消息提示為：若要撤消或拒絕可授予的特權(quán)，請指定CASCADE 選項（9） 取消USER1和USER2的關(guān)于表COOURSES的權(quán)限 revoke select,update on COURSES from USER1,USER21.6空值和空集的處理一實驗?zāi)康恼J(rèn)識NULL值在數(shù)據(jù)庫中的特殊含義，了解空值和空值對于數(shù)據(jù)庫的數(shù)據(jù)查詢操作，特別是空值在條件表達(dá)式中與其他的算術(shù)運算符或邏輯運算符的運算中，空集作為嵌套查詢的子查詢的返回結(jié)果時候的特殊性，能夠熟練使用SQL語句來進(jìn)行與空值、空集相關(guān)的操作。 二實驗內(nèi)容通過實驗驗證在原理解析中分析過的SQ

11、L SERVER對NULL的處理包括：· 在查詢的目標(biāo)表達(dá)式中包含空值的運算· 查詢條件中空值與比較運算符的運算結(jié)果· 使用“IS NULL"或"IS NOT NULL”來判斷元組該列是否為空值· 對存在取空值的列按值進(jìn)行ORDER BY排序.· 使用保留字DISTINCT對空值的處理，區(qū)分?jǐn)?shù)據(jù)庫的多種取值與現(xiàn)實中的多種取值的不同。·使用GROUP BY對存在取空值的屬性值進(jìn)行分組· 結(jié)合分組考察空值對各個集合函數(shù)的影響.特別注憊對COUNT(*)和COUNT(列名)的不同影響· 考察結(jié)果集是空

12、集時，各個集函數(shù)的處理情況。· 驗證嵌套查詢中返回空集的悄況下與各個謂詞的運算結(jié)果· 進(jìn)行與空值有關(guān)的等值連接運算.三實驗習(xí)題1、查詢所有課程記錄的上課學(xué)時數(shù)據(jù)庫中為每星期學(xué)時)，以一學(xué)期十八個星期計算每個課程的總學(xué)時，注意HOUR取NULL值的情況。select cid,hour*18 from COURSES 對NULL值做算術(shù)運算結(jié)果為NULL2、通過查詢選修課程C+的學(xué)生的人數(shù)，其中成績合格的學(xué)生人數(shù)，不合格的學(xué)生人數(shù)，討論NULL值的特殊含義。選修課程C+的學(xué)生的人數(shù)為187人成績合格的人數(shù)： 151select choices.cid,COUNT(*)from

13、CHOICES,COURSES where COURSES.cname='C+'and COURSES.cid=CHOICES.cidand score >=60 group by choices.cid成績不合格的人數(shù)：19select choices.cid,COUNT(*)from CHOICES,COURSES where COURSES.cname='C+'and COURSES.cid=CHOICES.cidand score <60 group by choices.cid成績?yōu)镹ULL的人數(shù)：17select choices.cid,

14、COUNT(*)from CHOICES,COURSES where COURSES.cname='C+'and COURSES.cid=CHOICES.cidand score is null group by choices.cid3、查詢選修課程C+的學(xué)生的編號和成績使用ORDER BY按成統(tǒng)進(jìn)行排序時，取NULL的項是否出現(xiàn)在結(jié)果中?如果有在什么位置?select sid,score from CHOICES,COURSES where COURSES.cname='C+' and COURSES.cid=CHOICES.cidorder by scor

15、e取NULL的項出現(xiàn)在結(jié)果的最前面。NULL值被當(dāng)作最小值。4、在上面的查詢的過程中，如果加上保留字DISTINCT會有什么效果呢?select distinct sid, score from CHOICES,COURSES where COURSES.cname='C+' and COURSES.cid=CHOICES.cidorder by score加上DISTINCT，執(zhí)行結(jié)果沒有區(qū)別5、按年級對所有的學(xué)生進(jìn)行分組，能得到多少個組?與現(xiàn)實的情況有什么不同?select grade from STUDENTS group by grade得到15個組，有一條記錄中g(shù)ra

16、de為NULL。現(xiàn)實中有14個組6、結(jié)合分組，使用集合函數(shù)求每個課程選修的學(xué)生的平均分總的選課記錄數(shù)，最高成績，最低成績，討論考察取空值的項對集合函數(shù)的作用的影響。select cid,AVG(score),COUNT(*),MAX(score),MIN(score) from CHOICESgroup by cid在集合函數(shù)中，除了使用COUNT（*）計算元組時要把取空值的項算進(jìn)去，其他的集合函數(shù)都忽略了取空值的項。取平均值的函數(shù)也是取非空值的項的平均值而不是所有項的平均值7、采用嵌套查詢的方式，利用比較運算符和謂詞ALL的結(jié)合來查詢表STUDENTS中最晚入學(xué)的學(xué)生年級。當(dāng)存在GRAND取空值的項時，考慮可能出現(xiàn)的情況，并解釋原因。select distinct grade from STUDENTS where grade>=all