Oracle高级查询部分

一、简介

本部分需要有如下两个部分的基础

《oracle数据库开发必备利器之SQL基础》
《oracle数据库开发利器之函数》

二、分组查询

（1）什么是分组函数

分组函数作用于一组数据，并对一组数据返回一个值。

结构：

select [column,] group function(column),...
from table
[where condition]
[group by column]
[order by column];

（2）常见的分组函数

avg 求平均值和 sum 求和

求出员工的平均工资和工资的总和
求出员工工资的最大值和最小值
求出员工的总人数
distinct关键字求出部门数
如

select count(distinct deptno) from emp;

min 最小值

max 最大值

count 求个数

wm_concat 行转列

如

select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;

（3）分组函数与空值

举例1：统计员工的平均工资
如

select sum(sal)/count(*) 一,sum(sal)/count(sal) 二,avg(sal) 三 from emp;

结果一样

举例二：统计员工的平均奖金
如

select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from emp;

二和三结果一样，一不一样，因为在奖金列里面含有空值，count的时候数数不一样

所以分组函数会自动忽略空值，可以在分组函数中使用nvl函数来使分组函数无法忽略空值

如

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

（4）分组数据

group by 子句

求出员工表中各个部门的平均工资
注意：在select列表中所有未包含在组函数（就是汇总计算xxx的列）中的列都应该包含在group by子句中，但包含在group by子句中的列不必包含在select列表中
如

select avg(sal) from emp group by deptno;

使用多个列分组

如

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

（5）非法使用组函数

要求所用包含于select列表中，而未包含于组函数中的列都必须包含于group by子句中。

select deptno,count(ename)
from emp;

这里的deptno没有包含在group by子句中，所以会报错。

（6）过滤分组

求平均工资大于2000的部门，having子句的使用
在group by后加[having group_condition]
如

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

注意不能在where子句中使用组函数（注意）。

可以在having子句中使用组函数。

如果在能使用where的场景下，从SQL优化的角度来看，尽量使用where效率更高，因为having是在分组的基础上过滤分组的结果，而where是先过滤，再分组。要处理的记录数不同。所以where能使分组记录数大大降低，从而提高效率。

（7）在分组查询中使用order by子句

示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资，并且按照工资升序排列
可以按照：列、别名、表达式、序号进行排序

select deptno,avg(sal) from emp group by deptno order by avg(sal);
select deptno,avg(sal) 平均工资 from emp group by deptno order by 2;  --也可以填写序号

（8）分组函数的嵌套

示例：求部门平均工资的最大值
如

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

（9）group by语句的增强

按部门、不同的职位显示工资的总额；同时按部门，统计工资总额；统计所有员工的工资总额。
如

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

结果：

DEPTNO JOB         SUM(SAL)
------ --------- ----------
    10 CLERK           4541
    10 MANAGER         6455
    10 PRESIDENT       9711
    10                20707
    20 CLERK           8235
    20 ANALYST        12360
    20 MANAGER       7032.5
    20              27627.5
    30 CLERK         4117.5
    30 MANAGER         6895
    30 SALESMAN       18648
    30              29660.5
                      77995

rollup就可以实现上述的效果。小计、总计的效果，可以用在报表里面。

再次优化，先运行：

break on deptno skip 2

再运行上面的代码即可。

（10）sqlplus的报表功能

如

ttitle col 15 '我的报表' col 35 sql.pno    --15表示空15列，sql.pno表示报表页码
col deptno heading 部门号   --设置别名
col job heading 职位
col sum(sal) heading 工资总额
break on deptno skip 1		--deptno只显示一次，部门间间隔一行

结果：

              我的报表                     1
    部门号 职位        工资总额
---------- --------- ----------
        10 CLERK           4541
           MANAGER         6455
           PRESIDENT       9711
                          20707

        20 CLERK           8235
           ANALYST        12360
           MANAGER       7032.5
                        27627.5


              我的报表                     2
    部门号 职位        工资总额
---------- --------- ----------
        30 CLERK         4117.5
           MANAGER         6895
           SALESMAN       18648
                        29660.5

                          77995

三、多表查询

（1）简介

按数据库设计原则，员工表中只有部门的编号信息，部门的详细信息会存放在部门表中。

什么是多表查询：就是从多个表中获取数据。

前提是有一个外键约束来表示员工是哪个部门的，有个一个部门号来联结。

（2）笛卡尔集

有了它才有多表查询的存在。笛卡尔集的列数等于每张表列数的相加，行数等于每张表的行数相乘。比如emp*dept有六列六行。里面的每一条记录不一定都是对的。多表查询就是要从笛卡尔集中选择出正确的记录。需要一个连接条件，比如部门号相等。有了连接条件，就能避免使用笛卡尔全集。在实际运行环境下，应提供where连接条件，避免使用笛卡尔全集。连接条件至少有要连接表数-1个。

创建笛卡尔集可以使用全连接：

FULL JOIN

（3）连接的类型

等值连接
不等值连接
外连接
自连接

（4）等值连接

如

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

（5）不等值连接

如

SELECT e.empno,e.ename,e.sal,s.grade FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;

（6）外连接

如

SELECT d.deptno 部门号,d.dname 部门名称,COUNT(e.empno) 人数 FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.deptno,d.dname;   --漏了一个部门

核心：通过外连接，把对于连接条件不成立的记录，仍然包含在最后的结果中。

左外连接（LEFT [OUTER] JOIN）：当连接条件不成立的时候，等号左边的表仍然被包含

右外连接（RIGHT [OUTER] JOIN）：当连接条件不成立的时候，等号右边的表仍然被包含

因此上述表达式改为：

改为右外连接方法是在相反的方向的等值连接结尾加上(+),比如右外连接就是加在左边的最后。

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 d.deptno 部门号,d.dname 部门名称,COUNT(e.empno) 人数 FROM emp e,dept d WHERE e.deptno right join d.deptno GROUP BY d.deptno,d.dname;

得到结果：

部门号 部门名称               人数
------ -------------- ----------
    10 ACCOUNTING              3
    40 OPERATIONS              0
    20 RESEARCH                5
    30 SALES                   6

（7）自连接

作用：通过别名，将同一张表视为多张表（核心） INNER JOIN

如

SELECT e.ename 员工姓名,b.ename 老板姓名 FROM emp e,emp b WHERE e.mgr=b.empno;

自连接存在的问题

尽管是查询一张表，但本质上仍然是多表查询，会产生笛卡尔集。

可以通过这个看笛卡尔集有多少条记录select count(*) from emp e,emp b;表越多，次方越多。比如员工表中有一亿条记录，如果看成三张表，就有一亿的立方的笛卡尔集，所以自连接不适合查询大表。

所以要使用解决方法： 层次查询 （单表查询，只有在一张表时才不会查询笛卡尔集，在某些情况下可以取代自连接）。
层次查询的原理：可以把前面的结果变成分level的一棵树。这棵树的根是没有上司的king，也就是mgr就是NULL。
如：

SELECT level,empno,ename,sal,mgr FROM emp CONNECT BY PRIOR empno=mgr START WITH mgr IS NULL ORDER BY 1;

自连接的优点：结果直观。缺点：不适合操作大表。
层次查询的优点：适合单表查询，不会产生笛卡尔集。缺点：并没有自连接那么直观。

四、子查询

（1）子查询介绍

为什么要学习子查询：子查询可以解决不能一步求解的问题

示例：查询工资比scott高的员工信息

子查询的语法：其实也就是select语句的嵌套

select select_list
from table
where expr operator
	(select select_list
	from table);

如

select * from emp where sal > (select sal from emp where ename='SCOTT');

（2）子查询注意的十个问题

子查询语法中的小括号
语法中一定要有小括号，不然是错的。
子查询的书写风格
该换行的换行，该缩进的索引，可以便于阅读。

该换行的换行，该缩进的索引，可以便于阅读。

可以使用子查询的位置：where,select,having,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后面放置：

非常的重要，很多问题都是在from后面方式子查询来解决的

如

SELECT * from(SELECT empno,ename,sal FROM emp);

不可以使用子查询的位置：group by

如

SELECT AVG(sal)
FROM emp
GROUP BY (SELECT deptno FROM emp);   --会报错，这里不允许出现子查询表达式

强调：from后面的子查询，比较特殊，比较重要

如

SELECT *
FROM (SELECT empno,ename,sal,sal*12 annsal FROM emp);

主查询和子查询可以不是同一张表

如

SELECT * FROM emp WHERE deptno=
                  (SELECT deptno
		FROM dept
		WHERE dname='SALES');

多表查询代码：

SELECT e.*							
FROM emp e,dept d
WHERE e.deptno=d.deptno AND d.dname='SALES';

哪种查询方式好呢？从理论上来讲，尽量使用多表查询比较好，因为子查询需要对数据库访问两次，而多表查询只需要对数据库访问一次。但实际情况下有可能不一样，因为多表查询的笛卡尔集可能很大所以慢了。

一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序

比如找到员工表中工资最高的前三名。

rownum行号，是一个伪列，表上没有这一列，当做一些特殊操作的时候，oracle自动加上。行号需要注意的问题：行号永远按照默认的顺序生成；行号只能使用<，<=，不能使用>或者>=这样的符号。

如

SELECT ROWNUM,empno,ename,sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM<=3;

一般先执行子查询，再执行主查询；但相关子查询例外

相关子查询的表必须设定一个别名，然后把主查询的内容传入到子查询中进行查询。

如

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);

这里就把主查询e表中的部门号传入子查询中进行查询了。

单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

单行操作符：=、>、>=、<、<=、<>

如

SELECT * FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=7566)
AND sal > (SELECT sal FROM emp WHERE empno=7782);

又如

SELECT * FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp);

如

SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
                  FROM emp
		WHERE deptno=20);

非法使用单行子查询：

如

select empno,ename from emp where sal=(select min(sal)
		from emp
		group by deptno);    --因为子查询返回了不止一行，所以是非法使用单行子查询。

多行操作符：in（等于列表中的任意一个）、any（和子查询返回的任意一个值比较）、all（和子查询返回的所有值比较）

in：

如

SELECT * FROM emp 
WHERE deptno IN (SELECT deptno FROM dept WHERE dname='SALES' OR dname='ACCOUNTING');

又如

SELECT e.* FROM emp e,dept d
WHERE e.deptno=d.deptno AND (d.dname='SALES' OR d.dname='ACCOUNTING');

any：

如

SELECT * FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);

等价于

SELECT * FROM emp
WHERE sal > (SELECT min(sal) FROM emp WHERE deptno=30);

all：

如

SELECT * FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);

等价于：

SELECT * FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);

注意：子查询中是null值的问题

单行子查询中返回空值，要使用in之类的关键字，等于号的话永远为空。

多行子查询中，如查询不是老板的员工

如：

SELECT * FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp);   --会不返回结果，因为当子查询中包含空值的时候，不能使用not in，因为not in等同于不等于所有（永远为假）。

所以修改为：

SELECT * FROM emp
WHERE empno NOT IN (SELECT mgr FROM emp
               WHERE mgr IS NOT NULL);

五、综合示例

（1）案例一

分页查询显示员工信息：显示员工号，姓名，月薪
每页显示四条记录
显示第二页的员工
按照月薪降序排列
注意：rownum只能使用<,<=不能使用>,>=，因为oracle数据库是一个行式数据库，取了第一行才能取第二行，所以行号永远从1开始，所以比如rownum>=5这样的条件永远为假。

所以分页查询：

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;

（2）案例二

找到员工表中薪水大于本部门平均薪水的员工

如

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;   --多表查询

如果需要查询执行计划看性能的话，则在语句前面加上EXPLAIN PLAN FOR

执行一遍之后，运行select * from table(dbms_xplan.display);即可查看性能分析，看消耗的CPU的多少来判定性能的优劣。

（3）案例三

按部门统计员工人数，按照规定格式输入，已知员工的入职年份在80,81,82,87年之中。
如

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"
FROM emp;

使用子查询方法：

SELECT
(SELECT COUNT(*) FROM emp) Total,
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1980') "1980",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1981') "1981",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1982') "1982",
(SELECT COUNT(*) FROM emp WHERE to_char(hiredate,'yyyy')='1987') "1987"
FROM dual;

（4）练习

新建两个表，然后按要求查到相关的内容

第一个表：

CI_ID                STU_IDS
-------------------- --------------------------------------------------------------------------------
1                    1,2,3,4
2                    1,4

表结构：

Name    Type          Nullable Default Comments 
------- ------------- -------- ------- -------- 
CI_ID   VARCHAR2(20)                            
STU_IDS VARCHAR2(100) Y

第二个表：

STU_ID               STU_NAME
-------------------- --------------------
1                    张三
2                    李四
3                    王五
4                    赵六

表结构：

Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
STU_ID   VARCHAR2(20)                           
STU_NAME VARCHAR2(20) Y

提示：

1.需要进行两个表的连接查询，为两个表都取别名

2.使用instr(a,b)函数，该函数的含义为：如果字符串b在字符串a的里面，则返回的是b在a中的位置，及返回值大于0

3.需要用到分组查询

4.使用wm_concat(cols)函数对学生姓名用逗号进行拼接

--结果查询语句
SELECT ci_id,wm_concat(stu_name) stu_name 
FROM pm_stu,pm_ci 
WHERE INSTR(stu_ids,stu_id)>0 
GROUP BY ci_id;

得到正确结果：

CI_ID                STU_NAME
-------------------- --------------------------------------------------------------------------------
1                    张三,赵六,王五,李四
2                    张三,赵六

同时学会了一个，如果在oracle中，需要实现如果表已经存在则先删除表的操作，写法为：

--如果已经存在表则先删除表
DECLARE
  k NUMBER;
BEGIN
	select count(*) INTO k from all_tables where table_name='PM_CI';
	IF k=1 THEN
		execute IMMEDIATE 'DROP TABLE pm_ci';
	END IF;
END;
/

其中查询的表名和drop的表名变成你要检测的表名即可。

===================================================================================