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 个练习
查阅不认识的术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
动手做项目
通过引导式项目应用所学,含起始代码与解决方案。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。