关于MySql函数avg的使用特性-null值不计入统计

1、使用avg函数，不统计为null的值

2、测试过程：

drop table if exists tmp_avg_t;
create table  tmp_avg_t(
id bigint unsigned auto_increment comment '',
test_code varchar(20) default '' comment '',
test_val int(11)  comment '',
primary key (id)
)comment='mysql测试avg函数'
;

insert into tmp_avg_t(test_code,test_val)
values ('test1',2),('test1',4),('test2',2)
;

select * from tmp_avg_t;

select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;

-插入null值后查看

insert into tmp_avg_t(test_code,test_val)
values ('test1',null)
;

select * from tmp_avg_t; 

 
select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;

update tmp_avg_t set test_val = 0 where id > 0 and test_code='test1' and test_val is null;

select * from tmp_avg_t;

select test_code,avg(test_val)
from tmp_avg_t
group by test_code
;

 

以上