2026-08-29 · MySQL 学习笔记 05

MySQL 关联查询 JOIN

1. 为什么需要多张表

数据不塞一张表里，而是拆开：商品归商品、会员归会员、订单归订单，用关联字段串起来。

-- 会员表
CREATE TABLE member (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(20) NOT NULL COMMENT '姓名',
  phone VARCHAR(11) UNIQUE COMMENT '手机号',
  level TINYINT NOT NULL DEFAULT 1 COMMENT '等级 1普通 2银卡 3金卡',
  balance DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '余额',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT '会员表';

-- 订单表：member_id 指向会员表的 id
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  order_no VARCHAR(20) NOT NULL COMMENT '订单号',
  member_id INT COMMENT '会员ID（关联 member.id）',
  amount DECIMAL(10,2) NOT NULL COMMENT '金额',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已退款',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT '订单表';

订单表里只存 member_id（数字），不存会员名字——名字在会员表里。这样会员改名，所有订单自动跟着变，不会出现你这边叫"张三"、那边叫"张四"。

2. 内连接 INNER JOIN（两表都有才出来）

SELECT o.order_no, m.name, o.amount, o.status
FROM orders o
INNER JOIN member m ON o.member_id = m.id;

逐段解释：

• orders o：给表起别名，后面写 o.xxx 少打字
• INNER JOIN member m：连接会员表，别名 m
• ON o.member_id = m.id：关联条件——订单的会员 id 等于会员表的 id，靠这句把两行拼起来

结果只有"能对上会员"的订单。订单表里有一条 member_id 为空的（散客买单），内连接把它丢了。

3. 左连接 LEFT JOIN（左表全留，右表没有就 NULL）

SELECT o.order_no, m.name, o.amount
FROM orders o
LEFT JOIN member m ON o.member_id = m.id;

• orders 是左表，左表所有行都保留；
• 右表（member）对不上的地方，字段显示 NULL（比如散客订单的 name 是 NULL）；
• 查"谁没买过"就靠它：WHERE m.id IS NULL。

工作里 LEFT JOIN 比 INNER JOIN 用得多——通常想保留左表的全部行。

别名不是必须，但表名长、又要多表时几乎必用。全写也行：

SELECT orders.order_no, member.name FROM orders
INNER JOIN member ON orders.member_id = member.id;

4. 三表连接：一对多再连

场景：订单要显示"哪个会员买的、买的什么商品"→ 订单明细表把订单和商品串起来：

-- 订单明细表：一个订单（orders）→ 多行明细（order_item），每个明细指向一个商品
CREATE TABLE order_item (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  order_id INT COMMENT '订单ID（关联 orders.id）',
  goods_id INT COMMENT '商品ID（关联 goods.id）',
  quantity INT NOT NULL DEFAULT 1 COMMENT '数量'
) COMMENT '订单明细表';

三表 JOIN，条件平移：

SELECT o.order_no, g.name AS 商品名, i.quantity
FROM orders o
INNER JOIN order_item i ON i.order_id = o.id
INNER JOIN goods g ON g.id = i.goods_id;

规律：每一步都拿"上一张表的主键"对上"下一张表的外键"，链条一直接下去。

5. JOIN + 聚合：经典统计

每个会员的订单数和总消费额：

SELECT m.name, COUNT(o.id) AS 订单数, SUM(o.amount) AS 总消费
FROM member m
LEFT JOIN orders o ON o.member_id = m.id
GROUP BY m.id, m.name
ORDER BY 总消费 DESC;

• 用 LEFT JOIN：没下过单的会员（右侧全 NULL）也要显示，订单数 0；
• GROUP BY m.id, m.name：按会员分组统计配对出来的订单；
• COUNT(o.id) 数订单行数，NULL 不计。

6. 常见坑

坑	后果	解法
忘了写 ON 关联条件	笛卡尔积：每行和另一表每行相乘，数据爆炸	JOIN 写完立刻检查 ON
不知道用 INNER 还是 LEFT	该有的行莫名消失	默认 LEFT JOIN，要"两边都必须有"才 INNER
JOIN 后结果变多	一对多时订单行重复	先 SELECT COUNT(*) 看行数对不对，再对明细；去重用 DISTINCT

7. 看别人库的关联关系

拿到一个不认识的库，可以看表结构注释：

SHOW CREATE TABLE orders;   -- 看外键/关联字段的注释