Skip to content

INSERT, UPDATE, DELETE

修改数据: insert 行, 更新 existing 行, and 删除使用 safety.

你将学到

  • INSERT 语法 for 单个 and 多个行
  • UPDATE 使用 SET and WHERE 到修改 existing 行
  • DELETE 使用 WHERE 到移除行 safely
  • Using RETURNING 到获取后面 inserted/更新行
  • Upsert 模式: INSERT ... ON CONFLICT

概念讲解

INSERT

INSERT INTO 表 (col1, col2) VALUES (val1, val2) adds a new row. You can insert multiple rows in one statement by listing multiple value tuples: VALUES (1, ''), (2, 'b'), (3, 'c'). This is much faster than separate INSERT statements because it is a single round-trip to the database. If you omit column names, you must provide values for every column in table order — always specify columns explicitly for clarity and safety.

UPDATE

UPDATE 表 SET col = 值 WHERE 条件 modifies existing rows. The SET clause can update multiple columns: SET name = '新', price = 10. Always include a WHERE clause — without it, every row in the table is updated. A good habit is to first run a SELECT with the same WHERE to preview which rows will be affected.

DELETE

DELETE FROM 表 WHERE 条件 removes rows. Like UPDATE, always use WHERE. In practice, many applications use "soft 删除" — a deleted_at timestamp column — instead of actually removing rows, so data can be recovered. Hard deletes are permanent.

RETURNING

PostgreSQL and SQLite support RETURNING * after INSERT, UPDATE, or DELETE. This returns the affected rows in one statement, which is very useful when you insert a row with an auto-generated ID and immediately need that ID: INSERT INTO 用户 (name) VALUES ('Ada') RETURNING id.

Upsert (ON CONFLICT)

INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL) lets you insert a row, and if it already exists (conflict on a unique key), update it instead. This is the standard "upsert" pattern for syncing data.

示例代码

              -- Insert multiple rows in one statement
INSERT INTO products (name, price, category, in_stock)
VALUES
  ('Keyboard', 49.99, 'electronics', TRUE),
  ('Mousepad', 9.99, 'accessories', TRUE),
  ('USB Cable', 5.99, 'accessories', FALSE);

-- Update with RETURNING (PostgreSQL)
UPDATE products
SET price = 39.99, in_stock = TRUE
WHERE name = 'Keyboard'
RETURNING id, name, price;

-- Delete safely with a WHERE clause
DELETE FROM products
WHERE in_stock = FALSE AND price < 10;

-- Upsert: insert or update on conflict
INSERT INTO products (name, price, category)
VALUES ('Keyboard', 34.99, 'electronics')
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price;
            

Four DML 模式: bulk insert, 更新使用 RETURNING, safe 删除使用 WHERE, and upsert 使用 ON CONFLICT.

动手试一试

  • SQL Formatter

    格式 INSERT, UPDATE, and DELETE 语句.

  • SQL Escaper

    Escape 用户输入之前 inserting 到 SQL 字符串.

常见错误

错误写法

运行 UPDATE or DELETE 不使用 WHERE

正确做法

不使用 WHERE, 每个行 是 affected. 总是包含 WHERE clause. 运行 SELECT 使用相同 WHERE 首先到 preview affected 行.

错误写法

Inserting 用户输入 directly 到 SQL 字符串

正确做法

这个 causes SQL injection. Use parameterized 查询 / prepared 语句相反的 字符串 concatenation. 从不 trust 用户输入.

练习 INSERT, UPDATE, DELETE

8 个练习

练习 INSERT, UPDATE, DELETE

查阅不认识的术语

新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。

查看术语表

动手做项目

通过引导式项目应用所学,含起始代码与解决方案。

查看项目

解读错误信息

用大白话解释常见错误——什么意思、为什么发生、如何修复。

查看错误