SQL SERVER数据库实验.docx
《SQL SERVER数据库实验.docx》由会员分享，可在线阅读，更多相关《SQL SERVER数据库实验.docx（13页珍藏版）》请在冰豆网上搜索。
SQLSERVER数据库实验
SQLSERVER数据库应用技术
实验报告
选课序号：
班级：
学号：
姓名：
指导教师：
成绩：
1.实验目的
（1）创建与使用数据库。
了解数据库及其各类逻辑对象、数据库的文件与文件组的概念；实践数据库的设计、创建、查看和维护等的操作，。
（2）T-SQL查询。
掌握SELECT查询命令，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，订购单价Moprice），其中，订购价格>进货价格，杂志类别：
文学类、历史类、科技类。
主键为（杂志代码Mno）。
（2）客户（杂志的订购单位信息）表Customer（客户代码Cno，客户名称Cname，客户所在城市Ccity，上级主管单位代码Sno，客户类别Ctype），客户（单位）类别：
政府单位、事业单位、企业单位。
主键为（客户代码Cno）。
（3）杂志订购情况主表OrderH（订单编号Ono，客户代码Cno，订购日期Odate，订单货款金额合计OMsum，订单盈利金额合计OPsum），主键为订单编号Ono。
（4）杂志订购情况明细表OrderList（订单编号Ono，杂志代码Mno，订购数量Onum，进货单价Miprice，订购单价Moprice，订购金额Omoney，盈利金额Oprofit），主键为（订单编号Ono，杂志代码Mno），订购金额=订购单价×订购数量，盈利金额=（订购单价-进货单价）×订购数量。
2.2T-SQL查询
实现如下查询功能前，请向所有数据表添加足够多的演示数据。
求年份的函数为year（），返回类型为int，年份=year（订购日期Odate）。
（1）使用WITH公用表表达式查看客户名称为’珠江航运公司’在广州市的所有上级主管单位代码和单位名称。
（2）查询客户名称为’天空网络公司’在2011年所订购的大于其最小订购数量的2倍的杂志代码、杂志名称及订购数量。
（3）使用COMPUTEBY、COMPUTE，求客户类别为’事业单位’在2011年订购的杂志类别为’历史类’的客户代码、客户名称、订购数量、订购金额，要求同时输出按客户计算的订购数量和订购金额的合计、所有客户的订购数量和订购金额的总计。
（4）使用TOP和查询结果集别名表达式，查询杂志名称为’读者’、2011年订购数量为第4-10名的客户代码、客户名称和订购数量（设’读者’的订购客户数>=10）。
（5）用游标编程，求大连市的杂志在2013年的平均订购数量和总订购数量的功能，不能用COUNT、AVG和SUM函数。
2.3存储过程、自定义函数和触发器编程
（1）设计自定义函数fGetProfit，实现统计某年份给定杂志类别的盈利金额合计的功能，输入参数是统计年份和杂志类别，输出参数是盈利金额合计。
（2）设计存储过程pGetMoney，实现统计某年份给定客户类别的订购金额合计的功能，输入参数是统计年份和客户类别，输出参数是订购金额合计。
（3）编写一段T-SQL程序调用函数fGetProfit，输出2012年杂志类别为’科技类’的盈利金额合计。
（4）编写一段T-SQL程序调用存储过程pGetMoney，输出2013年客户类别为’企业单位’的订购金额合计。
（5）为杂志订购情况明细表OrderList定义一个【AFTER】触发器tr_after_OrderList，每插入一条订购情况明细记录（订单编号Ono，杂志代码Mno，订购数量Onum，进货单价Miprice，订购单价Moprice），自动计算其订购金额Omoney和盈利金额Oprofit，同时自动计算订购情况主表OrderH的订单货款金额合计OMsum和订单盈利金额合计OPsum。
其中，订购情况明细表OrderList的订购金额=订购单价×订购数量，盈利金额=（订购单价-进货单价）×订购数量。
（6）禁用触发器tr_after_OrderList，再为杂志订购情况明细表OrderList设计一个【INSTEADOF】触发器tr_instead_OrderList，完成（5）的同样功能。
（7）编写insert语句示例，分别验证触发器tr_after_OrderList和tr_instead_OrderList效果。
3.实验步骤
按以上实验内容的要求，给出实验步骤，包括功能实现过程的简要文字说明、T-SQL语句、SQLServerManagementStudio的运行结果截图等。
3.1创建数据库OrderDB（杂志订购数据库）
3.1.1根据实验要求利用交互式SQLSERVER2005创建OrderDB数据库的数据类型、列项和主键。
1.Customer表
2.Magazine表
3.OrderH表
4.OrderList表
3.1.2根据实验的查询要求向表中加入数据
1.Customer表
2.Magazine表
3.OrderH表
4.OrderList表（数据量较大，未能截全）
3.2T-SQL查询
（1）--T-SQL
（1）
WITHCsno（sno）AS
（SELECTsno
FROMCustomer
WHERECname='珠江航运公司'
UNIONALL
SELECTCustomer.sno
FROMCsno,Customer
WHERECsno.sno=Co）
SELECTCustomer.Cno,Customer.Cname
FROMCsno,Customer
WHERECsno.sno=CoANDCcity='广州';--递归求出珠江航运公司在广州的上级主管单位
执行结果
（2）--T-SQL
（2）
SELECTMagazine.Mno,Mname,Onum
FROMCustomerJOINOrderHON（Customer.Cno=OrderH.Cno）JOINOrderListON（OrderH.Ono=OrderList.Ono）JOINMagazineON（OrderList.Mno=Magazine.Mno）
WHEREyear（Odate）='2011'ANDCustomer.Cname='天空网络公司'ANDOnum>（2*（SELECTMIN（Onum）
FROMCustomerJOINOrderHON（Customer.Cno=OrderH.Cno）JOINOrderListON（OrderH.Ono=OrderList.Ono）JOINMagazineON（OrderList.Mno=Magazine.Mno）
WHEREyear（Odate）='2011'ANDCustomer.Cname='天空网络公司'））--’天空网络公司’在年所订购的大于其最小订购数量的倍的杂志代码、杂志名称及订购数量
执行结果
（3）--T-SQL（3）
SELECTCustomer.Cno,Customer.Cname,Onum,Omoney
FROMCustomerJOINOrderHON（Customer.Cno=OrderH.Cno）JOINOrderListON（OrderH.Ono=OrderList.Ono）JOINMagazineON（OrderList.Mno=Magazine.Mno）
WHERECustomer.Ctype='事业单位'ANDyear（Odate）='2011'ANDMagazine.Mtype='历史类'
ORDERBYCno--客户类别为’事业单位’在年订购的杂志类别为’历史类’的客户代码、客户名称、订购数量、订购金额
COMPUTESUM（Onum）,SUM（Omoney）BYCno--使用COMPUTEBY、COMPUTE同时输出按客户计算的订购数量和订购金额的合计、所有客户的订购数量和订购金额的总计。
COMPUTESUM（Onum）,SUM（Omoney）
执行结果
（4）--T-SQL（4）
SELECTTOP7Onum,Customer.Cno,Cname--使用TOP和查询结果集别名表达式，查询杂志名称为’读者’、年订购数量为第-10名的客户代码、客户名称和订购数量
FROM（SELECTDISTINCTTOP10Onum,Cno
FROMOrderHJOINOrderListON（OrderH.Ono=OrderList.Ono）JOINMagazineON（OrderList.Mno=Magazine.Mno）
WHEREyear（Odate）='2011'ANDMname='读者'ORDERBYOnumASC）AST1（Onum,Cno）JOINCustomerON（T1.Cno=Customer.Cno）
执行结果
（5）--T-SQL（5）
DECLARECurDnumSCROLLCURSORFOR
SELECTOnum--用游标编程，求大连市的杂志在年的平均订购数量和总订购数量的功能，不能用COUNT、AVG和SUM函数。
FROMMagazineJOINOrderListON（Magazine.Mno=OrderList.Mno）JOINOrderHON（OrderList.Ono=OrderH.Ono）
WHEREMcity='大连'ANDyear（Odate）='2013';
--定义局部变量
DECLARE@AvgDnumnumeric（4,1）,@SumDnumINT,@PerDnumINT,@Cntnumeric（4,1）;
SET@SumDnum=0;
SET@Cnt=0;
OPENCurDnum;--打开游标
FETCHNextFROMCurDnumINTO@PerDnum;--提取第一条游标记录
WHILE@@FETCH_STATUS=0--提取成功则循环
BEGIN
SET@SumDnum=@SumDnum+@PerDnum;
Set@Cnt=@Cnt+1
FETCHNextFROMCurDnumINTO@PerDnum;--提取下一条游标记录
END
SET@AvgDnum=@SumDnum/@Cnt
--显示总订阅数量和平均订阅数量
SELECT@SumDnumas'总订阅数量',@AvgDnumas'平均订阅数量'
CLOSECurDnum;
DEALLOCATECurDnum;--释放游标
执行结果
3.3存储过程、自定义函数和触发器编程
（1）--存储过程、自定义函数和触发器编程
（1）
CREATEFUNCTIONdbo.fGetProfit（@timedatetime,@typevarchar（20））RETURNSnumeric（10,2）
AS
BEGIN
RETURN（SELECTSUM（Oprofit）AS总利润
FROMMagazineJOINOrderListON（Magazine.Mno=OrderList.Mno）JOINOrderHON（OrderList.Ono=OrderH.Ono）
WHEREyear（Odate）=@timeANDMtype=@type）
END
执行结果
（2）--存储过程、自定义函数和触发器编程
（2）
CREATEPROCpGetMony
@timedatetime,@typevarchar（20）,@moneynumeric（10,2）OUTPUT
AS
BEGIN
SELECT@money=SUM（Omsum）
FROMCustomerJOINOrderHON（Customer.Cno=OrderH.Cno）
WHEREyear（Odate）=@timeANDCtype=@type
END
执行结果
（3）--存储过程、自定义函数和触发器编程（3）
DECLARE@adatetime,@bvarchar（20）,@cnumeric（10,2）
SET@a=2011
SET@b='文学类'
select@c=dbo.fGetProfit（@a,@b）
print（@c）
执行结果
（4）--存储过程、自定义函数和触发器编程（4）
DECLARE@time1datetime,@type1varchar（20）,@money1numeric（10,2）
SET@time1=2013
SET@type1='企业单位';
EXECpGetMony@time1,@type1,@money1OUTPUT
print（@money1）
执行结果
（5）--存储过程、自定义函数和触发器编程（5）
CREATETRIGGERtr_after_OrderList
ONOrderListAFTERINSERTAS
BEGIN
DECLARE@moneynumeric（10,2）,@profitnumeric（10,2）,@cvarchar（6）--设置变量
UPDATEOrderList--更新OrderList表
SETOmoney=（OrderList.Moprice*OrderList.Onum）,Oprofit=（（OrderList.Moprice-OrderList.Miprice）*OrderList.Onum）
FROMOrderHJOINinsertedaON（OrderH.Ono=a.Ono）
SELECT@c=Ono--从插入的表中提取出Ono，作为后续更新表的条件
FROMinserted
SELECT@money=SUM（Omoney）,@profit=SUM（Oprofit）
FROMOrderList
WHEREOrderList.Ono=@c
UPDATEOrderH--更新OrderH表
SETOMsum=@money,OPsum=@profit
WHEREOrderH.Ono=@c
END
执行结果
（6）--存储过程、自定义函数和触发器编程（6）
DISABLETRIGGERtr_after_OrderListONOrderList--禁用tr_after_OrderList触发器
CREATETRIGGERtr_instead_OrderList
ONOrderListinsteadofINSERTAS
BEGIN
DECLARE@moneynumeric（10,2）,@profitnumeric（10,2）,@cvarchar（6）--设置变量
UPDATEOrderList--更新OrderList表
SETOmoney=（OrderList.Moprice*OrderList.Onum）,Oprofit=（（OrderList.Moprice-OrderList.Miprice）*OrderList.Onum）
FROMOrderHJOINinsertedaON（OrderH.Ono=a.Ono）
SELECT@c=Ono--从插入的表中提取出Ono，作为后续更新表的条件
FROMinserted
SELECT@money=SUM（Omoney）,@profit=SUM（Oprofit）
FROMOrderList
WHEREOrderList.Ono=@c
UPDATEOrderH--更新OrderH表
SETOMsum=@money,OPsum=@profit
WHEREOrderH.Ono=@c
END
执行结果
（7）--存储过程、自定义函数和触发器编程（7）
INSERT--验证tr_after_OrderList触发器
INTOOrderList（Ono,Mno,Onum,Miprice,Moprice）
VALUES（'D7',2,300,3.2,3.5）
执行结果
INSERT--验证tr_instead_OrderList触发器
INTOOrderList（Ono,Mno,Onum,Miprice,Moprice）
VALUES（'DD',6,300,4.6,5.0）
执行结果
4.总结与体会
这次实验学到了很多，首先是创建数据库，还有表格，当然这个利用SQLSERVER2005的图形界面操作很简单，不过对于表的约束还是要注意的地方。
然后是用T-SQL语言查询数据，利用WITN表达式，TOP，COMPUTE,COMPUTEBY以及创建游标过程，通过实验学习的更加清晰。
再之后就是在定义函数，这个教材讲的实在是太少，从网上学了学才算是搞明白。
对于存储过程个人理解和自定义函数功能相似，实现较为简单。
DML触发器这一块对于INSERT语句的触发的作用还是很总要的，触发操作是很总要的，对于数据的保护作用很强。
不过对于数据库数据的添加真的很麻烦，希望下次老师能给予数据!
升级会员 