这章主要介绍子查询和连接，当然还涉及多表的删除以及多表的更新。

1. 准备数据

1）首先创建一个新的数据库

1 2	mysql> CREATE DATABASE goods CHARSET utf8; Query OK, 1 row affected (0.00 sec)

还记得第一章里讲过的，新建的数据库默认为utf-8.
检验一下：

mysql> SHOW CREATE DATABASE goods;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| goods    | CREATE DATABASE `goods` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

2）再创建一个数据表

mysql> USE goods;
Database changed
mysql>CREATE TABLE IF NOT EXISTS tdb_goods(
    ->goods_id    SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->goods_name  VARCHAR(150) NOT NULL,
    ->goods_cate  VARCHAR(40)  NOT NULL,
    ->brand_name  VARCHAR(40)  NOT NULL,
    ->goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
    ->is_show     BOOLEAN NOT NULL DEFAULT 1,
    ->is_saleoff  BOOLEAN NOT NULL DEFAULT 0
    ->);
Query OK, 0 rows affected (0.04 sec)

此数据表为商品的信息，包括：商品的id，名字，分类，品牌，价格，是否上架（默认为1，表示在架），是否售空（默认为0，表示没有售空）。

3）插入记录

因为cmd的字符类型为gbk，所以当我们插入中文时总是出错，所以在插入记录前，先把mysql设置为gbk；再进行插入。
注意：此方法只是设置的客户端的编码，并不影响数据表tdb_goods，它还是utf8的编码方式。

1 2	mysql> set names gbk; Query OK, 0 rows affected (0.00 sec)

直接复制下面语句到cmd即可插入记录：

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);
 
 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

使用下面语句可以查看插入的记录，\G的作用是使显示成网格，更加方便查看。

1	SELECT * FROM tdb_goods/G;

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（子查询）

使用子查询时注意：
【a】子查询嵌套在查询内部，并且子查询必须出现在圆括号内。
【b】子查询可以包含多个关键字或条件，如DISTINCT、GROUT BY、ORDER BY、LIMIT,函数等。
【c】子查询的外层查询可以是：SELECT,INSERT，UPDATE，SET或DO。
【d】外查询这里指所有SQL命令的统称，因为SQL称为结构化查询语言
子查询返回值：可以是返回标量、一行、一列、或者子查询，并且可以通过该返回值进行增、删、改、查。

2）由比较运算符引发的子查询

产生子查询的情况可以分为3类，首先来看下由比较运算符引发的子查询：
使用比较运算符的子查询
=、>、<、>=、<=、<>、!=、<=>
语法结构
operand comparison_operator subquery
下面做一个简单的演示，来看一下子查询到底怎么回事？
我们之前已经创建了许多电脑的商品，来看一下商品的平均价格是多少呢？

mysql> SELECT AVG(goods_price) FROM tdb_goods;
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
1 row in set (0.00 sec)

AVG是用于求平均数的聚合函数，其返回一个值。
可以看到返回值得小数部分很小，显然我们不需要这么长，做一下处理：

mysql> SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;
+---------------------------+
| ROUND(AVG(goods_price),2) |
+---------------------------+
|                   5636.36 |
+---------------------------+
1 row in set (0.00 sec)

使用ROUND 四舍五入并且保留两位小数。
接下来想看看哪些商品大于平均价格：

mysql> SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price>=5636.36;
+----------+----------------------------------+-------------+
| 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 |
|       21 |  HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+----------------------------------+-------------+
7 rows in set (0.00 sec)

一共七条符合条件，再来看下命令语句，我是通过输入5636.36 来进行比较的，那么能不能和上个语句结合起来呢？
只需要将5636.36替换为（），里面放入之前求平均价格的语句即可：

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 |
|       21 |  HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+----------------------------------+-------------+
7 rows in set (0.00 sec)

这样我们就使用了比较运算符进行了子查询！！！
当然我们还可以查询其他的，下面来看看超级本的价格都是多少？

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)

超级本一共有三个，下面来看看大于超极本价格的电脑有哪些？

1
2

mysql> SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price>(SELECT goods_price FROM tdb_goods WHERE  goods_cate = '超级本');
ERROR 1242 (21000): Subquery returns more than 1 row

超级本的价格有三个，大于这三个价格，到底大于哪个呢，肯定有错误。
所以这时候我们需要一些修饰符
用ANY、SOME或ALL修饰的比较运算符
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
其中ANY跟SOME是等价的，满足其中一条就可以，ALL必须要满足所有条件

我们修改一下上面的命令：

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 |
|        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 |
|       21 |  HMZ-T3W 头戴显示设备                    |    6999.000 |
+----------+----------------------------------+-------------+
10 rows in set (0.00 sec)

添加了ANY之后，返回了所有大于4299的电脑。
那么试试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 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑            |    9188.000 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑           |   28888.000 |
+----------+----------------------------------+-------------+
3 rows in set (0.02 sec)

返回了大于7999的电脑

3）IN/NOT IN 引发的子查询

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

1 2	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 = '超级本'); 19 rows in set (0.00 sec)

将会有19个商品返回。这是总共22个商品除去那3个超级本的。

4）[ NOT ] EXISTS的子查询

使用[ NOT ] EXISTS的子查询
如果子查询返回任何行，EXISTS将返回TRUE；否则为FALSE
这个使用比较少。

3. 多表更新

1）使用INSERT…SELECT插入记录

语法：
INSERT [INTO] tbl_name [(col_name,…)] SELECT …
它是将查询的结果写入到数据表，列名省略也可以，否则列名之间用逗号隔开。
INSERT语句有两种形式：INSERT SET和INSERT VALUES,区别在于INSERT SET 可以使用子查询，因为一般这样写，INSERT SET A字段=多少，这里的=是比较运算符，可以引发子查询。接下来会验证。
如果一张表中存在很多记录，并且这张表中存在很多重复的信息，例如：上面课程中商品表中的品牌字段和商品类型，汉字占的字节数一定比数字占的字节数多，如果数据越来越多，那么数据表就会越来越庞大，查找的时候速度就会变得很慢，所以我们可以通过外键来解决这个问题，但是使用外键至少有两张数据表，而现在就有一张数据表，所以就要再创建两张数据表存储商品的分类和品牌。

2）新建一个数据表存储分类

mysql> CREATE TABLE 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)

但是此时分类表里还没有数据信息，我们需要从商品表中查找所有的分类信息插入到分类表中，在查找的时候按分类来进行分组，并且插入时有两种方式，方式1录入7条INSERT语句，但这样太麻烦，方式2使用INSERT…SELECT将查询的结果直接写入数据表.

3) 使用方式2插入记录

先看一下分类：

mysql> SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
+---------------+
| goods_cate    |
+---------------+
| 台式机            |
| 平板电脑             |
| 服务器/工作站            |
| 游戏本             |
| 笔记本            |
| 笔记本配件            |
| 超级本              |
+---------------+
7 rows in set (0.00 sec)

如果上面汉字显示异常：
可以使用：

1	set names gbk;

下面将分类信息写入到tdb_goods_cates

mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------+
| cate_id | cate_name     |
+---------+---------------+
|       1 | 台式机            |
|       2 | 平板电脑             |
|       3 | 服务器/工作站            |
|       4 | 游戏本             |
|       5 | 笔记本            |
|       6 | 笔记本配件            |
|       7 | 超级本              |
+---------+---------------+
7 rows in set (0.00 sec)

但是此时商品表还没有分类表的外键，需要参照分类表更新商品表，以前学习的是单表更新，但是现在商品表需要参照分类表进行更新，所以这就是多表更新。

4）多表更新的实现

语法：
UPDATE table_references SET col_name1={expr1 | DEFAULT}
[,col_name2={expr2 | DEFAULT}] … [WHERE where_condition]
需要表的参照关系，这就是将要讲的连接。

表的参照关系：一张表通过INNER JOIN或者LEFT OUTER JOIN去连接另外的一张表并通过ON指定连接条件，也可以给表起别名。

连接表的语法：
table_reference
{[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

连接表处理使用表的名称以外，还需要使用连接类型。

连接类型一共分为3中：在MySQL中，JOIN,CROSS JOIN和INNER JOIN是等价的。

【1】INNER JOIN:内连接.

【2】LEFT [OUTER] JOIN:左外连接。

【3】RIGHT [OUTER] JOIN:右外连接。

下面我们使用INNER JOIN连接两张表，并作为表的参照关系进行多表更新，个例：更新商品表id=22的商品分类属性，修改为6，因为商品分类的名称在商品分类表中对应的id为6。就是说将tdb_goods 中的商品属性，更改为了tdb_goods_cates 中属性名称对应的id。

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
    -> set goods_cate = cate_id;
Query OK, 22 rows affected (0.04 sec)
Rows matched: 22  Changed: 22  Warnings: 0

结果：数据信息被更新了
所以多表更新，就是参照另外的表更新本表。

4. 多表更新之一步到位

上面我们创建多表更新时，一共分为三步：
[A] 创建表
[B] INSERT SELECT 将记录写入数据表
[C] 多表的更新
下面可以减少操作：
CREATE……SELECT
创建数据表同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…..)]
select_statement

上一小节我们将商品的分类已经更新，还有商品的品牌，下面处理品牌。
看一下商品品牌都有哪些种类

mysql> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
+------------+
| brand_name |
+------------+
| IBM        |
| 华硕          |
| 宏碁         |
| 惠普           |
| 戴尔           |
| 索尼           |
| 联想           |
| 苹果          |
| 雷神           |
+------------+
9 rows in set (0.00 sec)

一共有9个品牌。

1）创建表+写入记录

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, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

查看一下这张表：

mysql> SELECT * FROM tdb_goods_brands;
+----------+------------+
| brand_id | brand_name |
+----------+------------+
|        1 | IBM        |
|        2 | 华硕          |
|        3 | 宏碁         |
|        4 | 惠普           |
|        5 | 戴尔           |
|        6 | 索尼           |
|        7 | 联想           |
|        8 | 苹果          |
|        9 | 雷神           |
+----------+------------+
9 rows in set (0.00 sec)

都已经插入好啦。现在一步既可完成。

2）实现多表更新

1
2
3

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name
    -> SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

出现错误，原因是两张表中都含brand_name,系统不知道如何区分啦，这时候就需要使用别名。

mysql> UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name
    -> SET g.brand_name = b.brand_id;
Query OK, 22 rows affected (0.03 sec)
Rows matched: 22  Changed: 22  Warnings: 0

这样就可以啦。

3）数据表减肥

下面我们再来看一下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    |                |
| 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.04 sec)

我们已经将goods_cate和brand_name改为了编号，然而他的数据类型还为字符型，一般我们不这样存储，可以将其改为brand_id和cate_id

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, 22 rows affected (0.04 sec)
Records: 22  Duplicates: 0  Warnings: 0

这就是数据表减肥。
现在分别在tdb_goods_cates和tdb_goods_brands表插入记录：

1
2
3

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');

INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

在tdb_goods数据表写入任意记录：

1	INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

上面这条记录的种类是12，然而tdb_goods_cates中并没有12，但是也没有报错。因为我们没有设置物理约束。

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)

在实际使用过程中，我们不可能种类和品牌只显示数字，应该给用户提供具体的名称，这时候就需要连接来实现。

5. 连接的语法结构

MySQL 在SELECT 语句多表更新多表删除语句中支持JOIN 操作
语法结构
table_reference
{[INNER|CROSS] JOIN {LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr
基本类型：A表链接类型 B表两张表的链接条
数据表参照
table_reference
tbl_name [[AS] alias] | table_sabquery [AS] alias
数据表可以使用tbl_name AS alias_name
或tbl_name alias_name 赋予别名
table_subquery可以作为子查询使用在FROM子句中
这样的子查询必须为其赋予别名

1) 内连接INNER JOIN

使用ON关键字来设定连接条件，也可以使用WHERE来代替
通常使用ON关键字来设定连接条件，
使用WHERE关键字进行结果集记录的过滤

INNER JOIN连接表示两个表的交集。
下面来个示例;
现在我们的商品一共有23个，并且第23个商品的cate_id为12，在tdb_goods_cates中是不存在的，下面使用内连接将这两个表连接在一起查询

1 2	mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates -> ON tdb_goods.cate_id = tdb_goods_cates.cate_id\G;

运行上述语句之后，会有22条商品信息返回，缺失第23条
来看一下其中一条商品的信息：

1
2
3

  goods_id: 5
goods_name: X240(20ALA0EYCD) 12.5英寸超极本
 cate_name: 超级本

通过连接，我们就把商品的种类名称也就显示出来啦，而不是数字。

2）外连接OUTER JOIN

左外连接：显示左表的全部记录和右表符合连接条件的记录
右外连接：显示右表的全部记录和左表符合连接条件的记录
先看一下左外连接：（左表tdb_goods 右表tdb_goods_cates）

1 2	mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT OUTER JOIN tdb_goods_cates -> ON tdb_goods.cate_id = tdb_goods_cates.cate_id\G;

将会返回23条记录，也就是tdb_goods 中的全部，看一下返回的第23条记录

  goods_id: 23
goods_name:  LaserJet Pro P1606dn 黑白激光打印机
 cate_name: NULL
23 rows in set (0.00 sec)

因为cate_name在tdb_goods_cates中不存在所以为NULL。

再来看一下右外连接：

1 2	mysql> SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT OUTER JOIN tdb_goods_cates -> ON tdb_goods.cate_id = tdb_goods_cates.cate_id\G;

一共有25个记录返回，包括右表的全部和左表符合连接条件的22条
其中的后三条：

*************************** 23. row ***************************
  goods_id: NULL
goods_name: NULL
 cate_name: 路由器
*************************** 24. row ***************************
  goods_id: NULL
goods_name: NULL
 cate_name: 交换机
*************************** 25. row ***************************
  goods_id: NULL
goods_name: NULL
 cate_name: 网卡

因为在左表中无连接，所以其他信息为NULL。
这三种连接中，内连接用的相对比较多。

6. 多表连接

上一小节我们实现了两张表的连接，下面来试试三张表的连接，将商品的品牌，种类都通过连接的方式一起显示出来。

1
2
3

mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g
    -> INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id
    -> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

这样最开始创建的22条商品就全部显示出来啦。
要明白，这个显示的结果和原来显示的结果不一样的地方在于：这是通过多张表的连接实现的，而以前是纯粹的一张表查询实现的。
表的连接可以看做是外键的一种逆向操作，外键将多张表分开存储，而连接又将表联系在一起。

7. 关于连接的几点说明

外连接为例：
A LEFT JOIN B join_condition（左外连接，右外连接情况下调换AB就可）
数据表B的结果集依赖数据表A。（A中有的记录才可以在B中显示，否则不显示）
数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。（A表决定B表）
如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行。

如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作: col-namd IS NULL时,如果col-name被定义为NOT NULL, MySQL将在找到符合连执着条件的记录后停止搜索更多的行（查到为null，而要求不为null，此时发生冲突）。

8. 无限级分类表设计

当一个分类下面可能还要无数个子分类时，需要用到：
– 无限分类的数据表设计
下面创建一张无限级分类的数据表：

CREATE TABLE tdb_goods_types(
   type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   type_name VARCHAR(20) NOT NULL,
   parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

插入下面的记录：

INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

查看记录：

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.00 sec)

可以看到家电用器和电脑办公的parent_id 均为0，说明这两个是顶级分类，没有父节点；大家电和生活电器是家用电器的子类。
数据表建好啦，怎么实现查找呢？
自身连接
同一个数据表对其自身进行连接

下面我们将上面的那张表复制一份，看做两张表，分为左和右，如果把左表看作是父表，那么右表就是子表。同时，左表的parent_id 就没有意义啦，而且右表的type_id 也没有意义。

左表(父表p)	右表（子表s）
type_id type_name	type_name parent_id

下面看下如何连接：

mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p
    -> LEFT JOIN tdb_goods_types AS s
    -> ON p.type_id = s.parent_id;
+---------+------------+-----------+
| type_id | type_name  | type_name |
+---------+------------+-----------+
|       1 | 家用电器          | 大家电         |
|       1 | 家用电器          | 生活电器          |
|       2 | 电脑、办公        | 电脑整机          |
|       2 | 电脑、办公        | 电脑配件          |
|       3 | 大家电          | 平板电视         |
|       3 | 大家电          | 空调         |
|       4 | 生活电器           | 电风扇          |
|       4 | 生活电器           | 饮水机         |
|       5 | 平板电视          | NULL      |
|       6 | 空调          | NULL      |
|       7 | 电风扇           | NULL      |
|       8 | 饮水机          | NULL      |
|       9 | 电脑整机           | 笔记本        |
|       9 | 电脑整机           | 超级本          |
|       9 | 电脑整机           | 游戏本         |
|      10 | 电脑配件           | CPU       |
|      10 | 电脑配件           | 主机          |
|      11 | 笔记本         | NULL      |
|      12 | 超级本           | NULL      |
|      13 | 游戏本          | NULL      |
|      14 | CPU        | NULL      |
|      15 | 主机           | NULL      |
+---------+------------+-----------+
22 rows in set (0.00 sec)

第一列是父表的id，第二列是父表的类型，第三列是父类型对应的子类型
可以看到家用电器有两个子类，如果想显示每个父类具体有几个子类的数量呢？

mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) child_count FROM tdb_goods_types AS p
    -> LEFT JOIN tdb_goods_types AS s
    -> ON p.type_id = s.parent_id GROUP BY p.type_name ORDER BY p.type_id;
+---------+------------+-------------+
| type_id | type_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 |
+---------+------------+-------------+
15 rows in set (0.00 sec)

这相当于根据父类查找的子类，那如果我们想要根据子类查找父类呢？

mysql> SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s
    -> LEFT JOIN tdb_goods_types AS p
    -> ON p.type_id = s.parent_id;
+---------+------------+------------+
| type_id | type_name  | type_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)

第一列就是子类的id，第二列子类的名称，第三其父类的名称

9. 多表删除

我们来看一下最开始创建的这张表：

1	mysql> SELECT * FROM tdb_goods\G;

你会发现有一些重复的商品，像第19和22个商品，下面我们的目标就是删除这些重复的商品。
语法：
DELETE tbL_name[.] [, tbl_name[.]] …FROM table_references [WHERE where_condition]
首先来看看哪些是多余的？

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.00 sec)

上面语句的意思是按照goods_name进行分组，附加条件是，相同的goods_name数量大于等于2，这样就把重复的挑选出来啦。
我们可以把上面的结果想象成一张表，参照这张表来删除tdb_goods中的重复商品。

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.03 sec)

delete t1 from tdb_goods as t1 //从本表中删除,将tdb_goods看做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 //t1和t2的连接条件
where t1.goods_id>t2.goods_id; //删除id号较大的条目
这样重复的两条记录就被删除。

小结

子查询是指出现在select语句中的，必须出现在小括号内。
子查询的外层可以是select语句，insert语句、update语句及delete语句。
子查询中可以包含多个关键字或条件，如group by、order by、limit以及相关函数等。
使用子查询的环境：
（1）比较运算符引发的子查询
（2）in或not in引发的子查询
（3）exist或not exist引发的子查询。

多表更新、多表删除、select语句中都可以使用连接。

连接的类型：
（1）内连接（交集，左表和右表都符合条件的记录）
（2）外连接（左连接和右连接）