《數(shù)據(jù)庫開發(fā)技術(shù)實(shí)驗(yàn)》實(shí)驗(yàn)指導(dǎo)書

1、浙江樹人大學(xué)信息科技學(xué)院數(shù)據(jù)庫開發(fā)技術(shù)實(shí)驗(yàn)實(shí)驗(yàn)指導(dǎo)書適合專業(yè)：計(jì)算機(jī)科學(xué)與技術(shù)本科專業(yè)編寫部門：電子商務(wù)教研室編寫日期：2014.0232實(shí)驗(yàn)一：安全性管理1實(shí)驗(yàn)二：T-SQL高級(jí)查詢7實(shí)驗(yàn)三：數(shù)據(jù)完整性測(cè)試13實(shí)驗(yàn)四：T-SQL編程結(jié)構(gòu)15實(shí)驗(yàn)五：游標(biāo)和事務(wù)設(shè)計(jì)17實(shí)驗(yàn)六：SQL Server函數(shù)的使用19實(shí)驗(yàn)七：存儲(chǔ)過程的創(chuàng)建和使用21實(shí)驗(yàn)八：觸發(fā)器的創(chuàng)建23實(shí)驗(yàn)九：數(shù)據(jù)庫應(yīng)用系統(tǒng)分析與設(shè)計(jì)28實(shí)驗(yàn)一：安全性管理一、 實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生理解SQL Server 2000數(shù)據(jù)庫安全性的管理方法。二、 原理解析1、SQL Server登錄帳號(hào)與登錄有關(guān)的存儲(chǔ)過程：sp_addlogin 創(chuàng)

2、建SQL Server登錄帳號(hào)sp_droplogin刪除SQL Server登錄帳號(hào)sp_grantlogin創(chuàng)建windows登錄帳號(hào)sp_revokelogin刪除windows用戶或用戶組在SQL Server上的登錄信息sp_denylogin拒絕某一windows用戶或用戶組連接到SQL Server2、數(shù)據(jù)庫用戶帳號(hào)只有數(shù)據(jù)庫用戶才具有訪問、操作該數(shù)據(jù)庫的權(quán)限，某一登錄帳號(hào)要獲得操作數(shù)據(jù)庫的權(quán)限，必須與相應(yīng)的數(shù)據(jù)庫用戶相映射。與數(shù)據(jù)庫用戶帳號(hào)有關(guān)的存儲(chǔ)過程：sp_grantdbaccess創(chuàng)建一個(gè)數(shù)據(jù)庫用戶sp_revokedbaccess刪除一個(gè)數(shù)據(jù)庫用戶3、角色管理可以把一些

3、用戶歸入某一角色，這樣只要角色具有了一定的權(quán)限，用戶相應(yīng)地從該角色繼承了相應(yīng)的權(quán)限，角色的提出有利于權(quán)限的管理。角色可分為固定服務(wù)器角色和數(shù)據(jù)庫角色。與角色有關(guān)的存儲(chǔ)過程：sp_addsrvrolemember添加固定服務(wù)器角色成員sp_addrole向數(shù)據(jù)庫添加一角色sp_addrolemember添加數(shù)據(jù)庫角色成員sp_dropsrvrolemember刪除固定服務(wù)器角色成員sp_droprolemember刪除數(shù)據(jù)庫角色成員sp_droprole刪除角色注意：刪除角色前，必須刪除角色成員。4、許可管理許可是用來授權(quán)用戶可以使用數(shù)據(jù)庫中數(shù)據(jù)和執(zhí)行數(shù)據(jù)庫操作。許可授予grant語句許可禁止d

4、eny語句許可收回revoke語句三、 實(shí)驗(yàn)內(nèi)容（1） 登錄帳號(hào)的管理。（2） 數(shù)據(jù)庫用戶帳號(hào)的管理。（3） 角色管理。（4） 許可管理。四、 實(shí)驗(yàn)步驟（1）創(chuàng)建SQL Server登錄帳號(hào)aa，賦予其系統(tǒng)管理員角色。（2）賦予windows登錄帳號(hào)bb在數(shù)據(jù)庫Student上的db_owner訪問權(quán)限。（3）創(chuàng)建SQL Server登錄帳號(hào)LoginT，其在Student數(shù)據(jù)庫上的對(duì)應(yīng)用戶為userT。（4）刪除userT所對(duì)應(yīng)的登錄帳號(hào)loginT。（5）刪除Student數(shù)據(jù)庫用戶帳號(hào)userT。（6）在服務(wù)器上創(chuàng)建一Windows用戶cc，然后將cc授權(quán)登錄和訪問SQL Server系

5、統(tǒng)。依次利用此賬號(hào)作測(cè)試登錄。測(cè)試成功后運(yùn)行腳本：sp_revokelogin cc然后以cc登錄SQL Server系統(tǒng)，能否成功。運(yùn)行腳本：sp_addlogin cc,cc Gosp_denylogin cc Go (7) 利用系統(tǒng)存儲(chǔ)過程為數(shù)據(jù)庫Student創(chuàng)建一數(shù)據(jù)庫角色myrole，并創(chuàng)建一個(gè)數(shù)據(jù)庫用戶myuser1，使其屬于角色myrole。(8) 將數(shù)據(jù)庫用戶myuser1添加為數(shù)據(jù)庫Student的db_owner角色。(9) 在SQL Server查詢分析器中，執(zhí)行下列腳本，并查看數(shù)據(jù)庫myTestDB。-創(chuàng)建范例數(shù)據(jù)庫myTestDBCreate Database my

6、TestDBGo-創(chuàng)建測(cè)試用表stud01和stud02use myTestDBGoCreate Table stud01(no char(5) ,name char(10) ,age int ) GoCreate Table stud02(no char(5) ,name char(10) ,address varchar(30) ) Go-添加測(cè)試數(shù)據(jù)Insert Into stud01(no,name,age) Values (00001,aa,15)GoInsert Into stud01(no,name,age) Values (00002,bb,16)GoInsert Into s

7、tud02(no,name,address) Values (00001,aa,zhejiang)GoInsert Into stud02(no,name,address) Values (00002,bb,hangzhou)Go1) 執(zhí)行下列腳本，并查看數(shù)據(jù)庫myTestDB登錄賬號(hào)、數(shù)據(jù)庫用戶賬號(hào)和角色。-創(chuàng)建登錄賬號(hào)loginA和loginB，口令分別為a和bsp_addlogin loginA,aGosp_addlogin loginB,bGo-創(chuàng)建testDB數(shù)據(jù)庫的userA和userB用戶賬號(hào)use myTestDBGosp_grantdbaccess loginA,userAG

8、osp_grantdbaccess loginB,userBGo-添加testDB數(shù)據(jù)庫的角色userNsp_addrole roleNGo-為角色roleN添加兩用戶userA和userBsp_addrolemember roleN,userAGosp_addrolemember roleN,userBGo2) 斷開SQL Server查詢分析器連接，重新以賬號(hào)loginA登錄，執(zhí)行下列腳本,查看結(jié)果（系統(tǒng)提示無Select權(quán)限）Use myTestDBGoSelect * from stud01Go3) 斷開SQL Server查詢分析器連接，以賬號(hào)sa登錄，執(zhí)行下列腳本。-將testDB

9、數(shù)據(jù)庫中的表products的Select許可授予角色roleNUse myTestDBGoGrant Select On stud01 To roleNGo-將myTestDB數(shù)據(jù)庫中的表stud01的Insert,Update,Delete許可-授予用戶userA和userBGrant Insert,Update,Delete On stud01 To userA,userBGo-將myTestD數(shù)據(jù)庫創(chuàng)建表的許可授予用戶userAGrant Create Table To userAGo4) 斷開SQL Server查詢分析器連接，重新以賬號(hào)loginA登錄，重新執(zhí)行腳本,查看結(jié)果（可檢

10、索表stud01，但無檢索表stud02權(quán)限）Use myTestDBGoSelect * from stud01GoSelect * from stud02Go5) 斷開SQL Server查詢分析器連接，重新以賬號(hào)sa登錄，執(zhí)行下列腳本。Use myTestDBGo-將userA對(duì)表stud01的Select許可收回Revoke Select On stud01 To userAGo6) 斷開SQL Server查詢分析器連接，重新以賬號(hào)loginA登錄，再執(zhí)行下列腳本,查看結(jié)果（還是可以檢索表stud01，因?yàn)殡m然userA的Select許可被收回，但由于角色roleN具有表stud01

11、的Select許可，而userA作為角色roleN的成員繼承了roleN的權(quán)限，故還能Select表stud01）Use myTestDBGoSelect * from stud01Go7) 斷開SQL Server查詢分析器連接，以賬號(hào)sa登錄，執(zhí)行下列腳本。Use myTestDBGo-將用戶userA對(duì)表stud01的Select許可否決Deny Select On stud01 To userAGo8) 斷開SQL Server查詢分析器連接，重新以賬號(hào)loginA登錄，再執(zhí)行下列腳本,查看結(jié)果（此時(shí)，將無法檢索表stud01）Use myTestDBGoSelect * from s

12、tud01Go9) 執(zhí)行下列腳本查看數(shù)據(jù)庫用戶信息和許可信息。Use myTestDBGosp_helprotectGosp_helpuserGo實(shí)驗(yàn)二：T-SQL高級(jí)查詢一、 實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生掌握T-SQL高級(jí)查詢。本次實(shí)驗(yàn)讓學(xué)生學(xué)會(huì)掌握多表查詢的思想，使用UNION子句，以及熟悉統(tǒng)計(jì)函數(shù)的用法；讓學(xué)生學(xué)會(huì)使用group by子句，compute和compute by子句，以及掌握較為復(fù)雜的嵌套查詢的思想。二、 原理解析1、 多表查詢?cè)谝郧暗恼n程中，所使用的查詢局限于一張表格中，但在更多的情況下，需要對(duì)多張表格中的數(shù)據(jù)同時(shí)進(jìn)行查詢，這是可以把多張表格的名字全部填寫在FROM子句中。在使

13、用多表查詢時(shí)需要注意的是如何避免笛卡爾積的出現(xiàn)。2、 使用UNION子句如果有多個(gè)不同的查詢結(jié)果數(shù)據(jù)集合，但又希望將他們連接在一起組成一組數(shù)據(jù)。這組數(shù)據(jù)是這多個(gè)結(jié)果集合的邏輯聯(lián)合，在這種情況下，可以使用UNION子句。在UNION子句的使用中，有兩條基本原則：1)、每一個(gè)結(jié)果集的數(shù)據(jù)類型都必須相同，更確切地說是兼容；2)、每一個(gè)結(jié)果集中列的數(shù)量都必須相等，排列順序必須相互對(duì)應(yīng)。3、 使用統(tǒng)計(jì)函數(shù)為了有效的處理使用SQL查詢得到的數(shù)據(jù)集合，SQL Server提供了一系列統(tǒng)計(jì)函數(shù)。這些函數(shù)把存儲(chǔ)在數(shù)據(jù)庫中的數(shù)據(jù)描述為一個(gè)整體而不是一行行孤立的記錄。通過使用這些函數(shù)可以實(shí)現(xiàn)對(duì)數(shù)據(jù)集合匯總，求平均值

14、等各種運(yùn)算。4、 使用group by 子句在大多數(shù)情況下使用統(tǒng)計(jì)函數(shù)，返回的是所有行數(shù)據(jù)的統(tǒng)計(jì)結(jié)果。如果需要按某一列數(shù)據(jù)的值進(jìn)行分類，在分類的基礎(chǔ)上再進(jìn)行查詢，就需要使用group by子句了。需要注意的是在group by子句中不支持對(duì)列分配的別名也不支持任何使用了統(tǒng)計(jì)函數(shù)的集合列。另外，Select后面每一列數(shù)據(jù)除了出現(xiàn)在統(tǒng)計(jì)函數(shù)中的列以外都必須在group by子句中應(yīng)用。5、 使用compute 和compute by子句使用compute子句，允許同時(shí)觀察查詢所得的各列數(shù)據(jù)的細(xì)節(jié)以及統(tǒng)計(jì)各列數(shù)據(jù)所產(chǎn)生的總和。通過使用compute子句既可以計(jì)算數(shù)據(jù)分類后的和，也可以計(jì)算所有數(shù)據(jù)的總

15、和。需要注意的是，從返回的查詢結(jié)果來看，compute子句和group by子句非常類似。但是兩者之間存在著較大的區(qū)別，使用group by子句只能產(chǎn)生一個(gè)結(jié)果集合；使用compute可以返回多種結(jié)果集。6、 使用嵌套查詢?cè)谝郧暗膶?shí)驗(yàn)中，我們所使用的查詢都是單層查詢，但在實(shí)際應(yīng)用中經(jīng)常要應(yīng)用到嵌套查詢。嵌套查詢要求服務(wù)器在處理最終查詢工作之前先生成一個(gè)結(jié)果，然后根據(jù)當(dāng)前的查詢結(jié)果再進(jìn)一步繼續(xù)下面的查詢工作。但嵌套查詢返回的結(jié)果作為查詢條件等號(hào)右邊的值存在時(shí)之允許嵌套查詢返回一行結(jié)果，否則系統(tǒng)出錯(cuò)。三、 實(shí)驗(yàn)內(nèi)容1、 查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、郵政編碼等基本信息及其所選課程的成績(jī)情況；2、 查詢

16、學(xué)生信息數(shù)據(jù)表中所有學(xué)生的學(xué)號(hào)、姓名、院系名稱和院系編號(hào)；3、 查詢每門課程名稱及該門課的任課教師的姓名、編號(hào)；4、 在stud_info與stud_grade中按學(xué)號(hào)stud_id進(jìn)行等值連接，以查詢所有參加考試的基本信息和成績(jī)；5、 查詢所學(xué)專業(yè)為“計(jì)算機(jī)控制技術(shù)”或年齡為21歲的所有學(xué)生的姓名；6、 查詢計(jì)算機(jī)工程系全體教師的平均工資；7、 求計(jì)算機(jī)工程系教師的平均年齡；8、 求計(jì)算機(jī)工程系教師的總?cè)藬?shù)。9、 統(tǒng)計(jì)計(jì)算機(jī)工程系各個(gè)專業(yè)的學(xué)生的平均入學(xué)成績(jī)；10、 在學(xué)生成績(jī)表中統(tǒng)計(jì)各門課程的人數(shù)；11、 在教師信息表中，按職稱分組統(tǒng)計(jì)“教授”或“副教授”的工資總額；12、 按職稱統(tǒng)計(jì)各個(gè)

17、教研室的教師人數(shù)；13、 對(duì)teacher_info中職稱為“講師”的教師工資生成匯總行和明細(xì)行；14、 對(duì)teacher_info中職稱為“講師”或“助教”的教師工資，按照其職稱生成分組匯總行和明細(xì)行；15、 查詢工資高于“孫樂多”的所有老師的編號(hào)、姓名、性別及工資（用子查詢實(shí)現(xiàn)）；16、 在學(xué)生成績(jī)表中查詢課程類型為“考試”的學(xué)生的學(xué)號(hào)、姓名、成績(jī)（用子查詢實(shí)現(xiàn)）；17、 查詢課程號(hào)為“0401010106”的課程的成績(jī)?cè)?089分的學(xué)生的學(xué)號(hào)、姓名（用exists實(shí)現(xiàn)）。18、 完成習(xí)題中的相應(yīng)內(nèi)容。四、 實(shí)驗(yàn)步驟1、查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、郵政編碼等基本信息及其所選課程的成績(jī)情況：s

18、elect from where 2、查詢學(xué)生信息數(shù)據(jù)表中所有學(xué)生的學(xué)號(hào)、姓名、院系名稱和院系編號(hào)：select from where substring（ ）= 3、查詢每門課程名稱及該門課的任課教師的姓名、編號(hào)：select from where 4、 在Stud_info與stud_grade中按學(xué)號(hào)stud_id進(jìn)行等值連接，以查詢所有參加考試的基本信息和成績(jī)：select *from stud_info stud_gradeon order by stud_info.stud_id5、 查詢所學(xué)專業(yè)為“計(jì)算機(jī)控制技術(shù)”或年齡為21歲的所有學(xué)生的姓名：select stud_id, n

19、ame from where unionselect stud_id, name from stud_info where DATEDIFF）（ ）=216、 查詢計(jì)算機(jī)工程系全體教師的平均工資：select as 計(jì)算機(jī)工程系教師平均工資from teacher_info where left（ ）= （select deptcode from dept_code where deptname=計(jì)算機(jī)工程系）7、 求計(jì)算機(jī)工程系教師的平均年齡：select as 平均年齡from teacher_infowhere left（ ）=（select deptcode from dept_cod

20、e where deptname=計(jì)算機(jī)工程系）8、 求計(jì)算機(jī)工程系教師的總?cè)藬?shù)：select from teacher_infowhere left（ ）=（select deptcode from dept_code where deptname=計(jì)算機(jī)工程系）9、 統(tǒng)計(jì)計(jì)算機(jī)工程系各個(gè)專業(yè)的學(xué)生的平均入學(xué)成績(jī)：select from where substring（stud_id,5,2）10、 在學(xué)生成績(jī)表中統(tǒng)計(jì)各門課程的人數(shù)：select from 11、 在教師信息表中，按職稱分組統(tǒng)計(jì)“教授”或“副教授”的工資總額：select from 12、 按職稱統(tǒng)計(jì)各個(gè)教研室的教師人數(shù)：s

21、elect tech_title 職稱， from teacher_infogroup by 13、 對(duì)teacher_info中職稱為“講師”的教師工資生成匯總行和明細(xì)行：select tech_title,salaryfrom teacher_infowhere order by tech_title 14、 對(duì)teacher_info中職稱為“講師”或“助教”的教師工資，按照其職稱生成分組匯總行和明細(xì)行：select tech_title,salaryfrom teacher_infowhere order by tech_title 15、 查詢工資高于“孫樂多”的所有老師的編號(hào)、姓名

22、、性別及工資：select from teacher_infowhere 16、 在學(xué)生成績(jī)表中查詢課程類型為“考試”的學(xué)生的學(xué)號(hào)、姓名、成績(jī)：select from teacher_infowhere 17、 查詢課程號(hào)為“0401010106”的課程的成績(jī)?cè)?089分的學(xué)生的學(xué)號(hào)、姓名（用exists實(shí)現(xiàn)）：select from stud_infowhere exists 實(shí)驗(yàn)三：數(shù)據(jù)完整性測(cè)試一、實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生理解數(shù)據(jù)庫設(shè)計(jì)中的數(shù)據(jù)完整性概念，通過學(xué)習(xí)教程，掌握和熟悉實(shí)施數(shù)據(jù)完整性的途徑，本章實(shí)施數(shù)據(jù)完整性的途徑包括約束、規(guī)則、默認(rèn)值、標(biāo)識(shí)列。二、原理解析數(shù)據(jù)的完整性是指存儲(chǔ)在

23、數(shù)據(jù)庫中數(shù)據(jù)的正確性和相容性。設(shè)計(jì)數(shù)據(jù)庫完整性的目的是為了防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)，防止錯(cuò)誤信息的輸入和輸出。1、 使用約束實(shí)施數(shù)據(jù)完整性約束的用途是檢查輸入到表中的值的范圍，SQL Server提供的約束主要有：主鍵約束、外鍵約束、惟一約束、檢查約束、NULL約束、CASCADE級(jí)聯(lián)引用一致性約束等。2、 使用規(guī)則規(guī)則是一組使用T-SQL書寫的條件語句，它可以和列或者是用戶自定義數(shù)據(jù)類型捆綁在一起。當(dāng)用戶向綁定有規(guī)則的數(shù)據(jù)列上插入或修改值時(shí)，規(guī)則會(huì)檢測(cè)修改的完整性。3、 使用默認(rèn)值SQL Server提供的默認(rèn)值有助于處理用戶不含全部表列的INSERT操作。使用方法類似于規(guī)則。4、

24、使用標(biāo)識(shí)列定義為標(biāo)識(shí)列屬性的列不必在插入新行時(shí)為其賦值，因?yàn)榉?wù)器會(huì)自動(dòng)為新增加的行中的Identity列設(shè)置一個(gè)唯一的行序列號(hào)。三、 實(shí)驗(yàn)內(nèi)容1、 在企業(yè)管理中為表stud_info中的學(xué)生性別列（GENDER）創(chuàng)建一CHECK約束，使得該列的值只能是“男”或“女”；2、 使用T-SQL語句為stud_info創(chuàng)建一個(gè)名為“CK_stud_info”的CHECK約束；3、 使用T-SQL為表stud_info船艦一個(gè)約束名為“DE_gender”的DEFAULT約束，要求性別（gender）的默認(rèn)值為“男”；4、 使用T-SQL語句為表stud_info中的學(xué)號(hào)（stud_id）定義名為“P

25、K_XH”的主鍵約束；5、分別用企業(yè)管理器和T-SQL語句為表stud_grade和表lesson_info建立外鍵約束；6、用T-SQL語句為表stud_info的“電話號(hào)碼”列創(chuàng)建名為UN_telcode的UNIQUE約束；7、用T-SQL語句在數(shù)據(jù)庫student中創(chuàng)建默認(rèn)MR_GRADE，并將其綁定到表stud_info中的學(xué)生總分（mark）列上，從而實(shí)現(xiàn)每名學(xué)生的總分默認(rèn)值為560分；8、用T-SQL語句在數(shù)據(jù)庫student中創(chuàng)建一個(gè)規(guī)則GZ_GRADE，并將其綁定到表stud_grade中的學(xué)生成績(jī)（grade）列上，使得用戶輸入的成績(jī)?cè)?100的范圍內(nèi)，否則提示輸入無效。9、

26、完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)四：T-SQL編程結(jié)構(gòu)一、實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生掌握更復(fù)雜的T-SQL查詢的應(yīng)用規(guī)則，如需要循環(huán)、判斷才能表達(dá)清楚的查詢過程。二、原理解析1、批處理批處理是一個(gè)或多個(gè)Transact-SQL語句的集合，從應(yīng)用程序一次性發(fā)送到SQL Server并由SQL Server編譯成一個(gè)可執(zhí)行單元，此單元稱為執(zhí)行計(jì)劃。執(zhí)行計(jì)劃中的語句每次執(zhí)行一條。2、變量分局部變量和全局變量，局部變量以打頭，全局變量以打頭。局部變量用DECLARE語句聲明，用SET語句賦值，具體語法請(qǐng)參見幫助。3、流程控制語句vBEGINNEND語句塊 vIFElSE語句vCASE語句vWAITFOR語句vW

27、HILE語句vRETURN語句 三、實(shí)驗(yàn)內(nèi)容1、分析程序的運(yùn)行結(jié)果2、使用局部變量向表中插入數(shù)據(jù)3、ifelse語句的使用4、case語句的使用5、while語句的使用實(shí)驗(yàn)步驟1、 執(zhí)行下列語句，分析語句的執(zhí)行結(jié)果：use studentcreate view teacher_info_viewasselect teacher_id,name,tech_title,salary from teacher_infogoselect * from teacher_infogo2、 分析下列語句的執(zhí)行結(jié)果（局部變量的作用域）：declare stud_var intgoselect stud_var

28、=25goprint stud_vargo3、 使用局部變量，向表格stud_info插入一行數(shù)據(jù)（先聲明變量，再賦值，最后將這些變量的值插入對(duì)應(yīng)表格中），0401010705、王小明、1986-10-12、男、上海市楊浦區(qū)201800、560。4、 使用ifelse語句，從數(shù)據(jù)表stud_grade中讀出學(xué)生“陳紅”的成績(jī)，將百分制轉(zhuǎn)換成等級(jí)制，成績(jī)?cè)?0分到100分之間等級(jí)為A，80分以上為B，70分以上為C，60分以上為D，60分以下為E。5、 使用CASE語句實(shí)現(xiàn)從數(shù)據(jù)表stud_info中，選取stud_id、gender，如果gender為“男”則輸出“

29、M”，如果為“女”則輸出“F”。6、 使用while循環(huán)語句實(shí)現(xiàn)以下功能：求2300之間的所有素?cái)?shù)。7、7、從stud_grade表中查詢所有同學(xué)考試成績(jī)情況，凡成績(jī)?yōu)榭照咻敵觥拔纯肌?、小?0分輸出“不及格”、60分至70分輸出“及格”、70分至90分輸出“良好”、大于或等于90分輸出“優(yōu)秀”。8、 使用CASE語句，輸出教師基本信息表（teacher_info）中所有教師所在的年齡段（每6年劃分一個(gè)段），并說明對(duì)應(yīng)教師的職稱。9、 編寫計(jì)算n!（n=20）的T-SQL語句，并顯示計(jì)算結(jié)果。10、 完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)五：游標(biāo)和事務(wù)設(shè)計(jì)一、實(shí)驗(yàn)?zāi)康臄?shù)據(jù)庫游標(biāo)是類似于C語言指針的語言結(jié)構(gòu)

30、。游標(biāo)的功能比較復(fù)雜，要靈活使用游標(biāo)需要花費(fèi)較長(zhǎng)的時(shí)間練習(xí)和積累經(jīng)驗(yàn)。本次實(shí)驗(yàn)讓學(xué)生掌握游標(biāo)設(shè)計(jì)最基本和最常用的方法。SQL Server作為典型的關(guān)系數(shù)據(jù)庫，為事務(wù)控制提供了完善的編程結(jié)構(gòu)。通過實(shí)驗(yàn)掌握事務(wù)的各種特性。二、 原理解析1、游標(biāo)設(shè)計(jì)通常情況下，數(shù)據(jù)庫執(zhí)行的大多數(shù)SQL命令都是同時(shí)處理集合內(nèi)部的所有數(shù)據(jù)。但是，有時(shí)候用戶也需要對(duì)這些數(shù)據(jù)集合中的每一行進(jìn)行操作。在沒有游標(biāo)的情況下，這種工作不得不放到數(shù)據(jù)庫前端，用高級(jí)語言來實(shí)現(xiàn)，這將導(dǎo)致不必要的數(shù)據(jù)傳輸，從而延長(zhǎng)執(zhí)行的時(shí)間。通過使用游標(biāo)可以在服務(wù)器端有效的解決這個(gè)問題。游標(biāo)的使用一般可按順序分為如下幾個(gè)步驟： 聲明游標(biāo)語法：DECLA

31、RE 游標(biāo)名 INSENSITIVE SCOROLL CURSORFOR SELECT 語句 打開游標(biāo)語法：OPEN CURSOR_NAME 使用游標(biāo)取數(shù)或修改數(shù)據(jù)語法：FETCH COURSOR_NAME INTO VAR 關(guān)閉游標(biāo)語法：CLOSE CURSOR_NAME 釋放游標(biāo)語法：DEALLOCATE CURSOR_NAME2、 事務(wù)事務(wù)處理控制語句有以下4個(gè)： BEGIN TRAN TRAN_NAME COMMIT TRAN ROLLBACK TRAN SAVE TRAN三、實(shí)驗(yàn)內(nèi)容和步驟1、統(tǒng)計(jì)“多媒體技術(shù)”課程考試成績(jī)的各分?jǐn)?shù)段的分布情況，即100分的有己幾人，90100分有幾人

32、，8090有幾人，7080有幾人，6070有幾人，不及格有幾人，用游標(biāo)實(shí)現(xiàn)。2、運(yùn)用事務(wù)處理將student數(shù)據(jù)庫中課程信息表lesson_info中的多媒體技術(shù)課程編號(hào)course_id由0401010106改為0401010116。3、編寫一個(gè)事務(wù)控制程序，要求在事務(wù)中包含3個(gè)操作：第一個(gè)操作是在student數(shù)據(jù)庫的stud_info表中插入一條數(shù)據(jù)，并檢索插入是否成剛，然后 設(shè)置一個(gè)保存點(diǎn)，緊接著執(zhí)行第二個(gè)操作，刪除剛才插入的數(shù)據(jù)，并檢索刪除是否成功，然后回滾事務(wù)到保存點(diǎn)，最后執(zhí)行檢索操作，看插入的數(shù)據(jù)是否還存在。4、定義一個(gè)游標(biāo)，將教師表teacher中所有教師的姓名、 教師職稱顯示

33、出來。5、通過游標(biāo)將教師表teacher_info中記錄號(hào)為5的老師的職稱由改為“副教授”。6、使用游標(biāo)顯示stud_info表中所有學(xué)生的基本信息。7、通過游標(biāo)實(shí)現(xiàn)：對(duì)于stud_info表中的每個(gè)學(xué)生如果在stud_grade表中有相應(yīng)的選課記錄則刪除該生的選課記錄。8、通過游標(biāo)實(shí)現(xiàn)顯示stud_info表中所有男生的基本信息。9、通過游標(biāo)實(shí)現(xiàn)：統(tǒng)計(jì)學(xué)生人數(shù)。10、為stud_info表添加一“備注”字段，某學(xué)生如果已選修了課程，則將“備注”字段改為“已選”。11、用游標(biāo)實(shí)現(xiàn)在查詢出來的結(jié)果集中添加一個(gè)新列（有規(guī)律），即將stud_info表的STUD_ID、NAME列查詢出來到一個(gè)新表，

34、并且在新表中添加一列“序號(hào)”，用游標(biāo)實(shí)現(xiàn)。12、完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)六：SQL Server函數(shù)的使用一、實(shí)驗(yàn)?zāi)康臑榱耸褂脩魧?duì)數(shù)據(jù)庫進(jìn)行查詢和修改時(shí)更加方便，SQL Server在TSQL中提供了許多內(nèi)部函數(shù)以供使用。本次使用掌握其中某些函數(shù)的使用方法和意義。SQL Server還提供了用戶自定義函數(shù)，用戶可以按照自己的意愿創(chuàng)建函數(shù)。本次實(shí)驗(yàn)要求學(xué)生掌握用戶自定義函數(shù)的創(chuàng)建和使用。二、原理解析SQL Server函數(shù)分為：1、 數(shù)學(xué)函數(shù)2、 字符串函數(shù)3、 日期函數(shù)4、 系統(tǒng)函數(shù)5、 統(tǒng)計(jì)函數(shù)6、 用戶自定義函數(shù)其中用戶自定義函數(shù)又可分為標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù)。v標(biāo)量函數(shù)

35、：返回單個(gè)數(shù)據(jù)值。v內(nèi)嵌表值函數(shù)：返回值是一個(gè)記錄集合-表。在此函數(shù)中，return語句包含一條單獨(dú)的select語句。v多語句表值函數(shù)：返回值是由選擇的結(jié)果構(gòu)成的記錄集。19、 實(shí)驗(yàn)內(nèi)容1、創(chuàng)建一標(biāo)量函數(shù)，要求：每次輸入一個(gè)學(xué)號(hào)，計(jì)算該學(xué)生的所有課程的平均分，如果是85100分，返回“優(yōu)”；如果是7584分，返回“良”；如果是6574，返回“中”；如果是064，返回“差”。2、創(chuàng)建一內(nèi)嵌表值函數(shù)，要求：每次輸入一個(gè)學(xué)號(hào)，返回學(xué)生選修的課程名和成績(jī)。調(diào)用創(chuàng)建的函數(shù)查詢學(xué)號(hào)為“0401020201”學(xué)生所選修的課程名和成績(jī)。3、用多語句表值函數(shù)實(shí)現(xiàn)查詢某個(gè)學(xué)生的學(xué)號(hào)、姓名及選修門數(shù)。利用創(chuàng)建的函

36、數(shù)查詢學(xué)號(hào)為“0401020201”的學(xué)生的學(xué)號(hào)、姓名及選修門數(shù)。4、在存儲(chǔ)過程中調(diào)用用戶自定義函數(shù)，實(shí)現(xiàn)求兩個(gè)數(shù)的較大值。5、利用標(biāo)量函數(shù)和游標(biāo)修改表數(shù)據(jù)。6、創(chuàng)建一內(nèi)嵌表值函數(shù)來替代視圖，這個(gè)函數(shù)返回成績(jī)?cè)谒袑W(xué)生平均成績(jī)之上的學(xué)生姓名和成績(jī)。7、用多語句表值函數(shù)實(shí)現(xiàn)根據(jù)某學(xué)生的學(xué)號(hào)，查詢?cè)撋男彰斑x修門數(shù)。8、用內(nèi)嵌表值函數(shù)實(shí)現(xiàn)第7題。9、完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)步驟1、創(chuàng)建一標(biāo)量函數(shù)，要求：每次輸入一個(gè)學(xué)號(hào)，計(jì)算該學(xué)生的所有課程的平均分，如果是85100分，返回“優(yōu)”；如果是7584分，返回“良”；如果是6574，返回“中”；如果是064，返回“差”。 查詢學(xué)號(hào)為“04010202

37、01”的學(xué)生的平均分的等級(jí)。2、創(chuàng)建一內(nèi)嵌表值函數(shù)，要求：每次輸入一個(gè)學(xué)號(hào)，返回學(xué)生選修的課程名和成績(jī)。調(diào)用創(chuàng)建的函數(shù)查詢學(xué)號(hào)為“0401020201”學(xué)生所選修的課程名和成績(jī)。3、用多語句表值函數(shù)實(shí)現(xiàn)查詢某個(gè)學(xué)生的學(xué)號(hào)、姓名及選修門數(shù)。利用創(chuàng)建的函數(shù)查詢學(xué)號(hào)為“0401020201的學(xué)生的學(xué)號(hào)、姓名及選修門數(shù)。4、在存儲(chǔ)過程中調(diào)用用戶自定義函數(shù)，實(shí)現(xiàn)求兩個(gè)數(shù)的較大值。5、利用標(biāo)量函數(shù)和游標(biāo)修改表數(shù)據(jù)，要求：編寫一標(biāo)量函數(shù)以求得某學(xué)生的年齡，向stud_info表中添加一列“年齡”，利用游標(biāo)修改每個(gè)學(xué)生的年齡。6、創(chuàng)建一內(nèi)嵌表值函數(shù)來替代視圖，這個(gè)函數(shù)返回成績(jī)?cè)谒袑W(xué)生平均成績(jī)之上的學(xué)生姓名和

38、成績(jī)。7、用多語句表值函數(shù)實(shí)現(xiàn)根據(jù)某學(xué)生的學(xué)號(hào)，查詢?cè)撋男彰斑x修門數(shù)。8、用內(nèi)嵌表值函數(shù)實(shí)現(xiàn)第7題。實(shí)驗(yàn)七：存儲(chǔ)過程的創(chuàng)建和使用一、 實(shí)驗(yàn)?zāi)康谋敬螌?shí)驗(yàn)要求學(xué)生掌握用戶自定義存儲(chǔ)過程的創(chuàng)建和使用。二、 原理解析存儲(chǔ)過程是一組預(yù)先編譯好的TSQL代碼，可以作為一個(gè)獨(dú)立的數(shù)據(jù)庫對(duì)象，也可以作為一個(gè)單元被用戶的應(yīng)用程序調(diào)用。由于是已經(jīng)編譯好的代碼，所以執(zhí)行的時(shí)候不必再次進(jìn)行編譯，提高了效率。創(chuàng)建存儲(chǔ)過程語法：CREATE PROC PROC_NAMEWITHRECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONAS SQL_STATEMENT N三、 實(shí)驗(yàn)內(nèi)容

39、1、創(chuàng)建和執(zhí)行不帶參數(shù)的存儲(chǔ)過程。2、創(chuàng)建和執(zhí)行帶參數(shù)的存儲(chǔ)過程。3、創(chuàng)建和執(zhí)行帶輸出參數(shù)的存儲(chǔ)過程。四、 實(shí)驗(yàn)步驟1、針對(duì)教師基本信息表teacher_info，創(chuàng)建一個(gè)名稱為teacher_proc1的存儲(chǔ)過程，該存儲(chǔ)過程的功能是從數(shù)據(jù)表teacher_info中查詢所有男教師的信息。2、創(chuàng)建一個(gè)名稱為student_proc1的存儲(chǔ)過程，該存儲(chǔ)過程的功能是查詢學(xué)生的姓名、所選修的課程名以及成績(jī)。3、針對(duì)教師基本信息表teacher_info，創(chuàng)建一個(gè)名為teacher_proc2的存儲(chǔ)過程，執(zhí)行該存儲(chǔ)過程將向數(shù)據(jù)表teacher_info中插入一條記錄，新記錄的值由參數(shù)提供。3、針對(duì)教師

40、基本信息表teacher_info，創(chuàng)建一個(gè)名為teacher_proc3的存儲(chǔ)過程，執(zhí)行該存儲(chǔ)過程將向數(shù)據(jù)表teacher_info中插入一條記錄，新記錄的值由參數(shù)提供，如果未提供職稱tech_title的值，則由參數(shù)的默認(rèn)值提供。4、在數(shù)據(jù)庫student上創(chuàng)建一個(gè)名為stud_proc2的存儲(chǔ)過程，其功能是輸入兩個(gè)日期型數(shù)據(jù)，并使用輸出參數(shù)返回這兩個(gè)出生日期之間的所有學(xué)生人數(shù)。調(diào)用該存儲(chǔ)過程返回出生日期在1986年1月1日與1986年12月31日之間的學(xué)生記錄的條數(shù)。use studentgo 5、存儲(chǔ)過程名為proc1，要求實(shí)現(xiàn)如下功能：根據(jù)學(xué)生學(xué)號(hào)，查詢?cè)搶W(xué)生的選課情況，其中包括該學(xué)

41、生學(xué)號(hào)、姓名、課程號(hào)、課程名、成績(jī)等。6、定義存儲(chǔ)過程proc2，要求實(shí)現(xiàn)輸入學(xué)生學(xué)號(hào)，根據(jù)該學(xué)生所選課程的平均成績(jī)顯示提示信息，即如果平均成績(jī)?cè)?0分以上，顯示“此學(xué)生綜合成績(jī)合格！”，否則顯示“此學(xué)生綜合成績(jī)不合格！”。7、完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)八：觸發(fā)器的創(chuàng)建一、實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生掌握觸發(fā)器的創(chuàng)建和使用，理解如何使用觸發(fā)器實(shí)現(xiàn)更復(fù)雜的數(shù)據(jù)約束。二、原理解析觸發(fā)器是一種特殊的存儲(chǔ)過程，它與表格緊密相連，可以看作是表格定義的一部分。當(dāng)用戶修改指定表或視圖中的數(shù)據(jù)時(shí)，觸發(fā)器將會(huì)自動(dòng)執(zhí)行。觸發(fā)器基于一個(gè)表創(chuàng)建，但是可以針對(duì)多個(gè)表進(jìn)行操作。創(chuàng)建觸發(fā)器語法：CREATE TRIGGER TR

42、IG_NAMEON TABLE|VIEWWITH ENCRYPTIONFOR|AFTER|INSTEAD OFNOT FOR REPLICATIONASSQL_STATEMENTN RETURN INTEGER_EXP三、實(shí)驗(yàn)內(nèi)容1、創(chuàng)建insert觸發(fā)器。2、創(chuàng)建update觸發(fā)器。3、 創(chuàng)建delete觸發(fā)器。4、 創(chuàng)建instead of觸發(fā)器。四、實(shí)驗(yàn)步驟1、編寫一個(gè)（teacher_insert）觸發(fā)器，當(dāng)向teacher_info表中插入一條記錄時(shí)，激活觸發(fā)器，檢查記錄的course_id是否存在于lesson_info表中，若不存在，則取消插入。use studentgo as

43、if begin end2、編寫一個(gè)（teacher_insert1）觸發(fā)器，當(dāng)向teacher_info表中插入一條記錄時(shí)，激活觸發(fā)器，檢查記錄的course_id是否存在于lesson_info表中，若不存在，則將相應(yīng)的課程信息在lesson_info表中插入。use studentgo as if begin declare course_id char(10) insert into lesson_info values( ,數(shù)據(jù)庫開發(fā)技術(shù),考試,3,60) end3、編寫一個(gè)（teacher_update）觸發(fā)器，當(dāng)修改lesson_info表的course_id列值時(shí)，該列在tea

44、cher_info表中的對(duì)應(yīng)值也作相應(yīng)的修改。use studentgo as begin select course_id update teacher_info from teacher_info,deleted where end 4、編寫一個(gè)（teacher_update1）觸發(fā)器，當(dāng)修改lesson_info表的course_id列值時(shí)，該列在teacher_info表中若有相應(yīng)的值，則拒絕修改。use studentgo as if begin end use studentgo5、編寫一個(gè)（teacher_deletee）觸發(fā)器，當(dāng)刪除lesson_info表中的記錄時(shí)，也將同時(shí)

45、刪除該記錄course_id列值在teacher_info表中的對(duì)應(yīng)記錄。use studentgo as from deleted,teacher_info use studentgo6、編寫一個(gè)（teacher_delete1）觸發(fā)器，當(dāng)刪除lesson_info表的course_id列值時(shí)，該列在teacher_info表中若有相應(yīng)的值，則拒絕刪除。use studentgo as if begin end7、編寫一個(gè)（teacher_trigger）觸發(fā)器，當(dāng)向teacher_info表中的插入、修改、刪除記錄時(shí)，發(fā)出報(bào)警語句，并取消相應(yīng)的操作。 as 8、完成習(xí)題中的相應(yīng)內(nèi)容。實(shí)驗(yàn)九

46、：數(shù)據(jù)庫應(yīng)用系統(tǒng)分析與設(shè)計(jì)一、實(shí)驗(yàn)?zāi)康耐ㄟ^實(shí)驗(yàn)使學(xué)生掌握具體數(shù)據(jù)庫應(yīng)用系統(tǒng)的分析和設(shè)計(jì)步驟，完成數(shù)據(jù)庫系統(tǒng)的需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、物理結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫的運(yùn)行實(shí)施以及維護(hù)。二、原理解析數(shù)據(jù)庫系統(tǒng)的設(shè)計(jì)步驟主要包括需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、物理結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫的運(yùn)行實(shí)施以及維護(hù)等六個(gè)步驟，其中需求分析階段的主要工具是數(shù)據(jù)流程圖，產(chǎn)生的文檔資料是用戶需求說明書；概念結(jié)構(gòu)設(shè)計(jì)的主要工具是E-R圖；邏輯結(jié)構(gòu)設(shè)計(jì)的主要任務(wù)是將概念結(jié)構(gòu)設(shè)計(jì)階段的E-R圖轉(zhuǎn)換成關(guān)系數(shù)據(jù)模型，本實(shí)驗(yàn)主要的側(cè)重點(diǎn)是需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)。三、實(shí)驗(yàn)內(nèi)容每個(gè)同學(xué)根據(jù)選題原則選擇自己的課題，完

47、成具體數(shù)據(jù)庫應(yīng)用系統(tǒng)的設(shè)計(jì)?？晒┻x擇的課題如下：一、 超市管理系統(tǒng)（一）系統(tǒng)的功能需求1、應(yīng)超市需要人員進(jìn)行各個(gè)方面的管理，如采購員、導(dǎo)購員等，這需要建立一個(gè)數(shù)據(jù)庫來管理每個(gè)工作人員的信息，如用戶添加、刪除、修改密碼等，這有助于清晰地管理每個(gè)工作人員的信息。因權(quán)限不同，各個(gè)用戶進(jìn)入的界面不同，他們只能進(jìn)行權(quán)限之內(nèi)的操作。所有的用戶都可以在密碼更改中更改密碼。2、按照超市管理人員的操作習(xí)慣，可以對(duì)倉庫、商品類別、供貨商和商品等基本信息進(jìn)行增、刪、改和查詢操作。3、采購、入庫或銷售商品時(shí)，用戶要能夠?qū)M(jìn)貨單、入庫單或銷售單進(jìn)行核對(duì)審查，并進(jìn)行登記、修改和刪除，用戶要獲得進(jìn)貨、銷售、入庫信息時(shí)，要對(duì)

48、進(jìn)貨情況、銷售情況、庫存情況等進(jìn)行查詢。4、倉庫管理員根據(jù)每一次的入庫情況，自動(dòng)增加商品數(shù)量，并將數(shù)據(jù)保存到數(shù)據(jù)庫表中；導(dǎo)購員根據(jù)每一次的銷售情況，在銷售商品時(shí)自動(dòng)減少商品數(shù)量，并將數(shù)據(jù)保存到數(shù)據(jù)庫表中。5、采購員采購某種商品后，自動(dòng)把收款情況保存到收款單中，銷售員銷售某種商品后，自動(dòng)把付款情況保存到付款單中，系統(tǒng)應(yīng)該可以隨時(shí)查詢商品的收、付款情況。（二）系統(tǒng)的數(shù)據(jù)需求1、商品按類別和名稱進(jìn)行管理。2、必須記錄商品的基本信息，比如規(guī)格和生產(chǎn)企業(yè)等。3、需要記錄商品的供應(yīng)商信息。4、供應(yīng)商的聯(lián)系方式非常重要。5、在采購信息中需要記錄采購的數(shù)量和單價(jià)等。6、訂貨單、銷售單和入庫單必須記錄相應(yīng)的經(jīng)手人和開具時(shí)間。二、進(jìn)銷存管理系統(tǒng)（一）系統(tǒng)的功能需求進(jìn)銷存管理系統(tǒng)主要涉及到商品的采購、銷售、庫存及報(bào)損等方面，具體地講，包括：1、 基本信息的維護(hù)l 商品基本信息管理：包括商品基本信息的增加、修改和刪除等。l 商品記錄的查詢、統(tǒng)計(jì)和打印等。l 供貨商信息管理：包括供貨商信息的增加、修改和刪除等。l 供應(yīng)商記錄的查詢統(tǒng)計(jì)和打印等。2、 采購管理l 采購信息管理：商品采購信息包括商品進(jìn)貨數(shù)量、單價(jià)、供貨商等。商品進(jìn)貨管理功能完成進(jìn)貨信息登記、修改和刪除等。l 統(tǒng)計(jì)查詢：可以根據(jù)各種條件來查詢統(tǒng)計(jì)商品的采購情況。l 更新庫存：采購信息的變動(dòng)直接關(guān)系到商品庫