在MySQL中使用NULL和IS NOT NULL
对于NULL，MySQL中有两个属性-
一片空白
不为空。
为了理解上述概念，让我们创建一个表。创建表的查询如下-
mysql> create table NULL_Demo
-> (
-> UserId int,
-> UserName varchar(20),
-> UserAddress varchar(20)
-> );示例
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into NULL_Demo values(12345,'John',NULL); mysql> insert into NULL_Demo values(2345,'Carol','UK'); mysql> insert into NULL_Demo values(233444,NULL,NULL); mysql> insert into NULL_Demo values(NULL,NULL,NULL);
使用select语句显示表中的所有记录。查询如下-
mysql> select *from NULL_Demo;
输出结果
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | | 2345 | Carol | UK | | 233444 | NULL | NULL | | NULL | NULL | NULL | +--------+----------+-------------+ 4 rows in set (0.00 sec)
这是适用于ISNULL和ISNOTNULL属性的查询。
情况1-ISNOTNULL
查询如下-
mysql> select *from NULL_Demo where UserId = 2345 AND UserName = 'Carol' AND UserAddress IS NOT NULL;
以下是根据上述查询中设置的条件显示NOTNULL记录的输出-
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 2345 | Carol | UK | +--------+----------+-------------+ 1 row in set (0.00 sec)
情况2-NOTNULL
查询如下-
mysql> select *from NULL_Demo where UserName = 'John' AND UserAddress IS NULL;
以下是根据上述查询中设置的条件显示NULL记录的输出-
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | +--------+----------+-------------+ 1 row in set (0.00 sec)