一网一电大《MySQL数据库应用》实验训练2：数据查询操作MySQL数据库应用.docx

实验内容:1.单表查询【实验2.1】字段查询（1）查询商品名称为“挡风玻璃”的商品信息。(2)查询ID为1的订单。UWffA<fcH4Qd旬a2SCLCCTPWPoppln<.014IJW R【实验2.2】多条件查询查询所有促销的价格小于1000的商品信息。nH<4，Aig7IG:*1?S<r-rmrtcprt-is.”:。三price<（1）查询所有对商品ID为1的商品发表过评论的用户IDoQuery1XGSflOIBOOSIDonlgI*I0皿回1I2SELECTdistinct(Client_cid)FROMcomentwhereAUtoPartSjPidResuRGrid指FAerRows：Export:kWrapCelContent:JaCfenJcjd12（2）查询此汽车用品网上商城会员的创建时间段，1年为一段。Ca H A Jl后 11毋*1*1*1I EXPQft； t Wrap Cd 8tent; E2SELECTdistinct(left(CreateTime,)fromclientResultGrid韭*FimrRows;Qeft(Createlime,4)201920182017【实验2.4】ORDERBY(1)查询类别ID为1的所有商品，结果按照商品ID降序排列。HlffAOl0Mg,l%lA陋园1SELCCT*FROMautopartswhereSecdClas&_sdd三jr<feb/ApiddescReMltGl里Or%cv9:L_JlC由iiIE*po*VWcrt.柏融WrapCelContmtiE7ApnamemgcJriklimogcrA2negcinlc3InbxduCiono_»cddjrcRiCCWtOjgcnt3雨刷XXXXXxXXXX南剧（单E）110.989.8112轮贴XXXXXXXXX轮的050093500%211mm®AXXXXXXXXXXX出R股瀛12.31.3321IIcaO3S(IoCPrnlCnIiUnnlCTIlCMI(2)查询今年新增的所有会员，结果按照用户名字排序。Oi八WIVF'VIS“5"I%jULLTi12SELECTF":shopping.clientwhereCreatetime>'219'orderbyCna>eas<jResultGridIFerRows:Etftx¾P¾FEXPOCt/Import;WrapC三DContent;ddImageCnamePasswordphore_nuberEnaiGeateameOdrd,1XXXX张三12M5615509871234zs126com2019-01-01sOOrX14XXXX手木子23333333333201911llr3015赵子龙1122222222019-02-03JOOOO2.CZIQnumGJQ31【实验2.5】GROUPBY（I）查询每个用户的消费总金额（所有订单）。I*伊艮。I阳9喀Dlg7金|-Q曲囱RndY4,IQ1SELECTClient_cid,suB(total_price)fromshopping.ordergroubyClienJcid;23RocuhGII杷2HterRo<vt.IEXpon,UQWnpCdContanti11Qenjddsum(totalj)rice)1232279.92339984S9.98（2）查询类别价格一样的各种商品数量总和。FM3QWliqJCOfMiCIIdJrk,T(APi4)hopplng.4utopArt甲一“StcodClvc!d/Pri<IHtT皿tM¾>fIjE>0WrW38<<ct:JEnmJWda“jf<(Ad)，tt-I夕99S2230BI2 功第t3 SDO«t4 MSl分析：此查询中需要对商品进行分类，分类依据是同类别和价格，这是"多列分组"，较上一个例子更为复杂。2.聚合函数查询【实验2.6】COUNTOAfxj1 (1)查询类别的数量。selectcount(Nae)frorshopping.clientkincRMuk Grid gg 弋» Fr R<x:I Export： I 日 I Wrap C COnunt JaCountCName)3分析：此查询利用CoUNT()函数，返回指定列中值的数目，此处指定列是类别表中的ID(或者名称均可)。(2)查询汽车用品网上商城的每天的接单数。，find;4>!|。卜1l*r(ordrdttj凡court(Oid)fihooping.ororgrjpbyltft(crdrt,.)IRocuitGrtd£3e*O三V:feft-wwCalCorttntz口eftw(fj3aie,JC)count(3d)»aoas030194»«8201909S32019X<XI201930-191分析：订单相关，此处使用聚合函数Ce)UNT()和GroUPby子句。查询该商城每天的销售额。钎Iof,(oP.,dte)J<(totlJTCU)f<chopping.-匚,一/L(oierdate,)RaaA Grid : j Q» Ror ft J I Export P WraP S Ca<nt J9 979.999.99NH9gO8R253JDKgl920»100920191019分析：在订单表中，有一列是订单总价，将所有订单的订单总价求和，按照下单日期分组，使用SUM()函数和Groupby子句。【实验2.8】AVGO(1)查询所有订单的平均销售金额。1selectavg(total_price)fr<xnshopping.order2ResuhtGridFer Rows;Export: Q I Wrap Ge9 Content; Jaavg(totaljxe)13.728571分析：同上一个相同，还是在订单表中，依然取用订单总价列，使用AVG()函数，对指定列的值求平均数。(I)查询所有商品中的数量最大者。FindV4可叵匚1selectHiax(Inventory)trowshoppingsautopartsResultGridUAfterRovs:rnax(Iventory)分析：商品的数量信息存在于商品表中，此处查询应该去商品表，在商品数量指定列中求值最大者。使用MAX()函数。(2)查询所有用户按字母排序中名字最靠前者。Fhd三|回回叵匚1selectmax(Came)fr。Irshopping,lientjRdIttGrid即«FterHowl：Export:WnpClContent:max(Cname)赵子龙分析：MAX()或者MIN()也可以用在文本列，以获得按字母顺序排列的最高或者最低者。同上一个实验一样，使用MAX()函数。Rnd1 Result Grid Rftr Rows:nn(Priee)1.33JI Export: 轮 I Wrap C Content: Ja(I)查询所有商品中价格最低者。selectTin(Price)frorshopping.autoparts分析：同MAXO用法相同，找到表和列，使用MIN()函数。3.连接查询【实验2.11】内连接查询(1)查询所有订单的发出者名字。4WIAlV.»mm"I<fLUAnd«HQ.b-Old,b.Cx"1»chopping.-I八r<J<!Uiopplng,<I>11tb、.Clienjcldb.cld三E四四E四 4支3FT平率7分析：此处订单的信息需要从订单表中得到，订单表中主键是订单号，外键是用户ID,同时查询需要得到订单发出者的姓名，也就是用户名，因此需要将订单表和用户表通过用户ID进行连接。使用内连接的(INNER)JolN语句。(2)查询每个用户购物车中的商品名称。分析：购物车中的信息可以从购物车表中得到，购物车表中有用户ID和商品ID两项，通过这两项可以与商品表连接，从而可以获得商品名称。与上一个实验相似，此查询使ffl(INNER)JOIN语句。【实验2.12】外连接查询(1)查询列出所有用户ID,以及他们的评论，如果有的话。stl«ca.cld.D.comfltcl-cnchopolng.clientaItft1：lnchcoplng.coffr.-tb(、.cldb.cllntcldI rid JU e HU> owi: i mts 1邑国情弊2不Ul不依ihq杆得2信评了BBFrpcxti ¾¾ I Wrap Cdl Ox<art: 公分析：此查询首先需列出所有用户ID,如果参与过评论的话，再列出相关的评论。此处使用外查询中的LEFT(OUTER)JOIN语句，注意需将全部显示的列名写在JOIN语句左边。(2)查询列出所有用户ID,以及他们的评论，如果有的话。dV«<.一1，a.cid,srwwt%thcpping.一b,(thcrpi<<g.：-m.cid三b.(limt-cidIMa*gfl 2 rarMwm T Enfnr :85?：>-f*>t* 42封解了I4 SSI (wort 0 1 WTW 3 C4fM分析：依然是上一个实验，还可以使用RIGHT(OUTER)JolN语句，注意需将全部显示的列名写在JOlN语句右边。(1)查询用户ID为1的客户的订单信息和客户名。J4八"WIP，rMUUSCirit.cavc.b.iFpIshopping.cli?rtaLttjo:nchopping.5re<rbga.cd=b.Cllent_cidWhCL二a.cid-4R<at6tdjt¼FXPCE：IBfPoT:q3IWkC*1OMerr:2un*OidStatueOrdvjcateClnt_6dgoccc.pricecamage_PrCetotaJXdtcta_v*«ightZkCtribUorTpIGUM»19-09-0933:00<0一1"-2.32CC.33-'财张三2subri20W-C%OSJOOOt9.99OOO9.992资到何登分析：复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询的结果,使查询的结果更加准确。此查询需在内查询的基础上加上另一个条件，用户iD为L使用AND语句添加精确条件。(2)查询每个用户的购物车中的商品价格，并且按照价格顺序排列。Rndv.CX卜1 SELECTa.Client-cid,b.PriceFROM2 shopping.Shoppingcartainnerjoinshopping.autopartsbona.autoparts_apid-b.apidorderbyb.pricedescjResultGrid指FHterRews：Export：-¾WrapCdContent：JXClient-ddPrice4500.981500.982500.983500.9829.991L331L33分析：此查询需要先使用内连接对商品表和购物车表进行连接，得到商品的价格，在使用ORDERBY语句对价格进行顺序排列。4.嵌套查询(1)查询订购商品ID为1的订单ID,并根据订单ID查询发出此订单的用户ID。QUaylSQLFile MbXMfts coonettcetQtdef(wnoc4(t OfdOrJaeao(M&。I。MpfcClsJ I © I DOntg1 二elect cid free shooping.client2 nerc Cid in(5 lect CllenT_cld free shopping.ord*1" rere old Ir4 L (select ord<r oid free sh090i.order has Jtoparts -mere autoprts apid-' 1,)04f Crw1 2B 9 加<>r Ito>w, Mt ¾ H> >> >>poftlARpsAt J W<5 Cal Canaanti JJ分析：此查询需要使用IN关键字进行子查询，子查询是通过SELECT语句在订单明细表 中先确定此订单ID,在通过SELECT在订单表中查询到用户IDo(2)查询订购商品ID为1的订单ID,并根据订单ID查询未发出此订单的用户ID。select ClieejCid fro shopping.order where oxd,生匕("H OrdeJo 1(3 froa sh。POing.ordc-_"s_aUtoPartS Mure aut8artspid. =141 t H»r torn:Jl dt;法 二七 Expert'Inport： g & I Wr Cei Cotart: p分析：此查询和前一个实验相似，只是需使用NoTlN语句。(I)查询今年新增会员的订单，并且列出所有订单总价小于100的订单ID。SGLF1*u)c(Nr<xrtrwMd«nt-34SSUld«!0_UUCtlU1g。屈Gf*?DertgeQ.1.Fnd-<>QLF¾11Cppig.'-rClienreidi*(nrridf-rneppi11g.-：*-t,1rt(eretetie,)-,<.,)endtotl.pic<<:ISlfe9UiXfatxoorvoertjdIWrapgCntt0t>EB0ryjeOfnaorjnreWaLPrCetoaj<EMrrbrwi4)ejeVarBJVO1M)TIXlMHWOOMgn12330.032：IaMn3-2SJhm20*8088008198OOO2我，力”22222»7Mw20100900*00«.003992我为总象2ZZ2213Mrw丹及6。9见0<%84999QJoo992发赛内去22222BnOBODCDS3icmOBBBCDI分析：此查询需要使用嵌套，子查询需先查询用户表得到今年创建的用户信息，在将用户ID匹配找打订单信息，其中使用比较运算符提供订单总价小于100的条件。(2)查询所有订单商品数量总和小于100的商品ID,并将不在此商品所在类别的其他类别的ID列出来。2gS三L!CTNAcategory-EA!CjUCOry_RW(1CtrlFftWeMdCidfreeu*"r"apldi4eectut9prls,idfr3L(&>cq.Iwtoparts_apldsshopping.or4erJhaswMtopartsg11Mpbyatoprtsj,ld)CShrc.rue*)<HMmMMHHHHmHMHHHHHm9HPMHHHHHHMHHHHHMMHMHHH3«IClll2Tv9eS：昌由沿8»VUBOOfW.WF»3COrMrtlQCategoryj,to*eDcscrteCergoryjzjo'15卦：M1三,1就5?H2ro.DQicancmam分析：此查询需要进行嵌套查询，子查询过程需要使用到SUM()函数和GRoUPBY求出同种商品的所有被订数量，使用比较运算符得到数量总和小于100的商品ID,再使用比较运算符"不等于"得到非此商品所在类的类别ID。(1)查询表中是否存在用户ID为100的用户，如果存在，列出此用户的信息。select'roclientw：-cCid-sndcixs(xclcctcidf-onclientKKFCid-)GrdtemeOond(2：查出表丰是否岑在类别ID为100的萃房类三,二果学在.另出二类别丰宝忌少修小于5的套WIDF，“C4t<xyMZY.CmfB11Fj>SeceMClMtaUMvcys14分析：EXISTS关键字后面的参数是一个任意的子查询，系统对于查询进行运算以判断它是否返回行，如果至少返回一行，那以EXlSTS的结果为TRUE,此时外层查询语句将进行查询。此查询需要对用户ID进行EXIST操作。(2)查询表中是否存在类别ID为IOo的商品类别，如果存在，列出此类别中商品价格小于5的商品ID。Iliir1AOfi>JU4Q工VI，MHKTS3V8*y<Zr<*s(wU<t，t.yfrj>MWClMtjcMfr.M*>分析：与上一个实验相似，此实验在外查询过程添加了比较运算符。【实验2.17】ANY查询所有商品表中价格比订单表中商品ID对应的价格大的商品IDoWMiMWMMW*WAMBR>kM>M4rt0WV-8*uISbkIDon?S为观血ib*lectpidfrcautoprt%wher>pricv>y(%rlcttot】_prir-fromShoppingCrdrb【eftWinOrdgr'Bs_acopart3Conb.(nd-c.ordejefdWherga.ood-c.<t4toparRfts*GM-IQRtar3rc>pcrt?*VrpBCCrUareB分析：ANY关键字在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE,则返回TRUE。此处使用ANY来引出内查询。【实验2.18】ALL查询所有商品表中价格比订单表中所有商品ID对应的价格大的商品ID。1.9I?开Q¾>时DoMg«0ITi1 0r-.'spid-autcprf%-.aZhL-Ca.price><-tctlpric*，<r»Shopping.-T2 lr*tjoi-Order_2$_(WtoperqSconboidc.order-pid,a.utopertsapld)tJdQ,MtoKCgSCC;JQwrap3Goncarts巨11分析：使用ALL时需要同时满足所有内层查询的条件。ALL关键字在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE,则返回TRUE。此处使用ALL来引出内查询。【实验2.19】集合查询(1)查询所有价格小于5的商品，查询类别ID为1和2的所有商品，使用UNloN连接查询结果。分析：由前所述,UNION将多个SELECT语句的结果组合成一个结果集合,第1条SELECT语句查询价格小于5的商品，第2条SELEcr语句查询类别ID为1和2的商品，使用UNloN将两条SELEcT语句分隔开，执行完毕之后把输出结果组合为单个的结果集，并删除重复的记录。(2)查询所有价格小于5的商品，查询类别ID为1和2的所有商品，使用UNIONALL连接查询结果。1.了hni11iihln11jWSltU=*P.*wISSR.rw23UOJU.*DR&B6S5«Bgc|IuggbggaugL,py!Hhnhir!11IHHiHIE11:Ba"BM,lXKN榨VWg-.MW分析：使用UNle)NALL包含重复的行，在前面的例子中，分开查询时，两个返回结果中有相同的记录，使用UNION会自动去除重复行。UNIC)NALL从查询结果集中自动要返回所有匹配行，而不进行删除。