数据库原理与应用实验报告二

1、数据库原理与应用实验报告二姓名AAAA西安财经学院信息学院?数据库原理与应用?实验报告实验名称根本表SQL语句练习实验室618实验日期2021.11.6一、实验目的及要求：实验目的1：1 .加深对表间关系的理解.2 .理解数据库中数据的查询方法和应用.3 .学会各种查询的异同及相互之间的转换方法.根本表SQL语句练习实验目的2:1.理解数据库中数据的其他查询方法和应用；学会各种查询要求的实现.实验目的3:1 .加深对数据库相关性质的理解；2 .各种约束性理解；3 .学会数据库中数据的更新的方法.二、实验环境：Windows7,SQL2000三、实验内容：实验内容1:1 .查询所有作者的作者号、

2、姓名信息.2 .查询所有作者的姓名、作者号信息,并在每个作者的作者号前面显示字符串身份证号：,表明显示的信息是身份证信息.3 .查询在CA州的作者姓名和城市.4 .查询出版日期在2000.1.1-2000.12.3佗间的书名和出版日期.5 .查询每个出版社出版的书.6 .查询某店销售某书的数量.7 .查询有销售记录的所有书信息书号、书名、类型和价格等.8 .显示所有的书名无销售记录的书也包括在内.9 .查询已销售书的信息书号、书名、作者等.10 .查询所有出版商业business书籍的出版社的名称.实验内容2:在实验1的根底上,练习查询语句的使用,包括计算列、求和、最大、最小值、各类选择条件、

3、字符匹配、分组和排序,体会各种查询的执行过程,为简单综合应用打下根底.1 .查询书名以T开头或者出版社号为0877,而且价格大于16的书的信息.2 .根据类型的升序和价格的降序在类型相同时显示书的信息书名、作者、出版社、类型、价格.3 .查询销售量大于30的书名及销售数量.4 .查询在1990.1.1到2000.12.31间,每本书的销售总额.5 .查询所有作者的所在城市和州名,要求没有重复信息.6 .计算多少种书已被订价.7 .查询每本书的书名、作者及它的售书总量.8 .计算所有书的平均价格.9 .查询价格最高的书的书名、作者及价格.实验内容3:1 .参照以上各表给出的主键、外键设置的设置要

4、求,在自己创立表中进行相应的设置.2 .向authors表中插入一行作者信息具体值自定.3 .数量超过100的商店增加10%的折扣.4,删除1994.9.14的订单.5 .删除1中所建立的索引.6 .建立CA州作者所著书的视图包括作者号、姓名、所在州、书名、价格、出版日期.7 .建立付款方式是现金cash的订单视图,9,建立CA州的所有商店的视图.四、实验步骤：内容一：1,查询所有作者的作者号、姓名信息seled:frctjin.authorsiau_lnajiie与au_£najne11,au_idau_Inane1409-56-700?BennetAbraham2648-92-1

5、872Blotdiet-Hal1sReginald323A95-TT66CarstJiChecyl4722-51-5154DeFrmceJlichel8 .查询所有作者的姓名、作者号信息,并在每个作者的作者号前面显示字符串“身份证号：,说明显示的信息是身份证信息usepubs-、select凯L_lnam生&TL_fnane,身份证号；'as身份证号,au_：LcifromAntliersuuaijlnajn号au_fTi3jne身份证号an.id1EtrmfltAbraham身份证号:40P-5&-70082ELotchet-HalIsRegnmald身份任S48-S

6、2-18723CarsonCheryl身份证号;Z36-S5-770C4D&FranceMichel身份证号二722-51-5464dalCastilloInnes身份证号2712-45-13576Dull身份证号：427-17-23107GreenWarjone身份证号工213-45-39158rFftYiAnrninpstAr券7TAsJMA9 .查询在CA州的作者姓名和城市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 查询每个出版社出版的书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 查询某店销售某书的数量7.查询有销售记录的所有书信息书号、书名、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类型和价格等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 .查询已销售书的信息书号、书名、作者等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 .查询商业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内容二:1.查询书名以T开头或者出版社号为0877,而且价格大于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,查询所有作者的所在城市和州名,要求没有重复信息Llislatecity1CkSsrk&

23、;ley2匚ACuv-alu3CAMenloPark4CkOakland5ChPaleAlto6aSw.FranciscQ7HA5anJose6CiLVacaville9CkWalnutCreep10INGarynK5Laurence12而RoclsviLle13HIAmArbor14ORCorvallisISTHNashville16urSaltLakeCityusepubsselectdLstinctcitfromAuthors6,计算多少种书已被订价usepubsselectcount(typeas'已破定价书的种类戮量.,fromTitleswheueprice'>

24、;0|I己祓定价书的种类数量IS7 .查询每本书的书名、作者及它的售书总量usepubsSelecttitle-auInane.au_fnianeYtdsalesFrantLtlesauthors,titleaiUthorffhere(authors,em_id-titleauthoT.auid)and(titlestitle_id=titleauthortitle_idTLtl*aufnanwThelusyDaftabasGioid«firmerWsTjarie现的2TheEujyEsoeutsDcrta.basoGuide3omrtAbioham,0953Cockiinjvith

25、rSurireirti+ionrRsilanc.0"LearyWirhUPl187fiCockinevithCcnputrsxBdlu厚.KaJcaihor5-tearn?36768 .计算所有书的平均价格9 .查询价格最高的书的书名、作者及价格usepubsSelecttitleprice,au_lname.au_fnnireFromtitlesAuthorsTltlea.uthorThereAuthors.au_ic-TitleanthDr.iLi_itiia2LLtitles-TitLeathor.title_idurice-(selectiraxr)ricefroEtitle

26、s-itlLte1au._lnaiiBarLfiKUn*lButItUehFriwidly712.9S1UUitmr匚h#ryl内容三:1 .参照以上各表给出的主键、外键设置的设置要求,在自己创立表中进行相应的设置.2 .向authors表中插入一行作者信息(具体值自定)usepubsinsertintoauthors(au_£djaulnama.au_fname.phone,contract,zip)values('口O7'qLQi'whou'010710100,3 .数量超过100的商店增加10%的折扣jsepubsUpdatediseourtsS

27、etciscount-1.l-discocntVherehighqty100即影响的行数为：fl4,删除1994.9.13的订单usepubsDeletefrorsalesWheieord_date=,1994-9-13,?肝影响的什翦为七什结果：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州作者所著书的视图包括作者号、姓名、所在州、书名、价格、出版日期-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 .建立付款方式是现金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_