5、子查询与连接
5.1、数据准备
mysql 中对记录操作可分为两类
- 写操作：INSERT、DELETE、UPDATE
- 读取操作：SELECT
若在查询数据表时，发现数据是乱码，可以将编码方式修改为 gbk（默认 utf-8），只需在记录插入后添加以下一个语句即可：
# 需注意的是这只影响 mysql 客户端，并不能改变默认编码方式，只是以 gbk 的编码方式呈现出来
SET NAMES gbk;
5.2、子查询简介
5.2.1、定义
子查询（subquery）：指出现在其他 SQL 语句内的 SELECT 子句。
# 示例
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
其中 SELECT * FROM t1 ，称为 Outer Query/ Outer Statement（外部查询）
SELECT col2 FROM t2，称为 SubQuery
5.2.2、条件：
- 子查询指嵌套在查询内部，且必须始终出现在圆括号内。
- 子查询可以包含多个关键字或条件，如DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。
- 子查询的外层查询可以是：SELECT、INSERT、UPDATE、SET或DO。
5.2.3、返回值
子查询可以返回标量、一行、一列或子查询
5.3、由比较运算符引发的子查询
5.3.1、使用比较运算符的子查询
# 运算符
=、>、<、>=、<=、<>、！=、<=>
# 语法结构
operand comparison_operator subquery
# 一张关于电脑及配件的数据表 tdb_goods，其中有23条记录，有7列，分别是goods_id、goods_name、goods_cate（分类）、brand_name（品牌名）、goods_price、is_show（是否上架）、is_saleoff（是否销售完）
# 求所有商品价格的平均值（AVG聚合函数）
mysql> SELECT AVG(goods_price) FROM tdb_goods;
+------------------+
| AVG(goods_price) |
+------------------+
| 5395.6086957 |
+------------------+
1 row in set (0.01 sec)
# 将平均值四舍五入，取小数点后两位（round聚合函数）
mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
| 5395.61 |
+---------------------------+
1 row in set (0.01 sec)
# 查询价格大于平均值的笔记本（一般查询方式）
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5395.61;
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 22 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+----------------------------------+-------------+
7 rows in set (0.00 sec)
# 查询价格大于平均值的笔记本（子查询方式）
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 22 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+----------------------------------+-------------+
7 rows in set (0.02 sec)
5.3.2、用 ANY、SOME 或 ALL 修饰的比较运算符
当子查询的结果是多个时，在做比较时可以使用 any、some、all 关键字修饰比较运算符。
# 语法结构，any、some 只需符合其中一个即可，all 需要符合全部
operand comparison_operator ANY （subquery）
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
ANY、SOME、ALL关键字
# 查询笔记本的价格
mysql> SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';
+-------------+
| goods_price |
+-------------+
| 4999.000 |
| 4299.000 |
| 7999.000 |
+-------------+
3 rows in set (0.00 sec)
# 查询数据表 tdb_price 中所有比子查询中的最小值（4299）大或等于的笔记本，使用了关键字 ANY
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 5 | X240(20ALA0EYCD) 12.5英寸超极本 | 4999.000 |
| 6 | U330P 13.3英寸超极本 | 4299.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 16 | PowerEdge T110 II服务器 | 5388.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 |
| 22 | HMZ-T3W 头戴显示设备 | 6999.000 |
+----------+----------------------------------+-------------+
11 rows in set (0.01 sec)
# 查询数据表 tdb_price 中所有比子查询中的最小值（7999）大或等于的笔记本，使用了关键字 ALL
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
+----------+----------------------------------+-------------+
| goods_id | goods_name | goods_price |
+----------+----------------------------------+-------------+
| 3 | G150TH 15.6英寸游戏本 | 8499.000 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 |
+----------+----------------------------------+-------------+
5.4、使用 [NOT ] IN 的子查询
5.4.1、IN、NOT IN
# 语法结构
operand comparison_operator [NOT] IN (subquery)
= ANY 运算符与 IN 等效
！= ALL 或 <> ALL 运符与 NOT IN 等效
# NOT IN
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price NOT IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
# != ALL
mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price != ALL (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
5.4.2、EXISTS、NOT EXISTS
如果子查询返回任何行， EXISTS 将返回 True，否则 False
5.5、使用 INSERT...SELECT 插入记录
将查询结果写入数据表
# 语法结构
INSERT [INTO] tbl_name [(col_name,...)]
SELECT ...
# 查看数据表结构
mysql> SELECT * FROM tdb_goods\G
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 笔记本
brand_name: 华硕
goods_price: 3399.000
is_show: 1
is_saleoff: 0
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
goods_cate: 笔记本
brand_name: 联想
goods_price: 4899.000
is_show: 1
is_saleoff: 0
.............................................................
*************************** 22. row ***************************
goods_id: 22
goods_name: HMZ-T3W 头戴显示设备
goods_cate: 笔记本配件
brand_name: 索尼
goods_price: 6999.000
is_show: 1
is_saleoff: 0
*************************** 23. row ***************************
goods_id: 23
goods_name: 商务双肩背包
goods_cate: 笔记本配件
brand_name: 索尼
goods_price: 99.000
is_show: 1
is_saleoff: 0
23 rows in set (0.00 sec)
# 数据表 tdb_goods 中的商品可分为 7 类
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
7 rows in set (0.00 sec)
通过查看 tdb_goods 的数据表结构发现商品分为7类，使用汉字代替的 goods_cate，因为汉字占的字节比字母长，所以当一个数据表很庞大时，就会影响查询速度，最好的办法是使用外键（即将goods_cate的汉字使用数字代替），而使用外键需要用到两张数据表，因此，我们需要将子查询出来的分类记录插入到另外一张数据表中。
物理外键指的是使用foreign key 作为外键关联另一张的字段的连接方法，而且限定了引擎为InnoDB,而逻辑外键，又叫做事实外键，是因为存在语法上的逻辑关联而产生的外键，需要有连接关键词inner join 或者left join 等等和连接部分，也就是on后面的部分,如果需要对应的设置，也可以加上set等语句
# 创建一个新的数据表 tbd_goods_cates，用于存储数据表 tdb_goods 中的商品分类
mysql> CREATE TABLE IF NOT EXISTS tdb_goods_cates(
-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cate_name VARCHAR(40) NOT NULL
-> );
Query OK, 0 rows affected (0.04 sec)
# 查询 tdb_goods_cates 的数据表结构
mysql> DESC tdb_goods_cates;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 将查询结果插入到数据表 tdb_goods_cates 的 cate_name 列中
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
# 查看tdb_goods_cates 结构
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
7 rows in set (0.00 sec)
5.6、多表更新
# 语法结构
UPDATE table_references # 表的参照关系
SET col_name1 = {expr1 | DEFAULT}
[, col_name2 = {expr2 | DEFAULT}]...
[WHERE where_Condition]
# 表的参照关系（table_reference）的语法结构
table_referenece # 表 1
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} # 连接类型
table_reference # 表 2
ON conditional_expr # 连接条件
# 连接类型
INNER JOIN, 内连接
在 MySQL 中，JOIN, CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN, 左外连接
RIGHT [OUTER] JOIN, 右外连接
# 使用内连接的方式把两个数据表连接起来，连接条件为 goods_cate = cate_name，将 goods_cate 设置为 cate_id
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
-> SET goods_cate = cate_id;
Query OK, 23 rows affected (0.01 sec)
Rows matched: 23 Changed: 23 Warnings: 0
# 查询数据表 tdb_goods 结构，发现 goods_cate 已经更新为 cate_id
mysql> SELECT * FROM tdb_goods\G
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 5
brand_name: 华硕
goods_price: 3399.000
is_show: 1
is_saleoff: 0
........................................
5.7、多表更新之一步到位
创建数据表同时将查询结果写入到数据表（CREATE...SELECT）
CREATE TABLE [IF NOT EXISTS ] tbl_name
[(create_definition,...)]
select_statement
将（创建数据表）和（把查询结果插入到新的数据表）两步，合并成一步： **
# 创建数据表，插入查询结果
mysql> CREATE TABLE tdb_goods_brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
# 查看刚创建的数据表
mysql> SELECT * FROM tdb_goods_brands;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
| 1 | IBM |
| 2 | 九州风神 |
| 3 | 华硕 |
| 4 | 宏碁 |
| 5 | 惠普 |
| 6 | 戴尔 |
| 7 | 索尼 |
| 8 | 联想 |
| 9 | 苹果 |
| 10 | 雷神 |
+----------+------------+
10 rows in set (0.00 sec)
多表更新：
# 给两个数据表取别名，以区分各自的 brand_name 列
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brands AS b ON a.brand_name = b.brand_name
-> SET a.brand_name = b.brand_id;
Query OK, 23 rows affected (0.01 sec)
Rows matched: 23 Changed: 23 Warnings: 0
# 查看数据表 tdb_goods 结构，发现 brand_name 已更新为 brand_id
mysql> SELECT * FROM tdb_goods\G
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
goods_cate: 5
brand_name: 3
goods_price: 3399.000
is_show: 1
is_saleoff: 0
...
23 rows in set (0.00 sec)
更改数据类型：
# 查看数据表 tdb_goods，发现更新了之后，goods_cate、brand_name 的名称和数据类型都没有改变，仍然是字符型而不是数字类型
mysql> SHOW COLUMNS FROM tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| goods_name | varchar(150) | NO | | NULL | |
| goods_cate | varchar(40) | NO | | NULL | |
| brand_name | varchar(40) | NO | | NULL | |
| goods_price | decimal(15,3) unsigned | NO | | 0.000 | |
| is_show | tinyint(1) | NO | | 1 | |
| is_saleoff | tinyint(1) | NO | | 0 | |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
# 修改列名和数据类型
mysql> ALTER TABLE tdb_goods
-> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
-> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
Query OK, 23 rows affected (0.09 sec)
Records: 23 Duplicates: 0 Warnings: 0
# 查看数据表结构
mysql> SHOW COLUMNS FROM tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| goods_name | varchar(150) | NO | | NULL | |
| cate_id | smallint(5) unsigned | NO | | NULL | |
| brand_id | smallint(5) unsigned | NO | | NULL | |
| goods_price | decimal(15,3) unsigned | NO | | 0.000 | |
| is_show | tinyint(1) | NO | | 1 | |
| is_saleoff | tinyint(1) | NO | | 0 | |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
5.8、连接
连接多个数据表，从中获取数据
MySQL 在 SELECT 语句、多表更新、多表删除语句中支持 JOIN 操作
# 语法结构
table_referenece # 表 1
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} # 连接类型
table_reference # 表 2
ON conditional_expr # 连接条件
# 连接类型
INNER JOIN, 内连接
在 MySQL 中，JOIN, CROSS JOIN 和 INNER JOIN 是等价的
LEFT [OUTER] JOIN, 左外连接
RIGHT [OUTER] JOIN, 右外连接
数据表参照（table_reference）：
tbl_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用 tbl_name AS alias_name
或 tbl_name alias_name 赋予别名
tbl_subquery 可以作为子查询使用在 FROM 子句中，
这样的子查询必须为其赋予别名。
5.9、内连接 INNER JOIN
一般情况都是使用关键字 ON 来作为连接条件，也可以使用 WHERE（对结果集记录的过滤） 来代替。
内连接
# 查看 tdb_goods 数据表结构
mysql> SHOW COLUMNS FROM tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| goods_name | varchar(150) | NO | | NULL | |
| cate_id | smallint(5) unsigned | NO | | NULL | |
| brand_id | smallint(5) unsigned | NO | | NULL | |
| goods_price | decimal(15,3) unsigned | NO | | 0.000 | |
| is_show | tinyint(1) | NO | | 1 | |
| is_saleoff | tinyint(1) | NO | | 0 | |
+-------------+------------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
# 查看 tdb_goods_cates 数据表结构
mysql> SHOW COLUMNS FROM tdb_goods_cates;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| cate_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| cate_name | varchar(40) | NO | | NULL | |
+-----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 查看数据表 tdb_goods_cates
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
+---------+---------------+
7 rows in set (0.00 sec)
# 给数据表 tdb_goods 插入一条记录，其中 cate_id 设置为12，因为 cate_id 中没有 12，所有，内连接没有显示这条数据
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
Query OK, 1 row affected (0.00 sec)
# 内连接
mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods AS a INNER JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id;
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name | cate_name |
+----------+------------------------------------------------------------------------+---------------+
| 1 | R510VC 15.6英寸笔记本 | 笔记本 |
| 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 |
| 21 | 玄龙精英版 笔记本散热器 | 笔记本配件 |
| 22 | HMZ-T3W 头戴显示设备 | 笔记本配件 |
| 23 | 商务双肩背包 | 笔记本配件 |
+----------+------------------------------------------------------------------------+---------------+
23 rows in set (0.00 sec)
5.10、外连接 OUTER JOIN
左外
右外
# 数据表tdb_goods_cates 插入一条记录
mysql> INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 查看数据表 tdb_goods_cates
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name |
+---------+---------------+
| 1 | 台式机 |
| 2 | 平板电脑 |
| 3 | 服务器/工作站 |
| 4 | 游戏本 |
| 5 | 笔记本 |
| 6 | 笔记本配件 |
| 7 | 超级本 |
| 8 | 路由器 |
| 9 | 交换机 |
| 10 | 网卡 |
+---------+---------------+
10 rows in set (0.00 sec)
# 左外连接，显示左表（tdb_goods）的全部记录，和符合（a.cate_id = b.cate_id）条件的右表（tdb_goods_cates）的记录，没有的为 NULL
mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods AS a LEFT JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id;
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name | cate_name |
+----------+------------------------------------------------------------------------+---------------+
| 1 | R510VC 15.6英寸笔记本 | 笔记本 |
| 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 |
| 3 | G150TH 15.6英寸游戏本 | 游戏本 |
| 4 | X550CC 15.6英寸笔记本 | 笔记本 |
| 5 | X240(20ALA0EYCD 12.5英寸超极本 | 超级本 |
| 6 | U330P 13.3英寸超极本 | 超级本 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 超级本 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 平板电脑 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 平板电脑 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 台式机 |
| 12 | Vostro 3800-R1206 台式电脑 | 台式机 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 台式机 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 台式机 |
| 15 | Z220SFF F4F06PA工作站 | 服务器/工作站 |
| 16 | PowerEdge T110 II服务器 | 服务器/工作站 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 服务器/工作站 |
| 18 | HMZ-T3W 头戴显示设备 | 笔记本配件 |
| 19 | 商务双肩背包 | 笔记本配件 |
| 20 | X3250 M4机架式服务器 2583i14 | 服务器/工作站 |
| 21 | 玄龙精英版 笔记本散热器 | 笔记本配件 |
| 22 | HMZ-T3W 头戴显示设备 | 笔记本配件 |
| 23 | 商务双肩背包 | 笔记本配件 |
| 24 | LaserJet Pro P1606dn 黑白激光打印机 | NULL |
+----------+------------------------------------------------------------------------+---------------+
24 rows in set (0.00 sec)
# 右外连接，显示右表（tdb_goods_cates）的全部记录，和符合（a.cate_id = b.cate_id）条件的左表（tdb_goods）的记录，没有的为 NULL
mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods AS a RIGHT JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id;
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name | cate_name |
+----------+------------------------------------------------------------------------+---------------+
| 1 | R510VC 15.6英寸笔记本 | 笔记本 |
| 2 | Y400N 14.0英寸笔记本电脑 | 笔记本 |
| 3 | G150TH 15.6英寸游戏本 | 游戏本 |
| 4 | X550CC 15.6英寸笔记本 | 笔记本 |
| 5 | X240(20ALA0EYCD 12.5英寸超极本 | 超级本 |
| 6 | U330P 13.3英寸超极本 | 超级本 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 超级本 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 平板电脑 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 平板电脑 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 平板电脑 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 台式机 |
| 12 | Vostro 3800-R1206 台式电脑 | 台式机 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 台式机 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 台式机 |
| 15 | Z220SFF F4F06PA工作站 | 服务器/工作站 |
| 16 | PowerEdge T110 II服务器 | 服务器/工作站 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 服务器/工作站 |
| 18 | HMZ-T3W 头戴显示设备 | 笔记本配件 |
| 19 | 商务双肩背包 | 笔记本配件 |
| 20 | X3250 M4机架式服务器 2583i14 | 服务器/工作站 |
| 21 | 玄龙精英版 笔记本散热器 | 笔记本配件 |
| 22 | HMZ-T3W 头戴显示设备 | 笔记本配件 |
| 23 | 商务双肩背包 | 笔记本配件 |
| NULL | NULL | 路由器 |
| NULL | NULL | 交换机 |
| NULL | NULL | 网卡 |
+----------+------------------------------------------------------------------------+---------------+
26 rows in set (0.01 sec)
5.11、多表连接
# 三张表连接,又回到（tdb_goods）最开始的时候
mysql> SELECT goods_id,goods_name,goods_price,brand_name,cate_name FROM tdb_goods AS a
-> INNER JOIN tdb_goods_cates AS b ON a.cate_id = b.cate_id
-> INNER JOIN tdb_goods_brands AS c ON a.brand_id = c.brand_id;
+----------+------------------------------------------------------------------------+-------------+------------+---------------+
| goods_id | goods_name | goods_price | brand_name | cate_name |
+----------+------------------------------------------------------------------------+-------------+------------+---------------+
| 1 | R510VC 15.6英寸笔记本 | 3399.000 | 华硕 | 笔记本 |
| 2 | Y400N 14.0英寸笔记本电脑 | 4899.000 | 联想 | 笔记本 |
| 3 | G150TH 15.6英寸游戏本 | 8499.000 | 雷神 | 游戏本 |
| 4 | X550CC 15.6英寸笔记本 | 2799.000 | 华硕 | 笔记本 |
| 5 | X240(20ALA0EYCD 12.5英寸超极本 | 4999.000 | 联想 | 超级本 |
| 6 | U330P 13.3英寸超极本 | 4299.000 | 联想 | 超级本 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7999.000 | 索尼 | 超级本 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 1998.000 | 苹果 | 平板电脑 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 3388.000 | 苹果 | 平板电脑 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 2788.000 | 苹果 | 平板电脑 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 3499.000 | 联想 | 台式机 |
| 12 | Vostro 3800-R1206 台式电脑 | 2899.000 | 戴尔 | 台式机 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 9188.000 | 苹果 | 台式机 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 3699.000 | 宏碁 | 台式机 |
| 15 | Z220SFF F4F06PA工作站 | 4288.000 | 惠普 | 服务器/工作站 |
| 16 | PowerEdge T110 II服务器 | 5388.000 | 戴尔 | 服务器/工作站 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 28888.000 | 苹果 | 服务器/工作站 |
| 18 | HMZ-T3W 头戴显示设备 | 6999.000 | 索尼 | 笔记本配件 |
| 19 | 商务双肩背包 | 99.000 | 索尼 | 笔记本配件 |
| 20 | X3250 M4机架式服务器 2583i14 | 6888.000 | IBM | 服务器/工作站 |
| 21 | 玄龙精英版 笔记本散热器 | 99.000 | 九州风神 | 笔记本配件 |
| 22 | HMZ-T3W 头戴显示设备 | 6999.000 | 索尼 | 笔记本配件 |
| 23 | 商务双肩背包 | 99.000 | 索尼 | 笔记本配件 |
+----------+------------------------------------------------------------------------+-------------+------------+---------------+
23 rows in set (0.00 sec)
5.12、关于连接的几点说明
外连接：
# 左外连接
A LIFET JOIN B join_condition
- 数据表 B 的结果集依赖数据表 A
- 数据表 A 的结果集根据左连接条件依赖所有数据表（B 表除外）
- 左外连接条件决定如何检索数据表 B （在没有指定 WHERE 条件的情况下）
- 如果数据表 A 的某条记录符合 WHERE 条件，但是在数据表 B 不存在，符合连接条件的记录，将生成一个所有列为空的额外的 B 行。
- 如果使用内连接查找的记录在连接数据表中不存在，并且在 WHERE 子句中尝试以下操作： col_name IS NULL 时，如果 col_name 被定义为 NOT NULL，mysql 将在找到符合连接条件的记录后停止搜索更多的行。
5.13、无限极分类表设计
一般情况下，网站上的分类分为很多级，如书籍-文学-（散文、诗歌、文言文、歌剧等），因此在创建数据表时，需要对记录的分类进行无限极分类。
自身连接：
同一个数据表对其自身进行连接。
# 数据表 tdb_goods_types
mysql> SELECT * FROM tdb_goods_types;
+---------+------------+-----------+
| type_id | type_name | parent_id |
+---------+------------+-----------+
| 1 | 家用电器 | 0 |
| 2 | 电脑、办公 | 0 |
| 3 | 大家电 | 1 |
| 4 | 生活电器 | 1 |
| 5 | 平板电视 | 3 |
| 6 | 空调 | 3 |
| 7 | 电风扇 | 4 |
| 8 | 饮水机 | 4 |
| 9 | 电脑整机 | 2 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 9 |
| 12 | 超级本 | 9 |
| 13 | 游戏本 | 9 |
| 14 | CPU | 10 |
| 15 | 主机 | 10 |
+---------+------------+-----------+
15 rows in set (0.01 sec)
现有一张关于家用电器、电脑和办公分类的数据表 tdb_goods_types，其中 parent_id 对应的是 type_id，比如说，电脑、办公和家用电器为顶级分类（对应 parent_id 为 0），大家电、生活电器是属于家用电器分类下的（对应的 parent_id 为 1），平板电视是属于大家电分类下的（对应的 parent_id 为 3），现想将哪种电器是属于哪个分类下的一一呈现出来：
5.13.1、左表为子表，右表为父表
将 tdb_goods_types 想象成左右两张表，假设：
- 左表为子表：s.tdb_goods_types
- 右表为父表：p.tdb_goods_types
现将两张表连接起来,其中 child_id，child_name 属于子表，parents_name 属于父表，从表中可以看出，家用电器、电脑、办公为顶级分类，不属于其他分类，大家电、生活电器、平板电视属于家用电器（也就是说它的父类为家用电器）。
mysql> SELECT s.type_id AS child_id,s.type_name AS child_name,p.type_name AS parents_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p
-> ON s.parent_id = p.type_id;
+----------+------------+--------------+
| child_id | child_name | parents_name |
+----------+------------+--------------+
| 1 | 家用电器 | NULL |
| 2 | 电脑、办公 | NULL |
| 3 | 大家电 | 家用电器 |
| 4 | 生活电器 | 家用电器 |
| 5 | 平板电视 | 大家电 |
| 6 | 空调 | 大家电 |
| 7 | 电风扇 | 生活电器 |
| 8 | 饮水机 | 生活电器 |
| 9 | 电脑整机 | 电脑、办公 |
| 10 | 电脑配件 | 电脑、办公 |
| 11 | 笔记本 | 电脑整机 |
| 12 | 超级本 | 电脑整机 |
| 13 | 游戏本 | 电脑整机 |
| 14 | CPU | 电脑配件 |
| 15 | 主机 | 电脑配件 |
+----------+------------+--------------+
15 rows in set (0.00 sec)
5.13.2、左表为父表，右表为父子
将 tdb_goods_types 想象成左右两张表，假设：
- 左表为父表：p.tdb_goods_types
- 右表为子表：s.tdb_goods_types
现将两张表连接起来,其中 parents_id，parents_name 属于父表，child_name 属于子表。
一、左为父表，右为子表：
mysql> SELECT p.type_id AS parents_id,
-> p.type_name AS parents_name,
-> s.type_name AS child_name
-> FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s
-> ON s.parent_id = p.type_id;
+------------+--------------+------------+
| parents_id | parents_name | child_name |
+------------+--------------+------------+
| 1 | 家用电器 | 大家电 |
| 1 | 家用电器 | 生活电器 |
| 3 | 大家电 | 平板电视 |
| 3 | 大家电 | 空调 |
| 4 | 生活电器 | 电风扇 |
| 4 | 生活电器 | 饮水机 |
| 2 | 电脑、办公 | 电脑整机 |
| 2 | 电脑、办公 | 电脑配件 |
| 9 | 电脑整机 | 笔记本 |
| 9 | 电脑整机 | 超级本 |
| 9 | 电脑整机 | 游戏本 |
| 10 | 电脑配件 | CPU |
| 10 | 电脑配件 | 主机 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
+------------+--------------+------------+
22 rows in set (0.00 sec)
二、对 p.type_name 进行分类：
mysql> SELECT p.type_id AS parents_id,
-> p.type_name AS parents_name,
-> s.type_name AS child_name
-> FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s
-> ON s.parent_id = p.type_id GROUP BY p.type_name
+------------+--------------+------------+
| parents_id | parents_name | child_name |
+------------+--------------+------------+
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
| 3 | 大家电 | 平板电视 |
| 1 | 家用电器 | 大家电 |
| 5 | 平板电视 | NULL |
| 13 | 游戏本 | NULL |
| 4 | 生活电器 | 电风扇 |
| 2 | 电脑、办公 | 电脑整机 |
| 9 | 电脑整机 | 笔记本 |
| 10 | 电脑配件 | CPU |
| 7 | 电风扇 | NULL |
| 6 | 空调 | NULL |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 8 | 饮水机 | NULL |
+------------+--------------+------------+
15 rows in set (0.00 sec)
三、对 p.type_id 进行递增排序：
mysql> SELECT p.type_id AS parents_id,
-> p.type_name AS parents_name,
-> s.type_name AS child_name
-> FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s
-> ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
+------------+--------------+------------+
| parents_id | parents_name | child_name |
+------------+--------------+------------+
| 1 | 家用电器 | 大家电 |
| 2 | 电脑、办公 | 电脑整机 |
| 3 | 大家电 | 平板电视 |
| 4 | 生活电器 | 电风扇 |
| 5 | 平板电视 | NULL |
| 6 | 空调 | NULL |
| 7 | 电风扇 | NULL |
| 8 | 饮水机 | NULL |
| 9 | 电脑整机 | 笔记本 |
| 10 | 电脑配件 | CPU |
| 11 | 笔记本 | NULL |
| 12 | 超级本 | NULL |
| 13 | 游戏本 | NULL |
| 14 | CPU | NULL |
| 15 | 主机 | NULL |
+------------+--------------+------------+
15 rows in set (0.00 sec)
四：对 s.type_name 进行计算数目：
mysql> SELECT p.type_id AS parents_id,
-> p.type_name AS parents_name,
-> count(s.type_name) AS child_count
-> FROM tdb_goods_types AS p
-> LEFT JOIN tdb_goods_types AS s
-> ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;
+------------+--------------+-------------+
| parents_id | parents_name | child_count |
+------------+--------------+-------------+
| 1 | 家用电器 | 2 |
| 2 | 电脑、办公 | 2 |
| 3 | 大家电 | 2 |
| 4 | 生活电器 | 2 |
| 5 | 平板电视 | 0 |
| 6 | 空调 | 0 |
| 7 | 电风扇 | 0 |
| 8 | 饮水机 | 0 |
| 9 | 电脑整机 | 3 |
| 10 | 电脑配件 | 2 |
| 11 | 笔记本 | 0 |
| 12 | 超级本 | 0 |
| 13 | 游戏本 | 0 |
| 14 | CPU | 0 |
| 15 | 主机 | 0 |
+------------+--------------+-------------+
5.14、多表删除
DELETE tb_name[.*] [,tbl_name[.*]] ...
FROM table_references
[WHERE where_condition]
使用对自身的模拟，来删除数据表中多余的记录：
# 对数据表 tdb_goods 进行查询发现，有两条记录是多余的
mysql> SELECT goods_id,goods_name FROM tdb_goods
-> GROUP BY goods_name
-> HAVING count(goods_name) >= 2;
+----------+-----------------------+
| goods_id | goods_name |
+----------+-----------------------+
| 18 | HMZ-T3W 头戴显示设备 |
| 19 | 商务双肩背包 |
+----------+-----------------------+
2 rows in set (0.01 sec)
# 删除多余的记录，留下 goods_id 小的那个
mysql> DELETE t1 FROM tdb_goods AS t1
-> LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2) AS t2
-> ON t1.goods_name = t2.goods_name
-> WHERE t1.goods_id > t2.goods_id;
Query OK, 2 rows affected (0.02 sec)
mysql> SELECT * FROM tdb_goods;
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
| goods_id | goods_name | cate_id | brand_id | goods_price | is_show | is_saleoff |
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
| 1 | R510VC 15.6英寸笔记本 | 5 | 3 | 3399.000 | 1 | 0 |
| 2 | Y400N 14.0英寸笔记本电脑 | 5 | 8 | 4899.000 | 1 | 0 |
| 3 | G150TH 15.6英寸游戏本 | 4 | 10 | 8499.000 | 1 | 0 |
| 4 | X550CC 15.6英寸笔记本 | 5 | 3 | 2799.000 | 1 | 0 |
| 5 | X240(20ALA0EYCD 12.5英寸超极本 | 7 | 8 | 4999.000 | 1 | 0 |
| 6 | U330P 13.3英寸超极本 | 7 | 8 | 4299.000 | 1 | 0 |
| 7 | SVP13226SCB 13.3英寸触控超极本 | 7 | 7 | 7999.000 | 1 | 0 |
| 8 | iPad mini MD531CH/A 7.9英寸平板电脑 | 2 | 9 | 1998.000 | 1 | 0 |
| 9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版） | 2 | 9 | 3388.000 | 1 | 0 |
| 10 | iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 2 | 9 | 2788.000 | 1 | 0 |
| 11 | IdeaCentre C340 20英寸一体电脑 | 1 | 8 | 3499.000 | 1 | 0 |
| 12 | Vostro 3800-R1206 台式电脑 | 1 | 6 | 2899.000 | 1 | 0 |
| 13 | iMac ME086CH/A 21.5英寸一体电脑 | 1 | 9 | 9188.000 | 1 | 0 |
| 14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ） | 1 | 4 | 3699.000 | 1 | 0 |
| 15 | Z220SFF F4F06PA工作站 | 3 | 5 | 4288.000 | 1 | 0 |
| 16 | PowerEdge T110 II服务器 | 3 | 6 | 5388.000 | 1 | 0 |
| 17 | Mac Pro MD878CH/A 专业级台式电脑 | 3 | 9 | 28888.000 | 1 | 0 |
| 18 | HMZ-T3W 头戴显示设备 | 6 | 7 | 6999.000 | 1 | 0 |
| 19 | 商务双肩背包 | 6 | 7 | 99.000 | 1 | 0 |
| 20 | X3250 M4机架式服务器 2583i14 | 3 | 1 | 6888.000 | 1 | 0 |
| 21 | 玄龙精英版 笔记本散热器 | 6 | 2 | 99.000 | 1 | 0 |
| 24 | LaserJet Pro P1606dn 黑白激光打印机 | 12 | 4 | 1849.000 | 1 | 0 |
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
22 rows in set (0.00 sec)
网友评论