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（右外链接）
<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无法实现递归查询，因此只能显示一级父类
例如此时需要查询父类的商品编号、名称以及其子类的名称：
以父表为参照
此时的查询结果为：
但这种显示方式比较混乱，对此修改为显示父类商品的编号、名称以及其子类商品的数目：
第一步，简化父类的数目，以父类商品的名称分组
第二步，按照编号排序
第三步，修改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)
运算符＼关键字 | 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]
版权声明：欢迎转载，欢迎扩散，但转载时请标明作者以及原文出处，谢谢合作！ ↓↓↓
网友评论