数据库学习笔记：Oracle高级查询基础

Oracle高级查询

• 分组查询
• 多表查询
• 子查询
• 使用示例
  笔记中的例子均以Oracle自带的表emp、表dept和表salgrade进行说明，其结构如下图：
  

---

1. 分组查询

• 分组函数的概念
  分组函数的作用：分组函数作用于一组数据，并对一组数据返回一个值。
  常用的6个分组函数：AVG（平均值）、SUM（求和）、MIN（最小值）、MAX（最大值）、COUNT（计数）、WM_CONCAT（行转列）
• 分组函数的使用
  求出员工的平均工资和工资总额：select avg(sal), sum(sal) from emp;
  求出员工工资的最小值和最大值：select max(sal), min(sal) from emp;
  求出员工的总人数：select count(empno) from emp;
  求出部门数：select count(distinct deptno) from dept;
  行转列：select deptno, wm_concat(ename) from emp group by deptno;
  行转列的结果如下：
  wm_concat函数将查询结果中，员工姓名的多个值用逗号合并在一起。
  分组函数与空值
  分组函数会自动忽略空值。
  例1：统计员工的平均工资：

select sum(sal)/count(*) from emp;
select sum(sal)/count(empno) from emp;
select avg(sal) from emp;

例2：统计员工的平均奖金：

select sum(comm)/count(*) from emp;
select sum(comm)/count(empno) from emp; 
select avg(comm) from emp;

在SQL PLUS中执行上述语句之后，会发现得到的结果不尽相同，这是因为comm字段当中含有某些空值，所以count(*)和count(comm)得到的结果是不一样的。在分组函数中使用NVL函数可以使分组函数无法忽略空值。如：

select count(*), count(nvl(comm,0)) from emp;		--查询结果是相同的

• 使用GROUP BY子句进行数据分组

基本语法
SELECT column_name, group_function(column)
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression]   
[HAVING group_condition]
[ORDER BY column];

注意：在select 列表中所有未包含在组函数中的列都应该包含在GROUP BY字句中，而包含在GROUP BY子句中的列不必包含在SELECT列表中。
例如：求出emo表中各部分员工的平均工资

select deptno, avg(sal) 
from emp
group by deptno;

多个字段的分组要求第一个字段相同，第二个字段相同,…所有分组字段的值相同的记录为一组。

select deptno, job, sum(sal)
from emp
group by deptno, job;

• 使用HAVING子句过滤分组结果集
  例：查询平均工资大于2000的部门

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;

where和having的区别：

1. 不能在where子句中使用组函数
2. 在having子句中是可以使用组函数的
3. 从SQL优化的角度来看，不使用组函数时，where的效率更高（having先分组后过滤，where先过滤再分组）

• 在分组查询中使用ORDER BY子句
  例：求每个部分的平均工资，要求显示部门号和平均工资，并且按照工资升序排列

select deptno, avg(sal)
from emp
group by deptno
order by avg(sal) asc;

分组函数的嵌套
例：求部门平均工资的最大值

select max(avg(sal))
from emp
group by deptno;

• GROUP BY语句的增强

select deptno, job, sum(sal) from emp group by deptno, job;
select deptno, sum(sal) from emp group by deptno;
select sum(sal) from emp;

以上三条语句分别显示不同部门各职位工资总额，不同部门工资总额和工资总额，以下的语句可以将这三个结果结合起来：

select deptno, job, sum(sal) from emp group by rollup(deptno, job);
其中rollup(deptno, job)等价于：
group by deptno, job
group by deptno
group by null

查询结果如下：

• SQL*PLUS的报表功能
  报表包括：标题、页码、别名等，使用如下语句可以让上述查询结果更美观：

ttitle col 15 '我的报表' col 35 sql.pno;		--col 15表示空15个列
col deptno heading 部门号;
col job heading 职位;
col sum(sal) heading 工资总额;
break on deptno skip 1;

2. 多表查询

• 笛卡尔集
  笛卡尔集是集合的一种,假设A和B都是集合，A和B的笛卡尔积用A X B来表示，是所有有序偶(a,b)的集合，其中a属于A，b属于B（类似于一个全连接的组合）。
  A X B={（a,b)|a属于A且b属于 B} ,则AXB所形成的集合就叫笛卡尔集。
  为了避免使用笛卡尔集中的错误数据，我们可以在where语句中加入有效的连接条件，在实际运用的情况先，我们应该避免使用笛卡尔全集。若连接表的数量为n，则连接条件至少有n-1个。
• 等值连接
  连接条件中，如果是一个等号判断，则称作等值连接。
  例：查询员工信息，要求显示员工号、姓名、薪水、部门名称

select e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno

• 不等值连接
  链接条件中，使用的不是等号判断，则为不等值连接。
  例：查询员工信息，要求显示员工号、姓名、薪水、薪水级别

select e.empno, e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

• 外链接
  例：按部门统计员工的人数，要求显示：部门号、部门名称、人数

select d.deptno, d.dname, count(e.empno)
from emp e, dept d
where e.deptno = d.deptno
group by d.deptno, d.dname;

但在emp表中并不存在属于40部门的员工，所以以上查询的结果是有缺陷的，使用外链接可以避免这种情况。通过外链接，把对于连接条件不成立的记录，任然包含在最后的结果中。
左外链接：当连接条件不成立时，等号左边的表任然被包含
右外链接：当连接条件不成立时，等号右边的表任然被包含

select d.deptno, d.dname, count(e.empno)
from emp e, dept d
where e.deptno (+) = d.deptno		--使用右外链接，在等号的左边加上(+)
group by d.deptno, d.dname;

• 自连接
  例：查询员工姓名和员工的老板姓名（老板也是公司员工）

select e.ename, b.ename
from emp e, emp b
where e.mgr = b.empno;

自连接通过别名，将同一张表视为多张表。

• 自连接存在的问题
  自连接不适合操作大表，因为其使用笛卡尔集，其大小与原来的表至少成平方关系，使用层次查询可以解决该问题。

层次查询是一种单表查询，某些情况下，可以取代自连接。

想象以树的结构来构造数据，老板是父节点，他的员工是其子节点...
select level, empno, ename, salary, mgr
from emp
connect by priro empno=mgr	--上一层的员工号=老板号
start with empno = 7839 	--设置遍历起始位置
start with mgr = null		--设定遍历起始位置为根节点
order by 1;

层次查询是一个单表查询，不会再查询过程中产生大量额外数据，但其查询结果不如自连接来的直观，每种查询都有其优点和缺点，要根据实际情况结合各种查询的优缺点来使用。

3. 子查询

• 子查询概述
  使用子查询需要注意的10个问题：
  1. 子查询语法中的小括号
  2. 子查询的书写风格
  3. 可以使用子查询的位置：where，select，having，from
  4. 不可以使用子查询的位置：group by
  5. from后面的子查询
  6. 主查询和子查询可以不是同一张表
  7. 一般不在子查询中，使用排序，但在Top-N分析问题中，必须对子查询排序
  8. 一般先执行子查询，再执行主查询，但相关子查询例外
  9. 单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
  10. 注意子查询中的null值问题
• 子查询的使用
  1. 子查询语法中的小括号
     子查询部分一定要放入小括号中，这是Oracle中的语法要求。
  2. 子查询的书写风格
     注意适时换行和缩进对齐，以便于阅读，尤其是对于复杂的查询语句。
  3. 可以使用子查询的位置：where，select，having，from
  4. 不可以使用子查询的位置：group by
  5. from后面的子查询
     from后面的子查询可以在一些已知条件的基础上，再增加一些已知的条件。

从理论上来说，多表查询的效率要高于子查询的效率（在不考虑多表查询产生笛卡尔集大小的情况下），实际情况也有子查询效率高于多表查询。

--在select语句之后使用的必须是单行子查询
select empno, ename, sal, (select job from emp where empno=7839) 
from emp;
   
--在having后使用的子查询
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > (select max(sal)
				   from emp
				   where deptno=30);
				   
--在from语句之后使用子查询
select *
from (select empno, ename, sal from emp);
select *
from (select empno, ename, sal, sal * 12 as annsal from emp);

6. 子查询的排序问题
   一般不再子查询中使用排序，但在Top-N分析问题中，必须对子查询进行排序。
   例：找到员工工资最高的前三名

select rownum, empno, ename, sal 
from emp
where rownum <= 3
order by sal desc;
-----------------------------------------
select rownum, empno, ename, sal 
from (select *from emp order by sal desc)
where rownum <= 3;

（行号永远按照默认的顺序生成，行号只能使用<, <=，不能使用>, >=）

7. 子查询和主查询的执行顺序
   一般先执行子查询，后执行主查询，但相关子查询除外。相关子查询可以把主查询中的一些数据作为参数在子查询中使用。
   例：找到员工表中薪水大于本部门平均薪水的员工

select empno, ename, sal (select acg(sal) from emp where deptno=e.deptno) avgsal
from emp e		--必须要用别名
where sal > (select acg(sal) from emp where deptno=e.deptno);

• 子查询的类型（单行/多行子查询）
  单行子查询只能使用单行操作符，多行子查询只能使用多行操作符。
  返回单条记录的是单行子查询，多条记录的是多行子查询。
  单行操作符：

操作符	含义
=	Equal to
>	Greater than
>=	Greater than or Equal to
<	Less than
=<	Less than or Equal to
<>	Not equal to

多行操作符：

操作符	含义
IN	等于列表中的任意一个
ANY	和子查询返回的任意一个值比较
ALL	和子查询返回的所有值比较

• 子查询中的空值问题
  单行子查询中的空值即子查询不返回任何行，而多行子查询中的空值问题则需要注意，多行子查询返回的是一个多条记录的集合，若其中含有空值，会影响一些操作的结果，例如，查询所有不是老板的员工，若使用not in，则有下面的语句：

select e.*
from emp e
where empno not in (select mgr from emp);
where empno not in (select mgr from emp where mgr is not null);  --修改后的语句

其中select mgr from emp返回的记录中含有空值，而not in操作符会和集合中的所有数据进行比较，而任意数值和null比较的结果都为假，所以where之后的条件永远不会成立。

4. 使用示例

例1：分页查询显示员工信息：员工号、姓名、月薪，每页显示4条记录，显示第二页的员工，按照月薪降序排列

select r, empno, ename, sal
from (select rownum r, empno, ename, sal 
      from (select rownum, empno, ename, sal from emp order by sal desc) e1
      where rownum <=8) e2
where r >=5;

因为rownum只能够使用<,<=操作符，因此需要利用子查询，先筛选出部分行号的记录，在将行号作为一个字段，对其进一步设定查询条件。
例2：找到员工表中薪水大于本部门平均薪水的员工

explain plan for		--得到以下sql语句的执行计划
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 * from table(dbms_xplan.display);		--查看执行计划
-----------------------------------------------------------------
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;

通过比较执行计划可以得出，在以上示例中，相关子查询的方式更节省资源。
例3：按照部门统计员工的人数

select count(*) Total,
       sum(decode(to_char(hiredate, 'YYYY'), '1980', 1, 0)) "1980", 
       sum(decode(to_char(hiredate, 'YYYY'), '1981', 1, 0)) "1981", 
       sum(decode(to_char(hiredate, 'YYYY'), '1982', 1, 0)) "1982", 
       sum(decode(to_char(hiredate, 'YYYY'), '1987', 1, 0)) "1987", 
       --子查询方式
       (select count(*) from emp) Total,
       (select count(*) from emp where to_char(hiredate, 'YYYY')='1980') "1980",
       ...
from emp;