mysql 聚集函数需要注意的问题

时间:2022-04-14 06:52:57

1、当没有记录的时候,使用聚集函数,会导致出现一条记录,记录的取值都是NULL,如下:
mysql> select name from student where name='David';
Empty set

mysql> select name,avg(age) from student where name='David';
+------+----------+
| name | avg(age) |
+------+----------+
| NULL | NULL |
+------+----------+
1 row in set
2、对于取值为NULL的记录,不参与聚集函数的计算。注意:取值为NULL,也就是没有值,与取值为0,或者''不是一个概念。如下:
mysql> select * from student;
+----+--------+----------+------+
| ID | SCHOOL | NAME | AGE |
+----+--------+----------+------+
| 1 | NUM_1 | Andy | 35 |
| 2 | NUM_1 | Andy | 31 |
| 3 | NUM_2 | Bill | 28 |
| 4 | NUM_2 | Caroline | 30 |
| 5 | NUM_3 | Bill | 26 |
| 9 | NUM_2 | Andy | NULL |
+----+--------+----------+------+
6 rows in set

mysql> select name,avg(age) from student where name='Andy';
+------+----------+
| name | avg(age) |
+------+----------+
| Andy | 33.0000 |
+------+----------+
1 row in set

mysql> update student set age=0 where id =9;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select name,avg(age) from student where name='Andy';
+------+----------+
| name | avg(age) |
+------+----------+
| Andy | 22.0000 |
+------+----------+
1 row in set