Oracle学习笔记（七）——高级查询（1）
Oracle学习笔记（7）——高级查询（1）
在学习高级查询之前，我们先了解一下如何查看Oracle数据库中的所有表。因为我们要使用到Oracle数据库中SCOTT用户下的几张表（这些表是Oracle数据库自带的表）。
- 分组查询
-
分组函数的概念：分组函数作用于一组数据，并对一组数据返回一个值。
- 常用的分组函数：AVG、SUM、MIN、MAX、COUNT、WM_CONCAT(行转列)
- 语法：
-
分组函数的使用
- AVG(平均值)和SUM(合计)函数
select avg(sal),sum(sal) from emp;
- MIN(最小值)和MAX(最大值)函数
select max(sal),min(sal) from emp;
- COUNT(计数)函数
select count(*) from emp;
select count(empno) from emp;
- DISTINCE(distinct)关键字(DISTINCT用于去掉重复的记录)
select count(deptno) from emp;
select deptno from emp;
select count(distinct deptno) from emp;
- WM_CONCAT:行转列
上面显示的样式很不好看，我们首先用host cls命令清屏，然后执行下面命令，格式化显示的格式
set linesize 200
col 部门中员工的姓名 for a60
select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno;
- 分组函数与空值
select sum(sal)/count(*) 一,sum(sal)/count(sal) 二,avg(sal) 三 from emp;
2、统计员工的平均奖金
select sum(comm)/count(*) 一,sum(comm)/count(comm) 二,avg(comm) 三 from emp;
select count(*),count(comm) from emp;
总结：分组函数会自动忽略空值，只会统计非空的个数
- 在分组函数中使用NVL函数
select count(*),count(nvl(comm,0)) from emp;
- 使用GROUP BY子句数据分组(GROUP BY可以作用在一个列上，也可以作用在多个列上)
- group by子句的使用
- 使用单个列分组
- group by子句的使用
select deptno,avg(sal) from emp group by deptno;
抽象：Oracle中语法的规定
select a,组函数(x) from table group by a;
select a,b,c,组函数(x) from table group by a,b,c;
注意：在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。包含在GROUP BY子句中的列不必包含在SELECT列表中
示例：求每个部门的平均工资，要求显示：部门的平均工资
select avg(sal) from emp group by deptno;
- 使用多个列分组
select deptno,job,sum(sal) from emp group by deptno,job;
select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
非法使用组函数：
修改：
- 使用HAVING子句过滤分组结果集
- 求平均工资大于2000的部门，要求显示：部门号，平均工资
- where与having的区别
- 相同点：都是过滤结果集
- 不同点：
可以在HAVING子句中使用组函数。
- where与having通用的情况
select deptno,avg(sal) from emp where deptno=10 group by deptno;
having 先分组 后过滤
where 先过滤 后分组
where使得分组记录数大大降低，从而提高效率
注意：where子句中不能使用组函数
- 在分组查询中使用order by字句
- 示例：求每个部门的平均工资，要求显示：部门号，部门的平均工资并且按照工资升序排列
select deptno,avg(sal) 平均工资 from emp group by deptno order by
平均工资;
select
deptno,avg(sal) 平均工资 from emp group by deptno order by 2;
- 可以按照:列，别名，表达式，序号进行排序
错误演示：
-
分组函数的嵌套
- 示例：求部门平均工资的最大值
2、嵌套MAX函数求出部门平均工资的最大值
select max(avg(sal)) from emp group by deptno;
- group by语句的增强
set pagesize 30 每页显示30条记录
- SQL*Plus的报表功能