Mysql 简明教程
MySQL - IS NOT NULL Operator
MySQL 表中的 NULL 值表示缺失或未知值。它似乎是空白的，不包含任何数据。这不同于零值。
A NULL value in a MySQL table indicates a missing or unknown value. It appears to be blank and does not contain any data. This is different from zero values.
非常重要的是要明白一个 NULL 值不同于一个零值或一个包含空格的字段。为了检查表列中的 NULL 值，我们可以使用两个基本运算符：
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. To check for NULL values in a table column, we can use two basic operators:
-
IS NULL
-
IS NOT NULL
MySQL IS NOT NULL Operator
MySQL IS NOT NULL 运算符用于验证一个特定的列是否有一个非空值，或者是没有。这个运算符可以与诸如 SELECT、UPDATE 和 DELETE 这类 SQL 语句一起使用。
The MySQL IS NOT NULL operator is used to verify whether a particular column has a non-null value or not. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.
通过在条件子句中使用 IS NOT NULL 运算符，我们仅能够获取包含特定列中有效数据的记录。
By using the IS NOT NULL operator in a conditional clause, we can only fetch the records that contain valid data in a particular column.
Syntax
以下是 MySQL 中 IS NOT NULL 的语法——
Following is the syntax of IS NOT NULL in MySQL −
SELECT column_name1, column_name2, ...
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) NOT NULL,
AGE INT,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);在以下查询中，我们使用 INSERT 语句向表中插入值 −
In the following query, we are using the INSERT statement to insert values to the table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', NULL),
(2, 'Khilan', 25, 'Delhi', 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, 'Indore', 10000.00);该表创建如下 −
The table is created as follows −
IS NOT NULL with COUNT() function
我们可以将 IS NOT NULL 运算符与 MySQL COUNT() 函数一起使用，来只计数特定列中的非空值。
We can use the IS NOT NULL operator along with the MySQL COUNT() function to count only the non-null values in a specific column(s).
Syntax
以下是 MySQL 中 IS NOT NULL 与 COUNT() 函数的语法——
Following is the syntax of the IS NOT NULL with COUNT() function in MySQL −
SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NOT NULL;IS NOT NULL with UPDATE statement
在 MySQL 中，我们可以使用带有 IS NOT NULL 运算符的 UPDATE 语句，来更新特定列中的所有非空行。
In MySQL, we can update all the non-null rows in a specific column(s) using the UPDATE statement with IS NOT NULL operator.
Syntax
以下是 MySQL 中 IS NOT NULL 运算符与 UPDATE 语句的语法——
Following is the syntax of the IS NOT NULL operator with the UPDATE statement in MySQL -
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;IS NOT NULL with DELETE statement
在 MySQL 中，我们可以使用带有 IS NOT NULL 运算符的 DELETE 语句，来删除特定列中的所有非空行。
In MySQL, we can delete all the non-null rows in a specific column(s) using the DELETE statement with IS NOT NULL operator.
Syntax
以下是 MySQL 中带 DELETE 语句的 IS NOT NULL 运算符的语法 -
Following is the syntax of the IS NOT NULL operator with the DELETE statement in MySQL -
DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;IS NOT NULL Operator Using Client Program
除了使用 SQL 查询在 MySQL 服务器上执行 IS NOT NULL 运算符外，我们还可以使用客户端程序执行它。
In addition to executing the IS NOT NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.