程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql中is null語句的用法分享

mysql中is null語句的用法分享

編輯：MySQL綜合教程

mysql中is null語句的用法分享。本站提示廣大學習愛好者：（mysql中is null語句的用法分享）文章只能為提供參考，不一定能成為您想要的結果。以下是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