连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
-
连接类型
-
INNER JOIN 内连接
在MySQL中,JOIN, CROSS JOIN 和 INNER JOIN是等价的。
显示左表及右表符合连接条件的记录.
-
-
LEFT [ OUTER ] JOIN 左外连接
显示左表的全部记录及右表符合连接条件的记录.
根据左表用连接条件去连接右表
-
RIGHT [ OUTER ] JOIN 右外连接
显示右表的全部记录及左表符合连接条件的记录.
根据右边去连接左表
// 添加数据
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');
mysql> INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
mysql> INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
// 内连接
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. row ***************************
goods_id: 22
goods_name: HMZ-T3W 头戴显示设备
cate_name: 笔记本配件
*************************** 23. row ***************************
goods_id: 23
goods_name: 商务双肩背包
cate_name: 笔记本配件
23 rows in set (0.00 sec)
// 左连接
mysql> 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\G;
// ...
*************************** 23. row ***************************
goods_id: 23
goods_name: 商务双肩背包
cate_name: 笔记本配件
*************************** 24. row ***************************
goods_id: 24
goods_name: LaserJet Pro P1606dn 黑白激光打印机
cate_name: NULL
24 rows in set (0.00 sec)
// 右连接
mysql> 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\G;
// ...
*************************** 25. row ***************************
goods_id: NULL
goods_name: NULL
cate_name: 交换机
*************************** 26. row ***************************
goods_id: NULL
goods_name: NULL
cate_name: 网卡
26 rows in set (0.00 sec)
- 连接语法结构
table_reference
{ [ INNER | CROSS ] JOIN | { LEFT | RIGHT } [ OUTER ] JOIN }
table_reference
ON conditional_expr
- 数据表参照
table_reference
tbl_name [ [ AS ] alias ] | table_subquery [ AS ] alias
数据表可以使用 tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
table_subquery可以作为子查询使用在FROM子句中,这样的子查询必须为其赋予别名。
-
连接条件
使用ON关键字来设定连接条件,也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件,使用WHERE关键字进行结果集记录的过滤。 -
多表连接
从多张表中恢复数据,便于理解查询的结果。
mysql> SELECT goods_id, goods_name, cate_name, brand_name, goods_price 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\G;
*************************** 1. row ***************************
goods_id: 1
goods_name: R510VC 15.6英寸笔记本
cate_name: 笔记本
brand_name: 华硕
goods_price: 3399.000
*************************** 2. row ***************************
goods_id: 2
goods_name: Y400N 14.0英寸笔记本电脑
cate_name: 笔记本
brand_name: 联想
goods_price: 4899.000
*************************** 3. row ***************************
goods_id: 3
goods_name: G150TH 15.6英寸游戏本
cate_name: 游戏本
brand_name: 雷神
goods_price: 8499.000
// ......
- 连接的几点说明
*A LEFT JOIN B join_condition
- 数据表B的结果集依赖数据表A。(数据表A中有的记录才在数据表B中显示出来)
- 数据表A的结果集根据左连接条件依赖所有数据表(B表除外)。
- 左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)。
- 如果数据表A的某条记录符合WHERE条件,但是在数据表B不存在符合连接条件的记录,将生成一个所有列为空的额外的B行。
- 如果使用内连接查找的记录在连接数据表中不存在,并且在WHERE子句中尝试以下操作:col_namd IS NULL时,如果col_name被定义为NOT NULL,MySQL将在找到符合连接条件的记录后停止搜索更多的行。
无限分类数据表的设计
- 创建数据表
mysql> 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
-> );
Query OK, 0 rows affected (0.02 sec)
- 插入数据
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> SHOW COLUMNS FROM tdb_goods_types;
+-----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+----------------+
| type_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| type_name | varchar(20) | NO | | NULL | |
| parent_id | smallint(5) unsigned | NO | | 0 | |
+-----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
- 查看数据
家用电器、电脑办公属于顶级分类为0,大家电、生活电器属于家用电器,其分类为家用电器的type_id,即1,平板电视、空调属于大家电,其分类为大家电的type_id,即为3,一依次类推。
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)
自身连接(自连接)
同一个数据表对其自身进行连接。
自内连接
// 查询每类对应的父类,没有的不显示
mysql> SELECT s.type_id s_type_id, s.type_name s_type_name, p.type_name p_type_name, p.parent_id p_parent_id FROM tdb_goods_types AS s
-> INNER JOIN tdb_goods_types p
-> ON s.parent_id = p.type_id;
+-----------+-------------+-------------+-------------+
| s_type_id | s_type_name | p_type_name | p_parent_id |
+-----------+-------------+-------------+-------------+
| 3 | 大家电 | 家用电器 | 0 |
| 4 | 生活电器 | 家用电器 | 0 |
| 5 | 平板电视 | 大家电 | 1 |
| 6 | 空调 | 大家电 | 1 |
| 7 | 电风扇 | 生活电器 | 1 |
| 8 | 饮水机 | 生活电器 | 1 |
| 9 | 电脑整机 | 电脑、办公 | 0 |
| 10 | 电脑配件 | 电脑、办公 | 0 |
| 11 | 笔记本 | 电脑整机 | 2 |
| 12 | 超级本 | 电脑整机 | 2 |
| 13 | 游戏本 | 电脑整机 | 2 |
| 14 | CPU | 电脑配件 | 2 |
| 15 | 主机 | 电脑配件 | 2 |
+-----------+-------------+-------------+-------------+
13 rows in set (0.00 sec)
tdb_goods_types分别作为左右表,以左表为参照,根据连接条件在右表中得到连接部分的内容,最终查询结果为左表和连接部分的公共部分。
自左连接
// 查询每类对应的父类,没有的显示为NULL
mysql> SELECT s.type_id s_type_id, s.type_name s_type_name, p.type_name p_type_name, p.parent_id p_parent_id FROM tdb_goods_types AS s
-> LEFT JOIN tdb_goods_types p
-> ON s.parent_id = p.type_id;
+-----------+-------------+-------------+-------------+
| s_type_id | s_type_name | p_type_name | p_parent_id |
+-----------+-------------+-------------+-------------+
| 1 | 家用电器 | NULL | NULL |
| 2 | 电脑、办公 | NULL | NULL |
| 3 | 大家电 | 家用电器 | 0 |
| 4 | 生活电器 | 家用电器 | 0 |
| 5 | 平板电视 | 大家电 | 1 |
| 6 | 空调 | 大家电 | 1 |
| 7 | 电风扇 | 生活电器 | 1 |
| 8 | 饮水机 | 生活电器 | 1 |
| 9 | 电脑整机 | 电脑、办公 | 0 |
| 10 | 电脑配件 | 电脑、办公 | 0 |
| 11 | 笔记本 | 电脑整机 | 2 |
| 12 | 超级本 | 电脑整机 | 2 |
| 13 | 游戏本 | 电脑整机 | 2 |
| 14 | CPU | 电脑配件 | 2 |
| 15 | 主机 | 电脑配件 | 2 |
+-----------+-------------+-------------+-------------+
15 rows in set (0.00 sec)
tdb_goods_types分别作为左右表,以左表为参照,根据连接条件在右表中得到连接部分的内容,最终查询结果为左表和连接部分。
自右连接
// 查询每类对应的子类
mysql> SELECT p.type_id p_type_id, p.type_name p_type_name, s.type_name s_type_name, s.type_id s_type_id
-> FROM tdb_goods_types AS s
-> RIGHT JOIN tdb_goods_types p
-> ON s.parent_id = p.type_id;
+-----------+-------------+-------------+-----------+
| p_type_id | p_type_name | s_type_name | s_type_id |
+-----------+-------------+-------------+-----------+
| 1 | 家用电器 | 大家电 | 3 |
| 1 | 家用电器 | 生活电器 | 4 |
| 2 | 电脑、办公 | 电脑整机 | 9 |
| 2 | 电脑、办公 | 电脑配件 | 10 |
| 3 | 大家电 | 平板电视 | 5 |
| 3 | 大家电 | 空调 | 6 |
| 4 | 生活电器 | 电风扇 | 7 |
| 4 | 生活电器 | 饮水机 | 8 |
| 5 | 平板电视 | NULL | NULL |
| 6 | 空调 | NULL | NULL |
| 7 | 电风扇 | NULL | NULL |
| 8 | 饮水机 | NULL | NULL |
| 9 | 电脑整机 | 笔记本 | 11 |
| 9 | 电脑整机 | 超级本 | 12 |
| 9 | 电脑整机 | 游戏本 | 13 |
| 10 | 电脑配件 | CPU | 14 |
| 10 | 电脑配件 | 主机 | 15 |
| 11 | 笔记本 | NULL | NULL |
| 12 | 超级本 | NULL | NULL |
| 13 | 游戏本 | NULL | NULL |
| 14 | CPU | NULL | NULL |
| 15 | 主机 | NULL | NULL |
+-----------+-------------+-------------+-----------+
22 rows in set (0.00 sec)
tdb_goods_types分别作为左右表,以右表为参照,根据连接条件在左表中得到连接部分的内容,最终查询结果为右表和连接部分的公共部分。
自右连接是可以改写成自左连接的,一般不使用自右连接,上面的例子用于学习右连接。改成自左连接如下:
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;
可以使用分组函数对结果分组:
// 查询每类对应的子类的数目
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 s
-> ON s.parent_id = p.type_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)
+++
多表删除
语法
DELETE tbl_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.00 sec)
- 删除 tdb_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.01 sec)