1.all用法
select * from emp where sal> all
(select sal from emp where deptno='20') //括号里返回多条记录必需要用all
2.any用法
select * from emp where sal> any
(select sal from emp where deptno='20') //查找出工资比部门20的任意一个员工的工资高的员工的姓名、工资和部门号
3.能返回两个结果集
select * from emp where (deptno,job)=(select deptno,job from where ename='SMITH') //oracle能返回两个结果集
4.把一个结果集当表
select a2.* from emp a2,(select avg(sal) mysal,deptno from emp group by deptno) a1
where a2.deptno=a1.deptno and a2.sal>a1.mysal
5.查询结果创建表
create table mytable (id,name,sal,job,deptno)
as select empno,ename,sal,job,deptno from emp
6.同时更新几个字段
update emp set (job,sal,comm)=(select job,sal,comm
from emp where ename='SMITH') where ename='SCOTT'