关于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）关系，通过中间表实现交叉关联!
