sql is not null 与 is null 用法

 百家乐-数据     |      2020-03-03 13:20

假定表中的某些列是可选的,那么大家得以在不向该列增加值的图景下插入新记录或更新已部分记录。那象征该字段将以 NULL 值保存。

NULL 值的管理方式与其他值差别。

NULL 用作未知的或不适用的值的占位符。

批注:不可能相比 NULL 和 0;它们是不等价的。

is not null实例

5 create table Billings (6 BankerID INTEGER,7 BillingNumber INTEGER,8 BillingDate datetime,9 BillingTotal INTEGER,10 TermsID INTEGER,11 BillingDueDate datetime ,12 PaymentTotal INTEGER,13 CreditTotal INTEGER1415 );16 GO12 INSERT INTO Billings VALUES (1, 1, '2005-01-22', 165, 1,'2005-04-22',123,321);3 GO

(1 rows affected)1 INSERT INTO Billings VALUES (2, 2, '2001-02-21', 165, 1,'2002-02-22',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (3, 3, '2003-05-02', 165, 1,'2005-04-12',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (4, 4, '1999-03-12', 165, 1,'2005-04-18',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (5, 5, '2000-04-23', 165, 1,'2005-04-17',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (6, 6, '2001-06-14', 165, 1,'2005-04-18',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (7, 7, '2002-07-15', 165, 1,'2005-04-19',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (8, 8, '2003-08-16', 165, 1,'2005-04-20',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (9, 9, '2004-09-17', 165, 1,'2005-04-21',123,321);2 GO

(1 rows affected)1 INSERT INTO Billings VALUES (0, 0, '2005-10-18', 165, 1,'2005-04-22',123,321);2 GO

(1 rows affected)123 SELECT *4 FROM Billings5 WHERE BillingTotal IS NOT NULL6 GOBankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal----------- -------------


----------------------- ------------ ----------- 1 1 2005-01-22 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321 2 2 2001-02-21 00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321 3 3 2003-05-02 00:00:00.000 165 1 2005-04-12 00:00:00.000 123 321 4 4 1999-03-12 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321 5 5 2000-04-23 00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321 6 6 2001-06-14 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321 7 7 2002-07-15 00:00:00.000 165 1 2005-04-19 00:00:00.000 123 321 8 8 2003-08-16 00:00:00.000 165 1 2005-04-20 00:00:00.000 123 321 9 9 2004-09-17 00:00:00.000 165 1 2005-04-21 00:00:00.000 123 321 0 0 2005-10-18 00:00:00.000 165 1 2005-04-22 00:00:00.000 123 321

(10 rows affected)12 drop table Billings;3 GO

is null 剖断为空的内容

45 CREATE TABLE titleauthor(6 au_id varchar(20),7 title_id varchar(20),8 au_ord tinyint NULL,9 royaltyper int NULL10 )11 GO12 insert titleauthor values(null, '2', 1, 60)3 insert titleauthor values('2', '3', 1, 100)4 insert titleauthor values('3', '4', 1, 100)5 insert titleauthor values('4', '5', 1, 100)6 insert titleauthor values('5', '6', 1, 100)7 insert titleauthor values('6', '7', 2, 40)8 insert titleauthor values('7', '8', 1, 100)9 insert titleauthor values('8', '9', 1, 100)10 GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)12 select * from titleauthor where au_id is null;3 GOau_id title_id au_ord royaltyper-------------------- -------------------- ------ -----------NULL 2 1 60

(1 rows affected)1 select * from titleauthor where au_id = null;2 GOau_id title_id au_ord royaltyper--------------------


(0 rows affected)