数据库中临时表与表变量的用法与应用场景

临时表：

临时表（Temporary Table）是在数据库中临时创建的表，用于存储临时性数据或在复杂查询中间结果的表。它们在数据库会话结束时自动被销毁，不会长期存储数据，因此适用于需要临时存储数据的场景。

1.1创建临时表

可以使用类似于创建普通表的语法来创建临时表。临时表的名称通常以特定的前缀(如"#"或"##"）来标识，以与普通表区分开。

创建临时表的语句可以包括表的列定义、约束和索引等。

-----#TempTable为表名称 
CREATE TABLE #TempTable 
(
    ID INT NOT NULL,
    Name VARCHAR(40) NULL
)

1.2插入数据、更新数据

可以使用INSERT语句将数据插入临时表中，与普通表的插入操作相似。

可以使用UPDATE语句来更新临时表中的数据，语法与更新普通表的语法相同。

--可以使用逐条插入
INSERT INTO #TempTable (ID, Name) 
VALUES (1, 'John')


--也可以使用批量插入
INSERT INTO #TempTable (ID,Name)
SELECT ID,Name FROM  表名称


--更新
UPDATE #TempTable 
SET Name = 'Jane' 
WHERE ID = 1

1.3查询、删除临时表

可以使用SELECT语句从临时表中检索数据。可以使用临时表的名称作为查询的来源，并像查询普通表一样执行查询操作。

可以使用DELETE语句从临时表中删除数据，语法与删除普通表数据的语法相同。

注：如果需要多次删除临时表数据，建议使用TRUNCATE  TABLE 进行删除，这样会存储少量的日志信息，但TRUNCATE TABLE 语句不能用于具有外键约束的表。

--查询
SELECT ID,NAME FROM  #TempTable

--DELETE 语句用于从表中删除指定的行。
DELETE FROM #TempTable WHERE ID=1 

--TRUNCATE TABLE 语句用于快速删除表中的所有数据。
TRUNCATE TABLE  #TempTable         

--DROP TABLE 语句用于删除整个表。它不仅会删除表中的所有数据，还会删除表本身及其相关的索引、触发器和约束。
DROP  TABLE  #TempTable

1.4临时表的应用场景 

临时表常见应用场景：

1. 复杂查询优化：当需要进行复杂的数据查询和分析时，可以使用临时表来存储中间结果，以便后续查询使用。通过将中间结果存储在临时表中，可以减少查询的复杂性和提高性能。

2. 数据筛选和过滤：临时表可以用于存储满足特定条件的数据子集。通过将数据筛选和过滤的结果存储在临时表中，可以简化后续的查询和操作，并提高查询的效率。

3. 数据转换和清洗：在进行数据转换和清洗的过程中，临时表可以作为中间存储结构。可以将原始数据导入临时表中，对数据进行转换、清洗和规范化，然后将处理后的数据插入到目标表中。

4. 大批量数据处理：当需要处理大量数据时，临时表可以作为临时存储结构来处理数据。可以将大量数据分批次导入到临时表中，然后对临时表中的数据进行批量处理，最后将结果导入到目标表或其他系统中。

5. 过程性操作：临时表可用于存储在过程性操作中使用的临时数据。例如，在存储过程或函数中，可以使用临时表来存储中间结果，以便在过程执行过程中使用和处理。

6. 会话级别的数据共享：临时表在同一个数据库连接会话中的多个查询之间共享数据。这种共享能力可以用于在一个会话中的多个查询中共享临时结果，从而提高查询的可读性和可维护性。

7. 表关联和数据比较：临时表可以用于表关联操作和数据比较。可以将需要关联或比较的数据存储在临时表中，然后通过临时表进行连接操作或数据比较。

 1.5临时表的优缺点

优点：

1. 临时性：临时表只在当前会话中存在，会话结束后自动销毁，不占用永久存储空间。这减少了数据库中的数据冗余，保持数据库的整洁性。

2. 提高性能：通过使用临时表存储中间结果，可以优化复杂查询的性能。临时表提供了更好的可读性和可优化性，同时还可以通过索引等技术进一步提高查询性能。

3. 共享数据：临时表在同一个会话中的多个查询之间共享数据，这使得复杂查询更易于编写和维护。它还可以在存储和检索数据方面提供更高的灵活性。

缺点：

1. 数据丢失：临时表只在当前会话中存在，会话结束后数据会被自动清除。如果需要长期存储数据，临时表不适合使用。

2. 资源占用：临时表占用数据库的内存和其他资源。当临时表的数据量较大或会话并发量较高时，可能会对数据库的性能和资源利用产生一定的影响。

3. 命名冲突：临时表的命名通常以特定前缀或后缀来标识，以与普通表区分开。但如果命名不当，可能会导致与其他表发生命名冲突，造成意外的结果。

总结：在使用临时表时，需要根据具体的需求和场景权衡其优缺点，确保正确使用临时表的优势，并避免潜在的缺点。 

表变量 ：

表变量（Table Variable）是在数据库中声明和使用的临时表。与临时表类似，它可以存储临时数据或中间结果，并在当前会话结束时自动销毁。

表变量的数据定义与使用：

--@TempTable表名称
DECLARE @TempTable TABLE 
(
    ID INT,
    Name VARCHAR(40)
)

--插入表变量数据
INSERT INTO @TempTable (ID, Name) VALUES (1, 'John')

--查询表变量数据
SELECT * FROM @TempTable

--更新表变量数据
UPDATE @TempTable SET Name = 'Jane' WHERE ID = 1

--删除表变量数据
DELETE FROM @TempTable WHERE ID = 1

注意事项：

• 表变量的作用域限于声明它的批处理、存储过程或语句块。在作用域之外无法访问表变量。
• 表变量可以在查询中使用，可以进行连接（JOIN）操作、子查询、聚合函数等操作。
• 表变量可以有索引，通过创建索引可以提高查询性能。
• 表变量的命名应遵循数据库命名规范，以避免与其他对象发生命名冲突。

 表变量的应用场景：：

表变量常见应用场景：

1. 存储中间结果：在复杂查询中，可以使用表变量来存储中间计算结果，以便后续查询使用。这样可以提高查询性能和可读性，减少复杂查询语句的嵌套。

2. 临时数据处理：当需要对一部分数据进行临时的处理、转换或分析时，可以使用表变量来存储这些临时数据。例如，在数据清洗或ETL（提取、转换、加载）过程中，可以使用表变量来存储清洗后的数据。

3. 参数化查询：可以将表变量作为参数传递给存储过程或函数，以便在内部使用和处理。这对于需要在存储过程或函数中对临时数据进行操作和返回结果的场景非常有用。

4. 数据集合操作：可以使用表变量来存储和处理一组数据，进行集合操作，如合并、交集、差集等。表变量提供了一种方便的方式来处理和操作数据集合。

5. 批量操作：当需要一次性插入大量数据时，使用表变量可以更高效地进行批量操作，避免逐条插入的性能问题。

6. 递归查询：表变量可以在递归查询中作为递归数据的临时存储结构，用于处理递归关系的数据。

7. 动态SQL：在动态SQL查询中，表变量可以用作临时表存储动态生成的查询结果，以方便后续查询或操作。

 表变量的优缺点：

优点：

1. 临时性：表变量只在当前会话中存在，会话结束后自动销毁，不占用永久存储空间。
2. 提高性能：通过使用表变量存储中间结果，可以优化复杂查询的性能。
3. 共享数据：表变量在同一个会话中的多个查询之间共享数据，提高查询的可读性和可维护性。
4. 存储中间结果：表变量可以用于存储中间计算结果，减少复杂查询语句的嵌套。
5. 临时数据处理：表变量适用于临时存储和处理数据，如数据清洗或ETL过程。

缺点：

1. 数据丢失：表变量只在当前会话中存在，会话结束后数据会被自动清除，不适合长期存储数据。
2. 资源占用：表变量占用数据库的内存和其他资源，可能影响数据库的性能和资源利用。
3. 作用域限制：表变量的作用域仅限于定义它的批处理或存储过程。

 总结：

表变量的使用场景类似于临时表，适用于存储临时数据、处理中间结果、优化查询等情况。然而，与临时表相比，表变量更适合在较小规模的数据集上进行操作，并且对于复杂查询中的临时计算结果存储更加方便。