MySQL with 子句

数据库 1年前 11

在本教程中，我们将学习 MySQL WITH 子句，也称为 Common Table Expression (CTE)。每当你想要操作困难的子查询数据时，都会使用 CTE。

我们还将学习 Common Table Expression (CTE) 如何让你以易于阅读和理解的方式编写复杂的查询。我们还将看看是否可以使用嵌套的 WITH 子句。

请注意，在 MySQL 8.0 版之前，Common Table Expression 不可用。你必须拥有 MySQL 8.0 或更高版本才能使用它。你可以在此处查看 MySQL 8.0 版中的新功能。

使用 MySQL `WITH` 子句即 `Common Table Expression`

要使用 MySQL WITH 子句，让我们先了解 CTE。公用表表达式 (CTE) 被命名为临时结果集，仅存在于编写它的语句的执行范围内。

通过使用 WITH 子句，你可以为复杂的子查询指定名称，你可以在主查询中轻松使用该名称（SELECT、INSERT、UPDATE 或 DELETE）。请记住，并非所有数据库都支持 WITH 子句。

你可以在同一个 WITH 子句中使用一个或多个子查询和 CTE，但不能使用嵌套 WITH（WITH 子句中的另一个 WITH）。让我们创建一个名为 tb_order 的 Table 并用一些数据填充它以练习 WITH 子句。

示例代码：

#SQLProgrammingUsingMySQLVersion8.27CREATETABLE`practice_with_clause`.`tb_order`(ORDER_IDINTEGERNOTNULL,CUSTOMER_FIRST_NAMEVARCHAR(30)NOTNULL,CUSTOMER_LAST_NAMEVARCHAR(30)NOTNULL,CITY_NAMEVARCHAR(64)NOTNULL,PURCHASED_PRODUCTSVARCHAR(64)NOTNULL,ORDER_DATEDATENOTNULL,PRIMARYKEY(ORDER_ID));

确保在 Tables 下的 Database 中成功创建了你的表。

MySQL with 子句

使用下面的 INSERT 命令用 7 条记录填充表。

#SQLProgrammingUsingMySQLVersion8.27INSERTINTOpractice_with_clause.tb_order(ORDER_ID,CUSTOMER_FIRST_NAME,CUSTOMER_LAST_NAME,CITY_NAME,PURCHASED_PRODUCTS,ORDER_DATE)VALUES(1,'John','Horton','Washington','Books','2021-05-03'),(2,'Banji','Horton','Florida','Pens','2010-5-6'),(3,'Nayya','Sofia','South Carolina','Books','2011-10-15'),(4,'Martell','Daniel','Michigan','NoteBooks','2012-12-02'),(5,'Sana','Preston','Michigan','White Board Marker','2013-08-27'),(6,'Gulraiz','Yonja','Washington','Books','2021-05-03'),(7,'Mashal','Naaz','Florida','Comic Books','2019-01-01');

现在，使用 SELECT 命令查看数据。

#SQLProgrammingUsingMySQLVersion8.27SELECT*FROMpractice_with_clause.tb_order;

MySQL with 子句

此时，我们将使用 WITH 子句来使用公共表表达式并操作复杂的子查询，如下所示。

#SQLProgrammingUsingMySQLVersion8.27WITHcte_orderAS(SELECTPURCHASED_PRODUCTS,COUNT(ORDER_ID)asNumber_of_OrdersFROMpractice_with_clause.tb_orderGROUPBYPURCHASED_PRODUCTS)SELECTAVG(Number_of_Orders)AS"Average Orders Per Category"FROMcte_order;

让我们把上面的查询分解成几个部分来理解：

公用表表达式：cte_order

子查询：

SELECTPURCHASED_PRODUCTS,COUNT(ORDER_ID)asNumber_of_OrdersFROMpractice_with_clause.tb_orderGROUPBYPURCHASED_PRODUCTS

主查询：

SELECTAVG(Number_of_Orders)AS"Average Orders Per Category"FROMcte_order;

请注意，CTE 在主查询中引用自身来读取数据。它将根据我的数据显示以下输出（你的输出可能不同）。

MySQL with 子句

`公用表表达式`执行范围

正如我们所说，CTE 只在其执行范围内工作，如何？请参阅以下屏幕截图。

MySQL with 子句

当你只选择绿色框突出显示的代码时，你仍然在名为 cte_order 的 CTE 的执行范围内，但是当你只选择红色框内的代码时，你现在不在执行范围内，无法引用名为 cte_order 的公用表表达式。这意味着你可以在编写它的同一 WITH 子句中引用 CTE。

使用具有多个`公共表表达式`的 MySQL `WITH` 子句

让我们通过使用多个公用表表达式来练习 WITH 子句。

WITHcte_orderAS(SELECTPURCHASED_PRODUCTS,COUNT(ORDER_ID)asNumber_of_OrdersFROMpractice_with_clause.tb_orderGROUPBYPURCHASED_PRODUCTS),cte_locationAS(SELECTCOUNT(CITY_NAME)asCityFROMpractice_with_clause.tb_orderWHERECITY_NAME='Washington')SELECTAVG(Number_of_Orders)AS"Average Orders Per Category",CityFROMcte_order,cte_location;

现在，你将看到以下输出。

MySQL with 子句