SQLSERVER数据库实验

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. v.1.实验目的（1） 创建与使用数据库。了解数据库及其各类逻辑对象、数据库的文件与文件组的概念；实践数据库的设计、创建、查看和维护等的操作，。（2） T-SQL查询。掌握SELECT查询命令，INSERT、UPD

2、ATE和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，订购单价Moprice)，其中

3、，订购价格>进货价格，杂志类别：文学类、历史类、科技类。主键为（杂志代码Mno）。（2） 客户（杂志的订购单位信息）表Customer(客户代码Cno，客户名称Cname，客户所在城市Ccity，上级主管单位代码Sno，客户类别Ctype)，客户（单位）类别：政府单位、事业单位、企业单位。主键为（客户代码Cno）。（3） 杂志订购情况主表OrderH（订单编号Ono，客户代码Cno，订购日期Odate，订单货款金额合计OMsum，订单盈利金额合计OPsum)，主键为订单编号Ono。（4） 杂志订购情况明细表OrderList（订单编号Ono，杂志代码Mno，订购数量Onum，进货单价Mi

4、price，订购单价Moprice，订购金额Omoney，盈利金额Oprofit)，主键为(订单编号Ono，杂志代码Mno)，订购金额=订购单价×订购数量，盈利金额=（订购单价-进货单价）×订购数量。2.2T-SQL查询实现如下查询功能前，请向所有数据表添加足够多的演示数据。求年份的函数为year( )，返回类型为int，年份=year（订购日期Odate）。（1） 使用WITH公用表表达式查看客户名称为珠江航运公司在广州市的所有上级主管单位代码和单位名称。（2） 查询客户名称为天空网络公司在2011年所订购的大于其最小订购数量的2倍的杂志代码、杂志名称及订购数量。（3）

5、使用COMPUTE BY、COMPUTE，求客户类别为事业单位在2011年订购的杂志类别为历史类的客户代码、客户名称、订购数量、订购金额，要求同时输出按客户计算的订购数量和订购金额的合计、所有客户的订购数量和订购金额的总计。（4） 使用TOP和查询结果集别名表达式，查询杂志名称为读者、2011年订购数量为第4-10名的客户代码、客户名称和订购数量（设读者的订购客户数>=10）。 （5） 用游标编程，求大连市的杂志在2013年的平均订购数量和总订购数量的功能，不能用COUNT、AVG和SUM函数。2.3存储过程、自定义函数和触发器编程（1） 设计自定义函数fGetProfit，实现统计某年

6、份给定杂志类别的盈利金额合计的功能，输入参数是统计年份和杂志类别，输出参数是盈利金额合计。（2） 设计存储过程pGetMoney，实现统计某年份给定客户类别的订购金额合计的功能，输入参数是统计年份和客户类别，输出参数是订购金额合计。（3） 编写一段T-SQL程序调用函数fGetProfit，输出2012年杂志类别为科技类的盈利金额合计。（4） 编写一段T-SQL程序调用存储过程pGetMoney，输出2013年客户类别为企业单位的订购金额合计。（5） 为杂志订购情况明细表OrderList定义一个【AFTER】触发器tr_after_OrderList，每插入一条订购情况明细记录（订单编号On

7、o，杂志代码Mno，订购数量Onum，进货单价Miprice，订购单价Moprice），自动计算其订购金额Omoney和盈利金额Oprofit，同时自动计算订购情况主表OrderH的订单货款金额合计OMsum和订单盈利金额合计OPsum。其中，订购情况明细表OrderList的订购金额=订购单价×订购数量，盈利金额=（订购单价-进货单价）×订购数量。（6） 禁用触发器tr_after_OrderList，再为杂志订购情况明细表OrderList设计一个【INSTEAD OF】触发器tr_instead_OrderList，完成（5）的同样功能。（7） 编写insert语句示

8、例，分别验证触发器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.Customer表2.Magazine

9、表3.OrderH表4.OrderList表(数据量较大，未能截全)3.2 T-SQL查询（1）-T-SQL(1)WITH Csno(sno)AS(SELECT sno FROM Customer WHERE Cname ='珠江航运公司'UNION ALLSELECT 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)WHEREyear(Odate)='2011'AND Customer.Cname='天空网络公司'AND Onum>(2*(SELECTMIN(Onum)FRO

11、M Customer JOIN OrderH ON(Customer.Cno=OrderH.Cno)JOIN OrderList ON(OrderH.Ono=OrderList.Ono)JOIN Magazine ON(OrderList.Mno=Magazine.Mno)WHEREyear(Odate)='2011'AND Customer.Cname='天空网络公司')-天空网络公司在年所订购的大于其最小订购数量的倍的杂志代码、杂志名称及订购数量执行结果（3）-T-SQL(3)SELECT Customer.Cno,Customer.Cname,Onum,O

12、moneyFROM 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='事业单位'ANDyear(Odate)='2011'AND Magazine.Mtype='历史类'ORDERBY Cno -客户类别为事业单位在年订购的杂志类别为历史类的客户代码、客户名称、订购数量、订购金额COMP

13、UTESUM(Onum),SUM(Omoney)BY Cno -使用COMPUTE BY、COMPUTE同时输出按客户计算的订购数量和订购金额的合计、所有客户的订购数量和订购金额的总计。COMPUTESUM(Onum),SUM(Omoney)执行结果（4）-T-SQL(4)SELECTTOP 7 Onum,Customer.Cno,Cname -使用TOP和查询结果集别名表达式，查询杂志名称为读者、年订购数量为第-10名的客户代码、客户名称和订购数量FROM(SELECTDISTINCTTOP 10 Onum,CnoFROM OrderH JOIN OrderList ON(OrderH.On

14、o=OrderList.Ono)JOIN Magazine ON(OrderList.Mno=Magazine.Mno)WHEREyear(Odate)='2011'AND Mname='读者'ORDERBY Onum ASC)AS T1(Onum,Cno)JOIN Customer ON(T1.Cno=Customer.Cno)执行结果（5）-T-SQL(5)DECLARE CurDnum SCROLLCURSORFORSELECT Onum -用游标编程，求大连市的杂志在年的平均订购数量和总订购数量的功能，不能用COUNT、AVG和SUM函数。FROM Ma

15、gazine JOIN OrderList ON(Magazine.Mno=OrderList.Mno)JOIN OrderH ON(OrderList.Ono=OrderH.Ono)WHERE Mcity=''ANDyear(Odate)='2013'-定义局部变量DECLARE AvgDnum numeric(4,1), SumDnum INT, PerDnum INT,Cnt numeric(4,1);SET SumDnum = 0;SET Cnt = 0;OPEN CurDnum;-打开游标FETCHNextFROM CurDnum INTO PerDn

16、um;-提取第一条游标记录WHILEFETCH_STATUS= 0 -提取成功则循环BEGINSET SumDnum=SumDnum+PerDnum;Set Cnt=Cnt+1FETCHNextFROM CurDnum INTO PerDnum;-提取下一条游标记录ENDSET AvgDnum = SumDnum / Cnt-显示总订阅数量和平均订阅数量SELECT SumDnum as'总订阅数量',AvgDnum as'平均订阅数量'CLOSE CurDnum;DEALLOCATE CurDnum;-释放游标执行结果3.3存储过程、自定义函数和触发器编程（1

17、）-存储过程、自定义函数和触发器编程(1)CREATEFUNCTION dbo.fGetProfit(time datetime,type varchar(20)RETURNSnumeric(10,2)ASBEGINRETURN(SELECTSUM(Oprofit)AS 总利润FROM Magazine JOIN OrderList ON(Magazine.Mno=OrderList.Mno)JOIN OrderH ON(OrderList.Ono=OrderH.Ono)WHEREyear(Odate)= time AND Mtype=type)END执行结果（2）-存储过程、自定义函数和触发

18、器编程(2)CREATEPROC pGetMonytime datetime,type varchar(20),money numeric(10,2)OUTPUTASBEGINSELECT money=SUM(Omsum)FROM Customer JOIN OrderH ON(Customer.Cno=OrderH.Cno)WHEREyear(Odate)=time AND Ctype=typeEND执行结果（3）-存储过程、自定义函数和触发器编程(3)DECLARE a datetime,b varchar(20),c numeric(10,2)SET a=2011SET b='文

19、学类'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 OUTPUTprint(money1)执行结果（5）-存储过程、自定义函数和触发器编程(5)CREATETRIGGER tr_after_OrderListON OrderList AFTERIN

20、SERTASBEGINDECLARE 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=Ono -从插入的表中提取出Ono，作为后续更新表的条件

21、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)DISABLETRIGGER tr_after_OrderList ON OrderList-禁用tr_after_OrderList触发器CREATETRIGGER tr_instead_OrderListON O

22、rderList insteadofINSERTASBEGINDECLARE 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=Ono -从插入的表中提