Home >Database >Mysql Tutorial >MySQL学习16：多表连接
MySQL学习16：多表连接
- WBOYOriginal
- 2016-06-07 14:49:401089browse
一连接概述 (1)连接 MySQL数据库在SELECT语句，多表更新以及多表删除中都支持JOIN操作。多表连接的语法结构为： table_reference {[INNER | CROSS] JOIN} | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON condtional_expr; (2)数据表参照 table_reference
一连接概述
(1)连接
MySQL数据库在SELECT语句，多表更新以及多表删除中都支持JOIN操作。多表连接的语法结构为：
table_reference {[INNER | CROSS] JOIN} | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON
condtional_expr;
(2)数据表参照
table_reference table_name [[AS] alias] | table_subquery [AS] alias
数据表可以使用table_name AS alias_name或table_name alias_name赋予别名。table_subquery可以作为子查
询使用在FROM子句中，这样的子查询必须为其赋予其别名。
我们在两张数据表中的可能会有相同名称的字段，为了区分各个表中的字段我们给其数据表名称起了别名，用别
名加以区分。
(3)连接类型
INNER JOIN，内连接；在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等价的。
LEFT [OUTER] JOIN，左外连接。
RIGHT [OUTER] JOIN，右外连接。(4)连接条件
使用ON关键字来设定连接条件，也可以使用WHERE来代替。
通常使用ON关键字来设定连接条件，使用WHERE关键字进行结果集记录的过滤。
二连接方式
(1)内连接(INNER JOIN)
显示左表及右表符合连接条件的记录：
实例：
使用内连接将数据表tdb_goods和数据表tdb_goods_cates两个表连接起来进行联合查询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;
我们看到查询的结果只是查找到22条记录，我们新添加的第23条记录并没有被查询到，因为不符合查询的连接的
条件。
(2)左外连接(LEFT [OUTER] JOIN)
显示左表的全部记录及右表符合连接条件的记录
实例：
显示tdb_goods数据表中全部的记录以及tdb_goods_cates数据表中符合条件的记录
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;
(3)右外连接(RIGHT [OUTER] JOIN)
显示左表的全部记录及右表符合连接条件的记录
实例：
显示tdb_goods_cates数据表中的所有记录以及tdb_goods数据表中符合条件的记录
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;
三多表连接
(1)多表连接示例
我们在这里使用三张数据表的内连接作为说明：
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记录。
(2)关于连接的几点说明
外连接：A LEFT JOIN B join_condition1)数据表B的结果集依赖数据表A
2)数据报A的结果集根据左连接条件依赖所有数据表(B表除外)
3)左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)4)如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为
空的额外的B行。
也就是下面显示的结果：
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;
这个结果我们在上面的例子中已经知道。
如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL
时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行。
四无限级分类表设计
(1)设计步骤：
1)首先创建数据表
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
);
2)插入记录
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);
3)查看数据表中的记录
SELECT * FROM tdb_goods_types;
上面的显示结果的最后一列表示的意思是：0代表顶级分类，没有父亲节点；1到10代表子类。
(2)自身连接
自身连接指的是同一个数据表对其自身进行连接。实例：
1)查看所有分类及其父类
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;
2)查找所有分类及其子类
SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS
s ON s.parent_id = p.type_id;
3)查找所有分类及其子类的数目
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;
五多表删除
多表删除的语法结构为：
DELETE tabke_name[.*] [,table_name[.*]] ... FROM table_references [WHERE where_condition];
SELECT * FROM tdb_goods\G;
我们查找到有重复的记录。那么下面所做的事情就是将重复的记录删除，保留id值较小的记录。
(1)查找重复记录
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >=
2;
(2)删除重复记录
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;
(3)再次查看数据表中的所有记录是否存在重复记录
SELECT * FROM tdb_goods\G;
SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >=
2;
从上面的结果可以看出数据表中已经没有重复的记录，说明我们成功删除了重复的记录，并且保留了goods_id值
较小的记录。