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

1、SQL 临时表

在SQL中，临时表（Temporary Table）是一种在会话或毗连期间临时存储数据的表。它们对于存储中心效果、简化复杂查询以及进步性能非常有用。以下是一个创建和利用临时表的示例。
假设我们有一个名为 employees 的表，此中包罗以下列：employee_id, first_name, last_name, department_id, 和 salary。我们想要创建一个临时表来存储某个特定部分的员工，并对这些员工举行一些分析。
1.1、创建并利用临时表的步调

• 创建临时表：
  起首，我们须要创建一个临时表来存储数据。临时表在差别的数据库体系中大概有所差别，但通常利用 CREATE TEMPORARY TABLE 语句。
  
• 插入数据：
  接下来，我们将从原始表中插入数据到临时表中。
  
• 查询和分析数据：
  末了，我们可以对临时表举行查询和分析。
  

示例代码

以下是一个完备的示例，展示了怎样在MySQL中创建和利用临时表：

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

复制代码

1.2、注意事项

• 临时表的生命周期：
  
  
  
  • 在MySQL中，临时表在会话竣事时会主动删除。
    
  • 在其他数据库体系（如SQL Server）中，临时表分为局部临时表（以 # 开头，会话竣事时删除）和全局临时表（以 ## 开头，全部会话不再引用时删除）。
    
  
  
• 权限：
  
  
  
  • 创建临时表通常须要 CREATE TEMPORARY TABLES 权限。
    
  
  
• 变乱处置惩罚：
  
  
  
  • 在某些数据库体系中，临时表大概不支持变乱处置惩罚或具有特殊的举动。
    
  
  
• 定名辩说：
  
  
  
  • 在同一会话中，不能创建两个同名的临时表。
    
  
  

通过创建和利用临时表，你可以有用地处置惩罚复杂的查询和数据利用，同时克制对原始数据的直接修改。
固然可以。以下是一些现实的利用SQL临时表的案例，这些案例展示了临时表在差别场景下的应用。
案例一：盘算订单交付周期统计信息

假设有一个名为 pln_order_pool 的订单表，包罗以下字段：create_time（订单创建时间）、reality_deliver_time（现实交付时间）、order_state（订单状态）等。我们须要盘算全部状态为“finish”的订单的匀称交付周期、最大交付周期和最小交付周期。

1. -- 计算每个订单的交付周期，并存储在临时表中
2. WITH order_lead_times AS (
3.     SELECT DATEDIFF(reality_deliver_time, create_time) AS leadTime
4.     FROM pln_order_pool
5.     WHERE order_state = 'finish'
6. )
7. -- 从临时表中计算平均交付周期、最大交付周期和最小交付周期
8. SELECT
9.     AVG(leadTime) AS avgLeadTime,
10.     MAX(leadTime) AS maxLeadTime,
11.     MIN(leadTime) AS minLeadTime
12. FROM order_lead_times;

复制代码

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

假设有一个名为 plan 的表，用于记载某项目的筹划数和现实完成数。该表包罗以下字段：date（日期）、type（范例，‘筹划’或’实出’）、num（数量）。我们须要盘算某一天的筹划完成率，即该天的现实完成数与筹划数的比值。

1. -- 计算当天的计划总数，并存储在临时表中
2. WITH plan_nums AS (
3.     SELECT SUM(num) AS planNums
4.     FROM plan
5.     WHERE type = '计划' AND date = '2022-08-09'
6. ),
7. -- 计算当天的实际完成总数，并存储在另一个临时表中
8. finish_nums AS (
9.     SELECT SUM(num) AS finishNums
10.     FROM plan
11.     WHERE type = '实出' AND date = '2022-08-09'
12. )
13. -- 从两个临时表中计算计划完成率
14. SELECT
15.     planNums,
16.     finishNums,
17.     IFNULL(ROUND(finishNums / planNums * 100, 2), 0) AS finishRate
18. FROM plan_nums, finish_nums;

复制代码

在这个案例中，我们利用了两个CTE来分别创建存储当天筹划总数和现实完成总数的临时表。然后，我们从这两个临时表中盘算出了筹划完成率。
案例三：数据转换和ETL过程

在ETL（提取、转换、加载）过程中，临时表常用于临时生存和转换数据。比方，我们大概须要从多个表中提取数据，举行转换和洗濯，然后将效果加载到目的表中。在这个过程中，临时表可以作为一个中心存储点，用于存储转换后的数据。

1. -- 创建一个临时表来存储转换后的数据
2. CREATE TEMPORARY TABLE temp_transformed_data (
3.     column1 datatype,
4.     column2 datatype,
5.     ...
6. );
8. -- 从原始表中提取并转换数据，然后插入到临时表中
9. INSERT INTO temp_transformed_data (column1, column2, ...)
10. SELECT
11.     transform_function1(original_column1),
12.     transform_function2(original_column2),
13.     ...
14. FROM original_table;
16. -- 将转换后的数据从临时表加载到目标表中
17. INSERT INTO target_table (column1, column2, ...)
18. SELECT column1, column2, ...
19. FROM temp_transformed_data;
21. -- 删除临时表（虽然它会在会话结束时自动删除）
22. DROP TEMPORARY TABLE temp_transformed_data;

复制代码

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