mysql聚集函数重命名_mysql 聚集函数需要注意的问题

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