SQL如何增删查改字段值为null的记录

数据库中建表的时候,允许某些字段为空可以简化很多操作,但是,字段为空(null)值,在增(insert)删(delete)查(select)改(update)操作时,所使用的运算符或表达式还是不完全一样的。

插入字段值为null的记录

insert插入操作如果要插入一条记录,其中的某些字段值是null,有两种方法。一种是直接忽略该字段,一种是直接插入null值。注意不要写成’null’,因为null是一个特定的值,不是一个字符串。

假设User表有Id、Name、Age、City四个字段,如果要插入一条City字段值为null的记录:

1
2
3
4
5
/** 1、直接忽略City字段 **/
INSERT INTO USER (Id, Name, Age) VALUES ('0003', '张三', 29)

/** 2、设置该字段值为null **/
INSERT INTO USER (Id, Name, Age, City) VALUES ('0003', '张三', 29, NULL)

删除字段值为null或不为null的记录

delete删除操作如果要删除记录,条件时某些字段是null或者不是null,要通过“字段名 is null”或者“字段名 is not null”来判断,不能使用“字段名 = null”判断。

假设User表有Id、Name、Age、City四个字段,如果要删除所有City字段值为null的记录:

1
2
3
4
5
/** 1、使用City = null,执行后影响的记录条数为0 **/
DELETE USER WHERE City = NULL

/** 2、使用City is null,执行后所有City字段为null的记录被删除 **/
DELETE USER WHERE City IS NULL

假设User表有Id、Name、Age、City四个字段,如果要删除所有City字段值不为null的记录:

1
2
3
4
5
/** 1、使用City <> null,执行后影响的记录条数为0 **/
DELETE USER WHERE City = NULL

/** 2、使用City is not null,执行后所有City字段不为null的记录被删除 **/
DELETE USER WHERE City IS NOT NULL

查询字段值为null或者不为null的记录

select查询操作如果要查询记录,条件时某些字段是null或者不是null,要通过“字段名 is null”或者“字段名 is not null”来判断,不能使用“字段名 = null”判断。

假设User表有Id、Name、Age、City四个字段,如果要查询所有City字段值为null的记录:

1
2
3
4
5
/** 1、使用City = null,执行后影响的记录条数为0 **/
SELECT * FROM USER WHERE City = NULL

/** 2、使用City is null,执行后所有City字段为null的记录被列出 **/
SELECT * FROM USER WHERE City IS NULL

假设User表有Id、Name、Age、City四个字段,如果要查询所有City字段值不为null的记录:

1
2
3
4
5
/** 1、使用City <> null,执行后影响的记录条数为0 **/
SELECT * FROM USER WHERE City = NULL

/** 2、使用City is not null,执行后所有City字段不为null的记录被删除 **/
SELECT * FROM USER WHERE City IS NOT NULL

修改记录的字段值为null

update更新操作如果要更新记录,将记录的某个字段设置为null值,要直接设置“字段名=null”,而非“字段名 is null”。

假设User表有Id、Name、Age、City四个字段,如果要修改Id为“0001”的记录的City字段值为null:

1
2
3
4
5
/** 1、使用City is null,执行后SQL Server会报错,提示有语法错误 **/
UPDATE USER SET City IS NULL WHERE Id = '0001'

/** 2、使用City = null,执行后Id为“0001”的记录的City字段值被改为null **/
UPDATE USER SET City = NULL WHERE Id = '0001'

总结

综上,对null字段的操作,什么时候用“=”符号,什么时候用“is”和“is not”呢?
当需要判断某字段是否为null时,即所有“where”的后面都是“is null”、“is not null”;
当需要设置某字段为null时,即insert into和update set之后,都要用“= null”。