目录
自连接查询
多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
一对多案例
多对多案例
一对一案例
多表查询概念
概述:指的是从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中,两个集合,A集合与B集合的所有组合情况。(在多表查询中,需要消除无效的笛卡尔积)
补充:如果使用到了多表查询,那么多张表之间一定有着,直接或间接的连接条件
多表查询分类
-
连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 右外连接:查询右表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用表别名
-
子查询
如图所示:
内连接查询
内连接是查询两张表交集部分的数据
内连接查询语法:
隐式内连接:
SELECT 字段列表 FROM 表1,表2 WHERE 条件 ...;显示内连接:
SELECT 字段列表 FROM [INNER] JOIN 表2 ON 连接条件...;
注意事项:为什么叫内连接?
回答:内连接之所以称为"内",是因为它只关心哪些被连接的表中都存在的记录,也就是说,只有当两张表中的连接键完全相等时,才会被选入结果集中。因此他关注的是内部的,共同的,共享的数据部分。
核心知识点:
- 隐式内连接中,满足where 后的条件表达式 为 true 时,才会被查询出来。
- 显示内连接中,on 后的连接条件为 true时,才会被查询出来。
- 无论是显示内连接,还是隐式内连接,表达式的条件都可以有多个,但最后得到的值要么是false要么为true
- 结果只保留条件为true时数据
隐式内连接语法案例:
显式内连接语法案例:
案例一:带inner
案例二:不带inner
外连接查询
左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;
注意:相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
案例1:
上述案例可以看到员工的所有信息与部门信息都被查询出来了,唯独id为3010的员工是没有部门的,所以他的部门信息是null。
案例2: 去掉 outer
所以一般我们使用左连接时,都是用left join ,这样少些一个单词。
右外连接
select 字段列表 from 表1 reght [outer] join 表2 on 条件 ...;
注意:相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
案例1:
可以看到id为9的开发部是没有绑定任何员工的,所以后面的员工信息都为空。
案例2:outer去除效果一样
注意:其实在实际开发者使用一种外连接就可以了,一般使用的较多的是左外连接,但其实看个人习惯即可,因为左外连接与右外连接一样,其实就是把表的摆放位置调转一下就可以了。
自连接查询
自连接查询语法:
select 字段列表 from 表A 别名A join 表B on 条件...;
注意:自连接查询,可以是内连接查询,也可以是外连接查询。
自连接-内连接查询:
错误示例:【查询员工的直属领导】
注意:在使用内连接时,必须给表起别名。
正确示例:【查询员工的直属领导】
注意:内连接查询只能查询出符合条件的数据,不符合条件的数据是无法查出的。
自连接-外连接查询:【查询出员工的全部信息与领导名称,如果没有领导,也需要展示】
联合查询
对于union查询,就是把多次查询的结果合并在一起,形成一个新的查询结果集。
语法如下:select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;
union all 案例:
可以发现,union all 的查询是直接将两条查询语句的结果集直接合并,所以我们可以看见结果集中具有相同的数据。
union 案例:
可以看见,union 的查询就没有重复数据了,这是因为union会在结果集合并之后再对结果集进行去重操作。
注意事项:
1、对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
2、union all 会将全部数据直接进行合并,而 union 会在数据合并之后进行去重操作。
演示注意事项1 --- 案例1:
可以看见如果两个select语句所查询的字段数量不同时,会出现报错。
演示注意事项1 --- 案例2:
1、上述可以发现,这里存在一个坑,在我的数据表中,我的id是int类型,name是varchar类型,他们两个数据格式不一样,但执行sql却未报错,这是什么原因呢?
这是因为mysql进行了隐私转换,那么这里int类型就会被转为varchar类型,因为varchar的取值范围更大。
2、大家有没有发现,当我们使用union 和 union all 时,他们的结果集都一致,这是为什么?这是因为,union的去重是针对于查询出来的结果集中列中的值进行去重的。也就是取唯一值。但如果两个的列的查询出来的值,恰好相同时,union将会去除掉重复的,只保留一个。
演示注意事项1 --- 案例3:
可以看见,我将name与id的顺序进行了调转,也会出现相同的数据。
那么由此可以发现,union的唯一性是这么确定的:去除重复行的前提是结果集中所有列的组合是唯一的【注意这里的组合,组合指的是一行,这一行包含了所有查询出来的字段】
子查询
概念:SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
语法格式:
select * from 表名 where 字段1 = (select 字段2 from 表名)
子查询外部的语句可以是 insert , update , delete , select 的任何一个。
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:where 之后,from 之后,select 之后
标量子查询
子查询返回的结果是单个值(数字,字符串,日期,字符等),这是子查询最简单的形式,这种子查询称为标量子查询。
常用的操作符有:= <> > >= < <= !=
标量子查询案例1:查询指定部门下有哪些员工
标量子查询案例2:查询工资大于 路明非 的员工
列子查询
列子查询 返回的结果是一列(可以是多列),这种子查询称之为列子查询。
常用的操作符有:in , not in , any , some ,all
列子查询案例1:查询多个部门,所有员工的工资
列子查询案例2:查询工资比多个部门所有员工工资都要高的员工信息
列子查询案例3:查询工资比多个部门任意员工工资高的员工信息
注意:这里any 也可以换位 some,结果与any是一样的
行子查询
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用操作符有:= , <> , in , not in
行子查询案例1:查询与路明非相同上级与相同薪资的员工信息
表子查询
子查询返回的结果是多行多列,这种查询称之为表子查询
常用操作符有:in
这里子查询的结果会作为一张临时表存在,相当于做了一个多表查询的操作。