SQL-DQL（5）之分组函数

分组函数：可以理解为向函数传入一组值，返回一个值。主要做统计使用，又称为统计函数，聚合函数，组函数。

接下来主要讲的是常见的分组函数：

---

一. 常见函数 :

   - sum():求和
   - avg():平均值
   - max():最大值
   - min():最小值
   - count():计算个数

注意：

1.sum(),avg()一般用于处理数值型。max(),min(),count()可以处理任何类型。
2.以上分组函数都忽略null值。
3.可以和distinct搭配实现去重的运算。
4.一般使用 count（*）/count（1）函数用作统计行数。
5.和分组函数一同查询的字段要求是group by 后的字段。

二 . 分组函数的练习

1. 分组函数的简单使用

mysql> #分组函数的简单使用
mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
|   691400.00 |
+-------------+
1 row in set (0.29 sec)

mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)

mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
|    24000.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
|     2100.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(salary) total ,avg(salary) average, max(salary) maximum,min(salary) minimum from employees;
+-----------+-------------+----------+---------+
| total     | average     | maximum  | minimum |
+-----------+-------------+----------+---------+
| 691400.00 | 6461.682243 | 24000.00 | 2100.00 |
+-----------+-------------+----------+---------+
1 row in set (0.00 sec)

2. 检验参数支持哪些类型

#这些函数都忽略null值

# sum(),avg(),一般用于处理数值型参数.其他情况不推荐。

mysql> select sum(last_name),avg(last_name) from employees;//这样会报警告的，别这么用。
+----------------+----------------+
| sum(last_name) | avg(last_name) |
+----------------+----------------+
|              0 |              0 |
+----------------+----------------+
1 row in set, 214 warnings (0.00 sec)

#日期也相当于数值型
mysql> select sum(hiredate),avg(hiredate) from employees;
+------------------+---------------------+
| sum(hiredate)    | avg(hiredate)       |
+------------------+---------------------+
| 2148552443000000 | 20079929373831.7757 |
+------------------+---------------------+
1 row in set (0.00 sec)

#max(),min(),count()可以处理任何类型。
mysql> select max(last_name) ,min(last_name) from employees;
+----------------+----------------+
| max(last_name) | min(last_name) |
+----------------+----------------+
| Zlotkey        | Abel           |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> select max(hiredate) ,min(hiredate) from employees;
+---------------------+---------------------+
| max(hiredate)       | min(hiredate)       |
+---------------------+---------------------+
| 2016-03-03 00:00:00 | 1992-04-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.35 sec)

mysql> select count(commission_pct) from employees;
+-----------------------+
| count(commission_pct) |
+-----------------------+
|                    35 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select count(last_name) from employees;
+------------------+
| count(last_name) |
+------------------+
|              107 |
+------------------+
1 row in set (0.00 sec)

3. 和distinct搭配使用

mysql> select sum(distinct salary), sum(salary) from employees;
+----------------------+-------------+
| sum(distinct salary) | sum(salary) |
+----------------------+-------------+
|            397900.00 |   691400.00 |
+----------------------+-------------+
1 row in set (0.35 sec)

mysql> select count(distinct salary),count(salary) from employees;
+------------------------+---------------+
| count(distinct salary) | count(salary) |
+------------------------+---------------+
|                     57 |           107 |
+------------------------+---------------+
1 row in set (0.07 sec)

4. count()函数的详细介绍

• count(*):统计所有列的行数，这样可以避免有的列有null值而造成未统计进来，因为一行的所有列，肯定有一个不为null值，这样就统计成功了。
• count（1）：就是在在表的前面加一列1，然后统计1 的个数，那么也就是行的个数了。当然这个数值可以是1，或0，或2，或3...

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from employees;
+----------+
| count(1) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

mysql> select count(0) from employees;
+----------+
| count(0) |
+----------+
|      107 |
+----------+
1 row in set (0.00 sec)

√三. 分组函数作业

1.查询公司员工工资的最大值，最小值，平均值，总和.(上面代码已有，不重复了）

2.查询员工表中的最早入职时间和最迟入职时间的相差天数（DIFFRENCE）

#使用DATEDIFF（）函数。
mysql> select  DATEDIFF(max(hiredate),min(hiredate)) DIFFRENCE  from employees; +-----------+
| DIFFRENCE |
+-----------+
|      8735 |
+-----------+
1 row in set (0.00 sec)

#突然想查一下，今天距离下个情人节还有几天。😄

#是02-14吗，😅
mysql> select datediff('2019-02-14',now()) myGiftDate;
+------------+
| myGiftDate |
+------------+
|        177 |
+------------+
1 row in set (0.00 sec)

3. 查询部门编号为90的员工个数

mysql> select count(last_name) as 
    -> 员工个数 from employees where department_id = 90;
+--------------+
| 员工个数     |
+--------------+
|            3 |
+--------------+
1 row in set (0.40 sec)

#推荐使用这种哈！
mysql> select count(*) as  员工个数 from employees where department_id = 90; 
+--------------+
| 员工个数     |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！