Oracle查询笔记--子查询，exist使用和集合运算符

子查询

子查询(也叫嵌套查询) 多个select嵌套出现,第一次的查询结果可以作为第二次的查询条件 或 表名

单行子查询:子查询只有一条记录

–需求1:求最大工资那个人的姓名和薪水
–1.求最大工资 5000
select max(sal) from emp
–2.求薪资5000的那个人的姓名和薪水
select ename,sal from emp where sal =5000
–3 组合起来
select ename,sal from emp where sal=(select max(sal) from emp);

–需求2:哪些人得工资位于所有人得平均工资之上
–1.求平均工资 2073.21428571429
select avg(sal) from emp
–2.求工资大于平均工资的姓名和薪水
select ename, sal from emp where sal>2073.21428571429
–3.第一次查询的结果作为第二次的查询条件
select ename, sal from emp where sal>（select avg(sal) from emp）
–需求3:查SMITH的上司叫什么

select * from emp
select MGR from emp where ename ='SMITH'
select ename from emp where empno =7902
select ename from emp where empno =(select MGR from emp where ename ='SMITH')

–自关联也可以实现

select e.ename,e.MGR,m.deptno,m.ename from emp e inner join emp m on e.mgr =m.empno

–多行子查询：子查询有多条记录，子查询做虚拟表

– in（1,2,3） 等于其中的任何一个都可以
– =any等于其中的任何一个都可以
– <any（1,2,3） 小于子查询的最大值
– <all（1,2,3） 小于子查询的最小值
– >any（1,2,3） 大于子查询的最小值
– >all（1,2,3） 大于子查询的最大值

–需求：查询公司的所有雇员中，哪些雇员的薪水和部门编号为30里雇员的薪水相同
–部门编号为30的雇员的薪水

select sal from emp where deptno = 30

select * from emp where sal in(select sal from emp where deptno = 30) and deptno !=30

–需求：查询公司的所有雇员中，哪些雇员的薪水大于部门编号为30里雇员的最高薪水

select * from emp where sal >all(select sal from emp where deptno = 30) 

–需求：显示各个部门的信息（编号、名称）和人员数量
–各部门人员数量

select deptno,count(*) from emp group by deptno

–把结果集当成一张表和部门表进行关联

select d.deptno,d.dname,t.num from dept d ,
(select deptno,count(*) as num from emp group by deptno) t
where t.deptno = d.deptno

–需求：显示高于自己部门平均工资的员工信息
–1.求每个部门的平均工资

select * from emp group by deptno

select * from emp e,
(select deptno,avg(sal) as avg_sal from emp group by deptno) g
where e.deptno=g.deptno and e.sal > g.avg_sal

注意 虚拟表的特殊列一般指定别名，便于书写。如（avg(sal)）

exists用法

exists （sql 返回结果集为真）
exists的条件就像一个boolean条件，当能返回结果集则为true，不能返回结果集则为 false

select * from T1 where exists(select 1 from T2 where T1.a=T2.a);

**用途：**当T1数据量小而T2数据量非常大时，T1<<T2 时，该查询效率高
而当T1数据量大而T2数据量非常小时，采用另一种方法效率更高where deptno in

select * from dept where deptno in(select distinct deptno from emp)

–需求：查询包含员工的部门信息
select * from dept where exists (select 1 from emp where emp.deptno = dept.deptno)

集合运算符：将多个查询结果合并为一张临时表

语法：A表查询结果 集合运算符 B表查询结果
union：并集-------------A和B的并集，去掉重复行
union all：并集----------A和B的并集，不去掉重复行
minus：差集-------------A中的结果减去B中的结果
intersect：交集----------A和B的交集
//查询70、90号部门的员工信息

select * from employees 
where department_id in(60,70)
union
select * from employees where department_id in(70,90); //重复数据保留一份