Mysql 简明教程

MySQL - NOT Operator

MySQL NOT Operator

MySQL NOT 是一个逻辑运算符，允许我们从 WHERE 子句中排除特定条件或表达式。当我们需要指定要从结果表中排除的内容，而不是要包括的内容时，通常会使用此运算符。

MySQL NOT is a logical operator that allows us to exclude specific conditions or expressions from a WHERE clause. This operator is often used when we need to specify what NOT to include in the result table rather than what to include.

假设我们以印度投票系统为例，其中不允许 18 岁以下者投票。在这种情况下，我们可以使用 NOT 运算符在获取所有符合条件的选民信息时过滤掉未成年人。这有助于我们对未成年人创建例外，并且仅显示符合投票资格人员的详细信息。

Suppose we take the example of the Indian voting system, where people under 18 are not allowed to vote. In such a scenario, we can use the NOT operator to filter out minors while retrieving information about all eligible voters. This helps us create an exception for minors and only display details of those who are eligible to vote.

Syntax

以下是 MySQL 中 NOT 运算符的语法：

Following is the syntax of the NOT operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example

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

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

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

以下查询使用 INSERT 语句向上述创建的表中插入 7 条记录：

The following query uses INSERT statement to insert 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

执行以下查询以从 CUSTOMERS 表中获取所有记录：

Execute the following query to fetch all the records from CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

在以下查询中，我们正在从 CUSTOMERS 表中选择所有 ADDRESS 不为“Hyderabad”的记录。

In the following query, We are selecting all the records from the CUSTOMERS table where the ADDRESS is NOT "Hyderabad".

SELECT * FROM Customers
WHERE NOT ADDRESS = 'Hyderabad';

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

NOT with IN Operator

我们可以使用 MySQL 逻辑 NOT 运算符和 IN 关键字来消除匹配给定列表中的任何值的那些行。

We can use the MySQL logical NOT operator along with the IN keyword to eliminate the rows that match any value in a given list.

Example

使用以下查询，我们正在从 CUSTOMERS 表中获取所有 NAME 不为“Khilan”、“Chaital”和“Muffy”的记录。

Using the following query, we are fetching all the records from the CUSTOMERS table where NAME is NOT "Khilan", "Chaital", and "Muffy".

SELECT * FROM CUSTOMERS
WHERE NAME NOT IN ("Khilan", "Chaital", "Muffy");

Output

如果我们执行上述查询，则产生的结果如下：

If we execute the above query, the result is produced as follows −

NOT with IS NULL Operator

我们可以使用 MySQL 逻辑 NOT 运算符和 IS NULL 关键字来选择指定列中没有 NULL 值的行。

We can use the MySQL logical NOT operator along with the IS NULL keyword to select rows in a specified column that do not have a NULL value.

Example

在该查询中，我们正在从 CUSTOMERS 表中选择 ADDRESS 列不为 null 的所有记录。

In this query, we are selecting all the records from the CUSTOMERS table where the ADDRESS column is not null.

SELECT * FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;

Output

输出将显示为：

The output will be displayed as −

NOT with LIKE Operator

我们可以使用 MySQL 逻辑 NOT 运算符和 LIKE 关键字来选择不匹配给定模式的行。

We can use the MySQL logical NOT operator along with the LIKE keyword to select the rows that do not match a given pattern.

Example

在下面的查询中，我们正在从 CUSTOMERS 表中获取所有 NAME 列不以字母 K 开头的记录。

In the query below, we are fetching all the records from the CUSTOMERS table where the NAME column does not start with the letter K.

SELECT * FROM CUSTOMERS
WHERE NAME NOT LIKE 'K%';

Output

输出将显示为：

The output will be displayed as −

NOT with BETWEEN Operator

MySQL 的 NOT 运算符可以与 BETWEEN 关键字一起使用，以返回位于指定范围或时间间隔之外的行。

MySQL’s NOT operator can be used with the BETWEEN keyword to return rows outside a specified range or interval of time.

Example

在以下示例中，我们正在从 CUSTOMERS 表中选择所有 AGE 不在 25 到 30 之间的记录。

In the following example, we are selecting all the records from the CUSTOMERS table where the AGE is not between 25 and 30.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 25 AND 30;

Output

当我们执行以上查询时，输出如下 −

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

NOT with UPDATE Statement

MySQL 中的 UPDATE 语句可以与 WHERE 子句中的 NOT 运算符一起使用，以更新不满足特定条件的行。

The UPDATE statement in MySQL can be used along with the NOT operator in the WHERE clause to update rows that do not meet a specific condition.

Syntax

以下是 MySQL 中 NOT 运算符与 UPDATE 语句的语法：

Following is the syntax of the NOT operator with the UPDATE statement in MySQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE NOT condition ... ;

Example

在以下查询中，我们将 CUSTOMERS 的 SALARY 更新为 12000，其中 AGE 不在 25 到 30 之间。

In the following query, we are updating the SALARY of the CUSTOMERS to a value of 12000 where the AGE is not between 25 and 30.

UPDATE CUSTOMERS
SET SALARY = 12000
WHERE AGE NOT BETWEEN 25 AND 30;

Output

输出将显示为：

The output will be displayed as −

Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

Verification

使用以下查询，我们可以验证 CUSTOMERS 的 SALARY 是否已更新：

Using the below query, we can verify whether the SALARY of CUSTOMERS is updated or not −

SELECT * FROM CUSTOMERS;

Output

以上查询的输出如下所示：

The output for the query above is produced as given below −

NOT with DELETE Statement

MySQL 中的 DELETE 语句可以与 WHERE 子句中的 NOT 运算符一起使用，以删除不满足特定条件的行。

The DELETE statement in MySQL can be used along with the NOT operator in the WHERE clause to delete rows that do not meet a specific condition.

Syntax

以下是在 MySQL 中 DELETE 语句中使用 NOT 运算符的语法：

Following is the syntax of NOT operator with the DELETE statement in MySQL −

DELETE FROM table_name
WHERE NOT condition ... ;

Example

在以下查询中，我们正在从 SALARY 不在 10000 到 15000 之间的 CUSTOMERS 表中删除记录。

In the following query, we are deleting records from the CUSTOMERS table where the SALARY is not between 10000 and 15000.

DELETE FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 10000 AND 15000;

Output

Query OK, 3 rows affected (0.01 sec)

Verification

使用以下查询，我们可以验证上述操作是否成功：

Using the below query, we can verify whether the above operation is successful or not −

SELECT * FROM CUSTOMERS;

Output

执行给定的查询后，输出如下：

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

NOT Operator Using a Client Program

除了使用 MySQL 查询执行 NOT 运算符之外，我们还可以使用 Node.js、PHP、Java 和 Python 等客户端程序来实现相同的结果。

Besides using MySQL queries to perform the NOT operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

以下是此操作在各种编程语言中的语法 −

Following are the syntaxes of this operation in various programming languages −

Example

以下是这些程序 −

Following are the programs −