1、數 據 庫實驗指導書電氣與電子工程學院實 驗 項 目實驗一 認識DBMS及數據庫的建立實驗二 交互式SQL(數據庫查詢與更新)實驗三 數據控制（安全性部分）實驗四 數據控制（完整性部分）實驗一 認識DBMS及數據庫的建立一、實驗目的：1. 掌握使用SQL SERVER企業管理器及SQL語言創建數據庫與表。2. 掌握使用SQL SERVER企業管理器及SQL語言修改數據庫與表。3. 掌握使用SQL SERVER企業管理器及SQL語言刪除數據庫與表。二、實驗內容及步驟：1 通過用企業管理器創建一個指定多個數據文件和日志文件的數據庫，該數據庫名稱為jiaoxuedb，要求：有1個10MB和1個20M

2、B的數據文件和2個10MB的事務日志文件。數據文件邏輯名稱為jiaoxuedb1和jiaoxuedb2，物理文件名為jiaoxuedb1.mdf和jiaoxuedb2.mdf。主文件是jiaoxuedb1，由primary指定，兩個數據文件的最大尺寸分別為無限大和100MB，增長速度分別為10%和1MB。事務日志文件的邏輯名為jiaoxuedblog1和jiaoxuedblog2，物理文件名為jiaoxuedblog1.df和jiaoxuedblog2.ldf，最大尺寸均為50MB，文件增長速度為1MB。 2 用企業管理器在jiaoxuedb數據庫中建立如下表： 學生表: jiaoxuedbd

3、ent 課程表: Course 選課表: SC 3 查看表格：顯示當前數據庫中所有對象的信息。 4 利用企業管理器刪除表：在企業管理器中，展開指定的數據庫和表格項，用右鍵單擊要刪除的表，從快捷菜單中選擇“除去表”選項，則會出現除去對象對話框。單擊“全部刪除”按鈕，即可去除 表。 5 利用企業管理器刪除數據庫jiaoxuedb。 6 用SQL語言創建數據庫jiaoxuedb： 7 用SQL語言創建jiaoxuedb中的表： 實驗二 交互式SQL(數據庫查詢與更新)1、 實驗目的：1. 掌握SELECT查詢語句在單表查詢、連接查詢、嵌套查詢和集合查詢中的常規用法。2. 掌握常用聚集函數的用法。3.

4、 掌握LIKE、DISTINCT、ORDER BY、GROUP BY及UNION的用法。4. 掌握帶EXISITS的查詢語句。5. 掌握用SQL語句實現數據的插入、修改和刪除。二、實驗內容及步驟：基于jiaoxuedb實驗，需按實驗一中的數據值建立數據庫及其中的表。1. 重建實驗一中的數據庫jiaoxuedb和其中的表，并插入相應數據值。插入jiaoxuedb庫中各表值： 2. 重建實驗一中的數據庫jiaoxuedb和其中的表，并插入相應的數值。創建數據庫jiaoxuedb：創建jiaoxuedb中的表： 插入jiaoxuedb庫中各表值： 3. 將數據庫jiaoxuedb中信息系全體學生的成

5、績置0。 4. 將數據庫jiaoxuedb中電子商務系學生年齡增加1歲。 5. 將數據庫jiaoxuedb中計算機系所有學生的選課記錄刪除。 6. 查詢成績在8090之間的記錄。SELECT jiaoxuedbdent.Sno,Sname,Dept,Course.Cno,ScoreFROM jiaoxuedbdent,SC,CourseWHERE jiaoxuedbdent.Sno=SC.Sno AND SC.Score BETWEEN 80 AND 90AND SC.Cno=Course.Cno;7. 查詢至少有4個同學選修的課程名。USE jiaoxuedbSELECT Cno AS 課程

6、號,Cname AS 課程名FROM CourseWHERE Cno=ANY (SELECTCno AS 課程號FROM SCGROUP BY CnoHAVING COUNT(*)=4)GO8. 查詢其他系中比“信息系”所有學生年齡都打的學生名單及年齡，并按年齡降序輸出。USE jiaoxuedbSELECT Sno AS 學號,Sname AS 姓名,Age AS 年齡,Dept AS 專業FROM jiaoxuedbdentWHERE AgeALL (SELECT AgeFROM jiaoxuedbdentWHERE Dept=信息)AND Dept 信息ORDER BY Age DESC

7、GO9. 查詢與學生張建國同歲的所有學生的學號、姓名和系別。/*方法一*/USE jiaoxuedbSELECT Sno AS 學號,Sname AS 姓名,Age AS 年齡,Dept AS 專業FROM jiaoxuedbdentWHERE Age=ANY (SELECT AgeFROM jiaoxuedbdentWHERE Sname=張建國)AND Sname 張建國ORDER BY Age DESCGO/*方法二*/USE jiaoxuedbSELECT s2.Sno,s2.Sname,s2.DeptFROM jiaoxuedbdent s1, jiaoxuedbdent s2WHE

8、RE s1.age=s2.age AND s1.Sname=張建國 AND s2.Sname 張建國10. 查詢選修了2門以上課程的學生名單。USE jiaoxuedbSELECT Sno AS 學號,Sname AS 姓名,Age AS 年齡,Dept AS 專業FROM jiaoxuedbdentWHERE Sno=ANY (SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*) =2)GO11. 查詢至少有一門與“張建國”選課相同的學生的姓名、課程名和系別。USE jiaoxuedbSELECT Sno AS 學號,Sname AS 姓名,Age AS

9、年齡,Dept AS 專業FROM jiaoxuedbdentWHERE Sno=ANY (SELECT SnoFROM SCWHERE Cno=ANY(SELECT CnoFROM SCWHERE Sno=991103)AND Sname 張建國GO12. 查詢成績比該課程平均成績高的學生的成績表。/*方法一*/USE jiaoxuedbSELECT *FROM Sc xWHERE Score (SELECT AVG(Score)FROM SC yWHERE y.Cno=x.Cno);GO/*方法二*/SELECT *FROM SC sc1WHERE Score (SELECT AVG(Sc

10、ore)FROM SC sc2)AND sc1.Cno=sc2.Cno;GO13. 查詢選修課號為01001課程且成績高于課程01002學生的成績姓名、此兩門課程的選修課程名和成績。/*方法一*/USE jiaoxuedbSELECT Sname,Cname,ScoreFROM jiaoxuedbdent ,SC,CourseWHERE jiaoxuedbdent.Sno in(SELECT s1.SnoFROM SC s1,SC s2WHERE s1.Cno=01001 AND s2.Cno=01002 AND s1.Scores2.Score AND s1.Sno=s2.Sno)AND S

11、C.Sno=jiaoxuedbdent.SnoAND Course.Cno=SC.CnoGO/*方法二*/USE jiaoxuedbSELECT Sname,Cname,ScoreFROM jiaoxuedbdent ,SC,CourseWHERE jiaoxuedbdent.Sno in(SELECT SnoFROM SC xWHERE Score = (SELECT AVG(Score)FROM SC yWHERE y.Sno=x.SnoAND x.Cno=01001AND y.Cno=01002)AND SC.Sno=jiaoxuedbdent.SnoAND Course.Cno=SC.

12、Cno)GO14. 查詢所有未修01001號課程的學生名單。USE jiaoxuedbSELECT jiaoxuedbdent.Sno,SnameFROM jiaoxuedbdentWHERE jiaoxuedbdent.Sno IN(SELECT s1.SnoFROM SC s1WHERE Sno NOT IN(SELECT s2.SnoFROM SC s2WHERE Cno=01001)GO15. 查詢每個同學各門課程的平均成績和最高成績，按降序排列輸出姓名、平均成績、最高成績。USE jiaoxuedbSELECT Sname AS 姓名,AVG(Score) AS 平均,MAX(Sco

13、re) AS 最高FROM jiaoxuedbdent,SCWHERE jiaoxuedbdent.Sno=SC.SnoGROUP BY SnameORDER BY Sname DESCGO16. 查詢所有學生都選修了的課程號和課程名。USE jiaoxuedbSELECT Cname AS 課程號,Cno AS 課程名FROM CourseWHERE NOT EXISTS(SELECT *FROM jiaoxuedbdentWHERE NOT EXISTS(SELECT * FROM SCWHERE jiaoxuedbdent.Sno=SC.Sno AND SC.Cno=Course.Cno

14、);GO17. 查詢選修了991102號學生選修了的課程的學生學號和姓名。use jiaoxuedbSELECT DISTINCT Sno，SnameFROM jiaoxuedbdent s1WHERE NOT EXISTS(SELECT Cno FROM SC s2 WHERE Sno=991102 AND NOT EXISTS(SELECT Cno FROM SC s3 WHERE s1.Sno=s3.Sno AND s2.Cno=s3.Cno)AND s1.Sno!= 991102GO實驗三 數據控制（安全性部分）一、實驗目的：1. 掌握利用SQL語言及SQL SERVER企業管理器創建

15、索引的操作。2. 掌握利用SQL語言及SQL SERVER企業管理器創建和查看視圖的操作。3. 掌握利用SQL語言及SQL SERVER企業管理器修改和刪除視圖的操作。二、實驗內容及步驟：基于jiaoxuedb實驗，需按實驗二中的數據值建立數據庫及其中的表。1. 對教師表Teacher中的教師號Tno創建聚族索引，并按降序排列。2. 對學生選課表SC，先按課號Cno升序排列，再按成績Score降序排列。Cno升序：USE jiaoxuedbCREATE CLUSTERED INDEX IND_SC_CNO ON SC(Cno ASC)3. 對表jiaoxuedbdent中的學號Sno創建唯一索

16、引，并按升序排列。USE jiaoxuedbCREATE UNIQUE INDEX IND_S_SNO ON jiaoxuedbdent(Sno ASC)4. 通過企業管理器的索引管理功能觀察Teacher、jiaoxuedbdent和SC索引的建立情況。5. 用DROP INDEX語句刪除以上建立的索引。6. 創建成績視圖SCORE_VIEW,包含學號SNO,姓名SN，課程名CN，成績SCORE。7. 創建一個計算機系學生名單視圖S_VIEW，包含學號SNO，姓名SN，性別SEX。8. 通過上面的視圖，修改學號為991102，課號為01001的成績記錄。9. 創建一個視圖，計算機系學生的成績

17、單SCORE_VIEW_CDEPT,包含學號SNO，姓名SN，課程名CN，成績SCORE。10. 給視圖S_VIEW增加一個年齡AGE字段。11. 創建一個教師工資表視圖SAL_VIEW，包含字段教師名Tname，性別Sex，職稱Prof，工資總和SALSUM，系別Dept。12. 通過視圖查詢教師“張明”的工資收入。USE jiaoxuedbSELECT Tname,SALSUMFROM SAL_VIEWWHERE Tname=張朋GO13. 通過視圖S_VIEW，刪除學生王蕾的記錄。USE jiaoxuedbDELETE FORM S_VIEWWHERE Sname=王蕾)GO實驗四 數據控制（完整性部分）一、 實驗目的：掌握關系數據庫中的實體、參照及用戶自定義完整性約束定義。二、實驗內容及步驟：基于jiaoxu