數據庫第一章實驗報告

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

2、約束。(2)創(chuàng)建數據庫表ORDERS(ORDNA,MONTH,CID,AID,PID,QTY,DOLLARS)。其中ORDNA分表是主鍵，具有唯一性約束。CID、AID、PID分別是外鍵引用自表CUSTOMERS、表AGENTS,表PRODUCTS。(3)增加數據庫表PRODUCTS三個屬性列：CITY、QUANTITY、PRICE。(4)為以上四個表建立各自的按主鍵增序排列的序列。(5)取消（4）建立的四個索引。三、實驗結果專心-專注-專業(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實驗1.2 數據查詢一、 實驗目的熟悉SQL語句的數據查詢語言，能夠使用SQL語句對數據庫進行單表查詢、連接查詢、嵌套查詢、集合查詢和統(tǒng)計查詢。二、 實驗內容(1)查詢全部課程的詳細記錄。(2

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

7、a的學生的名稱。(15) 查詢課時最少的課程的詳細信息。(16)查詢工資最高的教師的編號和開設的課號。(17)找出選修課程ERP成績最高的學生編號。(18)查詢沒有學生選的課程的名稱。(19)找出講授課程UML的教師講授的所有課程名稱。(20查詢選修了編號的教師開設所有課程的學生編號。(21)查詢選修課程database的學生集合與選修UML的學生集合的并集。(22)實現集合交運算，查詢級選修課程database又選修UML的學生的編號。(23)實現集合減運算，查詢級選修課程database而又沒有選修UML的學生的編號。三、實驗結果(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');實驗1.3 數據更新一、 實驗目的熟悉數據庫的數據更新操作，能夠使用SQL語句對數據庫進行數據的插入、更新、刪除操作二、 實驗內容(1)

17、向STUDENTS表插入編號是且姓名是WangLan的元組。(2)向 TEACHERS表插入元組（，LXL，s4zrck，3024）(3)將 TEACHERS表中編號為的老師工資改為4000。(4)將 TEACHERS表中所有工資小于2500的老師工資改為2500。(5)將有編號老師講授的課程全部改成姓名rnupx的老師講授。(6)更新編號的學生年紀為2001。(7)刪除沒有學生選修的課程。(8)刪除年級高于1998的學生信息。(9)刪除沒有選修課程的學生信息。(10)刪除成績不及格的選課記錄。三、實驗結果(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;實驗1.4 視圖一、 實驗目的熟悉SQL支持的有關視圖的操作，能夠熟練地使用SQL語句來創(chuàng)建需要的視圖，對視圖進行查詢和取消視圖。二、 實驗內容(1) 定義選課信息和課程名稱的視圖VIEWC；CREATE VIEW VIEWCASSELECT CHOICES.no,CHOICES.sid,CHOICES.tid,CHOICES.score,COURSES.cnameFROM CHOICES,COURSESWHERE CHOICES.cid=COURSES.cid;(2) 定義學生姓名與選課信息的視圖VIEWS；CREATE VIEW VIEWSASSELECT STUDENTS.sname

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

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

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

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

25、SELECTON STUDENTSTO PUBLIC;(2) 授予所有用戶對表COURSES的查詢和更新權限。GRANT SELECT,UPDATEON COURSESTO PUBLIC;(3) 授予USER1對表TEACHERS的查詢，更新工資的權限，且允許USER1可以傳播這些權限。GRANT SELECT,UPDATE(salary)ON TEACHERSTO USER1WITH GRANT OPTION;(4) 授予USER2對表CHOICES的查詢，更新成績的權限。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的查詢權限和傳播的此項權限的權利。GRANT SELECTON TEACHERSTO USER2WITH GRANT OPTION;(7) 由USER2授予USER3對表TEACHERS的查詢權限，和傳播的此項權限的權利。再由USER3授予USER2上述權限，這樣的SQL語句能否成功得到執(zhí)行?GRANT SELECTON TEACHERSTO

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

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

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

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