MySQL8.0的WITH查询详情

当使用 MySQL 8.0 时,我们可以使用 Common Table Expressions (CTE) 或者简称为 WITH 查询来简化查询语句。本文将向您介绍MySQL 8.0的 WITH 查询详情及其用法的完整攻略。

什么是WITH查询

WITH查询在MySQL8.0中被称为公共表达式。使用WITH语句,我们可以为一次查询创建一个临时表,并在查询中使用它。这个临时表可以由其他查询使用,因此可以大大简化复杂的查询。

一个公共表达式由以下部分组成:

  • WITH 子句定义公共表达式的名称和列。
  • 声明子句定义公共子句的内容。
  • SELECT语句是执行实际查询的语句。

如何使用WITH查询

WITH查询的语法如下:

WITH cte_name [(column_name [, column_name] ...)] AS ( subquery ) SELECT statement;

其中cte_name是您给公共表达式命名的名称,column_name是您要包含在新创建的表中的列名,subquery是公共表达式的内容和SELECT语句是执行实际查询的语句。WITH查询支持递归和非递归、多个公共表达式和多个定义等特性。

非递归公共表达式

以下是一个用于举例的基本非递归公共表达式:

WITH sales AS (
    SELECT customer_id, SUM(amount) AS total_sales FROM orders 
    GROUP BY customer_id
)
SELECT customer_id, total_sales
FROM sales
WHERE total_sales > 100000;

这个查询使用了一个公共表达式,该公共表达式被命名为sales。在这个表达式中,我们从订单表中获取每个客户的总销售额。在使用了公共表达式后,我们可以很容易地使用WHERE条件语句筛选出我们需要的结果。

递归公共表达式

递归公共表达式是与自身递归关系的查询。在表的行之间建立关系时,这种公共表达式非常有用。

这是递归公共表达式的基本语法:

WITH recursive cte_name (column_name,column_name...) AS ( subquery_with_union )

其中recursive告诉MYSQL这是一个递归的公共表达式,cte_name是您给公共表达式命名的名称,subquery_with_union是公共表达式的内容和SELECT语句是执行实际查询的语句。

以下面的例子为例。它的功能是从指定的起始节点开始递归地遍历所有子节点,最终返回所有与起始节点相邻的子节点(直接相邻或间接相邻)。使用递归公共表达式可以简化查询。

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
           CAST(CONCAT('/',g.id,'/') AS CHAR(100)), false
        FROM graph g
       WHERE g.id = start_node_id
     UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
           CAST(CONCAT(sg.path,g.id,'/') AS CHAR(100)),
           g.id REGEXP sg.path as cycle
        FROM graph g, search_graph sg
       WHERE g.id = sg.link AND NOT cycle AND sg.depth < max_depth
)
SELECT * FROM search_graph;

在这个示例中,我们从graph表中选择了第1步。我们选择起始节点为start_node_id,然后创建一个公共表达式search_graph,用于追踪图表中的所有行。我们使用 UNION ALL 运算符将常规 SQL 查询与递归公共表达式组合在一起,并基于是否出现异常数据(即和起始节点直接或间接相邻的子节点是否存在环)以及遍历深度的限制来确定何时应停止递归。

公共表达式的输出也可以作为其他查询的输入。例如,下面的查询使用公共表达式来查找所有客户及其订购的产品数量。

WITH sales AS (
    SELECT customer_id, SUM(amount) AS total_sales FROM orders 
    GROUP BY customer_id
)
SELECT customers.customer_name, sales.total_sales, product_count
FROM customers 
JOIN sales ON customers.customer_id = sales.customer_id
JOIN (
    SELECT customer_id, COUNT(*) AS product_count FROM order_details 
    GROUP BY customer_id
) AS products ON customers.customer_id = products.customer_id;

在这个示例中,我们使用了两个公共表达式。一个是“sales”公共表达式,另一个是products子查询。

总结

使用WITH查询可以大大简化复杂查询。它提供了一种方便的方法,可以将一个查询分成多个查询块,提高可读性和可维护性。在MySQL 8.0中,公共表达式支持非递归和递归查询,您可以使用它来处理大量数据。

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL8.0的WITH查询详情 - Python技术站

(0)
上一篇 2023年5月22日
下一篇 2023年5月22日

相关文章

  • mysql的sql语句特殊处理语句总结(必看)

    mysql的sql语句特殊处理语句总结(必看) 在mysql中,我们常常需要对查询结果进行特殊处理,比如去重、排序、分组、计数等等,这时候就需要用到一些特殊处理语句。本文总结了mysql的常见特殊处理语句及其用法,希望能够帮助大家更好地处理数据。 去重 去重是经常需要用到的操作,mysql中可以通过DISTINCT关键字进行去重。例如,以下SQL语句可以从u…

    database 2023年5月21日
    00
  • PHP针对伪静态的注入总结【附asp与Python相关代码】

    PHP针对伪静态的注入攻略总结 什么是伪静态? 伪静态是指通过URL重写等方式,将动态页面的URL转化为静态页面的URL,以提高搜索引擎的爬取效率和用户的访问速度,同时也可以增加网站的安全性。 举个例子,假如原本的动态页面URL是www.example.com/article.php?id=123,转化为伪静态后可能会变成www.example.com/ar…

    database 2023年5月22日
    00
  • linux下讲解MySQL安装与登录方法

    下面就是关于”Linux下讲解MySQL安装与登录方法”的完整攻略。 MySQL的安装方法 MySQL的安装方法有很多种,在这里我们主要介绍通过Linux系统自带的包管理器(yum、apt-get等)进行安装的方法。以下以CentOS 7系统为例: 1. 更新系统 在安装MySQL前,我们需要先确保系统是最新的。通过以下命令进行更新: sudo yum up…

    database 2023年5月22日
    00
  • mysql自动断开该连接解决方案

    作者: MySQL 的默认设置下,当一个连接的空闲时间超过8小时后,MySQL 就会断开该连接,而 c3p0 连接池则以为该被断开的连接依然有效。在这种情况下,如果客户端代码向 c3p0 连接池请求连接的话,连接池就会把已经失效的连接返回给客户端,客户端在使用该失效连接的时候即抛出异常 复制代码代码如下: <bean /> <!–othe…

    MySQL 2023年4月13日
    00
  • PostgreSQL 和 MongoDB 的区别

    PostgreSQL和MongoDB是两种不同类型的数据库管理系统。PostgreSQL是一种关系型数据库管理系统(RDBMS),MongoDB是一种文档导向数据库管理系统(NoSQL)。 数据库结构 PostgreSQL是一种关系型数据库,数据存储在表中,包括多个表,可以通过表关系互相连接。每个表可以包含多个列(字段),每个列可以包含不同类型的数据。 Mo…

    database 2023年3月27日
    00
  • MySQL基本架构与锁的知识点有哪些

    本篇内容主要讲解“MySQL基本架构与锁的知识点有哪些”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL基本架构与锁的知识点有哪些”吧! MySql架构 SQL Layer Connection Pool : 连接池,用于接收连接请求和管理连接。 ManagementService&Utilitie…

    2023年4月8日
    00
  • Mysql中的日期时间函数小结

    Mysql中的日期时间函数小结 MySQL提供了丰富的日期和时间函数,方便我们对日期和时间进行各种操作。在这篇文章中,我们将给出MySQL中一些常用的日期和时间函数的介绍和用法演示。 NOW() NOW()函数返回当前日期和时间。 SELECT NOW(); — 返回如下结果 — 2021-11-16 10:05:25 DATE() DATE()函数返回…

    database 2023年5月22日
    00
  • Linux 连续执行多条命令的方法(推荐)

    下面就为大家讲解一下“Linux 连续执行多条命令的方法(推荐)”的完整攻略。 标准的方法 语法格式 shell_command1 && shell_command2 && … && shell_commandN 上述命令中,命令之间使用&&隔开,表示先执行前面的命令,如果成功才继续执行后面…

    database 2023年5月22日
    00
合作推广
合作推广
分享本页
返回顶部