|
子查询
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、练习题截图：
 
|
|
|
