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

引言

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

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

一对多

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

举个一对多的例子：

有两张表，

表A：学生表student（子表）

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

表B：班级表class（父表）

id      name
111     class1
222     class2
1
2
3

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

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

查询案例：查询所有姓张的学生的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 '张%'
1
2
3
4
5
6
7
8
9
10

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

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

---

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

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

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

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

一对一

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

例如：

学生表student：

id     name
1001    张三
1002    张四
1
2
3

学生卡表card：

id     name
111    card1
222    card2
1
2
3

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

一对一设计有两种方案：

共享主键：(不推荐)

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

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

学生卡表card
id      name
1001    card1
1002    card2
1
2
3
4
5
6
7
8
9

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

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

SELECT 
	*
FROM 
	card c
WHERE 
	id='1001'
1
2
3
4
5
6

注意：

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

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

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

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

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

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

学生卡表card
id      name	stuent_id（设计表时给该字段添加唯一性约束）
111    card1	 1001
222    card2	 1002
1
2
3
4
5
6
7
8
9

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

SELECT 
	* 
FROM 
	card c 
JOIN 
	student s 
ON 
	c.student_id=s.id 
WHERE 
	s.name='张三'
1
2
3
4
5
6
7
8
9
10

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

多对多

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

比如：

学生表student：

id      name
1001    张三
1002    张四
1003    王五
1004    赵六
1
2
3
4
5

课程表course：

id     name   
111    java   
222    mysql
1
2
3

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

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

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

student_id     course_id
1001            111
1001            222
1002            111
1002            222
1
2
3
4
5

这样就可以分开来看了：

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

查询案例：查询所有姓张的学生的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 '张%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

---

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

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

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

总结

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

这两句口诀再强调一下：

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

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

先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦