數(shù)據(jù)庫第一章實(shí)驗(yàn)報告

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫原理及應(yīng)用實(shí)驗(yàn)報告題目：實(shí)驗(yàn)四、五、六 SQL語言（視圖、數(shù)據(jù)控制、空值處理） 學(xué)號： 姓名： 么紅帥 專業(yè)班級： 軟件131 指導(dǎo)教師： 張滿囤 完成日期： 2015年11月9日 實(shí)驗(yàn)1.1 數(shù)據(jù)定義一、實(shí)驗(yàn)?zāi)康氖煜QL的數(shù)據(jù)定義語言，能夠熟練地使用SQL語句來創(chuàng)建和更改基本表，創(chuàng)建和取消索引。二、實(shí)驗(yàn)內(nèi)容(1)創(chuàng)建數(shù)據(jù)庫表CUSTOMERS(CID,CNAME,CITY,DISCNT)、數(shù)據(jù)庫表AGENTS(AID,ANAME,CITY,PERCENT)、數(shù)據(jù)庫表PRODUCTS(PID,PNAME)。其中CID、AID、PID分表是各表的主鍵，具有唯一性

2、約束。(2)創(chuàng)建數(shù)據(jù)庫表ORDERS(ORDNA,MONTH,CID,AID,PID,QTY,DOLLARS)。其中ORDNA分表是主鍵，具有唯一性約束。CID、AID、PID分別是外鍵引用自表CUSTOMERS、表AGENTS,表PRODUCTS。(3)增加數(shù)據(jù)庫表PRODUCTS三個屬性列：CITY、QUANTITY、PRICE。(4)為以上四個表建立各自的按主鍵增序排列的序列。(5)取消（4）建立的四個索引。三、實(shí)驗(yàn)結(jié)果專心-專注-專業(yè)(1)CREATE TABLE CUSTOMERS(CID CHAR(8) UNIQUE,CNAME CHAR(20),CITY CHAR(20),DIS

3、CNT INT,PRIMARY KEY(CID)CREATE TABLE AGENTS(AID CHAR(8) UNIQUE,ANAME CHAR(20),CITY CHAR(20),PERCENT FLOAT,PRIMARY KEY(AID)CREATE TABLE PRODUCTS(PID CHAR(8) UNIQUE,PANME CHAR(20),PRIMARY KEY(PID)(2)CREATE TABLE ORDERS(ORDNA CHAR(8) UNIQUE,MONTH INT,CID CHAR(8) UNIQUE,AID CHAR(8) UNIQUE,PID CHAR(8) UN

4、IQUE,QTY INT,DOLLARS FLOAT,PRIMARY KEY(ORDNA),FOREIGN KEY(CID) REFERENCES CUSTOMERS,FOREIGN KEY(AID) REFERENCES AGENTS,FOREIGN KEY(PID) REFERENCES PRODUCTS)(3)ALTER TABLE PRODUCTS ADD CITY CHAR(20)ALTER TABLE PRODUCTS ADD QUANTITY INTALTER TABLE PRODUCTS ADD PRICE FLOAT(4)CREATE INDEX XSNO ON CUSTOM

5、ERS(CID)CREATE INDEX XSNO ON AGENTS(AID)CREATE INDEX XSNO ON PRODUCTS(PID)CREATE INDEX XSNO ON ORDERS(ORDNA)(5)DROP INDEX CUSTOMERS.XSNODROP INDEX AGENTS.XSNODROP INDEX PRODUCTS.XSNODROP INDEX ORDERS.XSNO實(shí)驗(yàn)1.2 數(shù)據(jù)查詢一、 實(shí)驗(yàn)?zāi)康氖煜QL語句的數(shù)據(jù)查詢語言，能夠使用SQL語句對數(shù)據(jù)庫進(jìn)行單表查詢、連接查詢、嵌套查詢、集合查詢和統(tǒng)計查詢。二、 實(shí)驗(yàn)內(nèi)容(1)查詢?nèi)空n程的詳細(xì)記錄。(2

6、)查詢所有有選課的學(xué)生的編號。(3)查詢課時<80（小時）的課程的編號。(4)請找出總分超過400分的編號(5)查詢課程的總數(shù)。(6)查詢所有課程和選修該課程的學(xué)生總數(shù)。(7)查詢選修成績合格的課程超過兩門的學(xué)生的編號。(8)統(tǒng)計各個學(xué)生的選修課程數(shù)目和平均成績。(9)查詢選修java的所有學(xué)生的編號及姓名。(10)分別使用等值連接和謂詞IN兩種方式查詢姓名為sssht的學(xué)生所選的課程的編號和成績。(11) 查詢課時比c+多的課程的名稱。(12)查詢選修c+課程的成績比姓名為znkoo學(xué)生高的所有學(xué)生的編號和姓名。(13)找出和學(xué)生或?qū)W生的年級一樣的學(xué)生的姓名。(14)查詢沒有選修jav

7、a的學(xué)生的名稱。(15) 查詢課時最少的課程的詳細(xì)信息。(16)查詢工資最高的教師的編號和開設(shè)的課號。(17)找出選修課程ERP成績最高的學(xué)生編號。(18)查詢沒有學(xué)生選的課程的名稱。(19)找出講授課程UML的教師講授的所有課程名稱。(20查詢選修了編號的教師開設(shè)所有課程的學(xué)生編號。(21)查詢選修課程database的學(xué)生集合與選修UML的學(xué)生集合的并集。(22)實(shí)現(xiàn)集合交運(yùn)算，查詢級選修課程database又選修UML的學(xué)生的編號。(23)實(shí)現(xiàn)集合減運(yùn)算，查詢級選修課程database而又沒有選修UML的學(xué)生的編號。三、實(shí)驗(yàn)結(jié)果(1)SELECT *FROM COURSES;(2)SEL

8、ECT sidFROM CHOICES;(3)SELECT cidFROM COURSESWHERE hour<88;(4)SELECT sidFROM CHOICESGROUP BY sid HAVING SUM(score)>400;(5)SELECT COUNT(cid)FROM COURSES;(6)SELECT cid,COUNT(sid)FROM CHOICESGROUP BY cid;(7)SELECT sidFROM CHOICESWHERE score>60GROUP BY sidHAVING COUNT(cid)>2;(8)SELECT sid,CO

9、UNT(cid),AVG(score)FROM CHOICESGROUP BY sid;(9)SELECT sid,snameFROM STUDENTSWHERE sid IN(SELECT sid FROM CHOICES WHERE cid=(SELECT cidFROM COURSESWHERE cname='Java');(10)等值連接：SELECT cid,scoreFROM STUDENTS,CHOICESWHERE STUDENTS.sid=CHOICES.sid AND sname='sssht'謂詞IN：SELECT cid,scoreFRO

10、M CHOICESWHERE sid IN(SELECT sid FROM STUDENTS WHERE sname='sssht');(11)SELECT cnameFROM COURSESWHERE hour>(SELECT hour FROM COURSES WHERE cname='C+');(12)SELECT sid,snameFROM STUDENTSWHERE sid IN(SELECT C1.sid FROM CHOICES C1,CHOICES C2 WHERE C1.score>C2.score AND C1.cid=C2.ci

11、d AND C1.cid=(SELECT cid FROM COURSES WHERE cname='c+') AND C2.sid=(SELECT sid FROM STUDENTS WHERE sname='znkoo');(13)SELECT snameFROM STUDENTSWHERE grade IN(SELECT grade FROM STUDENTS WHERE sid='' OR sid='');(14)SELECT snameFROM STUDENTSWHERE sid NOT IN(SELECT sid FR

12、OM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname='Java');(15)SELECT *FROM COURSESWHERE hour=(SELECT MIN(hour) FROM COURSES);(16)SELECT tid,cidFROM CHOICESWHERE tid IN(SELECT tid FROM TEACHERS WHERE salary=(SELECT MAX(salary) FROM TEACHERS);(17)SELECT sidFROM CHOICESWHERE score=(SELE

13、CT MAX(score) FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname='ERP') AND cid=(SELECT cid FROM COURSES WHERE cname='ERP');(18)SELECT cnameFROM COURSESWHERE cid NOT IN(SELECT cid FROM CHOICES);(19)SELECT cnameFROM COURSESWHERE cid IN(SELECT cid FROM CHOICES WHERE tid IN(SEL

14、ECT tid FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname='UML'); (20)SELECT sidFROM CHOICESWHERE cid IN(SELECT cid FROM CHOICES WHERE tid='')GROUP BY sidHAVING COUNT(*)=(SELECT COUNT(DISTINCT cid) FROM CHOICES WHERE tid='');(21)SELECT sidFROM CHOICESWHERE cid=(SELE

15、CT cid FROM COURSES WHERE cname='Database')UNIONSELECT sidFROM CHOICESWHERE cid=(SELECT cid FROM COURSES WHERE cname='UML');(22)SELECT C1.sidFROM CHOICES C1,CHOICES C2WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname='Database')AND C2.cid=(SELECT cid FROM COURSES WHERE cname=

16、'UML')AND C1.sid=C2.sid; (23)SELECT C1.sidFROM CHOICES C1,CHOICES C2WHERE C1.cid=(SELECT cid FROM COURSES WHERE cname='Database')AND C1.sid=C2.sidAND C2.cid<>(SELECT cid FROM COURSES WHERE cname='UML');實(shí)驗(yàn)1.3 數(shù)據(jù)更新一、 實(shí)驗(yàn)?zāi)康氖煜?shù)據(jù)庫的數(shù)據(jù)更新操作，能夠使用SQL語句對數(shù)據(jù)庫進(jìn)行數(shù)據(jù)的插入、更新、刪除操作二、 實(shí)驗(yàn)內(nèi)容(1)

17、向STUDENTS表插入編號是且姓名是WangLan的元組。(2)向 TEACHERS表插入元組（，LXL，s4zrck，3024）(3)將 TEACHERS表中編號為的老師工資改為4000。(4)將 TEACHERS表中所有工資小于2500的老師工資改為2500。(5)將有編號老師講授的課程全部改成姓名rnupx的老師講授。(6)更新編號的學(xué)生年紀(jì)為2001。(7)刪除沒有學(xué)生選修的課程。(8)刪除年級高于1998的學(xué)生信息。(9)刪除沒有選修課程的學(xué)生信息。(10)刪除成績不及格的選課記錄。三、實(shí)驗(yàn)結(jié)果(1)INSERTINTO STUDENTS(sid,sname)VALUES (

18、9;','WangLan');(2)INSERTINTO TEACHERSVALUES ('','LXL','s4zrck',3024);(3)UPDATE TEACHERSSET salary=4000WHERE tid=''(4)UPDATE TEACHERSSET salary=2500WHERE salary<2500;(5)UPDATE CHOICESSET tid=(SELECT tid FROM TEACHERS WHERE tname='rnupx')WHERE tid

19、=''(6)UPDATE STUDENTSSET grade='2001'WHERE sid=''(7)DELETEFROM COURSESWHERE cid NOT IN(SELECT DISTINCT cid FROM CHOICES);(8)DELETEFROM STUDENTSWHERE grade<1998; (9)DELETEFROM STUDENTSWHERE sid NOT IN (SELECT DISTINCT sid FROM CHOICES);(10)DELETEFROM CHOICESWHERE score<6

20、0;實(shí)驗(yàn)1.4 視圖一、 實(shí)驗(yàn)?zāi)康氖煜QL支持的有關(guān)視圖的操作，能夠熟練地使用SQL語句來創(chuàng)建需要的視圖，對視圖進(jìn)行查詢和取消視圖。二、 實(shí)驗(yàn)內(nèi)容(1) 定義選課信息和課程名稱的視圖VIEWC；CREATE VIEW VIEWCASSELECT CHOICES.no,CHOICES.sid,CHOICES.tid,CHOICES.score,COURSES.cnameFROM CHOICES,COURSESWHERE CHOICES.cid=COURSES.cid;(2) 定義學(xué)生姓名與選課信息的視圖VIEWS；CREATE VIEW VIEWSASSELECT STUDENTS.sname

21、,CHOICES.no,CHOICES.tid,CHOICES.cid,CHOICES.scoreFROM STUDENTS,CHOICESWHERE STUDENTS.sid=CHOICES.sid;(3) 定義年級低于1998的學(xué)生的視圖S1(SID，SNAME，GRADE)；CREATE VIEW S1(sid,sname,grade)ASSELECT sid,sname,gradeFROM STUDENTSWHERE grade>1998;(4) 查詢學(xué)生為“uxjof”的學(xué)生的選課信息；SELECT *FROM VIEWSWHERE sname='uxjof'(

22、5) 查詢選修課程“UML”的學(xué)生的編號和成績；SELECT sid,scoreFROM VIEWCWHERE cname='UML'(6) 向視圖S1插入記錄(“，Lily，2001”)；INSERTINTO S1VALUES ('','Lily',2001);(7) 定義包括更新和插入約束的視圖S1，嘗試向視圖插入記錄(“，Lily，1997”)，刪除所有年級為1999的學(xué)生記錄，討論更新和插入約束帶來的影響。定義視圖：CREATE VIEW S1(sid,sname,grade)ASSELECT sid,sname,gradeFROM ST

23、UDENTSWHERE grade>1998WITH CHECK OPTION;插入元組：INSERTINTO S1VALUES('','Lily',1997);執(zhí)行結(jié)果：消息550，級別16，狀態(tài)1，第1 行試圖進(jìn)行的插入或更新已失敗，原因是目標(biāo)視圖或者目標(biāo)視圖所跨越的某一視圖指定了WITH CHECK OPTION，而該操作的一個或多個結(jié)果行又不符合CHECK OPTION 約束。語句已終止。結(jié)果分析：WITH CHECK OPTION表示對視圖進(jìn)行操作時要滿足子查詢的條件表達(dá)式，grade=1997本題的條件grade>1998不符，因此在本題

24、中插入這個元組是不成功的。刪除元組：DELETEFROM S1WHERE grade=1999;執(zhí)行結(jié)果：刪除成功(8) 在視圖VIEWS中將姓名為“uxjof”的學(xué)生的選課成績都加上5分。UPDATE VIEWSSET score=score+5WHERE sname='uxjof'(9) 取消以上建立的所有視圖。DROP VIEW VIEWCDROP VIEW VIEWSDROP VIEW S1;實(shí)驗(yàn)1.5 數(shù)據(jù)控制一、 實(shí)驗(yàn)?zāi)康氖煜QL的數(shù)據(jù)控制功能，能夠使用SQL語句來向用戶授予和收回權(quán)限。二、 實(shí)驗(yàn)內(nèi)容(1) 授予所有用戶對表STUDENTS的查詢權(quán)限。GRANT

25、SELECTON STUDENTSTO PUBLIC;(2) 授予所有用戶對表COURSES的查詢和更新權(quán)限。GRANT SELECT,UPDATEON COURSESTO PUBLIC;(3) 授予USER1對表TEACHERS的查詢，更新工資的權(quán)限，且允許USER1可以傳播這些權(quán)限。GRANT SELECT,UPDATE(salary)ON TEACHERSTO USER1WITH GRANT OPTION;(4) 授予USER2對表CHOICES的查詢，更新成績的權(quán)限。GRANT SELECT,UPDATE(score)ON CHOICESTO USER2;(5) 授予USER2對表TE

26、ACHERS的除了工資之外的所有信息的查詢。CREATE VIEW TVASSELECT tid,tname,emailFROM TEACHERS;GRANT SELECTON TVTO UESR2;(6) 由USER1授予USER2對表TEACHERS的查詢權(quán)限和傳播的此項權(quán)限的權(quán)利。GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION;(7) 由USER2授予USER3對表TEACHERS的查詢權(quán)限，和傳播的此項權(quán)限的權(quán)利。再由USER3授予USER2上述權(quán)限，這樣的SQL語句能否成功得到執(zhí)行?GRANT SELECTON TEACHERSTO

27、USER3WITH GRANT OPTION;GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION;(8) 取消USER1對表STUDENTS的查詢權(quán)限，考慮由USER2的身份對表STUDENTS進(jìn)行查詢，操作能否成功？為什么？REVOKE SELECTON TEACHERSFROM UESR1 CASCADE;操作不成功，取消授權(quán)操作的時候存在級聯(lián)效應(yīng)。(9) 取消USER1和USER2的關(guān)于表COURSES的權(quán)限。REVOKE SELECT,UPDATEON COURSESFROM UESR1,UESR2;實(shí)驗(yàn)1.6 空值和空集的處理一、實(shí)驗(yàn)?zāi)康?/p>

28、認(rèn)識NULL值在數(shù)據(jù)庫中的特殊含義，了解空值和空集對于數(shù)據(jù)庫的數(shù)據(jù)查詢操作，特別是空值在條件表達(dá)式中與其他的算術(shù)運(yùn)算符或邏輯運(yùn)算符的運(yùn)算中，空集作為嵌套查詢的子查詢返回結(jié)果的時候的特殊性，能夠熟練使用SQL語句來進(jìn)行與空值，空集相關(guān)的操作。二、實(shí)驗(yàn)內(nèi)容(1) 查詢所有課程記錄的上課學(xué)時（數(shù)據(jù)庫中為每星期學(xué)時），以一學(xué)期十八個星期計算每個課程的總學(xué)時，注意HOUR取NULL值的情況。SELECT cid,hour*18FROM COURSES;(2) 通過查詢選修課程C+的學(xué)生的人數(shù)，其中成績合格的學(xué)生人數(shù)，不合格的學(xué)生人數(shù)，討論NULL值得特殊含義。查詢選修C+的學(xué)生人數(shù)：SELECT COU

29、NT(*)FROM COURSES,CHOICESWHERE CHOICES.cid=COURSES.cid AND cname='C+'運(yùn)行結(jié)果為：95成績合格的學(xué)生人數(shù)：SELECT COUNT(*)FROM COURSES,CHOICESWHERE CHOICES.cid=COURSES.cid AND cname='C+' AND score<60;運(yùn)行結(jié)果為：86成績不合格的學(xué)生人數(shù)：SELECT COUNT(*)FROM COURSES,CHOICESWHERE CHOICES.cid=COURSES.cid AND cname='C+

30、' AND score>=60;運(yùn)行結(jié)果為：0NULL與比較運(yùn)算符的運(yùn)算都返回FALSE值，都不會出現(xiàn)在結(jié)果的統(tǒng)計之中。(3) 查詢選修課程C+的學(xué)生的編號和成績，使用ORDER BY按成績進(jìn)行排序時，取NULL的項是否出現(xiàn)在結(jié)果中？如果有，在什么位置？SELECT sid,scoreFROM CHOICESWHERE cid=(SELECT cid FROM COURSES WHERE cname='C+')ORDER BY score;NULL的項出現(xiàn)在結(jié)果中，被當(dāng)作最小值處理。(4) 在上面的查詢的過程中，如果加上保留字DISTINCT會有什么效果呢？SELECT DISTINCT scoreFROM CHOICESWHERE cid =(SELECT cid FROM COURSES WHERE cname='C+')ORDER BY score;成績?yōu)镹ULL的學(xué)生被合并(5) 按年級對所有的學(xué)生進(jìn)行分組，能得到多少個組？與現(xiàn)實(shí)的情況有什么不同？SELECT gradeFROM STUDENTSGROUP BY grade;得到9個分組，現(xiàn)實(shí)中有8個年級。(6) 結(jié)合分組，使用集合函數(shù)求每個課程選