子查询
子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,为了让读者更加清楚子查询的概念。
例: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(+);