Sql 简明教程

SQL - NULL Values

SQL 使用术语 NULL 来表示数据库中不存在的数据值。这些值与空字符串或零不同。它们在数据库中不占用任何空间，用于表示数据字段中不存在的值或未知的值。

SQL uses the term NULL to represent a non-existent data value in the database. These values are not the same as an empty string or a zero. They don’t hold any space in the database and are used to signify the absence of a value or the unknown value in a data field.

导致值为 NULL 的一些常见原因 −

Some common reasons why a value may be NULL −

  1. The value may not be provided during the data entry.

  2. The value is not yet known.

Creating a Table without NULL Values

由于 NULL 值与任何特定数据类型无关，因此可以将其插入表中的任何列。然而，当使用“NOT NULL”关键字定义列时，只要尝试将 NULL 值插入该特定列，就会引发错误。

NULL values can be inserted in any column of a table as they are not associated with any specific data type. However, when a column is defined with the "NOT NULL" keyword, an error is raised whenever you try to insert NULL values into that specific column.

Syntax

在创建表时， NOT NULL 的基本语法如下：

The basic syntax of NOT NULL while creating a table is as follows −

CREATE TABLE table-name (
   column1 datatype NOT NULL,
   column2 datatype NOT NULL,
   ...
   columnN datatype
);

此处，NOT NULL 表示列应始终接受给定数据类型的显式值。您可以在我们未使用 NOT NULL 的列中插入 NULL 值。

Here, NOT NULL signifies that column should always accept an explicit value of the given data type. You can insert NULL values into the columns where we did not use NOT NULL.

Example

让我们使用 CREATE 语句在 SQL 数据库中创建一个名为 CUSTOMERS 的表，如下面的查询所示：

Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

让我们使用以下查询将一些值插入上述创建的表中：

Let us insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', NULL),
(7, 'Muffy', 24, 'Indore', NULL);

该表已成功在数据库中创建。

The table is successfully created in the database.

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

NULL

7

Muffy

24

Indore

NULL

现在，让我们使用 IS NOT NULL 运算符检索表中存在且不为 null 的记录：

Now, let us retrieve the records present in the table that are not null using the IS NOT NULL operator −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

上述查询将产生以下结果：

The above query would produce the following result −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

您还可以使用 SELECT 查询中的 IS NULL 运算符检索表中存在的 NULL 记录，如下所示：

You can also retrieve the NULL records present in the table using IS NULL operator in the SELECT query as shown below −

SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS
WHERE SALARY IS NULL;

上述查询将产生以下结果：

The above query would produce the following result −

ID

NAME

AGE

ADDRESS

SALARY

6

Komal

22

Hyderabad

NULL

7

Muffy

24

Indore

NULL

Updating NULL Values in a Table

您可以使用 SQL 中的 UPDATE 语句更新表中存在的 NULL 值。为此，您可以在 WHERE 子句中使用 IS NULL 运算符来筛选包含 NULL 值的行，然后使用 SET 关键字设置新值。

You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to filter the rows containing NULL values and then set the new value using the SET keyword.

Example

考虑先前创建的表，并使用 UPDATE 语句更新表中存在的 NULL 值，如下所示：

Consider the previously created table and update the NULL value(s) present in the table using the UPDATE statement as shown below −

UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;

Output

执行以上查询后，将获取如下输出——

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

让我们使用以下查询验证表中指定的记录是否已更新：

Let us verify whether the specified record(s) in the table is updated or not using the following query −

SELECT * FROM CUSTOMERS;

执行以上查询后，输出显示如下 −

On executing the above query, the output is displayed as follows −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

9000.00

7

Muffy

24

Indore

9000.00

Deleting Records with NULL Values

您可以使用 DELETE FROM 语句从表中删除包含 NULL 值的记录。您首先使用 WHERE 子句中的 IS NULL 运算符检查表是否包含 NULL 值，然后删除已筛选的记录。

You can delete records containing NULL values from a table using the DELETE FROM statement. You first check whether the table consists of NULL values using the IS NULL operator in WHERE clause and delete the records that are filtered.

Example

考虑先前创建的 CUSTOMERS 表，并使用 DELETE 语句删除表中存在的 NULL 值，如下所示：

Consider the previously created CUSTOMERS table and delete the NULL value(s) present in the table using the DELETE statement as shown below −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

执行以上查询后，将获取如下输出——

When you execute the above query, the output is obtained as follows −

Query OK, 2 rows affected (0.01 sec)

Verification

让我们使用 SELECT 语句显示表，验证表中筛选的记录是否已删除。

Let us verify whether the filtered record(s) in the table is deleted or not, by displaying the table using a SELECT statement.

SELECT * FROM CUSTOMERS;

将显示以下形式的表 −

The table will be displayed as −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00