MySQL表关联与多表联查一对一、一对多、多对多及JOIN查询介绍_F11

网站首页收藏本站

广告位联系

APP正在开发中...

返回顶部

分享到

MySQL表关联与多表联查一对一、一对多、多对多及JOIN查询介绍

Mysql 来源：互联网作者：佚名发布时间：2026-09-22 22:16:38 人浏览

摘要

本文系统梳理 MySQL 中三种常见的表关联关系（一对一、一对多、多对多）的建表方式与外键约束写法，并配合 INNER JOIN / LEFT JOIN / RIGHT JOIN 三种联合查询的实战示例，帮助你快速掌握多表设计核

本文系统梳理 MySQL 中三种常见的表关联关系（一对一、一对多、多对多）的建表方式与外键约束写法，并配合 INNER JOIN / LEFT JOIN / RIGHT JOIN 三种联合查询的实战示例，帮助你快速掌握多表设计核心。

一、表关联关系速览

在数据库设计中，表与表之间的关联关系主要分为以下几种：

关联类型	核心设计逻辑	常见场景
一对一 (1:1)	在从表外键列上添加 UNIQUE 约束，确保一条主表记录只对应一条从表记录	用户与身份证、用户与银行卡
一对多 (1:n)	在多方（n 方）建立外键列，指向一方的主键（去掉 UNIQUE 约束）	一个班级对应多名学生
多对多 (m:n)	不能直接在两张表互相加外键，必须引入第三张中间表绑定双方主键	用户（账号）与角色

二、一对一关联 (1:1)

一对一的核心在于：从表的外键字段既要引用主表主键，又要加上 UNIQUE 约束，从而保证一个主表记录最多只能被一条从表记录引用。

2.1 创建主表 t_card

-- 1. 创建主表 (t_card)

CREATE TABLE t_card (

card_id INT PRIMARY KEY AUTO_INCREMENT,

card_number VARCHAR(10),

card_date INT

);

2.2 创建从表 t_yonghu（外键字段加 UNIQUE）

-- 2. 创建从表 (t_yonghu)，暂不绑定外键关系，但外键字段需加 UNIQUE

CREATE TABLE t_yonghu (

yonghu_id INT PRIMARY KEY AUTO_INCREMENT,

yonghu_name VARCHAR(10),

yonghu_address VARCHAR(10),

fk_card_id INT UNIQUE

);

2.3 使用 ALTER TABLE 动态追加外键约束

-- 3. 使用 ALTER TABLE 动态追加外键约束 (约束命名为 fk_1)

ALTER TABLE t_yonghu

ADD CONSTRAINT fk_1 FOREIGN KEY (fk_card_id) REFERENCES t_card(card_id);

2.4 插入测试数据

-- 4. 插入测试数据

INSERT INTO t_card VALUES (NULL, '000000000', 10);

INSERT INTO t_card VALUES (NULL, '111111111', 10);

INSERT INTO t_yonghu VALUES (NULL, 'zhangsan', '西安', 1);

INSERT INTO t_yonghu VALUES (NULL, 'lisi', '北京', 2);

关键点：因为 fk_card_id 加了 UNIQUE，所以同一个 card_id 不能被多个用户引用，从而实现一对一关系。

三、一对多关联 (1:n)

3.1 设计原则

外键列必须添加到多方这一边。
一个班级（一方）对应多名学生（多方），所以外键加在学生表。
与一对一的区别：外键列不再加 UNIQUE 约束，从而允许一条主表记录被多条从表记录引用。

3.2 示例代码

-- 1. 创建一方表：班级表 (t_class)

CREATE TABLE t_class (

class_id INT PRIMARY KEY AUTO_INCREMENT,

class_name VARCHAR(10),

class_type VARCHAR(10),

class_count INT

);

-- 2. 创建多方表：学生表 (t_student)

-- 建表时直接定义外键 (不加 UNIQUE 约束)

CREATE TABLE t_student (

stu_id INT PRIMARY KEY AUTO_INCREMENT,

stu_name VARCHAR(10),

stu_age INT,

stu_address VARCHAR(20),

fk_class_id INT,

FOREIGN KEY (fk_class_id) REFERENCES t_class(class_id)

);

也可以先建表，后续通过 ALTER TABLE 追加外键约束：

-- 补充写法：先建表后追加外键

ALTER TABLE t_student

ADD CONSTRAINT fk_1 FOREIGN KEY (fk_class_id) REFERENCES t_class(class_id);

四、多对多关联 (m:n)

4.1 设计原则

两张主表（账号表、角色表）各自保持独立，不加任何外键。
必须单独创建一张中间表（关系表），里面包含两个外键，分别指向两张主表的主键。

4.2 示例代码

-- 1. 创建账号表 (t_zhanghao)

CREATE TABLE t_zhanghao (

zhanghao_id INT PRIMARY KEY AUTO_INCREMENT,

zhanghao_name VARCHAR(20),

zhanghao_miaoshu VARCHAR(20)

);

-- 2. 创建角色表 (t_juese)

CREATE TABLE t_juese (

juese_id INT PRIMARY KEY AUTO_INCREMENT,

juese_name VARCHAR(20),

juese_miaoshu VARCHAR(20)

);

-- 3. 创建中间表 (t_zhanghao_juese) 负责绑定双方关联

CREATE TABLE t_zhanghao_juese (

id INT PRIMARY KEY AUTO_INCREMENT,

fk_zhanghao_id INT,

fk_juese_id INT

);

-- 4. 为中间表追加两个外键约束

ALTER TABLE t_zhanghao_juese

ADD CONSTRAINT fk_zhanghao FOREIGN KEY (fk_zhanghao_id) REFERENCES t_zhanghao(zhanghao_id);

ALTER TABLE t_zhanghao_juese

ADD CONSTRAINT fk_juese FOREIGN KEY (fk_juese_id) REFERENCES t_juese(juese_id);

4.3 插入测试数据

-- 5. 插入测试数据

-- 插入账号数据

INSERT INTO t_zhanghao VALUES (NULL, 'zhangsan', '老实人');

INSERT INTO t_zhanghao VALUES (NULL, 'lisi', '伶俐人');

-- 插入角色数据

INSERT INTO t_juese VALUES (NULL, '开发', '写代码');

INSERT INTO t_juese VALUES (NULL, '测试', '找茬');

-- 插入关联关系 (实现 zhangsan 和 lisi 同时拥有开发和测试角色)

INSERT INTO t_zhanghao_juese VALUES (NULL, 1, 1);

INSERT INTO t_zhanghao_juese VALUES (NULL, 1, 2);

INSERT INTO t_zhanghao_juese VALUES (NULL, 2, 1);

INSERT INTO t_zhanghao_juese VALUES (NULL, 2, 2);

关键点：多对多的本质是「一个账号可有多个角色，一个角色也可属于多个账号」，这种双向的「多」关系只能通过中间表来承载。

五、多表联合查询（JOIN）

5.1 三种连接类型的区别

在进行多表联查时，通常使用 ON 来指定关联条件，主要分为以下三种连接方式：

连接类型	关键字 / 语法	核心区别（结果集特点）
内连接	INNER JOIN 或 JOIN	只显示左表和右表共同满足关联条件的数据（取交集）
左（外）连接	LEFT JOIN 或 LEFT OUTER JOIN	左表数据全部显示；右表有匹配则显示，没有匹配补 NULL
右（外）连接	RIGHT JOIN 或 RIGHT OUTER JOIN	右表数据全部显示；左表有匹配则显示，没有匹配补 NULL

5.2 通用语法格式

SQL

SELECT 表别名1.列名1, 表别名2.列名2...

FROM 表名称1 表别名1

[INNER JOIN | LEFT JOIN | RIGHT JOIN] 表名称2 表别名2

ON 表别名1.关联列 = 表别名2.关联列

WHERE 筛选条件;

5.3 经典实操示例

假设有学生表 t_student（从表）与班级表 t_class（主表），通过 fk_class_id = class_id 建立一对多关联。

示例一：查询学生姓名为 zhangsan 的所有信息

使用 INNER JOIN（也可使用 LEFT JOIN），查询结果包含学生基本信息和班级信息：

SELECT

s.stu_id,

s.stu_name,

s.stu_age,

s.stu_address,

c.class_name,

c.class_type

FROM t_student s

INNER JOIN t_class c

ON s.fk_class_id = c.class_id

WHERE s.stu_name = 'zhangsan';

示例二：查询班级名称为 java 的所有信息

使用 LEFT JOIN，保障即使该班级暂时没有学生，班级基本信息也能正常查出：

SELECT

c.class_id,

c.class_name,

c.class_type,

s.stu_id,

s.stu_name,

s.stu_age

FROM t_class c

LEFT JOIN t_student s

ON c.class_id = s.fk_class_id

WHERE c.class_name = 'java';

选用建议：以哪张表为主显示结果，就把那张表放在 LEFT JOIN 的左边。查询「某班级下的

所有学生」时，班级是主体，用左连接可以避免班级为空时被过滤掉。

六、总结

关联关系	外键位置	是否加 UNIQUE	是否需要中间表
一对一	从表外键列	是	否
一对多	多方外键列	否	否
多对多	中间表两个外键	否	是

掌握以上三种表关联关系的设计思路，再配合 INNER JOIN / LEFT JOIN / RIGHT JOIN 灵活选用，基本可以覆盖日常开发中绝大多数多表查询场景。建议在实际项目中：先理清业务实体之间的数量对应关系，再决定外键落在哪一侧、是否引入中间表，最后根据查询主体选择合适的 JOIN 类型。

您可能感兴趣的文章 :

原文链接 :

Tag : mysql(322)join(7)

MySQL表关联与多表联查一对一、一对多、多对多及

本文系统梳理 MySQL 中三种常见的表关联关系（一对一、一对多、多对多）的建表方式与外键约束写法，并配合 INNER JOIN / LEFT JOIN / RIGHT JOIN
MySQL数据库存储过程介绍

在MySQL开发中，很多新手会分不清存储过程、函数、触发器三者的定位。前面我们已经学习过MySQL触发器、普通查询SQL，今天我们单独把存储
MySQL数据库内置函数超详细图文教程介绍

日期函数函数名称描述 current_date() 当前日期 current_time() 当前时间 current_timestamp() 当前时间戳 date(datetime) 返回datetime参数的日期部分 date_
MySQL锁等待和死锁问题怎么排查？show engine innod

锁的分类乐观锁（S）：读锁又叫共享锁，允许其他事物读不允许写 1 2 3 4 mysql8版本 SELECT * FROM USER FOR SHARE mysql8之前的版本 SELECT * FROM USER
mysql优化神器前缀索引的使用介绍

所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这
MySQL数据库迁移：从连接到代码的“零改造“路径

一、为什么迁移成本总是超预期很多同学在做 MySQL 迁移时最头疼的是改造工作量驱动要换、SQL 要改、函数要重写、代码要调整.一套流程走
查看Mysql版本最好用的几种方法

查看 MySQL 版本主要分为未登录数据库（命令行）和已登录数据库（SQL指令）两种场景。以下是最好用的几种方法，您可以根据当前的环境选
MySQL对查询的值四舍五入保留两位小数的方法

开发财务、充值、消费、余额相关业务时，经常遇到金额展示需求：数据库原始小数位数较多，前端需要统一保留两位小数展示。很多同学
MySQL必备的常用内置函数介绍

一.日期函数上面这些就是常见的日期函数，通过相应的描述相信大家都知道它们相应的作用，那么下面我们废话不多说，直接通过一些例子
MySQL高效处理ORDER BY与GROUP BY查询的优化策略

在高并发、大数据量的业务场景中，SQL 查询性能直接影响系统整体响应速度。其中，ORDER BY（排序）和GROUP BY（分组）是最常见但也最容易引

MySQL表关联与多表联查一对一、一对多、多对多及JOIN查询介绍

一、表关联关系速览

二、一对一关联 (1:1)

2.1 创建主表 t_card

2.2 创建从表 t_yonghu（外键字段加 UNIQUE）

2.3 使用 ALTER TABLE 动态追加外键约束

2.4 插入测试数据

三、一对多关联 (1:n)

3.1 设计原则

3.2 示例代码

四、多对多关联 (m:n)

4.1 设计原则

4.2 示例代码

4.3 插入测试数据

五、多表联合查询（JOIN）

5.1 三种连接类型的区别

5.2 通用语法格式

5.3 经典实操示例

示例一：查询学生姓名为 zhangsan 的所有信息

示例二：查询班级名称为 java 的所有信息

六、总结

您可能感兴趣的文章 :

MySQL表关联与多表联查一对一、一对多、多对多及

MySQL数据库存储过程介绍

MySQL数据库内置函数超详细图文教程介绍

MySQL锁等待和死锁问题怎么排查？show engine innod

mysql优化神器前缀索引的使用介绍

MySQL数据库迁移：从连接到代码的“零改造“路径

查看Mysql版本最好用的几种方法

MySQL对查询的值四舍五入保留两位小数的方法

MySQL必备的常用内置函数介绍

MySQL高效处理ORDER BY与GROUP BY查询的优化策略

zabbix监控mysql的实例方法

MySQL时间类型的选择

MySQL数据库基本SQL语句教程之高级操作

MySQL文件权限存在的安全问题和解决方

利用Mysql定时+存储过程创建临时表统