在from字句中使用子查询
示例:如何显示高自己于部门平均工资的员工的信息
思路:先查出每个部门的平均工资
select deptno ,avg(sal) mysal from emp group by deptno;
然后在把查出的平均工资当作一个表
SQL> select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(selectdeptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptnoand a2.sal>a1.mysal;
要点:当在from字句中使用子查询时,该子查询会呗作为一个视图来对待,因此叫作内嵌视图,当在from字句中使用子查询时,必须给子查询指定别名,如上面下划线部分,别名为a1.(给表取别名不能加as给列取别名可以加as)
分页查询
示例:按雇员的id号升序取出,显示第十条到第六条之间的
SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 whererownum<=10) where rn>=6;
其中:1.显示rownum[oracle分配的]2.指定查询列,只需修改最里层的子查询。3.如何排序,只需修改最里层的子查询。里面rownum自能出现一次如select a1.*,rownum rn from (select * from emp) a1 whererownum<=10 and rownum<=6;会出错。
分页分为三种
1.根据rowid来分
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,ridfrom (select rowid rid,cid from t_xiaoxi order by cid desc) whererownum<10000)where rn>9980)order by cid desc;
执行时间0.03秒
2.按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxit)where rownum<10000) where rn>9980;
执行时间1.01秒
3.按rownum来分
select * from(select t.*,rownum rn from (selec * from t_xiaoxi order by ciddesc) t where rownum<10000) where rn>9800;
z执行时间0.1秒
其中t_xiaoxi为表名称,cid为表的关键字段,取按cid降序排序后的第9981-9999条记录,t_xiaoxi表有700000多条记录
个人感觉1的效率最好,3次之,2最差
用查询结果创建新表
这个命令是一种快捷的建表方法
create table myemp (id,ename,sal) as select empno,ename,sal from emp;
合并查询
有时在集合操作符号实践应用中,为了合并多个select语句的结果,可以使用union,union all,intersect,minus
1).union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。
示例:
单独查询
SQL> select ename,sal,job from emp where sal>2500;
ENAME SAL JOB
---------- ---------- ---------
JONES 2975 MANAGER
BLAKE 2850 MANAGER
SCOTT 3000 ANALYST
KING 5000 PRESIDENT
FORD 3000 ANALYST
SQL> select ename,sal,job from emp where job='MANAGER';
ENAME SAL JOB
---------- ---------- ---------
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
合并查询
SQL> select ename,sal,job from emp where sal>2500 union
2 select ename,sal,job from emp where job='MANAGER';
ENAME SAL JOB
---------- ---------- ---------
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
FORD 3000 ANALYST
JONES 2975 MANAGER
KING 5000 PRESIDENT
SCOTT 3000 ANALYST
已选择6行。
2).union all
该操作赋予union相似,但是它不会取消重复行,而且不会排序。
示例:
select ename,sal,job from emp where sal>2500
union all select ename,sal,job from emp where job='manager';
该操作符用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中重复行。
3).intersect
使用该操作符用于取得两个结果集的交集
select ename,sal,job from emp where sal>2500 intersect
select ename,sal,job from emp where job='manager';
4).munus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus