轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL 子查询


简介:在本教程中,我们将向您展示如何使用MySQL子查询编写复杂查询并解释相关子查询概念。


一个MySQL子查询是嵌套在另一个查询内,如查询  SELECTINSERTUPDATE,DELETE  。此外,MySQL子查询可以嵌套在另一个子查询中。

MySQL子查询称为内部查询,而包含子查询的查询称为外部查询。子查询可以在任何使用表达式的地方使用,并且必须在括号中关闭。

以下查询返回在美国办事处工作的员工。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

在这个例子中:

  • 子查询返回位于美国的办事处的所有办公代码
  • 外部查询选择在办公室代码位于子查询返回的结果集中的办公室中工作的员工的姓氏和名字。

 

执行查询时,子查询首先运行并返回结果集。然后,此结果集用作外部查询的输入。


MySQL子查询在WHERE子句中

我们将使用示例数据库中payments表进行演示。

+----------------+
| payments       |
+----------------+
| customerNumber |
| checkNumber    |
| paymentDate    |
| amount         |
+----------------+
4 rows in set (0.02 sec)

MySQL子查询带有比较运算符

您可以使用比较运算符(例如,=,>,<等等)将子查询返回的单个值与WHERE子句中的表达式进行比较。

例如,以下查询返回具有最高付款金额的客户。

SELECT 
    customerNumber, checkNumber, amount
FROM
    payments
WHERE
    amount = (SELECT 
            MAX(amount)
        FROM
            payments);

运行结果:

+----------------+-------------+-----------+
| customerNumber | checkNumber | amount    |
+----------------+-------------+-----------+
|            141 | JE105477    | 120166.58 |
+----------------+-------------+-----------+
1 row in set (0.02 sec)

除了等于运算符,您还可以使用其他比较运算符,例如大于(>),小于(<)等。

例如,您可以使用子查询查找付款大于平均付款的客户。首先,在子查询使用AVG聚合函数计算平均付款。然后,在外部查询中,查询大于子查询返回的平均付款的付款。

SELECT 
    customerNumber, checkNumber, amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);

运行结果:

+----------------+-------------+-----------+
| customerNumber | checkNumber | amount    |
+----------------+-------------+-----------+
|            112 | HQ55022     |  32641.98 |
|            112 | ND748579    |  33347.88 |
|            114 | GG31455     |  45864.03 |
|            114 | MA765515    |  82261.22 |
|            114 | NR27552     |  44894.74 |
|            119 | LN373447    |  47924.19 |
...

MySQL子查询带有IN和NOT IN运算符

如果子查询返回多个值,则可以在WHERE子句中使用其他运算符,例如INNOT IN运算符。

请参阅以下内容customersorders表格:

+------------------------+
| customers              |
+------------------------+
| customerNumber         |
| customerName           |
| contactLastName        |
| contactFirstName       |
| phone                  |
| addressLine1           |
| addressLine2           |
| city                   |
| state                  |
| postalCode             |
| country                |
| salesRepEmployeeNumber |
| creditLimit            |
+------------------------+
13 rows in set (0.00 sec)

+----------------+
| orders         |
+----------------+
| orderNumber    |
| orderDate      |
| requiredDate   |
| shippedDate    |
| status         |
| comments       |
| customerNumber |
+----------------+
7 rows in set (0.00 sec)

例如,您可以使用带有NOT IN运算符的子查询来查找没有订单的客户,如下所示:

SELECT 
    customerName
FROM
    customers
WHERE
    customerNumber NOT IN (SELECT DISTINCT
            customerNumber
        FROM
            orders);

运行结果:

+--------------------------------+
| customerName                   |
+--------------------------------+
| Havel & Zbyszek Co             |
| American Souvenirs Inc         |
| Porto Imports Co.              |
| Asian Shopping Network, Co     |
| Natrlich Autos                 |
| ANG Resellers                  |
| Messner Shopping Network       |
...

MySQL子查询中的FROM子句

FROM子句中使用子查询时,从子查询返回的结果集将用作临时表。此表称为派生表或实现子查询。

以下子查询查找销售订单中的最大最小平均项数:

SELECT 
    MAX(items), MIN(items), FLOOR(AVG(items))
FROM
    (SELECT 
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;

运行结果:

+------------+------------+-------------------+
| MAX(items) | MIN(items) | FLOOR(AVG(items)) |
+------------+------------+-------------------+
|         18 |          1 |                 9 |
+------------+------------+-------------------+
1 row in set (0.00 sec)

注意:FLOOR()函数用于从项目的平均值中删除小数位。

MySQL相关子查询

在前面的示例中,您注意到子查询是独立的。这意味着您可以将子查询作为独立查询执行,例如:

SELECT 
    orderNumber, 
    COUNT(orderNumber) AS items
FROM
    orderdetails
GROUP BY orderNumber;

与独立子查询不同,相关子查询是使用外部查询中的数据的子查询。换句话说,相关子查询取决于外部查询。对外部查询中的每一行评估一次相关子查询。

在以下查询中,我们选择购买价格高于每个 产品系列中所有产品的平均购买价格的产品。

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)

运行结果:

+-----------------------------------------+----------+
| productname                             | buyprice |
+-----------------------------------------+----------+
| 1952 Alpine Renault 1300                |    98.58 |
| 1996 Moto Guzzi 1100i                   |    68.99 |
| 2003 Harley-Davidson Eagle Drag Bike    |    91.02 |
| 1972 Alfa Romeo GTA                     |    85.68 |
| 1962 LanciaA Delta 16V                  |   103.42 |
| 1968 Ford Mustang                       |    95.34 |
...

内部查询针对每个产品系列执行,因为每行都更改了产品系列。因此,平均购买价格也会发生变化。外部查询仅筛选购买价格高于子查询中每个产品系列的平均购买价格的产品。

MySQL子查询带有EXISTS和NOT EXISTS

当子查询与  EXISTSor NOT EXISTS运算符一起使用时,子查询返回布尔值TRUEor FALSE。以下查询说明了与EXISTS运算符一起使用的子查询:

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

在上面的查询中,如果子查询返回任何行,则EXISTS subquery返回TRUE,否则返回FALSE

EXISTSNOT EXISTS经常在用于相关子查询。

我们来看看示例数据库中的orders和orderDetails表:

+----------------+
| orders         |
+----------------+
| orderNumber    |
| orderDate      |
| requiredDate   |
| shippedDate    |
| status         |
| comments       |
| customerNumber |
+----------------+
7 rows in set (0.00 sec)

+-----------------+
| orderdetails    |
+-----------------+
| orderNumber     |
| productCode     |
| quantityOrdered |
| priceEach       |
| orderLineNumber |
+-----------------+
5 rows in set (0.01 sec)

以下查询选择总值大于60K的销售订单。

SELECT 
    orderNumber, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

运行结果:

+-------------+----------+
| orderNumber | total    |
+-------------+----------+
|       10165 | 67392.85 |
|       10287 | 61402.00 |
|       10310 | 61234.67 |
+-------------+----------+
3 rows in set (0.01 sec)

它返回3行,这意味着有3个销售订单的总值大于60K。

可以使用上面的查询作为相关子查询来查找通过使用EXISTS运算符放置至少一个总值大于60K的销售订单的客户:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

运行结果:

+----------------+-------------------------+
| customerNumber | customerName            |
+----------------+-------------------------+
|            148 | Dragon Souveniers, Ltd. |
|            259 | Toms Spezialitten, Ltd  |
|            298 | Vida Sport, Ltd         |
+----------------+-------------------------+
3 rows in set (0.01 sec)

在本教程中,我们向您展示了如何使用MySQL子查询和相关子查询来构造更复杂的查询。