当前位置: 首页 > article >正文

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 leadTime
    FROM pln_order_pool
    WHERE 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 planNums
    FROM plan
    WHERE type = '计划' AND date = '2022-08-09'
),
-- 计算当天的实际完成总数,并存储在另一个临时表中
finish_nums AS (
    SELECT SUM(num) AS finishNums
    FROM plan
    WHERE 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的语法编写的,但类似的概念和操作可以在其他数据库系统中实现。


http://www.kler.cn/news/337165.html

相关文章:

  • 【DC00026】基于java swing+MySQL图书借阅管理系统
  • 基于SpringBoot+Uniapp的家庭记账本微信小程序系统设计与实现
  • 【Kubernetes】常见面试题汇总(五十四)
  • 前端的全栈混合之路Meteor篇(四):支持自定义对象序列化的EJSON介绍
  • JAVA开发系统环境搭建
  • 用Arduino单片机制作一个简单的音乐播放器
  • Ajax开发技术
  • 安卓真机调试“no target device found“以及“ INSTALL_FAILED_USER_RESTRICTED“两个问题的解决办法
  • 商云10如何开启积分付款功能
  • 人工智能与医疗健康的融合:未来的机遇与挑战
  • 《 防MAC 地址欺骗攻击》
  • Steam Deck掌机可装“黑苹果” 开发者成功安装macOS 15 Sequoia
  • 20款奔驰CLS300升级原厂抬头显示HUD 23P智能辅助驾驶 触摸屏人机交互系统
  • MFC工控项目实例二十三模拟量输入设置界面
  • 反射在Go语言中的具体应用场景
  • YOLO系列模型
  • 【光追模组】使命召唤7黑色行动光追mod,调色并修改光影,并且支持光追效果,游戏画质大提升
  • GPU Puzzles讲解(一)
  • Windows 11:如何轻松安装或卸载 Copilot 应用(多种方法)
  • Qt+VS2019+大恒相机相机回调方式总结