MySQL数据库技术与项目应用教程（微课版）（AI助学）（第3版）-习题答案 项目4

MySQL数据库技术与项目应用教程(微课版)（AI助学）（第3版）习题答案项目四CRUD操作网上商城系统数据单选题：CACABACDBDCBDBB思考题（参考）：（1）HAVING和WHERE都可以用于对查询结果进行筛选，它们的作用有何不同？为什么会产生这种不同？在什么时候HAVING的条件可以用WHERE条件来取代而结果不变？请谈谈你的理解。答：两者在语法上表现不相同，WHERE子句在语法上放在FROM子句后，用来筛选表中满足条件的数据行；HAVING子句需与GROUPBY子句一起使用，用来筛选分组统计后的数行，通常用来筛选聚合数据的行。主要原因是SELECT语句各子句的执行是有一定顺序的，且WHERE子句先于SELECT子句执行，因此在WHERE子句中不能使用别名和聚合函数。而HAVING子句可以使用别名和聚合函数作为筛选条件，若HAVING对分组筛选的数据是表中的基础数据时，可以用WHERE子句来替代。例如：#语句1selectgname,sum(gsale_qty)fromgoodsgroupbygnamehavinggnamelike’平凡%’;#语句2selectgname,sum(gsale_qty)fromgoodswheregnamelike’平凡%’groupbygname从实际应用看，上述两条SQL语句尽管结果一致，但建议使用语句2。（2）当查询的数据涉及多张数据表时，可以使用连接查询或者子查询（嵌套查询）来实现。那么子查询和连接查询是否可以相互替代呢？是不是所有使用了连接查询的SQL语句都可以用子查询来实现？通过子查询来实现的查询业务是否又都可以写成连接查询呢？请简述你的理解。答：连接查询和子查询是多表查询的不同方式。连接查询是将多张表进行笛卡尔集后再对关联列进行等值筛选，该种查询结构上较简单，方便易用。子查询则根据查询逻辑可以将内层查询的结果作为外层查询的条件、表达式和相关性查询，子查询实现时更灵活，它还可以嵌套在INSERT、UPDATE、DELETE语句中。一般情况下，使用连接查询的SELECT语句都可以用子查询来实现。子查询实现的查询业务也可以写成连接查询。（3）使用TRUNCATE语句清除数据表中的数据后，再向数据表中添加数据时，自动增长（设置了AUTO-INCREMENT）的字段默认初始值重新从1开始；使用DELETE语句删除数据表中所有数据之后，再向数据表中添加数据时自动增长字段的值会从该字段最大值加1开始编号。这是什么原因呢？TRUNCATE语句是如何快速清空数据的呢？请你简述你对这个问题的理解。答：主要是两种删除方式不一样。DELETE操作属于数据操作语言DML，是将表中的记录一条条进行删除，在一个事务中，数据删除完成后，还可以回滚至删除前的状态。因此数据的删除不会改变AUTO-INCREMENT的当前值。TRUNCATE操作以属于数据定义语言DDL，该操作保留表的结构，并重新创建表，该的所有状态都是新表状态。因此，该操作的执行效率更高。项目实践：#单表查询（1）查询users表，列出用户的所有信息。select*fromusers;（2）查询goods表，列出商品编号、名称和进货量（库存量+销售量，列名为purchases）。selectgcode,gname,gquantity+gsale_qtyaspurchasesfromgoods;（3）查询users表，找出2000年以后出生的用户，列出用户名、性别和所在城市。select*fromuserswhereubirthday>='2000-1-1';#orselect*fromuserswhereyear(ubirthday)>=2000;（4）查询users表，找出使用QQ邮箱的用户，列出登录名、用户名和邮箱地址。selectuloing,uname,uemailfromuserswhereuemaillike'%@';#orselectuloing,uname,uemailfromuserswhereuemailregexp'@$';（5）查询users表，找出来自北京、广州和深圳三个城市的用户，列出用户名、性别和所在城市。selectuname,ugender,ucityfromuserswhereucityin('北京','广州','深圳');#orselectuname,ugender,ucityfromuserswhereucity='北京'orucity='广州'orucity='深圳');（6）查询orders表，列出单笔订单金额在5000元以上的订单号、订单金额和下单时间。select*fromorderswhereoamount>=5000;#排序、分组统计入分析（7）查询users表，列出积分排名前5的用户名和积分。selectuname,ucreditfromusersorderbyucreditDESClimit5;#limit0,5;（8）查询users表，按性别统计用户的平均年龄，列出姓名和平均年龄（avg_age）。selectugender,round(avg(year(now())-year(ubirthday)),2)#round(x,y)函数：返回保留y个小数点的x值，截断时四舍五入fromusersgroupbyugender;（9）查询users表，统计各城市的用户人数(num)，并按人数从高到低排序。selectucity,COUNT(*)asnumfromusersgroupbyucityorderbynumdesc;（10）查询users表，列出积分排名前5的用户名、积分和名次(ranking)。selectuname,ucredit,ROW_NUMBER()OVER(ORDERBYucreditdesc)asrankingfromuserslimit5;#limit0,5;#orselectuname,ucredit,RANK()OVER(ORDERBYucreditDESC)asrankingfromuserslimit5;#limit0,5;#多表查询（11）查询goods表，列出所有乐器类商品的id、名称、类别id和类别名称。selectgid,gname,g.cid,cnamefromcategorycjoingoodsg onc.cid=g.cidwherecname='乐器';（12）查询图书类商品的总销售量(sale_count)。selectsum(gsale_qty)assale_countfromcategorycjoingoodsg onc.cid=g.cidwherecname='图书';（13）查询用户“郭辉”的购物车信息，列出商品id、名称、价格和数量。selectg.gid,gname,gprice,cnumfromusersujoincartcjoingoodsg onu.uid=c.uidandc.gid=g.gidwhereuname='郭辉';（14）查询用户“郭辉”的订单信息，列出订单id、订单编号、订单金额和下单日期。selectocode,oamount,ordertimefromusersujoinorderso onu.uid=o.uidwhereuname='郭辉';（15）查询订单号为“O210912082615101”的订单详情，列出商品名称、价格和购买量selectgname,gprice,inumfromordersojoinordersitemodjoingoodsg ono.oid=od.oidandod.gid=g.gidwhereocode='O210912082615101';（16）查询购买过“平凡的世界”商品的用户信息，列出用户名、性别和出生日期selectdistinctuname,ugenderfromusersujoinordersojoinordersitemodjoingoodsg onu.uid=o.uidando.oid=od.oidandod.gid=g.gid#wheregnamelike'%平凡的世界%';wheregnameregexp'平凡的世界';（17）使用联合查询，找出来自北京、广州和深圳三个城市的用户，列出用户名、性别和所在城市。selectuname,ugender,ucityfromuserswhereucity='北京'unionselectuname,ugender,ucityfromuserswhereucity='广州'unionselectuname,ugender,ucityfromuserswhereucity='深圳';#更改数据（18）向category表中添加新的商品类别，类别名称为“玩具”。insertintocategoryvalues(null,'玩具');set@id=LAST_INSERT_ID();#获取insert语句最后产生的自增长id值（19）向goods表中添加新的商品，商品类别为“玩具”，商品编号为“G0601”,商品名称为“乐高科技组51515MINDSTORMS编程机器人”，价格为3999，数量为100。insertintogoods(cid,gcode,gname,gprice,gquantity)values(@id,'G0601','乐高科技组51515MINDSTORMS编程机器人',3999,100);（20）修改goods表中商品编号为“G0601”的商品销量为1，库存数量相应减少。updategoodssetgsale_qty=gsale_qty+1,gquantity=gquantity-1wheregcode='G0601';（21）删除goods表中“玩具”商品类别中的所有商品。deletefromgoodswherecid=@id;拓展实训：•单表查询（1）查询poet表，列出所有诗人的所有信息。select*frompoet;查询poem表，列出诗歌标题、内容及诗词热度。selectpmTitle,pmContent,pmHotfrompoem;（3）查询poet表，找出所有唐朝诗人的姓名、性别及字号。selectpName,pgender,pZi,pHaofrompoetwherepDynasty='唐';（4）查询poem表，找出所有热度大于100的诗词，列出诗词标题及诗词内容、诗词热度。selectpmTitle,pmContent,pmHotfrompoemwherepmHot>100;（5）查询poemType表，找出所有根据诗词主题进行分类的类别名称。selectptNamefrompoemTypewhereptType='主题';（6）查询poet表，列出诗人的姓名、生活的朝代及享年岁数（逝世年份-出生年份，列名为Age）selectpname,pdynasty,pdeathYear-pBirthYearasAgefrompoet;•排序、分组统计与分析（7）查询poem表，列出热度排名前5的诗词的标题及内容。selectpmTitle,pmContentfrompoemorderbypmHotdesclimit5;（8）查询poet表，统计各朝代诗人的平均热度，列出朝代及平均热度，并按热度从高到低排序。selectpDynasty,avg(pHot)asavrerageHotfrompoetgroupbypDynastyorderbyavrerageHotdesc;（9）查询poet表，统计各朝代诗人的数量。selectpDynasty,count(pID)asnumfrompoetgroupbypDynasty;•多表查询或子查询（10）查询poem表中唐代诗人所创作的诗词，列出诗词标题、作者、朝代及诗词内容。selectpmTitle,pname,pDynasty,pmContentfrompoemjoinpoetonpoem.pID=poet.pIDwherepDynasty='唐';（11）查询包含地名令的所有诗词的诗词标题、内容及注解。selectpmTitle,pmContent,pmAnnotationfrompoemjoinpoemlingonpoem.pmid=poemling.pmidjoinfeihualingonpoemling.fID=feihualing.fidwherefname='地名';（12）查询记录了所有爱国主义诗词的诗词标题、内容及诗词热度。selectpmTitle,pmContent,pmHotfrompoemjoinpoemIndexonpoem.pmid=poemIndex.pmidjoinpoemTypeonpoemIndex.ptID=poemType.ptIDwhereptname='爱国';（13）查询诗人李清照所创作的所有诗词的总热度。selectpname,sum(pmHot)astotalfrompoetjoinpoemonpoet.pid=poem.pIDwherepname='李清照';（14）查询所有以“边塞征战”为主题的诗词中热度为高的诗词的标题、内容及作者。selectpmTitle,pmContent,pnamefrompoetjoinpoemonpoet.pid=poem.pidjoinpoemIndexonpoem.pmID=poemIndex.pmID joinpoemTypeonpoemIndex.ptID=poemType.ptIDwhereptname='边塞征战'orderbypmHotdesclimit1;（15）查询诗词《破阵子·为陈同甫赋壮词以寄之》所包含的所有飞花令，列出诗词标题及飞花令名称。selectpmTitle,fnamefrompoemjoinpoemlingonpoem.pmid=poemling.pmidjoinfeihualingonpoemling.fID=feihualing.fidwherepmTitle='破阵子·为陈同甫赋壮词以寄之';descpoet;（16）使用联合查询，查询出生于山东、四川及浙江的诗人，列出诗人姓名，朝代及出生地。selectpname,pdynasty,pbirthPlacefrompoetwherepbirthPlacelike'%山东%'unionselectpname,pdynasty,pbirthPlacefrompoetwherepbirthPlacelike'%四川%'unionselectpname,pdynasty,pbirthPlacefrompoetwherepbirthPlacelike'%浙江%';#更改数据（17）向poem表中添加一首你喜欢的诗词，并完善诗词相应信息。若该诗词的作者未记录在本数据库中，同时在poet表中添加诗人的相应数据。insertintopoet(pName,pGender,pZi,pHao,pBirthYear,pDeathYear,pBirthPlace,pEthnicity,pDynasty,pProfile)values('范仲淹','男','希文','文正',989,1052,'陕西邠州','汉','宋','北宋时期著名政治家、军事家、文学家、教育家。范仲淹幼年丧父，母亲改嫁长山朱氏，遂更名朱说。大中祥符八年（1015年），范仲淹苦读及第，授广德军司理参军。后历任兴化县令、秘阁校理、陈州通判、苏州知州、权知开封府等职，因秉公直言而屡遭贬斥');insertintopoem(pid,pmTitle,pmContent)values(LAST_INSERT_ID(),'渔家傲·秋思','塞下秋来风景异，衡阳雁去无留意。四面边声连角起，千嶂里，长烟落日孤城闭。浊酒一杯家万里，燕然未勒归无计。羌管悠悠霜满地，人不寐，将军白发征夫泪。');ps:查看poem中添加的新记录其pmid值为16。（18）向poemling表及poemIndex表中添加相应的数据，为（17）中你所添加的诗词完成分类及飞花令关联信息。descpoemling;insertintopoemling(pmid,fid)values(16,3);insertintopoemling(pmid,fid)values(16,10);insertintopoemli