數(shù)據(jù)庫應(yīng)用實驗教案

1、 課程實 驗 教 案課程編號： 總學時： 適用年級專業(yè)（學科類）： 開課時間 使用教材： 授課教師姓名： 實驗二：數(shù)據(jù)庫定義實驗一、實驗?zāi)康恼莆赵赟QL Server Query Analyzer中利用CREATE、DROP、ALTER等SQL語句創(chuàng)建和刪除數(shù)據(jù)庫，創(chuàng)建、刪除、更新基本表。二、實驗內(nèi)容1、利用查詢分析器創(chuàng)建學生課程數(shù)據(jù)庫（student），保存在學生學號命名的文件夾下。 注意觀察數(shù)據(jù)庫文件的后綴名。2、利用查詢分析器在student數(shù)據(jù)庫中，創(chuàng)建基本表學生（Student）、課程（Course）、選課（SC）,各表屬性具體描述如下：學生（學號，姓名，年齡，性別，所在系）； /*

2、學號是主碼，姓名取值惟一*/課程（課程號，課程名，先行課，學分）； /*課程號是主碼*/選課（學號，課程號，成績）。 /*學號和課程號為主碼*/Student(Sno char(10), Sname char(10), Ssex char(2), Sage int, Sdept Varchar(10) )Course(Cno char(4), Cname char(20), Cpno char(4), Ccredit int) SC(Sno char(10), Cno char(4), Grade int)要求：<為屬性選擇合適的數(shù)據(jù)類型。<定義必要的列級約束。<建庫、建表、

3、建立表間聯(lián)系（表間完整性約束）。注意：（1）將建立表SQL語句保存為以表名命名的sql腳本文件。（2）Allow Null和Default Value在定義表中的約束作用是什么？3、在查詢分析器窗體下鍵入如下命令DROP TABLE Student及DROP TABLE SC命令,運行后觀察數(shù)據(jù)庫student中的表有什么變化？再重新運行student.sql及 sc.sql，再觀察數(shù)據(jù)庫student中的表有什么變化？分析DROP TABLE Student及DROP TABLE SC命令的功能。4、在查詢分析器窗體下完成下列操作命令： （1）在Student表中增加一個新的屬性“專業(yè)”Sm

4、ajor char(20);（2）將Student表中的Sdept字段的長度更改為20；（3）刪除Student表中剛增加的屬性“專業(yè)”；（4）刪除Student表、SC表和Course表。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)實驗三：數(shù)據(jù)更新實驗一、實驗?zāi)康牧私馄髽I(yè)管理器中數(shù)據(jù)表數(shù)據(jù)的輸入、修改和刪除操作，熟練掌握使用SQL語句向數(shù)據(jù)庫輸入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)的方法。二、實驗內(nèi)容學生課程數(shù)據(jù)庫的三個基本表為學生（學號，姓名，年齡，性別，所在系）；課程（課程號，課程名，先行課，學分）；選課（學號，課程號，成績）。1、利用企業(yè)

5、管理器更新數(shù)據(jù)在學生課程數(shù)據(jù)庫的學生、課程和選課個表中各輸入下列表中的記錄。Student表學號姓名性別年齡所在系200215121李勇男20CS200215122劉晨女19CS200215123王敏女18MA200215125張立男19ISCourse表課程號課程名先行課學分1數(shù)據(jù)庫542數(shù)學23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27Pascal語言64SC表學號課程號成績2002151211922002151212852002151213882002151222902002151223802、使用查詢分析器更新數(shù)據(jù)，寫出相應(yīng)的SQL語句，并查看、分析執(zhí)行的結(jié)果。（1）使用S

6、QL語句刪除Student表、SC表和Course表中的所有數(shù)據(jù)；（2）使用SQL語句將上面三個表中的數(shù)據(jù)添加到Student表、SC表和Course表中；（3）將Student表中每個學生的年齡都增加1歲；（4）將SC表中每個學生的每門課程成績都乘以0.8。（5）思考：刪除表和刪除表中的數(shù)據(jù)使用的SQL語言有什么不同？三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)實驗四：數(shù)據(jù)查詢實驗一、實驗?zāi)康牧私庠谄髽I(yè)管理器或查詢分析器中執(zhí)行數(shù)據(jù)查詢的方法；掌握SQL Server Query Analyzer中簡單查詢、連接查詢、嵌套查詢操作方法

7、。二、實驗內(nèi)容1、使用企業(yè)管理器進行查詢（驗證性，了解部分）（1）查詢數(shù)據(jù)表的全部數(shù)據(jù)在企業(yè)管理器中，選擇需要查詢數(shù)據(jù)的表，用鼠標右鍵單擊該表，從彈出的快捷菜單中選擇“打開表”命令，這時將顯示該表的全部數(shù)據(jù)。（2）使用SELECT語句查詢數(shù)據(jù)表的數(shù)據(jù)在企業(yè)管理器中，選擇需要查詢數(shù)據(jù)的表，用鼠標右鍵單擊該表，從彈出的快捷菜單中選擇“打開表”命令，在打開返回表內(nèi)容的子窗口中，單擊工具欄按鈕“顯示/隱藏SQL窗格”，把子窗口分為上下兩部分，上面部分能輸入不同的SQL命令。執(zhí)行時單擊工具欄按鈕“運行”即可。（3）學習和使用QBE查詢在企業(yè)管理器中，選擇需要查詢數(shù)據(jù)的表，用鼠標右鍵單擊該表，從彈出的快捷

8、菜單中選擇“打開表”命令，在打開返回表內(nèi)容的子窗口中，單擊工具欄按鈕“顯示/隱藏條件窗格”，把子窗口分為上下兩部分，上面部分能選擇QBE查詢條件，執(zhí)行時單擊工具欄按鈕“運行”即可。若單擊工具欄按鈕“顯示/隱藏SQL窗格”，則在SQL窗格中會顯示自動生成的對應(yīng)SQL語句。2、使用查詢分析器進行查詢（驗證性）（1）查詢Student表中所有學生的學號、姓名和性別。SELECT Sno, Sname, SsexFROM Student（2）可以用“*”來選取數(shù)據(jù)表的全部列例如：查詢Student表中所有學生的基本情況。SELECT *FROM Student（3）在查詢結(jié)果中增加計算列，還可修改數(shù)據(jù)

9、列的顯示名稱。例如：查詢Student表中所有學生的學號、姓名、性別和出生年份。SELECT Sno 學號, Sname 姓名, Ssex 性別, Year(GetDate() - Sage 出生年份FROM Student（4）使用WHERE子句，可以選擇滿足條件的部分記錄例如：查詢成績在8590分之間的學生情況。SELECT Sno, Cno, GradeFROM SCWHERE Grade >= 85 AND Grade <= 90（5）使用DISTINCT關(guān)鍵字，可以消除重復(fù)記錄例如：查詢有成績的學生的學號。SELECT DISTINCT SnoFROM SC（6）使用IN

10、關(guān)鍵字，選擇不連續(xù)條件的記錄例如：查詢學生成績?yōu)?0或85的學生的學號。SELECT *FROM SCWHERE Grade IN (80, 85)（7）使用謂詞LIKE和通配符“%”或“_”，實現(xiàn)模糊查詢例如：查詢姓“張”的學生的基本情況。SELECT *FROM StudentWHERE Sname LIKE '張%'注意：“%”代表0個或多個字符，“_”代表一個字符。有的書上說，一個漢字占兩個字符，但這里一個漢字只占一個字符位置，這與系統(tǒng)的設(shè)置有關(guān)。（8）使用ORDER BY子句，對查詢結(jié)果進行排序例如：查詢所有學生的2號課的成績，并按成績由高向低排序。SELECT *F

11、ROM SCWHERE Cno = 2ORDER BY Grade DESC（9）使用TOP關(guān)鍵字，選擇查詢結(jié)果的前幾條記錄例如：查詢2號課成績最高的學生記錄。SELECT TOP 1 *FROM SCWHERE Cno = '2'ORDER BY Grade DESC注意：如果2號課成績存在并列最高，則使用下面的SQL語句。SELECT TOP 1 WITH TIES *FROM SC WHERE Cno = '2'ORDER BY Grade DESC（10）用WHERE子句指定連接條件例如：查詢所有有2號課程成績的學生的學號、姓名和成績。SELECT St

12、udent.Sno, Sname, GradeFROM Student, SCWHERE Student.Sno = SC.Sno AND Cno = '2' (11)將查詢結(jié)果存儲到表中例如：將總分在200分以上的學生的情況（學號、姓名、性別、總成績）存儲到數(shù)據(jù)表GradeList中，假設(shè)數(shù)據(jù)表GradeList不存在。SELECT A.Sno, Sname, Ssex, SUM(Grade) AS sumGradeINTO GradeListFROM Student A, SCWHERE A.Sno = SC.SnoGROUP BY A.Sno, Sname, SsexHA

13、VING (SUM(SC.Grade) >= 200)該語句執(zhí)行成功后，在企業(yè)管理器中可以看到已經(jīng)創(chuàng)建的數(shù)據(jù)表GradeList，并將查詢結(jié)果是存儲在該表中了。（12）將查詢結(jié)果保存到變量中例如：查詢學號為200215121的學生1號課的成績，將其保存到變量Grade中。DECLARE Grade INT -定義變量SELECT Grade = Grade -給變量賦值FROM SCWHERE Sno = '200215121'PRINT Grade -顯示變量的值注意：要將查詢結(jié)果保存到變量中，只能將查詢結(jié)果集中第一條記錄的值賦給變量。3、分析設(shè)計部分在學生選課庫中實現(xiàn)

14、下列數(shù)據(jù)查詢操作，并寫出相應(yīng)的SQL腳本。(1)求計算機系學生的學號和姓名(2)求選修3號課程的學生學號和成績,結(jié)果按成績降序排列,如成績同按學號升序排列(3)求選修課程2成績在80-90之間的學生學號和成績,并將成績乘以0.8輸出(4)求數(shù)學或計算機系姓張的學生的信息(5)求缺少了成績的學生的學號和課程號(6)查詢各門課程及相應(yīng)的選課人數(shù)。(7)查詢總成績在200分以上的學生的學號、總成績和平均成績(8)在FROM子句中用INNER JOIN連接符指定連接條件查詢所有有2號課程成績的學生的學號、姓名和成績。 (9)查詢學生中年齡相同的學生情況（使用自連接查詢）。(10)查詢所有學生的總成績（

15、包括沒有成績的學生）、學號和姓名（外部連接查詢）。(11)查詢某課程成績在90分以上的學生的學號和姓名（使用謂詞IN連接子查詢）。(12)查詢有課程成績的學生的學號和姓名(使用謂詞EXISTS連接子查詢)。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)實驗五：用戶權(quán)限管理實驗一、實驗?zāi)康恼莆誗QL Server中有關(guān)用戶、角色及操作權(quán)限的設(shè)置方法。二、實驗內(nèi)容1. 用超級用戶登錄學生數(shù)據(jù)庫student。2. 建立兩個新用戶:用戶名stu1,密碼stu1（登錄名login1）；用戶名stu2,密碼stu2（登錄名login2）和用戶名s

16、tu3,密碼stu3（登錄名login3）。3. 授予stu1對SC具有select和insert權(quán)限,授予stu1對student具有select,update,insert和delete權(quán)限, 授予stu1對Course具有select,update,insert和delete權(quán)限。4授予stu2對SC具有select權(quán)限,授予stu2對student具有select權(quán)限, 授予stu2對Course具有select權(quán)限；授予stu2對Course具有更新屬性Ccredit的權(quán)限。5測試授權(quán)以用戶Stu1登錄學生數(shù)據(jù)庫：（1）查詢SC、student和Course表中的所有數(shù)據(jù)；（2）分別

17、向SC、student和Course表中插入一組合法數(shù)據(jù)；（3）分別對SC、student和Course表更改一個元組；（4）分別對SC、student和Course表刪除一個元組。觀察運行結(jié)果并分析原因。以用戶Stu2登錄學生數(shù)據(jù)庫：（1）分別向SC、student和Course表中插入一組合法數(shù)據(jù)；（2）更新Course表中課程號為1的課程的名稱為人工智能；（3）更新Course表中課程號為1的課程的學分為3。觀察運行結(jié)果并分析原因。以用戶Stu1登錄學生數(shù)據(jù)庫：（1）收回stu1對SC表的select權(quán)限，對student表的update和insert權(quán)限，對Course表的delete

18、權(quán)限；（2）查詢SC、student和Course表中的數(shù)據(jù)，觀察運行結(jié)果；（3）分別向SC、student和Course表中插入一組合法數(shù)據(jù)，觀察運行結(jié)果；（4）分別對SC、student和Course表更改一個元組，觀察運行結(jié)果；（5）分別對SC、student和Course表刪除一個元組，觀察運行結(jié)果。思考Course是否具有delete權(quán)限執(zhí)行效果有何不同？6創(chuàng)建數(shù)據(jù)庫角色 Myrole，設(shè)置訪問student表的select和insert權(quán)限，并添加用戶成員stu3。以stu3登錄查看驗證。7使用SQL命令完成：刪除登錄名login1、login2和login3；刪除用戶名stu1、

19、stu2和stu3；刪除角色Myrole。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)實驗六：數(shù)據(jù)完整性實驗一、實驗?zāi)康模赫莆帐褂肧QL中的PRIMARY KEY、CHECK、FOREIGN KEYREFERENCES、NOT NULL、UNIQUE等關(guān)鍵字是現(xiàn)實體完整性、參照完整性及用戶定義完整性約束定義。二、實驗步驟：（一）驗證分析部分1、利用SQL查詢分析器用PRIMARY KEY子句保證實體完整性。在查詢分析器窗體下鍵入如下命令：CREATE TABLE Student(sno CHAR(5) NOT NULL UNIQUE,

20、 sname CHAR(8), ssex CHAR(1),sage INT,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno); UPDATE Student SET sno= WHERE sdept=CS;UPDATE Student SET sno=200215121 WHERE sname=王敏;運行并觀察結(jié)果。2、利用SQL查詢分析器用FOREIGN KEYREFERENCES子句保證實體完整性。在查詢分析器窗體下鍵入如下命令：(1)先刪除原來SC表中關(guān)于Sno 的外鍵，再將SC的Sno外鍵修改為：alter TABLE SC a

21、dd CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON update CASCADE;然后執(zhí)行以下語句Update Student SET sno=200215128 WHERE sno=200215121;(2) 先刪除原來SC表中關(guān)于Sno 的外鍵，再將SC的Sno外鍵修改為：alter TABLE SC add CONSTRAINT FK_Sno FOREIGN KEY (sno)REFERENCES Student(sno)ON delete CASCADE;然后執(zhí)行以下語句delete from student

22、WHERE sno=200215122;運行并觀察結(jié)果。3、利用SQL查詢分析器用短語NOT NULL、UNIQUE、CHECK保證用戶定義完整性。CREATE TABLE Student(sno CHAR(5) , sname CHAR(8) CONSTRAINT U1 UNIQUE, ssex CHAR(1),sage INT CONSTRAINT U2 CHECK FOR sage<=28,sdept CHAR(20)，CONSTRAINT PK_Student PRIMARY KEY(sno)執(zhí)行以下SQL語句Insert into values(2002101,li,女,89,

23、DS)運行并觀察結(jié)果。思考：如果要求sdept輸入內(nèi)容只能為字母該如何約束？（二）設(shè)計分析部分 在Student數(shù)據(jù)庫中，完成以下完整性約束設(shè)置。1、設(shè)置Student表中Ssex的取值只能是男或女；2、設(shè)置Student表中Sdept的取值默認為CS；3、刪除操作測試（1）設(shè)置Course表和 SC表之間的關(guān)系，實現(xiàn)cascade delete related records。（2）刪除Course 表中記錄，查看SC表中數(shù)據(jù)的變化（SC表中Cno有與Course表中Cno對應(yīng)的記錄刪除和無對應(yīng)記錄的刪除都要測試），有什么結(jié)果，為什么？4. 修改操作測試（1）在SC表中，將課程號為3的改為3

24、3，都有什么結(jié)果？為什么？（2）將Course表和sc表的課程號為3的都改為33，請用完整性約束實現(xiàn)？5. 插入數(shù)據(jù)測試（1）在STUDENT表中插入一條新紀錄： 95006 李三 男 21 IS（2）在SC表中插入一條新紀錄： 95008 3 80 插入執(zhí)行后都有什么結(jié)果？為什么？如何完成上述操作？三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)實驗七：觸發(fā)器設(shè)計實驗一、實驗?zāi)康模赫莆沼|發(fā)器編寫方法，能運用設(shè)計的觸發(fā)器實現(xiàn)對數(shù)據(jù)表中數(shù)據(jù)完整性約束的控制。二、實驗內(nèi)容：要求為Course表編寫一個名為CourseTrigger插入觸發(fā)器，

25、當插入記錄到Course表時，該觸發(fā)器檢查新插入記錄的userid字段，如果插入這個字段的值不存在于Teacher表中，則從Course表中刪除這個新插入記錄。Teacher(userid char(20), name char(20),department char(20), age)Course(courseid int, userid char(20), name char(20), grade int)說明：1對表Teacher和Course之間不允許建立外鍵約束；2本實驗屬于設(shè)計型實驗，要求學生設(shè)計一個符合實驗內(nèi)容要求的觸發(fā)器。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 20

26、03平臺 + SQL Server 2005系統(tǒng)課外補充：在Student、Course和SC表中創(chuàng)建以下觸發(fā)器(1)創(chuàng)建一個after觸發(fā)器，在Student表中刪除某學生的記錄時，刪除其相應(yīng)的選課記錄 (不允許使用外鍵級聯(lián)刪除約束)。(2)創(chuàng)建一個觸發(fā)器，不允許刪除Course表中的任何數(shù)據(jù)。實驗八、概念數(shù)據(jù)模型設(shè)計實驗一、實驗?zāi)康模菏煜owerDesigner環(huán)境，掌握創(chuàng)建概念數(shù)據(jù)模型的方法。 二、實驗內(nèi)容：1設(shè)計出版社出版圖書管理系統(tǒng)的概念數(shù)據(jù)模型，其設(shè)計CDM圖結(jié)構(gòu)如下圖所示。完成下圖所示的CDM圖，并對圖中的實體和標識符進行完善和補充。2將實驗2中創(chuàng)建的CDM模型轉(zhuǎn)換成SQL S

27、ERVER對應(yīng)的邏輯模型。3實驗報告中寫出完善的E-R圖和對應(yīng)的邏輯模型圖。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + PowerDesigner + SQL Server 2005系統(tǒng)實驗九 存儲過程設(shè)計一、實驗?zāi)康?掌握使用SQL Server2005平臺設(shè)計存儲過程的方法。二、實驗內(nèi)容設(shè)計要求：1、使用STUDENT數(shù)據(jù)庫中的學生表、課程表、選課表，創(chuàng)建一個帶參數(shù)的存儲過程userproc。該存儲過程的作用是：當任意輸入一個學生的姓名時，將從三個表中返回該學生的學號、選修的課程名稱和課程成績。2、將姓名為XXX的學生所有課程的成績減少10分（其中學生姓名為存儲過程的參數(shù)）。三、實驗學時2學時四、實驗設(shè)備與環(huán)境Windows 2003平臺 + SQL Server 2005系統(tǒng)存儲過程設(shè)計輔助實驗一 實驗?zāi)康?通過實驗掌握SQL SERVER存儲過程的基本設(shè)計方法。二、內(nèi)容和方法1. 創(chuàng)建存儲過程步驟1) 寫SQL語句 例如：查詢所有學生的記錄 select * from student2) 測試SQL語句 執(zhí)行這些SQL語句。確認符合要求。3) 若得到所需結(jié)果，則創(chuàng)建過程 如果發(fā)現(xiàn)符合要求，則按照存儲