第五章 子查询与连接

一、子查询

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

  • 子查询指嵌套在查询内部，且必须始终出现在圆括号内。
  • 子查询可以包含多个关键词或条件
    • 如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等。
  • 子查询的外层查询可以是:SELECT,INSERT,UPDATE,SET或DO。

2.子查询的返回值

子查询可以返回标量、一行、一列或子查询。

3.使用比较运算符的子查询

使用比较运算符的子查询
=，>，<，>=，<=，<>，!=，<=>
语法结构

operand comparison_operator subquery

example:

SELECT AVG(goods_price) FROM tdb_goods;
/*Result
+------------------+
| AVG(goods_price) |
+------------------+
|     5636.3636364 |
+------------------+
*/
SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods;
/*Result
+----------------------------+
| ROUND(AVG(goods_price), 2) |
+----------------------------+
|                    5636.36 |
+----------------------------+
*/
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price >= 5636.36;
/*Result
+----------+----------------------------------+-------------+
| 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 |
+----------+----------------------------------+-------------+
*/
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price >= (SELECT ROUND(AVG(goods_price), 2) FROM tdb_goods);
/*Result
+----------+----------------------------------+-------------+
| 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 |
+----------+----------------------------------+-------------+
*/
SELECT goods_name, goods_price FROM tdb_goods
WHERE goods_cate = '超级本';
/*Result
+---------------------------------+-------------+
| goods_name                      | goods_price |
+---------------------------------+-------------+
| X240(20ALA0EYCD) 12.5英寸超极本 |    4999.000 |
| U330P 13.3英寸超极本            |    4299.000 |
| SVP13226SCB 13.3英寸触控超极本  |    7999.000 |
+---------------------------------+-------------+
*/

4.用ANY,SOME或ALL修饰的比较运算符

operand comparison_operator ANY(subquery)
operand comparison_operator SOME(subquery)    --和ANY等价
operand comparison_operator ALL(subquery)

5.ANY,SOME,ALL关键字

运算符 ANY SOME ALL
>,>= 最小值 最小值 最大值
<,<= 最大值 最大值 最小值
= 任意值 任意值
<>,!= 任意值

example:

SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
/*Result
+----------+----------------------------------+-------------+
| 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 |
+----------+----------------------------------+-------------+
*/
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
/*Result
+----------+----------------------------------+-------------+
| 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 |
+----------+----------------------------------+-------------+
*/
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
/*Result
+----------+---------------------------------+-------------+
| goods_id | goods_name                      | goods_price |
+----------+---------------------------------+-------------+
|        5 | X240(20ALA0EYCD) 12.5英寸超极本 |    4999.000 |
|        6 | U330P 13.3英寸超极本            |    4299.000 |
|        7 | SVP13226SCB 13.3英寸触控超极本  |    7999.000 |
+----------+---------------------------------+-------------+
*/

6.使用[NOT] IN的子查询

语法结构

operand comparison_operator [NOT] IN(subquery)

= ANY与运算符IN等效
!=ALL或<>ALL运算符与NOT IN等效。

example:

SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price NOT IN(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本');
/*Result
+----------+------------------------------------------------------------------------+-------------+
| goods_id | goods_name                                                             | goods_price |
+----------+------------------------------------------------------------------------+-------------+
|        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 |
|       21 |  HMZ-T3W 头戴显示设备                                                  |    6999.000 |
|       22 | 商务双肩背包                                                           |      99.000 |
+----------+------------------------------------------------------------------------+-------------+
*/

7.使用[NOT] EXISTS的子查询

如果子查询返回任何行，EXISTS将返回TRUE,否则返回FALSE。

补充：INSERT...SELECT

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name, ...)]
SELECT ...

example:

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
  cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  cate_name VARCHAR(40)
);
SELECT goods_cate FROM tdb_goods
GROUP BY goods_cate;
/*Result
+---------------+
| goods_cate    |
+---------------+
| 台式机        |
| 平板电脑      |
| 服务器/工作站 |
| 游戏本        |
| 笔记本        |
| 笔记本配件    |
| 超级本        |
+---------------+
*/
INSERT tdb_goods_cates(cate_name)
SELECT goods_cate FROM tdb_goods
GROUP BY goods_cate;
SELECT * FROM tdb_goods_cates;
/*Result
+---------+---------------+
| cate_id | cate_name     |
+---------+---------------+
|       1 | 台式机        |
|       2 | 平板电脑      |
|       3 | 服务器/工作站 |
|       4 | 游戏本        |
|       5 | 笔记本        |
|       6 | 笔记本配件    |
|       7 | 超级本        |
+---------+---------------+
*/

二、多表更新

UPDATE table_references
SET col_name1 = {expr1| DEFAULT}
[, col_name2 = {expr2| DEFAULT}]...
[WHERE where_condition]

example:

UPDATE tdb_goods
INNER JOIN tdb_goods_cates ON goods_cate = cate_name
SET goods_cate = cate_id;
SELECT * FROM tdb_goods;
/*Result
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name                                                             | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
|        1 | R510VC 15.6英寸笔记本                                                  | 5          | 华硕       |    3399.000 |       1 |          0 |
|        2 | Y400N 14.0英寸笔记本电脑                                               | 5          | 联想       |    4899.000 |       1 |          0 |
|        3 | G150TH 15.6英寸游戏本                                                  | 4          | 雷神       |    8499.000 |       1 |          0 |
|        4 | X550CC 15.6英寸笔记本                                                  | 5          | 华硕       |    2799.000 |       1 |          0 |
|        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        | 7          | 联想       |    4999.000 |       1 |          0 |
|        6 | U330P 13.3英寸超极本                                                   | 7          | 联想       |    4299.000 |       1 |          0 |
|        7 | SVP13226SCB 13.3英寸触控超极本                                         | 7          | 索尼       |    7999.000 |       1 |          0 |
|        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    | 2          | 苹果       |    1998.000 |       1 |          0 |
|        9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      | 2          | 苹果       |    3388.000 |       1 |          0 |
|       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） | 2          | 苹果       |    2788.000 |       1 |          0 |
|       11 | IdeaCentre C340 20英寸一体电脑                                         | 1          | 联想       |    3499.000 |       1 |          0 |
|       12 | Vostro 3800-R1206 台式电脑                                             | 1          | 戴尔       |    2899.000 |       1 |          0 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑                                        | 1          | 苹果       |    9188.000 |       1 |          0 |
|       14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     | 1          | 宏碁       |    3699.000 |       1 |          0 |
|       15 | Z220SFF F4F06PA工作站                                                  | 3          | 惠普       |    4288.000 |       1 |          0 |
|       16 | PowerEdge T110 II服务器                                                | 3          | 戴尔       |    5388.000 |       1 |          0 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑                                       | 3          | 苹果       |   28888.000 |       1 |          0 |
|       18 |  HMZ-T3W 头戴显示设备                                                  | 6          | 索尼       |    6999.000 |       1 |          0 |
|       19 | 商务双肩背包                                                           | 6          | 索尼       |      99.000 |       1 |          0 |
|       20 | X3250 M4机架式服务器 2583i14                                           | 3          | IBM        |    6888.000 |       1 |          0 |
|       21 |  HMZ-T3W 头戴显示设备                                                  | 6          | 索尼       |    6999.000 |       1 |          0 |
|       22 | 商务双肩背包                                                           | 6          | 索尼       |      99.000 |       1 |          0 |
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
*/

三、多表更新之一步到位

CREATE...SELECT

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition, ...)]
select_statement

example:

SELECT brand_name FROM tdb_goods GROUP BY brand_name;
/*Result
+------------+
| brand_name |
+------------+
| IBM        |
| 华硕       |
| 宏碁       |
| 惠普       |
| 戴尔       |
| 索尼       |
| 联想       |
| 苹果       |
| 雷神       |
+------------+
*/
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;
SELECT * FROM tdb_goods_brands;
/*Result
+----------+------------+
| brand_id | brand_name |
+----------+------------+
|        1 | IBM        |
|        2 | 华硕       |
|        3 | 宏碁       |
|        4 | 惠普       |
|        5 | 戴尔       |
|        6 | 索尼       |
|        7 | 联想       |
|        8 | 苹果       |
|        9 | 雷神       |
+----------+------------+
*/
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;
SELECT * FROM tdb_goods;
/*Result
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
| goods_id | goods_name                                                             | goods_cate | brand_name | goods_price | is_show | is_saleoff |
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
|        1 | R510VC 15.6英寸笔记本                                                  | 5          | 2          |    3399.000 |       1 |          0 |
|        2 | Y400N 14.0英寸笔记本电脑                                               | 5          | 7          |    4899.000 |       1 |          0 |
|        3 | G150TH 15.6英寸游戏本                                                  | 4          | 9          |    8499.000 |       1 |          0 |
|        4 | X550CC 15.6英寸笔记本                                                  | 5          | 2          |    2799.000 |       1 |          0 |
|        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        | 7          | 7          |    4999.000 |       1 |          0 |
......
+----------+------------------------------------------------------------------------+------------+------------+-------------+---------+------------+
*/
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT NOT NULL;
SHOW COLUMNS FROM tdb_goods;
/*Result
+-------------+------------------------+------+-----+---------+----------------+
| 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(6)            | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+
*/

四、连接

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

1.语法结构

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

2.数据表参照

table_reference
tbl_name[[AS]alias]|table_subquery[AS]alias

数据表可以使用tbl_name AS alias_name
或tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

3.链接类型

  • INNER JOIN,内连接</br>
    • 在MySQL中, JOIN, CROSS JOIN和INNER JOIN是等价的。
  • LEFT [OUTER] JOIN,左外连接</br>
  • RIGHT [OUTER] JOIN,右外连接</br>

4.连接条件

使用ON关键词来设定连接条件，也可以使用WHERE来代替。

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

5.内连接

显示左表级右表符合连接条件的记录

example:

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;
/*Result
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name                                                             | cate_name     |
+----------+------------------------------------------------------------------------+---------------+
|        1 | R510VC 15.6英寸笔记本                                                  | 笔记本        |
|        2 | Y400N 14.0英寸笔记本电脑                                               | 笔记本        |
......
|       21 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    |
|       22 | 商务双肩背包                                                           | 笔记本配件    |
+----------+------------------------------------------------------------------------+---------------+
*/

6.左外连接

显示左表的全部记录及右表符合连接条件的记录。

example:

SELECT goods_id, goods_name, cate_name FROM tdb_goods
LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
/*Result
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name                                                             | cate_name     |
+----------+------------------------------------------------------------------------+---------------+
......
|       20 | X3250 M4机架式服务器 2583i14                                           | 服务器/工作站 |
|       21 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    |
|       22 | 商务双肩背包                                                           | 笔记本配件    |
|       23 |  LaserJet Pro P1606dn 黑白激光打印机                                   | NULL          |
+----------+------------------------------------------------------------------------+---------------+
*/

7.右外连接

显示右表的全部记录及左表符合连接条件的记录

example:

SELECT goods_id, goods_name, cate_name FROM tdb_goods
RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
/*Result
+----------+------------------------------------------------------------------------+---------------+
| goods_id | goods_name                                                             | cate_name     |
+----------+------------------------------------------------------------------------+---------------+
|        1 | R510VC 15.6英寸笔记本                                                  | 笔记本        |
......
|       22 | 商务双肩背包                                                           | 笔记本配件    |
|     NULL | NULL                                                                   | 路由器        |
|     NULL | NULL                                                                   | 交换机        |
|     NULL | NULL                                                                   | 网卡          |
+----------+------------------------------------------------------------------------+---------------+
*/

8.多表连接

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;
/*Result
+----------+------------------------------------------------------------------------+---------------+------------+-------------+
| goods_id | goods_name                                                             | cate_name     | brand_name | goods_price |
+----------+------------------------------------------------------------------------+---------------+------------+-------------+
|        1 | R510VC 15.6英寸笔记本                                                  | 笔记本        | 华硕       |    3399.000 |
|        2 | Y400N 14.0英寸笔记本电脑                                               | 笔记本        | 联想       |    4899.000 |
......
|       21 |  HMZ-T3W 头戴显示设备                                                  | 笔记本配件    | 索尼       |    6999.000 |
|       22 | 商务双肩背包                                                           | 笔记本配件    | 索尼       |      99.000 |
+----------+------------------------------------------------------------------------+---------------+------------+-------------+
*/

9.关于连接的几点说明

  • 外链接
A LEFT JOIN B join_condition
  • 数据表B的结果集依赖数据表A。
  • 数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
  • 左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。
  • 如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。
  • 如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作:col_name IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多的行。

10.无限级分类表设计

CREATE TABLE tdb_goods_types(
  type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -分类id
  type_name VARCHAR(20) NOT NULL,                         -分类名称
  parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0          -父类id
);

11.自身连接

同一个数据表对其自身进行连接。

SELECT s.type_id, s.type_name, p.type_name
FROM tdb_goods_types AS s
LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;
/*Result
+---------+------------+------------+
| type_id | type_name  | type_name  |
+---------+------------+------------+
|       1 | 家用电器   | NULL       |
|       2 | 电脑、办公 | NULL       |
|       3 | 大家电     | 家用电器   |
|       4 | 生活电器   | 家用电器   |
|       5 | 平板电视   | 大家电     |
|       6 | 空调       | 大家电     |
|       7 | 电风扇     | 生活电器   |
|       8 | 饮水机     | 生活电器   |
|       9 | 电脑整机   | 电脑、办公 |
|      10 | 电脑配件   | 电脑、办公 |
|      11 | 笔记本     | 电脑整机   |
|      12 | 超级本     | 电脑整机   |
|      13 | 游戏本     | 电脑整机   |
|      14 | CPU        | 电脑配件   |
|      15 | 主机       | 电脑配件   |
+---------+------------+------------+
*/
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;
/*Result
+---------+------------+-----------+
| type_id | type_name  | type_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      |
+---------+------------+-----------+
*/
SELECT p.type_id, p.type_name,
count(s.type_name) AS children_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;
/*Result
+---------+------------+---------------+
| type_id | type_name  | children_coun |
+---------+------------+---------------+
|       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 |
+---------+------------+---------------+
*/

12.多表删除

DELETE tbl_name[.*][, tbl_name[.*]]...
FROM table_references
[WHERE where_condition]

example:

-- 查找重复记录
SELECT goods_id,goods_name FROM tdb_goods
GROUP BY goods_name HAVING count(goods_name) >= 2;
/*Result
+----------+-----------------------+
| goods_id | goods_name            |
+----------+-----------------------+
|       18 |  HMZ-T3W 头戴显示设备 |
|       19 | 商务双肩背包          |
+----------+-----------------------+
*/
-- 删除重复记录
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;
/*Result
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
| goods_id | goods_name                                                             | cate_id | brand_id | goods_price | is_show | is_saleoff |
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
|        1 | R510VC 15.6英寸笔记本                                                  |       5 |        2 |    3399.000 |       1 |          0 |
|        2 | Y400N 14.0英寸笔记本电脑                                               |       5 |        7 |    4899.000 |       1 |          0 |
|        3 | G150TH 15.6英寸游戏本                                                  |       4 |        9 |    8499.000 |       1 |          0 |
|        4 | X550CC 15.6英寸笔记本                                                  |       5 |        2 |    2799.000 |       1 |          0 |
|        5 | X240(20ALA0EYCD) 12.5英寸超极本                                        |       7 |        7 |    4999.000 |       1 |          0 |
|        6 | U330P 13.3英寸超极本                                                   |       7 |        7 |    4299.000 |       1 |          0 |
|        7 | SVP13226SCB 13.3英寸触控超极本                                         |       7 |        6 |    7999.000 |       1 |          0 |
|        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                    |       2 |        8 |    1998.000 |       1 |          0 |
|        9 | iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）                      |       2 |        8 |    3388.000 |       1 |          0 |
|       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版） |       2 |        8 |    2788.000 |       1 |          0 |
|       11 | IdeaCentre C340 20英寸一体电脑                                         |       1 |        7 |    3499.000 |       1 |          0 |
|       12 | Vostro 3800-R1206 台式电脑                                             |       1 |        5 |    2899.000 |       1 |          0 |
|       13 | iMac ME086CH/A 21.5英寸一体电脑                                        |       1 |        8 |    9188.000 |       1 |          0 |
|       14 | AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）     |       1 |        3 |    3699.000 |       1 |          0 |
|       15 | Z220SFF F4F06PA工作站                                                  |       3 |        4 |    4288.000 |       1 |          0 |
|       16 | PowerEdge T110 II服务器                                                |       3 |        5 |    5388.000 |       1 |          0 |
|       17 | Mac Pro MD878CH/A 专业级台式电脑                                       |       3 |        8 |   28888.000 |       1 |          0 |
|       18 |  HMZ-T3W 头戴显示设备                                                  |       6 |        6 |    6999.000 |       1 |          0 |
|       19 | 商务双肩背包                                                           |       6 |        6 |      99.000 |       1 |          0 |
|       20 | X3250 M4机架式服务器 2583i14                                           |       3 |        1 |    6888.000 |       1 |          0 |
|       23 |  LaserJet Pro P1606dn 黑白激光打印机                                   |      12 |        4 |    1849.000 |       1 |          0 |
+----------+------------------------------------------------------------------------+---------+----------+-------------+---------+------------+
*/
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,125评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,293评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,054评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,077评论 1赞 291
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,096评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,062评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,988评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,817评论 0赞 273
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,266评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,486评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,646评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,375评论 5赞 342
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,974评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,621评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,796评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,642评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,538评论 2赞 352

推荐阅读更多精彩内容

  • 第五章 子查询与连接
    数据准备 回顾 记录操作：写操作：INSERT,UPDATE,DELETE读取操作：SELECT 这章主要学习：子...
    齐天大圣李圣杰阅读 1,059评论 0赞 4
  • （六）子查询与连接
    1、创建练习使用的数据表 goods_id —— 商品编号goods_name —— 商品名称goods_cate...
    黒猫阅读 1,018评论 0赞 4
  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,809评论 5赞 116
  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,722评论 0赞 44
  • 2019.2.4
    一江春水绕城走 半城山色舞霓虹 觥筹交错 年近 同学正酣筹 山城火锅 袖卷围兜 辣椒 刚刚红 酒过三巡空瓶摔 我辈...
    9b72a20f04d6阅读 234评论 0赞 0