数据库（组函数与分组）

8.2.8组函数、多行函数，聚合函数

--组函数|多行函数|聚合函数 : 多条记录返回一个结果
--count()  sum()  max()  min()  avg()
--注意: null值不参与组函数计算
        --如果select后面一旦出现了组函数,只能与其他组函数或者分组字段一起使用
        --组函数不能使用在where后面
​
-- 统计一下一共有多少个员工
select count(*) from emp;
select count(empno) from emp;
select count(deptno) from emp;
select count(1) from emp;
​
-- 统计一共有几个部门 
select count(deptno) from dept;
​
-- 统计有员工存在的部门总数
select count(distinct deptno) from emp;
​
-- 统计20部门一共有多少人
select count(deptno) from emp where deptno=20;
​
-- 计算本公司每个月一共要在工资上花费多少钱
select sum(sal) from emp;
​
-- 计算20部门每个月的工资花销
select sum(sal) from emp where deptno =20;
​
-- 查询本公司的最高工资和最低工资
select max(sal),min(sal) from emp;
--查看30部门的最高工资和最低工资
select max(sal),min(sal) from emp where deptno = 30;
​
-- 请查询出 20部门的平均工资
select avg(sal) from emp where deptno = 20;
​
-- 计算出所有员工的奖金总和
select sum(comm) from emp;
​
-- 统计有奖金的员工有几个
select count(comm) from emp;
​
--查询最高薪水的员工姓名,及薪水
select max(sal) from emp;
select ename,sal from emp where sal = (最高薪水);
select ename,sal from emp where sal = (select max(sal) from emp);
​
--查询工资低于平均工资的员工编号，姓名及工资
select empno,ename,sal from emp where sal <(公司平均工资);
select empno,ename,sal from emp where sal <(select avg(sal) from emp);
​
--查看高于本部门平均薪水员工姓名
select ename,deptno,sal from emp where deptno>avg(sal);
select ename,deptno,sal from emp where deptno>(select avg(sal) from emp);
select *
  from emp e1
 where sal > (select avg(sal) from emp e2 where e1.deptno = e2.deptno);
 
​

8.2.9分组 group by

--分组 : group by 分组字段1,分组字段2...
--查询: select 数据 from 数据源 where 行过滤条件 group by 分组字段..  having 组过滤条件  order by 排序字段..
--执行流程: from --> where  --> group by --> having --> select --> order by 
--注意: sql语句中一旦分组,之后只能使用分组字段或者组函数
      -- where 后面不能使用组函数,而having后面可以使用,因为执行流程问题
​
​
-- 求出每个部门的平均工资,部门编号
select deptno,avg(sal) from emp group by deptno;
​
-- 找出20部门和30部门的最高工资 
--1)先过滤后分组 : 找到所有20,30部门的员工信息,然后根据部门分组,然后计算组函数
select deptno,max(sal) from emp where deptno in (20,30) group by deptno;
--2)先分组后过滤 : 先按照部门分组,10,30,20,然后以组为单位过滤,只要20,30两组
select deptno,max(sal) from emp group by deptno having deptno in (20,30);
​
-- 求出每个部门员工工资高于1000的员工的平均工资
select deptno,avg(sal)  from emp where sal>1000 group by deptno;
​
-- 求出10和20部门部门的哪些工资高于1000的员工的平均工资
select deptno,avg(sal)  from emp where sal>1000 and  deptno in (20,30) group by deptno;
select deptno,avg(sal)  from emp where sal>1000 group by deptno having deptno in (20,30);
​
-- 找出每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
​
--求所有组的平局工资和部门编号
select deptno,max(sal),avg(sal) from emp group by deptno;
​
-- 按 岗位查询 平均工资，且平均工资大于2000的岗位
select job,avg(sal) from emp group by job having avg(sal)>2000; --推荐
​
select job,avg(sal) avg_sal from emp group by job;
select * from (select job,avg(sal) avg_sal from emp group by job) where avg_sal > 2000;
​
-- 统计每个部门的员工数，和部门编号,按照员工个数升序排序
select job,sum(ename),deptno from emp group by jon having sum(ename);
-- 查询每个工种的最高工资以及工种
select job,max(sal),job from emp group by job;
-- 查询平均工资在1500到2000之间的部门平均工资和部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>1500 and avg(sal)<2000;
-- 查询工资高于20部门平均工资的员工
select avg(sal) from emp where deptno = 20;
select ename,deptno,avg(sal) from emp group by deptno,ename having  avg(sal)>20;