MySQL的几个length函数以及聚合函数对于NULL的处理
数据库基于MySQL，其他数据库有些不一样
最近跟着CSDN上的一位博主的MySQL系列文章学习了下，做下笔记复习巩固下。
length函数：
char_length:字符个数
length:字节个数
bit_length:二进制个数
mysql> select length('a'),char_length('a'),bit_length('a'); +-------------+------------------+-----------------+ | length('a') | char_length('a') | bit_length('a') | +-------------+------------------+-----------------+ | 1 | 1 | 8 | +-------------+------------------+-----------------+ 1 row in set (0.00 sec) mysql> select length('啊'),char_length('啊'),bit_length('啊'); +---------------+--------------------+-------------------+ | length('啊') | char_length('啊') | bit_length('啊') | +---------------+--------------------+-------------------+ | 3 | 1 | 24 | +---------------+--------------------+-------------------+ 1 row in set (0.00 sec)
聚合函数对于NULL
COUNT(*):会对所有的NULL计数，
COUNT(1):会对所有的NULL计数,
COUNT(某列):会忽略所有的NULL,
COUNT(NULL):返回0
SUM():会忽略NULL
MAX():忽略NULL
MIN():忽略NULL
AVG():忽略NULL
上面这4个都会忽略null比较好理解：毕竟NULL无法参与计数，但是要注意是忽略NULL而不是把NULL当成0，通过这个就能说明：
mysql> select avg(v) from test_count; +--------+ | avg(v) | +--------+ | 1.5000 | +--------+ 1 row in set (0.04 sec) mysql> select * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | +------+ 3 rows in set (0.00 sec) #平均数结果是1.5，所以能证明除以的是2而不是3，能证明NULL被忽略了
group by:这个以前我还没注意，NULL列会在查询之后放在最前面
mysql> select * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | +------+ 3 rows in set (0.00 sec) mysql> select * from test_count group by v; +------+ | v | +------+ | NULL | | 1 | | 2 | +------+ 3 rows in set (0.01 sec)
order by:NULL会被认为是最小值 （工作需要，用了postgresql，order by ,null会在最后，order by desc，null在最前面）
mysql> select * from test_count order by v; +------+ | v | +------+ | NULL | | -128 | | 1 | | 2 | +------+ 4 rows in set (0.00 sec)
distinct:NULL会被消重
mysql> select distinct * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | | -128 | +------+ 4 rows in set (0.02 sec) mysql> select * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | | -128 | | NULL | | NULL | +------+ 6 rows in set (0.00 sec)
+:与NULL相加结果都为NULL
mysql> select * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | | -128 | | NULL | | NULL | +------+ 6 rows in set (0.00 sec) mysql> select 1+v from test_count; +------+ | 1+v | +------+ | 2 | | 3 | | NULL | | -127 | | NULL | | NULL | +------+ 6 rows in set (0.00 sec)
<,>,<>,=:与NULL比较都返回NULL，需要用is null 或者not null
is null和is not null：结果为真返回1，结果为假返回0
mysql> select 0<v,0>v,0<>v,0=v,v is null, v is not null from test_count; +------+------+------+------+-----------+---------------+ | 0<v | 0>v | 0<>v | 0=v | v is null | v is not null | +------+------+------+------+-----------+---------------+ | 1 | 0 | 1 | 0 | 0 | 1 | | 1 | 0 | 1 | 0 | 0 | 1 | | NULL | NULL | NULL | NULL | 1 | 0 | | 0 | 1 | 1 | 0 | 0 | 1 | | NULL | NULL | NULL | NULL | 1 | 0 | | NULL | NULL | NULL | NULL | 1 | 0 | +------+------+------+------+-----------+---------------+ 6 rows in set (0.03 sec) mysql> select * from test_count; +------+ | v | +------+ | 1 | | 2 | | NULL | | -128 | | NULL | | NULL | +------+ 6 rows in set (0.00 sec)
如果想统计NULL一共有多少行行，可以使用if函数：
mysql> select sum(if(v is null, 1, 0)) NULL一共多少行 from test_count; +---------------------+ | NULL一共多少行 | +---------------------+ | 4 | +---------------------+ 1 row in set (0.03 sec) mysql> select sum(if(v is null, 0, 1)) 非NULL一共多少行 from test_count; +------------------------+ | 非NULL一共多少行 | +------------------------+ | 3 | +------------------------+ 1 row in set (0.00 sec) mysql> select * from test_count; unt;t; +------+ | v | +------+ | 1 | | 2 | | NULL | | -128 | | NULL | | NULL | | NULL | +------+ 7 rows in set (0.00 sec)
