mysql> show create table test;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------+
mysql> select * from test;
+------+
| a |
+------+
| 1 |
| 2 |
| NULL |
+------+
mysql> select avg(a) from test;
+--------+
| avg(a) |
+--------+
| 1.5000 |
+--------+
mysql> select count(a) from test;
+----------+
| count(a) |
+----------+
| 2 |
+----------+
mysql> select count(a),avg(a) from test;
+----------+--------+
| count(a) | avg(a) |
+----------+--------+
| 2 | 1.5000 |
+----------+--------+
mysql> select count(a),avg(a) from test where a is not null;
+----------+--------+
| count(a) | avg(a) |
+----------+--------+
| 2 | 1.5000 |
+----------+--------+
mysql> select avg(a) from test where a is null;
+--------+
| avg(a) |
+--------+
| NULL |
+--------+
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
说明:NULL值
AVG()函数忽略列值为NULL的行。
MAX()函数忽略列值为NULL的行。
MIN()函数忽略列值为NULL的行。
SUM()函数忽略列值为NULL的行。
COUNT()函数有两种使用方式:
使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。