MySql_Day3—单行处理函数—分组函数/聚合函数/多行处理函数—分组查询（group by、having）

0.单行处理函数

• 什么是单行处理函数？
  输入一行，输出一行。

• 计算每个员工的年薪？

•   错误算法：select ename,(sal+comm)*12 as yearsal from emp;
  

重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。

• 使用ifnull函数：

•   正确算法：select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
  

  

• ifnull() 空处理函数？
  ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。

•   select ename,ifnull(comm,0) as comm from emp;
  

1.分组函数/聚合函数/多行处理函数

含义：每次将多条记录当做参数，输入给函数得到多条记录对应的单行结果。

• 注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
  select count(*) from emp where xxx; 符合条件的所有记录总数。
  select count(comm) from emp; comm这个字段中不为空的元素总数。

• 注意：分组函数不能直接使用在where关键字后面。

•   错误：mysql> select ename,sal from emp where sal > avg(sal);
  

count	取得记录数
sum	求和
avg	取平均
max	取最大数
min	去最小数

1.1.count——取得记录数

• 取得所有员工数

•   select count(*) from emp;
  

• 取得有津贴的人数

•   select count(comm) from emp;(自动忽略null)
  

• 取得工作岗位的种类数——distinct（可以去重）：明显的；独特的；清楚的；有区别的

•   select count(distinct job) from emp;
  

1.2.sum——求和

• 取得工资总和

•   select sum(sal) from emp;
  

• 取得薪水的合计（sal+comm）

•   错误：select sum(sal+comm) from emp;
  

•   正确： select sum(sal+IFNULL(comm,0)) from emp;
  

正确的做法是将comm字段的NULL转换成0。

1.3.avg——取平均

• 取得平均工资

•   select avg(sal) from emp;
  

1.4.max——取最大值

• 取得最晚入职的员工

•   select max(str_to_date(hiredate,'%Y-%m-%d')) from emp;
  

1.5.min—最小值

• 取得最早入职的员工

• select min(str_to_date(hiredate,'%Y-%m-%d')) from emp;
  

1.6.组合聚合函数

• 取得员工总数、总月薪、平均月薪、最高月薪、最低月薪

•   select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
  

2.分组查询——group by 、 having

语法：Oracle的语法规则比MySQL语法规则严谨。

• 当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

2.1.group by——与order by连用时，必须在order by 前边。

• 按工作岗位，取得每种岗位的名称和工资合计

•   select job,sum(sal) from emp group by job;
  

• 并按工资合计升序排列

• select job, sum(sal) from emp group by job order by sum(sal);
  

2.2.按照工作岗位+部门编码 分组，并取得工资合计

•   select job,deptno,sum(sal) from emp group by job,deptno;
  

• 找出每个部门的最高薪资，要求显示薪资大于2900的数据。

•   1.select deptno, max(sal) from emp group by deptno having max(sal) > 2900; // 效率低。
  

•   2.select max(sal),deptno from emp where sal > 2900 group by deptno;  // 效率高，建议能够使用where过滤的尽量先使用where。
  

• 找出平均工资高于2000的部门（此时不能再用where先行过滤，因为涉及求平均）

•   select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000; 
    错误：select avg(sal),deptno from emp where sal > 2900 group by deptno;
  

3.select语句总结

•   select 字段			4	
    from 表名
    where ……				1
    group by …….			2
    having ……				3
    (就是为了过滤分组后的数据而存在的—不可以单独的出现)
    order by ……			5
  

以上语句的执行顺序

• 1.首先执行where语句过滤原始数据
• 2.执行group by进行分组
• 3.执行having对分组数据进行操作
• 4.执行select选出数据
• 5.执行order by排序