SQL 子查询

子查询
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，为了让读者更加清楚子查询的概念。

例：select * from emp where sal=(select min(sal) from emp);查询最低工资员工信息。
子查询可以返回的数据类型一共分为四种：
**单行单列:返回的是一个具体列的内容，可以理解为一个单值数据；
单行多列：返回一行数据中多个列的内容；
多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
多行多列：查询返回的结果是一张临时
一个查询语句内部可以定义多个子查询；子查询一般在WHERE、FROM、HAVING子句之中出现较多
WHERE子句：此时子查询返回的结果一般都是单行单列、单行多列、多行
单列；
HAVING子句：此时子查询返回的都是单行单列数据，同时为了使用统计函
数（组函数）**操作；
FROM子句：此时子查询返回的结果一般都是多行多列，可以按照一张数据
表（临时表）的形式操作

例：查询出基本工资比ALLEN低的全部雇员信息
select * from emp where sal< (select sal from emp where ename=‘ALLEN’);

查询基本工资高于公司平均薪金的全部雇员信息
select * from emp where sal>(select avg(sal)from emp);

查找出与ALLEN从事同一工作，并且基本工资高于雇员编号为7521的全部雇员信息
select * from emp where sal>(select sal from emp where empno=7521)
and job=(select job from emp where ename=‘ALLEN’);

查询与SCOTT从事同一工作且工资相同的雇员信息
select * from emp where (job,SAL)=(select job,SAL from emp WHERE ename=‘SCOTT’)
AND ENAME NOT IN(‘SCOTT’);
查询与雇员7566从事同一工作且领导相同的全部雇员信息
select * from emp where (job,mgr)=(select job,mgr from emp where
empno=7566 )and empno<>7566;
查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（不包括ALLEN）
SELECT * FROM EMP WHERE(job,to_char(hiredate,‘yyyy’))=(select job,to_char(hiredate,‘yyyy’) from emp
where ename=‘ALLEN’)AND ENAME <>‘ALLEN’;
别名 只存在select后或from后
0155 0060 ora600系统内核错误
多行子查询：常使用 in ，any ， all
WHERE子句可以判断单个数值、多个数值；使用IN、ANY、ALL可以处理多行单列子查询；利用EXISTS()可以判断查询结果是否为null
查询出与每个部门中最低工资相同的全部雇员信息
select * from emp where sal in(select min(sal) from emp group by deptno);
SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO;查询结果为单列数值
SELECT deptno，MIN(SAL) FROM EMP GROUP BY DEPTNO;查询结果为双列。
select * from emp where sal not in(select min(sal) from emp group by deptno);
ANY表示与子查询中返回的每个结果进行比较
ANY在使用中有如下三种使用形式：
=ANY：表示与子查询中的每个元素进行比较，功能与 in 类似（然而<>ANY不等价于NOT IN）；
>ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）；
<ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）
select * from emp where sal=any (select min(sal)
from emp where job=‘MANAGER’ group by deptno);
select * from emp where sal>any (select min(sal)
from emp where job=‘MANAGER’ group by deptno);
select * from emp where sal<any (select min(sal)
from emp where job=‘MANAGER’ group by deptno);
ALL操作符有以下三种用法：
<>ALL：等价于NOT IN（但是=ALL并不等价于IN）；
>ALL：比子查询中最大的值还要大（还包含了>=ALL）；
<ALL：比子查询中最小的值还要小（还包含了<=ALL）
select * from emp where sal>all(select min(sal) from emp where job=‘MANAGER’ group by deptno);

空数据判断
验证exists结构；exists后子语句有结果就执行select，减少回表次数
select * from emp where exists (select *from emp where empno=9999);
select * from emp where exists (select 1 from emp );
not exists：
select * from emp where not exists (select * from emp where empno=7566 );

子查询 —— 范例（HAVING）
在HAVING子句中使用子查询，子查询返回的都是单行单列数据，
同时也可以在HAVING中利用统计函数进行判断。
查询部门编号、雇员人数、平均工资，并且要求这些部门的平均工资高于公司平均薪金
select deptno ,count(empno),avg(sal) from emp
group by deptno
having avg(sal)>(select avg(sal) from emp );

查询出每个部门平均工资最高的部门名称及平均工资
select d.dname,round(avg(sal),1) from emp e,dept d
where d.deptno=e.deptno
group by d.dname
having avg(sal)=(select max(avg(sal)) from emp group by deptno);
查询出每个部门平均工资最高的部门名称及平均工资
select dname,round(avg(sal),1) from emp e,dept d
where e.deptno=d.deptno
group by dname
having avg(sal)=(select max(avg(sal)) from emp group by
deptno );
如果现在子查询返回的数据是多行多列的，那么就可以将其当做一张数据表（同时存在多行多列）来使用，并且这种子查询一般都出现在FROM子句之
FROM子句出现的子查询返回结构为多行多列；利用子查询可以解决多表查询所带来的性能问题
要求查询出每个部门的编号、名称、位置、部门人数、平均工资
select d.deptno,d.dname,d.loc,count,avgsal
from dept d,(select deptno,count(empno) count,round(avg(sal),1) avgsal
from emp group by deptno)a
where a.deptno(+)=d.deptno;

查询出所有在部门“SALES”（销售部）工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、
部门的最高和最低工资
select empno,ename,sal,comm,job,hiredate,maxsal,minsal
from emp e ,(select deptno,min(sal) minsal,max(sal) maxsal
from emp group by deptno)a
where e.deptno=a.deptno(+)
and e.deptno
in (select deptno from dept where dname=‘SALES’);
查询出所有薪金高于公司平均薪金的员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限。
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc
,m.ename,s.grade,count,avgsal,ahire
from emp e,dept d,emp m,salgrade s,
(select deptno,count(empno) count,round(avg(sal),1) avgsal,
round(avg(months_between(sysdate,hiredate)/12),1) ahire from
emp group by deptno)a
where e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal
and a.deptno=d.deptno
and e.sal>(select avg(sal) from emp);

select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,
p.ename,s.grade,a.cundeptno,a.avgsal,a.ahiredate
from emp e,dept d,emp p,salgrade s,
(select deptno,count(ename) cundeptno,avg(sal) avgsal,
round(avg(months_between(sysdate,hiredate)/12）,1) ahiredate from emp
group by deptno) a
where e.deptno=d.deptno
and e.mgr=p.empno(+)
and e.sal between s.losal and s.hisal
and d.deptno=a.deptno
and e.sal>(select avg(sal) from emp );
列出薪金比“ALLEN”或“CLARK”多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名，部门人数
select e.empno,e.ename,e.sal,d.dname,m.ename,a.count
from emp e,emp m,dept d,(select deptno,count(empno) count from emp
group by deptno) a
where e.deptno=d.deptno
and e.mgr=m.empno
and d.deptno=a.deptno
and e.sal >any(select sal from emp where ename in (‘ALLEN’,‘CLARK’));

列出公司各个部门的经理（假设每个部门只有一个经理，job为“MANAGER”）的姓名、薪金、部门名称、部门人数、部门平均工资
select e.ename,e.sal,d.dname,count,avgsal
from emp e,dept d,(select deptno,count(empno) count,round(avg(sal),1)
avgsal from emp group by deptno ) a
where e.deptno=d.deptno(+)
and a.deptno=d.deptno
and job=‘MANAGER’;
子查询 —— 范例（SELEC
SELECT子句中出现子查询的情况一般比较少见，了解即可
查询出公司每个部门的编号、名称、位置、部门人数、平均
select deptno ,dname,loc,
(select count(empno) from emp where deptno=d.deptno group by deptno) count,
(select avg(sal)from emp where deptno=d.deptno group by deptno) avgsal from dept d;
上面select 中d.deptno 与外面deptno表建立了相关子查询
常规语句如下
select d.deptno,d.dname,d.loc,count,avg
from dept d,(select deptno,count(empno) count,round(avg(sal),1) avg
from emp group by deptno) a
where d.deptno=a.deptno(+);