引言
在数据库中,单表的操作是最简单的,但是在实际业务中最少也有十几张表,并且表与表之间常常相互间联系;
一对一、一对多、多对多是表与表之间的常见的关系,初学时在多表连接时会纠结如何写对应的Sql
语句,下面就分享一些小技巧;
1. 一对一
一对一关系是最好理解的一种关系,在数据库建表的时候可以将人表的主键放置与身份证表里面,也可以将身份证表的主键放置于人表里面
一对一的关系就是一种特殊的多对多的关系,一张表A中的一条记录只能对应另一张表B中的一条记录,另一张表B中的一条记录也只能对应一张表A中的一条记录
例如:
学生表student:id name1001 张三1002 张四
学生卡表card:id name111 card1222 card2
这里的一个学生只能对应一张学生卡,一张学生卡只能对应一个学生,那么学生和学生卡就是一对一的关系;
一对一设计有两种方案:
方案一:共享主键(不推荐)
共享主键就是让学生表的主键和学生卡表的主键一样,那么两张表可以变成这样:
学生表studentid name1001 张三1002 张四
学生卡表cardid name1001 card11002 card2
这样学生卡表的id和学生表的id主键相同,这就是主键共享
查询案例:查询张三的学生卡信息
SELECT *FROM card cWHERE id='1001'
注意:
- 添加数据:先添加先产生的表,后添加后产生的表记录
- 删除数据:先删除后产生的表记录,再删除先产生的表记录
- 查询数据:无需进行连接查询
但是一般在表的设计时尽量避免主键的相同,所以主键共享一般不会去使用,了解即可;
方案二:唯一外键:(外键加一个唯一性约束)
唯一外键是一对一设计推荐的方法,顾名思义,也是需要给某个表添加外键,但是该外键必须有唯一性约束,通俗来说就是该外键不能有重复;
假设给学生卡表添加外键:
学生表studentid name1001 张三1002 张四
学生卡表cardid namestuent_id(设计表时给该字段添加唯一性约束)111 card1 1001222 card2 1002
还是上面的例子:查询张三的学生卡信息
SELECT * FROM card c JOIN student s ON c.student_id=s.id WHERE s.name='张三'
在这里就直接当成多对多使用即可,所以一对一和多对多语法上并没有什么太大区别,只是在外键处加了一个唯一性约束;
2. 一对多
一对多是最基础的表间关系,意思是一张表A中的一条记录可以对应另一张表B中的多条记录,另一张表B中的一条记录只能对应一张表A中的一条记录
班级是1端,学生是多端, 结合面向对象的思想,1端是父亲,多端是儿子,所以多端具有1端的属性,也就是说多端里面应该放置1端的主键,那么学生表里面应该放置班级表里面的主键
举个一对多
的例子:
有两张表,
表A:学生表student(子表)id name class_id(外键非空:班级id)1001 张三 1111002 张四 2221003 王五 1111004 赵六 111
表B:班级表class(父表)id name111 class1222 class2
一个班级对应多个学生,一个学生只能对应一个班级,所以这两个表的关系也就很明确了:
班级表中的一条记录可以对应学生表的多条数据,学生表中的一条记录只能对应班级表的一条数据;
查询案例:查询所有姓张的学生的id,name和所在班级name
SELECT s.id,s.name,c.name as classNameFROM student sJOIN class c ON s.class_id=c.idWHERE s.name LIKE '张%'
在一对多
关系中需要注意以下几点:
- 添加数据时,先添加父表(class)记录,再添加子表(student)记录;(比如增加一个学生而他的班级是class3,父表没有该班级需要先添加)
- 删除数据时,先删除子表(student)记录,再删除父表(class)记录;(比如删除一个学生且只有他的班级是class2,先删除学生后再删除父表的class2)
在设计表时,可以遵循以下口诀: 一对多,两张表,多的表加外键
解释一下:在一对多的关系中,存在两张表(一张父表一张子表),父表的一条数据对应子表的多条数据,那么子表(多)就需要添加上父表(一)的外键字段;
这样才能将两张表连接起来;
3.多对多
多对多的意思是: 一张表A中的一条记录可以对应另一张表B中的多条记录,另一张表B中的一条记录也可以对应一张表A中的多条记录
对于多对多关系,需要转换成1对多关系,那么就需要一张中间表来转换,这张中间表里面需要存放学生表里面的主键和课程表里面的主键,此时学生与中间表示1对多关系,课程与中间表是1对多关系,学生与课程是多对多关系
比如:
学生表student:id name1001 张三1002 张四1003 王五1004 赵六
课程表course:id name 111 java 222 mysql
这两张表就是多对多的关系,因为一个学生可以选择多门课程,一门课程可以被多个学生选择;
那么这样不论给哪个表添加外键都不行,这时就需要额外创建一个关系表来存放这两张表的id键值:
学生课程关系表student_course_relation
(关系表):
student_id course_id1001 1111001 2221002 1111002 222
这样就可以分开来看了:
学生表和关系表就是一对多的关系,课程表和关系表也是一对多的关系
查询案例:查询所有姓张的学生的id、name和所选课程的name
SELECT s.id, s.name, c.nameFROMstudent sJOIN student_course_relation scrON scr.student_id=s.idJOIN course cON scr.course_id=c.idWHEREs.name LIKE '张%'
多对多关系同样注意几点:
- 添加数据时,先添加父表记录(student,course),再添加子表(student_course_relation)记录
- 删除数据时,先删除子表记录(student_course_relation),再删除父表记录(student,course)
注:父表和子表的确认关系是:谁先生成谁是父表。
上述一对多例子中必须要先生成1端的表,才能有多端的表,因此父表为班级表。
而多对多的例子中则是给两张表(学生和课程)建立关联关系,需要在关系表子表中添加新的记录关联两张父表,因此父表为先生成的学生和课程表。
在多对多设计表时,可以遵循以下口诀:
多对多,三张表,关系表加外键!!!
意思就是当存在多对多的关系时,需要涉及到第三张表关系表的设计,而关系表就是存放了存在多对多关系的两张表的外键;
4.总结
在写sql语句中最重要的就是找表之间的关系,只有搞清楚各种表之间的联系,才不容易出错;
这两句口诀再强调一下:
一对多,两张表,多的表加外键
多对多,三张表,关系表加外键