Oracle基础知识整理：C站下载链接
文章目录
6 Oracle 表的管理
6.5 oracle表的管理 -表查询（重点）
6.5.4 Oracle表复杂查询 -子查询
·什么是子查询
·单行子查询
·多行子查询
·多列子查询
·在form子句中使用子查询
.分页查询
6 Oracle 表的管理
6.5 oracle表的管理 -表查询（重点）
6.5.4 Oracle表复杂查询 -子查询
·什么是子查询
子查询是指嵌入在其它sql语句中的select语句，也叫嵌套查询
·单行子查询
**单行子查询:**是指返回一行数据的子查询语句
请思考：如何显示与SMITH同一部门的所有员工？
select ename from emp where deptno= (select deptno from emp where ename='SMITH');
·数据库在执行sql语句的时候，是从左到右， 扫描的时候是从右到左
·多行子查询
**多行子查询:**指返回多行数据的子查询
请思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select * from emp where job in(select distinct job from emp where deptno=10);
·在多行子查询中使用all操作符
请思考：如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
方法一：
select ename,sal,deptno from emp where sal >all(select sal from emp where deptno=30 );
select ename,sal,deptno from emp where sal >all(select sal from emp where deptno=30 );
方法二：（执行效率高）
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
·在多行子查询中使用any操作符
请思考：如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
方法一：
select ename,sal,deptno from emp where sal > any (select sal from emp where deptno=30 );
方法二：（执行效率高）
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
·多列子查询
**单行子查询:**是指子查询返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句
请思考：如何查询与SMITH的部门和岗位完全相同的所有雇员
方法一：
select * from emp where deptno=(select deptno from emp where ename='SMITH')and job=(select job from emp where ename= 'SMITH');
方法二：（效率高）
select * from emp where (deptno,job) =(select deptno,job from emp where ename='SMITH');
·在form子句中使用子查询
请思考：如何显示高于自己部门平均工资的员工的信息
select 1.ename,a1.deptno,a1.sal,a2.mysal from emp a1, (select deptno,avg(sal) mysal from emp group by deptno ) a2 where a1.deptno=a2.deptno and a1.sal>a2.mysal order by a1.deptno;
实现步骤：先查询各个部门的编号和平均工资（平均工资起个别名）作为一个新表，起个别名，与emp表进行表连接查询。
·总结：
这里需要说明的当在form子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫做内嵌视图，当在form字句中使用子查询时，必须给子查询指定别名。
给表取别名的时候不能加as；
给列取别名的时候可以加as，也可以不加as；
.分页查询
·Oracle的分页一共有3种方式：rownum, ROWID
1.rownum分页
执行时间为0.1秒，速度比ROWID次之
select a1.*,rownum rn from (select * from emp) a1;
sql语句解析：
(select * from emp) 内嵌视图 取个别名a1
a1.* 表示把子查询的信息再读取出来
rownum 是Oracle给它分配的
rn 相当于Oracle给每一行分配的一个行号
（这个行号是变化的，而不是固定的）
（rownum的别名）
显示前十条数据
select al.*,rownum rn from(select * from emp ) a1 where rownum<=10;
显示第六条到第十条数据（用二分机制）
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6;
·几个查询变化
&指定查询列，只需修改最里边的子查询
select * from (select a1.*,rownum rn from (select enamel,sale from emp) a1 where rownum<=10) where rn>=6;
如何排序
对某薪水列排序后取第6条到第10条
select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=10) where rn>=6;
显示第4条到第9条的记录
select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal desc) a1 where rownum<=9) where rn>=4;
**2.根据ROWID来分 **
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
执行时间0.03秒，速度快，较复杂
3.按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t)where rk<10000 and rk>9980;
执行时间为1.01秒，速度最慢
·用查询结果创建新表
这个命令是一种快捷的建表方法
create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
