多表关系
在项目开发中,根据业务需求及业务模块得不同,在众多数据库表中,会存在各种联系。基本分为:
- 一对一关系
- 一对多(多对一)关系
- 多对多关系
一对一关系
- 例如:学生与学生信息得关系
- 实现方式:单表拆分,将主字段放在一张表中,其他非主字段放入另一张表中。在任意一方加入外键,关联另外一方得主键,并且设置外键为唯一的
一对多(多对一)
- 例如:班级与学生的关系;一个学生对应一个班级,一个班级对应多名学生
- 实现方式:在多的一方建立外键,指向唯一的一方的主键。(如:该班的学生表有多个,该班的班级表只有一个)
多对多
- 例如:学生与课程的关系;一个学生可以学习多门课程,一门课程也可以接受多名学生
- 实现方式:建立第三张表,作为中间表,中间表至少包含两个外键,分别关联存在关系的两表中的主键。
多表查询
笛卡尔积
笛卡尔积,数学中表示两个集合中第一个对象是X,第二个对象是Y,展示二者之间所有可能的组合情况,也就是并集关系。在多表查询中容易出现数据重复的情况,也就是无效数据。在多表查询中,需要做到清除无效的笛卡尔积。要用到特定的查询方式达到效果。
多表查询分类
连接查询
- 内连接(Inner Join):相当于查询X,Y中交集部分的数据
- 外连接
- 左外连接(左连接 Left Join):查询左表所有数据,以及两张表交集部分数据
- 右外连接(右连接 Right Jion):查询右表所有数据,以及两张表交集部分数据
- 自连接(Self Jion):当前表与自身相关联的数据
子查询
内连接
- 内连接两张表交集的部分
- 内连接查询语法
- 隐式内连接:SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
- 显式内连接:SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
- 内连接查询语法
- 演示
-- 查询每一个学生的姓名,及关联的班级
-- 表结构:class(班级表),stu(学生表)
-- 连接方式:stu.class_id = class.id(学生表的班级id = 班级的主键id)
-- 隐式内连接演示
SELECT stu.name,class.name FROM stu,class WHERE stu.class_id = class.id;
-- 显式内连接演示
SELECT stu.name,class.name FROM stu INNER JOIN class ON stu.class_id = class.id;
外连接
- 左外连接:相当于查询左表的所有数据,包含左表和右表交集部分(也就是左右表都满足条件的部分)的数据,另外左边剩下的,不满足右边条件的部分用null表示。
- 左外连接语法:SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
- 右外连接:相当于查询右表的所有数据,包含右表和左表交集部分(也就是左右表都满足条件的部分)的数据,另外右边剩下的,不满足左边条件的部分用null表示。
- 右外连接语法:SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
- 演示
-- 还是以班级和学生为例,查询学生的所有数据,和对应的班级信息
-- 左外连接演示
SELECT stu.*, class.name FROM stu LEFT JOIN class ON stu.class_id = stu.id;
--可简写为
SELECT * FROM stu s LEFT class c ON s.class_id = c.id;
-- 右外连接演示
SELECT stu.*, class.name FROM stu RIGHT JOIN class ON stu.class_id = stu.id;
--可简写为
SELECT * FROM stu s RIGHT class c ON s.class_id = c.id;
- 重点:左右表连接返回的结果和记录条数不一定相等,因为左右连接是以左表或者右表为标准,对表中每一个数据都进行一次比较并返回结果。以左连接为例,就是以左表为准,那么在没有其他操作的情况下,查询结果总记录条数应该与左表的记录条数相等。
自连接
- 自连接是一种物理上存在于同一张表的连接关系,但是逻辑上确实多表查询关系。最常见的是表中存在于上下级和父子级的关系。例如学校职工表,其中包含所有的职工,身份包括校长,教导主任,学年组长,班主任,科任老师等。为了清晰的表示之间的层级关系,可以增加一个直接领导id的字段,教导主任指向校长id,学年组长指向教导主任,班主任指向学年组长等。
- 自连接语法:SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
- 演示
-- 查询教师职工 及其 所属直接领导的名字
-- 表结构:emp(职工表)
-- 自连接演示(结果为交集)
SELECT e.name,m.name FROM emp e, emp m WHERE e.id = m.manger_id;
-- 如果存在员工暂时没有领导, 同时也需要查询出时(可采用左右连接,不存在领导的领导为null)
SELECT e.name,m.name FROM emp e LEFT JOIN emp m ON e.id = m.manger_id;
子查询
- SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。
- 子查询语法:SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);