叶磊数据库.docx
《叶磊数据库.docx》由会员分享，可在线阅读，更多相关《叶磊数据库.docx（15页珍藏版）》请在冰点文库上搜索。
叶磊数据库
实验报告
姓名：
叶磊学号：
1任务概述（任务说明）
1、存储过程
使用相应SQL语句，完成如下操作：
1.新建一存储过程proc1,显示指定用户ID的订单信息列表（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。
2.新建一存储过程proc2,通过输入订单号、书目ID及数量，实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。
3.新建一存储过程proc3，通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。
4.新建一存储过程proc4，统计各订单总金额的分布情况。
订单金额划分情况如下：
0-50元、51-100元、101-150元、151-200元和201元以上。
5.新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时购买过的购买次数最多的前3名书目名称及购买次数，并按照购买次数排序。
6.新建一存储过程proc6，实现查询与指定用户ID购买过相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。
2、触发器
1.创建一触发器tg1，当往orderbook表中增加记录时，实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量相应变动。
2.创建一触发器tg2，当往orderbook中增加记录时，如果商品库存量少于10，则不允许购买该商品，并给出相应提示。
新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），并根据数据库的订单情况将社科类图书的订购情况插入表中。
创建一触发器tg3，当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息
4源码，文档化
---新建一存储过程proc1,显示指定用户ID的订单信息列表
---（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。
createprocedureproc1
@user_idvarchar（20）
as
selectoid,ordertime,payment,orderstate,avgpayment,sumpayment
from"order",orderstate,
（selectavg（payment）avgpayment
from"order"
where"user"=@user_id）a,
（selectsum（payment）sumpayment
from"order"
where"user"=@user_id）b
where"order".state=orderstate.osid
and"user"=@user_id
orderbyordertimedesc
go
execproc1102
---新建一存储过程proc2,通过输入订单号、书目ID及数量，
---实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。
createprocedureproc2（
@order_idvarchar（20）,
@book_dvarchar（20）,
@book_svarchar（20）
）
as
update"order"
setpayment=payment+price
frombook,"order"
whereoidin（
selectorderid
fromorderbook
wherebookid=@book_d）
andbid=@book_d
updateorderbook
setquantity=quantity+@book_s
whereorderid=@order_id
selectbookid
fromorderbook
whereorderid=@order_id
go
execproc22014001,1001,4
---新建一存储过程proc3，通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。
createprocedureproc3
@order_idvarchar（20）,
@book_idvarchar（20）,
@s_pricevarchar（20）
as
update"order"
setpayment=payment+（（@s_price-price）*quantity）
frombook,orderbook
wherebookid=@book_idand（bid=@book_idandorderid=@order_id）
select*
from"order"
whereoid=@order_id
go
execproc32014001,1001,20
---新建一存储过程proc4，统计各订单总金额的分布情况。
订单金额划分情况如下：
---0-50元、-100元、-150元、-200元和元以上。
createprocedureproc4
as
begin
declare@zerotofiftyint,
@fiftytofundredint,
@hundredtohunfint,
@hunftoTwohint,
@moretwohint;
select@zerotofifty=COUNT（case
whenpayment>=0ANDpayment<=50then'1'end）,
@fiftytofundred=COUNT（case
whenpayment>=51ANDpayment<=100then'2'end）,
@hundredtohunf=COUNT（case
whenpayment>=101ANDpayment<=150then'3'end）,
@hunftoTwoh=COUNT（case
whenpayment>=151ANDpayment<=200then'4'end）,
@moretwoh=COUNT（case
whenpayment>=201then'5'end）
from"order"
print@zerotofifty;
print@fiftytofundred;
print@hundredtohunf;
print@hunftoTwoh;
print@moretwoh
end
execproc4
---新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时
---购买过的购买次数最多的前名书目名称及购买次数，并按照购买次数排序。
createprocedureproc5
@book_idvarchar（20）
as
begin
declare@user_idint;
select@user_id=uid
fromorderbook,"order","user"
wherebookid=@book_id
andorderid=oid
and"order"."user"=uid
selectuid,name,oid,quantity,a.bookid,a.title
from"user","order",orderbook,（selecttop3sum（quantity）sumquantity,bookid,title
fromorderbook,book
wherebookid=bid
groupbybookid,title
orderbysumquantitydesc）a
whereuid=@user_id
anduid="user"
andoid=orderid
andorderbook.bookid=a.bookid
orderbya.sumquantitydesc
end
execproc51003
---新建一存储过程proc6，实现查询与指定用户ID购买过
---相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。
createprocedureproc6
@user_idvarchar（20）
as
begin
selecta.uid,a.name,a.bookid,a.quantity
fromorderbook,"order","user",（selectuid,name,bookid,quantity
fromorderbook,"order","user"
whereorderid=oid
and"user"=uid）a
where"user".uid=@user_id
and"order"."user"="user".uid
and"order".oid=orderbook.orderid
anda.uid!
="user".uid
anda.bookid=orderbook.bookid
groupbya.uid,a.name,a.bookid,a.quantity
orderbyquantitydesc
end
execproc6102
---创建一触发器tg1，当往orderbook表中增加记录时，
---实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量相应变动。
createtriggertg1
onorderbook
forinsert,update
as
begin
declare@order_idint,
@book_idint,
@quantityint;
select@book_id=bookid,@quantity=quantity
frominserted;
updatebook
setbook.stock=book.stock-@quantity
where@book_id=book.bid;
print'insertsucceed';
declare@book_idfint,
@quantityfint,
@bookidoint,
@quantityoint,
@lstint;
select@bookido=bookid,@quantityo=quantity
fromdeleted;
select@book_idf=bookid,@quantityf=quantity
frominserted;
updatebook
setbook.stock=book.stock-@quantityf+@quantityo
where@book_idf=book.bid;
print'updatesucceed';
end
droptriggertg1
---创建一触发器tg2，当往orderbook中增加记录时，
---如果商品库存量少于，则不允许购买该商品，并给出相应提示。
createtriggertg2
onorderbookafterinsert
as
begin
declare@stockint,@bookidint;
select@bookid=s.bookid
frominserteds;
select@stock=b.stock
frombookb
whereb.bid=@bookid;
if（@stock<10）
raiserror（'库存少于，不允许购买',16,1）;
rollbacktran;
end
droptriggertg2
---新建图书订购情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），
---并根据数据库的订单情况将社科类图书的订购情况插入表中。
创建一触发器tg3，
---当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息
createtablebookstas
（
idintprimarykey,
titlevarchar（20）notnull,
categoryvarchar（20）notnull,
pricefloatnotnull,
quantityint
）;
createtriggertg3
onorderbook
forinsert
as
begin
declare@bookidint,
@quantityint;
select@bookid=bookid,@quantity=quantity
frominserted;
updatebookstas
setquantity=quantity+@quantity
whereid=@bookid
end
droptriggertg3
5执行过程截屏
6总结
存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。
存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
1:
触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。
所以触发器可以用来实现对表实施复杂的完整性约`束。
2:
SQLServer为每个触发器都创建了两个专用表﹕Inserted表和Deleted表。
这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。
这两个表的结构总是与被该触发器作用的表的结构相同。
触发器执行完成后﹐与该触发器相关的这两个表也被删除。
升级会员 