我要评论前言
本文系统梳理 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 类型。
到此这篇关于mysql表关联与多表联查一对一、一对多、多对多及join查询详解的文章就介绍到这了,更多相关mysql表关联与多表联查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论