（六）子查询与连接

1、创建练习使用的数据表

goods_id —— 商品编号
goods_name —— 商品名称
goods_cate —— 商品分类
brand_name —— 商品品牌
goods_price —— 商品价格
is_show —— 商品是否上架，默认为在售
is_saleoff —— 商品是否售罄，默认为否

由于商品中有中文字符出现，因此需要首先设置客户端显示数据的编码为“gbk”，但实际数据库中的存储数据的编码格式仍然是之前设定的“utf8”，不会受影响。

此次举例共填入了22条记录，手动输入过于繁琐，因此提供源码，点击下载使用。

2、子查询简介

子查询(Subquery)是指出现在其他SQL语句内的SELECT子句。

例如：
SELECT＊FROM t1 WHERE col1 = ( SELECT col2 FROM t2);
其中“SELECT＊FROM t1”被称为Outer Query／Outer Statement，即外层查询；
而“SELECT col2 FROM t2”被称为SubQuery，即子查询。

子查询指嵌套在查询内部，且必须始终出现在圆括号内；
子查询可以包含多个关键字或条件，例如：DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等；
子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET、DO。
注意：这里的“外层查询”并不仅仅指“SELECT”查找，而是所有的SQL命令的统称，因为SQL语言被称为是结构化查询语言，包括增、删、改、查等。
子查询可以返回标量、一行、一列或子查询。

3、由比较运算符引发的子查询

语法结构：
operand comparison_operator subquery
比较运算符包括：
=、>、<、>=、<=、<>、!=、<=>

查询所有商品的平均价格，还可以对查询的结果进行四舍五入，保留2位小数：

　　在所有商品中，查询售价大于或等于平均价格的商品，显示其编号、名称及价格：

　　可以看到，在查找大于等于平均价格的商品中，“5636.36”其实就是上一条SQL语句所求得的结果，将这两条语句合并，就是子查询：

通过查询可知在所有商品中属于“超级本”分类的商品共有3件，售价分别是“4999”、“4299”、“7999”。如果想使用子查询的方式，查找售价大于“超级本”的商品，结果报错：

出现错误的原因是之前提到的，子查询的返回值只能是一行，虽然要查找售价大于“超级本”价格的商品，但“超级本”的价格有3个，系统无法得知要与哪个进行比较，此时就需要使用ANY、SOME、ALL关键字来修饰比较运算符：

语法结构：
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)

operand comparison_operator ALL (subquery)

运算符＼关键字 | ANY | SOME| ALL
----|------|----

、>= | 最小值　　　　 | 最小值　　　　 | 最大值　　　　
<、<= | 最大值 | 最大值| 最小值
= | 任意值 | 任意值|
<>、!= | | | 任意值

根据上表修改之前的SQL语句，例如添加“ALL”关键字，要求比所有的“超级本”价格都高，即大于最大值“7999”即可：

4、由[NOT] IN / EXISTS引发的子查询

使用[NOT] IN的语法结构：
operand comparison_operator [NOT] IN (subquery)
= ANY 运算符与 IN 等效；
!= ALL 或<> ALL运算符与 NOT IN 等效。

对于上条SQL语句，首先修改为“!= ALL”关键字，即除去3款“超级本”商品，查询剩余的全部19款商品：

　　可见结果确实是19款：

由于篇幅限制，不便详细展示19条结果，可自行查询验证

再修改为“NOT IN”关键字：

　　结果与之前相同：

由于篇幅限制，不便详细展示19条结果，可自行查询验证

使用[NOT] EXISTS的语法结构：
operand comparison_operator [NOT] EXISTS (subquery)
EXISTS：当子查询返回任何行时，EXISTS返回TRUE，触发外层查询；否则返回FALSE。
NOT EXISTS：与EXISTS相反。

例如，子查询中“id”为100的商品不存在，因此不返回任何行，EXISTS返回FALSE，外层查询为空：

当查询“id”为10的商品时，可以返回结果，EXISTS返回TRUE，外层查询为全部的22件商品：

NOT EXISTS与EXISTS相反，当查询“id”为10的商品时，可以返回结果，NOT EXISTS返回FALSE，外层查询为空；当子查询中“id”为100的商品不存在，不返回任何行时，NOT EXISTS返回TRUE，外层查询为全部的22件商品：

5、使用INSERT……SELECT插入记录

其实目前用于演示的表在实际使用中，有一个严重的缺陷，即重复数据过多，而且中文字符占有更多的字节，会导致数据表使用效率降低，例如下图所示的“笔记本配件”、“索尼”等等：

　　针对上述问题，可以使用外键来解决，创建新的数据表“tdb_goods_cates”：

　　查询“tdb_goods”表的所有记录，并且按"类别"分组：

将查询的分组结果写入到“tdb_goods_cates”数据表中：

6、多表更新

语法结构
UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [,col_name2 = {expr2 | DEFAULT}……
[WHERE where_condition]

在使用多表更新时，需要使用“连接”，这里先简单了解一下：

table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }
table_reference ON conditional_expr

可以理解为：
表1“table_reference”+参照关系“{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }”
+表2“table_reference”+连接条件“ON conditional_expr”

连接类型分为：

INNER JOIN（内连接）
在MySQL中，JOIN、CROSS JOIN、INNER JOIN是等价的。
LEFT [OUTER] JOIN（左外连接）
RIGHT [OUTER] JOIN（右外链接）

此时需要将新创的“tdb_goods_cates”表中的“id”替换到原“tdb_goods”表的“类别”中：

　　对于上述SQL语句的简单理解是：要更新“tdb_goods”表，以内连接“INNER JOIN”的方式连接“tdb_goods_cates”表，连接的条件是“tdb_goods”表中的商品类别“goods_cate”等于“tdb_goods_cates”表中的“cate_name”，最后将“tdb_goods”表中的商品类别“goods_cate”更新为“tdb_goods_cates”表中的“cate_id”。

通过查询可见已实现更新：

但对于多表更新，还存在一种更为简便的方法，目前的方法是首先创建数据表，之后查询数据并写入，最后进行连接更新；那么使用如下语句，便可将创建与查询写入合二为一：

CREATE……SELECT
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,……)] select_statement
创建数据表的同时，将查询的结果写入数据表。

首先查询“tdb_goods”表的所有记录，并且按"品牌"分组：

此步骤仅用作展示品牌数量

之后创建数据表“tdb_goods_brands”并写入查询数据：

可见数据表以创建完成且存在记录：

此时可以使用之前的语句进行连接：

系统提示出错，因为无法分辨两个“brand_name”分别属于哪一张表，此时可以在字段前添加表名来加以区分，但更常使用别名来区分，例如：

可见所有的类别及品牌都已修改，虽然数字可能相同，但实际意义不同：

再来查看该数据表的结构：

虽然此时已修改了记录，但表的根本结构没有改变，此时的“1、2、3……”仍然是字符型，因此此时建议修改表的结构：

此时数据表才算真正的完成了“瘦身”操作，不过可能会有疑问：之前所说的外键，怎么没看到使用“FOREIGN KEY”，其实在实际开发中，使用物理外键即“FOREIGN KEY”的机会并不多，相反这种事实外键会应用的更广泛一些。

7、连接的语法结构

MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。

table_reference {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }
table_reference ON conditional_expr

可以理解为：
　表1“table_reference”+参照关系“{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN }”
　+表2“table_reference”+连接条件“ON conditional_expr”

连接条件：
　使用“ON”关键字来设定连接条件，也可以使用WHERE来代替。
　“ON”关键字更多的用来设定连接条件；
　“WHERE”关键字则进行结果集记录的过滤。

数据表参照：
　　table_reference
　　tbl_name [[AS] alias] | table_subquery [AS] alias
　　数据表可以使用“tbl_name AS alias_name”或“tbl_name alias_name”赋予别名；
　　table_subquery可以作为子查询使用在FROM子句中，此类子查询必须为其赋予别名。

连接类型分为：
　　INNER JOIN（内连接）：在MySQL中，JOIN、CROSS JOIN、INNER JOIN是等价的。
　　LEFT [OUTER] JOIN（左外连接）
　　RIGHT [OUTER] JOIN（右外链接）

内连接 INNER JOIN

内连接

在演示内连接之前，先分别向数据表“tdb_goods_cates”、“tdb_goods_brands”中添加三个商品分类及三个品牌：

之后向数据表“tdb_goods”中添加一条记录：

要注意，该记录中“cate_id”添加的“12”仅仅是符合数据类型的要求而没有报错，但实际上根本没有“id”为“12”的商品分类，此时数据表中共有23条记录。

在实际使用中，对于客户来讲，可能根本不清楚例如“cate_id”中的“6”代表的是什么意思：

因此需要联合查询数据表“tdb_goods”以及数据表“tdb_goods_cates”，由于两张数据表都有“cate_id”字段，需要添加表名加以区分：

此时共查询到结果22条，没有新加入的记录的原因是在“tdb_goods_cates”表中不存在“cate_id”为12的商品分类，而“INNER JOIN”只显示符合连接条件的记录，即共有的部分，因此没有第23条记录：

外连接 OUTER JOIN

左外连接

　　修改之前的SQL语句，将“INNER JOIN”改为“LEFT JOIN”，“OUTER”可以省略：

此时共查询到结果23条，由于是左外连接，因此显示“tdb_goods”表的全部23条记录，但是“tdb_goods_cates”表中没有符合连接条件的记录，即“cate_id”为12的商品分类，因此显示“NULL”：

右外连接

修改之前的SQL语句，将“LEFT JOIN”改为“RIGHT JOIN”，“OUTER”可以省略：

此时共查询到结果25条，由于是右外连接，因此显示“tdb_goods_cates”表的全部10条记录，但是“tdb_goods”表中没有符合连接条件的记录，即分类属于“路由器”、“交换机”、“网卡”的商品，因此显示“NULL”，而且不显示“cate_id”为“12”的商品：

关于外链接的几点说明（以左外连接为例）：

tbl_A LEFT JOIN tbl_B ON join_condition
数据表B的结果集依赖数据表A，即数据表A中存在的记录，在数据表B中才会显示，而数据表B的其他记录则不会显示；
数据表A的结果集根据左连接条件依赖所有数据表（B表除外）；
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）；
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行（例如“右外链接”中的查询结果）。

多表连接

除去之前的两张表连接之外，还可以更多的表进行连接：

此时查询的结果与之前最开始的单表结果相同，但此时的意义完全不同：

8、无限级分类表设计

本篇作为案列使用数据表“tdb_goods”中的商品分类，在实际开发中是远远不够的，那么例如“X宝”、“X东”等电商网站的商品分类是如何实现的，可以参考以下案列：

首先创建数据表“tdb_goods_types”：

type_id —— 分类编号
type_name —— 分类名称
parent_id —— 父类编号

插入相关记录：

此次举例共填入了15条记录，手动输入过于繁琐，因此提供源码，点击下载使用。

查看该表的所有记录：

其中：
“家用电器”与“电脑、办公”都是顶级分类，没有父类；
“大家电”与“生活电器”都属于“家用电器”，父类编号为“1”，即“家用电器”的商品编号；
“平板电视”与“空调”都属于“大家电”，父类编号为“3”，以此类推。

对这种数据表进行查询就需要使用“自身连接”，例如使查询结果显示子类商店的编号、名称以及父类商品的名称，就可以这样理解：

想象有两张完全相同的表，分别是父表与子表，至于如何确定父表、子表的方式不唯一，位置可以交换，只是为了满足别名的需要，否则系统将无法区分这些相同的字段；当确定父表与子表后，父表中“parent_id”字段就没有用处了，因为本身就是父表，而子表中“parent_id”其实就是父表中的“type_id”，因此，就可以做如下考虑：

以子表为参照

此时的查询结果为：

由于MySQL无法实现递归查询，因此只能显示一级父类

例如此时需要查询父类的商品编号、名称以及其子类的名称：

以父表为参照

此时的查询结果为：

但这种显示方式比较混乱，对此修改为显示父类商品的编号、名称以及其子类商品的数目：

第一步，简化父类的数目，以父类商品的名称分组

第二步，按照编号排序

第三步，修改SQL语句，使用“count”计数，同时赋予别名

9、多表删除

语法结构
DELETE tbl_name[.＊] [,tbl_name[.＊]] …… FROM tbl_references [WHERE where_condition]

通过查询发现，在“tdb_goods”数据表中有部分商品名称重复的记录，例如：

细化查询条件，通过商品名称分组，并查询商品名称大于或等于2个的商品，即为重复商品：

与之前的“自身连接”类似，仍然使用这同一张表演示多表删除操作：

相同商品的编号分别是18、19与21、22，WHERE语句的目的是删除“id”编号较大的重名商品

输入SQL语句后提示“有两条记录被删除”，此时查询记录只有21条：

10、操作数据表记录的SQL语句汇总：

子查询
SELECT＊FROM t1 WHERE col1 = ( SELECT col2 FROM t2);
由[NOT] IN 引发的子查询：
operand comparison_operator [NOT] IN (subquery)
= ANY 运算符与 IN 等效；
!= ALL 或<> ALL运算符与 NOT IN 等效。
由[NOT] EXISTS引发的子查询：
operand comparison_operator [NOT] EXISTS (subquery)
EXISTS：当子查询返回任何行时，EXISTS返回TRUE，触发外层查询；否则返回FALSE。
NOT EXISTS：与EXISTS相反。
由比较运算符引发的子查询：
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)