Sql 简明教程

SQL - IS NOT NULL

NULL 值表示缺失或未知的值。它看起来是空白的，不包含任何数据。非常重要的是要明白，NULL 值与零值或包含空格的字段不同。要检查 null 值，我们可以使用两个基本运算符。

A NULL value indicates a missing or unknown value. It appears to be blank and does not contain any data. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. For checking null values we can use two basic operators.

  1. IS NULL

  2. IS NOT NULL

The SQL IS NOT NULL Operator

SQL IS NOT NULL 运算符用于通过验证特定列是否具有非 null 值来筛选数据。此运算符可以与 SQL 语句（如 SELECT、UPDATE 和 DELETE）一起使用。

The SQL IS NOT NULL operator is used to filter data by verifying whether a particular column has a not-null values. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.

通过使用 IS NOT NULL 运算符，我们只能获取特定列中包含有效数据的记录。

By using the IS NOT NULL operator, we can only fetch the records that contain valid data in a particular column.

Syntax

以下是 SQL IS NOT NULL 运算符的语法 −

Following is the syntax of the SQL IS NOT NULL operator −

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −

Firstly, let us create a table named CUSTOMERS using the following query −

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

现在，使用如下所示的 INSERT 语句向此表中插入值：

Now, insert values into this table using the INSERT statement as follows −

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

该表将按如下方式创建：

The table will be created as follows −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

NULL

2

Khilan

25

NULL

1500.00

3

Kaushik

NULL

Kota

2000.00

4

Chaitali

25

Mumbai

NULL

5

Hardik

27

Bhopal

8500.00

6

Komal

NULL

Hyderabad

4500.00

7

Muffy

24

NULL

10000.00

Example

在以下查询中，我们将返回 CUSTOMERS 表中所有 ADDRESS 不为 null 的记录 −

In the following query, we are going to return all the records from the CUSTOMERS table where the ADDRESS is not null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL;

Output

在执行以上查询时，它将生成如下所示的输出 −

On executing the above query, it will generate the output as shown below −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

NULL

3

Kaushik

NULL

Kota

2000.00

4

Chaitali

25

Mumbai

NULL

5

Hardik

27

Bhopal

8500.00

6

Komal

NULL

Hyderabad

4500.00

IS NOT NULL with COUNT() Function

我们可以将 IS NOT NULL 运算符与 SQL COUNT() 函数一起使用，以仅计算特定列中的非 null 值。

We can use the IS NOT NULL operator along with the SQL COUNT() function to count only the non-null values in a specific column.

Syntax

以下是 COUNT() 函数中 IS NOT NULL 运算符的语法 −

Following is the syntax of IS NOT NULL operator with the COUNT() function −

SELECT COUNT(column_name)
FROM table_name
WHERE condition IS NOT NULL;

Example

以下查询返回 CUSTOMERS 表中 SALARY 列不为 null 的所有行的计数 −

The following query returns the count of all rows in the CUSTOMERS table where the SALARY column is not null −

SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NOT NULL;

Output

下面显示了产生的输出：

The output produced is as shown below −

COUNT(*)

5

IS NOT NULL with DELETE Statement

在 SQL 中，我们可以使用 DELETE 语句和 IS NOT NULL 运算符来删除特定列中不包含 NULL 值的所有行。

In SQL, we can delete all rows that do not contain NULL values in a specific column using the DELETE statement with IS NOT NULL operator.

Syntax

以下是 SQL 中 DELETE 语句中 IS NOT NULL 运算符的语法 −

Following is the syntax of the IS NOT NULL operator with the DELETE statement in SQL −

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

在以下查询中，我们删除了 CUSTOMERS 表的 SALARY 列中不为 null 的记录 −

In the following query, we are deleting records which are not null in the SALARY column of the CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL;

Output

我们获得以下结果 −

We get the following result −

Query OK, 5 rows affected (0.02 sec)

Verification

执行下面给出的 SELECT 查询，以检查表是否已更改 −

Execute the SELECT query given below to check whether the table has been changed or not −

SELECT * FROM CUSTOMERS;

如果我们编译并运行该程序，结果如下所示 −

If we compile and run the program, the result is produced as follows −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

NULL

4

Chaitali

25

Mumbai

NULL

IS NOT NULL with UPDATE Statement

我们可以在 SQL 中将 UPDATE 语句与 IS NOT NULL 运算符配合使用，以使用非空记录更新特定列中的记录。

We can use the UPDATE statement with the IS NOT NULL operator in SQL to update records with not-null records in a particular column.

Syntax

下面是 SQL 中 UPDATE 语句与 IS NOT NULL 运算符的语法：

Following is the syntax of the IS NOT NULL operator with the UPDATE statement in SQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

截断 CUSTOMERS 表并重新向其中插入全部 7 条记录。以下查询将 SALARY 列中的所有值加 5000，这里 salary 值不为空：

Truncate the CUSTOMERS table and reinsert all the 7 records into it again. The following query, increments all the values in the SALARY column of the with 5000, where the salary value is not null −

UPDATE CUSTOMERS SET SALARY = SALARY+5000 WHERE SALARY IS NOT NULL;

Output

执行上面程序时，将获得如下所示的输出：

When we execute the program above, the output is obtained as follows −

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

Verification

要检查表是否已更新，请执行下面的 SELECT 查询：

To check whether the table has been updated or not, execute the SELECT query below −

SELECT * FROM CUSTOMERS;

该表显示如下：

The table is displayed as follows −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

NULL

2

Khilan

25

NULL

6500.00

3

Kaushik

NULL

Kota

7000.00

4

Chaitali

25

Mumbai

NULL

5

Hardik

27

Bhopal

13500.00

6

Komal

NULL

Hyderabad

9500.00

7

Muffy

24

NULL

15000.00