Mysql 简明教程

MySQL - SubQuery

MySQL 子查询，也称为内部查询或嵌套查询，是另一个查询中的查询。它允许您根据另一个查询的结果从一张或多张表中检索数据。子查询可用于 SQL 语句的各个部分，包括 SELECT、INSERT、UPDATE 和 DELETE。

The MySQL subquery, also known as an inner query or nested query, is a query inside another query. It allows you to retrieve data from one or more tables based on the results of another query. Subqueries can be used in various parts of SQL statements, including SELECT, INSERT, UPDATE, and DELETE.

Subquery with the SELECT Statement

SELECT 语句中的子查询用于根据从子查询检索的值过滤主查询的结果。

A subquery within a SELECT statement is used to filter the results of the main query based on the values retrieved from the subquery.

Syntax

以下是一个 SELECT 语句中子查询的基本语法 −

Following is the basic syntax of a subquery within a SELECT statement −

SELECT column1, column2, ...
FROM table1
WHERE columnN operator
(SELECT column_name FROM table2 WHERE condition);

Example

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

First, let us create a table with the name CUSTOMERS using the following query −

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

现在，使用 INSERT 语句将值插入到上面创建的表中，如下所示 -

Now, let us insert values into the above-created table using the INSERT statement as shown below −

INSERT INTO CUSTOMERS 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 );

CUSTOMER 表显示如下 -

The CUSTOMERS table displayed is as shown below −

以下查询检索出 ID 与同一表中 ID 匹配的 CUSTOMERS 表中的所有客户的薪水 -

The following query retrieves the salaries of all customers from the CUSTOMERS table whose ID’s match with the ID’s in the same table −

SELECT SALARY FROM CUSTOMERS
WHERE ID IN
(SELECT ID FROM CUSTOMERS);

Output

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

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

Subquery with the INSERT Statement

我们还可以在 MySQL 中使用插入语句和子查询。插入语句将使用从子查询返回的数据插入到另一张表中。

We can also use the subqueries with the INSERT statements in MySQL. The INSERT statement will use the data returned from the subquery to insert into another table.

Syntax

以下是 INSERT 语句中子查询的基本语法 -

Following is the basic syntax of a subquery within an INSERT statement −

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

Example

在对 INSERT 语句执行子查询之前，让我们创建一个名为 "CUSTOMERS_BKP" 的表，其结构与 CUSTOMERS 表相似 -

Before performing the subqueries with INSERT statement, let us create a table named "CUSTOMERS_BKP" with a similar structure as CUSTOMERS table −

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

现在，让我们使用以下查询将 CUSTOMERS 表中的所有记录插入到 CUSTOMERS_BKP 表中 -

Now, let us insert all records from CUSTOMERS table into the CUSTOMERS_BKP table using the following query −

INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS);

Output

CUSTOMERS 表中的记录已成功插入到 CUSTOMERS_BKP 表中 -

The records of CUSTOMERS table has successfully inserted into CUSTOMERS_BKP table −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

让我们使用以下 SELECT 语句验证 CUSTOMERS_BKP 表中是否有记录 -

Let us verify whether the CUSTOMERS_BKP table have records using the following SELECT statement −

SELECT * FROM CUSTOMERS_BKP;

正如我们在下面的表中所看到的，CUSTOMERS 表中的所有记录都被插入到 CUSTOMERS_BKP 表中 -

As we can see in the table below, all the records in CUSTOMERS table is inserted into CUSTOMERS_BKP table −

Subquery with Comparison Operator

MySQL 子查询与比较运算符允许我们在另一个查询中使用一个查询，并使用比较运算符将其结果与外部查询进行比较。

The MySQL Subquery with comparison operator allows us to use a query inside another query and compare its result with the outer query using comparison operators.

Syntax

以下是带比较运算符的子查询的基本语法 -

Following is the basic syntax of a subquery with comparison operators −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE] .....)

Example

以下查询从表 CUSTOMERS_BKP 中检索出所有 AGE 大于 23 的 CUSTOMERS，并返回其 ID。

The following query retrieves all the CUSTOMERS from the table CUSTOMERS_BKP with an AGE greater than 23 and returns their IDs.

SELECT * FROM CUSTOMERS_BKP
WHERE ID IN (SELECT ID FROM CUSTOMERS_BKP
WHERE AGE > 23);

Output

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

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

Subquery with IN or NOT-IN Operator

MySQL 子查询与 IN/NOT-IN 运算符用于根据值是否从一个查询匹配或不匹配另一个查询中的值来过滤数据 -

The MySQL subqueries with IN/NOT-IN operators are used to filter data based on whether values from one query match or do not match values from another query −

  1. IN matches any value from the list

  2. NOT-IN excludes any value from the list.

Example

以下查询从 CUSTOMERS 表中检索出所有记录，该记录中的 ADDRESS is not "Hyderabad"，方法是将其与 CUSTOMERS_BKP 表中的地址进行比较 -

The following query retrieves all the records from the CUSTOMERS table where the ADDRESS is not "Hyderabad" by comparing it to addresses in the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS NOT IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

以下是以上查询的输出：

Following is the output of the above query −

Example

现在，以下查询从 CUSTOMERS 表中检索出所有地址为 "Hyderabad" 的行，通过使用子查询从 CUSTOMERS_BKP 表中获取所有与 "Hyderabad" 匹配的地址 -

Now, the following query retrieves all the rows from the CUSTOMERS table where the ADDRESS is "Hyderabad" by using a subquery to fetch all addresses that match "Hyderabad" from the CUSTOMERS_BKP table −

SELECT * FROM CUSTOMERS
WHERE ADDRESS IN (
SELECT ADDRESS FROM CUSTOMERS_BKP WHERE ADDRESS = "Hyderabad");

Output

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

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

Subquery Using a Client Program

我们还可以使用客户端程序执行子查询。

We can also perform Subquery using the client program.

Syntax

Example

以下是这些程序 −

Following are the programs −