Mysql 简明教程

MySQL - Exists Operator

MySQL Exists Operator

MySQL 中的 EXISTS 运算符用于检查表中记录是否存在。在 SELECT 语句的 WHERE 子句中使用它来验证子查询是否返回任何行。如果子查询返回至少一条记录，则返回 TRUE，否则返回 FALSE。

The EXISTS operator in MySQL checks for the existence of a record in a table. It’s used in the WHERE clause of a SELECT statement to verify if a subquery returns any rows. It returns TRUE if the subquery returns at least one record, else false.

我们还可以将运算符与 SQL 语句（如 SELECT, INSERT, UPDATE, and DELETE ）配合使用，以验证子查询中记录的存在性。

We can also use the operator with the SQL statements such as SELECT, INSERT, UPDATE, and DELETE to verify the existence of the records in subqueries.

Syntax

下面是 MySQL 中 EXISTS 运算符的语法 −

Following is the syntax of the EXISTS operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Example

在执行 EXISTS 运算符之前，让我们先生成两个不同的表，名为 CUSTOMERS 和 CARS 。在此，我们正在创建 CUSTOMERS 表 −

Before performing the EXISTS operator, let us first two different tables named CUSTOMERS and CARS. Here, we are creating the CUSTOMERS table −

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 INTO 语句将 7 条记录添加到上述已创建的表中 −

The following query uses INSERT INTO statement to add 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 present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

以下为 CUSTOMERS 表 −

Following is the CUSTOMERS table −

让我们创建一个名为 CARS 的另一个表，其中包含 ID、NAME 和车的 PRICE 的详细信息 −

Let us create another table named CARS, which contains the details such as ID of the customer, NAME and PRICE of the car −

CREATE TABLE CARS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY (ID)
);

以下查询将 3 条记录插入到上述已创建的表中 −

The following query inserts 3 records into the above-created table −

INSERT INTO CARS (ID, NAME, PRICE) VALUES
(2, 'Maruti Swift', 450000),
(4, 'VOLVO', 2250000),
(7, 'Toyota', 2400000);

执行以下查询以获取 CARS 表中存在的所有记录 −

Execute the below query to fetch all the records present in the CARS table −

SELECT * FROM CARS;

以下是 CARS 表 −

Following is the CARS table −

EXISTS operator with SELECT statement

MySQL 中的 SELECT 语句用于从一个或多个表中检索数据。EXISTS 运算符可与 SELECT 语句一起使用，以检查是否存在与特定条件匹配的行。

The SELECT statement in MySQL is used to retrieve data from one or more tables. The EXISTS operator can be used with the SELECT statement to check if rows exist that match a specific condition.

Example

现在，让我们获取价格高于 2,000,000 的客户列表 −

Now, let us fetch the list of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS
WHERE EXISTS
(SELECT PRICE FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND PRICE > 2000000);

Output

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

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

EXISTS Operator with UPDATE statement

MySQL EXISTS 运算符可与 UPDATE 语句一起使用，以根据另一个表中匹配行的存在情况，更新表中的行。

The MySQL EXISTS operator can be used with the UPDATE statement to update the rows in a table based on the existence of rows matching in another table.

Example

在此查询中，我们使用 EXISTS 运算符将匹配 CARS 表中的 ID 的所有客户的名称更新为“Kushal” −

In this query, we are using the EXISTS operator to UPDATE the name 'Kushal' to all of the customers whose ID is equal to the ID of the CARS table −

UPDATE CUSTOMERS
SET NAME = 'Kushal'
WHERE EXISTS
(SELECT NAME FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

正如我们观察到的输出，3 行已被修改 −

As we can observe the output, 3 rows have been modified −

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

Verification

要验证更改是否反映在 CUSTOMERS 表中，请执行以下查询 −

To verify whether the changes are reflected in the CUSTOMERS table, execute the following query −

SELECT * FROM CUSTOMERS;

以下是 CUSTOMERS 表的显示方式 −

The CUSTOMERS table is displayed as follows −

EXISTS Operator with DELETE statement

MySQL EXISTS 运算符与 DELETE 语句配合使用，根据子查询返回的行是否存在来删除表中的行。

The MySQL EXISTS operator is used with the DELETE statement to delete the rows in a table based on the existence of rows returned by a subquery.

Example

在此，我们删除 CUSTOMERS 表中 ID 等于 CARS 表中 ID 的所有记录，该表的 price 等于 2,250,000 −

Here, we are deleting all the records from the CUSTOMERS table whose ID is equal to the ID in the CARS table having a price equal to 2,250,000 −

DELETE FROM CUSTOMERS
WHERE EXISTS
(SELECT * FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND CARS.PRICE = 2250000);

Output

正如我们观察输出所示，已删除 1 行 −

As we can observe the output, 1 row has been deleted −

Query OK, 1 row affected (0.00 sec)

Verification

我们可以使用以下查询来验证更改是否已反映在 CUSTOMERS 表中 −

We can verify whether the changes have been reflected in the CUSTOMERS table using the following query −

SELECT * FROM CUSTOMERS;

Output

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

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

NOT Operator with EXISTS Operator

如果我们在 MySQL 中使用带有 EXISTS 运算符的 NOT，它将从一个表中选择不存在于另一个表中的记录。

If we use the NOT with EXISTS operator in MySQL, it will select records from one table that do not exist in another table.

Syntax

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

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

SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);

Example

在以下查询中，我们提取尚未购买任何车的客户的 NAME −

In the following query, we are fetching the NAME of the customers who have not bought any car −

SELECT * FROM CUSTOMERS
WHERE NOT EXISTS
(SELECT * FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

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

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

Exists Operator Using a Client Program

除了使用 MySQL 查询来验证特定记录是否存在于 MySQL 表中之外，您还可以使用客户端程序执行 EXISTS 操作。

In addition to verify whether a particular record exists in a MySQL table with a MySQL query, you can also use a client program to perform the EXISTS operation.

Syntax

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

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

Example

以下是这些程序 −

Following are the programs −