《MySQL数据库应用实战教程（微课版）》第06章-子查询、视图和索引

项目6

子查询、视图和索引任务1使用子查询任务1使用子查询6.1.1【实训6-1】嵌套子查询6.1.2【实训6-2】相关子查询6.1.3【实训6-3】增、删、改与子查询任务2使用视图任务3使用索引6.1.1【实训6-1】嵌套子查询根据Jitor校验器的要求，在dbForge上完成“【实训6-1】嵌套子查询”查询购买金额大于所有订单平均金额的订单方法一先查询所有订单的平均金额将查询的结果复制到第二条语句通过两个独立的查询得到最终结果Selectavg(col_ammount)fromshop_order_headwherecol_status>0;Selectid_shop_order_head,id_shop_customer,col_order_date,col_ammountfromshop_order_headwherecol_ammount>173.80andcol_status>0;【实训6-1】6.1.1【实训6-1】嵌套子查询（续）用另一种思路实现查询购买金额大于所有订单平均金额的订单方法二：不是复制查询结果，而是复制查询语句先写好所有订单的平均金额的语句（子查询）将查询语句复制到第二条语句通过一个嵌套的查询得到最终结果Selectavg(col_ammount)fromshop_order_headwherecol_status>0;Selectid_shop_order_head,id_shop_customer,col_order_date,col_ammountfromshop_order_headwherecol_ammount>(

Selectavg(col_ammount)fromshop_order_headwherecol_status>0;)andcol_status>0;不需要知道平均金额是多少思路简单效果明显父查询{子查询{注意：任何时候使用子查询时，子查询语句必须用圆括号括起来6.1.2【实训6-2】相关子查询根据Jitor校验器的要求，在dbForge上完成“【实训6-2】相关子查询”查询购买金额大于本人订单平均金额的订单，而不是所有订单平均金额方法一：依照嵌套查询方法一的思路先查询本人订单的金额，先查询第一个客户把第一个客户的平均金额复制到下面的语句中得到第一个客户大于自己的平均金额的订单有几个客户？有两个客户，还好，再做一遍就好了什么？有100个客户？10000个客户？Selectavg(col_ammount)fromshop_order_headwherecol_status>0andid_shop_customer=1;Selectid_shop_order_head,id_shop_customer,col_order_date,col_ammountfromshop_order_headwherecol_ammount>nnn.nandcol_status>0andid_shop_customer=1;这是不可行的！【实训6-2】6.1.2【实训6-2】相关子查询（续）必须换一种思路实现查询购买金额大于本人订单平均金额的订单方法二：先写好查询指定用户（id）的平均金额的语句（子查询）同样是把这条语句复制到第二条语句中也是用一条语句得到结果，但是这条语句称为相关子查询，因为子查询引用了父查询的表，它们是相关的思路巧妙效果显著Selectid_shop_order_head,id_shop_customer,col_ammountfromshop_order_headwherecol_ammount>(

Selectavg(col_ammount)fromshop_order_headwherecol_status>0andid_shop_customer=id--这个id的值从哪儿来？)andcol_status>0;Selectavg(col_ammount)fromshop_order_headwherecol_status>0andid_shop_customer=id;Selectid_shop_order_head,id_shop_customer,col_ammountfromshop_order_headasPARENTwherecol_ammount>(

Selectavg(col_ammount)fromshop_order_headwherecol_status>0andid_shop_customer=PARENT.id_shop_customer)--引用父查询的，这时id是动态的andcol_status>0;=>嵌套子查询和相关子查询的区别--嵌套子查询Selectid_shop_order_head,id_shop_customer,col_order_date,col_ammountfromshop_order_headwherecol_ammount>(

Selectavg(col_ammount)fromshop_order_headwherecol_status>0)andcol_status>0;--相关子查询Selectid_shop_order_head,id_shop_customer,col_ammountfromshop_order_headasPARENTwherecol_ammount>(

Selectavg(col_ammount)fromshop_order_headwherecol_status>0andid_shop_customer=PARENT.id_shop_customer)andcol_status>0;6.1.3【实训6-3】增、删、改与子查询根据Jitor校验器的要求，在dbForge上完成“【实训6-3】增、删、改与子查询”1．子查询与Update语句（1）更新用户的级别将下单次数（不含未确认的订单）大于5的客户设置为会员级别（col_rank为1）第一步：编写查询指定客户（id）下单次数的语句（子查询）第二步：编写更新指定客户的col_rank为1的语句（外部语句）第三步：按照相关子查询的思路，合并上述两条语句Selectcount(*)fromshop_order_headwherecol_status>0andid_shop_customer=idUpdateshop_customersetcol_rank=1where下单次数

>5;Updateshop_customeras

outer_tablesetcol_rank=1where(

Selectcount(*)fromshop_order_headwherecol_status>0andid_shop_customer=outer_table.id_shop_customer)>5;外部语句{子查询{【实训6-3】（续）子查询与Update语句1．子查询与Update语句（续）（2）更新订单总金额用一条语句更新所有订单的总金额，每个订单的总金额是自己订单行的金额的合计第一步：编写统计指定订单（id）总金额的语句（子查询）第二步：编写更新订单总金额的语句（外部语句）第三步：按照相关子查询的思路，合并上述两条语句Updateshop_order_headsetcol_ammount=(

Selectsum(col_quantity*shop_goods.col_price)fromshop_order_linejoinshop_goodsonshop_order_line.id_shop_goods=shop_goods.id_shop_goodswhereshop_order_line.id_shop_order_head=shop_order_head.id_shop_order_head);Selectsum(col_quantity*shop_goods.col_price)fromshop_order_linejoinshop_goodsonshop_order_line.id_shop_goods=shop_goods.id_shop_goodswhereshop_order_line.id_shop_order_head=idUpdateshop_order_headsetcol_ammount=nnn.n;为什么外部Update

语句不需

where子句？因为子查询引用了外部语句的id子查询与Insert语句在这个小节，先补充讲解“子查询与Create语句”，然后再讲解“子查询与Insert语句”1、子查询与Create语句（1）复制表以及所有数据（2）复制表结构（3）复制表以及部分数据2、子查询与Insert语句（1）复制所有数据到已有的表中（2）复制表的部分列以及部分数据到已有的表中本节内容与教材不同，因为教材的内容不太容易理解1、子查询与Create语句子查询与Create语句的关系相当简单，就是根据查询得到的数据，生成一张新的表（1）复制表以及所有数据复制一张表，包括结构和数据。下述语句将shop_staff的结构和数据全部复制到新表tbl_tmp1（2）复制表结构复制一张表的结构，不包括数据。下述语句将shop_staff的结构复制到新表tbl_tmp2，不包含数据其中的where条件使查询结果为空（3）复制表的部分列以及部分数据复制一张表的部分列，以及部分数据。下述语句将所有男生的shop_staff的col_name列和col_mobile列复制到新表tbl_tmp3查询结果有哪些列，有哪些数据，新表就有哪些列，有哪些数据新表结构与查询结果完全一致Createtabletbl_tmp2asSelect*fromshop_staffwhere1=2;Createtabletbl_tmp1asSelect*fromshop_staff;Createtabletbl_tmp3asSelectcol_name,col_mobilefromshop_staffwherecol_sex='m';2、子查询与Insert语句子查询与Insert语句的关系相当简单，就是将查询得到的数据，插入到另一张表中（1）复制所有数据到已有的表中作为一个演示，下面将前面的“复制表以及所有数据”分为两条语句其中第二条语句就是复制所有数据到已有的表中Createtabletbl_tmp4asSelect*fromshop_staffwhere1=2;--只复制结构--单独复制数据Insertintotbl_tmp4Select*fromshop_staff;2、子查询与Insert语句（续）（2）复制表的部分列以及部分数据到已有的表中作为一个演示，下面将前面的“复制表的部分列以及部分数据”分为两条语句执行的结果与前面的有所不同，这里新表的结构与旧表的结构是相同的这一步需要注意的是，复制的数据必须满足以下条件插入列的列名与查询列的列名在数量、类型和含义必须一一对应，但是可以不同名，也可以从不同表中查询得到满足所有的数据完整性约束，如主键约束、外键约束、非空约束、唯一性约束等Createtabletbl_tmp5asSelect*fromshop_staffwhere1=2;--只复制结构--复制数据部分列的部分数据Insertintotbl_tmp5(col_name,col_sex,col_account,col_password,col_mobile)Selectcol_name,col_sex,col_account,col_password,col_mobilefromshop_staffwherecol_sex='m';3、子查询与Delete语句3．子查询与Delete语句删除从来没有使用过购物车的客户第一步：编写查询订单头表中所有客户id，加distinct关键字结果是使用过购物车的客户的id列表，不含重复值第二步：删除id不在上述id列表中的客户这样就实现了删除从来没有使用过购物车的客户Deletefromshop_customerwhereid_shop_customernotin(

Selectdistinctid_shop_customerfromshop_order_head);Selectdistinctid_shop_customerfromshop_order_head任务2使用视图任务1使用子查询任务2使用视图6.2.1视图的优点6.2.2【实训6-4】创建和使用视图6.2.3【实训6-5】管理视图任务3使用索引6.2.1视图的优点简单性视图可以简化对数据的理解，也可以简化对数据的操作。可以将经常使用的查询定义为视图，在使用时不必每次指定连接操作等复杂的子句，简化查询语句的编写。安全性通过视图可以屏蔽某些数据，也可以只赋予特定的用户查看特定数据的权限，而对其他数据既无法查看，更无法修改，从而保障数据的安全。独立性视图可以屏蔽基表结构变化带来的影响，如果基表的结构发生变化，可以修改视图，而使视图的功能保持不变。微课：6-1使用视图6.2.2【实训6-4】创建和使用视图创建视图视图名是在数据库范围内唯一的标识符，通常以v_起头视图中的Select语句中的每列必须有唯一的列名，不允许出现二义性的列名（不同表的同名列），也不允许出现未定义的列名（无列名的计算列）视图中的Select语句不能有orderby子句当基表的结构改变时，如果改变的部分涉及视图的Select语句，则必须重建视图使用视图视图的作用与表基本相同，可以用在Select语句中使用表的任何地方，包括内、外连接在一定的条件下，甚至还可以用于Insert语句、Delete语句和Update语句等Createview<视图名>as<Select…>;Select列名列表,表达式,[统计函数]from表名或视图名where条件…等子句【实训6-4】6.2.2【实训6-4】创建和使用视图（续）根据Jitor校验器的要求，在dbForge上完成“【实训6-4】创建和使用视图”这是一条Select语句为这条Select语句创建一个视图访问这个视图就如同执行原来的Select语句Select*fromv_goods;Createviewv_goodsasSelectshop_category.col_nameascategory,shop_goods.*fromshop_goodsinnerjoinshop_categoryonshop_goods.id_shop_category=shop_category.id_shop_category;Selectshop_category.col_nameascategory,shop_goods.*fromshop_goodsinnerjoinshop_categoryonshop_goods.id_shop_category=shop_category.id_shop_category;6.2.3【实训6-5】管理视图根据Jitor校验器的要求，在dbForge上完成“【实训6-5】管理视图”1．查看视图列表，与表列表同时列出2．查看视图的数据结构，与查看表结构相同3．变更视图4．丢弃视图Alterview<视图名>as<Select…>;Showtables;Describe表名或视图名;Dropview<视图名>;【实训6-5】任务3使用索引任务1使用子查询任务2使用视图任务3使用索引6.3.1索引及其分类6.3.2索引的设计原则6.3.3【实训6-6】创建索引6.3.4【实训6-7】管理索引6.3.1索引及其分类索引可以极大地提高查询的速度，这就像在新华字典中查找一个字的读音时，如果没有索引，就需要从第一页翻到最后一页，一个字一个字地查找，而通过笔划索引或偏旁索引就可以很快找到这个字。索引的优点提高查询数据的速度。通过唯一性索引，可以实现唯一性约束。提高实现外键约束、分组查询和排序子句的速度。索引的缺点索引的创建和维护需要耗费计算机CPU资源，会降低插入、更新、删除的速度。索引本身需要占用磁盘空间，会消耗计算机硬盘资源。6.3.1索引及其分类（续）1．按用途分类普通索引：为提高查询效率而创建的普通索引。唯一性索引：为实现唯一性约束而创建的索引，也具有普通索引提高查询效率的作用2．按列的数量分类单列索引：仅对一列进行的索引。复合索引：对多列进行的索引，可以是普通索引，也可以是唯一性索引。还有一些其他类型的索引，如聚簇索引、全文索引等，本书不予讲解。6.3.2索引的设计原则1．应该建立索引的情形主键必须建立索引，这是默认的和强制性的，这种索引是唯一性索引不允许出现重复值的列，这时需要建立唯一性索引经常查询的列应该建立索引，这时需要建立普通索引2．不应该建立索引的情形从来不查询或很少查询的列不应建立索引，例如备注列行数少的表不需要建立索引，例如全国省级行政区表，只有34行取值范围很小的列不应建立索引，例如“性别”列，只有“男”和“女”两种值6.3.3【实训6-6】创建索引根据Jitor校验器的要求，在dbForge上完成“【实训6-6】创建索引”1．间接创建索引，例如下述代码primarykey创建一个主