目录
之前的学习其实都是基于一张表的查询,但是在实际问题中可能会有很多张表。所以情况会更加的复杂,这个时候就需要去学习多表查询,复合查询了。但是MySQL的复合查询是建立在单表查询的基础之上的,有了单表查询的基础,学习符合查询会更加得心应手
多表查询
查询的from语句后面带着两个表名,相当于做笛卡尔积
笛卡尔积的话,势必会存在一些不合理的数据,我们需要把他筛选出去
比如说有这样的表结构
我需要查询雇员名字,雇员工资以及他们所在部门的名字。
由于雇员名字和雇员工资来自emp表,而部门名字来自dept表,因此要复合查询
由于是笛卡尔积,我们会发现有很多地方是没有意义的,这个时候需要设置一个筛选条件:员工所在的部门号要和对应的部门表的部门号相等
由于需要查询的是雇员名字,雇员工资以及所在部门的名字,因此带上select对应的筛选条件
这样就完成了从两张表中查到对应的数据,我们在查找的过程中需要注意;
1 多表查询是笛卡尔积
2 对于重复的没有意义的数据设置where筛选条件去除。一般是主键和外键对应
3 最后可以select找出对应列
自连接
是什么:
表自己和自己做笛卡尔积,自己和自己连接
应用场景:
1. 嵌套层次结构:有时候,数据可能以嵌套的方式存储在同一个表中。例如,在文章评论表中,每个评论可能会有多个回复或子评论。为了分析数据并了解回复与主评论之间的关系,需要使用自连接操作。
2. 数据分析:当执行数据分析任务时,自连接也是一个非常有用的工具。例如,在销售订单表中,可能需要查找所有涉及某一客户的订单,并计算他们的总金额。这通常可以通过连接同一张表来完成,使用聚合函数,如 SUM() 来进行汇总。
3. 自己与自己做比较:有些情况下需求要把表中记录在某个维度上进行比较、聚合,又不想新建立一个对应该维度的表,那么就可以使用自连接实现。例如,在一个学生成绩表中,可能需要找到某个学生和其他所有学生的平均成绩进行比较,这就可以使用自连接来连接同一张学生成绩表。
需要注意的是,自连接虽然功能强大,但如果使用不当可能会影响查询性能。
此时我们需要根据具体情况来评估性能,并根据需要进行优化调整。
示例
显示员工FORD的上级领导的编号和姓名
这个查询涉及到了FORD,和FORD的上级领导,这是存在一个表中的,我们分析的时候可以利用FORD的领导的编号找到对应的上级领导的信息,再连接
涉及到两张表需要取别名
相当于从员工和领导的角度来看待这一张表
也可以使用子查询来完成以上的工作
子查询
有时候查询的条件比较复杂,在一个select语句中需要借助另外一个select语句来完成查询的工作,这样就会相应的产生子查询
本质上就是一个嵌套查询
比方说:如果显示工资最高的员工的名字和工作岗位
需要先利用聚合查询查找出最高的工资,再来查找
括号内的就是子查询语句
1 单行子查询
子查询语句的结果返回的是单行结果
先找到对应的员工名字是SMITH对应的部门号,再把这个作为条件查询对应的员工的信息。
2 多行子查询
返回多行记录的子查询
有时候子查询的结果记录不止一行,就可以用多行子查询。多行子查询一般是搭配in all any等关键字进行查找的
in
比如说查找和10号部门的工作岗位相同的员工的名字,岗位,工资,部门号,但是不包括10号自己
因为和10号部门的工作岗位相同的工作有很多,只要符合一条就满足,就可以用in
all
比如说显示工资比30的所有员工的工资高的员工的姓名,工资和部门号
any
any的话,和all不同,只要满足大于任意一个,理解all如果是最大或者最小的话,any则反之
并且他查询的结果是包含自己的
比如说显示工资比30号部门的人以员工的工资高的员工的姓名,工资和部门号。
这里显示的结果就包含了30号自己
3 多列子查询
查询返回多个列数据的子查询语句
比如说查询SMITH的部门和岗位完全相同的所有雇员,不包含SMITH本人
这里就涉及到了job和deptno两个列
4 多行多列
其实就是嵌套的是一个表
这个处理的逻辑就是from后面可以跟上多个表,因此我们需要把一个子查询的表当做一个临时表来使用进行查询
mysql处理的时候,其实不管是多表还是一张表,最终都会被看待成一张表进行查询显示的。
不管是进行操作的对象还是查询出来的结果都是一张表。那么对我们可以在查询之前先用select语句查询出来一张表的结果,再对多张表进行整合
mysql研究的是表和表的关系,不管是各种真实存在的表还是说select查询出来的结果都是可以作为表来查找的
比如说查找每个高于自己部门平均工资的员工的姓名,部门,工资和平均工资
合并查询
前提:表的结构需要一样,否则没有意义(本质上是笛卡尔积)
有时候两个毫不相关的sql语句如果想要进行横向的整合的话,使用合并查询,把多个查询结果合并成一个查询结果,其实就是去做笛卡尔积
用来取得两个结果集的并集
本质上就是把多个表合并成为一个表,会自动进行去重的操作
比如说我查询工资大于两千或者职位等于MANAGER的人的信息
就要对两个查询结果进行合并:1 工资大于2000 2 职位等于MANAGER
都是来自同一张表就保证了表结构的一致性
这个查询语句其实相当于