Oracle(第五天)

子查询

　1、单行子查询：

　　  例如：select * from emp where sal>(select sal from emp where ename = 'ALLEN');

　2、多行子查询：any all in

　　 　　大于any就是大于最小值

　　select * from emp where sal>any(select sal from emp where deptno=10);

　　select * from emp where sal>(select min(sal) from emp where deptno=10);

　　　　大于all就是大于最大值

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

　　select * from emp where sal>(select max(sal) from emp where deptno=10);

　　　　小于any就是小于最大值

　　select * from emp where sal<any(select sal from emp where deptno=10);

　　select * from emp where sal<(select max(sal) from emp where deptno=10);

　　　　小于all就是小于最小值

　　select * from emp where sal<all(select sal from emp where deptno=10);

　　select * from emp where sal<(select min(sal) from emp where deptno=10);

in (用法举例如下)

3、多列子查询：（了解）

　　select deptno , job from emp where deptno != 10;

　　select * from emp where (deptno,job) in (select deptno , job from emp where deptno != 10);

4、相关子查询：

　　每执行一次主查询，就要执行一次子查询。

　　查询各个部门工资比平均工资高的人的信息

select * from emp where deptno = 10 and sal > (select avg(sal) from emp where deptno=10);

select * from emp where deptno = 20 and sal > (select avg(sal) from emp where deptno=20);

select * from emp where deptno = 30 and sal > (select avg(sal) from emp where deptno=30);

select * from emp e1 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);

 

5、练习题截图：