文章目录
1. 分组函数(聚合函数,统计函数,多行统计函数):
作用:用作统计使用,又称为聚合函数或者统计函数或者组函数。
1.1 分组函数的分类
分组函数一共有下面5个:
- sum() 求和
- avg() 求平均值
- max() 求最大值
- min() 求最小值
- count() 计算个数
1.2 分组函数的特点
- sum() , avg()一般用于处理数值
- max() , min() , count()可以处理任何类型。
- 以上分组函数都会自动过滤null。
- 可以实现与distinct组合使用。
- 在计算时,统计多行数据,最终输出的结果为1行。
所有的分组函数都是对某一组数据进行操作的。
2. 分组函数案例
2.1 分组函数使用案例1
// 查询所有员工的工资总和
select sum(sal) from emp;
// 查询所有员工的平均薪资
select avg(sal) from emp;
// 查询所有员工的最高薪资
select max(sal) from emp;
// 查询所有员工的最低薪资
select min(sal) from emp;
// 查询员工的总人数
select count(ename) from emp;
2.2 分组函数使用案例2
查询出工资高于平均工资的员工
select ename, sal from emp where sal > avg(sal);
查询报错:
ERROR 1111 (HY000): Invalid use of group function
思考以上错误信息:无效的使用了分组函数。
原因:SQL语句当中有一个语法规则,分组函数不可直接使用在where子句当中。
解释: group by(第3步执行)实在where(第2步执行)执行之后才能使用,要先分组才能在使用。
执行顺序:
select ... 5
from ... 1
where ... 2 // 第一层条件过滤
group by.. 3
having ... 4 // 第二层过滤数据
order by .. 6
正确写法:
第一步:找出平均工资;
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
第二步:找出高于平均工资的员工;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)
将两个语句合在一起(子查询):
select ename, sal from emp where sal > (select avg(sal) from emp);
2.3 count(*)和count(字段)的区别
(1)count(*)
统计的是总记录的条数
select count(*) from emp;
查询结果:
+----------+
| count(*) |
+----------+
| 14 |
+----------+
1 row in set (0.00 sec)
(2)count(字段)
表示count当中字段不为空的元素总数
select count(comm) from emp;
查询结果:
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
总结:
count(*) :不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
count(comm):表示统计comm字段中不为NULL的数据总数。
2.4 分组函数的组合使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
查询结果:
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | max(sal) | min(sal) |
+----------+----------+-------------+----------+----------+
| 14 | 29025.00 | 2073.214286 | 5000.00 | 800.00 |
+----------+----------+-------------+----------+----------+
1 row in set (0.00 sec)
3. 单行处理函数
输入一行,输出一行
4. 单行处理函数案例
4.1 计算每个员工的年薪
(这里计算年薪时,我们算上每个月的补助)
每个员工的年资 = (月薪 + 补助)* 12 ;
因此:
select ename, (sal + comm)*12 from emp;
查询结果:
+--------+-----------------+
| ename | (sal + comm)*12 |
+--------+-----------------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+-----------------+
14 rows in set (0.00 sec)
但是,我们发现上面的查询结果中出现了NULL,这是因为有些员工的补助(comm)为NULL,在SQL语句的表达式中 (800+NULL)*12
只要有NULL参与运算,计算结果一定为NULL。所有的数据库都是这样规定!!
这个时候就要用到 ifnull() 空处理函数:
ifnull(可能为NULL的数据,被当作什么处理)
因此,上面正确的查询每个员工的年薪sql语句为:
select ename ,(sal + ifnull(comm,0)) * 12 as sal_year from emp;
ifnull(comm,0)) 将补助为NULL的值当作0;
查询结果:
+--------+----------+
| ename | sal_year |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)
4.1 计算所有员工的补助
在查询之前,我们先看一下comm中的值
select ename,comm from emp;
查询结果:
+--------+---------+
| ename | comm |
+--------+---------+
| SMITH | NULL |
| ALLEN | 300.00 |
| WARD | 500.00 |
| JONES | NULL |
| MARTIN | 1400.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 0.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+---------+
14 rows in set (0.00 sec)
很简单,使用sum函数就可以完成!
select sum(comm) from emp;
查询结果:
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
1 row in set (0.00 sec)
但是,在上面讲单行处理函数时,我们说到
在SQL语句的表达式中,只要有NULL参与运算,计算结果一定为NULL。所有的数据库都是这样规定!!
为什么这里的查询结果不是NULL?其实,在1.2分组函数的特点时,我们就说到分组函数都会自动过滤NULL! 因此,这里的查询结果不为NULL。