oracle系列之高级查询

分组查询

常用分组函数

-- avg,sum,min,max,count,wm_concat
select avg(sal),sum(sal),min(sal),max(sal),count(sal) from emp;

select count(*),count(comm) from emp;

上面二个值不一样，是因为分组函数会自动忽略空值，会自动忽略空值。NVL函数可以使分组函数无法忽略空值，例如下面的sql：

select count(*),count(nvl(comm, 0)) from emp;

group by语句

select column, from table_name [where condition] [group by group_by_condition] [having group_condition] [order by column];
-- 平均工资
select deptno,avg(sal) from emp group by deptno;

错误写法：

select a,b,avg(x) from emp group by a;
-- b必须出现在group by 后面。

注意：在select列表中所有未包含在组函数中的列都应该包含在group by子句中；包含在group by子句中的列不必包含在select列表中。

-- 按照部门，不同职位，统计员工的工资总额
select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
-- 分组按照group by子句列的先后顺序。

过滤分组,having子句语法

-- 求平均工资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

注意：where子句不能使用组函数，having才可以。没有组函数的时候，条件写在二者之后都是可以的，但是从sql优化的角度上，where的效率会更加高。

having是先分组，后过滤。where是先过滤，后分组。

分组函数的嵌套

-- 求部门平均工资的最大值
select max(avg(sal)) from emp group by deptno;

group by 的增强 rollup(a,b)

group by rollup(a,b)
-- 等价于
group by a,b 
+
group by a 
+
group by null(不分组)

rollup用于报表的生成非常有用

break on deptno skip 2（相同的部门号只显示一次，不同的部门号跳过2行）
select deptno,job,sum(sal) from emp group by rollup(deptno, job);–生成升序报表

order by 排序

select deptno,avg(sal) 平均工资 from emp group by deptno order by 平均工资

上面平均工资也可以用2来代替，2代表select出来的列的序号。

多表查询

笛卡尔积

假定有部门表dept和员工表emp

笛卡尔全集的列数=dept行数*emp行数.
sql拼写的时候一定要注意避免错误的笛卡尔全集。

等值连接

连接条件中如果是等于号，那就是等值连接。

-- 查询二个表的员工信息
select ... from emp e, dept d where e.deptno=d.deptno

不等值连接

连接条件中如果不是等于号，那就是等值连接。

-- salgrade薪水级别表
select ... from emp e, salgrade s where e.sal between s.lowsal and s.highsal
-- 使用between一定要注意小的在前大的在后

外链接

核心：通过外链接，把对于连接条件不成立的记录，仍然包含在最后的结果中。
左外连接：当连接条件不成立的时候，等号左边的表仍然被包含。
右外链接：当连接条件不成立的时候，等号右边的表仍然被包含。

-- 右外链接
select ... from emp e, dept d where e.deptno(+)=d.deptno group by ...;
-- 左外链接反之

自连接

通过别名，将同一张表视为多张表

-- 查询员工的老板，mgr员工老板的empno
select ... from emp a, emp b where a.mgr=b.eepno;

自连接存在的问题

根据笛卡尔积，n个别名表，自连接的总行数是表记录的n次方，所以自连接不适合操作大表，解决方法：层次查询。

层次查询

单表查询，树结构查询

-- 管理者和下属可以理解为树结构关系，emp.mgr指向管理者emp.empno
select level, empno, ename, sal, mgr from emp connect by prior empno=mgr
start with mgr is null order by 1;
-- level是一个伪劣，相当于树结构的层次，越高层越小，从1开始，order by 1代表根据level排序

自连接优点是直观，缺点是不适合操作大表。
层次查询有点是单表查询，不会产生笛卡尔积，缺点是结果不直观，可能不满足某些应用场景。

子查询

子查询说白了只是select语句的嵌套。

子查询要注意的10个问题

1. 语法的小括号不可缺少
2. 书写风格，让语句方便阅读
3. 可以使用子查询的位置：where，select，having，from
4. 不可以使用子查询的位置：group by
5. 强调：from后面的子查询
6. 主查询和子查询可以不是同一张表
7. 一般不在子查询中使用排序，但在top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但是相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10. 注意：子查询中是null值问题

-- top-n分析问题
-- 找到员工表中工资最高的前三名
-- rownum：行号，伪列

--以下是错误写法：
select rownum, empno, ename, sal from emp where rownum<=3 order by sal desc;

rownum 行号永远按照默认的顺序生成，行号只能使用<, <=; 不能使用>, >=。默认顺序是指select不加排序条件查出来的顺序，使用rownum排序是误区。

-- 正确的写法
select rownum, empno, ename, sal from（select * from emp order by sal desc）where rownum<=3;

--一般先执行子查询，再执行主查询；但是相关子查询例外,例如下面的相关子查询（子查询要依赖于父查询）。
-- 找到员工表中薪水大于本部门平均薪水的员工
select empno，ename，sal, (select avg(e2.sal) from emp e where e2.dept=e1.dept) avgsal from emp e where e.sal > (select avg(e2.sal) from emp e where e2.dept=e1.dept);
以上就是相关子查询实例，

子查询的类型

子查询分单行子查询和多行子查询。
多行操作符：in，any，all

-- 查询工资大于部门号30的任意一个员工工资的员工
select * from emp e where e.sal > any (select sal from emp where deptno=30);
-- 或者使用min函数
select * from emp e where e.sal > (select min(sal) from emp where deptno=30);
查询工资大于部门号30所有人工资的员工
select * from emp e where e.sal > any (select sal from emp where deptno=30);
-- 同样可以用max函数
-- 查询不是老板的员工
-- 下面是错误写法：
select * from emp where empno not in (select mgr from emp);--空结果
--原因：a not in (10,20,null) == a!=10 and a!=20 and a!=null  最后一个条件永远不假
-- 正确写法
select * from emp where empno not in (select mgr from emp where mgr is not null);

综合示例

-- 分页案例 查询排序后的5到8条记录
select * from (select rownum r,* from (select * from emp order by desc) e1  where rownum <= 8) e2 where e2.r > 5;

-- 按照入职年份查询员工的人数, 例如1981
select count(*) total, sum(decode(to_char(hiredate, 'YYYY'), '1981', 1, 0)) '1981' from emp;
select (select count(*) from emp) total, (select count(*) from emp where ...) '1981' fro dual;

相关子查询效率正常比多表查询快

Extra notes

-- desc emp
-- show user