SQL SERVER數(shù)據(jù)庫實驗

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

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

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

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

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

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

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

8、ert語句示例，分別驗證觸發(fā)器tr_after_OrderList和tr_instead_OrderList效果。3.實驗步驟按以上實驗內(nèi)容的要求，給出實驗步驟，包括功能實現(xiàn)過程的簡要文字說明、T-SQL語句、SQL Server Management Studio的運行結(jié)果截圖等。3.1創(chuàng)建數(shù)據(jù)庫OrderDB（雜志訂購數(shù)據(jù)庫）3.1.1根據(jù)實驗要求利用交互式SQL SERVER 2005創(chuàng)建OrderDB數(shù)據(jù)庫的數(shù)據(jù)類型、列項和主鍵。1.Customer表2.Magazine表3.OrderH表4.OrderList表3.1.2根據(jù)實驗的查詢要求向表中加入數(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 = '珠江航運公司' 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、'-遞歸求出珠江航運公司在廣州的上級主管單位執(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ò)公司在年所訂購的大于其最小訂購數(shù)量的倍的雜志代碼、雜志名稱及訂購數(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è)單位在年訂購的雜志類別

13、為歷史類的客戶代碼、客戶名稱、訂購數(shù)量、訂購金額COMPUTE SUM(Onum),SUM(Omoney) BY Cno -使用COMPUTE BY、COMPUTE同時輸出按客戶計算的訂購數(shù)量和訂購金額的合計、所有客戶的訂購數(shù)量和訂購金額的總計。COMPUTE SUM(Onum),SUM(Omoney)執(zhí)行結(jié)果（4）-T-SQL(4)SELECT TOP 7 Onum,Customer.Cno,Cname -使用TOP和查詢結(jié)果集別名表達(dá)式，查詢雜志名稱為讀者、年訂購數(shù)量為第-10名的客戶代碼、客戶名稱和訂購數(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、大連市的雜志在年的平均訂購數(shù)量和總訂購數(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存儲過程、自定義函數(shù)和觸發(fā)器編程（1）-存儲過程、自定義函數(shù)和觸發(fā)器編程(1)CREATE FUNCTION dbo.fGetProfit(time datetime,type varchar(20) RETURNS numeric(10,2)ASBEGINRETURN (SELECT SUM(Oprofit) AS 總利潤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）-存儲過程、自定義函數(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）-存儲過

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）-存儲過程、自定義函數(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）-存儲過程、自定義函數(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）-存儲過程、自定義函數(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