SQL> select ename,sal,deptno from emp wheresal> any (select sal from empwhere deptno=30);
ENAME SAL DEPTNO
KING 5000.00 10
FORD 3000.00 20
多列子查询
--- where后的条件是一一对应的，实际是两个条件的and连接
| Eg:查询出与'SMITH'部门和岗位一样的员工信息 |
| SQL> selectename,job,deptno from emp where (deptno,job)=(select deptno,job from emp whereename='SMITH'); ENAME JOB DEPTNO ADAMS CLERK 20 |
--- 在from子句中使用子查询 ---- 重要内容
From中的子查询被认定为一个内嵌试图，并且应该取别名
| Eg:显示高于自己部门平均工资的员工信息 |
| SQL> selectename,sal,mysal,a1.deptno from emp a1,(select deptno,avg(sal) mysal from emp group bydeptno) a2 wherea1.deptno=a2.deptno anda1.sal>a2.mysal; ENAME SAL MYSAL DEPTNO ALLEN 1600.00 1566.66666 30 JONES 2975.00 2175 20 |
分页查询 ----- 非常重要
Rownum分页a. (select * from emp) 建立内嵌试图
b. 显示rownum。(这里的rownum是oracle自带分配的)
| Eg |
SQL> select ename,job,sal,rownum rn from(select * from emp) a1; ENAME JOB SAL RN SMITH CLERK 800.00 1 ALLEN SALESMAN 1600.00 2 WARD SALESMAN 1250.00 3 |
c. 筛选出rn为6-10的数据 --(非常重要)
d. Oracle采用二分法筛选，所以需要先筛选出1-10.，在筛选出6-10
e. 如需改变查询列时，只需改变最里层的select的就可以了
f. 如需排序，也只需修改最里层的select视图就可以了
| Eg: 在第一次的筛选中要用rownum，第二次要用别名rn |
| SQL> select * from (select a1.*,rownum rn from (selectename,job,sal from emp) a1 where rownum<=10) wherern>=6; ENAME JOB SAL RN BLAKE MANAGER 2850.00 6 CLARK MANAGER 2450.00 7 SCOTT ANALYST 3000.00 8 |
利用查询结果来创建新表
| Eg:创建一个部门平均工资表从emp表中生成 |
| SQL> create table avgsal (deptno,avgsal) as select deptno,avg(sal) fromemp group by deptno; Table created SQL> select * from avgsal; DEPTNO AVGSAL 30 1566.66666 20 2175 10 2916.66666 |
合并查询 ---- 比较麻烦，但效率较高
Ag：在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号：union all,union,intersect,minus
a. Union
Union用于取得两个结果集的合集
| Eg:查询sal大于2500并且部门为20的员工，实际效果等同于and |
| SQL> select ename,deptno,sal from emp wheresal>2500 2 union select ename,deptno,sal from emp where deptno=20; ENAME DEPTNO SAL ADAMS 20 1100.00 BLAKE 30 2850.00 FORD 20 3000.00 JONES 20 2975.00 KING 10 5000.00 |
b. Unionall
注： union all与union用法相同，但他的效果是缺两个集合的全集(不会合并相同的)
c. Intersect
Intersect是用于取两个集合的交集
| Eg: 对于分页查询同样可以采用这种方法 |
| SQL> select * from (select ename,job,sal,rownumrn from emp) where rownum<=10 2 intersect select * from (selectename,job,sal,rownum rn from emp) where rn>=6; ENAME JOB SAL RN BLAKE MANAGER 2850.00 6 CLARK MANAGER 2450.00 7 KING PRESIDENT 5000.00 9 SCOTT ANALYST 3000.00 8 TURNER SALESMAN 1500.00 10 |
d. Minus
Minus是取两个集合的差集(即取A集合减去与B集合重复的部分)
union all, union,intersect,minus
1. A minus B = (1,2)
2. A union B= (1,2,3,4,5)
3. A uniomall B = (1,2,3,3,4,5)
4. A intersect B = (3)