《MYSQL教程mysql中is null語句的用法分享》要點：
本文介紹了MYSQL教程mysql中is null語句的用法分享，希望對您有用。如果有疑問，可以聯系我們。

mysql數據庫中is null語句的用法MYSQL入門

注意在mysql中,0或 null意味著假而其它值意味著真.布爾運算的默認真值是1.MYSQL入門

對null的特殊處理即是在前面的章節中,為了決定哪個動物不再是活著的,使用death is not null而不使用death != null的原因.MYSQL入門

在group by中,兩個null值視為相同.MYSQL入門

執行order by時,如果運行 order by ... asc,則null值出現在最前面,若運行order by ... desc,則null值出現在最后面.MYSQL入門

null操作的常見錯誤是不能在定義為not null的列內插入0或空字符串,但事實并非如此.在null表示"沒有數值"的地方有數值MYSQL入門

.使用is [not] null則可以很容易地進行測試MYSQL入門

is null or = nullMYSQL入門

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>
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: 0MYSQL入門

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>
mysql> select name, department, category
??? -> from topic
??? -> where category is null
??? -> order by name;
empty set (0.00 sec)MYSQL入門

mysql>
mysql>
mysql>
mysql> select name, department, category
??? -> from topic
??? -> where category = null
??? -> order by name;
empty set (0.00 sec)MYSQL入門

mysql>
mysql>
mysql> drop table topic;
query ok, 0 rows affected (0.00 sec)MYSQL入門

?<=>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>
mysql> drop table topic;
query ok, 0 rows affected (0.02 sec)MYSQL入門

is not nullMYSQL入門

mysql> select name, department, category
??? -> from topic
??? -> where category is not null
??? -> order by name;
+----------------+------------+----------+
| name?????????? | department | category |
+----------------+------------+----------+
| asp.net??????? | popular??? | country? |
| c????????????? | classical? | dance??? |
| c sharp??????? | popular??? | jazz???? |
| c++??????????? | classical? | general? |
| java?????????? | popular??? | rock???? |
| javascript???? | classical? | opera??? |
| oracle???????? | classical? | general? |
| perl?????????? | classical? | vocal??? |
| php??????????? | popular??? | jazz???? |
| pl/sql???????? | classical? | opera??? |
| python???????? | popular??? | blues??? |
| sql server???? | classical? | general? |
| uml??????????? | classical? | general? |
| vb.net???????? | popular??? | new age? |
| vc.net???????? | popular??? | new age? |
| www.java2s.com | classical? | dance??? |
+----------------+------------+----------+
16 rows in set (0.00 sec)MYSQL入門

mysql>
mysql> drop table topic;
query ok, 0 rows affected (0.00 sec)MYSQL入門

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4737.html