数据库表关系详解（一对多、一对一、多对多）

引言

在数据库中，单表的操作是最简单的，但是在实际业务中最少也有十几张表，并且表与表之间常常相互间联系；

一对多、一对一、多对多是表与表之间的常见的关系，初学时在多表连接时会纠结如何写对应的Sql语句，下面就分享一些小技巧；

一对多

一对多是最基础的表间关系，意思是一张表A中的一条记录可以对应另一张表B中的多条记录，另一张表B中的一条记录只能对应一张表A中的一条记录

举个一对多的例子：

有两张表，

表A：学生表student（子表）

id     name   class_id（外键非空：班级id）
1001    张三      111
1002    张四      222
1003    王五      111
1004    赵六      111

表B：班级表class（父表）

id      name
111     class1
222     class2

一个班级对应多个学生，一个学生只能对应一个班级，所以这两个表的关系也就很明确了：

班级表中的一条记录可以对应学生表的多条数据，学生表中的一条记录只能对应班级表的一条数据；

查询案例：查询所有姓张的学生的id，name和所在班级name

SELECT 
	s.id,s.name,c.name as className
FROM 
	student s
JOIN 
	class c 
ON 
	s.class_id=c.id
WHERE 
	s.name LIKE '张%'

在一对多关系中需要注意以下几点：

• 添加数据时，先添加父表（class）记录，再添加子表（student）记录；（比如增加一个学生而他的班级是class3，父表没有该班级需要先添加）
• 删除数据时，先删除子表（student）记录，再删除父表（class）记录；（比如删除一个学生且只有他的班级是class2，先删除学生后再删除父表的class2）

---

在设计表时，可以遵循以下口诀：

一对多，两张表，多的表加外键

解释一下：在一对多的关系中，存在两张表（一张父表一张子表），父表的一条数据对应子表的多条数据，那么子表（多）就需要添加上父表（一）的外键字段；

这样才能将两张表连接起来；

一对一

一对一的关系就是一种特殊的多对多的关系，一张表A中的一条记录只能对应另一张表B中的一条记录，另一张表B中的一条记录也只能对应一张表A中的一条记录

例如：

学生表student：

id     name
1001    张三
1002    张四

学生卡表card：

id     name
111    card1
222    card2

这里的一个学生只能对应一张学生卡，一张学生卡只能对应一个学生，那么学生和学生卡就是一对一的关系；

一对一设计有两种方案：

共享主键：(不推荐)

共享主键就是让学生表的主键和学生卡表的主键一样，那么两张表可以变成这样：

学生表student
id     name
1001    张三
1002    张四

学生卡表card
id      name
1001    card1
1002    card2

这样学生卡表的id和学生表的id主键相同，这就是主键共享

查询案例：查询张三的学生卡信息

SELECT 
	*
FROM 
	card c
WHERE 
	id='1001'

注意：

• 添加数据：先添加先产生的表，后添加后产生的表记录
• 删除数据：先删除后产生的表记录，再删除先产生的表记录
• 查询数据：无需进行连接查询

但是一般在表的设计时尽量避免主键的相同，所以主键共享一般不会去使用，了解即可；

唯一外键：（外键加一个唯一性约束）

唯一外键是一对一设计推荐的方法，顾名思义，也是需要给某个表添加外键，但是该外键必须有唯一性约束，通俗来说就是该外键不能有重复；

假设给学生卡表添加外键：

学生表student
id     name
1001    张三
1002    张四

学生卡表card
id      name	stuent_id（设计表时给该字段添加唯一性约束）
111    card1	 1001
222    card2	 1002

还是上面的例子：查询张三的学生卡信息

SELECT 
	* 
FROM 
	card c 
JOIN 
	student s 
ON 
	c.student_id=s.id 
WHERE 
	s.name='张三'

在这里就直接当成一对多使用即可，所以一对一和一对多语法上并没有什么太大区别，只是在外键处加了一个唯一性约束；

多对多

多对多的意思是：一张表A中的一条记录可以对应另一张表B中的多条记录，另一张表B中的一条记录也可以对应一张表A中的多条记录

比如：

学生表student：

id      name
1001    张三
1002    张四
1003    王五
1004    赵六

课程表course：

id     name   
111    java   
222    mysql

这两张表就是多对多的关系，因为一个学生可以选择多门课程，一门课程可以被多个学生选择；

那么这样不论给哪个表添加外键都不行，这时就需要额外创建一个关系表来存放这两张表的id键值：

学生课程关系表student_course_relation（关系表）：

student_id     course_id
1001            111
1001            222
1002            111
1002            222

这样就可以分开来看了：

学生表和关系表就是一对多的关系，课程表和关系表也是一对多的关系

查询案例：查询所有姓张的学生的id、name和所选课程的name

SELECT 
	s.id, s.name, c.name
FROM
	student s
JOIN 
	student_course_relation scr
ON 
	scr.student_id=s.id
JOIN 
	course c
ON 
	scr.course_id=c.id
WHERE
	s.name LIKE '张%'

多对多关系同样注意几点：

• 添加数据时，先添加父表记录(student，course)，再添加子表(student_course_relation)记录
• 删除数据时，先删除子表记录(student_course_relation),再删除父表记录(student，course)

---

在多对多设计表时，可以遵循以下口诀：

多对多，三张表，关系表加外键

意思就是当存在多对多的关系时，需要涉及到第三张表关系表的设计，而关系表就是存放了存在多对多关系的两张表的外键；

总结

在写sql语句中最重要的就是找表之间的关系，只有搞清楚各种表之间的联系，才不容易出错；

这两句口诀再强调一下：

一对多，两张表，多的表加外键

多对多，三张表，关系表加外键