(六)子查询与连接


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条结果,可自行查询验证
由于篇幅限制,不便详细展示19条结果,可自行查询验证

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



  结果与之前相同:


由于篇幅限制,不便详细展示19条结果,可自行查询验证
由于篇幅限制,不便详细展示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(右外链接)

<br />
<br />

内连接 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条记录:


<br />
<br />

外连接 OUTER JOIN

左外连接
左外连接

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


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


<br />


右外连接
右外连接

修改之前的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行(例如“右外链接”中的查询结果)。

<br />
<br />

多表连接

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

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



8、无限级分类表设计

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

首先创建数据表“tdb_goods_types”:


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

插入相关记录:

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

查看该表的所有记录:

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

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

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

以子表为参照
以子表为参照

此时的查询结果为:

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

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

以父表为参照
以父表为参照

此时的查询结果为:

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

第一步,简化父类的数目,以父类商品的名称分组
第一步,简化父类的数目,以父类商品的名称分组
第二步,按照编号排序
第二步,按照编号排序
第三步,修改SQL语句,使用“count”计数,同时赋予别名
第三步,修改SQL语句,使用“count”计数,同时赋予别名

9、多表删除

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

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

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


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

相同商品的编号分别是18、19与21、22,WHERE语句的目的是删除“id”编号较大的重名商品
相同商品的编号分别是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)

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

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

  • 多表更新
    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
    连接分类:
    INNER JOIN(内连接):在MySQL中,JOIN、CROSS JOIN、INNER JOIN是等价的。
    LEFT [OUTER] JOIN(左外连接)
    RIGHT [OUTER] JOIN(右外链接)
    连接条件:
    使用“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子句中,此类子查询必须为其赋予别名。

  • 使用CREATE……SELECT插入记录
    CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,……)] select_statement

  • 多表删除
    DELETE tbl_name[.*] [,tbl_name[.*]] …… FROM tbl_references [WHERE where_condition]

版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

推荐阅读更多精彩内容

  • 数据准备 回顾 记录操作:写操作:INSERT,UPDATE,DELETE读取操作:SELECT 这章主要学习:子...
    齐天大圣李圣杰阅读 488评论 0 4
  • 第1章 初涉MySQL 1.1 MySQL文件 (1)MySQL目录结构 (2)MySQL配置向导文件(安装后配置...
    凛0_0阅读 388评论 1 0
  • 1.肯定的言辞 eg 你的男朋友帮你拿了快递,不要忘记说:辛苦你了,谢谢你。再配上一个蜜汁微笑 要知道感谢的力量可...
    走过平湖烟雨阅读 233评论 0 0
  • -01- 背 景 百度媒体年前曾邀请我们这群写手篡一篇以历史事件,社会百态,春节承载的聚散与变迁等为中心的《...
    唯美宁国阅读 222评论 0 1
  • 有一种精神病药,叫真心罐头。 昨天感冒了,身体特别不舒服。除了鼻音之外,鼻涕不止,还流泪。头也是晕乎的。 小时候,...
    小玉学姐阅读 220评论 0 1