MySQL is null usage

Source: Internet
Author: User
Tags mysql tutorial reserved

MySQL tutorial is null usage

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, using death is not NULL instead of using death!= Null's original

Because

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 '),
-> (' Web Effects ', 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 tutorials ', 3, ' popular ', ' Jazz '),
-> (' ASP tutorial. Net ', ', ', ' 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 |
| 9 2       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 Tutorials |      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 "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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.