SQL實(shí)驗(yàn)四數(shù)據(jù)更新與視圖

數(shù)據(jù)庫(kù)系統(tǒng)概論實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱(chēng)數(shù)據(jù)更新與視圖實(shí)驗(yàn)日期2018/4/26學(xué)生姓名丘雨倫學(xué)號(hào)1610020043班級(jí) 計(jì)算機(jī)2班實(shí)驗(yàn)?zāi)康氖褂孟鄳?yīng)SQL語(yǔ)句，完成：（1） 數(shù)據(jù)更新（2） 視圖實(shí)驗(yàn)內(nèi)容利用SQL語(yǔ)言完成:①數(shù)據(jù)更新、②視圖。實(shí)驗(yàn)器材?PC機(jī)一臺(tái)?MicrosoftSQLServerManagementStudio實(shí)驗(yàn)過(guò)程及結(jié)果記錄及實(shí)驗(yàn)結(jié)果分析數(shù)據(jù)更新1：根據(jù)訂單信息更新order表中的payment(訂單總金額)字段的值。Bupdateorderinfosetpayment=(selectSUM(price*quantity)frombook,orderBookwhereorderBook?bookld=book?bookidandorderlnfo? ?ordered)□select*fromorderinfo100%▼曲結(jié)果肉消息1orderiduseridorderStsiteldpaymentordertime1 ]2014001|101 4 109 2014-01-010T:56:32.0002014002 102 4 140 2014-01-03 08:34:38.0002014003 102 2 143 2014-01-03 21:34:53.0002014004 102 1 11? 2014-01-0? 08:50:29.0002014005 103 3 117 2014-01-08 15:50:28.0002014006 103 4 73 2014-01-08 23:28:28.000? 2014007 104 4 175 2014-01-09 12:50:33.0002：新建圖書(shū)訂購(gòu)情況統(tǒng)計(jì)表bookstas(包含圖書(shū)編號(hào)、圖書(shū)名稱(chēng)、圖書(shū)類(lèi)別.圖書(shū)價(jià)格和訂購(gòu)冊(cè)數(shù)，數(shù)據(jù)類(lèi)型自定),并根據(jù)數(shù)據(jù)庫(kù)釣訂單情況將社科類(lèi)圖書(shū)的訂購(gòu)情況插入表中?！鮟reatetablebookstasI(<L ”—■bookidintprionarykeytitlevarchar50*not-rmll,categoryvarchar(20pricefloatnuJ.L.ordertimgsinXnot-nul1)；Elinsert-intoboolcsts讀select,book?bookld,TitLe,category,Price?SUM(quantity)frombook?category,orderBookwherebook?bookidorderBook?bookidandcategory?categoryld^book-categoryld:址迅category=,社會(huì)科學(xué)類(lèi)’groupbybook,booild,Title,category.Priceslelect*frombookst対00%-二1結(jié)果匕消息Lookldtitiecategorypriceordertimes1j1004!經(jīng)濟(jì)學(xué)原理社會(huì)科學(xué)類(lèi)64221005中國(guó)哲學(xué)簡(jiǎn)史社會(huì)科學(xué)類(lèi)36331006敎肓乙理學(xué)社會(huì)科學(xué)類(lèi)3653：新建用戶(hù)訂購(gòu)統(tǒng)計(jì)表userstns（包含用戶(hù)編號(hào)、用戶(hù)名、訂單數(shù)量、總金額，數(shù)據(jù)類(lèi)型自定），并根據(jù)數(shù)據(jù)庫(kù)的訂單情況將活躍用戶(hù)（正常使用、鎖定）的情況插入表中。Rcreatetableusr：T?;iniprinarykey“Mevarchar20'??ocder^ini、；；!：?paynrntfloat：-'1RSlsertselectdistinetusej7n￡o.user"].us^rTif□.um*?.ouI'rd-i7.fo.userid)si：Ji(payneni'fromuserInfo-ordeclnfo?isecStatewherewerlnfo?xwctJd-orddnfo.usurlDe>1user7nfo.userStat^TD-userState.userStMuldai'.d(userState.usecSi^rtc-*正第便用'?iusGcStdc.userStat5-質(zhì)定'groupbyj?rrlnfc.Id.r.-aDrRselect分fromuserstasl100%?useridH4neorJer,ptLyneni1；1C2i王田34CO2ICG2ISO3104117S☆一開(kāi)始犯了一個(gè)慣性思維錯(cuò)誤，聯(lián)想到上而一道題里的總金額，后來(lái)檢查表發(fā)現(xiàn)，它根據(jù)不同類(lèi)型分組訃算的結(jié)果是不同的，需要理淸題目究竟要我們求得是什么，很容易粗心犯錯(cuò)4：給用戶(hù)表增加一字段level(等級(jí))，初始值為0,根據(jù)各用戶(hù)訂單總金額來(lái)給予評(píng)級(jí)(1:100?200元，2：200?300元，3:300元以上)。SQLQuery!.sql-L...okstore(sa(51))*X|altertableuserinfoaddlevelintdefault-0|100%■島消息命令已成功完成。b-altertableuserinfoaddLevelmtdefault□Eupdateu^crlufosetlevelselectcasevhen ?1003j；J；paynent200then1:i200 :?tnpayment.)300then2vhcn 曲；300then3endlevelftromorderovhereuscilnfo?useiIc?Q：leiInfo?uaeild；￡Select?fromuserln-fo|100%▼1userid泓三Sex]password101iiserStateTD3birthday1933-03-198：8：8.O3Dlevel)\101乙ice王麗010211934-01-1803:03:03.03333103李明]10311935-05-158:8803d]4104張艷麗010421933-12-0303:03:01OX361CB11051193307-058：8：O3.OX)MU1J.5:用戶(hù)102通過(guò)提交了一訂單購(gòu)買(mǎi)1001號(hào)圖書(shū)1本，請(qǐng)問(wèn)如何在數(shù)據(jù)庫(kù)中完成上述操作？(假設(shè)訂單號(hào)編號(hào)連續(xù)，要求完成訂單信息、庫(kù)存更新、訂單總額及用戶(hù)等級(jí)信息)。(1)首先在相關(guān)表中增加相關(guān)購(gòu)買(mǎi)記錄

insertorderInfovalues?^014008'1C2??'2’、'0’?'20L8J42812:5252’)insertorderBookvalxies*2014008*'100L'T‘；BSelect*fromoiderLif□select&fromord?rEook|100%▼二結(jié)早山消忌2??????????????????20)40021024UO2014-O1-CBCB：34：Z8.O333201400310221432014-01-CG21:24:E3CD3a2014004to?1U72014-01-07C&旳公OR5201-500610331172014-01-Ce16:EO:28.OD3620140001034732014-01-C6232628COD?201400?10441752CH4-CH-CO12:E0:33.ODD8201400810?202016-04-2612:^52ODDQrdcridloolldquant；ty：2014001=1001120H001100222014002100412014002100522014003100212014003100&3201400410012201400410031（2）更新相關(guān)信息□select*fromorderlnfo|orderinfo中的payment：□select*fromorderlnfo|-updateorderinfowherebook?bookld-ordcrBook?bookLiancorderlnfo?wherebook?bookld-ordcrBook?bookLiancorderlnfo?ordErId=ord8rEook?orderld)100%▼J結(jié)果Jj消息orderIduseridorderSeldpa>anentordertime1|2014001|10141092014-01-0107:56:32.0002201400210241402014-01-0308:34:38.0303201400310221432014-01-0321:34:53.0004201400410211172014-01-0708:50:29.00052014005103311?2014-01-0815:50:28.000620140061034732014-01-0823:28:28.0007201400710441752014-01-0912:50:33.000820140081022392018-04-2812:52:52.000庫(kù)存更新:

updatebookxertxtorlcAaount-sux?xtocl<frojkbool?bL,爛3clcctbook,oookld.tLtlccatczoty.category,pticc-stockAnoxu^tsm(quantity；.sungtockironbook,category.onieTBGokvherebook,caxegoryldcategory.ca*eg：or5rIddbook.bookld^ordcrEock.bcokld?rovpbybcok.'ootid,ti.l"r.^tcgory.catceoc/.pc::c.nt4)ckAmount：b2vherebl.bcokid=b2.bookid?-selectKIrout?ook|100%?二1結(jié)杲looUdTickauthorPressPricecateeoryldstoctAnouxiC]；imi?數(shù)拆磔境舷?王冊(cè)言翎育岀扳社39I186210)2娜結(jié)枸C語(yǔ)言版嚴(yán)即鎖猜華大字岀扳社35I24231003計(jì)算販絡(luò)謝希七電子工址出坂社39I464LM4爲(wèi)齊學(xué)原迴亙昆筆華大現(xiàn)飯社ei22151036中國(guó)営字罰史馮友蘭北京大字出扳社382461036娠心理堂型學(xué)科學(xué)岀腸社3621?0用戶(hù)等級(jí)信息:casewhenwhenwhenfron.□第果3消息j?iiri^paym*-nt100；iTi>.?-imcasewhenwhenwhenfron.□第果3消息owparent200；wml：pDymcrrt 300then2s^i：^d-znient:300then3end：levelwhcieuset ucciId-oidewhcieuset ucciId-oidelLifo?u^cild：useridaaneSexpess^ori?iserStateIDbirthdaylevel:10119KE110131993-03-1900:00:03.000Iice三麗010211984-01-1800:00:0300031G3I1031)996-05-1500：00：CD000I104張艷朋010421993-12-0300:00:03000I1C6110611999-07-2600:00:01000WLL6：給數(shù)據(jù)中庫(kù)存在100本以上的圖書(shū)增加庫(kù)存，其中社科類(lèi)圖書(shū)分別增加200本、理工類(lèi)圖書(shū)分別增加300本:bookset3tockAmovii-t~stockAanouiit200wherestockAmouot100categoryId-72'ElppdatebooksetstockAmountstockAmount300#ierestockAjnount>100 IcategoryIdTselect*frombook]100%▼二1結(jié)果J：1消息bookldTitleauthorPressPl"；GCcatejjoxyllstockAmouni1Hooi：數(shù)據(jù)庠系統(tǒng)原埋王蜩高等教肓出版社39148621002數(shù)據(jù)結(jié)構(gòu)C涪言版齊華大學(xué)出爍社351B4231003計(jì)算機(jī)網(wǎng)絡(luò)謝希仁電子工業(yè)出版社3914641004經(jīng)濟(jì)學(xué)原理豬華大學(xué)出版社6422151005中國(guó)哲學(xué)簡(jiǎn)史馮友蘭北穴大學(xué)出版社382461006教肓心理學(xué)莫雷教學(xué)科學(xué)出版社3623707:將數(shù)據(jù)庫(kù)中處于鎖定狀態(tài)的用戶(hù)的狀態(tài)更改為正常使用，同時(shí)重新設(shè)定其密碼為9000,-updateuserInfosetuserStatelD'『password'00C0"whereuserSselcctusdtStatdDfromaa;ci;StatewhereuserSt猶&‘做定’select*fromuserlnfo|二結(jié)果匕消息1userid攬三Sex1password101userStatelD3birthday1993-08-1900:0000.000Level1：1012102王麗010211984-01-1800:0000.00033103李明110311996-06-1500:0000.00014104張艷麗0000011993-12H3300:0000.00015105劉大海110511999-07-2600:0000.000HULL8：將數(shù)據(jù)庫(kù)中訂單狀態(tài)為“未提交”的訂單狀態(tài)修改為“已提交S同時(shí)給予該訂單九折優(yōu)惠EJupdateorderinfoIsetorderStateld'2’,paymentpayment:?0?9whereorderStateld叢匚二二二二“selectorderStateldfromorderStatewhereorderState：'未提交’IIselect*from,orderInfo|100%二結(jié)果J消息1orderiduserII：101!orderSt^tzeld4pa^nnent109ordertime|2014001?2014H31-0107:56:32.0002201400210241402014-01-0308:34:38.0003201400310221432014-01-0321:34:53.000420140041022105.32014-01-0708:50:29.0005201400510331172014-01-0815:50:28.000620140061034732014-01-0823:28:28.0007201400710441752014-01-0912:50:33.000820140081022392018H34-2812:52:52.0009：將數(shù)據(jù)庫(kù)已經(jīng)停用的用戶(hù)及訂單信息全部刪除100100%■：.-deletefromorderBookwhereorderld.in](selectorderBook?orderldfromorderBook,orderInfo?userInfo?userStatev^hereuserS-tate=?用'anduserInfo.userStateID=userState.usexStateldanduserln.fo.userld.=orderln,fo.userid.andorderlnfo.orderId=orderBook.orderld)日select*from.orderBook國(guó)結(jié)果肉消息orAerT1bookidqu.antity1\2014002!j10041220140021005232014003100214201400310063520140041001262014004100317201400510012820140051003192014006100211020140061005111201400710011122014007100411320140071006214201400810011-〕deletefromorderinfowhereuseridin(selectorderlnfo?useridfromorderinfo,userinfo,userStatewhereuserS-tate=,用'anduserlnfo.userStateID=userStstte.userStateldanduserlnfo?userId=orderInfo?userid)-select*fromorderlnfo|100%▼二結(jié)果：消息orderlduseridorderStateldpaymentordertime1：2014002：10241402014-01-0308:34:38.0002201400310221432014-01-0321:34:53.000320140041022105.32014-01-0708:50:29.0004201400510331172014-01-0815:50:28.000520140061034732014-01-0823:28:28.0006201400?10441752014-01-0912:50:33.000720140081022392018-04-2812:52:52.000-deletefromuserInfowhereuseridin(selectuseridfromuserinfo.userStatevhereuserState=,1*^用anduserInfo.userStatelDnuserStote?userStateld)Bselect*fromuserinfo100%▼J結(jié)果匕消息useridnameSexpassworduserSelDbirthhylerel1[102i王麗010211984-01-1800:00:00.00032103李明110311996-06-1500:00:00.00013104張艷麗0000011993-12-0300:00:00.00014105劉大海110511999HJ7-2600:00:00.000NULL要把已經(jīng)停用的用戸在各個(gè)表之間的信息都考慮到一并刪除二：視圖1：創(chuàng)建視圖Viewl,查看各訂單的訂單號(hào)、下單用戶(hù)、訂單總金額和訂單狀態(tài)-createviewvievlasselectorderld,userid,pa^nnent,orderstNtmfrom,orderInfo,orderStatewhereorderInfo?orderrStatEldnordEirState?orderS*tarteld100%-雷消息命令已成功賓成。日口視朗aD親統(tǒng)視圖S回dbo.viewle尸rr=i皿mE:select*fromviewl100%v曲結(jié)果由消息orderid■useridpaymentorderState1=?2014002|102140已完成22014003102143已提交32014004102105.3已提交4201400510311?已支何5201400610373已完成62014007104176已完成7201400810239已提交2：創(chuàng)建視圖View2,查看各圖書(shū)的編號(hào)、作者、名稱(chēng)、出版社、價(jià)格、訂購(gòu)冊(cè)數(shù)I 二一_create^vievj/igv2asselectbook:,bookairthor,titLo.press,pricp.s?unorderEock.quantity,ordertiines?fromhook,orderBookvherebook,bookId=orderBoo^.bookLdgroupbybook,bookid.author?title,proew,pirioBi100%?肉消息京令已跡芫戍。-!select*|fromviev/2ioo%-二I結(jié)果山消息bookidauthortitlepresspriceordertimes1]10011王珊數(shù)據(jù)庫(kù)系纟充原理高等教育出版社39621002嚴(yán)蔚敏藪據(jù)結(jié)構(gòu)c語(yǔ)言版唐華大學(xué)出版社35231003謝希仁計(jì)聲機(jī)網(wǎng)絡(luò)電子工業(yè)岀版社39241004曼昆經(jīng)濟(jì)學(xué)原理猜華大學(xué)岀版社64251005馮友蘭中國(guó)哲學(xué)簡(jiǎn)史北京大學(xué)岀版社38361006莫雷教育心理學(xué)教學(xué)科學(xué)出版社3653：創(chuàng)建視圖View4,查看社科類(lèi)圖書(shū)的基本情況，并保證以后對(duì)該視圖的修改都要滿(mǎn)足專(zhuān)業(yè)為“社科類(lèi)圖書(shū)”這個(gè)條件asselectbookid,titie,author,press,price,book?categoryld,stockAn.ountfrombook,category!wherecategory,category^-社會(huì)科學(xué)類(lèi).andbook?categor3fId=categcry?categcryldwithcheckoption100%▼由消息命令已成功完成。Eselect?fromviev/<|100%▼口結(jié)果Z消息bootidtitleautliorpricecat&gorylJRtockAnount1:10041經(jīng)^學(xué)原理曼昆育化犬學(xué)出版社G4221Z1005中國(guó)哲學(xué)簡(jiǎn)史馮友蘭北京犬學(xué)出版社382 431006教冒心埋字莫雷頷字科字岀版社362 3704：通過(guò)Viewl,查看訂單總額在100元以上(含100元)且狀態(tài)為已完成的訂單號(hào)、下單用戶(hù)、訂單總金額3selectorderId,userid,pajnrientfrom.viewl?ord.erState|wherep^3Tnent->=100andvievl.orderState=order5tatb?ord^rStateandorderState?o工de:rSt3te=r已完成'100%▼二結(jié)果匕消息orlerlluseridpayment12014002102140220140071041755：通過(guò)視圖View2,查看訂購(gòu)冊(cè)數(shù)在平均值以上的圖書(shū)編號(hào)、作者、名稱(chēng)、出版社、價(jià)格、訂購(gòu)冊(cè)數(shù)，并按訂購(gòu)冊(cè)數(shù)降序排列Ejselectbcokld,author,titie,press,price,ordertimzsfromviev2whereordcrtincs>(