Oracle查询（二）子查询和分页查询

一、GROUP BY子句（分组统计）

//查询出工资小于平均工资的员工编号
//仔细分析一下下面的语句有什么问题，应该如何改正
SELECT empno FROM myemp HAVING sal<AVG(sal)

（一）格式：

④	SELECT * 														--第四步，显示分组后的数据
①	FROM 	数据来源，数据来源										--第一步，找到数据来源
②	WHERE	过滤条件													--第二步，过滤出满足条件的数据
③	GROUP BY	分组字段												--第三步，对满足条件的数据进行分组
⑤	ORDER BY 	排序字段	排序方式ASC | DESC							--第五步，指定是否按照排序的方式显示

（二）注意事项：

1.没有GROUP BY子句的情况下，SELECT子句如果有分组函数，那么不允许有非分组函数出现（如其他字段）

SELECT job,AVG(sal) FROM emp;		--//报错，提示job不是分组函数

2.有GROUP BY子句的情况下，SELECT子句如果有分组函数，除了分组函数外，还可以有GROUP BY子句里面的分组字段出现

SELECT job,AVG(sal) FROM job GROUP BY job;		--//查询到了每个职位的平均工资

3.即使有GROUP BY子句，如果SELECT子句中时一个嵌套分组函数，那么不允许有非分组函数出现，即使是GROUP BY子句里面的分组字段也不允许。

4.WHERE子句不允许有分组函数出现，如果判断条件是分组函数，需要使用HAVING子句来实现

SELECT job,AVG(sal) FROM emp GROUP BY job WHERE AVG(sal)>2000;		--本来想要查询各职位平均工资高于2000的职位和平均工资，但是结果报错提示WHERE处不允许使用AVG(sal)分组函数

（三）DEMO

1.查询出每个职位的平均工资

SELECT job,AVG(sal) FROM emp GROUP BY job;

2.查询出每个部门的名称及其人数

SELECT dname,COUNT(*) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY dname;

3.统计出每个职位的名称、人数、平均工资

SELECT job,COUNT(*) 人数,AVG(sal)  平均工资 FROM emp e GROUP BY job;

4.查询出每个部门的名称、人数、以及每个部门的员工的最高工资和最低工资

SELECT dname  部门名称,dname 人数,MAX(sal) 最高工资,MIN(sal) 最低工资 
	FROM emp e INNER JOIN dept d ON e.deptno=d.deptno
 		GROUP BY dname; 

二、HAVING子句

HAVING子句就是对分组统计函数统计出的结果进行条件判断的字句。

（一）格式

⑤	SELECT * 														--第五步，显示分组统计过滤后的数据
①	FROM 	数据来源，数据来源										--第一步，找到数据来源
②	WHERE	过滤条件													--第二步，过滤出满足条件的数据
③	GROUP BY	分组字段												--第三步，对满足条件的数据进行分组
④ HAVING	过滤条件（统计函数类型）								--第四步，对分组统计的数据进行过滤
⑥	ORDER BY 	排序字段	排序方式ASC | DESC							--第六步，指定是否按照排序的方式显示

（二）面试题

1.请说明WHERE子句和HAVING子句的区别？

WHERE子句在GROUP BY子句之前执行，不能在WHERE子句中使用统计函数；
HAVING子句在GROUP BY子句之后执行，可以在HAVING子句中对统计函数进行过滤。

（三）DEMO

1.查询平均工资高于2000的职位和平均工资

SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal)>2000;

//错误，提示HAVING MAX(AVG(sal))分组函数的嵌套太深
SELECT job,AVG(sal) FROM emp GROUP BY job HAVING MAX(AVG(sal));

2.查询出非销售人员的工作名称以及从事同一工作的雇员的月工资总和，并且要求从事同一工作的雇员工资合计大于5000，输出结果按照雇员的薪资之和升序排列

SELECT job,SUM(sal) FROM emp 		--获取到过滤后，按照职位分组的工作名称，和月工资总和
	WHERE job<>'SALESMAN' 		--过滤出非销售人员
		GROUP BY job 			--按照职位进行分组
			HAVING SUM(sal)>5000 	--过滤出分组条件下雇员工资合计大于5000的数据
				ORDER BY SUM(sal);	--最终结果按照月工资总和升序排序

三、WHERE子查询

（一）子查询

1.子查询就是将一个查询的结果作为另外一个查询（外层查询）的数据来源或者是判断条件的查询
2.常见的子查询有WHERE子查询，FROM子查询，SELECT子查询，HAVING子查询，EXISTS子查询。

（二）DEMO

1.查询低于平均工资的员工的信息

SELECT * FROM emp 	WHERE sal < (
	SELECT AVG(sal) FROM emp);

2.查询出最早入职的雇员的信息

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

3.查询出与Scott从事同一工作，并且工资相同的雇员的信息

//方式一：
SELECT * FROM emp WHERE job=(
	SELECT job FROM emp WHERE ename='SCOTT') AND sal=(
		SELECT sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';

//方式二：
SELECT * FROM emp WHERE (job,sal) = (
	SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';		--返回的是单行多列的结果集
	--第二句的SELECT job,sal中job和sal的顺序必须和第一句的WHERE(job,sal)的顺序一致

4.查询出与职位是经理的工资相同的员工信息

SELECT * FROM emp WHERE sal IN(
	SELECT sal FROM emp WHERE job='MANAGER') AND job<>'MANAGER';		--返回的是多行单列的结果集

5.查询出薪资比经理职位中最低薪资高的员工信息

方式一：
SELECT * FROM emp WHERE sal>(
	SELECT MIN(sal) FROM emp WHERE job='MANAGER');		--返回的是单行单列的结果集

方式二：
SELECT * FROM emp WHERE sal>ANY(		----sal>ANY表示 sal比括号里面的值的任意一位大都可以
	SELECT sal FROM emp WHERE job='MANAGER') AND job<>'MANAGER';		--返回的是多行单列的结果集

6.查询薪资比经理职位中最高薪资低的员工信息

方式一：
SELECT * FROM emp WHERE sal<(
	SELECT MAX(sal) FROM emp WHERE job='MANAGER') AND job<>'MANAGER';

方式二：
SELECT * FROM emp WHERE sal<ANY(		--sal<ANY表示 sal比任意一位括号里面的值小都可以
	SELECT sal FROM emp WHERE job='MANAGER') AND job<>'MANAGER';

7.查询出薪资比经理职位中最低工资低的员工的信息

方式一：
SELECT * FROM emp WHERE sal<(
	SELECT MIN(sal) FROM emp WHERE job='MANAGER');

方式二：
	SELECT * FROM emp WHERE sal<ALL(		--sal<ALL表示 sal要满足比括号里面的所有值都小
		SELECT sal FROM emp WHERE job='MANAGER');

8.查询出薪资比经理职位中最高工资高的员工的信息

方式一：
SELECT * FROM emp WHERE sal>(
	SELECT MAX(sal) FROM emp WHERE job='MANAGER');

方式二：
SELECT * FROM emp WHERE sal>ALL(
	SELECT sal FROM emp WHERE job='MANAGER');

四、HAVING子查询

HAVING子句是对分组统计函数进行过滤的字句，也可以在HAVING子句中使用子查询，这就是HAVING子查询。

1.查询出平均薪资高于所有员工平均工资的职位的名称、以及职位的人数、这些高于平均工资的职位的平均工资

SELECT job 职位,COUNT(*) 人数,AVG(sal)  平均工资 FROM emp GROUP BY job HAVING AVG(sal) > (
	SELECT AVG(sal) FROM emp);

2.查询出平均薪资最高的职位名称和该职位的平均工资

SELECT job,AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) = (
	SELECT MAX(AVG(sal)) FROM emp GROUP BY job);

3.查询出薪资排名第三的雇员的信息（不考虑并列薪资）

//错误提示：SELECT * FROM不是分组函数
SELECT * FROM emp HAVING MAX(sal) < (
	SELECT MAX(sal) FROM emp HAVING MAX(sal) < (
		SELECT MAX(sal) FROM emp));

//正确格式：
SELECT * FROM emp WHERE sal = (	--查询到第三名薪资的员工信息
	SELECT MAX(sal) FROM emp WHERE sal < (		--求得第三名薪资
		SELECT MAX(sal) FROM emp WHERE sal < (		--求得第二名薪资
			SELECT MAX(sal) FROM emp )));		--求得第一名薪资

五、ROUWNUM伪列的使用

（一）特点：

1.ROUNUM是在查询过程中动态生成的一个列，该列类似于行号字段
2.但是这个列不是数据表里原来的数据，而是在查询中动态生成的
3.该列必须从1开始（MySQL从0开始计算）

1、生成伪列

SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp;

2.查询emp表的第一条数据（作用：可以通过第一条数据观察表的数据结构）

SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ROWNUM=1;

3.查询emp表的第二条数据

//错误演示：ROWNUM必须从第一条数据开始
SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ROWNUM=2;
//正确方式：
SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ROWNUM<=2;

4、使用ROWNUM取得emp 表中的前5条数据

SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ROWNUM<=5;

5、使用ROWNUM取得EMP表中的第6~10条数据
该方法是实行分页查询操作的基础

SELECT * FROM(		--将下面的结果作为一张临时数据表emp_temp，在这张表的基础上进行数据查询
	SELECT ROWNUM rn,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE ROWNUM<=10) emp_temp
 	WHERE emp_temp.rn>=6;

六、分页查询

分页查询在项目中经常使用，最常见的是浏览器页面比如在京东或淘宝搜索某类产品如衣服等都是分页显示该类产品。

（一）分页查询的实现方式：

Oracle：行号ROWNUM + FROM子查询
MySQL：关键字LIMIT m,n
SqlServer：top关键字

（二）实现分页需要知道的两个参数：

当前页currentPage(cg)
每页显示的数据量lineSize(ls)

（三）DEMO

1.从emp表中查询第一页数据，每页显示3条数据 --查询第（1，3）条数据

SELECT * FROM(
	SELECT ROWNUM rn,empno,ename,job,mgr,sal,comm,hiredate,deptno FROM emp WHERE ROWNUM<=3) temp
	WHERE temp.rn>=1;

2.查询第二页数据，每页显示3条数据 --查询第（4，6）条数据

SELECT * FROM (
	SELECT ROWNUM rn,empno,ename,job,mgr,sal,comm,hiredate,deptno FROM emp WHERE ROWNUM<=6) temp
	WHERE temp.rn>=4;

3.查询第三页数据，每页显示3条数据 --查询第（7，9）条数据

SELECT * FROM (
	SELECT ROWNUM rn,empno,ename,job,mgr,sal,comm,hiredate,deptno FROM emp WHERE ROWNUM<=9) temp
	WHERE temp.rn>=7;

4.查询页数为currentPage（cp），每页显示数据条数为lineSize（ls）; --查询第（m,n）条数据
m = (cp-1)ls + 1;
n = cpls;

SELECT * FROM (
	SELECT ROWNUM rn,empno,ename,job,mgr,sal,comm,hiredate,deptno FROM emp WHERE ROWNUM <= n ) temp
	WHERE temp.rn >= m;