MySQL数据关联之外键：表关系：联表查询实战详解

前言

在实际项目开发中，数据库绝不会只用单张表存储所有业务数据，用户、订单、商品、分类等数据都需要多表关联存储。

一、数据关联的必要性

单表存储模式存在明显弊端，不仅数据冗余严重，大量重复字段大幅耗费数据库存储空间，还会提升日常数据修改与维护难度，修改一处信息便需多处同步调整，极易引发数据错乱不一致的问题，同时该设计违背数据库三大范式原则，严重制约项目后续的功能拓展与版本迭代。

各类业务数据本身具备清晰的从属、匹配与对应逻辑，依靠单一数据表无法贴合实际业务架构，唯有采用多表拆分设计，借助表与表之间的关联查询，才能顺畅实现各类业务数据的联动调用，适配真实业务运转需求。

核心思想：拆分不同业务数据到不同数据表，通过关联字段建立数据联系，实现数据统一管理与联合查询。

二、MySQL 三大数据表关联关系

1.一对一 关系

A 表一条数据唯一对应 B 表一条数据，互相唯一匹配。

常见场景：用户基础信息表 & 用户隐私详情表、员工表 & 员工身份证表

设计思路：任意一方添加对方主键作为关联字段，可设置唯一约束保证一对一。

2. 一对多关系（开发最常用）

主表一条数据，对应从表多条数据，反向多条数据对应一条主表数据。

常见场景：

部门表（一）→ 员工表（多）

商品分类表（一）→ 商品表（多）

用户表（一）→ 订单表（多）

设计思路：多方数据表添加一方主键作为外键关联字段。

3. 多对多关系

A 表多条数据对应 B 表多条数据，双向均可多匹配。

常见场景：学生 & 课程、角色 & 权限、商品 & 购物车

设计思路：新增中间关联表，存储两张主表主键，拆分多对多为两个一对多关系

三、关联操作（JOIN）详解

我们以例子出发：

这是一张用户表

这是一张订单表

1. INNER JOIN（内连接）

作用：只返回两张表中匹配成功的数据（取交集）。

SELECT u.*, o.*FROM users uINNER JOIN orders o ON u.uid = o.user_id;

查询结果：

只显示有订单的用户 + 对应用户存在的订单（张三、李四）。

赵六（无订单）、订单 104（无对应用户）都不显示。

2. LEFT JOIN（左连接 / 左外连接）

作左表数据全部显示，右表只显示匹配的数据，不匹配显示 NULL。

SELECT u.*, o.*FROM users uLEFT JOIN orders o ON u.uid = o.user_id;

查询结果：

所有用户（张三、李四、王五、赵六）全部显示

有订单的显示订单，没订单的订单字段为 NULL

常用场景：查询所有用户，以及他们的订单（包括没下单的用户）。

3. RIGHT JOIN（右连接 / 右外连接）

右表数据全部显示，左表只显示匹配的数据，不匹配显示 NULL。

SELECT u.*, o.*FROM users uRIGHT JOIN orders o ON u.uid = o.user_id;

查询结果：

所有订单全部显示

订单 104（user_id=5）没有对应用户，用户字段为 NULL

4. FULL JOIN（全外连接）

两张表所有数据都显示，匹配不上的字段填 NULL（取并集）。

MySQL 不直接支持 FULL JOIN，用 UNION 实现。

SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.uid = o.user_idUNIONSELECT u.*, o.* FROM users u RIGHT JOIN orders o ON u.uid = o.user_id;

结果：所有用户 + 所有订单，缺失数据填 NULL。

5. CROSS JOIN（交叉连接）

作用：笛卡尔积 —— 左表每一行都和右表每一行拼接。

例如：

A：(a,b,c)

B：(1,2,3)

A与B作笛卡尔积—> a1,a2,a3,b1,b2,b3,c1,c2,c3

-- 无条件：4个用户 × 4个订单 = 16条数据SELECT * FROM users CROSS JOIN orders;-- 带条件等价于 INNER JOINSELECT * FROM users CROSS JOIN orders ON users.uid = orders.user_id;

四、JOIN 核心语法规则

SELECT 字段FROM 表1[JOIN类型] JOIN 表2 ON 表1.关联字段 = 表2.关联字段  #必须写关联条件WHERE 过滤条件;

• 其中的关键区别：ON 和 WHERE
• ON：关联时的匹配条件（JOIN 必须搭配 ON）
• WHERE：关联后对结果集过滤
• 外连接中：ON 不过滤主表，WHERE 会过滤所有数据

五、实例解析

现有两张表

第一张学生表Student

第二张成绩表SC

要求查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数.。

完整代码如下：

SELECT stu.*,a.score 01课程分数,b.score 02课程分数FROM Student stuJOIN SC a ON stu.SId=a.SId AND a.CId='01'LEFT JOIN SC b ON stu.SId=b.SId AND b.CId='02'WHERE a.score > IFNULL(b.score,0)

Student stu   -- 学生表，别名 stuSC a          -- 成绩表，别名 a（专门放 01 课程）SC b          -- 成绩表，别名 b（专门放 02 课程）

一张成绩表 SC 用了两次，分别取不同课程，这叫自连接。

FROM Student stuJOIN SC a ON stu.SId=a.SId AND a.CId='01'

只保留有 01 课程成绩的学生，没有 01 成绩的学生直接被过滤掉。

为什么？

其中INNER JOIN = 交集

必须两边表都能匹配上，才会出现在结果里。

LEFT JOIN SC b ON stu.SId = b.SId AND b.CId = '02'

左边学生（已经有 01 成绩），尝试匹配 02 课程成绩，匹配不到也保留学生，只是 02 分数显示 NULL。

其中 LEFT JOIN = 以左表为准

左表有，右表没有 → 右表字段填 NULL

不会删除学生记录。

WHERE a.score > IFNULL(b.score, 0)

条件判断01 课程分数 > 02 课程分数

总结

MySQL 数据关联是后端开发必备核心知识点，从表结构设计到联表查询贯穿整个项目开发流程。熟练掌握一对多、多对多设计思路，灵活运用内外连接查询，就能轻松搞定商城、后台管理、社交系统等绝大多数业务的数据关联需求。