轻松上手,快乐学习!

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 CTE


简介:在本教程中,您将学习如何使用MySQL CTE或公用表表达式以更易读的方式构造复杂查询。


从版本8.0开始,MySQL简单地引入了公用表表达式功能或CTE,因此您应在计算机上安装MySQL 8.0,以便练习本教程中的语句。


什么是常见的公用表表达式或CTE

公共表表达式是只存在一个单一的SQL语句例如执行范围内的一个命名的临时结果集,如:SELECTINSERTUPDATE,或DELETE

派生表类似,CTE不作为对象存储,仅在执行查询期间持续存在。与派生表不同,CTE可以是自引用(递归CTE),也可以在同一查询中多次引用。此外,与派生表相比,CTE提供了更好的可读性和性能。


MySQL CTE语法

CTE的结构包括名称,可选列列表和定义CTE的查询。CTE定义后,您可以使用它像一个视图SELECTINSERTUPDATEDELETE,或CREATE VIEW语句。

以下是CTE的基本语法:

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;

注意:查询中的列数必须与column_list中的列数相同。如果省略column_list,CTE将使用定义CTE的查询的列列表


简单的MySQL CTE示例

以下示例说明如何使用CTE从示例数据库中customers表中查询数据。请注意,此示例仅用于演示目的,以便您轻松了解CTE概念。

WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;

运行结果:

+------------------------------+
| customerName                 |
+------------------------------+
| Boards & Toys Co.            |
| Collectable Mini Designs Co. |
| Corporate Gift Ideas Co.     |
| Men 'R' US Retailers, Ltd.   |
| Mini Gifts Distributors Ltd. |
| Mini Wheels Co.              |
| Signal Collectibles Ltd.     |
| Technics Stores Inc.         |
| The Sharp Gifts Warehouse    |
| Toys4GrownUps.com            |
| West Coast Collectables Co.  |
+------------------------------+
11 rows in set (0.25 sec)

在此示例中,CTE的名称是customers_in_usa,定义CTE的查询返回两列customerNamestate。因此,customers_in_usaCTE返回位于美国的所有客户。

在定义customers_in_usaCTE之后,我们在SELECT语句中引用它同时查询“CA”的客户。

看另一个例子:

WITH topsales2013 AS (
    SELECT 
        salesRepEmployeeNumber employeeNumber,
        SUM(quantityOrdered * priceEach) sales
    FROM
        orders
            INNER JOIN
        orderdetails USING (orderNumber)
            INNER JOIN
        customers USING (customerNumber)
    WHERE
        YEAR(shippedDate) = 2013
            AND status = 'Shipped'
    GROUP BY salesRepEmployeeNumber
    ORDER BY sales DESC
    LIMIT 5
)
SELECT 
    employeeNumber, firstName, lastName, sales
FROM
    employees
        JOIN
    topsales2013 USING (employeeNumber);

运行结果:

+----------------+-----------+-----------+-----------+
| employeeNumber | firstName | lastName  | sales     |
+----------------+-----------+-----------+-----------+
|           1165 | Leslie    | Jennings  | 413219.85 |
|           1370 | Gerard    | Hernandez | 295246.44 |
|           1401 | Pamela    | Castillo  | 289982.88 |
|           1621 | Mami      | Nishi     | 267249.40 |
|           1501 | Larry     | Bott      | 261536.95 |
+----------------+-----------+-----------+-----------+
5 rows in set (0.17 sec)

在此示例中,CTE在2013年返回前5名销售代表。之后,我们引用topsales2013CTE以获取有关销售代表的其他信息,包括名字和姓氏。


MySQL CTE高级实例

请参阅以下示例:

WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

运行结果:

+------------------------------------+------------------+
| customerName                       | salesrepName     |
+------------------------------------+------------------+
| Alpha Cognac                       | Gerard Hernandez |
| Amica Models & Co.                 | Pamela Castillo  |
| Anna's Decorations, Ltd            | Andy Fixter      |
| Atelier graphique                  | Gerard Hernandez |
| Australian Collectables, Ltd       | Andy Fixter      |
| Australian Collectors, Co.         | Andy Fixter      |
| Australian Gift Network, Co        | Andy Fixter      |
| Auto Associs & Cie.                | Gerard Hernandez |
...

在此示例中,我们在同一查询中有两个CTE。第一个CTE(  salesrep)获得职称为销售代表的员工。第二个CTE(customer_salesrep)参考INNER JOIN 子句中的第一个CTE,以获得销售代表和每个销售代表负责的客户。

在进行第二次CTE之后,我们使用SELECT带有ORDER BY子句的简单语句从CTE查询数据。


WITH子句用法

有一些上下文可以使用WITH子句来创建公用表表达式:

首先,WITH子句可以在开始时使用SELECTUPDATEDELETE语句:

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

其次,WITH子句可以在子查询或派生表子查询的开头使用:

SELECT ... WHERE id IN (WITH ... SELECT ...);
 
SELECT * FROM (WITH ... SELECT ...) AS derived_table;

第三,WITH可以在SELECT包含SELECT子句的语句之前使用子句:

CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

在本教程中,您学习了如何使用MySQL CTE构建复杂查询。