012，奇怪的NULL

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 178
Server version: 5.5.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a              |
| b              |
| boy            |
| category       |
| girl           |
| goods          |
| m              |
| mian           |
| result         |
| t              |
| user           |
+----------------+
11 rows in set (0.00 sec)

mysql> select * from user;
+-----+-------+-----+
| uid | name  | age |
+-----+-------+-----+
|   1 | lisi  |  89 |
|   3 | lilei |  19 |
|   2 | luky  |  18 |
+-----+-------+-----+
3 rows in set (0.01 sec)

排序 user
mysql> desc user;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| uid   | int(11)              | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)          | NO   |     |         |                |
| age   | smallint(5) unsigned | NO   |     | 0       |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.21 sec)



创建一个表结构 tmp
mysql> create table tmp(
    -> id int,
    -> name varchar(20)
    -> )charset utf8 engine myisam;
Query OK, 0 rows affected (0.11 sec)



插入 tmp  值为
mysql> insert into tmp
    -> values
    -> (1,'liubei'),
    -> (2,null);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0




查询表结构
mysql> select * from tmp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liubei |
|    2 | NULL   |
+------+--------+
2 rows in set (0.00 sec)


查询liubei

mysql> select * from tmp where name='liubei';
+------+--------+
| id   | name   |
+------+--------+
|    1 | liubei |
+------+--------+
1 row in set (0.00 sec)



查询NULL,得不到想要的那个值
mysql> select * from tmp where name=null;
Empty set (0.00 sec)

mysql> select * from tmp where name!=null;
Empty set (0.00 sec)




mysql> select null = null;
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select 0=0;
+-----+
| 0=0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select null != null;
+--------------+
| null != null |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)







重点where name is null;      where name is not null;


mysql> select * from tmp where name is null;
+------+------+
| id   | name |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tmp where name is not null;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liubei |
+------+--------+
1 row in set (0.00 sec)