1、多表连接简介:
在关系数据库中,一个查询往往会涉及多个表,因为很少有数据库只有一个表,而如果大多查询只涉及一个表的,那么那个表也往往低于第三范式,存在大量冗余与异常。
因此,连接(Join)就是一种把多个表连接成一个表的重要手段
比如简单的两个表连接学生表(student)和班级(class)表
id studentName studentClassId
1 Careyson 1
2 tony 2
3 Jack 3
4 Nancy 2
5 Peter 1
classId className
1 软件一班
2 软件二班
3 软件三班
进行连接后 会得到一个5列 15行的表。
2、笛卡尔乘积:
笛卡尔乘积在SQL中的实现方式也是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔乘积表,笛卡尔乘积是 关系代数里的一个概念,表示两个表中的每一行数据任意组合,上图的两个表连接即为笛卡尔乘积。(交叉连接)
3、内连接
内连接可以看作是先对两个表进行交叉连接后,再通过加上限制条件(SQL中通过关键字ON)剔除不符合条件的行的子集,得到的结果就是内连接了。如果对刚才的表操作加上限制条件
SELECT * FROM class AS c INNER JOIN student AS s ON c.classId = s.studentClassId;
这样就会生成
classId className id studentName studentClassId
1 软件一班 1 Careyson 1
2 软件二班 2 tony 2
3 软件三班 3 Jack 3
4 软件二班 4 Nancy 2
5 软件四班 5 Peter 1
限制条件所涉及的两个列的数据类型必须匹配
4、关系演算
关系演算法思考的方式是:“给我找出所有学生的信息,包括他们的班级信息,班级ID,学生id,学生姓名
用关系演算法的SQL查询语句如下:
SELECT * FROM class AS c,student AS s WHERE c.classId =s.studentClassId
5、外连接
假设添加一名Eric学生,但是忘了填写班级id
id studentName studentClassId
1 Careyson 1
2 tony 2
3 Jack 3
4 Nancy 2
5 Peter 1
6 Eric NULL
SELECT s.studentName,c.className FROM student AS s LEFT OUTER JOIN class AS c ON s.studentClassId=c.classId
结果就能查询到Eric的信息
6、自连接
id employeeName managerId
1 JackNULL
2 CareySon 1
3 Tony2
4 Tom2
5 Peter1
上述员工表(employee),因为经理也是员工的一种,所以将两种人放在一个表中,managerId记录的是当前员工的直系经理的员工id
如何查找CareySon的经理姓名?
可以看出,虽然数据存储在单张表中,但除了嵌套查询,只有自连接可以做到。
SELECT m.employeeName FROM employee AS e INNER JOIN employee AS m ON e.ManagerId=m.id AND e.employeeName='CareySon'
record
employee action
CareySon talk
Jack talk
CareySon run
Tom run
找出既会talk又会run的员工
SELECT r.employee FROM record AS r INNER JOIN record AS e ON r.action ='talk' AND e.action='run' AND r.employee=e.employee;
7、多表连接
id studentName studentClassId
1 Careyson 1
2 tony 2
3 Jack 3
4 Nancy 2
5 Peter 1
classId className
1 软件一班
2 软件二班
3 软件三班
teacherName classId
王 二 1
李三 2
对三个表进行笛卡尔乘积如下:
SELECT * FROM class CROSS JOIN teacher CROSS JOIN student;