mysql表的链接

在 MySQL 中，表连接（JOIN）是用于从多个表中根据关联字段提取数据的核心操作。根据业务需求不同，主要分为内连接、外连接、交叉连接、自连接四类，以下是详细说明和示例：

准备示例表

先创建两个测试表，方便后续演示：

sql

-- 商品分类表 CREATE TABLE category ( cid INT PRIMARY KEY, cname VARCHAR(50) NOT NULL -- 分类名称 ); -- 商品表 CREATE TABLE product ( pid INT PRIMARY KEY, pname VARCHAR(50) NOT NULL, price DECIMAL(10,2), cid INT, -- 关联分类表的cid FOREIGN KEY (cid) REFERENCES category(cid) ); -- 插入测试数据 INSERT INTO category VALUES (1, '电子产品'), (2, '生活用品'), (3, '食品'); INSERT INTO product VALUES (1, '手机', 2999.00, 1), (2, '牙刷', 9.90, 2), (3, '面包', 5.80, 3), (4, '耳机', 199.00, 1), (5, '毛巾', 15.90, 2), (6, '薯片', 8.50, NULL); -- 无分类的商品

一、内连接（INNER JOIN）

核心逻辑

只返回两个表中关联字段匹配成功的记录（交集），是最常用的连接方式。

语法

sql

-- 标准写法 SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段; -- 简化写法（等价） SELECT 字段列表 FROM 表1, 表2 WHERE 表1.关联字段 = 表2.关联字段;

示例

查询所有有分类的商品及其分类名称：

sql

SELECT p.pid, p.pname, p.price, c.cname FROM product p INNER JOIN category c ON p.cid = c.cid;

结果

二、外连接（OUTER JOIN）

返回一个表的所有记录 + 另一个表匹配的记录，未匹配的字段显示NULL，分为左外连接、右外连接、全外连接（MySQL 不直接支持全外连接，需变通实现）。

1. 左外连接（LEFT JOIN / LEFT OUTER JOIN）

核心逻辑

返回左表的所有记录，右表匹配的记录显示，未匹配则右表字段为NULL。

语法

sql

SELECT 字段列表 FROM 左表 LEFT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例

查询所有商品（包括无分类的）及其分类名称：

sql

SELECT p.pid, p.pname, p.price, c.cname FROM product p LEFT JOIN category c ON p.cid = c.cid;

结果

2. 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

核心逻辑

返回右表的所有记录，左表匹配的记录显示，未匹配则左表字段为NULL。

语法

sql

SELECT 字段列表 FROM 左表 RIGHT JOIN 右表 ON 左表.关联字段 = 右表.关联字段;

示例

查询所有分类（包括无商品的分类）及其商品：

sql

-- 先插入一个无商品的分类 INSERT INTO category VALUES (4, '玩具'); -- 右连接查询 SELECT p.pid, p.pname, c.cname FROM product p RIGHT JOIN category c ON p.cid = c.cid;

结果

3. 全外连接（FULL JOIN）

MySQL 不直接支持FULL JOIN，需通过LEFT JOIN + UNION + RIGHT JOIN实现，返回两个表的所有记录，未匹配的字段为NULL。

示例

sql

SELECT p.pid, p.pname, c.cname FROM product p LEFT JOIN category c ON p.cid = c.cid UNION SELECT p.pid, p.pname, c.cname FROM product p RIGHT JOIN category c ON p.cid = c.cid;

三、交叉连接（CROSS JOIN）

核心逻辑

返回两个表的笛卡尔积（所有组合），无关联条件时慎用（数据量 = 表 1 行数 × 表 2 行数）。

语法

sql

-- 写法1 SELECT 字段列表 FROM 表1 CROSS JOIN 表2; -- 写法2（等价） SELECT 字段列表 FROM 表1, 表2;

示例

sql

-- 查询分类和商品的所有组合（仅演示，实际少用） SELECT c.cname, p.pname FROM category c CROSS JOIN product p LIMIT 5; -- 限制结果数

四、自连接（SELF JOIN）

核心逻辑

将表自身作为另一个表连接，需给表起别名，用于查询表内层级关系（如部门上下级、分类父子级）。

示例

先修改分类表，增加父分类字段：

sql

-- 新增父分类字段 ALTER TABLE category ADD COLUMN parent_cid INT; -- 更新数据：电子产品/生活用品是一级分类，手机/耳机属于电子产品子分类 UPDATE category SET parent_cid = NULL WHERE cid IN (1,2,3,4); INSERT INTO category VALUES (5, '手机配件', NULL, 1), (6, '快充头', NULL, 5); -- 自连接查询子分类和父分类名称 SELECT c1.cname AS 子分类, c2.cname AS 父分类 FROM category c1 LEFT JOIN category c2 ON c1.parent_cid = c2.cid;

五、连接的注意事项

1. 关联字段类型一致：关联的字段需为相同 / 兼容类型（如 INT 和 INT，VARCHAR 和 VARCHAR）。
2. 使用别名简化语句：多表连接时给表起别名（如product p），避免字段名冲突。
3. 索引优化：关联字段（如product.cid）建议建立索引，提升连接查询效率。
4. 避免笛卡尔积：内连接 / 外连接必须加ON条件，否则等价于交叉连接。
5. NULL 值处理：外连接中未匹配的字段为NULL，可通过IFNULL()替换：

   sql

   SELECT p.pname, IFNULL(c.cname, '未分类') AS cname FROM product p LEFT JOIN category c ON p.cid = c.cid;

以上是 MySQL 表连接的核心用法，实际开发中需根据业务场景选择合适的连接方式，优先使用内连接和左 / 右外连接，避免不必要的笛卡尔积和全外连接。