oracle 数据库4——高级查询

1. 常见高级查询有哪些？

高级查询	细分
子查询	子查询在 where 子句
	子查询在 having 子句
	子查询在 from 子句
	子查询在 select 子句
分页查询	ROWNUM
	使用子查询进行分页
	分页与 order by
DECODE 函数	DECODE 函数基本语法
	DECODE 函数在分组查询中的应用
排序函数	ROW_NUMBER /RANK /DENSE_RANK
高级分组函数	ROLLUP/ CUBE/ GROUPING SETS
集合操作	UNION	取并集，不会有重复
	UNIONALL	取并集，重复的会出现
	INTERSECT	取交集
	MINUS	取差集

2.常用的高级查询详解

子查询

1. 子查询中如果某个字段是表达式，则该字段必须给别名，否则会报错。

子查询在 where 子句中

--查看谁的工资高于 CLARK？
select sal,name
from emp
where sal>(select sal from emp where name='CLARK');

--查看与 CLARK 职位相同的员工
slect name,job
from emp
where job=(select job from emp where name='CLARK')
and name <>'CLARK';

子查询在 having 中

--查看部门的平均工资，前提是该部门的平均工资要高于或等于20部门的平均工资
select AVG(sal),deptno
from emp
group by deptno
having AVG(sal)>=(select AVG(sal) from emp where deptno=20);

子查询在 from子句 中
如果要在一个子查询的结果中继续查询，则子查询出现在 from 子句中，这个子查询页称作行内视图或者匿名视图。

--查询出薪水比本部门平均薪水高的员工信息
select * 
from emp e,（select AVG(sal) avg_sal,deptno from emp group by deptno）t
where e.deptno = t.deptno
and e.sal>t.avg_sal
order by e.deptno DESC;

子查询在 select 子句中
可以认为是外连接的另一种表现形式，使用更灵活

SELECT e.ename,e.sal,(SELECT d.dname FROM dept_qhj d
                       WHERE d.deptno=e.deptno) dname
FROM emp_qhj e 

分页查询

1. 当查询数据量过大时，会导致响应速度慢，资源消耗大等问题，所以常常采用分页查询的方式分段将数据查询出来。
2. 分页查询不同的数据库可能完全不同，是方言
3. ROWNUM，伪列，不存在于任何一张表中，但所有表都可以查询该字段，该字段的值是可以动态生成的，是在查询表生成结果集的过程中动态产生值的。原则上从 1 开始，逐次递增，在查询过程中，只要能从表中查询出一条记录，ROWNUM 的值就是该记录的行号。
4. 在使用 ROWNUM为结果集编行号的查询过程中，不要做 ROWNUM>1 的过滤判断，否则将得不到任何结果。

select rownum ,name,sal
from emp 
where rownum <3;


select * 
from (select rownum rn,name,sal from emp)
where rn between 5 and 10;


--??????效率低
SELECT *
FROM (SELECT ROWNUM rn ,t.*
      FROM (SELECT ename,sal,job
      FROM emp_qhj 
      ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10


--效率高
SELECT *
FROM (SELECT ROWNUM rn,t.*
      FROM (SELECT ename,sal,job
      FROM emp_qhj
      ORDER BY sal DESC) t
      WHERE ROWNUM<=10)
WHERE rn>=6

--分页查询(page：表示显示第几页 size:每页显示多少条数据)
start ： (page - 1)*size + 1
end：page*size

DECODE 函数
1. DECODE 函数可以实现简单的分支效果
2. DECODE函数可以在SELECT 子句，GROUP BY 子句，ORDER BY 子句中使用
3. 注意DECODE在SELECT子句中作为参数

SELECT ename,job,sal,
       DECODE(job,
              'MANAGER',sal*1.2,
              'ANALYST',sal*1.1,
              'SALESMAN',sal*1.05,
              sal
              )bonus --别名
FROM emp_qhj