《數(shù)據(jù)庫開發(fā)技術(shù)實驗》實驗指導書

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

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

3、用戶歸入某一角色，這樣只要角色具有了一定的權(quán)限，用戶相應地從該角色繼承了相應的權(quán)限，角色的提出有利于權(quán)限的管理。角色可分為固定服務(wù)器角色和數(shù)據(jù)庫角色。與角色有關(guān)的存儲過程：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語句三、 實驗內(nèi)容（1） 登錄帳號的管理。（2） 數(shù)據(jù)庫用戶帳號的管理。（3） 角色管理。（4） 許可管理。四、 實驗步驟（1）創(chuàng)建SQL Server登錄帳號aa，賦予其系統(tǒng)管理員角色。（2）賦予windows登錄帳號bb在數(shù)據(jù)庫Student上的db_owner訪問權(quán)限。（3）創(chuàng)建SQL Server登錄帳號LoginT，其在Student數(shù)據(jù)庫上的對應用戶為userT。（4）刪除userT所對應的登錄帳號loginT。（5）刪除Student數(shù)據(jù)庫用戶帳號userT。（6）在服務(wù)器上創(chuàng)建一Windows用戶cc，然后將cc授權(quán)登錄和訪問SQL Server系

5、統(tǒng)。依次利用此賬號作測試登錄。測試成功后運行腳本：sp_revokelogin cc然后以cc登錄SQL Server系統(tǒng)，能否成功。運行腳本：sp_addlogin cc,cc Gosp_denylogin cc Go (7) 利用系統(tǒng)存儲過程為數(shù)據(jù)庫Student創(chuàng)建一數(shù)據(jù)庫角色myrole，并創(chuàng)建一個數(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)建測試用表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-添加測試數(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登錄賬號、數(shù)據(jù)庫用戶賬號和角色。-創(chuàng)建登錄賬號loginA和loginB，口令分別為a和bsp_addlogin loginA,aGosp_addlogin loginB,bGo-創(chuàng)建testDB數(shù)據(jù)庫的userA和userB用戶賬號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查詢分析器連接，重新以賬號loginA登錄，執(zhí)行下列腳本,查看結(jié)果（系統(tǒng)提示無Select權(quán)限）Use myTestDBGoSelect * from stud01Go3) 斷開SQL Server查詢分析器連接，以賬號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查詢分析器連接，重新以賬號loginA登錄，重新執(zhí)行腳本,查看結(jié)果（可檢

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

27、HILE語句vRETURN語句 三、實驗內(nèi)容1、分析程序的運行結(jié)果2、使用局部變量向表中插入數(shù)據(jù)3、ifelse語句的使用4、case語句的使用5、while語句的使用實驗步驟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ù)（先聲明變量，再賦值，最后將這些變量的值插入對應表格中），0401010705、王小明、1986-10-12、男、上海市楊浦區(qū)201800、560。4、 使用ifelse語句，從數(shù)據(jù)表stud_grade中讀出學生“陳紅”的成績，將百分制轉(zhuǎn)換成等級制，成績在90分到100分之間等級為A，80分以上為B，70分以上為C，60分以上為D，60分以下為E。5、 使用CASE語句實現(xiàn)從數(shù)據(jù)表stud_info中，選取stud_id、gender，如果gender為“男”則輸出“

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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