叶磊数据库.docx
- 文档编号:7633396
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:15
- 大小:245.41KB
叶磊数据库.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表。
这两个表由系统来维护﹐它们存在于内存中而不是在数据库中。
这两个表的结构总是与被该触发器作用的表的结构相同。
触发器执行完成后﹐与该触发器相关的这两个表也被删除。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库