《數(shù)據(jù)庫開發(fā)技術(shù)》實驗指導(dǎo)書(計本10)-唐軍芳

1、浙江樹人大學(xué)信息科技學(xué)院實驗指導(dǎo)書適合專業(yè)：計算機科學(xué)與技術(shù)本科專業(yè)編寫部門：電子商務(wù)教研室編寫日期：2012.09T-SQLT-SQLServer. .實驗一：T-SQL 高級查詢一、 實驗?zāi)康?用 和二、 原理解析1、 多表查詢 2、 使用 UNION子句 子3、 使用統(tǒng)計函數(shù) 4、 使用 group by 子句 5、 使用 compute 和 compute by 子句 1 6、 使用嵌套查詢?nèi)?實驗內(nèi)容、在與、 、 、 、 對、 對、 、 現(xiàn)、 分的學(xué)生的學(xué)號、姓名（用、 2四、 實驗步驟（=、 在與 * 、 、 3（= ）、 （= ）、 （= ）、 ）、4、對、對 、5、 6實驗二

2、：數(shù)據(jù)完整性測試一、實驗?zāi)康亩?、原理解?、 使用約束實施數(shù)據(jù)完整性2、 使用規(guī)則3、 使用默認值4、 使用標識列三、 實驗內(nèi)容、 、 、 、 的7用T-SQL語句在數(shù)據(jù)庫student中創(chuàng)建一個規(guī)則GZ_GRADE8實驗三：T-SQL 編程結(jié)構(gòu)一、實驗?zāi)康耐ㄟ^實驗使學(xué)生掌握更復(fù)雜的 T-SQL 查詢的應(yīng)用規(guī)則，如需要循環(huán)、判斷才能表達清楚的查詢過程。二、原理解析1、批處理批處理是一個或多個 Transact-SQL語句的集合，從應(yīng)用程序一次性發(fā)送到SQL Server并由 SQL Server編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條。2、變量打頭。局部變量用 D

3、ECLARE語句聲明，用 SET 語句賦值，具體語法請參見幫助。3、流程控制語句語句塊語句語句語句語句語句三、實驗內(nèi)容1、分析程序的運行結(jié)果2、使用局部變量向表中插入數(shù)據(jù)3、ifelse語句的使用4、case語句的使用5、while語句的使用實驗步驟1、執(zhí)行下列語句，分析語句的執(zhí)行結(jié)果：9use studentcreate view teacher_info_viewasselect teacher_id,name,tech_title,salary from teacher_infogoselect * from teacher_infogo2、分析下列語句的執(zhí)行結(jié)果（局部變量的作用域）：d

4、eclare stud_var intgoselect stud_var=25goprint stud_vargo3、使用局部變量，向表格stud_info 插入一行數(shù)據(jù)（先聲明變量，再賦值，最后1986-10-12男上021-5604、使用 ifelse語句，從數(shù)據(jù)表stud_grade中讀出學(xué)生“陳紅”的成績，將百分制轉(zhuǎn)換成等級制，成績在 90 分到 100 分之間等級為 ，80 分以上為 ，70分以上為 C，60 分以上為 ，60 分以下為 E。5、使用 CASE 語句實現(xiàn)從數(shù)據(jù)表 stud_info stud_id gender為男則輸出“M”，如果為女則輸出“F”。6、使用 whil

5、e循環(huán)語句實現(xiàn)以下功能：求 2300 之間的所有素數(shù)。7 stud_grade 小于 60 分輸出不及格、60 分至 70 分輸出“及格、70 分至 90 分輸出良好、大于或等于 90 分輸出優(yōu)秀。8、 使用 CASE 語句，輸出教師基本信息表（teacher_info）中所有教師所在的年齡段（每 6 9、10、10實驗四：游標和事務(wù)設(shè)計一、實驗?zāi)康臄?shù)據(jù)庫游標是類似于 C基本和最常用的方法。SQL Server 作為典型的關(guān)系數(shù)據(jù)庫，為事務(wù)控制提供了完善的編程結(jié)構(gòu)。通過實驗掌握事務(wù)的各種特性。二、 原理解析1、游標設(shè)計 SQL命令都是同時處理集合內(nèi)部的所有數(shù)這個問題。游標的使用一般可按順序分為

6、如下幾個步驟： 聲明游標語法：DECLARE 游標名 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ù)段的分布情況，

7、即 100 分的有己幾人，90100 分有幾人，8090 有幾人，7080 有幾人，6070 有幾人，不及格有幾人，用游標實現(xiàn)。2、運用事務(wù)處理將 student數(shù)據(jù)庫中課程信息表 lesson_info中的多媒體技術(shù)課程編號 course_id由改為。3 3student數(shù)據(jù)庫的 stud_info設(shè)置功，然后回滾事務(wù)到保存點，最后執(zhí)行檢索操作，看插入的數(shù)據(jù)是否還存在。定義一個游標，將教師表teacher中所有教師的姓名、 教師職稱顯示出來。5、通過游標將教師表teacher_info 中記錄號為5 的老師的職稱由改為副教授。6、使用游標顯示 stud_info表中所有學(xué)生的基本信息。7 s

8、tud_info表中的每個學(xué)生如果在 stud_grade表中有相應(yīng)的選課記錄則刪除該生的選課記錄。8、通過游標實現(xiàn)顯示 stud_info表中所有男生的基本信息。9、通過游標實現(xiàn)：統(tǒng)計學(xué)生人數(shù)。10、為stud_info表添加一“備注”字段，某學(xué)生如果已選修了課程，則將“備11 stud_info表的 用游標實現(xiàn)。12、12實驗五：SQL Server函數(shù)的使用一、實驗?zāi)康臑榱耸褂脩魧?shù)據(jù)庫進行查詢和修改時更加方便，SQL Server在 TSQL 中提供了許多內(nèi)部函數(shù)以供使用。本次使用掌握其中某些函數(shù)的使用方法和意義。SQL Server還提供了用戶自定義函數(shù)，用戶可以按照自己的意愿創(chuàng)建函

9、數(shù)。本次實驗要求學(xué)生掌握用戶自定義函數(shù)的創(chuàng)建和使用。二、原理解析SQL Server函數(shù)分為：1、數(shù)學(xué)函數(shù)2、字符串函數(shù)3、日期函數(shù)4、系統(tǒng)函數(shù)5、統(tǒng)計函數(shù)6、用戶自定義函數(shù)其中用戶自定義函數(shù)又可分為標量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù)。標量函數(shù)：返回單個數(shù)據(jù)值。內(nèi)嵌表值函數(shù)：返回值是一個記錄集合表。在此函數(shù)中，return 語句包含一條單獨的 select語句。多語句表值函數(shù)：返回值是由選擇的結(jié)果構(gòu)成的記錄集。實驗內(nèi)容1、創(chuàng)建一標量函數(shù)，要求：每次輸入一個學(xué)號，計算該學(xué)生的所有課程的平均分，如果是 85100 分，返回“優(yōu)”；如果是 7584 分，返回“良”；如果是6574，返回“中”；如

10、果是 064，返回“差”。2、創(chuàng)建一內(nèi)嵌表值函數(shù)，要求：每次輸入一個學(xué)號，返回學(xué)生選修的課程名和成績。調(diào)用創(chuàng)建的函數(shù)查詢學(xué)號為“”學(xué)生所選修的課程名和成績。3、用多語句表值函數(shù)實現(xiàn)查詢某個學(xué)生的學(xué)號、姓名及選修門數(shù)。利用創(chuàng)建的函數(shù)查詢學(xué)號為“”的學(xué)生的學(xué)號、姓名及選修門數(shù)。4、在存儲過程中調(diào)用用戶自定義函數(shù)，實現(xiàn)求兩個數(shù)的較大值。135、利用標量函數(shù)和游標修改表數(shù)據(jù)。6、創(chuàng)建一內(nèi)嵌表值函數(shù)來替代視圖，這個函數(shù)返回成績在所有學(xué)生平均成績之上的學(xué)生姓名和成績。7、用多語句表值函數(shù)實現(xiàn)根據(jù)某學(xué)生的學(xué)號，查詢該生的姓名及選修門數(shù)。8、用內(nèi)嵌表值函數(shù)實現(xiàn)第 7 題。9、實驗步驟1、創(chuàng)建一標量函數(shù)，要求：

11、每次輸入一個學(xué)號，計算該學(xué)生的所有課程的平均分，如果是 85100 分，返回“優(yōu)”；如果是 7584 分，返回“良”；如果是6574，返回“中”；如果是 064，返回“差”。 查詢學(xué)號為“”的學(xué)生的平均分的等級。2、創(chuàng)建一內(nèi)嵌表值函數(shù)，要求：每次輸入一個學(xué)號，返回學(xué)生選修的課程名和成績。調(diào)用創(chuàng)建的函數(shù)查詢學(xué)號為“”學(xué)生所選修的課程名和成績。3、用多語句表值函數(shù)實現(xiàn)查詢某個學(xué)生的學(xué)號、姓名及選修門數(shù)。利用創(chuàng)建的函數(shù)查詢學(xué)號為“”的學(xué)生的學(xué)號、姓名及選修門數(shù)。4、在存儲過程中調(diào)用用戶自定義函數(shù)，實現(xiàn)求兩個數(shù)的較大值。5、利用標量函數(shù)和游標修改表數(shù)據(jù)，要求：編寫一標量函數(shù)以求得某學(xué)生的年齡，向 st

12、ud_info 表中添加一列“年齡”，利用游標修改每個學(xué)生的年齡。6、創(chuàng)建一內(nèi)嵌表值函數(shù)來替代視圖，這個函數(shù)返回成績在所有學(xué)生平均成績之上的學(xué)生姓名和成績。7、用多語句表值函數(shù)實現(xiàn)根據(jù)某學(xué)生的學(xué)號，查詢該生的姓名及選修門數(shù)。8、用內(nèi)嵌表值函數(shù)實現(xiàn)第 7 題。14實驗六：存儲過程的創(chuàng)建和使用一、 實驗?zāi)康谋敬螌嶒炓髮W(xué)生掌握用戶自定義存儲過程的創(chuàng)建和使用。二、 原理解析存儲過程是一組預(yù)先編譯好的 TSQL代碼，可以作為一個獨立的數(shù)據(jù)庫對以執(zhí)行的時候不必再次進行編譯，提高了效率。創(chuàng)建存儲過程語法：CREATE PROC PROC_NAMEWITHRECOMPILE | ENCRYPTION | R

13、ECOMPILE, ENCRYPTIONAS SQL_STATEMENT N實驗內(nèi)容1、創(chuàng)建和執(zhí)行不帶參數(shù)的存儲過程。2、創(chuàng)建和執(zhí)行帶參數(shù)的存儲過程。創(chuàng)建和執(zhí)行帶輸出參數(shù)的存儲過程。實驗步驟1、針對教師基本信息表teacher_info，創(chuàng)建一個名稱為teacher_proc1的存儲過程，該存儲過程的功能是從數(shù)據(jù)表 teacher_info中查詢所有男教師的信息。2、創(chuàng)建一個名稱為 student_proc1的存儲過程，該存儲過程的功能是查詢學(xué)生3、針對教師基本信息表teacher_info，創(chuàng)建一個名為 teacher_proc2的存儲過程，執(zhí)行該存儲過程將向數(shù)據(jù)表teacher_info中

14、插入一條記錄，新記錄的值由參數(shù)提供。針對教師基本信息表teacher_info teacher_proc3數(shù)據(jù)表 teacher_info中插入一條記錄，新記錄的值由參數(shù)提供，如果未提供職稱 tech_title4 student上創(chuàng)建一個15名為 stud_proc2的存儲過程，其功能是輸入兩個日期型數(shù)據(jù)，并使用輸出參數(shù)返回這兩個出生日期之間的所有學(xué)生人數(shù)。返回出生日期在 1986年 1月 1日與 1986年 12月 31日之間的學(xué)生記錄的條數(shù)。5、存儲過程名為 proc1，要求實現(xiàn)如下功能：根據(jù)學(xué)生學(xué)號，查詢該學(xué)生的選課情況，其中包括該學(xué)生學(xué)號、姓名、課程號、課程名、成績等。6、 實驗七：

15、觸發(fā)器的創(chuàng)建一、實驗?zāi)康牡臄?shù)據(jù)約束。二、原理解析于一個表創(chuàng)建，但是可以針對多個表進行操作。創(chuàng)建觸發(fā)器語法：CREATE TRIGGER TRIG_NAMEON TABLE|VIEWWITH ENCRYPTIONFOR|AFTER|INSTEAD OFNOT FOR REPLICATIONASSQL_STATEMENTN RETURN INTEGER_EXP16三、實驗內(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表中插

16、入一條記錄 course_id是否存在于 lesson_info在，則取消插入。use studentgoasifbeginend2、編寫一個（teacher_insert1）觸發(fā)器，當向 teacher_info表中插入一條記 course_id是否存在于 lesson_info存在，則將相應(yīng)的課程信息在 lesson_info表中插入。use studentgoas17ifbegindeclare course_id char(10)insert into lesson_infovalues( ,數(shù)據(jù)庫開發(fā)技術(shù),考試,3,60)end3、編寫一個（teacher_update）觸發(fā)器，當修

17、改 lesson_info表的 course_id列值時，該列在 teacher_info表中的對應(yīng)值也作相應(yīng)的修改。use studentgoasbeginselect course_idupdate teacher_infofrom teacher_info,deletedwhereend4、編寫一個（teacher_update1）觸發(fā)器，當修改lesson_info表的 course_id列值時，該列在 teacher_info表中若有相應(yīng)的值，則拒絕修改。use studentgo18asifbeginend5、編寫一個（teacher_deletee）觸發(fā)器，當刪除 lesson_info表中的記錄時，也將同時刪除該記錄 course_id