mysql学习教程，从入门到精通，SQL 临时表（37）

1、SQL 临时表

在SQL中，临时表（Temporary Table）是一种在会话或连接期间临时存储数据的表。它们对于存储中间结果、简化复杂查询以及提高性能非常有用。以下是一个创建和使用临时表的示例。

假设我们有一个名为 employees 的表，其中包含以下列：employee_id, first_name, last_name, department_id, 和 salary。我们想要创建一个临时表来存储某个特定部门的员工，并对这些员工进行一些分析。

1.1、创建并使用临时表的步骤

1. 创建临时表：
   首先，我们需要创建一个临时表来存储数据。临时表在不同的数据库系统中可能有所不同，但通常使用 CREATE TEMPORARY TABLE 语句。

2. 插入数据：
   接下来，我们将从原始表中插入数据到临时表中。

3. 查询和分析数据：
   最后，我们可以对临时表进行查询和分析。

示例代码

以下是一个完整的示例，展示了如何在MySQL中创建和使用临时表：

-- 1. 创建临时表
CREATE TEMPORARY TABLE temp_employees (employee_id INT,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,salary DECIMAL(10, 2)
);-- 2. 插入数据到临时表
-- 假设我们要插入部门ID为10的所有员工
INSERT INTO temp_employees (employee_id, first_name, last_name, department_id, salary)
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE department_id = 10;-- 3. 查询和分析数据
-- 例如，计算临时表中所有员工的平均工资
SELECT AVG(salary) AS average_salary
FROM temp_employees;-- 4. 临时表在会话结束时会自动删除，但你也可以手动删除它
-- DROP TEMPORARY TABLE temp_employees;

1.2、注意事项

1. 临时表的生命周期：

   • 在MySQL中，临时表在会话结束时会自动删除。
   • 在其他数据库系统（如SQL Server）中，临时表分为局部临时表（以 # 开头，会话结束时删除）和全局临时表（以 ## 开头，所有会话不再引用时删除）。

2. 权限：

   • 创建临时表通常需要 CREATE TEMPORARY TABLES 权限。

3. 事务处理：

   • 在某些数据库系统中，临时表可能不支持事务处理或具有特殊的行为。

4. 命名冲突：

   • 在同一会话中，不能创建两个同名的临时表。

通过创建和使用临时表，你可以有效地处理复杂的查询和数据操作，同时避免对原始数据的直接修改。

当然可以。以下是一些实际的使用SQL临时表的案例，这些案例展示了临时表在不同场景下的应用。

案例一：计算订单交付周期统计信息

假设有一个名为 pln_order_pool 的订单表，包含以下字段：create_time（订单创建时间）、reality_deliver_time（实际交付时间）、order_state（订单状态）等。我们需要计算所有状态为“finish”的订单的平均交付周期、最大交付周期和最小交付周期。

-- 计算每个订单的交付周期，并存储在临时表中
WITH order_lead_times AS (SELECT DATEDIFF(reality_deliver_time, create_time) AS leadTimeFROM pln_order_poolWHERE order_state = 'finish'
)
-- 从临时表中计算平均交付周期、最大交付周期和最小交付周期
SELECT AVG(leadTime) AS avgLeadTime,MAX(leadTime) AS maxLeadTime,MIN(leadTime) AS minLeadTime
FROM order_lead_times;

在这个案例中，我们使用了一个公共表表达式（CTE，也称为WITH子句）来创建一个临时表 order_lead_times，它存储了每个订单的交付周期。然后，我们从这个临时表中计算出了所需的统计信息。

案例二：计算计划完成率

假设有一个名为 plan 的表，用于记录某项目的计划数和实际完成数。该表包含以下字段：date（日期）、type（类型，‘计划’或’实出’）、num（数量）。我们需要计算某一天的计划完成率，即该天的实际完成数与计划数的比值。

-- 计算当天的计划总数，并存储在临时表中
WITH plan_nums AS (SELECT SUM(num) AS planNumsFROM planWHERE type = '计划' AND date = '2022-08-09'
),
-- 计算当天的实际完成总数，并存储在另一个临时表中
finish_nums AS (SELECT SUM(num) AS finishNumsFROM planWHERE type = '实出' AND date = '2022-08-09'
)
-- 从两个临时表中计算计划完成率
SELECT planNums,finishNums,IFNULL(ROUND(finishNums / planNums * 100, 2), 0) AS finishRate
FROM plan_nums, finish_nums;

在这个案例中，我们使用了两个CTE来分别创建存储当天计划总数和实际完成总数的临时表。然后，我们从这两个临时表中计算出了计划完成率。

案例三：数据转换和ETL过程

在ETL（提取、转换、加载）过程中，临时表常用于临时保存和转换数据。例如，我们可能需要从多个表中提取数据，进行转换和清洗，然后将结果加载到目标表中。在这个过程中，临时表可以作为一个中间存储点，用于存储转换后的数据。

-- 创建一个临时表来存储转换后的数据
CREATE TEMPORARY TABLE temp_transformed_data (column1 datatype,column2 datatype,...
);-- 从原始表中提取并转换数据，然后插入到临时表中
INSERT INTO temp_transformed_data (column1, column2, ...)
SELECT transform_function1(original_column1),transform_function2(original_column2),...
FROM original_table;-- 将转换后的数据从临时表加载到目标表中
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM temp_transformed_data;-- 删除临时表（虽然它会在会话结束时自动删除）
DROP TEMPORARY TABLE temp_transformed_data;

请注意，具体的SQL语法和函数可能因数据库系统的不同而有所差异。上述案例是基于MySQL的语法编写的，但类似的概念和操作可以在其他数据库系统中实现。