MySQL连接

发表于

联合查询的效率比较高

连接

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

1
2
3
4
table_reference A
{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr

连接类型

内连接

在MySQL中，JOIN, CROSS JOIN 和 INNER JOIN 是等价的。
显示左表及右表符合连接条件的记录

1
2
3
SELECT goods_id,goods_name,cate_name FROM tdb_goods 
INNER JOIN tdb_goods_cate 
ON tdb_goods.cate_id = tdb_goods_cate.cate_id;

外连接

左外连接：LEFT [OUTER] JOIN：显示左表的全部记录及右表符合连接条件的记录。
右外连接：RIGHT [OUTER] JOIN：显示右表的全部记录及左表符合连接条件的记录。

如果使用LEFT JOIN，左表中存在一条记录A，在右表中没有找到相应的记录，则在返回结果用会出现一条只有记录A中的相应字段内容，其他字段都为NULL在记录(RIGHT JOIN类似)

1
2
3
4
5
6
7
8
9
10
11
12
13
//查询所有商品的详细信息(通过左外连接实现)   
SELECT goods_id,goods_name,cate_name,brand_name,goods_price 
FROM tdb_goods AS g
LEFT JOIN tdb_goods_cates AS c 
ON g.cate_id = c.cate_id;
//LEFT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;
//查询所有商品的详细信息(通过右外连接实现)   
SELECT goods_id,goods_name,cate_name,brand_name,goods_price 
FROM tdb_goods AS g   
RIGHT JOIN tdb_goods_cates AS c 
ON g.cate_id = c.cate_id;
//RIGHT JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

外链接 A LEFT JOIN B join_condition
数据表B的结果集依赖数据表A。
数据表A的结果集根据左连接条件依赖于所有数据表（B表除外）。
左外连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。
如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

全连接

显示左表、右表两边中的所有行，即把左联结果表 + 右联结果表组合在一起，然后过滤掉重复的。

多表的连接跟两张表的连接一样
表的连接实质就是外键的逆向约束

1
2
3
4
5
6
SELECT goods_id,goods_name,b.cate_name,c.brand_name,goods_price
FROM products AS a 
INNER JOIN products_cate AS b 
ON a.goods_cate = b.cate_id
INNER JOIN products_brand AS c 
ON a.brand_name = c.brand_id;

数据表参照

1
2
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关键字来进行结果集记录的过滤

自身连接 – 无限分类

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

对于常见的分类，比如商城中的书籍>>文学>>小说，并不是使用多个表，而是使用一个表来进行联合查询

在同一张表中既有父类，又有子类。
通过对同一张数据表的自身连接来进行查询，需要对表标识别名。
做自身连接的话一定要起别名，以区分是子表还是父表

表的结构是这样的，使用parent_id表示父类目的id

1
2
3
4
5
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
);

查找所有分类及其父类

1
2
3
4
SELECT s.type_id ,s.type_name,p.type_name 
FROM tdb_goods_types s 
LEFT JOIN tdb_goods_types p 
ON s.parent_id=p.type_id;

查找所有分类及其子类

1
2
3
4
SELECT p.type_id ,p.type_name,s.type_name 
FROM tdb_goods_types p 
LEFT JOIN tdb_goods_types s 
ON p.type_id=s.parent_id;

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

1
2
3
4
5
6
SELECT p.type_id ,p.type_name,COUNT(s.type_name) 
FROM tdb_goods_types p 
LEFT JOIN tdb_goods_types s 
ON p.type_id=s.parent_id
GROUP BY p.type_name 
ORDER BY p.type_id;

补充：