**
子查询
**
1 为什么要有子查询
查询工资比scott高的 员工信息
select sal from emp
where ename = 'SCOTT'
select * from emp
where sal > 3000
====把2步合成一步
select *
from emp
where sal > (select sal from emp
where ename = ‘SCOTT’)
===主查询和子查询
====子查询的本质 多个select语言的嵌套
2 子查询知识体系搭建
1 合理的书写风格
2 子查询外面的()不要忘记
3 子查询和主查询可以查询的是同一张表,也可以不是同一张表,
只要子查询返回的结果,主查询可以用即可...
4 在什么地方放置子查询
select a , b, c ---OK, 只能存放单行子查询,不能是多行子查询
from tab1 ---OK 重点..
下列所示的col是列的意思
where col in (em1, em2) ----OK
col between a1 and a2
col > 222
col > ()
group by ... ---不可以
having ..... ---可以
order by .. ---不可以
5 子查询的分类
单行操作符对应单行子查询，多行操作符对应多行子查询。
按照子查询返回的条目数,分为: 单行子查询和多行子查询
–ppt上的例子
多行子查询只能使用多行比较操作符(in any all)
–eg 单行例子ppt例子
–eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法
–eg 查询薪水 比30号部门 任意一个员工薪高的员工信息
-eg 查询薪水 比30号部门 所有员工 高的员工信息
子查询按照执行的顺序
一般性子查询 相关子查询
6 子查询遇见NULL
7 一般情况下,子查询返回的是一个集合..子查询不排序....Top-N问题除外
=======>oracle分页
解释3: –eg 查询部门名称是SALES的员工信息 2种方法
A)
select *
from emp
where DEPTNO = (select deptno from dept where dname=’SALES’) –2次检索
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----------- --------- ---------- -------------- ------ ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
B)
select e.*
from emp e, dept d
where e.deptno = d.deptno and d.dname=’SALES’
=========>sql优化
1)select * /abc 2) where and and and ....<---- 3)having where ...
4) 是字查询的效率高还是多表查询的效率高 多表查询效率高...一次检索
解释4-1:—OK, 只能存放单行子查询,不能是多行子查询
select ename, empno, (select deptno from emp) AA
from emp
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
已写入 file afiedt.buf
1 select ename, empno, (select deptno from emp where EMPNO = 7369) AA ,sysdate
2* from emp
SQL> /
ENAME EMPNO AA SYSDATE
----------- ---------- ---------- --------------
SMITH 7369 20 08-10月-14
ALLEN 7499 20 08-10月-14
WARD 7521 20 08-10月-14
JONES 7566 20 08-10月-14
MARTIN 7654 20 08-10月-14
BLAKE 7698 20 08-10月-14
CLARK 7782 20 08-10月-14
SCOTT 7788 20 08-10月-14
KING 7839 20 08-10月-14
TURNER 7844 20 08-10月-14
ADAMS 7876 20 08-10月-14
JAMES 7900 20 08-10月-14
FORD 7902 20 08-10月-14
MILLER 7934 20 08-10月-14
已选择14行。
解释4-2 from tab1 —OK 重点..
查询员工的姓名和薪水 (考试题，这是一个填空题)
select *
from emp ----集合.....
----------
select *
from ( select ename, sal from emp)
ENAME SAL
----------- ------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
解释5-1 单行子查询
查询 和141号工种一样的,并且比143号员工薪水高的 员工信息
查询 薪水最低的 员工信息
查询 每部门编号和部门最小工资,并且这个部门最低工资大于50号部门的最低工资
解释5-2 多行子查询
多行子查询只能使用多行比较操作符(in any all)
--eg 单行例子ppt例子
--eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息
2种方法
--eg 查询薪水 比30号部门 任意一个员工薪高的员工信息
-eg 查询薪水 比30号部门 所有员工 高的员工信息
-eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法
例子：查询部门名字是 'SALES' 或者 'ACCOUNTING'的所有员工信息
select * from emp
where deptno in
(select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING')
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
ANY 和集合中的任意一个值比较
ALL 和集合中的所有值比较
--eg 查询薪水 比30号部门 任意一个员工薪高的员工信息 大于集合中的最小值 any
--eg 查询薪水 比30号部门 所有员工 高的员工信息 大于集合中的最大值 all
单行操作符对应单行子查询，多行操作符对应多行子查询。
select * from emp
where sal > all(select sal from emp where deptno = 30)
或者
select * from emp
where sal > (select max(sal) from emp where deptno = 30)
一个错误的示例：
1 select * from emp
2* where sal > (select sal from emp where deptno = 30)
SQL> /
where sal > (select sal from emp where deptno = 30)
*
第 2 行出现错误:
ORA-01427: 单行子查询返回多个行
1 select * from emp
2* where sal > (select max(sal) from emp where deptno = 30)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
解释6 - 查询不是经理的员工信息
—- 查询是经理的员工信息
select * from emp
where empno in (经理的集合)
select * from emp
where empno in (select mgr from emp)
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
已选择6行。
select * from emp
where empno not in (select mgr from emp)
特别注意，因为子查询返回的表中含有null 捣蛋鬼，所以不能正确的搜索，可参考oracle数据库之初步（1）
select * from emp
where empno not in (select mgr from emp where mgr is not null)
1 select * from emp
2* where empno not in (select mgr from emp where mgr is not null)
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
**
习题关于ROWNUM的用法
**
找到员工表中工资最高的前三名
求不排序的前3条
1 select rownum, empno, ename from emp
2* where rownum <=3
SQL> /
ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
ROWNUM的知识点
1 ROWNUM按照oracle的默认机制生成,
2 rownum 只能使用<= < 号,,不能使用 > >=
=======================rownum的实现机制有关系
rownum表示,返回的结果集的行号(是一个属性,固化到一行只中,不会因为你排序,而发生变化)..
没有第一行,就没有第二行;
没有第二行,就没有第三行
Top-N
select rownum, empno, ename, sal
from (select empno, ename , sal
from emp
order by sal desc)
where rownum<=3
使用这种方式就可以正确的找到员工工资的前三名，即在子查询中对员工工资降序排列生成新的表，在主查询中对子查询生成的表进行rownum固化排序，再利用where子句过滤即可。
下图是rownum的实现机制原理图
**
分页机制 5=< x<=8
**
查询员工的工资排在5-8位的员工信息
=======错误
SQL> ed
已写入 file afiedt.buf
1 select rownum, empno, ename, sal
2 from (select empno, ename , sal
3 from emp
4 order by sal desc)
5* where rownum<=8 and rownum>=5
SQL> /
未选定行
SQL>
select rownum r, empno, ename, sal
from (select empno, ename , sal
from emp
order by sal desc)
where rownum<=8 **B**
========================================
m=< x<= n
select r, empno, ename, sal
from
(
select rownum r, empno, ename, sal
from (select empno, ename , sal
from emp
order by sal desc
)
where rownum<=8
)
where r>=5
R EMPNO ENAME SAL
---------- ---------- ---------- ----------
5 7698 BLAKE 2850
6 7782 CLARK 2450
7 7499 ALLEN 1600
8 7844 TURNER 1500
======>分页 内层排序 外层选
=======oracle分页思想总结
（内层排序 外层选，需要三层查询）
内：排序。
中：使用rownum选择前n条；并给rownum指定一个**别名**，以供最外层过滤使用。
外：去掉前m条结果。
习题2: 找到员工表中薪水大于本部门平均薪水的员工
员工表 本部门平均薪水
思路1: 查员工的薪水 本部门平均薪水
本部门关系
要求薪水,需要求部门的薪水,要对部门分组 ====>分组s
select deptno, avg(sal)
from emp
group by deptno 部门薪水表
思路2 查找员工表 和 部门薪水表 ====> 多表查询
思路3 :等值连接条件
方法1 其中d是部门平均薪水表
select e.empno, e.ename, e.sal, d.avgsal
from emp e,
(select deptno, avg(sal) avgsal
from emp
group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal
考察点: 分组 多表查询 等值连接条件 别名 分组函数
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7566 JONES 2975 2175
7698 BLAKE 2850 1566.66667
7788 SCOTT 3000 2175
7839 KING 5000 2916.66667
7902 FORD 3000 2175
已选择6行。
方法2 相关子查询: 主查询的参数,让子查询用…一般是通过别名技术
一般子查询: 子查询的结果,被主查询使用
select avg(sal) from emp where deptno = 10
返回的是10号部门的平均工资表，，是单行
select empno, ename, sal, (select avg(sal) from emp where deptno =10) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno = 10)
求本部门的平均薪水, ====>
===========================
select empno, ename, sal, (select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where sal > (select avg(sal) from emp where deptno = e.deptno)
求本部门的平均薪水, ====>
select avg(sal) from emp where deptno = 10
EMPNO ENAME SAL AVGSAL
---------- ---------- ---------- ----------
7499 ALLEN 1600 1566.66667
7566 JONES 2975 2175
7698 BLAKE 2850 1566.66667
7788 SCOTT 3000 2175
7839 KING 5000 2916.66667
7902 FORD 3000 2175
集合运算
讲集合运算,实质上是讲集合运算的操作符
–查询部门号是10 和 20的员工信息
方法1
select * from emp where deptno in (10, 20);
方法2
select * from emp where deptno=10 or deptno=20;
方法3
select * from emp where deptno = 10
union
select * from emp where deptno = 20;
932
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
