MySQL系列关于NUll值的经验总结分析教程

create table test_null (
    id int,
    name varchar(20),
    chinese int,
    math int,
    english int
) charset=utf8;

insert into test_null 
values
(1,null,80,70,68),
(2,'张三',60,null,null),
(3,'李四',null,90,80),
(4,'王五',90,60,75),
(5,null,null,50,95);

# null值不能使用 == 或 != 来比较
# 下面两种用法都是错误的
select *
from test_null
where name == null;

select *
from test_null
where name != null;

# null值一般使用 is null 或 is not null 来比较
# 下面两种用法才是正确的
select *
from test_null
where name is null;

select *
from test_null
where name is not null;

select 	
	*,(chinese+math+english) as 总分
from test_null;

select 
    sum(chinese) 语文总分,
    sum(math) 数学总分,
    sum(english) 外语总分
from test_null

select 
    	id,
    	coalesce(name,'无名氏') name,
    	coalesce(chinese,0) chinese,
    	ifnull(math,0) math,
    	ifnull(english,0) english
from test_null;