SQL SERVER數(shù)據(jù)庫(kù)實(shí)驗(yàn)

1、SQL SERVER 數(shù)據(jù)庫(kù)應(yīng)用技術(shù)實(shí)驗(yàn)報(bào)告選課序號(hào)： 班 級(jí)： 學(xué) 號(hào)： 姓 名： 指導(dǎo)教師： 成 績(jī)： 目 錄1.實(shí)驗(yàn)?zāi)康?2.實(shí)驗(yàn)內(nèi)容12.1創(chuàng)建數(shù)據(jù)庫(kù)OrderDB（雜志訂購(gòu)數(shù)據(jù)庫(kù)）12.2 T-SQL查詢12.3存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程23.實(shí)驗(yàn)步驟23.1創(chuàng)建數(shù)據(jù)庫(kù)OrderDB（雜志訂購(gòu)數(shù)據(jù)庫(kù)）23.2 T-SQL查詢53.3存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程54.總結(jié)與體會(huì)81.實(shí)驗(yàn)?zāi)康模?） 創(chuàng)建與使用數(shù)據(jù)庫(kù)。了解數(shù)據(jù)庫(kù)及其各類邏輯對(duì)象、數(shù)據(jù)庫(kù)的文件與文件組的概念；實(shí)踐數(shù)據(jù)庫(kù)的設(shè)計(jì)、創(chuàng)建、查看和維護(hù)等的操作，。（2） T-SQL查詢。掌握SELECT查詢命令，INSER

2、T、UPDATE和DELETE等更新命令，及T-SQL對(duì)查詢與更新命令的增強(qiáng)功能操作。（3） 自定義函數(shù)、存儲(chǔ)過程與觸發(fā)器。實(shí)踐練習(xí)自定義函數(shù)、存儲(chǔ)過程和觸發(fā)器的使用方法。2.實(shí)驗(yàn)內(nèi)容2.1創(chuàng)建數(shù)據(jù)庫(kù)OrderDB（雜志訂購(gòu)數(shù)據(jù)庫(kù)）以下各表中的代碼或編號(hào)列為char(6)，名稱或類別列為varchar(20)，單價(jià)或金額列為numeric(10,2)，數(shù)量列為int，訂購(gòu)日期為日期類型datetime，所在城市列為varchar(16)。（1） 雜志表Magazine(雜志代碼Mno，雜志名稱Mname，雜志類別Mtype，出版商所在城市Mcity，進(jìn)貨單價(jià)Miprice，訂購(gòu)單價(jià)Mopric

3、e)，其中，訂購(gòu)價(jià)格>進(jìn)貨價(jià)格，雜志類別：文學(xué)類、歷史類、科技類。主鍵為（雜志代碼Mno）。（2） 客戶（雜志的訂購(gòu)單位信息）表Customer(客戶代碼Cno，客戶名稱Cname，客戶所在城市Ccity，上級(jí)主管單位代碼Sno，客戶類別Ctype)，客戶（單位）類別：政府單位、事業(yè)單位、企業(yè)單位。主鍵為（客戶代碼Cno）。（3） 雜志訂購(gòu)情況主表OrderH（訂單編號(hào)Ono，客戶代碼Cno，訂購(gòu)日期Odate，訂單貨款金額合計(jì)OMsum，訂單盈利金額合計(jì)OPsum)，主鍵為訂單編號(hào)Ono。（4） 雜志訂購(gòu)情況明細(xì)表OrderList（訂單編號(hào)Ono，雜志代碼Mno，訂購(gòu)數(shù)量Onum，進(jìn)

4、貨單價(jià)Miprice，訂購(gòu)單價(jià)Moprice，訂購(gòu)金額Omoney，盈利金額Oprofit)，主鍵為(訂單編號(hào)Ono，雜志代碼Mno)，訂購(gòu)金額=訂購(gòu)單價(jià)×訂購(gòu)數(shù)量，盈利金額=（訂購(gòu)單價(jià)-進(jìn)貨單價(jià)）×訂購(gòu)數(shù)量。2.2 T-SQL查詢實(shí)現(xiàn)如下查詢功能前，請(qǐng)向所有數(shù)據(jù)表添加足夠多的演示數(shù)據(jù)。求年份的函數(shù)為year( )，返回類型為int，年份=year（訂購(gòu)日期Odate）。（1） 使用WITH公用表表達(dá)式查看客戶名稱為珠江航運(yùn)公司在廣州市的所有上級(jí)主管單位代碼和單位名稱。（2） 查詢客戶名稱為天空網(wǎng)絡(luò)公司在2011年所訂購(gòu)的大于其最小訂購(gòu)數(shù)量的2倍的雜志代碼、雜志名稱及訂購(gòu)數(shù)

5、量。（3） 使用COMPUTE BY、COMPUTE，求客戶類別為事業(yè)單位在2011年訂購(gòu)的雜志類別為歷史類的客戶代碼、客戶名稱、訂購(gòu)數(shù)量、訂購(gòu)金額，要求同時(shí)輸出按客戶計(jì)算的訂購(gòu)數(shù)量和訂購(gòu)金額的合計(jì)、所有客戶的訂購(gòu)數(shù)量和訂購(gòu)金額的總計(jì)。（4） 使用TOP和查詢結(jié)果集別名表達(dá)式，查詢雜志名稱為讀者、2011年訂購(gòu)數(shù)量為第4-10名的客戶代碼、客戶名稱和訂購(gòu)數(shù)量（設(shè)讀者的訂購(gòu)客戶數(shù)>=10）。 （5） 用游標(biāo)編程，求大連市的雜志在2013年的平均訂購(gòu)數(shù)量和總訂購(gòu)數(shù)量的功能，不能用COUNT、AVG和SUM函數(shù)。2.3存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程（1） 設(shè)計(jì)自定義函數(shù)fGetProfit，

6、實(shí)現(xiàn)統(tǒng)計(jì)某年份給定雜志類別的盈利金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和雜志類別，輸出參數(shù)是盈利金額合計(jì)。（2） 設(shè)計(jì)存儲(chǔ)過程pGetMoney，實(shí)現(xiàn)統(tǒng)計(jì)某年份給定客戶類別的訂購(gòu)金額合計(jì)的功能，輸入?yún)?shù)是統(tǒng)計(jì)年份和客戶類別，輸出參數(shù)是訂購(gòu)金額合計(jì)。（3） 編寫一段T-SQL程序調(diào)用函數(shù)fGetProfit，輸出2012年雜志類別為科技類的盈利金額合計(jì)。（4） 編寫一段T-SQL程序調(diào)用存儲(chǔ)過程pGetMoney，輸出2013年客戶類別為企業(yè)單位的訂購(gòu)金額合計(jì)。（5） 為雜志訂購(gòu)情況明細(xì)表OrderList定義一個(gè)【AFTER】觸發(fā)器tr_after_OrderList，每插入一條訂購(gòu)情況明細(xì)記錄（

7、訂單編號(hào)Ono，雜志代碼Mno，訂購(gòu)數(shù)量Onum，進(jìn)貨單價(jià)Miprice，訂購(gòu)單價(jià)Moprice），自動(dòng)計(jì)算其訂購(gòu)金額Omoney和盈利金額Oprofit，同時(shí)自動(dòng)計(jì)算訂購(gòu)情況主表OrderH的訂單貨款金額合計(jì)OMsum和訂單盈利金額合計(jì)OPsum。其中，訂購(gòu)情況明細(xì)表OrderList的訂購(gòu)金額=訂購(gòu)單價(jià)×訂購(gòu)數(shù)量，盈利金額=（訂購(gòu)單價(jià)-進(jìn)貨單價(jià)）×訂購(gòu)數(shù)量。（6） 禁用觸發(fā)器tr_after_OrderList，再為雜志訂購(gòu)情況明細(xì)表OrderList設(shè)計(jì)一個(gè)【INSTEAD OF】觸發(fā)器tr_instead_OrderList，完成（5）的同樣功能。（7） 編寫ins

8、ert語句示例，分別驗(yàn)證觸發(fā)器tr_after_OrderList和tr_instead_OrderList效果。3.實(shí)驗(yàn)步驟按以上實(shí)驗(yàn)內(nèi)容的要求，給出實(shí)驗(yàn)步驟，包括功能實(shí)現(xiàn)過程的簡(jiǎn)要文字說明、T-SQL語句、SQL Server Management Studio的運(yùn)行結(jié)果截圖等。3.1創(chuàng)建數(shù)據(jù)庫(kù)OrderDB（雜志訂購(gòu)數(shù)據(jù)庫(kù)）3.1.1根據(jù)實(shí)驗(yàn)要求利用交互式SQL SERVER 2005創(chuàng)建OrderDB數(shù)據(jù)庫(kù)的數(shù)據(jù)類型、列項(xiàng)和主鍵。1.Customer表2.Magazine表3.OrderH表4.OrderList表3.1.2根據(jù)實(shí)驗(yàn)的查詢要求向表中加入數(shù)據(jù)1.Customer表2.Ma

9、gazine表3.OrderH表4.OrderList表(數(shù)據(jù)量較大，未能截全)3.2 T-SQL查詢（1）-T-SQL(1)WITH Csno(sno) AS(SELECT sno FROM Customer WHERE Cname = '珠江航運(yùn)公司' UNION ALL SELECT Customer.sno FROM Csno, Customer WHERE Csno.sno = Co )SELECT Customer.Cno ,Customer.Cname FROM Csno,Customer WHERE Csno.sno = Co AND Ccity='廣州

10、'-遞歸求出珠江航運(yùn)公司在廣州的上級(jí)主管單位執(zhí)行結(jié)果（2）-T-SQL(2)SELECT Magazine.Mno,Mname,OnumFROM Customer JOIN OrderH ON(Customer.Cno=OrderH.Cno) JOIN OrderList ON(OrderH.Ono=OrderList.Ono) JOIN Magazine ON(OrderList.Mno=Magazine.Mno)WHERE year(Odate)='2011' AND Customer.Cname='天空網(wǎng)絡(luò)公司' AND Onum>(2*(S

11、ELECT MIN(Onum)FROM Customer JOIN OrderH ON(Customer.Cno=OrderH.Cno) JOIN OrderList ON(OrderH.Ono=OrderList.Ono) JOIN Magazine ON(OrderList.Mno=Magazine.Mno)WHERE year(Odate)='2011' AND Customer.Cname='天空網(wǎng)絡(luò)公司')-天空網(wǎng)絡(luò)公司在年所訂購(gòu)的大于其最小訂購(gòu)數(shù)量的倍的雜志代碼、雜志名稱及訂購(gòu)數(shù)量執(zhí)行結(jié)果（3）-T-SQL(3)SELECT Customer.Cno

12、,Customer.Cname,Onum,OmoneyFROM Customer JOIN OrderH ON(Customer.Cno=OrderH.Cno) JOIN OrderList ON(OrderH.Ono=OrderList.Ono) JOIN Magazine ON(OrderList.Mno=Magazine.Mno)WHERE Customer.Ctype='事業(yè)單位' AND year(Odate)='2011' AND Magazine.Mtype='歷史類'ORDER BY Cno -客戶類別為事業(yè)單位在年訂購(gòu)的雜志類別

13、為歷史類的客戶代碼、客戶名稱、訂購(gòu)數(shù)量、訂購(gòu)金額COMPUTE SUM(Onum),SUM(Omoney) BY Cno -使用COMPUTE BY、COMPUTE同時(shí)輸出按客戶計(jì)算的訂購(gòu)數(shù)量和訂購(gòu)金額的合計(jì)、所有客戶的訂購(gòu)數(shù)量和訂購(gòu)金額的總計(jì)。COMPUTE SUM(Onum),SUM(Omoney)執(zhí)行結(jié)果（4）-T-SQL(4)SELECT TOP 7 Onum,Customer.Cno,Cname -使用TOP和查詢結(jié)果集別名表達(dá)式，查詢雜志名稱為讀者、年訂購(gòu)數(shù)量為第-10名的客戶代碼、客戶名稱和訂購(gòu)數(shù)量 FROM (SELECT DISTINCT TOP 10 Onum,Cno FR

14、OM OrderH JOIN OrderList ON (OrderH.Ono=OrderList.Ono) JOIN Magazine ON (OrderList.Mno=Magazine.Mno) WHERE year(Odate)='2011' AND Mname='讀者' ORDER BY Onum ASC) AS T1(Onum,Cno) JOIN Customer ON (T1.Cno=Customer.Cno)執(zhí)行結(jié)果（5）-T-SQL(5)DECLARE CurDnum SCROLL CURSOR FOR SELECT Onum -用游標(biāo)編程，求

15、大連市的雜志在年的平均訂購(gòu)數(shù)量和總訂購(gòu)數(shù)量的功能，不能用COUNT、AVG和SUM函數(shù)。FROM Magazine JOIN OrderList ON (Magazine.Mno=OrderList.Mno) JOIN OrderH ON(OrderList.Ono=OrderH.Ono) WHERE Mcity='大連' AND year(Odate)='2013'-定義局部變量DECLARE AvgDnum numeric(4,1), SumDnum INT, PerDnum INT,Cnt numeric(4,1);SET SumDnum = 0;SET

16、Cnt = 0;OPEN CurDnum; -打開游標(biāo)FETCH Next FROM CurDnum INTO PerDnum; -提取第一條游標(biāo)記錄WHILE FETCH_STATUS = 0 -提取成功則循環(huán)BEGIN SET SumDnum=SumDnum+PerDnum; Set Cnt=Cnt+1 FETCH Next FROM CurDnum INTO PerDnum; -提取下一條游標(biāo)記錄ENDSET AvgDnum = SumDnum / Cnt-顯示總訂閱數(shù)量和平均訂閱數(shù)量SELECT SumDnum as '總訂閱數(shù)量',AvgDnum as '平均

17、訂閱數(shù)量'CLOSE CurDnum;DEALLOCATE CurDnum;-釋放游標(biāo)執(zhí)行結(jié)果3.3存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程（1）-存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程(1)CREATE FUNCTION dbo.fGetProfit(time datetime,type varchar(20) RETURNS numeric(10,2)ASBEGINRETURN (SELECT SUM(Oprofit) AS 總利潤(rùn)FROM Magazine JOIN OrderList ON (Magazine.Mno=OrderList.Mno) JOIN OrderH ON (OrderL

18、ist.Ono=OrderH.Ono)WHERE year(Odate)= time AND Mtype=type)END執(zhí)行結(jié)果（2）-存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程(2)CREATE PROC pGetMonytime datetime,type varchar(20),money numeric(10,2) OUTPUTASBEGINSELECT money=SUM(Omsum)FROM Customer JOIN OrderH ON (Customer.Cno=OrderH.Cno)WHERE year(Odate)=time AND Ctype=typeEND執(zhí)行結(jié)果（3）-存儲(chǔ)過

19、程、自定義函數(shù)和觸發(fā)器編程(3)DECLARE a datetime ,b varchar(20),c numeric(10,2)SET a=2011SET b='文學(xué)類'select c=dbo.fGetProfit(a,b)print(c)執(zhí)行結(jié)果（4）-存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程(4)DECLARE time1 datetime,type1 varchar(20),money1 numeric(10,2)SET time1=2013SET type1='企業(yè)單位'EXEC pGetMony time1,type1,money1 OUTPUTprint

20、(money1)執(zhí)行結(jié)果（5）-存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程(5)CREATE TRIGGER tr_after_OrderList ON OrderList AFTER INSERT ASBEGINDECLARE money numeric(10,2),profit numeric(10,2),c varchar(6)-設(shè)置變量UPDATE OrderList -更新OrderList表SET Omoney=(OrderList.Moprice* OrderList.Onum),Oprofit=(OrderList.Moprice-OrderList.Miprice)*OrderList

21、.Onum)FROM OrderH JOIN inserted a ON (OrderH.Ono=a.Ono)SELECT c=Ono -從插入的表中提取出Ono，作為后續(xù)更新表的條件FROM insertedSELECT money=SUM(Omoney),profit=SUM(Oprofit)FROM OrderListWHERE OrderList.Ono=cUPDATE OrderH -更新OrderH表SET OMsum=money,OPsum=profitWHERE OrderH.Ono=cEND執(zhí)行結(jié)果（6）-存儲(chǔ)過程、自定義函數(shù)和觸發(fā)器編程(6)DISABLE TRIGGER

22、tr_after_OrderList ON OrderList-禁用tr_after_OrderList觸發(fā)器CREATE TRIGGER tr_instead_OrderList ON OrderList instead of INSERT ASBEGINDECLARE money numeric(10,2),profit numeric(10,2),c varchar(6)-設(shè)置變量UPDATE OrderList -更新OrderList表SET Omoney=(OrderList.Moprice* OrderList.Onum),Oprofit=(OrderList.Moprice-OrderList.Miprice)*OrderList.Onum)FROM OrderH JOIN inserted a ON (OrderH.Ono=a.Ono)SELECT c=O