• 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)
  • 相关阅读:
    GYM
    GYM
    GYM
    【HIHOCODER 1320】压缩字符串(区间DP)
    【HIHOCODER 1133】 二分·二分查找之k小数
    【HDU 2028】Lowest Common Multiple Plus
    【HIHOCODER 1601】 最大得分(01背包)
    概率论基础【概率论的基本概念笔记】
    【HIHOCODER 1575】 两个机器人(BFS)
    【HIHOCODER 1576】 子树中的最小权值(线段树维护DFS序)
  • 原文地址:https://www.cnblogs.com/woyujiezhen/p/13551801.html
Copyright © 2020-2023  润新知