Sql 简明教程

SQL - Temporary Tables

What are Temporary Tables?

临时表的名称很好地描述了它们的目的：它们是在数据库中创建的用于存储临时数据的表。我们可以执行与永久表类似的 SQL 操作，例如 CREATE、UPDATE、DELETE、INSERT、JOIN 等。但是，一旦当前客户端会话终止，这些表将被自动删除。此外，如果用户决定手动删除它们，也可以显式删除它们。

Temporary tables are pretty much what their name describes: they are the tables which are created in a database to store temporary data. We can perform SQL operations similar to the operations on permanent tables like CREATE, UPDATE, DELETE, INSERT, JOIN, etc. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually.

与 MySQL 等各种 RDBMS 一样，它们从版本 3.23 开始支持临时表。如果您使用的是低于 3.23 的 MySQL 旧版本，则无法使用临时表，但可以使用 heap tables 。

Various RDBMS, like MySQL, support temporary tables starting from version 3.23 onwards. If you are using an older version of MySQL than 3.23, you can’t use temporary tables, but you can use heap tables.

如前所述，临时表仅在客户端会话存续期间才会存在。如果您在 PHP 脚本中运行代码，那么临时表将在脚本执行完毕后自动销毁。如果您通过 MySQL 客户端程序连接到 MySQL 数据库服务器，那么临时表将在您关闭客户端连接或手动销毁该表之前一直存在。

As stated earlier, temporary tables will only last as long as the client session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through a MySQL client program, then the temporary table will exist until you close the client connection or manually destroy the table.

Creating Temporary Tables in MySQL

要在 MySQL 中创建临时表，我们遵循与创建常规数据库表相同的查询。但是，您将使用 CREATE TEMPORARY TABLE statement. ，而不是使用 CREATE TABLE 语句。

To create temporary tables in MySQL, we follow the same query as creating regular database tables. However, instead of using the CREATE TABLE statement, you use CREATE TEMPORARY TABLE statement.

Syntax

以下是创建临时表的语法：

Following is the syntax to create a temporary table −

CREATE TEMPORARY TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

以下是用于在 MySQL 数据库中创建临时表的 SQL 查询：

Following is the SQL Query to create a temporary table in MySQL database −

CREATE TEMPORARY TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

就像普通表一样，您可以使用 INSERT 语句将数据插入临时表中。以下查询向上述创建的临时表中插入 3 条记录：

Just like normal tables you can insert data into a temporary table using the INSERT statement. Following query inserts 3 records into the above created temporary table −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 );

将创建临时表 CUSTOMERS ，它将有如下记录：

The temporary table CUSTOMERS will be created and will have following records −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

当您发出 SHOW TABLES 命令时，您的临时表不会显示在表列表中。要验证是否已创建临时表，您需要使用 SELECT 语句检索其数据。由于在当前会话关闭时所有临时表都将被删除，因此如果您注销 MySQL 会话然后发出 SELECT 命令，那么您将找不到数据库中的临时表。

When you issue a SHOW TABLES command, then your temporary table will not be displayed in the list of tables. To verify whether the temporary table is created you need to retrieve its data using the SELECT statement. Since all the temporary tables will be removed when the current session is closed, if you log out of the MySQL session and then issue a SELECT command, you will not find temporary table in the database.

Dropping Temporary Tables in MySQL

虽然当您的数据库连接终止时所有临时表都会被 MySQL 删除，但如果您仍想手动删除它们，那么您可以通过发出 DROP TEMPORARY TABLE 命令来进行删除。

Though all the temporary tables are deleted by MySQL when your database connection gets terminated, still, if you want to delete them manually, then you can do so by issuing a DROP TEMPORARY TABLE command.

Syntax

以下是删除临时表的基本语法：

Following is the basic syntax to delete a temporary table:

DROP TEMPORARY TABLE table_name;

Example

以下查询删除了前面示例中创建的临时表 CUSTOMERS ：

Following query drops the temporary table CUSTOMERS created in the previous example −

DROP TEMPORARY TABLE CUSTOMERS;

Verification

由于我们已经删除了临时表 CUSTOMERS，因此如果您尝试使用 SELECT 语句检索其内容，那么它将生成一条错误消息，指出该表不存在。

Since we have removed the temporary table CUSTOMERS, if you try to retrieve the contents of it using the SELECT statement, it will generate an error saying the table does not exist.

SELECT * FROM CUSTOMERS;

这将生成以下结果：

This will produce following result −

ERROR 1146: Table 'TUTORIALS.CUSTOMERS' doesn't exist

Temporary Tables in SQL Server

在 MySQL 中创建的临时表仅在当前会话中可见。但是，在 Microsoft SQL Server 中您可以创建两类临时表。

The temporary table created in MySQL is visible only within the current session. But, in Microsoft SQL Server you can create two types of temporary tables.

  1. Local Temporary Tables: A Local Temporary Table is accessible only in the session that has created it. It is automatically deleted when the connection that has created it gets closed. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution.

  2. Global Temporary Tables: Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed.

Syntax of the Local Temporary Tables

要在 SQL Server 中创建局部临时表，单个 # 用作表名的前缀，如下所示：

To create Local Temporary Table in SQL Server a single # is used as the prefix of a table name, as shown below −

CREATE TABLE #table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Local Temporary Tables

以下查询在 SQL Server 中创建名为 CUSTOMERS 的局部临时表：

Following query creates a Local temporary table named CUSTOMERS in the SQL server −

CREATE TABLE #CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Syntax of the Global Temporary Tables

要创建全局临时表，我们需要在表名前加上前缀 ## ，如下所示：

To create a Global Temporary Table, we need to add the prefix ## before the table name, as shown below −

CREATE TABLE ##table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Global Temporary Tables

以下查询在 SQL Server 中创建名为 Buyers 的全局临时表：

Following query creates a Global temporary table named Buyers in the SQL server −

CREATE TABLE ##Buyers(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Dropping Temporary Tables in SQL Server

如果你想在 SQL Server 中手动删除临时表，则需要通过在局部临时表名前放置 和在全局临时表名前放置 # 来执行 DROP TABLE 语句。

If you want to drop a temporary table in SQL Server manually, you need to execute the DROP TABLE statement by placing before the local temporary table name and # before the global temporary table name.

Example

以下查询删除前面示例中创建的局部临时表 Customers 。

Following query removes the Local temporary table Customers created in the previous example.

DROP TABLE #Customers;

同时，以下查询删除全局临时表 Buyers 。

Whereas, following query removes the global temporary table Buyers.

DROP TABLE ##Buyers;