聚合函数

1 作用：将表中的一列数据作为一个整体，进行纵向计算。每个聚合函数的计算结果都是单行单列。
2 常见的聚合函数：
count:个数统计，统计一列中有多少个单元格
max:一列中的最大值
min:一列中的最小值
avg:一列的平均值
sum：一列数据的总和
3 聚合函数的计算是排除null值的,例子中没有对age的2个null进行统计

mysql> select * from employee order by salary,age,in_dpt;
+----+------+------+--------+--------+--------+
| id | name | age  | salary | phone  | in_dpt |
+----+------+------+--------+--------+--------+
|  2 | Jack |   24 |   2500 | 120120 | dpt2   |
|  1 | Tom  |   26 |   2500 | 119119 | dpt4   |
|  3 | Rose |   22 |   2800 | 114114 | dpt3   |
|  5 | Mary |   21 |   3000 | 100101 | dpt2   |
|  6 | Alex |   26 |   3000 | 123456 | dpt1   |
|  4 | Jim  |   35 |   3000 | 100861 | dpt1   |
| 12 | Tony | NULL |   3400 | 102938 | dpt3   |
| 10 | Mike |   23 |   3400 | 110110 | dpt4   |
|  8 | Rick |   24 |   3500 | 987654 | dpt3   |
|  7 | Ken  |   27 |   3500 | 654321 | dpt1   |
| 11 | Jobs | NULL |   3600 |  19283 | dpt2   |
|  9 | Joe  |   31 |   3600 | 110129 | dpt2   |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec)

mysql> 
mysql> select count(age) from employee;
+------------+
| count(age) |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)

mysql> select count(id) from employee;
+-----------+
| count(id) |
+-----------+
|        12 |
+-----------+
1 row in set (0.00 sec)

mysql> 

3.1 解决办法
使用非空的键：主键（empoyee表中的id）,count(*)---星号表示一行中不全是null就可以算作一个
使用IFNULL：count(IFNULL(age,0))

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

mysql> select count(ifnull(age,0)) from employee;
+----------------------+
| count(ifnull(age,0)) |
+----------------------+
|                   12 |
+----------------------+
1 row in set (0.00 sec)

4 例子

mysql> select max(age)from employee;
+----------+
| max(age) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql> select min(age)from employee;
+----------+
| min(age) |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(age)from employee;
+----------+
| sum(age) |
+----------+
|      259 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(age)from employee;
+----------+
| avg(age) |
+----------+
|  25.9000 |
+----------+
1 row in set (0.00 sec)