next up previous contents
Next: REGEXP 正規表示 Up: Clause 紀錄詢問條件 Previous: Join 結合查詢   Contents   DYWANG_HOME

NULL 值

  1. 先繼續在資料表 dcount 新增兩筆紀錄且其 dcount 都是 NULL。
    mariadb root@localhost:dyw> insert into dcount(author,dcount) values('jenny',nul
                                l),('sara',null);                                   
    Query OK, 2 rows affected
    Time: 0.006s
    
  2. 要查詢資料表 dcount 中欄位 dcount 是空的紀錄, 不能使用運算子 =, !=
    mysql> select * from dcount_tbl where dyw_count = null;
    mysql> select * from dcount_tbl where dyw_count != null;
    
  3. 要查詢資料表 dcount 中欄位 dcount 是空的紀錄,必須使用 IS NULL, IS NOT NULL 等語法。
    mariadb root@localhost:dyw> select * from dcount where dcount is null;          
    +--------+--------+
    | author | dcount |
    +--------+--------+
    | jenny  | <null> |
    | sara   | <null> |
    +--------+--------+
    2 rows in set
    Time: 0.025s
    mariadb root@localhost:dyw> select * from dcount where dcount is not null;      
    +--------+--------+
    | author | dcount |
    +--------+--------+
    | dywang | 4      |
    | linda  | 3      |
    | peter  | 2      |
    | rita   | 1      |
    +--------+--------+
    4 rows in set
    Time: 0.021s
    
  4. <=> 運算子等效 IS NULL 語法。
    mariadb root@localhost:dyw> select * from dcount where dcount <=> null;         
    +--------+--------+
    | author | dcount |
    +--------+--------+
    | jenny  | <null> |
    | sara   | <null> |
    +--------+--------+
    2 rows in set
    Time: 0.021s
    



De-Yu Wang 2020-04-07