oracle数据库之初步（3）
来源：互联网 发布：i3-2350m编程好用吗 编辑：程序博客网 时间：2024/05/17 22:51
**
子查询
**
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 30B)
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 empwhere 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 10ANY 和集合中的任意一个值比较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 WARDROWNUM的知识点
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;
- oracle数据库之初步（3）
- oracle数据库学习之初步（1）
- oracle数据库学习之初步（2）
- oracle数据库之初步（4）DML
- oracle数据库初步之用户
- oracle数据库初步之约束
- oracle数据库初步之查询
- oracle数据库初步之函数
- oracle数据库初步之操作表
- oracle数据库初步之表空间与数据文件
- oracle数据库初步之操作表中的数据
- 数据库之初步
- oracle数据库的搭建初步
- oracle数据库序列的初步认识
- oracle数据库的并发初步理解
- JavaWeb之数据库部分--JDBC初步
- C++程序设计（3）之程序设计初步
- oracle初步优化（一）
- Android 四大组件之BroadcastReceiver
- ZeroMemory、memset 和 “={0}” 三者用于清零操作的区别
- 设计模式相关
- 数据结构实验之二叉树的建立与遍历
- for...in和for...of循环以及forEach方法
- oracle数据库之初步（3）
- 小甲鱼数据结构教学视频（百度网盘）
- 递归求各种组合情况
- log4j
- eclipse rcp程序No application id has been found错误
- NOIP2016Day2T1组合数问题洛谷p2822解题报告
- 用微信小程序开店之五——小程序组件1：“视图容器”
- Nginx 功能展示实验
- 神经网络中epoch， iteration， batchsize相关理解和说明
