數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)報(bào)告二

1、數(shù)據(jù)庫原理與應(yīng)用實(shí)驗(yàn)報(bào)告二姓名AAAA西安財(cái)經(jīng)學(xué)院信息學(xué)院?數(shù)據(jù)庫原理與應(yīng)用?實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)名稱根本表SQL語句練習(xí)實(shí)驗(yàn)室618實(shí)驗(yàn)日期2021.11.6一、實(shí)驗(yàn)?zāi)康募耙螅簩?shí)驗(yàn)?zāi)康?：1 .加深對(duì)表間關(guān)系的理解.2 .理解數(shù)據(jù)庫中數(shù)據(jù)的查詢方法和應(yīng)用.3 .學(xué)會(huì)各種查詢的異同及相互之間的轉(zhuǎn)換方法.根本表SQL語句練習(xí)實(shí)驗(yàn)?zāi)康?:1.理解數(shù)據(jù)庫中數(shù)據(jù)的其他查詢方法和應(yīng)用；學(xué)會(huì)各種查詢要求的實(shí)現(xiàn).實(shí)驗(yàn)?zāi)康?:1 .加深對(duì)數(shù)據(jù)庫相關(guān)性質(zhì)的理解；2 .各種約束性理解；3 .學(xué)會(huì)數(shù)據(jù)庫中數(shù)據(jù)的更新的方法.二、實(shí)驗(yàn)環(huán)境：Windows7,SQL2000三、實(shí)驗(yàn)內(nèi)容：實(shí)驗(yàn)內(nèi)容1:1 .查詢所有作者的作者號(hào)、

2、姓名信息.2 .查詢所有作者的姓名、作者號(hào)信息,并在每個(gè)作者的作者號(hào)前面顯示字符串身份證號(hào)：,表明顯示的信息是身份證信息.3 .查詢?cè)贑A州的作者姓名和城市.4 .查詢出版日期在2000.1.1-2000.12.3佗間的書名和出版日期.5 .查詢每個(gè)出版社出版的書.6 .查詢某店銷售某書的數(shù)量.7 .查詢有銷售記錄的所有書信息書號(hào)、書名、類型和價(jià)格等.8 .顯示所有的書名無銷售記錄的書也包括在內(nèi).9 .查詢已銷售書的信息書號(hào)、書名、作者等.10 .查詢所有出版商業(yè)business書籍的出版社的名稱.實(shí)驗(yàn)內(nèi)容2:在實(shí)驗(yàn)1的根底上,練習(xí)查詢語句的使用,包括計(jì)算列、求和、最大、最小值、各類選擇條件、

3、字符匹配、分組和排序,體會(huì)各種查詢的執(zhí)行過程,為簡單綜合應(yīng)用打下根底.1 .查詢書名以T開頭或者出版社號(hào)為0877,而且價(jià)格大于16的書的信息.2 .根據(jù)類型的升序和價(jià)格的降序在類型相同時(shí)顯示書的信息書名、作者、出版社、類型、價(jià)格.3 .查詢銷售量大于30的書名及銷售數(shù)量.4 .查詢?cè)?990.1.1到2000.12.31間,每本書的銷售總額.5 .查詢所有作者的所在城市和州名,要求沒有重復(fù)信息.6 .計(jì)算多少種書已被訂價(jià).7 .查詢每本書的書名、作者及它的售書總量.8 .計(jì)算所有書的平均價(jià)格.9 .查詢價(jià)格最高的書的書名、作者及價(jià)格.實(shí)驗(yàn)內(nèi)容3:1 .參照以上各表給出的主鍵、外鍵設(shè)置的設(shè)置要

4、求,在自己創(chuàng)立表中進(jìn)行相應(yīng)的設(shè)置.2 .向authors表中插入一行作者信息具體值自定.3 .數(shù)量超過100的商店增加10%的折扣.4,刪除1994.9.14的訂單.5 .刪除1中所建立的索引.6 .建立CA州作者所著書的視圖包括作者號(hào)、姓名、所在州、書名、價(jià)格、出版日期.7 .建立付款方式是現(xiàn)金cash的訂單視圖,9,建立CA州的所有商店的視圖.四、實(shí)驗(yàn)步驟：內(nèi)容一：1,查詢所有作者的作者號(hào)、姓名信息seled:frctjin.authorsiau_lnajiie與au_£najne11,au_idau_Inane1409-56-700?BennetAbraham2648-92-1

5、872Blotdiet-Hal1sReginald323A95-TT66CarstJiChecyl4722-51-5154DeFrmceJlichel8 .查詢所有作者的姓名、作者號(hào)信息,并在每個(gè)作者的作者號(hào)前面顯示字符串“身份證號(hào)：,說明顯示的信息是身份證信息usepubs-、select凱L_lnam生&TL_fnane,身份證號(hào)；'as身份證號(hào),au_：LcifromAntliersuuaijlnajn號(hào)au_fTi3jne身份證號(hào)an.id1EtrmfltAbraham身份證號(hào):40P-5&-70082ELotchet-HalIsRegnmald身份任S48-S

6、2-18723CarsonCheryl身份證號(hào);Z36-S5-770C4D&FranceMichel身份證號(hào)二722-51-5464dalCastilloInnes身份證號(hào)2712-45-13576Dull身份證號(hào)：427-17-23107GreenWarjone身份證號(hào)工213-45-39158rFftYiAnrninpstAr券7TAsJMA9 .查詢?cè)贑A州的作者姓名和城市usepubsselectau_Inane,afnam*,cityfrom.authorswherestates'CA)“z1au_lnajn.eau_fnam.ecityVThiteJohnsanJl

7、enloPark2GreenWarjorieOakland|3CarsonCherylBerkeley4O'Lear中MichaelSan1口父它4.查詢出版日期在2000.1.1-2000.12.3億間的書名和出版日期usepunsselecttitlejpubdatefromtitlesvhercpubdatet*eteen'2000.L1'and12.31'jintitlepubdte200008-060i;33;54.123N&tEtiquette2000-08-0601:33:54,140ThePsychologyofCanputerCooki

8、nc10 查詢每個(gè)出版社出版的書dLfrcnPl±i1Lakers,tit1espub_rrai.etjllE1NevMqon.B.匚證3BmIsJiUserFriendly?iNeuNdonEocksCanpctsrPhabieAKDJlaiLL-FhobicI.3HevMaonBDcksCaokiTLgvithCinpEtEd：Surrcpti.J4NevMaon.Bocks3nLeitinnalScGiirztys2JTcirAlsoiritlbNevMoonBcrnks：Fifty丫亡a匚bzaBucklr虐haltPalace.16MirvMoonBorJks：IsAr.f

9、fMfheEr.emyTWtvMaanBaoks1LifeVithoutFeareNevMfliin.BotilrsNeiEilqu&ttC;qWevBocliCrLi.or.SjL電或與andCarliti亡.由NL.10NevMoonBods'FtolcngedDa*aDecrkaxicniFooh.11NwMoonMocksSscrat?o£SilicoriValley12NevMamiHocksSilicenYilleyGartEmorMicIrcats13NevMaon.BocksStiM-ehtT&1上Kbout:qNrvMoonBodfsSush

10、iiAnjrcme?yiwfloonriiX'lisTh舊FusyFcji.versDatitas8In：1MuMaonTiiMjftTh*Gcnjirnt；7WfvMnnn.IfioeterTHfPsyirhnlnjyafC口叫hTpt口齒in:EWevMoonEoclxrYou匚5屈aCoF.puterJtr<s?£,l13Einnot&Hudl為BinJaItUs«rFricnjlT?20Buuiirt良HsidLeyCanpeterPhobicAN?San-Fhobic：I.21Euuiet員HijidlcyCaokmE-vitkCanputS

11、mrrcpti-.口二l.£lTTJ1匚j_-1c：jiArr1；1:上111 查詢某店銷售某書的數(shù)量7.查詢有銷售記錄的所有書信息書號(hào)、書名、rj?ppi:n?seLscttitle,qty.ztarnaiiafrcottitles,sales,stores|Ul12j<5etitLeqt了st口r.najiEEmtI?TtUserFrieTiilly?ButIsITUj&tFriendlly?EntIs1ITUz&rFriendUy?EutIsIttf£eiFri&nd!lyButIsItUseiFriendlyButIsTtUseiFri

12、endly535075104.?ricThe民QadlbookricTheReadBnokSricThe民wadEoakErictheReadBookErictheReadBookErictheReadBooknTutIsItUse.tFriendly20ErictheReadBookVButIsItUseiFriendly20ErictheRradBook7ButIsTtUsciFcicmllySOErictheReaiJBookUEutIsItUsciFricjiilly?25ExittheReadBocklButI苫ItUseiFriendlly?20EncLheRradBookJ2Bu

13、t】WItUsciFiieliilly12GEtittlieiReadDuukJ3EutIsItUjtiFtiorLdl"16ErictheReadBookButIsItUserFirickndl3r今%EnctheReadBookJ6ButI.ItUaeiFEitnily"15ErictheReadBookhutLItUjciFriendly10&nctheRcaJ,Book17butIcItU;orFriondly'%eicthoRaad.Book.1gtlutIcItUrorFriendlyr1IEeicthtRoadBook1AEut1.工tUser

14、Friendly9IQEeicth«>RaadEaok70HutIcItllacrFriondly,2EEricthoRoadBook£1ButIfItUnrFritndly,3口Ericth*AndBook?2?38mput*tPhibi.ANPNjn-FlwbicL一HnputgtFh如.ANDBaa-FhcbLC.一53Era?thfrAndBootExicrhtR«a.dBook類型和價(jià)格等usepubsselecttitle_zd.title,typepricefromTitleswhereYtd._sales30|kl皿title_idtitle

15、typepr7T"BU1032TheBusyExecu,+ive,sDa+abas*Guidehusiness19.2BUL111Coo-kxngwitliConputers：Surrepti.,business11.3BU20T&fouCanCombatGonipijterStress1tmsiiness2.!4_BUT83?StraightTalkAboutComputersbusiness19-5MC2222SiliconValleyGastronoanicTreatsifcod_coolt19.6MC3021TheGcurmetWicrowaemod_cook2.S?

16、JPC1O36ButIsItUserFriendly?popular_comp22.,8色十月nf-ilinnnVsl1AVninniilsrnnimn；Ti.8 .顯示所有的書名無銷售記錄的書也包括在usepubsselecttitlsfromTitlesltitle1ButIsLtUserFriendly?2ComputerPhobicANDNon-PhotiicI.3CodkirigirilhComputers：Surreptix.qEmo-tionalSecurity:AMewAlgorithjn.5FiftyYearsinBuckinghamPalace.6IsAngertheEne

17、my?7LifeWithQutFearSITe-tEtiquettegOnionSjLeeks?andGarlxc*Cooki.,.10ProlongedDataDeprivation:Four,iiSecretsofSiliconValley12SiliconValleyastronoiiicTieats1N9 .查詢已銷售書的信息書號(hào)、書名、作者等usepubsselectQty37itLs.litLe_Ld,litle,au_lname,a.u_fnamefrontSaltsTitLfeE,AuthorsiwhetfeQty>0LlQty,title_idtitleau_lnant

18、eaiLfnameUEPCI035EatIsItUserFriefudly?BennetAbraham25PC1035ButIsTiUserFriendly?Blotchet-HallsReginald35fClb35ButIsItUserFriendly?CarsonCheryl15PC1035ButIsItUserFriendly?DeFtartceMichel55FC1035ButIsItUserFriendly?delCastilloInnes55PCI035HutIsItUserFriendly?DullAnn75FC1035ButIsliUserFriendly?CreenMarj

19、orie35FC1035EutIsItUserFriendly?GieenMorningst35pin赤ButIsItUserFriendly?GxinglestiyBur-tW5PCI035EutIsItUs&rFrieridly?HunterShrvl10 .查詢商業(yè)business書籍的出版社的名稱5agsei&,title|FromPublishers,Titleswheretype-5tusiness?JJIUpub_maiiiEtitli?NewMmBeaksBinnet艮HardleyTheB11S7sDatabaseCuidsTheBusyE

20、xecutive'sDatabasetuideAlgodatInfosystemsTheBusyecutiveJsJatabise<?uideFiveLakesPublishingTheBusyEzecutive'sdatabaseGuideRamcinaPublishersTheBusyExecutiveJsPartabaseGuideGGG也GTheBusyExecutive1sdatabaseGuideSeootne7BeaksLucernePublishingTheBtiS7Eiieciitiv&,sPatabaseGuideTheBusyEisecuti

21、ve"sP3rtabaseGuideNewMounBooksCookingwithCompaters:3urrepti.0R-iTrfcnSiT4nr11anrf"nnlriNcriiri+Ti廣nimi-ii+q丁*Wi-jt*rptt+n內(nèi)容二:1.查詢書名以T開頭或者出版社號(hào)為0877,而且價(jià)格大于16的書的信息.UAbPUIJSselecttitlejcountqtyfrajmTitles,Salesvhereord.datebrweerin1990.1'wrid'2000,12,frouptylitle¥ithcub4titleC無列名B

22、ut1$ItUserFriendly?21?CiJLputeEPKftbicANDITMV-PhobieI.21JCookingwithCcrnputers;Su匚repl»21EmotionalSecuiity;ANewAlgorithm21FiftyYearsinBuckinthaniPalace.21)IsAngerthenemy?21fLifeWithoutFear218N&tEliquette21)OriicmsjLeekandGarlic;CookiH«v215,查詢所有作者的所在城市和州名,要求沒有重復(fù)信息Llislatecity1CkSsrk&

23、;ley2匚ACuv-alu3CAMenloPark4CkOakland5ChPaleAlto6aSw.FranciscQ7HA5anJose6CiLVacaville9CkWalnutCreep10INGarynK5Laurence12而RoclsviLle13HIAmArbor14ORCorvallisISTHNashville16urSaltLakeCityusepubsselectdLstinctcitfromAuthors6,計(jì)算多少種書已被訂價(jià)usepubsselectcount(typeas'已破定價(jià)書的種類戮量.,fromTitleswheueprice'>

24、;0|I己祓定價(jià)書的種類數(shù)量IS7 .查詢每本書的書名、作者及它的售書總量usepubsSelecttitle-auInane.au_fnianeYtdsalesFrantLtlesauthors,titleaiUthorffhere(authors,em_id-titleauthoT.auid)and(titlestitle_id=titleauthortitle_idTLtl*aufnanwThelusyDaftabasGioid«firmerWsTjarie現(xiàn)的2TheEujyEsoeutsDcrta.basoGuide3omrtAbioham,0953Cockiinjvith

25、rSurireirti+ionrRsilanc.0"LearyWirhUPl187fiCockinevithCcnputrsxBdlu厚.KaJcaihor5-tearn?36768 .計(jì)算所有書的平均價(jià)格9 .查詢價(jià)格最高的書的書名、作者及價(jià)格usepubsSelecttitleprice,au_lname.au_fnnireFromtitlesAuthorsTltlea.uthorThereAuthors.au_ic-TitleanthDr.iLi_itiia2LLtitles-TitLeathor.title_idurice-(selectiraxr)ricefroEtitle

26、s-itlLte1au._lnaiiBarLfiKUn*lButItUehFriwidly712.9S1UUitmr匚h#ryl內(nèi)容三:1 .參照以上各表給出的主鍵、外鍵設(shè)置的設(shè)置要求,在自己創(chuàng)立表中進(jìn)行相應(yīng)的設(shè)置.2 .向authors表中插入一行作者信息(具體值自定)usepubsinsertintoauthors(au_£djaulnama.au_fname.phone,contract,zip)values('口O7'qLQi'whou'010710100,3 .數(shù)量超過100的商店增加10%的折扣jsepubsUpdatediseourtsS

27、etciscount-1.l-discocntVherehighqty100即影響的行數(shù)為：fl4,刪除1994.9.13的訂單usepubsDeletefrorsalesWheieord_date=,1994-9-13,?肝影響的什翦為七什結(jié)果：usepubsDeletefroisalesWhereord_date=*1994-9-13,select*frorsales升njid口tHLA*y+rn曾ti+l_3170£6£129161090-OB-24UO:UU：OUOOUb0We-削FCBSB62_70£7F21Z11V02-O616OOlOOiOLDQO4

28、0Wot30TC3218370f7P21211992-0S-15OOrOO:OC+DQO20WE30TC420?45T0A77124FZ121P3097*1992-00-150O:00:0C.DQO1BD3-05-2DOOiOOiOO.DOO20酗3D20Km60TC7T?7PS：1379671JIFJD87119W-05-2BU0!JUlOL.UOU26UeidDFS21DG5 .除1中所建立的索引6 .建立CA州作者所著書的視圖包括作者號(hào)、姓名、所在州、書名、價(jià)格、出版日期-use-puEsgocreatevievBooks_Anthars_Tit1easselectau_id.au_In

29、ane,au_fnane,staxe,titlt,prictjpubdatefremAuthors.Titlesvherertat«-hCAJEQCI國&今己成功先成.足east工frojr.fioDts1Aijthor?TitIpluau=idaM=?nuiotitlepricepubdate172-32-117fiVhit®JohTISTWIiCATh*BusyKxpcutivfTsJi匍rM?四客值Gut19.99001BOI-OH-12DD!OD!Oa.OO|172-32-：176VhitoJolxn.sonCACsokir4vithCgir.putorss

30、.SurscptL4U.SEOO1991Od0?DJiXiOQ.OD172-32-17fiWhiteJohnSPOTCAYou匚河CombMCcnpuTerStressI工明口01991-OB-3DDOsilOsOO.00172-32-1176VhitsJQ】5$gCA5t,Lu,4VtInLkAboutCunjuLeifIS.9900l的1-0622DJ;QO;CQ.OD172-32-1176VhitiJohnco(nCASilxccnVall»yGaitronomicIrt3trI0.S90Q1P91-O6-09ODsQOsOQ.00172-32-1176VhitsJolinso

31、ciCATheGcurnetMicicvave2.99001991aWD3:X:CQ.OO173-33-1H6VhitaJohnrwiQITh®PsyckoLngycfConputfrGaQ&LugMULL2DJD-0&-OSD1i33iG4.121T2-3S-117rtiVhitaJuhJLSOdCADilIsItUe式Friendly?Z2.9QOO1991-0-30DJ:X:QQ.OO7 .建立付款方式是現(xiàn)金cash的訂單視圖工口createvzbwSales_payiermasseLectoitLnmii.ord_date_,qty,title_idfram.

32、Salesiherepayterns-'cash?|SO,fl俞吟已咸勸完成,selact*fromS3les_paytemrdnu>orddateqtytitleid8 .建立CA州的所有商店的視圖usepubstreateStoreselectstor_id.?stor_najne,stce_address,city,state_,zi;fromStoresvheraC£|涔已成功完成Qsel&ct+fromStore|mist口匚.idstorenamestar_addresscitystate工ip1066Earnuft'w667PasadenaAve*TustinCA327397C67News&Erews577FirstSt.LosGatosCA967459001S7S96Ff、qativeBook5hop89MadisonS-tHFreJiontarasepxiDs駛selectstar_id<,stor_