sql-insert-query

SQL - INSERT 语句

1. SQL INSERT INTO 语句

INSERT INTO 语句用于向表中添加新的数据行。它是一个基本的数据操作语言（DML）命令。

语法

INSERT INTO 语句有两种常见形式。

形式 1：同时指定列名和要插入的值。这是推荐的方法，因为它对表结构的变化更具鲁棒性。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

形式 2：如果你要按照原始顺序为表的所有列添加值，则无需指定列名。

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

示例

首先，让我们创建一个 Customers 表。

CREATE TABLE Customers (
    id INT PRIMARY KEY AUTO_INCREMENT, -- PostgreSQL 中使用 SERIAL
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    city VARCHAR(100),
    signup_date DATE
);

现在，让我们使用推荐的语法插入一条记录。我们可以省略允许 NULL 或具有默认值的列，例如 city 和 signup_date。

INSERT INTO Customers (name, age)
VALUES ('Alice', 30);

2. 一次性插入多行

大多数现代 SQL 数据库都支持在单个语句中插入多行，这比运行多个单独的 INSERT 语句更高效。

INSERT INTO Customers (name, age, city, signup_date)
VALUES
    ('Bob', 25, 'New York', '2023-01-10'),
    ('Charlie', 35, 'Los Angeles', '2023-02-20'),
    ('Diana', 28, 'Chicago', '2023-03-15');

验证

你可以使用 SELECT 查询验证插入的数据。

SELECT * FROM Customers;

id	name	age	city	signup_date
1	Alice	30	NULL	NULL
2	Bob	25	New York	2023-01-10
3	Charlie	35	Los Angeles	2023-02-20
4	Diana	28	Chicago	2023-03-15

3. 从另一个表插入数据

一个常见的任务是将数据从一个表复制到另一个表。这通过 INSERT INTO … SELECT 语句完成。

语法

INSERT INTO table1 (column1, column2, ...)
SELECT columnA, columnB, ...
FROM table2
WHERE condition;

源列和目标列的数据类型必须兼容。

示例

让我们创建一个 Premium_Customers 表，并将所有年龄超过 30 岁的客户复制到其中。

CREATE TABLE Premium_Customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(100)
);

-- 现在，从 Customers 表复制数据
INSERT INTO Premium_Customers (id, name, city)
SELECT id, name, city
FROM Customers
WHERE age > 30;

注意：老旧的 INSERT INTO … TABLE 语法不是标准 SQL，应避免使用。INSERT INTO … SELECT 是正确、可移植的方法。

4. 插入后检索数据（RETURNING / OUTPUT）

通常，你需要获取刚刚插入的行的信息，例如其自动生成的 id。现代数据库提供了相应的子句来完成此操作，避免了第二次 SELECT 查询。

PostgreSQL 和 Oracle：RETURNING

INSERT INTO Customers (name, age, city)
VALUES ('Eve', 40, 'Houston')
RETURNING id, name;

此查询将插入新行并立即返回该新行的 id 和 name。

SQL Server：OUTPUT

INSERT INTO Customers (name, age, city)
OUTPUT INSERTED.id, INSERTED.name
VALUES ('Frank', 50, 'Miami');

5. 实际考量：事务和错误处理

在插入数据时，特别是多条相关联的行时，将语句包装在事务中至关重要。事务是一个“全有或全无”的操作。如果事务中的任何语句失败，所有先前的更改都可以被撤销（ROLLBACK）。

BEGIN TRANSACTION; -- 或者在 MySQL 中使用 START TRANSACTION

-- 尝试插入多条记录
INSERT INTO Orders (customer_id, order_date) VALUES (1, '2023-05-01');
INSERT INTO Order_Items (order_id, product_id, quantity) VALUES (101, 1, 2); -- 假设此处失败

-- 如果发生错误，你将发出 ROLLBACK 命令。
-- 如果一切成功，你将使用以下命令最终确定更改：
COMMIT;

如果你尝试插入违反约束（例如 PRIMARY KEY 或 NOT NULL 约束）的数据，数据库将引发错误，并且 INSERT 将失败。使用事务可确保数据库中不会留下部分数据。