【1024特别篇】 MySQL 复盘多表关联(1对1、1对多、多对多) 帮你打通任督二脉

🚀 个人主页 极客小俊
✍🏻 作者简介：程序猿、设计师、技术分享
🐋 希望大家多多支持, 我们一起学习和进步！
🏅 欢迎评论 ❤️点赞💬评论 📂收藏 📂加关注

关于1对1、1对多、多对多的操作 前面我们也讲过很多次了!

不过还是有朋友一直没有搞清楚这三种关系，今天这里给大家抛几个案例复习一下!

一对一 关系

在MySQL中, 一对一关系指两个表通过主键和外键关联，并且每条记录在另一表中仅对应唯一记录

如果还没有理解，我们来看一个案例帮助理解~

例如: 用户和身份证-->(一对一关系)

比如说: 这里有两个表，用户表(user)和用户信息表(card), 而且每个用户有唯一的用户id也就是主键id

这个时候，用户信息表里面的的身份证是不是只能属于一个用户? 对吧!

那么我们可以在这个用户信息表中创建一个外键(user_id)去关联用户表

SQL语句如下

/*用户表（主表）*/
CREATE TABLE user (
    id VARCHAR(32) PRIMARY KEY,  -- 用户唯一ID（如U1001）
    username VARCHAR(30)         -- 用户名（如 张三）
);

/*用户信息表*/
CREATE TABLE id_card (
    id VARCHAR(32) PRIMARY KEY,      -- 身份证唯一ID（如 "C2001"）仅仅代表数据条目的唯一性
    card_number VARCHAR(18) UNIQUE,  -- 身份证号（实际业务中可能也是唯一键，这里强调关联唯一）
    user_id VARCHAR(32) UNIQUE,      -- 关联的用户ID（必须唯一！）
    issue_date DATE,                 -- 发证日期
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES user(id)
);

我们把代码复制到Navicat for MySQL中去执行一下，

如图

这个时候，可能有人要问了，我这样创建出来的表，在从表(id_card)中, 可以添加多个不同的身份证去关联一个用户吗? 那么这样就会形成一个一对多（1:N）的关系了! 显然这样是不对的~

那么为了强制形成1:1关系 我们可以在从表的相应字段上加入UNIQUE约束

根据上面的案例，我们可以看到user_id字段既是外键 又是唯一键

并且card_number字段也做了UNIQUE约束处理, 因为身份证不能重复!

确保一个用户只能被一个身份证关，从而强制形成1:1关系

如图

从表的user_id字段必须填写并指向主表的user.id字段, 不能乱填!

并且user_id字段加了 UNIQUE约束，确保一个用户, 如U1001只能被一张身份证， 如C2001所关联!

反过来，也是一样的, 一张身份证也只能关联一个用户,不能同时属于多个用户!

现实场景中，一个人只能有一张有效身份证，一张身份证也只能属于一个人,这是法律规定的1:1关系!

所以有的时候，我们要搞明白对应关系，你就要对现实场景做出正确的分析才可以~

如果我们不在相应字段上处理或者不加UNIQUE, 那么则默认是一对多(1:N)

比如这里我们取消user_id和card_number字段的UNIQUE约束看看会怎么样~!

现在我们再次添加数据到从表(id_card)中看看~

如图

看到问题所在了吧~显然这样做是不符合逻辑的!

要加UNIQUE后, 严格满足我们数据库中的第三范式对1:1关系的定义!

总结

1:1 关系 = 从表的外键字段要加 UNIQUE 强制形成1 : 1

1:N 关系 = 从表的外键字段不加 UNIQUE 这样允许重复关联!

很多人没有搞懂1:1 关系 和 1:N 关系 的本质区, 这样是不是更清楚了？

案例

一个老公对应一个老婆，一个老婆对应一个老公，互相唯一绑定!

SQL代码

/*老公表*/
CREATE TABLE man(
  id VARCHAR(32) PRIMARY KEY,
  username VARCHAR(30)
);

/*老婆表*/
CREATE TABLE woman(
  id VARCHAR(32) PRIMARY KEY,
  username VARCHAR(30),
  husband VARCHAR(32) UNIQUE,
  CONSTRAINT wm_fk FOREIGN KEY(husband) REFERENCES man(id)
);

插入数据

INSERT INTO man VALUES('1','小明');
INSERT INTO man VALUES('2','小聪');
INSERT INTO man VALUES('3','老王');

INSERT INTO woman VALUES('1','小花','2');
INSERT INTO woman VALUES('2','小静','1');

那么如果我们再次插入以下数据就是错误的

INSERT INTO woman VALUES('3','小红','1');   //错误:因为违反1对1
INSERT INTO woman VALUES('3','小红','10');  //错误:因为违反外键--主表必须存在该外键值才行

接下来，我们就可以使用select查询语句来进行关联查询一下夫妻信息, 这里我们直接使用内联查询即可

SQL如下

SELECT m.username as '老公名称',w.username as '老婆名称'
FROM man as m INNER JOIN woman as w
ON m.id = w.husband
ORDER BY m.id DESC

结果如下

一对多 关系

搞懂了一对一的关系，那么一对多的关系也很好理解了!

一对一的关系也就是一个主表记录可以对应多个从表记录，但从表记录只能属于一个主表记录!

不明白没关系，我们来结合案例理解就行了!

举个栗子: 班级和学生

现在有一个班级表(class) 和 一个学生表(student)

那么现实生活中，我们已经知道一个班级可以有多个学生, 比如有小明、小红...，

但每个学生只能属于一个班级, 对吧! 这就是这就是1个班级 : N个学生 也就是1:N的关系

所以根据这个逻辑关系，我们可以把这两个表的代码结构创建出来

SQL如下

/*主表：班级(class)*/
CREATE TABLE class (
    id INT PRIMARY KEY,      -- 班级表ID（如1班）
    username VARCHAR(20)     -- 班级名称
);

/*从表：学生(student)*/
CREATE TABLE student (
    id INT PRIMARY KEY,  -- 学生表ID
    username VARCHAR(20),-- 学生姓名
    class_id INT,        -- 外键，关联班级ID 并且这个字段不加UNIQUE！
    FOREIGN KEY (class_id) REFERENCES class(id)  
);

从创建表结构上看，这里也看出来了区别~ 关联的字段没有加入UNIQUE约束!

因为从场景关系中分析我们得出的结论就是 主表一条记录可以对应从表多条记录, 所以不要加UNIQUE约束

总之记住一点: 主表的一条记录能拥有从表的N条记录，但从表的每条记录只能属于主表的一条记录!

而且还要注意一点，外键是建立在多的一方，也就是从表里面!

案例

比如人和车的关系，在现实中，我们一个人是不是可以购买多辆车呢!

那按照这个场景逻辑，我们创建表结构如下

/*主表 用户表*/
CREATE TABLE Person(
  id VARCHAR(32) PRIMARY KEY,
  username VARCHAR(30),
  sex VARCHAR(5)
);

/*从表 车辆表*/
CREATE TABLE car(
  id VARCHAR(32) PRIMARY KEY, /*车辆表主键字段*/
  username VARCHAR(30),		  /* 车辆名称*/
  price NUMERIC(10,2), 		  /*车辆价格*/
  pid VARCHAR(32),			  /* 车辆关联字段 代表车辆属于谁*/
  CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES Person(id)
);

插入数据

INSERT INTO Person VALUES('P01','杰克','男');
INSERT INTO Person VALUES('P02','汤姆','男');
INSERT INTO Person VALUES('P03','爱丽丝','女');
INSERT INTO Person VALUES('P04','巴顿','男');

INSERT INTO car VALUES('C001','宝马',300000,'P01');
INSERT INTO car VALUES('C002','奔驰',400000,'P01');
INSERT INTO car VALUES('C003','奥迪',450000,'P01');
INSERT INTO car VALUES('C004','QQ车',55000,'P02');

INSERT INTO car VALUES('C005','特斯拉',188000,null);
INSERT INTO car VALUES('C006','小米Su7',311000,null);
INSERT INTO car VALUES('C007','奔驰',400000,'P03');
INSERT INTO car VALUES('C008','宝马',300000,'P03');

注意: pid外键字段值可以为NULL, 来表示该车辆还未卖出!

那么现在我们就来查询一下!

问题1: 查询哪些人购买了哪些车

SQL如下

SELECT p.username as '姓名', c.username as '车辆名称'
FROM person as p INNER JOIN car as c
ON p.id = c.pid

如图

问题2: 查询杰克拥有哪些车

SQL如下

SELECT p.username as '姓名', c.username as '车辆名称'
FROM person as p INNER JOIN car as c
ON p.id = c.pid
WHERE p.username='杰克'

如图

问题3: 查询哪些人有两辆或两辆以上的车

SQL如下

SELECT p.username as '姓名'
FROM person as p INNER JOIN car as c
ON p.id = c.pid
GROUP BY p.id
HAVING COUNT(c.username)>2


/*或者*/

SELECT p.username as '姓名', c.username as '车辆名称',c.price as '车辆价格'
FROM person as p INNER JOIN car as c
ON p.id = c.pid
WHERE p.id in (SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2)

如图

问题4:查询谁没有车

SQL如下

SELECT person.username FROM person 
LEFT JOIN car ON car.pid=person.id WHERE pid is NULL

如图

右关联跟左关联一样，只需要把左关联的表调换一下位置便成了右关联的结果，

所以只要会了左关联，右关联也是一样的!

注意:

删除主键信息时，如果该主键字段值在外键表中存在时，该记录是不能删除的

我们要把外表相关信息删除之后，才能删除!

平时我们在做开发和练习的时候，也一定要多结合官方的文档~

多对多 关系

如果你搞懂了 前面两种表关系，那么多对多也可以很简单理解!

多对多（M:N） 也就是两个表的记录可以互相多对多的进行关联, 但必须通过第三张中间表来带两个外键实现!

举个栗子

比如学生选课, 现实中,一个学生可选多门课，一门课也可被多个学生选!

根据这个逻辑关系，我们创建以下3个表

SQL语句如下

-- 学生表
CREATE TABLE student (
    id INT PRIMARY KEY,
    sname VARCHAR(20)
);

-- 课程表
CREATE TABLE course (
    id INT PRIMARY KEY,
    title VARCHAR(50)
);

-- 中间表（解决多对多）
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),  -- 联合主键防止重复选课
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id)
);

添加数据

-- 插入学生数据
INSERT INTO student (id, sname) VALUES (1, '张三'),(2, '李四'),(3, '王五');

-- 插入课程数据
INSERT INTO course (id, title) VALUES (101, '数学'),(102, '英语'),(103, '编程');


-- 插入选课关系（中间表）
INSERT INTO student_course (student_id, course_id) VALUES 
(1, 101), -- 张三选数学
(1, 102), -- 张三选英语
(2, 101), -- 李四选数学
(2, 103), -- 李四选编程
(3, 102); -- 王五选英语

那么现在我们就可以来查询一下多对多关系的数据了!

问题1: 查询哪些人选了哪些课

SELECT s.sname,c.title FROM student as s,course as c,student_course as sc
WHERE sc.student_id=s.id and sc.course_id=c.id

/*或者*/

SELECT s.sname,c.title FROM student as s 
INNER JOIN student_course as sc ON s.id = sc.student_id
INNER JOIN course as c  ON c.id = sc.course_id

如图

问题2 查询哪些人没有选课

SELECT s.sname as '姓名',c.title as '课程名' FROM student as s 
LEFT  JOIN student_course as sc ON s.id = sc.student_id
LEFT  JOIN course as c  ON c.id = sc.course_id
WHERE c.title is NULL;

如图

问题3 反过来查询哪些课没人选

SELECT s.sname as '姓名',c.title as '课程名' FROM course as c 
LEFT  JOIN student_course as sc ON c.id = sc.course_id
LEFT  JOIN student as s  ON s.id = sc.student_id
WHERE s.sname is NULL

如图

多对多的核心重点其实就在于中间表, 用它来记录另外两个表之间的关系，并且通过两个外键关联两张主表!

上面的案例中就很好的体现了这一点，张三选了数学和英语（1:N）而数学被张三、李四选（N:1）

整体形成学生↔课程的多对多（M:N）关系，通过中间表实现交叉关联!

最后

如果你看到这里 ，那证明你已打通数据库关系的任督二脉——

一对一锁死唯一，一对多掌控从属，多对多玩转交叉，从此表关联设计信手拈来~哇哈哈

最后祝大家1024快乐!~ 🥳🥳🥳🥳

"👍点赞" "✍️评论" "收藏❤️"
大家的支持就是我坚持下去的动力!

---

如果以上内容有任何错误或者不准确的地方，🤗🤗🤗欢迎在下面 👇👇👇 留个言指出、或者你有更好的想法，
欢迎一起交流学习❤️❤️💛💛💚💚

更多好玩 好用 好看的干货教程可以点击下方关注❤️微信公众号❤️
说不定有意料之外的收获哦..🤗嘿嘿嘿、嘻嘻嘻🤗!
🌽🍓🍎🍍🍉🍇