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

MySQL数据库技术与项目应用教程(微课版)（AI助学）（第3版）习题答案项目一搭建数据库运行环境单选题：CBADBCCDAA思考题(参考)：数据库、数据库管理系统、数据库系统之间的关系是怎样？答：数据库是按照一定结构存储有相互联系的数据集合，用于支持具体的应用系统运行所必要的数据。数据库管理系统是用一为操纵和管理应用系统数据库的软件，可以实现对数据库中对象和数据的添加、删除和修改，并为数据库制定安全及高可用策略。数据库系统则是由以数据库管理系统为核心的软件、数据库和DBA组成的系统，它包含数据库及数据库管理系统。2.字符集utf8基本能满足应用需求，MySQL8.0为什么还要将默认字符集改为utf8mb4？答：utf8编码最大字符长度为3字节，当遇到4字节的字符就会出现错误了。三个字节的UTF-8最大能编码的Unicode字符是0xFFFFFF，也就是Unicode中的基本多文平面（BMP）。也就是说，任何不在基本多文平面的Unicode字符，都无法使用MySQL原有的utf8字符集存储。这些不在BMP中的字符包括哪些呢？最常见的就是Emoji表情（Emoji是一种特殊的Unicode编码，广泛应用在ios和android手机上），和一些不常用的汉字，以及任何新增的Unicode字符等等。那么utf8mb4比utf8多了什么的呢?多了emoji编码支持.如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.比如评论要支持emoji可以用到。项目实践：答案略拓展实训：答案略项目二设计网上商城系统数据库单选题：CCBACACDAC思考题(参考)：1、在数据抽象过程中，根据不同的用户视角，关系数据模型可以分为三个层次？请简述你对这三个层次数据模型的理解，以及他们之间的联系。 答：数据抽象过程可以分为概念模型、逻辑模型和物理模型。概念模型面向用户，是对现实世界中特征数据抽象后建立的模型。用于表达现实世界中对象间的联系，是数据库设计人员与用户交流的模型。逻辑模型独立于计算机系统，用来描述数据的逻辑结构，与计算机软硬件无关，由概念模型通过一定的转换原则转换来成。物理模型是逻辑模型的物理实现，与选用的数据库管理系统相关联。2.假若你是在B2C网上商城应用系统的会员“Helly”，当你浏览网上商城时，看到自己感兴趣的商品时，会将该商品加入购物车，这个时候网上商城数据库中会有哪些数据表中的记录发生什么样的变化呢？当你最后提交购物车中的商品后，数据库中又有哪些表中的记录会发生变化呢？你认为需要哪些数据操作来为你实现这个购买商品的业务，具体步骤怎样？ 答：略，读者可在诸如京东、华为官网等平台体验真实的业务流程。对照书中数据模型，思考操作会引起哪些数据及表的变化。3.假定有两张学生成绩表，表1（学号，姓名，数据库成绩），表2（学号，姓名，网页设计成绩），请问这两张表是否可以做“集合并”操作？为什么？ 答：不可以。集合并要求参与操作的两个关系对应列具有相同类型及语义，题干列出的表1和表2虽然第3列都是成绩，类型相同但其代表的是不同课程的成绩，其语义不同。项目实践：（1）新增打折功能的设计当需增加折扣功能时，都需要修改原来的价格属性为原价格，增加一个售价属性。同时折扣的处理可以有多种方案，下面列举两种仅供参考。方案一：若折扣为单一折扣，可以直接在商品实体中直接增加折扣价属性。当设置商品或修改商品时，设置商品的折扣。方案二：若设定的折扣与会员的积分相关，则可以抽象一个折扣实体，存储会员积分与折扣的对照值，通过程序逻辑来控制会员购买的折扣。（2）支持多层级分类的设计多层级分类广泛应用在信息系统中，比如课程章节、书目分类、商品分类、物流地址等，下面列举两种处理方案仅供参考方案一：当分类级别少于或等于三级时，可以对每个级别抽象一个实体集，比如可以分别建立一级目录、二级目录、三级目录三个实体集，并构建二级目录属于一级目录，三级目录属于二级目录间的关系，这种模式简单易操作。不支持无限分级。方案二：当分类级别层次不确定时，可以采用在类别表中增加一列parent_id列，用于表示当前类别的上一级类别的id，以此来构建无限分级。例如：上图中parent_id表示课程的先修课id，当值为0时，表示该课程没有选修课程。拓展实训：可在已有模型上，增加地名或名胜的实体集，由于各地名或名胜的行政分级层次的级别不确定，因此该实体集在描述地名或名胜的基本信息外也需要增加parent_id来表示，该地名或名胜属于哪一个地区。此外，诗人与地名或名胜的关系为游历关系，该关系描述为一个诗人可以游历多个地方，每一个地方当然也可以被多个诗人游历，因此诗人和地名或名胜间为多对多关系。项目三创建网上商城系统数据库单选题：BBADC思考题（参考）：（1）空值的处理。对于一个数值类型的字段，空值NULL和0值相同吗？对于一个字符串类型的字段，空值NULL和空字符串相同吗？请简述你对空值NULL的理解。答：在MySQL中，NULL与任何值不相等。NULL代表不确定。（2）在MySQL中datetime跟timestamp两种数据类型都可以表示日期时间，那么这两种数据类型有什么区别呢？根据你的观点，在航空公司机票预定的数据库中，航班的起降时间应该用哪种数据类型来表达呢？请简述你的观点和理由。答：datetime和timestamp两种类型所占存储空间和可表示的数据范围不同。其中datetime占8个字节，表示范围为：1000-01-0100:00:00～9999-12-3123:59:59；而timestamp占4个字节，表示范围为：19700101080001～20380119111407。此外，timestamp类型支持动态默认值，当使用ONUPDATECURRENT_TIMESTAMP语句定义timestamp类型后，每一次对记录的修改都会用系统当前时间自动更新时间。此外，timestamp还会把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。在航空公司机票预定的数据库中，航班起降时间跟起止地的时区相关，因此客户在查询时应该实时根据查询的客户端来显示，因此使用timestamp类型更合适。（3）使用TRUNCATE语句清除数据表中的数据后，再向数据表中添加数据时，自动增长（设置了AUTO-INCREMENT）的字段默认初始值重新从1开始；使用DELETE语句删除数据表中所有数据之后，再向数据表中添加数据时自动增长字段的值会从该字段最大值加1开始编号。这是什么原因呢？TRUNCATE语句是如何快速清空数据的呢？请你简述你对这个问题的理解。答：主要是两种删除方式不一样。DELETE操作属于数据操作语言DML，是将表中的记录一条条进行删除，在一个事务中，数据删除完成后，还可以回滚至删除前的状态。因此数据的删除不会改变AUTO-INCREMENT的当前值。TRUNCATE操作以属于数据定义语言DDL，该操作保留表的结构，并重新创建表，该的所有状态都是新表状态。因此，该操作的执行效率更高。项目实践：（1）创建名称为onlinedb的数据库，默认字符集设置为uft8mb4。createdatabaseonlinedbdefaultcharactersetutf8mb4;（2）据网上商城系统的数据库设计，在onlinedb数据库中添加用户信息表（users）、商品类别表（category）、商品表（goods）、购物车信息表（cart）、订单信息表（orders）、订单详情表（ordersitem）。表结构具体见附录A。createtablecategory(cidintprimarykeyauto_increment, cnamevarchar(100)notnull;);其他表略。（3）根据网上商城系统的数据库设计，为onlinedb数据库中的数据表添加如下约束。•根据物理模型，为goods,orders，ordersitem，cart表添加相应的外键。#为goods表的cid添加外键altertablegoodsaddconstraintfk_goods_cate_cidforeignkey(cid)referencescategory(cid);#为orders表的uid添加外键altertableordersaddconstraintfk_orders_users_uidforeignkey(uid)referencesusers(uid);#为cart表的uid,gid添加外键altertableordersaddconstraintfk_cart_users_uidforeignkey(uid)referencesusers(uid),addconstraintfk_cart_goods_gidforeignkey(gid)referencesgoods(gid);#为ordersitem表的oid，gid添加外键altertableordersaddconstraintfk_item_orders_uidforeignkey(oid)referencesorders(oid),addconstraintfk_item_goods_gidforeignkey(gid)referencesgoods(gid);•为users表中的ulogin字段添加UNIQUE约束。altertableusersaddconstraintuq_uloginunique(ulogin);•为goods中的gcode字段添加UNIQUE约束。altertablegoodsaddconstraintuq_gcodeunique(gcode);•为users表中的uregtime字段，goods中的gaddtime字段添加DEFAULT约束，默认值为当前系统时间。altertableusersmodifyuregtimedatetimedefaultcurrent_timestamp;altertablegoodsmodifygaddtimedatetimedefaultcurrent_timestamp;•为goods中的gsale_qty字段添加DEFAULT约束，默认值0。altertablegoodsmodifygsale_qtyintdefault0;•为goods中的gprice,gquantity字段添加约束，不允许负值（小于零的值）出现。altertablegoodsaddconstraintck_gpricecheck(gprice>=0),addconstraintck_gquantitycheck(gquantity>=0);拓展实训：根据项目二拓展实训中，诗词飞花令游戏的数据模型，完成以下练习。（1）创建名称为PoemGameDB的数据库，默认字符集设置为uft8mb4。createdatabasePoemGameDBcharactersetutf8mb4;（2）根据诗词飞花令游戏数据库的物理模型，在PoemGameDB数据库中添加诗词表（poem）、诗人表（poet）、飞花令表（feihualing）、诗词飞花令关联表（poemling）、诗词类别表（poemType）、诗词分类表(poemIndex)。CREATETABLE`poem`(`pmID`intNOTNULLAUTO_INCREMENTCOMMENT'诗词ID',`pID`intDEFAULTNULLCOMMENT'诗人ID',`pmTitle`varchar(100)NOTNULLCOMMENT'诗词标题',`pmContent`varchar(8000)NOTNULLCOMMENT'诗词内容',`pmHot`intDEFAULT'0'COMMENT'诗词热度',`pmPreface`textCOMMENT'创作背景',`pmAnnotation`textCOMMENT'注解',`pmComment`textCOMMENT'评析',PRIMARYKEY(`pmID`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;CREATETABLE`poet`(`pID`intNOTNULLAUTO_INCREMENTCOMMENT'诗人ID',`pName`varchar(10)NOTNULLCOMMENT'诗人姓名', `pGender`varchar(2)NOTNULLCOMMENT'诗人性别',`pZi`varchar(5)COMMENT'字', `pHao`varchar(10)COMMENT'号', `pBirthYear`intCOMMENT'出生年份', `pDeathYear`intCOMMENT'逝世年份', `pBirthPlace`varchar(20)COMMENT'出生地', `pEthnicity`varchar(10)COMMENT'民族', `pDynasty`varchar(10)COMMENT'朝代', `pProfile`textCOMMENT'诗人生平简介',`pHot`intDEFAULT'0'COMMENT'诗人热度',PRIMARYKEY(`pID`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;dropTABLE`feihualing`;CREATETABLE`feihualing`(`fID`intNOTNULLAUTO_INCREMENTCOMMENT'诗词ID',`fName`varchar(10)NOTNULLCOMMENT'飞花令名称',PRIMARYKEY(`fID`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;CREATETABLE`poemLing`(`plID`intNOTNULLAUTO_INCREMENTCOMMENT'诗令ID',`pmID`intNOTNULLCOMMENT'诗词ID', `fID`intNOTNULLCOMMENT'诗词ID',PRIMARYKEY(`plID`))ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;CREATETABLE`poemType`(`ptID`intNOTNULLAUTO_INCREMENTCOMMENT'类别ID',`ptTopic`varchar(50)NOTNULLCOMMENT'类别名称',`ptType`enum('选集','主题')DEFAULTNULLCOMMENT'分类方式',PRIMARYKEY(`ptID`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;altertablepoemTypechangeptTopicptNamevarchar(50)NOTNULLCOMMENT'类别名称';CREATETABLE`poemIndex`(`poID`intNOTNULLAUTO_INCREMENTCOMMENT'诗词分类ID',`ptID`intNOTNULLCOMMENT'类别ID',`pmID`intNOTNULLCOMMENT'诗词ID',PRIMARYKEY(`poID`))ENGINE=InnoDBAUTO_INCREMENT=20DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;（3）根据飞花令游戏的数据库设计，为PoemGameDB数据库中数据表添加如下约束。•根据物理模型，为poem,poemling，poemIndex表添加相应的外键。altertablepoemaddconstraintfk_poemforeignkey(pid)referencespoet(pid);altertablepoemIndexaddconstraintfk1_poemIndexforeignkey(pmid)referencespoem(pmid);altertablepoemIndexaddconstraintfk2_poemIndexforeignkey(ptid)referencespoemType(ptid);altertablepoemlingaddconstraintfk1_poemlingforeignkey(pmid)referencespoem(pmid);altertablepoemlingaddconstraintfk2_poemlingforeignkey(fid)referencesfeihualing(fid);•为poem表中的pmHot字段、poet中的pHot字段添加DEFAULT约束，默认值为0。altertablepoemmodifypmHotintdefault0;altertablepoetmodifypHotintdefault0;•为poem表中的pmHot字段、poet中的pHot字段添加约束，不允许负值（小于零的值）出现。altertablepoemaddconstraintchk1_poemcheck(pmHot>=0);altertablepoetaddconstraintchk1_poetcheck(pHot>=0);项目四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);insertintopoemling(pmid,fid)values(16,13);insertintopoemling(pmid,fid)values(16,17);descpoemIndex;insertintopoemIndex(ptid,pmid)values(4,16);insertintopoemIndex(ptid,pmid)values(6,16);insertintopoemIndex(ptid,pmid)values(9,16);insertintopoemIndex(ptid,pmid)values(12,16);（19）修改（17）中你在poem表中所添加诗词的诗词热度，将热度值改为10，同时该诗词作者的热度相应增加10。updatepoemsetpmHot=10wherepmid=16;（20）删除（17）中你在poem表中所添加的数据，同时将poemling表及poemIndex表中相应的数据删除。两种情况情况一：当poemling和poemindex两张表上建立的外键设置了ondeletecascade时，只需要删除主表poem中该诗句，这里对应的从表中的记录会自动删除。即deletefrompoemwherepmid=16;情况二：如第3小题答案中添加的外键未指明ondeletecascade，这时需要先删除从表中的关联数据，再删除主表poem中该诗句，共分三个删除#第1步：删除poemling中pmid为16的数据deletefrompoemlingwherepmid=16;#第2步：删除poemIndex中pmid为16的数据deletefrompoemIndexwherepmid=16;#第3步:删除poem表中pmid为16的数据deletefrompoemwherepmid=16;项目五：优化查询网上商城系统数据单选题：BCDDBADBBA思考题(参考)：1.视图跟基本表之间的关系是怎样的？请根据你的理解，举例说明视图机制是如何保障数据安全性，以及逻辑数据独立性的。答：视图是从数据库中一个或多个基本表导出的表，其关联的数据由SQL语句定义。基本表是本身独立存在的表，在SQL中一个关系就对应一个表。视图中的数据依赖基本表中的数据，一旦表中的数据发生改变，显示在视图中的数据也会发生改变。因此，视图是依赖与基本表的。数据安全性：为不同的用户定义不同的视图，可以限制用户的访问范围。通过视图机制把需要保密的数据对无权存取这些数据的用户隐藏起来，可以对数据库提供一定程度的安全保护。逻辑数据独立性：视图可以屏蔽基本表的表结构变化带来的影响。若应用程序使用视图，当基本表的表结构发生更改时，只需要修改视图对应的SQL语句即可，无须修改应用程序。通过视图可以将应用程序与数据库的基本表分隔开。2.合理的索引设置，是获得高性能数据库的基础，而未经合理分析随便添加索引，则会降低数据库的性能，那么索引是否一旦设置好就一劳永逸不需要维护了呢？请谈谈你的理解。答：索引并不是一劳永逸的,用的时间长了需要进行整理或者重建。索引创建后，由于数据的增加、删除或修改等操作会使索引页发生缺失或者多余，因此需要对索引进行维护。项目实践：•视图:(1)使用Navicat创建用来描述商品基本信息的视图，包括商品id、商品名称、商品价格和库存数量，视图名为view_goods。结果显示如图所示：(2)使用SQL语句创建用来描述订单信息的视图，包括订单id、会员姓名、商品名称和总金额等信息，视图名为view_orders。CREATEVIEWview_ordersASSELECToid,uname,gname,oamountFROMordersJOINusersUSING(uid)JOINordersitemUSING(oid)JOINgoodsUSING(gid);(3)分别使用SHOWTABLESTATUS语句和DESCRIBE/DESC语句查看(1)中创建的视图。SHOWTABLESTATUSLIKE'view_goods';DESCview_goods;(4)使用SHOWCREATEVIEW语句查看(2)中创建的视图的定义文本。SHOWCREATEVIEWview_orders;(5)分别使用CREATEORREPLACEVIEW语句和ALTER语句修改(2)中创建的视图，修改后的视图信息包括订单id、商品名称和购买数量。CREATEORREPLACEVIEWview_ordersASSELECToid,gname,inumFROMordersJOINusersUSING(uid)JOINordersitemUSING(oid)JOINgoodsUSING(gid);或者ALTERVIEWview_ordersASSELECToid,gname,inumFROMordersJOINusersUSING(uid)JOINordersitemUSING(oid)JOINgoodsUSING(gid);(6)删除(1)中创建的视图。DROPVIEWview_goods;(7)使用UPDATE语句更新视图view_goods，将所有商品的单价增加10%。UPDATEview_goodsSETgprice=gprice+gprice*0.1;(8)使用DELETE语句更新视图view_goods，删除goods表中的最后一条记录。#方法1，分步查询#第1步，获取gid值最大的记录selectmax(gid)fromgoods;#删除该记录DELETEFROMview_goodsWHEREgid=11;#子查询实现DELETEFROMview_goodsWHEREgid=（selectmax(gid)fromgoods）;#或DELETEFROMview_goodsWHEREgid=（selectgidfromgoodsorderbygiddesclimit1）;•索引：(9)使用Navicat在onlinedb.goodstype表的tName列上创建一个为名IX_tName的普通索引。结果显示如图所示：(10)使用SQL语句在onlinedb.goods表的gdCode和gdName列上创建一个名为IX_gdCN的复合索引。ALTERTABLEgoodsADDINDEXIX_gdCN(gcode,gname);(11)分别使用SHOWCREATETABLE语句和SHOWINDEXFROM/SHOWKEYSFROM语句查看(10)中创建的索引IX_gdCN的相关信息。SHOWCREATETABLEgoods;SHOWINDEXFROMgoods;(12)使用SQL语句删除(9)和(10)创建的索引。ALTERTABLEgoodsDROPINDEXIX_tName;ALTERTABLEgoodsDROPINDEXIX_gdCN;•查询优化(13)使用EXPLAIN语句分析如下查询语句，并对其进行优化。SELECTuname,ugender,ubirthday,uregtimeFROMusersWHEREuidIN(SELECTuidFROMordersGROUPBYuidHAVINGSUM(oamount)>=1000);EXPLAINSELECTuname,ugender,ubirthday,uregtimeFROMusersWHEREuidIN(SELECTuidFROMordersGROUPBYuidHAVINGSUM(oamount)>=1000);优化：在uid列上创建索引。(14)分别采用查询优化法和索引覆盖法对如下查询进行优化处理。查询goods表，显示从第30000行开始的连续5行数据的编号、名称和价格。SELECTgdCode,gdName,gdPriceFROMgoods

LIMIT30000,5;查询优化法：先按gid>30000进行条件筛选，再取出大于30000的前5行数据。索引覆盖法：为goods表的编号列创建索引。•拓展实训：使用Navicat创建用来描述诗词基本信息的视图，包括诗词标题、诗人姓名、朝代和诗词内容，视图名为view_poem。结果显示如图所示：使用SQL语句创建爱国主义主题诗词信息的视图，包括诗词标题、诗人姓名、诗词内容，诗词热度，类型名称和分类方式等信息，视图名为view_patriotism。CREATEVIEWview_patriotismASSELECTpmTitle,pName,pmContent,pmHot,ptTopic,ptTypeFROMpoemJOINpoetUSING(pID)JOINpoemIndexUSING(pmID)JOINpoemTypeUSING(ptID);分别使用SHOWTABLESTATUS语句和DESCRIBE/DESC语句查看(1)中创建的视图。SHOWTABLESTATUSLIKE'view_poem';DESCview_poem;使用SHOWCREATEVIEW语句查看(2)中创建的视图的定义文本。SHOWCREATEVIEWview_patriotism;分别使用CREATEORREPLACEVIEW语句和ALTER语句修改(2)中创建的视图，修改后的视图信息包括诗词标题、诗人姓名和诗词内容。CREATEORREPLACEVIEWview_patriotismASSELECTpmTitle,pName,pmContentFROMpoemJOINpoetUSING(pID);删除(1)中创建的视图。DROPVIEWview_poem;使用UPDATE语句更新视图view_patriotism，将所有诗词热度加1。UPDATEview_patriotismSETpmHot=pmHot+1;使用DELETE语句更新视图view_poem，删除poem表中的最后一条记录。selectmax(gid)fromview_poem;#结果显示pid=7DELETEFROMview_poemWHEREpid=7;•索引：(9)使用Navicat在poemGameDB.poem表的pmTitle列上创建一个为名IX_pmTitle的普通索引。结果显示如下：(10)使用SQL语句在onlinedb.poemType表的ptTopic和ptType列上创建一个名为IX_ptTy的复合索引。ALTERTABLEpoemTypeADDINDEXIX_gdCN(ptTopic,ptType);(11)分别使用SHOWCREATETABLE语句和SHOWINDEXFROM/SHOWKEYSFROM语句查看(10)中创建的索引IX_ptTy的相关信息。SHOWCREATETABLEpoemType;SHOWINDEXFROMpoemType;(12)使用SQL语句删除(9)和(10)创建的索引。ALTERTABLEpoemDROPINDEXIX_pmTitle;ALTERTABLEpoemTypeDROPINDEXIX_ptTy;•查询优化(13)使用EXPLAIN语句分析如下查询语句，并对其进行优化。SELECTpName,pZi,pHao,pDynastyFROMpoet

WHEREpIDIN(SELECTpIDFROMpoem

GROUPBYpID

HAVINGSUM(pmHot)>=100);EXPLAINSELECTpName,pZi,pHao,pDynastyFROMpoet

WHEREpIDIN(SELECTpIDFROMpoem

GROUPBYpID

HAVINGSUM(pmHot)>=100);优化：在uid列上创建索引。(14)分别采用查询优化法和索引覆盖法对如下查询进行优化处理。查询poem表，显示从第30000行开始的连续5行数据的诗词标题、诗歌内容和注解。SELECTpmTile,pmContent,pmAnnotationFROMpoem

LIMIT30000,5;查询优化法：先按pmid>30000进行条件筛选，再取出大于30000的前5行数据。索引覆盖法：为poem表的pmid列创建索引。项目六编程操作网上商城系统数据单选题：BCAADAABDA思考题(参考)：使用存储过程有诸多优点，在书中已有描述，那使用存储过程是否存在缺点呢？答：缺点可从以下几方面阐述（1）可移植性差（2）开发调试复杂，由于IDE的问题，存储过程的开发调试比一般程序要更困难（3）SQL本身是结构化查询语言，不是面向对象语言，在复杂业务的处理上会比较吃力。SQL擅长的是数据查询而不是业务逻辑处理，如果把业务逻辑全部放在数据库，违背了这一原则2.触发器在数据库中有很多合适的用途，它在插入、删除或者修改特定表中数据时，会触发一些数据操作，用以维护数据的参照完整性和维护数据安全等。但是我们说使用触发器时，需要特别小心，如果可以使用其他技术手段处理尽量别用触发器，这是为什么?答：MySQL触发器能基于行触发，MySQL触发器始终时基于表中的一条记录触发，而不是一组SQL语句。因此，如果需要变动整个数据集而数据集数据量又较大时，触发器效果会非常低。触发器是由于表中的数据变化，引起另外的数据表中发生相应的数据变化，过多的触发器会导致数据的链式反应，效率很低，甚至造成死循环，因此，使用触发器一定要特别小心。项目实践：#存储函数创建并调用存储函数func_users_count，查询2021年1月1日以后注册的用户总数。delimiter//createfunctionfunc_users_count()returnsintbeginreturn(selectcount(uid)fromusers whereuregtime>='2021-01-01');end//使用SQL语句查看用户自定义函数func_users_count。showfunctionstatuslike'func_users_count';(3)创建并调用存储过程proc_get_integer，输出100以内能够同时被3和5整除的整数。delimiter//createprocedureproc_get_integer()begindeclarecounterintdefault0;whilecounter<=100do ifcounter%3=0andcounter%5=0then selectcounter; endif; setcounter=counter+1; endwhile;end//(4)创建并调用存储过程proc_rand_record，为users表添加10000条测试记录。delimiter//createprocedureproc_rand_record()begindeclareiintdefault0; whilei<10000do insertintousers(ulogin,uName,uPwd) values(fnReturnStr(10),fnReturnStr(6),fnReturnStr(8)); seti=i+1; endwhile;end//(5)创建并调用存储过程proc_user_order，根据指定的uid查询该用户的订单总数。delimiter//createprocedureproc_user_order(userIDint)beginselectcount(oid) fromorders whereuid=userID;end//#(6)删除存储过程proc_user_order。dropprocedureproc_user_order(7)创建存储过程proc_orders_count，统计查询每个用户的订单数。delimiter//createprocedureproc_orders_count()beginselectuid,count(oid) fromorders groupbyuid;end//delimiter//createtriggertrig_order_numafterinsertonordersItemforeachrowbeginupdategoods setgdQuantity=gdQuantity-new.inum,gdSaleQty=gdSaleQty+new.inum wheregid=new.gid;end//(9)创建触发器trig_goods_type，当更改category表中某个类别id时，同时将goods表对应的类别id全部更新。delimiter//createtriggertrig_goods_typeafterupdateoncategoryforeachrowbeginupdategoods setcid=new.cid wherecid=old.cid;end//#(10)在网上商城数据库中，完成销售月报表及日报表功能，统计每月及每日的销售总金额，以及商品#销售总数量。#月报表功能#创建月报表createtablemonthlyReport(mrIDintprimarykeyauto_increment,mrTimevarchar(15)notnullunique,mrAmontfloatnotnullcheck(mrAmont>=0),mrNumintnotnullcheck(mrNum>=0));dropeventevent_monthlyReport;delimiter//createeventevent_monthlyReportonscheduleevery1monthstarts'2021-12-0103:00:10'docallproc_monthlyReport();//delimiter//createprocedureproc_monthlyReport()begindeclareamountfloat;--用于存放上月销售总金额 declarenumint;--用于存放上月销售总数量 --统计上月销售总金额 selectifnull(sum(ototal),0)intoamount fromorders whereyear(otime)=year(DATE_SUB(CURDATE(),INTERVAL1month)) andmonth(otime)=month(DATE_SUB(CURDATE(),INTERVAL1month)); --统计上月销售总数量 selectifnull(sum(odnum),0)intonum fromorderitemodjoinordersoonod.oid=o.oid whereyear(otime)=year(DATE_SUB(CURDATE(),INTERVAL1month)) andmonth(otime)=month(DATE_SUB(CURDATE(),INTERVAL1month));insertintomonthlyReport(mrTime,mrAmont,mrNum) values(DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL1month),'%Y-%m'), amount,num);end//#日报表功能#创建日报表createtabledailyReport(drIDintprimarykeyauto_increment,drDatedatenotnullunique,drAmontfloatnotnullcheck(drAmont>=0),drNumintnotnullcheck(drNum>=0));delimiter//createeventevent_dailyReportonscheduleevery1daystarts'2021-12-0103:00:10'docallproc_dailyReport();//delimiter//createprocedureproc_dailyReport()begindeclareamountfloat;--用于存放昨日销售总金额 declarenumint;--用于存放昨日销售总数量 --统计昨日销售总金额 selectifnull(sum(ototal),0)intoamount fromorders wheredate(otime)=(DATE_SUB(CURDATE(),INTERVAL1day)); --统计昨日销售总数量 selectifnull(sum(odnum),0)intonum fromorderitemodjoinordersoonod.oid=o.oid wheredate(otime)=(DATE_SUB(CURDATE(),INTERVAL1day));insertintodailyReport(drTime,drAmont,drNum) values(DATE_SUB(CURDATE(),INTERVAL1day),amount,num);end//拓展实训：(1)创建并调用存储函数func_poets_count，查询生于公元1000年之后的诗人总数。delimiter//createfunctionfunc_poets_count()returnsintbeginreturn(selectcount(pid) frompoet wherepbirthyear>=1000);end//selectfunc_poets_count();(2)使用SQL语句查看用户自定义函数func_poets_count。showfunctionstatuslike'func_poets_count'(3)创建并调用存储过程proc_poem_dynasty,统计各个朝代所创作的诗歌数量。delimiter//createprocedureproc_poem_dynasty()beginselectpdynasty,count(pmid) frompoemjoinpoetonpoem.pid=poet.pid groupbypdynasty;end//callproc_poem_dynasty();(4)创建并调用存储过程proc_feihualing，查询指定诗歌所包含的所有诗令名称。delimiter//createprocedureproc_feihualing(titlevarchar(50))beginselectfname fromfeihualingjoinpoemlingjoinpoem onfeihualing.fid=poemling.fidandpoemling.pmid=poem.pmid wherepmTitle=title;end//callproc_feihualing('永遇乐·京口北固亭怀古');(5)创建并调用存储过程proc_poem_type，查询指定诗词分类下所有的诗歌的诗歌标题。delimiter//createprocedureproc_poem_type(typevarchar(10))beginselectpmtitle frompoemjoinpoemIndexjoinpoemType onpoem.pmid=poemIndex.pmidandpoemIndex.ptid=poemType.ptid whereptname=type;end//callproc_poem_type('爱国');#(6)创建并调用存储过程proc_poem_count，统计指定诗人所创作的所有诗歌的诗歌数量。delimiter//create