Usage sharing of IS NULL statement in Mysql _mysql

Usage of IS NULL statement in MySQL database

Note that in MySQL, 0 or null means false and other values mean true. The default truth value of a Boolean operation is 1.

The special handling of NULL is in the previous section, in order to determine which animal is no longer alive, the reason for using death is not NULL instead of using death!= null.

In group BY, two null values are treated as the same.

When you execute order BY, if you run the ORDER by ... ASC, the null value appears at the front, and if you run the ORDER by ... desc, the null value appears at the last side.

A common error with null operations is that you cannot insert a 0 or an empty string in a aligns defined as not NULL, but that is not the case. A value where null indicates "no value"

。 You can easily test with IS [NOT] null

is null or = NULL

Mysql>
Mysql> CREATE TABLE Topic (
-> topicid smallint NOT NULL auto_increment primary key,
-> name varchar is 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 () is 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 ', 5, 3, ' popular ', ' Rock '),
-> (' CSS ', 5, 3, ' Classical ', ' opera '),
-> (' C Sharp ', 4, 1, ' popular ', ' Jazz '),
-> (' C ', 9, 4, 2, ' classical ', ' dance '),
-> (' C + + ', 2, 5, ' classical ', ' general '),
-> (' Perl ', 6, 8, ' classical ', ' vocal '),
-> (' Python ', 2, 6, ' popular ', ' Blues '),
-> (' php ', 3, ten, ' popular ', ' Jazz '),
-> (' asp.net ', A, M, ' popular ', ' country '),
-> (' vb.net ', 5,, ' Popular ', ' New Age '),
-> (' Vc.net ',,, one, one, ' popular ', ' New Age '),
-> (' UML ', "classical", "General"),
-> (' www.java2s.com ', M, M, ' classical ', ' dance '),
-> (' Oracle ', M, M, ' classical ', ' general '),
-> (' Pl/sql ', M, 5, ' Classical ', ' opera '),
-> (' SQL Server ', 8, ' classical ', ' general ');
Query OK, 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 |
+---------+----------------+---------+---------+----------+------------+----------+---------------------+
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 Unequal Space
Null means "No value" or www.3ppt.com "Unknown value", and it is considered a distinct value. In order to test NULL, you cannot use arithmetic comparison operators such as =, < or!=
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 |
+----------------+------------+----------+
Rows in Set (0.00 sec)

Mysql>
mysql> drop table topic;
Query OK, 0 rows Affected (0.00 sec)