Mysql 简明教程

MySQL - NOT EQUAL Operator

MySQL NOT EQUAL Operator

MySQL NOT EQUAL 运算符用于比较两个值,如果它们不相等则返回 true。它由 "<>""!=" 表示。这两者的区别在于 <> 遵循 ISO 标准,但 != 不遵循。所以,建议使用 <> 运算符。

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by "<>" and "!=". The difference between these two is that <> follows the ISO standard, but != doesn’t. So, it is recommended to use the <> operator.

我们可以在 WHERE 子句中使用此运算符基于特定条件筛选记录,在 GROUP BY 子句中使用此运算符对结果进行分组。

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: 使用此运算符时,默认情况下,比较将区分大小写。

Note: The comparison is case-sensitive by default when using this operator with text values.

Syntax

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

Following is the syntax of the NOT EQUAL operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

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 NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询使用了 INSERT INTO 语句向上述创建的表中添加了 7 条记录 −

The below query uses INSERT INTO statement to add 7 records into 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 retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

NOT EQUAL with String Values

在 MySQL 中,我们还可以使用 NOT EQUAL 来比较两个字符串值。如果两个值不相同,则返回 true。我们可以在 SQL 语句的 WHERE 子句中使用“<>”或“!=”,并排除匹配特定值的行。

In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use "<>" or "!=" in the WHERE clause of a SQL statement and exclude rows that match a specific value.

Example

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

In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not "Khilan".

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

Output

以上代码的输出如下所示 −

The output of the above code is as shown below −

NOT EQUAL with GROUP BY Clause

MySQL 的 NOT EQUAL 运算符可以与 GROUP BY 子句一起使用。它将按不等于指定文本值的值对结果进行分组。

MySQL’s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value.

聚合函数(例如 COUNT()、MAX()、MIN()、SUM() 和 AVG())经常与 GROUP BY 语句一起使用。

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

在此查询中,我们正在计算“CUSTOMERS”表中每个“AGE”具有不同的“ID”值记录的数量。我们排除“AGE”等于“22”的记录,并基于“AGE”列对结果进行分组。

In this query, we are counting the number of records with distinct 'ID' values for each 'AGE' in the 'CUSTOMERS' table. We are excluding records where 'AGE' is equal to '22', and grouping the results based on the 'AGE' column.

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE <> '22' GROUP BY AGE;

Output

NOT EQUAL with Multiple Conditions

根据情况,NOT EQUAL 运算符可以与 WHERE 子句中的多个条件一起使用,以筛选出匹配特定条件的行。

Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

在这里,我们将选择工资大于“2000”或等于“2000”的所有客户。同时,客户不能来自“Bhopal”。

Here, we are going to select all the customers whose salary is either ">2000" or "=2000". At the same time, the customer must not be from "Bhopal".

SELECT * FROM CUSTOMERS
WHERE ADDRESS <> 'Bhopal' AND (SALARY>'2000' OR SALARY='2000');

Output

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

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

Negating a Condition Using NOT EQUAL

MySQL NOT EQUAL 运算符可以与 NOT 运算符结合使用,以否定条件并筛选出满足特定条件的行。

The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition.

Example

以下查询检索“CUSTOMERS”表中“SALARY”等于“2000”的所有行 −

The following query retrieves all rows from the "CUSTOMERS" table where the "SALARY" is equal to '2000' −

SELECT * FROM CUSTOMERS
WHERE NOT SALARY != '2000';

Output

当查询执行后,它会生成以下输出,如下所示 −

When the query gets executed it will generate the following output as shown below −

NOT EQUAL Operator Using a Client Program

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

Besides using MySQL queries to perform the NOT EQUAL 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 −