sql-select-into

SQL：使用 SELECT INTO 和 CTAS 从查询创建表

在数据库管理中，一项常见任务是根据查询结果创建新表。这对于备份、归档或将复杂数据物化以用于报告非常有用。为此，SQL 提供了两种主要构造：SELECT ... INTO 和 CREATE TABLE AS SELECT (CTAS)。它们的可用性和语法在不同的数据库系统之间有所不同。

理解核心概念

SELECT INTO 和 CTAS 都可以在单次操作中创建一个新表并用 SELECT 语句中的数据填充它。新表的结构（列名和数据类型）是从查询结果推断出来的。

关键点： 这些语句只复制数据和基本的列结构。它们不会自动复制源表中的约束（例如 PRIMARY KEY、FOREIGN KEY、CHECK）、索引、触发器或权限。这些必须在新表创建后手动添加。

方法一：CREATE TABLE AS SELECT (CTAS)

CTAS 语句是 ANSI SQL 标准的一部分，也是最广泛支持的方法，可在 PostgreSQL、MySQL、Oracle 和 SQLite 等系统中使用。

语法

CREATE TABLE new_table_name AS
SELECT column1, column2, ...
FROM existing_table_name
WHERE [condition];

示例：创建完整备份

我们从一个现代的 customers 表开始。

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    signup_date DATE NOT NULL,
    city VARCHAR(50)
);

INSERT INTO customers (id, name, email, signup_date, city) VALUES
(1, 'Alice Johnson', 'alice.j@email.com', '2023-01-15', 'New York'),
(2, 'Bob Williams', 'bob.w@email.com', '2023-02-20', 'Los Angeles'),
(3, 'Charlie Brown', 'charlie.b@email.com', '2023-02-22', 'Chicago'),
(4, 'Diana Prince', 'diana.p@email.com', '2023-03-05', 'New York');

以下 CTAS 语句创建一个 customers_backup 表并从 customers 复制所有数据。

-- 这在 PostgreSQL、MySQL、Oracle 等数据库中有效。
CREATE TABLE customers_backup AS
SELECT * FROM customers;

验证

您现在可以查询新表，它将具有相同的数据，但会缺少 PRIMARY KEY 和 UNIQUE 约束。

SELECT * FROM customers_backup;

方法二：SELECT ... INTO

此语法特定于某些数据库系统，最值得注意的是 Microsoft SQL Server (T-SQL)。MySQL 或 PostgreSQL 不支持这种形式。

语法 (SQL Server)

SELECT column1, column2, ...
INTO new_table_name
FROM existing_table_name
WHERE [condition];

示例：复制特定列

让我们使用 SQL Server 的语法创建一个新表，其中只包含来自纽约客户的姓名和电子邮件地址。

-- 这在 SQL Server 中有效
SELECT name, email
INTO new_york_customers
FROM customers
WHERE city = 'New York';

验证

SELECT * FROM new_york_customers;

name	email
Alice Johnson	alice.j@email.com
Diana Prince	diana.p@email.com

实际应用和最佳实践

1.  **归档数据：** 您可以使用 `WHERE` 子句将旧记录复制到存档表中，然后再从活动表中删除它们。

    ```sql
    -- 归档 2023 年之前的所有记录
    CREATE TABLE customers_archive_2022 AS
    SELECT * FROM customers WHERE EXTRACT(YEAR FROM signup_date) < 2023;
    ```

2.  **合并数据：** 使用 `JOIN` 操作创建非规范化表用于报告或分析。

    ```sql
    CREATE TABLE customer_order_summary AS
    SELECT
        c.id AS customer_id,
        c.name,
        c.email,
        COUNT(o.id) AS number_of_orders,
        SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name, c.email;
    ```

3.  **性能考量：** 从大型数据集创建表是 I/O 和日志密集型操作。最好在非高峰时段执行。对于超大型表，如果可用，请考虑使用平台特定的最小日志记录操作功能。

常见陷阱和调试

• 忘记添加约束/索引： 最常见的错误。创建表后，务必运行 ALTER TABLE 语句来添加主键、外键和索引，以确保数据完整性和性能。
• 语法不匹配： 在 PostgreSQL 等期望 CREATE TABLE AS 的系统上使用 SELECT INTO。始终查阅您特定数据库的文档。
• 表已存在： 如果 new_table_name 的表已存在，这两个语句都会失败。如果您打算替换它，必须首先 DROP 现有表。