分组函数笔记

–Oracle自带用户scott用户，需要启用
show user–显示当前用户

desc emp–查看员工表结构

名称 空值? 类型

---

EMPNO NOT NULL NUMBER(4) --员工号
ENAME VARCHAR2(10) --姓名
JOB VARCHAR2(9) --职位
MGR NUMBER(4) --员工老板号
HIREDATE DATE --员工入职日期
SAL NUMBER(7,2) --员工月薪
COMM NUMBER(7,2) --员工奖金
DEPTNO NUMBER(2) --员工所在部门的部门号

select * from emp;

7369 SMITH CLERK 7902 17-12月- 80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10

desc dept --部门级别表

名称 空值? 类型

---

DEPTNO NOT NULL NUMBER(2) --部门的部门号
DNAME VARCHAR2(14) --部门名称
LOC VARCHAR2(13) --部门地点

select * from dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

desc salgrade–工资级别表

名称 空值? 类型

---

GRADE NUMBER --级别
LOSAL NUMBER --级别最低薪水
HISAL NUMBER --级别最高薪水

select *from salgrade;
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

常用的分组函数
1.AVG平均值
2.SUM函数
3.MIN最小值
4.MAX最大值
5.COUNT计数
6.WM_CONCAT行转列函数
！！！–分组函数中会自动过滤空值
DISTINCT 去掉重复值
SQL>HOST CLS --清除屏幕的字
SQL>/ --重新执行上一条语句
SQL>ed 修改上面的查询语句
SQL>set linesize 200–设置宽度
SQL>col --设置列的宽度
select avg(sal),sum(sal) from emp;
select min(sal),max(sal) from emp;
select count(*) from emp;
select count(empno) from emp;
select count(distinct(deptno)) from emp;
select deptno,ename from emp ;
select deptno,wm_concat(ename) from emp;
select deptno,wm_concat(ename) from emp group by DEPTNO;

–统计员工的平均总工资
select (sum(sal) + sum(comm))/count() from emp ;
select avg(sal) + avg(comm) from emp;
–统计员工的工资
select sum(sal)/count() 一, sum(sal)/count(sal) 二, avg(sal) 三 from emp;
–统计员工的奖金
select sum(comm)/count() 一, sum(comm)/count(comm) 二, avg(comm) 三 from emp;
–奖金里含有空值，所以结果不一样
select count() ,count(comm) from emp;–分组函数中会自动过滤空值
–nvl函数使分组函数无法忽略空值
select sum(comm)/count(*) 一, sum(comm)/count(nvl(comm,0)) 二, avg(comm) 三 from emp;

group by 子句的使用：
–求出员工表中各部门的平均工资
select * from emp;
select distinct(deptno),avg(sal) from emp group by deptno;
select distinct(deptno),sum(sal)/count(DEPTNO) from emp group by deptno;
select sum(sal)/count(DEPTNO) from emp group by deptno;
select avg(sal) from emp group by deptno;
–讲义
select deptno,avg(sal)from emp group by deptno;
抽象：
select a,b,c,组函数(x)
from table
group by a,b,c;
语法：
在select列表中所有未包含在组函数中的列都应该包含在group by 子句中
包含在group by 子句中的列不必包含在select列表中

使用多个列分组：
目标 ：按照部门、不同的职位，统计员工的工资总额
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;(排序）

非法使用组函数：
select deptno,count(ename)
from emp;–deptno未在分组函数count（）里面，所以错误
错误代码：ORA-00937：not a dingle-group gorup function