mysql的cte（公用表表达式）

（一）公用表表达式是一个命名的临时结果集，仅在单个SQL语句(例如SELECT，INSERT，UPDATE或DELETE)的执行范围内存在。

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

WITH 公用表名 (column_list) AS (    查询语句) SELECT * FROM 公用表名;

实例：

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;

（二）派生表是从SELECT语句返回的虚拟表。与子查询不同，派生表必须具有别名，以便稍后在查询中引用其名称。

SELECT     column_listFROM    (SELECT         column_list             ——》派生表    FROM        table_1) derived_table_name;WHERE derived_table_name.c1 > 0;

（三）临时表是一种特殊类型的表，它允许您存储一个临时结果集，可以在单个会话中多次重用。

1、使用CREATE TEMPORARY TABLE语句创建临时表。请注意，在CREATE和TABLE关键字之间添加TEMPORARY关键字。

2、当会话结束或连接终止时，MySQL会自动删除临时表。当您不再使用临时表时，也可以使用DROP TABLE语句来显式删除临时表。

3、一个临时表只能由创建它的客户机访问。不同的客户端可以创建具有相同名称的临时表，而不会导致错误，因为只有创建临时表的客户端才能看到它。 但是，在同一个会话中，两个临时表不能共享相同的名称。

4、临时表可以与数据库中的普通表具有相同的名称。 不推荐使用相同名称。例如，如果在示例数据库(yiibaidb)中创建一个名为employees的临时表，则现有的employees表将变得无法访问。 对employees表发出的每个查询现在都是指employees临时表。 当删除您临时表时，永久employees表可以再次访问。

创建一个临时表，按照收入存储前10名客户

CREATE TEMPORARY TABLE top10customersSELECT p.customerNumber,        c.customerName,        FORMAT(SUM(p.amount),2) totalFROM payments pINNER JOIN customers c ON c.customerNumber = p.customerNumberGROUP BY p.customerNumberORDER BY total DESCLIMIT 10;

删除临时表：

DROP TEMPORARY TABLE table_name;

（四）子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。

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