mysql中is null语句的用法分享

mysql数据库中is null语句的用法

注意在mysql中，0或 null意味着假而其它值意味着真。布尔运算的默认真值是1。

对null的特殊处理即是在前面的章节中，为了决定哪个动物不再是活着的，使用death is not null而不使用death ！= null的原因。

在group by中，两个null值视为相同。

执行order by时，如果运行 order by ... asc，则null值出现在最前面，若运行order by ... desc，则null值出现在最后面。

null操作的常见错误是不能在定义为not null的列内插入0或空字符串，但事实并非如此。在null表示"没有数值"的地方有数值

。使用is [not] null则可以很容易地进行测试

is null or = null

mysql>
mysql> create table topic（
    ->    topicid     smallint not null auto_increment primary key,
    ->    name        varchar（50） not null,
    ->    instock     smallint unsigned not null,
    ->    onorder     smallint unsigned not null,
    ->    reserved    smallint unsigned not null,
    ->    department enum（"classical", "popular"） not null,
    ->    category    varchar（20） not null,
    ->    rowupdate   timestamp not null
    -> ）;
query ok, 0 rows affected （0.02 sec）

mysql>
mysql>
mysql> insert into topic （name,          instock, onorder, reserved, department,   category） values
    ->                   （"java",          10,      5,       3,        "popular",    "rock"）,
    ->                   （"css",    10,      5,       3,        "classical", "opera"）,
    ->                   （"c sharp",       17,      4,       1,        "popular",    "jazz"）,
    ->                   （"c",             9,       4,       2,        "classical", "dance"）,
    ->                   （"c++",           24,      2,       5,        "classical", "general"）,
    ->                   （"perl",          16,      6,       8,        "classical", "vocal"）,
    ->                   （"python",        2,       25,      6,        "popular",    "blues"）,
    ->                   （"php",           32,      3,       10,       "popular",    "jazz"）,
    ->                   （"asp.net",       12,      15,      13,       "popular",    "country"）,
    ->                   （"vb.net",        5,       20,      10,       "popular",    "new age"）,
    ->                   （"vc.net",        24,      11,      14,       "popular",    "new age"）,
    ->                   （"uml",           42,      17,      17,       "classical", "general"）,
    ->                   （"www.java2s.com",25,      44,      28,       "classical", "dance"）,
    ->                   （"oracle",        32,      15,      12,       "classical", "general"）,
    ->                   （"pl/sql",        20,      10,      5,        "classical", "opera"）,
    ->                   （"sql server",    23,      12,      8,        "classical", "general"）;
query ok, 16 rows affected （0.00 sec）
records: 16 duplicates: 0 warnings: 0

mysql>
mysql> select * from topic;
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
| topicid | name           | instock | onorder | reserved | department | category | rowupdate           |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
|       1 | java           |      10 |       5 |        3 | popular    | rock     | 2007-07-23 19:09:45 |
|       2 | javascript     |      10 |       5 |        3 | classical | opera    | 2007-07-23 19:09:45 |
|       3 | c sharp        |      17 |       4 |        1 | popular    | jazz     | 2007-07-23 19:09:45 |
|       4 | c              |       9 |       4 |        2 | classical | dance    | 2007-07-23 19:09:45 |
|       5 | c++            |      24 |       2 |        5 | classical | general | 2007-07-23 19:09:45 |
|       6 | perl           |      16 |       6 |        8 | classical | vocal    | 2007-07-23 19:09:45 |
|       7 | python         |       2 |      25 |        6 | popular    | blues    | 2007-07-23 19:09:45 |
|       8 | php            |      32 |       3 |       10 | popular    | jazz     | 2007-07-23 19:09:45 |
|       9 | asp.net        |      12 |      15 |       13 | popular    | country | 2007-07-23 19:09:45 |
|      10 | vb.net         |       5 |      20 |       10 | popular    | new age | 2007-07-23 19:09:45 |
|      11 | vc.net         |      24 |      11 |       14 | popular    | new age | 2007-07-23 19:09:45 |
|      12 | uml            |      42 |      17 |       17 | classical | general | 2007-07-23 19:09:45 |
|      13 | www.java2s.com |      25 |      44 |       28 | classical | dance    | 2007-07-23 19:09:45 |
|      14 | oracle         |      32 |      15 |       12 | classical | general | 2007-07-23 19:09:45 |
|      15 | pl/sql         |      20 |      10 |        5 | classical | opera    | 2007-07-23 19:09:45 |
|      16 | sql server     |      23 |      12 |        8 | classical | general | 2007-07-23 19:09:45 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
16 rows in set （0.00 sec）

mysql>
mysql>
mysql> select name, department, category
    -> from topic
    -> where category is null
    -> order by name;
empty set （0.00 sec）

mysql>
mysql>
mysql>
mysql> select name, department, category
    -> from topic
    -> where category = null
    -> order by name;
empty set （0.00 sec）

mysql>
mysql>
mysql> drop table topic;
query ok, 0 rows affected （0.00 sec）

<=>null: null不等空
null意味着“没有值”或www.3ppt.com“未知值”，且它被看作与众不同的值。为了测试null，你不能使用算术比较操作符例如=、<或！=
mysql>
mysql> select name, department, category
    -> from topic
    -> where category<=>null
    -> order by name;
empty set （0.00 sec）

mysql>
mysql> drop table topic;
query ok, 0 rows affected （0.02 sec）

is not null

mysql>
mysql> drop table topic;
query ok, 0 rows affected （0.00 sec）