NULL()

ISNULL()、IFNULL() 和 COALESCE() 函数

NULL案例

教师表teachers中教师姓名为'Linghu Chong'中邮箱为NULL值，我们可以通过该记录对比学习ISNULL、IFNULL和COALESCE

SELECT name, email, ISNULL(email), IFNULL(email,0), COALESCE(email,0) 
FROM teachers;

执行输出结果

mysql> SELECT name, email, ISNULL(email), IFNULL(email,0), COALESCE(email,0) FROM teachers;
+------------------+---------------------------+---------------+---------------------------+---------------------------+
| name             | email                     | ISNULL(email) | IFNULL(email,0)           | COALESCE(email,0)         |
+------------------+---------------------------+---------------+---------------------------+---------------------------+
| Eastern Heretic  | [e-mail] |             0 | [e-mail] | [e-mail] |
| Northern Beggar  | [e-mail]    |             0 | [e-mail]    | [e-mail]    |
| Western Venom    | [e-mail]     |             0 | [e-mail]     | [e-mail]     |
| Southern Emperor | [e-mail]   |             0 | [e-mail]   | [e-mail]   |
| Linghu Chong     | NULL                      |             1 | 0                         | 0                         |
+------------------+---------------------------+---------------+---------------------------+---------------------------+
5 rows in set (0.00 sec)

NULL 总结

从上面实例可以看出

ISNULL会根据该字段是否为NULL值返回0或1，如果是NULL则返回1，不是则返回0

IFNULL(column_name, value)函数接收两个参数，第一个参数是列名，第二个如果该列某个字段是NULL则返回value值

COALESCE(column_name, value)是和IFNULL是同一个用法

练习题 NULL

题目描述：查询教师表teachers，计算所有教师的平均年龄，如果没有邮箱年龄按0计算，输出平均年龄应为四舍五入整数，并用别名avg_teacher_age表示

SELECT ROUND( AVG( age*(NOT ISNULL(email)) ), 0) as avg_teacher_count
FROM teachers;

目标输出结果

mysql> SELECT ROUND( AVG( age*(NOT ISNULL(email)) ), 0) as avg_teacher_count
    -> FROM teachers;
+-------------------+
| avg_teacher_count |
+-------------------+
|                18 |
+-------------------+
1 row in set (0.00 sec)