1、精選文檔SQL SERVER 數據庫應用技術實驗報告選課序號： 班 級： 學 號： 姓 名： 指導教師： 成 績： 目 錄1.實驗目的12.實驗內容12.1創建數據庫OrderDB（雜志訂購數據庫）12.2 T-SQL查詢12.3存儲過程、自定義函數和觸發器編程23.實驗步驟23.1創建數據庫OrderDB（雜志訂購數據庫）23.2 T-SQL查詢53.3存儲過程、自定義函數和觸發器編程54.總結與體會8精選文檔1.實驗目的（1） 創建與使用數據庫。了解數據庫及其各類邏輯對象、數據庫的文件與文件組的概念；實踐數據庫的設計、創建、查看和維護等的操作，。（2） T-SQL查詢。掌握SELECT查詢

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

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

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

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

6、tProfit，實現統計某年份給定雜志類別的盈利金額合計的功能，輸入參數是統計年份和雜志類別，輸出參數是盈利金額合計。（2） 設計存儲過程pGetMoney，實現統計某年份給定客戶類別的訂購金額合計的功能，輸入參數是統計年份和客戶類別，輸出參數是訂購金額合計。（3） 編寫一段T-SQL程序調用函數fGetProfit，輸出2012年雜志類別為科技類的盈利金額合計。（4） 編寫一段T-SQL程序調用存儲過程pGetMoney，輸出2013年客戶類別為企業單位的訂購金額合計。（5） 為雜志訂購情況明細表OrderList定義一個【AFTER】觸發器tr_after_OrderList，每插入一條訂

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

8、7） 編寫insert語句示例，分別驗證觸發器tr_after_OrderList和tr_instead_OrderList效果。3.實驗步驟按以上實驗內容的要求，給出實驗步驟，包括功能實現過程的簡要文字說明、T-SQL語句、SQL Server Management Studio的運行結果截圖等。3.1創建數據庫OrderDB（雜志訂購數據庫）3.1.1根據實驗要求利用交互式SQL SERVER 2005創建OrderDB數據庫的數據類型、列項和主鍵。1.Customer表2.Magazine表3.OrderH表4.OrderList表3.1.2根據實驗的查詢要求向表中加入數據1.Custo

9、mer表2.Magazine表3.OrderH表4.OrderList表(數據量較大，未能截全)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、='廣州'-遞歸求出珠江航運公司在廣州的上級主管單位執行結果（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='天空網絡公司' AND Onum&

11、gt;(2*(SELECT 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='天空網絡公司')-天空網絡公司在年所訂購的大于其最小訂購數量的倍的雜志代碼、雜志名稱及訂購數量執行結果（3）-T-SQL(3)SELECT Cust

12、omer.Cno,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='事業單位' AND year(Odate)='2011' AND Magazine.Mtype='歷史類'ORDER BY Cno -客戶類別為事業單位在

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

14、m,Cno FROM 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)執行結果（5）-T-SQL(5)DECLARE CurDnum SCROLL CURSOR FOR SELECT Onum

15、-用游標編程，求大連市的雜志在年的平均訂購數量和總訂購數量的功能，不能用COUNT、AVG和SUM函數。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

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

17、 '平均訂閱數量'CLOSE CurDnum;DEALLOCATE CurDnum;-釋放游標執行結果3.3存儲過程、自定義函數和觸發器編程（1）-存儲過程、自定義函數和觸發器編程(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

18、 (OrderList.Ono=OrderH.Ono)WHERE year(Odate)= time AND Mtype=type)END執行結果（2）-存儲過程、自定義函數和觸發器編程(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執行結

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

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

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

22、TRIGGER tr_after_OrderList ON OrderList-禁用tr_after_OrderList觸發器CREATE TRIGGER tr_instead_OrderList ON OrderList instead of INSERT ASBEGINDECLARE money numeric(10,2),profit numeric(10,2),c varchar(6)-設置變量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=