MySQL连接

连接

• MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
• 语法结构：

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

数据表参照

• table_reference
• tbl_name [[AS] alias] | table_subquery [AS] alias
• 数据表可以使用tbl_name AS alias_name或tbl_name alias_name赋予别名，以便于区分。
• table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

连接类型

• INNER JOIN，内连接
• LEFT [OUTER] JOIN，左外连接
• RIGHT [OUTER] JOIN，右外连接

在MySQL中，JOIN，INNER JOIN和CROSS JOIN是等价的，一般使用INNER JOIN。

连接条件

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

内连接（INNER JOIN）

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

在三种连接类型中，内连接（INNER JOIN）相对而言用的比较多一些。

左外连接（LEFT [OUTER] JOIN）

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

右外连接（RIGHT [OUTER] JOIN）

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

多表连接

示例：

其实，表的连接也可以说成是外键的逆向操作，外键把数据分开来存储，而连接将多张表联系在了一起。

关于连接的几点说明

外连接

• 例：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将在找到符合连接条件的记录后停止搜索更多的行。

无限级分类表设计

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); 

设计好的无限级分类表如下图所示：

自身连接

同一个数据表对其自身进行连接。把原表想象成两个表，s（子表）和 p（父表），然后两个表进行连接：

• 查找所有分类及其父类：

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;

• 查找所有分类及其子类：

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;

• 查找所有分类及其子类的数目：

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 tbl_name[.*] [,tbl_name[.*]]...FROM table_references[WHERE where_condition];

示例：

• 查找重复记录：

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 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;