(完整word版)葉磊數(shù)據(jù)庫(kù)

1、實(shí)驗(yàn)報(bào)告mlW71任務(wù)概述（任務(wù)說(shuō)明）一、存儲(chǔ)過(guò)程使用相應(yīng)SQL語(yǔ)句，完成如下操作：1. 新建一存儲(chǔ)過(guò)程procl,顯示指定用戶ID的訂單信息列表（訂單號(hào)、成交時(shí)間、訂單總金額、訂單狀態(tài)及所有訂單的平均金額），并成交時(shí)間降序排列。2. 新建一存儲(chǔ)過(guò)程proc2,通過(guò)輸入訂單號(hào)、書(shū)目ID及數(shù)量，實(shí)現(xiàn)給該訂單增加商品、更新訂單總金額，并返回當(dāng)前訂單包含的商品總數(shù)（商品類(lèi)別數(shù)量）。3. 新建一存儲(chǔ)過(guò)程proc3,通過(guò)修改指定訂單、指定商品的價(jià)格，并返回該訂單的新總金額及優(yōu)惠金額。4. 新建一存儲(chǔ)過(guò)程proc4,統(tǒng)計(jì)各訂單總金額的分布情況。訂單金額劃分情況如下：0-50元、51-100元、101-15

2、0元、151-200元和201元以上。5. 新建一存儲(chǔ)過(guò)程proc5，實(shí)現(xiàn)查詢購(gòu)買(mǎi)過(guò)指定書(shū)目ID的用戶還同時(shí)購(gòu)買(mǎi)過(guò)的購(gòu)買(mǎi)次數(shù)最多的前3名書(shū)目名稱及購(gòu)買(mǎi)次數(shù)，并按照購(gòu)買(mǎi)次數(shù)排序。6. 新建一存儲(chǔ)過(guò)程proc6，實(shí)現(xiàn)查詢與指定用戶ID購(gòu)買(mǎi)過(guò)相同商品的用戶及購(gòu)買(mǎi)過(guò)相同商品的數(shù)量，并按照相同商品數(shù)量降序排列。二、觸發(fā)器1. 創(chuàng)建一觸發(fā)器tg1，當(dāng)往orderbook表中增加記錄時(shí)，實(shí)現(xiàn)商品庫(kù)存相應(yīng)減少；當(dāng)修改orderbook表中相應(yīng)商品數(shù)量時(shí)，實(shí)現(xiàn)商品數(shù)量相應(yīng)變動(dòng)。2. 創(chuàng)建一觸發(fā)器tg2,當(dāng)往orderbook中增加記錄時(shí)，如果商品庫(kù)存量少于10,貝U不允許購(gòu)買(mǎi)該商品，并給出相應(yīng)提示。新建圖書(shū)訂購(gòu)

3、情況統(tǒng)計(jì)表bookstas（包含圖書(shū)編號(hào)、圖書(shū)名稱、圖書(shū)類(lèi)別、圖書(shū)價(jià)格和訂購(gòu)冊(cè)數(shù)，數(shù)據(jù)類(lèi)型自定），并根據(jù)數(shù)據(jù)庫(kù)的訂單情況將社科類(lèi)圖書(shū)的訂購(gòu)情況插入表中。創(chuàng)建一觸發(fā)器tg3,當(dāng)往orderbook表中增加記錄時(shí)，自動(dòng)更新bookstas表相應(yīng)圖書(shū)的統(tǒng)計(jì)信息4源碼，文檔化-新建一存儲(chǔ)過(guò)程proc1,顯示指定用戶ID的訂單信息列表-（訂單號(hào)、成交時(shí)間、訂單總金額、訂單狀態(tài)及所有訂單的平均金額），并成交時(shí)間降序排列。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-新建一存儲(chǔ)過(guò)程proc2,通過(guò)輸入訂單號(hào)、書(shū)目ID及數(shù)量，-實(shí)現(xiàn)給該訂單增加商品、更新訂單總金額，并返回當(dāng)前訂單包含的商品總數(shù)(商品類(lèi)別數(shù)量)。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-新建一存儲(chǔ)過(guò)程proc3,通過(guò)修改指定訂單、指定商品的價(jià)格，并返回該訂單的新總金額及優(yōu)惠金額。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-新建一存儲(chǔ)過(guò)程proc4,統(tǒng)計(jì)各訂單總金額的分布情況。訂單金額劃分情況如下：-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-新建一存儲(chǔ)過(guò)程proc5，實(shí)現(xiàn)查詢購(gòu)買(mǎi)過(guò)指定書(shū)目ID的用戶還同時(shí)-購(gòu)買(mǎi)過(guò)的購(gòu)買(mǎi)次數(shù)最多的前名書(shū)目名稱及購(gòu)買(mǎi)次

10、數(shù)，并按照購(gòu)買(mǎi)次數(shù)排序。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-新建一存儲(chǔ)過(guò)程proc6，實(shí)現(xiàn)查詢與指定用戶ID購(gòu)買(mǎi)過(guò)-相同商品的用戶及購(gòu)買(mǎi)過(guò)相同商品的數(shù)量，

12、并按照相同商品數(shù)量降序排列。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-創(chuàng)建一觸發(fā)器tg1,當(dāng)往orderbook表中增加記錄時(shí)，-實(shí)現(xiàn)商品庫(kù)存相應(yīng)減少；當(dāng)修改orderbook表中相應(yīng)商品數(shù)量時(shí)，實(shí)現(xiàn)商品數(shù)量

14、相應(yīng)變動(dòng)。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-創(chuàng)建一觸發(fā)器tg2,當(dāng)往orderbook中增加記錄時(shí)，-如果商品庫(kù)存量少于，則不允許購(gòu)買(mǎi)該商品，并給出相應(yīng)提示。createtrigge

16、rtg2onorderbookafterinsertasbegindeclarestockint,bookidint;selectbookid=s.bookidfrominserteds;selectstock=b.stockfrombookbwhereb.bid=bookid;if(stock<10)raiserror('庫(kù)存少于，不允許購(gòu)買(mǎi),16,1);rollbacktran;enddroptriggertg2-新建圖書(shū)訂購(gòu)情況統(tǒng)計(jì)表bookstas(包含圖書(shū)編號(hào)、圖書(shū)名稱、圖書(shū)類(lèi)別、圖書(shū)價(jià)格和訂購(gòu)冊(cè)數(shù)，數(shù)據(jù)類(lèi)型自定)，-并根據(jù)數(shù)據(jù)庫(kù)的訂單情況將社科類(lèi)圖書(shū)的訂購(gòu)情況插入表中

17、。創(chuàng)建一觸發(fā)器tg3,-當(dāng)往orderbook表中增加記錄時(shí)，自動(dòng)更新bookstas表相應(yīng)圖書(shū)的統(tǒng)計(jì)信息createtablebookstas(idintprimarykey,titlevarchar(20)notnull,categoryvarchar(20)notnull,pricefloatnotnull,quantityint);createtriggertg3onorderbookforinsertasbegindeclarebookidint,quantityint;selectbookid=bookid,quantity=quantityfrominserted;updateb

18、ookstassetquantity=quantity+quantitywhereid=bookidenddroptriggertg35執(zhí)行過(guò)程截屏曰一新建一存倍過(guò)程皿皿，顯示指定用戶工南訂國(guó)言息列表(訂單號(hào)、成交時(shí)間、訂單總金額、訂簞狀態(tài)及所有訂單的平均金額)，并成交時(shí)間降序有冽。曰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國(guó)谿果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金新建一存儲(chǔ)過(guò)程gf通過(guò)輸入訂單號(hào)、書(shū)目m及數(shù)量，實(shí)現(xiàn)紿該訂單增加商品、更新訂單總金額，并返回當(dāng)前訂單包含的商品總數(shù)(商品類(lèi)別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鑫口隊(duì)峪牧H:呂FF18；丁苗曲卓

22、U呂冬:iVff結(jié)果上消邕bockidiiiraHiimiil!1001!'"藏J舟建芹儲(chǔ)過(guò)程P*3,謹(jǐn)過(guò)修改才丁單、指定商品叩介格，用R目該訂章的新怠金額及優(yōu)惠金中£皿螃蝠冬城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催過(guò)程P*f統(tǒng)計(jì)冬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申一新建二旨儲(chǔ)過(guò)程P三京，統(tǒng)計(jì)琶丁單總金額的分布情逸。訂單金額劃分情況如下:。-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斯建一存倩過(guò)程書(shū)目I制用尸還同時(shí)-一-購(gòu)買(mǎi)過(guò)的購(gòu)買(mǎi)次敖最多的前路書(shū)目名稱及購(gòu)買(mǎi)袂數(shù).耳按照購(gòu)買(mǎ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敖據(jù)后.系統(tǒng)岸埋102手明201400G11002數(shù)據(jù)結(jié)構(gòu)T語(yǔ)言折審新建一存儲(chǔ)過(guò)程咬邱，實(shí)現(xiàn)查詢與指定用戶ID購(gòu)買(mǎi)過(guò)卜一相同商品的用戶及購(gòu)買(mǎi)過(guò)相同商品的數(shù)量,并按照相同商品數(shù)量降序排列。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&|列囪隊(duì)，int,n

30、otnul)Fza.bookidza.quantity-orderbyquantitydesc廠endexecproc6102Q創(chuàng)建一觸發(fā)器SL當(dāng)往orderbook表中增加記錄時(shí)，-實(shí)現(xiàn)商品庫(kù)存相應(yīng)減少；當(dāng)修改orderbooka中利應(yīng)商品數(shù)量時(shí)，實(shí)現(xiàn)商品數(shù)量，<iW一申一型建一健發(fā)一J；現(xiàn)商品庫(kù)缸o(hù)raemcok表中增加記錄時(shí),當(dāng)修改5dHb8表巾相應(yīng)商品數(shù)量時(shí)，實(shí)現(xiàn)商品數(shù)量相應(yīng)變動(dòng).ircreatetriggerzqlfor1nserlrupdateint-、，?quantityint;selectbookId一booked,Squantiyquont-xty11_：Z-19bootIdInc3Quantityint;,二-二:setbock.scock一book.stock-©quantitywher