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

MySQL数据库技术与项目应用教程（微课版）(AI助学)（第3版）习题答案项目五：优化查询网上商城系统数据单选题：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)分别采