Sqlite 简明教程
SQLite - NULL Values
SQLite NULL 是用于表示缺失值的术语。表中的 NULL 值是指似乎为空的字段中的值。
SQLite NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank.
具有 NULL 值的字段是没有任何值的字段。非常重要的一点是要了解 NULL 值不同于零值或包含空格的字段。
A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
Syntax
以下是创建表时使用 NULL 的基本语法。
Following is the basic syntax of using NULL while creating a table.
SQLite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);此处， NOT NULL 表示该列应该始终接受给定数据类型的明确值。有两个列我们没有使用 NOT NULL，这意味着这些列可能是 NULL。
Here, NOT NULL signifies that the column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL which means these columns could be NULL.
具有 NULL 值的字段在创建记录时留空。
A field with a NULL value is one that has been left blank during record creation.
Example
在选择数据时，NULL 值可能导致问题，因为将未知值与任何其他值进行比较时，结果始终是未知的，并且不会包含在最终结果中。考虑下表 COMPANY 及以下记录：
The NULL value can cause problems when selecting data, because when comparing an unknown value to any other value, the result is always unknown and not included in the final results. Consider the following table, COMPANY with the following records −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0让我们使用 UPDATE 语句将几个可空值设置为 NULL，如下：
Let us use UPDATE statement to set a few nullable values as NULL as follows −
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);现在，COMPANY 表将包含以下记录。
Now, COMPANY table will have the following records.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22
7 James 24接下来，让我们了解 IS NOT NULL 运算符，它用来列出 SALARY 不为 NULL 的所有记录。
Next, let us see the usage of IS NOT NULL operator to list down all the records where SALARY is not NULL.
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;上述 SQLite 语句将产生以下结果 -
The above SQLite statement will produce the following result −
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0以下是 IS NULL 运算符的用法，它将列出 SALARY 为 NULL 的所有记录。
Following is the usage of IS NULL operator, which will list down all the records where SALARY is NULL.
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;上述 SQLite 语句将产生以下结果。
The above SQLite statement will produce the following result.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22
7 James 24