(完整word版)叶磊数据库

1、实验报告mlW71任务概述（任务说明）一、存储过程使用相应SQL语句，完成如下操作：1. 新建一存储过程procl,显示指定用户ID的订单信息列表（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。2. 新建一存储过程proc2,通过输入订单号、书目ID及数量，实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数（商品类别数量）。3. 新建一存储过程proc3,通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。4. 新建一存储过程proc4,统计各订单总金额的分布情况。订单金额划分情况如下：0-50元、51-100元、101-15

2、0元、151-200元和201元以上。5. 新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时购买过的购买次数最多的前3名书目名称及购买次数，并按照购买次数排序。6. 新建一存储过程proc6，实现查询与指定用户ID购买过相同商品的用户及购买过相同商品的数量，并按照相同商品数量降序排列。二、触发器1. 创建一触发器tg1，当往orderbook表中增加记录时，实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量相应变动。2. 创建一触发器tg2,当往orderbook中增加记录时，如果商品库存量少于10,贝U不允许购买该商品，并给出相应提示。新建图书订购

3、情况统计表bookstas（包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定），并根据数据库的订单情况将社科类图书的订购情况插入表中。创建一触发器tg3,当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息4源码，文档化-新建一存储过程proc1,显示指定用户ID的订单信息列表-（订单号、成交时间、订单总金额、订单状态及所有订单的平均金额），并成交时间降序排列。createprocedureproc1user_idvarchar（20）asselectoid,ordertime,payment,orderstate,avgpayment,sump

4、aymentfrom"order",orderstate,(selectavg(payment)avgpaymentfrom"order"where"user"=user_id)a,(selectsum(payment)sumpaymentfrom"order"where"user"=user_id)bwhere"order".state=orderstate.osidand"user"=user_idorderbyordertimedescgoexecp

5、roc1102-新建一存储过程proc2,通过输入订单号、书目ID及数量，-实现给该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数(商品类别数量)。createprocedureproc2(order_idvarchar(20),book_dvarchar(20),book_svarchar(20)asupdate"order"setpayment=payment+pricefrombook,"order"whereoidin(selectorderidfromorderbookwherebookid=book_d)andbid=book_d

6、updateorderbooksetquantity=quantity+book_swhereorderid=order_idselectbookidfromorderbookwhereorderid=order_idgoexecproc22014001,1001,4-新建一存储过程proc3,通过修改指定订单、指定商品的价格，并返回该订单的新总金额及优惠金额。createprocedureproc3orderidvarchar(20),book_idvarchar(20),s_pricevarchar(20)asupdate"order"setpayment=paymen

7、t+(s_price-price)*quantity)frombook,orderbookwherebookid=book_idand(bid=book_idandorderid=order_id)select*from"order"whereoid=order_idgoexecproc32014001,1001,20-新建一存储过程proc4,统计各订单总金额的分布情况。订单金额划分情况如下：-0-50元、-100元、-150元、-200元和元以上。createprocedureproc4asbegindeclarezerotofiftyint,fiftytofundre

8、dint,hundredtohunfint,hunftoTwohint,moretwohint;selectzerotofifty=COUNT(casewhenpayment>=0ANDpayment<=50then'1'end),fiftytofundred=COUNT(casewhenpayment>=51ANDpayment<=100then'2'end),hundredtohunf=COUNT(casewhenpayment>=101ANDpayment<=150then'3'end),hunftoTw

9、oh=COUNT(casewhenpayment>=151ANDpayment<=200then'4'end),moretwoh=COUNT(casewhenpayment>=201then'5'end)from"order"printzerotofifty;printfiftytofundred;printhundredtohunf;printhunftoTwoh;printmoretwohendexecproc4-新建一存储过程proc5，实现查询购买过指定书目ID的用户还同时-购买过的购买次数最多的前名书目名称及购买次

10、数，并按照购买次数排序。createprocedureproc5book_idvarchar(20)asbegindeclareuser_idint;selectuser_id=uidfromorderbook,"order","user"wherebookid=book_idandorderid=oidand"order"."user"=uidselectuid,name,oid,quantity,a.bookid,a.titlefrom"user","order",ord

11、erbook,(selecttop3sum(quantity)sumquantity,bookid,titlefromorderbook,bookwherebookid=bidgroupbybookid,titleorderbysumquantitydesc)awhereuid=user_idanduid="user"andoid=orderidandorderbook.bookid=a.bookidorderbya.sumquantitydescendexecproc51003-新建一存储过程proc6，实现查询与指定用户ID购买过-相同商品的用户及购买过相同商品的数量，

12、并按照相同商品数量降序排列。createprocedureproc6user_idvarchar(20)asbeginselecta.uid,,a.bookid,a.quantityfromorderbook,"order","user",(selectuid,name,bookid,quantityfromorderbook,"order","user"whereorderid=oidand"user"=uid)awhere"user".uid=user_ida

13、nd"order"."user"="user".uidand"order".oid=orderbook.orderidanda.uid!="user".uidanda.bookid=orderbook.bookidgroupbya.uid,,a.bookid,a.quantityorderbyquantitydescendexecproc6102-创建一触发器tg1,当往orderbook表中增加记录时，-实现商品库存相应减少；当修改orderbook表中相应商品数量时，实现商品数量

14、相应变动。createtriggertg1onorderbookforinsert,updateasbegindeclareorder_idint,book_idint,quantityint;selectbook_id=bookid,quantity=quantityfrominserted;updatebooksetbook.stock=book.stock-quantitywherebook_id=book.bid;print'insertsucceed'declarebook_idfint,quantityfint,bookidoint,quantityoint,lst

15、int;selectbookido=bookid,quantityo=quantityfromdeleted;selectbook_idf=bookid,quantityf=quantityfrominserted;updatebooksetbook.stock=book.stock-quantityf+quantityowherebook_idf=book.bid;print'updatesucceed'enddroptriggertg1-创建一触发器tg2,当往orderbook中增加记录时，-如果商品库存量少于，则不允许购买该商品，并给出相应提示。createtrigge

16、rtg2onorderbookafterinsertasbegindeclarestockint,bookidint;selectbookid=s.bookidfrominserteds;selectstock=b.stockfrombookbwhereb.bid=bookid;if(stock<10)raiserror('库存少于，不允许购买,16,1);rollbacktran;enddroptriggertg2-新建图书订购情况统计表bookstas(包含图书编号、图书名称、图书类别、图书价格和订购册数，数据类型自定)，-并根据数据库的订单情况将社科类图书的订购情况插入表中

17、。创建一触发器tg3,-当往orderbook表中增加记录时，自动更新bookstas表相应图书的统计信息createtablebookstas(idintprimarykey,titlevarchar(20)notnull,categoryvarchar(20)notnull,pricefloatnotnull,quantityint);createtriggertg3onorderbookforinsertasbegindeclarebookidint,quantityint;selectbookid=bookid,quantity=quantityfrominserted;updateb

18、ookstassetquantity=quantity+quantitywhereid=bookidenddroptriggertg35执行过程截屏曰一新建一存倍过程皿皿，显示指定用户工南订国言息列表(订单号、成交时间、订单总金额、订箪状态及所有订单的平均金额)，并成交时间降序有冽。曰createprocedure1136ridvarchAr(20)asl£seleei:oldcrdemi&eirpaiTCertT：,orderacate,avgpaiTt-ent,suitpaenufxam"orderwPordexaelecravgpamen'E.)avg

19、pairmenr;Ifiojn11order"whereus-er*1=user_idjselect;SLzmpaTiient)s'JirxaTrentfrom,fozd-erwwhere"iiseru3er_ici.bwherefrarder",state=ard-erscate,o-sidaaduser".user_iclorderbyordertimedescgo4|air国谿果J消息c*dordesrtimepaymentorderaiteavgpayinertsunipaymerrtUIIUIIMJIHUIIMIUUI1_i2014(M

20、Mi2O1MH<Jf7O®：W：29.Wl&未提交.30.66666砌孺667922新藏-20141-0321:34:53DOO47已提哀-30G6S6GG6E£G7-9332&14M22fl141-0306:34:.000-M已完成-30.漏瀚6«抽667金新建一存储过程gf通过输入订单号、书目m及数量，实现绐该订单增加商品、更新订单总金额，并返回当前订单包含的商品总数(商品类别3orde：r_idvarchar20rbook_dvarchaz20/3book_svarchar20)qlupdate"order"setp

21、ayrnent-=payment+pricefrombackf"order"whereoidm(selectorderidfromorderbookwherebookid=bock_dlj-andbid=book:dupdatecrderbocksequanDixy=quantity+book_swhereorderidorder_idselectbockidfromorderboclcLwhereorderj.di=crder_idgoexecprocZ201400110014I幸一右去寸4Hrlw；缶；vtifeJfr+事户；丁始+或7鑫口队峪牧H:吕FF18；丁苗曲卓

22、U吕冬:iVff结果上消邕bockidiiiraHiimiil!1001!'"藏J舟建芹储过程P*3,谨过修改才丁单、指定商品叩介格，用R目该订章的新怠金额及优惠金中£皿螃蝠冬城gerd*r_idvarchar(20)rShookidvarchar20iGa_pixcevaxchax20iasEupdateordex1'actpa/icnt-payment-t(Gs_price-pxxcei*quantify)ironDooiCzCraerDoox-wherebookid=0book_±dandbid=febookidandoxderid=bord

23、erxd)曰select£xonorlexnL-wherAoid=gorder_idgcLdexecppu32C14001,1001,20斡一新建F催过程P*f统计冬T单总金颔也分布情也.订单佥颓划分情况如下;OSD兀、S1-100兀、101-150元、lSl-2007Kftl2Cl兀以上。R漏底炽璀做痢心、5初asJbegin-IdeclareSzeroLoriltyme,fiftytcfundreiirtrShundxeatohunfint/QU十Ji_»'»m果尽消息oidoroertrrepaymentdate2014)0'!1012014

24、-01-0107563200)-1354execproc32014001,1001,20申一新建二旨储过程P三京，统计琶丁单总金额的分布情逸。订单金额划分情况如下:。-50兀、51-100兀、101-150兀、151-200兀和201兀以上。3begindeclareQzerotofiftyInt,QfiftytofundredinzrQhundredcohunfinc,hunftoTwoh±nzfQmoretwohInt;selectSzerotofifty=COUNT(casewhenpayment>-0ANDpayment<-50then11end),Qfiftyt

25、ofundred=COUNT(casewhenpayment>51ANDpayment<100then'21hundredtohunf=COUNT(casewhenpayment>=101ANDpayment<=150then13hunftoTwoh=COUNT(casewhenpayment>=151ANDpayment<=200then,4'encmoretwoh=COUNT(casewhenpayment>=201then'5'end)from"order”printSzerotofifty;printQ

26、fiftytofundred;printghundredtohtmf;printQhunftoTwoh;printgmorerwohinj消息-警舍聚合或其他sm操作消除了Nun值。4斯建一存倩过程书目I制用尸还同时-一-购买过的购买次敖最多的前路书目名称及购买袂数.耳按照购买次敖林序。HcreateprocedureprocsSokidvarshaxi20)asbegindeclaredusexidint:3select3userid=uidfronorderbock,nordern#nu3»r"wherebootid=ooo)cidanaorderia=oia-and-

27、ordex-.-aser=uld23clecCuid,naitcrold,quancicy,a.bookid,atidefron*fu3erw/t,crderw/oxderbookloelecztcp330x1iqvaneicy)sumqaantityzbookidzticlfronorderbockbookwherebookidbidgmiiphyhnnkri,rir1aorderbysunavancityaesciawnerenil«8user_iaanduld-user*andoldordezidlandordexkook.bookld一a.bcckidorderbya.sxn

28、tquan*xtydess-endexecprccS10031串j姑果昌浦udnameok：auantibococidMtei103s孕明2014005210C1敖据后.系统岸埋102手明201400G11002数据结构T语言折审新建一存储过程咬邱，实现查询与指定用户ID购买过卜一相同商品的用户及购买过相同商品的数量,并按照相同商品数量降序排列。Acreateproceduregroc6Quseridvarchar(20)asbegin_selecta.uid,za.booJcidza.quantityfromorderbookrnorderuser*函果叵消息udnamebook

29、idquantityW李明10012'W新艳丽10062103李明10021103李明10031,(selectuidrnamezbookidzquantityfromorderbook,border11rnuserMwhereorderid=oldand"user11=uid)awherenuserrf.uid.u3er_idand."order”."user”=Huserrt.uidand."ordern.old=orderbook.orderidand.a.uxd!"usex".uidand&|列囱队，int,n

30、otnul)Fza.bookidza.quantity-orderbyquantitydesc厂endexecproc6102Q创建一触发器SL当往orderbook表中增加记录时，-实现商品库存相应减少；当修改orderbooka中利应商品数量时，实现商品数量，<iW一申一型建一健发一J；现商品库缸oraemcok表中增加记录时,当修改5dHb8表巾相应商品数量时，实现商品数量相应变动.ircreatetriggerzqlfor1nserlrupdateint-、，?quantityint;selectbookId一booked,Squantiyquont-xty11_：Z-19bootIdInc3Quantityint;,二-二:setbock.scock一book.stock-©quantitywher