NULL 值

1. 先繼續在資料表 dcount 新增兩筆紀錄且其 dcount 都是 NULL。

   MariaDB root@(none):dyw> insert into dcount(author,dcount) values('jenny',null),(
                         -> 'sara',null);
   Query OK, 2 rows affected
   Time: 0.089s
   

2. 要查詢資料表 dcount，有 6 筆紀錄。

   MariaDB root@(none):dyw> select * from dcount;
   +--------+--------+
   | author | dcount |
   +--------+--------+
   | dywang | 4      |
   | linda  | 3      |
   | peter  | 2      |
   | rita   | 1      |
   | jenny  | <null> |
   | sara   | <null> |
   +--------+--------+
   
   6 rows in set
   Time: 0.024s
   

3. 要查詢資料表 dcount 中欄位 dcount 是空的紀錄， 不能使用運算子 =, !=。

   mariadb> select * from tablename where field = null;
   mariadb> select * from tablename where field != null;
   

4. 使用 IS NULL 查詢資料表 dcount 中欄位 dcount 是空的紀錄。

   MariaDB root@(none):dyw> select * from dcount where dcount is null;
   +--------+--------+
   | author | dcount |
   +--------+--------+
   | jenny  | <null> |
   | sara   | <null> |
   +--------+--------+
   
   2 rows in set
   Time: 0.057s
   

5. 使用 IS NOT NULL 查詢資料表 dcount 中欄位 dcount 不是空的紀錄

   MariaDB root@(none):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.028s
   

6. <=> 運算子等效 IS NULL 語法。

   MariaDB root@(none):dyw> select * from dcount where dcount <=> null;
   +--------+--------+
   | author | dcount |
   +--------+--------+
   | jenny  | <null> |
   | sara   | <null> |
   +--------+--------+
   
   2 rows in set
   Time: 0.028s