【零基础学MySQL】第十章：表与表之间的关系及多表连接查询

原创 于 2025-11-02 19:30:00 发布 · 1.7k 阅读 · 39 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
收录于
零基础学MySQL

当前文章被收录于：

零基础学MySQL
16 篇文章 0 人学习
订阅专栏 查看详情

在 MySQL 数据库应用中，单一表往往无法满足复杂业务场景的数据存储需求。例如，一个电商平台需要存储用户信息、商品信息、订单信息等，这些数据分散在不同的表中，而表与表之间存在着紧密的关联。理解表与表之间的关系，并掌握多表连接查询技巧，是高效操作 MySQL 数据库的核心能力。本文将从表的关系入手，详细讲解多表连接查询的实现方式，并结合丰富示例帮助读者彻底掌握这一知识点。

一、MySQL 表与表之间的关系

在数据库设计中，表与表之间的关系主要分为三种：一对一关系、一对多关系和多对多关系。合理设计表关系是保证数据完整性、减少冗余的关键。

1.1 一对一关系（One-to-One）

概念

一对一关系指两个表中，一个表的一条记录只能对应另一个表的一条记录，反之亦然。这种关系通常用于拆分表结构，将常用字段和不常用字段分开存储，提高查询效率。

应用场景

例如，“用户表（users）” 和 “用户详细信息表（user_profiles）”。用户的基本信息（如用户名、手机号）常用，存储在users表中；而用户的详细信息（如家庭地址、兴趣爱好）不常用，存储在user_profiles表中，两者通过唯一标识关联。

表结构设计与示例
  • 用户表（users）：存储用户基本信息，id为主键。
CREATE TABLE users (
   id INT PRIMARY KEY AUTO_INCREMENT,
   username VARCHAR(50) NOT NULL UNIQUE,
   phone VARCHAR(20) NOT NULL UNIQUE,
   create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
  • 用户详细信息表（user_profiles）：存储用户详细信息，user_id为外键，关联users表的id，且user_id设置为唯一键（保证一对一关系）。
CREATE TABLE user_profiles (
   id INT PRIMARY KEY AUTO_INCREMENT,
   user_id INT NOT NULL UNIQUE, -- 唯一键确保一对一
   address VARCHAR(200),
   hobby VARCHAR(100),
   FOREIGN KEY (user_id) REFERENCES users(id) -- 外键关联
       ON DELETE CASCADE -- 级联删除：删除用户时，同时删除其详细信息
       ON UPDATE CASCADE -- 级联更新：用户id更新时，详细信息的user_id同步更新
);
数据插入示例
-- 插入用户数据
INSERT INTO users (username, phone)
VALUES ('zhangsan', '13800138000'), ('lisi', '13900139000');
-- 插入用户详细信息（需与users表的id对应）
INSERT INTO user_profiles (user_id, address, hobby)
VALUES (1, '北京市海淀区', '篮球'), (2, '上海市浦东新区', '读书');

1.2 一对多关系（One-to-Many）

概念

一对多关系是最常见的表关系，指一个表（“一” 方）的一条记录可以对应另一个表（“多” 方）的多条记录，但 “多” 方的一条记录只能对应 “一” 方的一条记录。

应用场景

例如，“商品分类表（categories）” 和 “商品表（products）”。一个分类下可以有多个商品（“一” 对 “多”），但一个商品只能属于一个分类（“多” 对 “一”）。

表结构设计与示例
  • 商品分类表（categories）：“一” 方，id为主键。
CREATE TABLE categories (
   id INT PRIMARY KEY AUTO_INCREMENT,
   category_name VARCHAR(50) NOT NULL UNIQUE,
   description VARCHAR(200)
);
  • 商品表（products）：“多” 方，通过category_id外键关联 “一” 方的id（无需唯一键，允许一个分类对应多个商品）。
CREATE TABLE products (
   id INT PRIMARY KEY AUTO_INCREMENT,
   product_name VARCHAR(100) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   stock INT DEFAULT 0,
   category_id INT NOT NULL, -- 外键关联分类表
   FOREIGN KEY (category_id) REFERENCES categories(id)
       ON DELETE RESTRICT -- 限制删除：若分类下有商品，禁止删除分类
       ON UPDATE CASCADE
);
数据插入示例
-- 插入分类数据
INSERT INTO categories (category_name, description)
VALUES ('手机', '智能手机及配件'), ('电脑', '笔记本电脑和台式机');
-- 插入商品数据（多个商品对应同一分类）
INSERT INTO products (product_name, price, stock, category_id)
VALUES
('iPhone 15', 5999.00, 100, 1),
('华为Mate 60', 4999.00, 80, 1),
('联想拯救者Y9000P', 7999.00, 50, 2),
('苹果MacBook Pro', 12999.00, 30, 2);

1.3 多对多关系（Many-to-Many）

概念

多对多关系指两个表中，一个表的一条记录可以对应另一个表的多条记录，反之亦然。这种关系无法直接通过外键实现，需要引入中间表（关联表） 来关联两个表。

应用场景

例如，“学生表（students）” 和 “课程表（courses）”。一个学生可以选多门课程，一门课程也可以被多个学生选择，两者通过 “学生选课表（student_courses）” 关联。

表结构设计与示例
  • 学生表（students）：id为主键。
CREATE TABLE students (
   id INT PRIMARY KEY AUTO_INCREMENT,
   student_name VARCHAR(50) NOT NULL,
   student_no VARCHAR(20) NOT NULL UNIQUE, -- 学号唯一
   age INT
);
  • 课程表（courses）：id为主键。
CREATE TABLE courses (
   id INT PRIMARY KEY AUTO_INCREMENT,
   course_name VARCHAR(50) NOT NULL UNIQUE,
   credit INT NOT NULL -- 学分
);
  • 中间表（student_courses）：同时关联students表和courses表，通过复合主键（或唯一键）确保同一学生不重复选同一课程。
CREATE TABLE student_courses (
   student_id INT NOT NULL,
   course_id INT NOT NULL,
   score DECIMAL(5,2) DEFAULT NULL, -- 成绩（可选）
   -- 复合主键：确保学生与课程的组合唯一
   PRIMARY KEY (student_id, course_id),
   -- 外键关联
   FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
   FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);
数据插入示例
-- 插入学生数据
INSERT INTO students (student_name, student_no, age)
VALUES ('王小五', '2023001', 18), ('赵六', '2023002', 19);
-- 插入课程数据
INSERT INTO courses (course_name, credit)
VALUES ('MySQL数据库', 3), ('Java编程', 4), ('Python数据分析', 3);
-- 插入选课数据（多对多关联）
INSERT INTO student_courses (student_id, course_id, score)
VALUES
(1, 1, 85.5), -- 王小五选MySQL，成绩85.5
(1, 2, 92.0), -- 王小五选Java，成绩92.0
(2, 1, 78.0), -- 赵六选MySQL，成绩78.0
(2, 3, 88.5); -- 赵六选Python，成绩88.5

二、MySQL 多表连接查询

当数据分散在多个表中时，需要通过 “连接查询” 将相关表的数据合并查询。MySQL 支持多种连接方式，核心包括内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN） 和全外连接（FULL JOIN，MySQL 间接支持）。

在讲解连接查询前，先明确两个核心概念：

  • 关联条件：通过ON子句指定表之间的关联字段（如users.id`` = user_profiles.user_id），避免产生 “笛卡尔积”（无关联条件时，两表数据会无意义地两两组合，导致数据量爆炸）。

  • 笛卡尔积：若两表分别有m和n条记录，无关联条件的连接会产生m*n条记录，通常是错误的，需通过ON子句过滤。

2.1 内连接（INNER JOIN）

概念

内连接只返回两个表中满足关联条件的记录，即 “交集” 部分。不满足条件的记录会被过滤掉。

语法格式
SELECT 字段列表

FROM 表1

INNER JOIN 表2

ON 表1.关联字段 = 表2.关联字段

[WHERE 其他过滤条件];
示例 1：查询用户及其详细信息（一对一内连接）

需求：查询所有有详细信息的用户的用户名、手机号和地址。

SELECT
   u.username,
   u.phone,
   up.address
FROM
   users u -- 表别名u（简化写法）
INNER JOIN
   user_profiles up -- 表别名up
ON
   u.id = up.user_id; -- 关联条件：用户id与详细信息的user_id相等
执行结果
usernamephoneaddress
zhangsan13800138000北京市海淀区
lisi13900139000上海市浦东新区
示例 2：查询分类及对应的商品（一对多内连接）

需求：查询 “手机” 分类下的所有商品名称和价格。

SELECT
   c.category_name,
   p.product_name,
   p.price
FROM
   categories c
INNER JOIN
   products p
ON
   c.id = p.category_id
WHERE
   c.category_name = '手机'; -- 过滤条件：只查“手机”分类
执行结果
category_nameproduct_nameprice
手机iPhone 155999.00
手机华为 Mate 604999.00

2.2 左外连接（LEFT JOIN / LEFT OUTER JOIN）

概念

左外连接以 “左表” 为基准，返回左表的所有记录，以及右表中满足关联条件的记录；若右表无匹配记录，则右表字段返回NULL。

语法格式
SELECT 字段列表

FROM 表1（左表）

LEFT JOIN 表2（右表）

ON 表1.关联字段 = 表2.关联字段

[WHERE 其他过滤条件];
示例：查询所有用户及其详细信息（含无详细信息的用户）

需求：即使用户没有填写详细信息（user_profiles中无对应记录），也要显示用户的基本信息，地址字段用NULL填充。

先插入一条无详细信息的用户数据：

INSERT INTO users (username, phone)
VALUES ('wangwu', '13700137000'); -- 未插入user_profiles数据

执行左连接查询：

SELECT
   u.username,
   u.phone,
   up.address
FROM
   users u --（左表：所有用户）
LEFT JOIN
   user_profiles up --（右表：用户详细信息）
ON
   u.id = up.user_id;
执行结果
usernamephoneaddress
zhangsan13800138000北京市海淀区
lisi13900139000上海市浦东新区
wangwu13700137000NULL

2.3 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

概念

右外连接与左外连接相反，以 “右表” 为基准，返回右表的所有记录，以及左表中满足关联条件的记录；若左表无匹配记录，则左表字段返回NULL。

语法格式
SELECT 字段列表

FROM 表1（左表）

RIGHT JOIN 表2（右表）

ON 表1.关联字段 = 表2.关联字段

[WHERE 其他过滤条件];
示例：查询所有课程及选该课程的学生（含无学生选的课程）

需求：显示所有课程名称，以及选该课程的学生姓名和成绩；若课程无人选，学生姓名和成绩返回NULL。

先插入一门无人选的课程：

INSERT INTO courses (course_name, credit)
VALUES ('Web前端开发', 3); -- 未插入student_courses数据

执行右连接查询：

SELECT
   c.course_name,
   s.student_name,
   sc.score
FROM
   students s --（左表：学生）
RIGHT JOIN
   student_courses sc --（中间表：选课记录）
ON
   s.id = sc.student_id
RIGHT JOIN
   courses c --（右表：所有课程）
ON
   sc.course_id = c.id;
执行结果
course_namestudent_namescore
MySQL 数据库王小五85.50
MySQL 数据库赵六78.00
Java 编程王小五92.00
Python 数据分析赵六88.50
Web 前端开发NULLNULL

2.4 全外连接（FULL JOIN）

概念

全外连接返回两个表的所有记录，当某一侧无匹配记录时，对应字段返回NULL。但 MySQL不直接支持FULL JOIN关键字，需通过LEFT JOIN + UNION + RIGHT JOIN间接实现。

语法格式（间接实现）
-- 左连接结果 + 右连接中左表无匹配的记录

SELECT 字段列表 FROM 表1 LEFT JOIN 表2 ON 关联条件

UNION

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 关联条件 WHERE 表1.关联字段 IS NULL;
示例：查询所有用户和所有课程的选课情况（含无选课的用户和课程）
-- 左连接：所有用户 + 其选课记录
SELECT
   u.username,
   c.course_name,
   sc.score
FROM
   users u
LEFT JOIN
   student_courses sc ON u.id = sc.student_id
LEFT JOIN
   courses c ON sc.course_id = c.id
UNION
-- 右连接：所有课程中无用户选的记录（补充左连接未覆盖的课程）
SELECT
   u.username,
   c.course_name,
   sc.score
FROM
   users u
RIGHT JOIN
   student_courses sc ON u.id = sc.student_id
RIGHT JOIN
   courses c ON sc.course_id = c.id
WHERE
   u.id IS NULL; -- 过滤出左表（用户）无匹配的记录
执行结果（关键部分）
usernamecourse_namescore
zhangsanNULLNULL
王小五MySQL 数据库85.50
赵六Python 数据分析88.50
NULLWeb 前端开发NULL

2.5 多表连接查询的常见技巧

1. 使用表别名简化 SQL

当连接多个表时，表名过长会导致 SQL 冗余，通过表名 别名（如users u）简化写法，提高可读性。

2. 区分字段名（避免歧义）

若多个表有同名字段（如id），查询时需指定表别名（如u.id、up.id），否则 MySQL 会报错 “Column ‘id’ in field list is ambiguous”（字段名歧义）。

3. 结合聚合函数与 GROUP BY

在多表连接中，常需对结果进行统计分析（如计数、求和、平均值），此时需结合COUNT()、SUM()、AVG()等聚合函数，以及GROUP BY分组。

示例：统计每个商品分类下的商品数量和平均价格。

SELECT
   c.category_name,
   COUNT(p.id) AS product_count, -- 统计商品数量
   AVG(p.price) AS avg_price -- 计算平均价格
FROM
   categories c
INNER JOIN
   products p ON c.id = p.category_id
GROUP BY
   c.category_name; -- 按分类分组

执行结果：

category_nameproduct_countavg_price
手机25499.00
电脑210499.00
4. 使用 DISTINCT 去重

若多表连接后出现重复记录（如一对多关系中，“一” 方记录被重复显示），可通过DISTINCT关键字去重。

示例：查询所有购买过商品的用户（假设存在 “订单表 orders” 和 “订单详情表 order_details”）。

-- 先创建订单相关表（补充示例数据）
CREATE TABLE orders (
   id INT PRIMARY KEY AUTO_INCREMENT,
   user_id INT NOT NULL,
   order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
   FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_details (
   id INT PRIMARY KEY AUTO_INCREMENT,
   order_id INT NOT NULL,
   product_id INT NOT NULL,
   quantity INT NOT NULL, -- 购买数量
   FOREIGN KEY (order_id) REFERENCES orders(id),
   FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入订单数据（张三买了2个商品，李四买了1个商品）
INSERT INTO orders (user_id) VALUES (1), (1), (2);
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (1, 1, 1), (2, 2, 1), (3, 3, 1);

-- 查询所有购买过商品的用户（去重）
SELECT DISTINCT
   u.username
FROM
   users u
INNER JOIN
   orders o ON u.id = o.user_id
INNER JOIN
   order_details od ON o.id = od.order_id;

执行结果：

username
zhangsan
lisi

2.6 多表连接查询的注意事项

1. 避免笛卡尔积

若忘记写ON子句或关联条件错误，会产生笛卡尔积，导致数据量骤增（如 1000 条用户数据和 1000 条商品数据，会产生 100 万条记录），严重影响性能。务必确保关联条件正确且必要。

2. 外连接与 WHERE 条件的顺序

外连接中，ON子句用于过滤关联条件，WHERE子句用于过滤最终结果。若将本应写在ON中的条件写在WHERE中，可能会将外连接转为内连接。

错误示例：查询所有用户及其详细信息，却在WHERE中过滤了地址，导致无详细信息的用户被排除。

-- 错误：WHERE up.address IS NOT NULL会过滤掉wangwu
SELECT
   u.username,
   up.address
FROM
   users u
LEFT JOIN
   user_profiles up ON u.id = up.user_id
WHERE
   up.address IS NOT NULL;

正确示例：若需过滤关联表的条件，应写在ON中（仅影响关联结果，不排除左表记录）。

-- 正确：只关联有地址的详细信息，但保留所有用户
SELECT
   u.username,
   up.address
FROM
   users u
LEFT JOIN
   user_profiles up ON u.id = up.user_id AND up.address IS NOT NULL;
3. 索引优化

多表连接查询的性能依赖于关联字段的索引。若关联字段（如外键user_id、category_id）未建索引，MySQL 会进行全表扫描，效率极低。建议为所有外键字段和常用查询字段建立索引。

示例：为user_profiles.user_id建立索引。

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

三、实战案例：电商订单查询

结合前面的知识点，我们通过一个完整的电商订单查询案例，巩固多表连接的应用。

3.1 涉及的表

  • users（用户表）：存储用户信息。

  • products（商品表）：存储商品信息。

  • categories（分类表）：存储商品分类。

  • orders（订单表）：存储订单基本信息（关联用户）。

  • order_details（订单详情表）：存储订单中的商品信息（关联订单和商品）。

3.2 需求：查询用户的订单详情（含商品分类、购买数量、订单时间）

SQL 语句
SELECT
   u.username, -- 用户名
   o.order_time, -- 订单时间
   c.category_name, -- 商品分类
   p.product_name, -- 商品名称
   p.price, -- 商品单价
   od.quantity, -- 购买数量
   (p.price * od.quantity) AS total_amount -- 小计（单价*数量）
FROM
   users u
INNER JOIN
   orders o ON u.id = o.user_id -- 用户与订单关联
INNER JOIN
   order_details od ON o.id = od.order_id -- 订单与订单详情关联
INNER JOIN
   products p ON od.product_id = p.product_id -- 订单详情与商品关联
INNER JOIN
   categories c ON p.category_id = c.id -- 商品与分类关联
WHERE
   o.order_time >= '2025-01-01' -- 过滤2025年以后的订单
ORDER BY
   o.order_time DESC; -- 按订单时间降序排列
执行结果（示例）
usernameorder_timecategory_nameproduct_namepricequantitytotal_amount
zhangsan2025-10-30 14:30:00手机华为 Mate 604999.0014999.00
zhangsan2025-10-29 10:15:00手机iPhone 155999.0015999.00
lisi2025-10-28 09:00:00电脑联想拯救者 Y9000P7999.0017999.00

四、总结

  1. 表关系设计是基础：一对一用于拆分表、减少冗余；一对多是最常见关系（如分类与商品）；多对多需通过中间表实现（如学生与课程）。合理的表关系是数据完整性的保障。

  2. 多表连接是核心：

  • 内连接（INNER JOIN）：取两表交集，只返回匹配记录。

  • 左外连接（LEFT JOIN）：以左表为基准，保留左表所有记录。

  • 右外连接（RIGHT JOIN）：以右表为基准，保留右表所有记录。

  • 全外连接：需通过LEFT JOIN + UNION + RIGHT JOIN间接实现，返回两表所有记录。

  1. 实战技巧需掌握：使用表别名简化 SQL、结合聚合函数做统计、通过DISTINCT去重、为关联字段建索引优化性能，同时避免笛卡尔积和外连接与WHERE条件的误用。

掌握表关系与多表连接查询，能让你从容应对复杂业务场景下的 MySQL 数据操作，为后续学习索引优化、事务管理等高级知识点打下坚实基础。

标签
#mysql #数据库 #1024程序员节
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码力引擎

你的鼓励是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值