子查询与结果集归并

子查询

概念：查询中嵌套查询。

分为：单行单列 select 字段列表 from 表 where 字段名 = 子查询;

        多行单列         `select 字段列表 from 表 where 字段名  in 子查询;`

        多行多列         `select 字段列表 from （子查询）where   条件;`

单行单列嵌套

例1：查询班级中成绩高于叶淑华的学生的信息：

select * from stus where score>(select score from stus where name =
"叶淑华");

多行单列嵌套

例2：查询财务部和销售部的所有员工信息：

select * from emp where dep_id in (select id from dept where dept_name
= "财务部" or dept_name = "销售部")

IN 操作符用于匹配一组值，其后也可以接一个SELECT子句，从而匹配子查询得到的一组值。

多行多列嵌套

例3：查询年龄大于20的员工信息和部门信息

思路：先列出年龄大于20岁的员工的所有信息，作为虚拟表与dep_id进行内连接

select * from (select * from emp where age>20) t1,dept where t1.dep_id
=  dept.id;

t1是别名，select * from emp where age>20作为虚拟表。

以上的嵌套查询中 子查询都没有连接操作

而按子查询连接与否又可以分类为：不相关嵌套查询和相关嵌套查询

不相关嵌套查询

子查询不依赖父查询 子查询内无连接（子查询可以单独执行）

image-b6edf8f9

注意点：

image-3e6821bc

判断好用=和用in的场景

上面的三个例子都是不相关嵌套查询

相关嵌套查询

换下顺序而已 先把俩表联合再找符合条件的

相关嵌套查询就是 子查询引用了某张表且这张表也在子查询外部被使用到

本质是将连接放在子查询里面

image-8a5a28b1

相关嵌套查询的子查询是无法单独进行执行的

也就是说它依赖于父查询

（下面这个例子好像有点问题 不用管含义 知道要用别名把自己区分开来就行）

image-69e2db25

要用好嵌套查询要学会使用别名

比如这里 把自己表里的数据和自己表里选出来的数据去做筛选（像自连接的思路）

如果不使用别名

image-77bd2223

它会报错 因为无法识别两个sc.sno

思路就是用t2这个临时表去存放求出来的平均分 把原表取别名为t1

这样一来两个sc就得以区分

还有一个派生表？？什么意思？

select sno, cno from sc, (select sno, avg(grade) from sc group by sno)
as avg_sc(avg_sno, avg_grade) where sc.sno = avg_sc.avg_sno and grade >
avg_sc.avg_grade

子查询的位置

前面更多用在了from后面

其实它能用在很多地方

select后面

from后面

where后面

where子句中的子查询

比如 找出比最低工资高的员工的姓名和工资

实现思路肯定是

（1）找到最低工资是多少

select min(sal) from emp;  //3000

（2）找出大于3000的

select ename,sal from emp where sal>3000;

总不可能每次都分两步查询吧

合成一句

select ename,sal from emp where sal>(select min(sal) from emp)
from子句中的子查询

from后面的子查询可以把子查询的查询结果当做一张临时表来看待

比如 找出每个岗位的平均工资的薪资等级

思路：

（1）找出每个岗位的平均工资（按岗位分组）

select job,avg(sal) from emp group by job
image-5df88b55

（2）查看薪资等级

select * from salgrade;
image-04fe4caf

（3）这两个表应该是有关联的 把岗位平均工资表当成t表 薪资等级表当成s表

t表的平均工资字段大于某等级的LOSAL 小于它的HISAL就是处于这个等级

select t.*,s.grade
from (select job,avg(sal) as 'avgsal' from emp group by job) as 't'
join salgrade as 's'
on t.avgsal between s.Losal and Hisal

把查询结果当成一个表（其实不存在）注意的是一定还要给avg(sal)取别名 否则会当成分组

select后面的子查询

比较别扭

例题：

找出每个员工的部门名称 要求显示员工名、部门名

image-bf79e297

select e.ename,e.deptno,(select d.dname from dept d where e.deptno=d.deptno)as dname

from emp e

如果子查询语句在select后面的话 它的结果只能是一条记录

因为每个字段都是用 , 隔开 它相当于就是一个字段

很绕很麻烦 看得懂就行 用的很少

其实同一个要求可以用很多种写法

select emp.ename,emp.deptno,dept.dname from emp join dept on emp.deptno=dept.deptno
join后的子查询

找出各科第一名

select a.name,a.course,a.score
from stu_score a
inner join
(select course,MAX(score) as score
from stu_score
group by course)
as b
On
    a.course=b.course
and
    a.score=b.score

结果集的归并处理

集合运算的基本格式是：

      集合查询1

      <集合运算>

      集合查询2

      [order by ...]

使用运算符Union(集合并)、Intersect(集合交)、Except(集合差)可以将多个结果处理成一个结果集。

UNION 并

image-17c328b6 image-c692047d

查询工作岗位是MANAGER和SALESMAN的员工

select ename,job from emp where job='MANAGER' or job='SALESMAN'

或者

select ename,job from emp where job in('MANAGER','SALESMAN')

再或者用union

select ename,job from emp where job='MANAGER'

union

select ename,job from emp where job='SALESMAN'

一般来说 三种效率一种比一种高 or<in<union

因为对于表连接 每连接一次新表 则匹配的次数满足笛卡尔积 成倍的翻

union可以减少匹配的次数 在减少匹配次数的情况下 还能完成两个结果集的拼接

比如a连接b连接c

abc各10条记录 用a的每条记录与b每条记录依次扫描再与c的每条…… 要匹配1000次

用union的话

a连接b——10*10=100次

a连接c——10*10=100次

合并就是100+100=200次

相较于1000要小很多 这还只是三个表都只有10条记录的情况下

             简单理解就是乘法变加法

union(并集)集合运算可以将多个查询结果集合并成一个结果集。union(隐含distinct，去除重复)、union all。

也不是什么时候都能用的 使用union时要求两个结果集的列数相同

mysql里只要求数量 oracle中还要求数据类型也一样

Intersect交

image-e69a1aaa image-b978feda image-62fe6d0d

删除两个集合中的重复行，返回只有在两个集合中都出现的行

EXCEPT差

image-93b9cd72 image-4b3f8d63

先将其中完全重复的数据行删除，再返回只在第一个集合中出现，在第二个集合中不出现的所有行。

优先级

intersect运算比union和except运算的优先级高，而union和except的优先级相等

image-83077cf7

⬅️ 13-多表连接查询 🏠 00-数据库 ➡️ 15-别名的妙用与limit分页