MySQL中is null语句的用法分享

这篇文章主要为大家详细介绍了MySQL中is null语句的用法分享，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

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> 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> drop table topic;query ok, 0 rows affected (0.00 sec)

注：关于MySQL中is null语句的用法分享的内容就先介绍到这里，更多相关文章的可以留意