Mysql is null usage

Mysql tutorial is null usage

Note that in mysql, 0 or null means false while other values mean true. The default true value of Boolean operations is 1.

The special processing of null is in the previous chapter. To determine which animal is no longer alive, use death is not null instead of death! = Null original

Because.

In group by, the two null values are considered to be the same.

When order by is executed, if order by... asc is run, the null value appears at the beginning. If order by... desc is run, the null value appears at the end.

A common error in null operations is that 0 or an empty string cannot be inserted in a column defined as not null, but this is not the case. Where null indicates "no value", there is a value.

. Using is [not] null can be easily tested

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 '),
-> ('Webpage effect ', 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 tutorial. 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 | 19:09:45 |
| 2 | javascript | 10 | 5 | 3 | classical | opera | 19:09:45 |
| 3 | c sharp | 17 | 4 | 1 | popular | jazz | 19:09:45 |
| 4 | c | 9 | 4 | 2 | classical | dance | 19:09:45 |
| 5 | c ++ | 24 | 2 | 5 | classical | general | 19:09:45 |
| 6 | perl | 16 | 6 | 8 | classical | vocal | 19:09:45 |
| 7 | python | 2 | 25 | 6 | popular | blues | 19:09:45 |
| 8 | php | 32 | 3 | 10 | popular | jazz | 19:09:45 |
| 9 | asp.net tutorial | 12 | 15 | 13 | popular | country | 19:09:45 |
| 10 | vb.net | 5 | 20 | 10 | popular | new age | 19:09:45 |
| 11 | vc.net | 24 | 11 | 14 | popular | new age | 19:09:45 |
| 12 | uml | 42 | 17 | 17 | classical | general | 19:09:45 |
| 13 | www.java2s.com | 25 | 44 | 28 | classical | dance | 19:09:45 |
| 14 | oracle | 32 | 15 | 12 | classical | general | 19:09:45 |
| 15 | pl/SQL | 20 | 10 | 5 | classical | opera | 19:09:45 |
| 16 | SQL server | 23 | 12 | 8 | classical | general | 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, not equal to null
Null means "no value" or "unknown value", and it is regarded as a different value. 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 |
+ ---------------- + ------------ + ---------- +
16 rows in set (0.00 sec)

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