其实Oracle并不难——这十天，跟着我一起学习数据库(第四天)

/*

补充：order by 子句中可以使用数字指定显示列表中对应的列作为排序标准；

多行函数：

1、分组函数

分组函数时多行函数；

max - 最大值

min - 最小值

avg - 平均值

sum - 总和

count - 个数(不过滤重复)

COUNT(*) - 返回表中满足条件的行记录数（这条记录中所有的字段都会作为区别标识字段）(不过滤重复)

distinct关键字--剔除重复内容

注意：

分组函数操作的数据记录有多行，运行结果集只有一行；

分组函数中空值处理:除了COUNT（*）之外，其它所有分组函数都会忽略列中的空值，然后再进行计算。

也就是分组函数只处理非null的值；

如果在分组函数中，需要将null值也计入其中，则需要先使用nvl函数将null值转换为默认值；

1、分组查询

分组查询是将查询的结果按分组标准，分成不同的数据组；

然后使用分组函数分别对每个数据组进行处理；

语法：

select 字段列表 from 表名列表 where 查询条件语句 group by 分组字段列表 order by 语句；

解析：

在分组查询中，select之后的字段列表只能显示分组函数和group by 之后的字段内容；

不能显示其他的字段内容，没有业务逻辑意义（语法也不允许）；

字段中的null值也会参与分组；

2、having分组条件子句

语法：

select 字段列表 from 表名列表 where 查询条件语句 group by 分组字段列表 having 分组条件语句 order by 语句；

解析：

having子句中的字段只能是分组函数或者是group by之后的字段；

having子句的要求和显示字段列表的要求是一样的；

一般，where条件子句对需要进行分组的数据过滤的条件，也就是，where条件过滤的数据准备进行分组的；

having条件子句是在分组之后对各组进行过滤的条件，也就是，having条件选择那些组的数据进行显示；

注意，如果过滤条件即可以写在having子句，也可以写在where子句时，优先将过滤条件写在where子句；

sql优化原则：尽可能多的where条件过滤，然后再having过滤；

3、select语句的执行流程；

SELECT语句执行过程：

1.通过FROM子句中找到需要查询的表；

2.通过WHERE子句进行非分组函数筛选判断；

3.通过GROUP BY子句完成分组操作；

4.通过HAVING子句完成组函数筛选判断；

5.通过SELECT子句选择显示的列或表达式及组函数；

6.通过ORDER BY子句进行排序操作。

*/

----------分组函数-------------

----查询部门30中，最低工资,最高工资,平均工资,总人数，工资之和

SELECT MAX(E.SAL) MAXSAL,MIN(E.SAL) MINSAL,AVG(E.SAL)AVGSAL,COUNT(E.EMPNO) EMPCOUNT,

SUM(E.COMM)EMPCOMM FROM EMP E WHERE E.DEPTNO=30;

-- DISTINCT使用在显示字段列表中

SELECT DISTINCT E.EMPNO FROM EMP E;

---分组查询

SELECT * FROM EMP E;

SELECT E.DEPTNO,MAX(E.SAL) MAXSAL,MIN(E.SAL) MINSAL,AVG(NVL(E.SAL,0)) MINSAL,

COUNT(DISTINCT NVL(E.EMPNO,0)) EMPCOUNT,SUM(E.EMPNO) SALSUM,COUNT(E.EMPNO) EMPCOUNT

FROM EMP E WHERE 1=1 GROUP BY E.DEPTNO;

--------------ppt6练习1-------------

--1.查询部门20的员工，每个月的工资总和及平均工资。

SELECT SUM(E.SAL) SALSUM,AVG(NVL(E.SAL,0))SALAVG FROM EMP E WHERE E.DEPTNO=20;

--2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT MAX(E.SAL) MAXSAL,MIN(E.SAL) MINSAL,COUNT(*) FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.LOC='CHICAGO';

--3.查询员工表中一共有几种岗位类型。

SELECT DISTINCT COUNT(E.JOB)"岗位类型" FROM EMP E;

--思考：查询每个部门的平均工资？

SELECT E.DEPTNO,AVG(NVL(E.SAL,0)) "平均工资" FROM EMP E WHERE 1=1 GROUP BY E.DEPTNO;

--------------ppt6练习2-------------

--1.查询每个部门的部门编号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资

SELECT E.DEPTNO "部门编号",D.DNAME "部门名称",COUNT(NVL(E.EMPNO,0)) "部门人数",MAX(E.SAL) "最高工资",

MIN(E.SAL) "最低工资",SUM(E.SAL)"工资总和",AVG(NVL(E.SAL,0)) "平均工资"

FROM EMP E,DEPT D

WHERE E.DEPTNO=D.DEPTNO GROUP BY E.DEPTNO,D.DNAME;

--2.查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资。

SELECT D.DEPTNO "部门编号",D.DNAME "部门名称",E.JOB "岗位名称",COUNT(NVL(E.EMPNO,0)) "部门人数",MAX(E.SAL) "最高工资",

MIN(E.SAL) "最低工资",SUM(E.SAL)"工资总和",AVG(NVL(E.SAL,0)) "平均工资"

FROM EMP E,DEPT D

WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO,D.DNAME,E.JOB;

--3.查询每个经理所管理的人数 ，经理编号，经理姓名，要求包括没有经理的人员信息。

SELECT COUNT(NVL(E.MGR,0)) "所管理的人数",E.MGR "经理编号", M.ENAME "经理姓名"

FROM EMP E LEFT JOIN EMP M ON E.MGR=M.EMPNO GROUP BY E.MGR,M.ENAME;

SELECT E.EMPNO,E.ENAME,E.JOB FROM EMP E ORDER BY E.MGR;

----****************2017-11-17***************-----

--查询工作在CHICAGO的员工人数，最高工资及最低工资。

SELECT MAX(E.SAL) MAXSAL,MIN(E.SAL) MINSAL,COUNT(*),D.LOC

FROM EMP E,DEPT D

WHERE E.DEPTNO=D.DEPTNO GROUP BY D.LOC

HAVING D.LOC='CHICAGO';

-- 查询每个部门最高工资大于2900的部门编号，最高工资

SELECT MAX(E.SAL),E.DEPTNO FROM EMP E GROUP BY E.DEPTNO HAVING MAX(E.SAL)>2900;

--查询部门编号大于10的每个部门最高工资大于2900的部门编号，最高工资

SELECT MAX(E.SAL),E.DEPTNO FROM EMP E GROUP BY E.DEPTNO HAVING MAX(E.SAL)>2900 AND E.DEPTNO>10;

-----效率高

SELECT E.DEPTNO,E.JOB,AVG(E.SAL) FROM EMP E

WHERE E.JOB IN('SALESMAN','MANAGER','CLERK')

GROUP BY E.DEPTNO,E.JOB

HAVING AVG(E.SAL)>2000

ORDER BY 3 DESC;

SELECT E.DEPTNO,E.JOB,AVG(E.SAL) FROM EMP E

GROUP BY E.DEPTNO,E.JOB

HAVING AVG(E.SAL)>2000 AND E.JOB IN('SALESMAN','MANAGER','CLERK')

ORDER BY 3 DESC;

--------------ppt6练习3-------------

--1.查询部门人数大于2的部门编号，部门名称，部门人数。

SELECT E.DEPTNO,D.DNAME,COUNT(*) FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

GROUP BY E.DEPTNO,D.DNAME

HAVING COUNT(*)>2;

--2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。

SELECT D.DEPTNO,D.DNAME,COUNT(*),AVG(NVL(E.SAL,0)) FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

GROUP BY D.DEPTNO,D.DNAME

HAVING COUNT(*)>2 AND AVG(NVL(E.SAL,0))>2000

ORDER BY COUNT(*);

--------------ppt6课后作业-------------

--1.查询部门平均工资在2500元以上的部门名称及平均工资

SELECT E.DEPTNO,D.DNAME,AVG(NVL(E.SAL,0)) FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

GROUP BY E.DEPTNO,D.DNAME

HAVING AVG(NVL(E.SAL,0))>2500;

-- 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

SELECT E.JOB,AVG(NVL(E.SAL,0)) FROM EMP E

GROUP BY E.JOB

HAVING E.JOB!='SA%' AND AVG(NVL(E.SAL,0))>2500

ORDER BY AVG(NVL(E.SAL,0)) DESC ;

--3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

SELECT E.DEPTNO,D.DNAME,AVG(NVL(E.SAL,0)) FROM EMP E

JOIN DEPT D ON E.DEPTNO=D.DEPTNO

GROUP BY E.DEPTNO,D.DNAME

HAVING AVG(NVL(E.SAL,0))>2500;

--4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

-- 5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

-- 6.写一个查询，显示每个部门最高工资和最低工资的差额。