Oracle学习笔记（7）——高级查询（1）

Oracle学习笔记（7）——高级查询（1）

在学习高级查询之前，我们先了解一下如何查看Oracle数据库中的所有表。因为我们要使用到Oracle数据库中SCOTT用户下的几张表（这些表是Oracle数据库自带的表）。

 

分组查询 分组函数的概念：分组函数作用于一组数据，并对一组数据返回一个值。 常用的分组函数：AVG、SUM、MIN、MAX、COUNT、WM_CONCAT(行转列) 语法： 分组函数的使用 AVG(平均值)和SUM(合计)函数 1、求出员工的平均工资和工资的总额 select avg(sal),sum(sal) from emp; MIN(最小值)和MAX(最大值)函数 2、求出员工工资的最大值和最小值 select max(sal),min(sal) from emp; COUNT(计数)函数 3、求出员工的总人数 select count(*) from emp; select count(empno) from emp; DISTINCE(distinct)关键字(DISTINCT用于去掉重复的记录) 4、求出部门数 select count(deptno) from emp; select deptno from emp; select count(distinct deptno) from emp;
WM_CONCAT:行转列 示例：select deptno,wm_concat(ename) from emp group by deptno;
上面显示的样式很不好看，我们首先用host cls命令清屏，然后执行下面命令，格式化显示的格式 set linesize 200 col 部门中员工的姓名 for a60 select deptno 部门号,wm_concat(ename) 部门中员工的姓名 from emp group by deptno; 分组函数与空值 1、统计员工的平均工资 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函数 注意：NVL函数使用分组函数无法忽略空值 select count(*),count(nvl(comm,0)) from emp; 使用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的部门，要求显示：部门号，平均工资 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000
where与having的区别 相同点：都是过滤结果集 不同点： 不能再WHERE子句中使用组函数(注意)。 可以在HAVING子句中使用组函数。 where与having通用的情况 select deptno,avg(sal) from emp group by deptno having deptno=10; 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 avg(sal); select deptno,avg(sal) 平均工资 from emp group by deptno order by 平均工资; select deptno,avg(sal) 平均工资 from emp group by deptno order by 2; 可以按照:列，别名，表达式，序号进行排序


错误演示：
分组函数的嵌套 示例：求部门平均工资的最大值 1、通过AVG函数求出每个部门的平均工资 2、嵌套MAX函数求出部门平均工资的最大值 select max(avg(sal)) from emp group by deptno; group by语句的增强 break on deptno 2 相同部门号的值只显示一次，不同的部门号之间跳过两行 set pagesize 30 每页显示30条记录




SQL*Plus的报表功能 报表包括：标题，页码，别名等