Muliminty Note

03-数据操作语言

7分钟阅读

数据操作语言 (DML)

DML (Data Manipulation Language) 用于对数据库中的数据进行增删改操作。

INSERT 插入数据

基本语法

-- 语法
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

插入单行数据

-- 指定列名插入（推荐）
INSERT INTO employees (name, department, salary, age, hire_date)
VALUES ('新员工', '技术部', 12000, 26, '2024-01-15');
 
-- 省略列名（需要按顺序提供所有列的值，不推荐）
INSERT INTO employees 
VALUES (NULL, '新员工2', '市场部', 11000, 24, '2024-02-01');

插入多行数据

-- 一次插入多行（高效）
INSERT INTO employees (name, department, salary, age, hire_date) VALUES
('员工A', '技术部', 13000, 27, '2024-01-10'),
('员工B', '市场部', 11000, 25, '2024-01-12'),
('员工C', '人事部', 10000, 23, '2024-01-15');

插入查询结果

-- 从其他表复制数据
INSERT INTO employees_backup (name, department, salary)
SELECT name, department, salary 
FROM employees 
WHERE department = '技术部';

插入或更新 (UPSERT)

-- MySQL: ON DUPLICATE KEY UPDATE
INSERT INTO employees (id, name, salary)
VALUES (1, '张三', 16000)
ON DUPLICATE KEY UPDATE salary = VALUES(salary);
 
-- MySQL 8.0+: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO employees (id, name, salary)
VALUES (1, '张三', 16000) AS new
ON DUPLICATE KEY UPDATE salary = new.salary;
 
-- PostgreSQL: ON CONFLICT
INSERT INTO employees (id, name, salary)
VALUES (1, '张三', 16000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

忽略重复插入

-- MySQL: INSERT IGNORE
INSERT IGNORE INTO employees (id, name, salary)
VALUES (1, '张三', 16000);  -- 如果 id=1 存在，则忽略
 
-- PostgreSQL: ON CONFLICT DO NOTHING
INSERT INTO employees (id, name, salary)
VALUES (1, '张三', 16000)
ON CONFLICT DO NOTHING;

UPDATE 更新数据

基本语法

-- 语法
UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

更新单列

-- 给张三加薪
UPDATE employees 
SET salary = 18000 
WHERE name = '张三';

更新多列

-- 更新多个字段
UPDATE employees 
SET salary = 20000, department = '研发部' 
WHERE id = 1;

基于计算更新

-- 所有人薪资上涨 10%
UPDATE employees 
SET salary = salary * 1.1;
 
-- 技术部员工薪资上涨 15%
UPDATE employees 
SET salary = salary * 1.15 
WHERE department = '技术部';

使用子查询更新

-- 将薪资更新为部门平均薪资
UPDATE employees e
SET salary = (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department = e.department
)
WHERE salary < 10000;

更新注意事项

-- ⚠️ 危险：不加 WHERE 会更新所有行！
UPDATE employees SET salary = 0;  -- 这会清空所有人的薪资！
 
-- ✅ 安全：先用 SELECT 验证条件
SELECT * FROM employees WHERE department = '技术部';
-- 确认无误后再执行 UPDATE
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';

DELETE 删除数据

基本语法

-- 语法
DELETE FROM 表名 WHERE 条件;

删除指定行

-- 删除指定员工
DELETE FROM employees WHERE id = 10;
 
-- 删除离职员工
DELETE FROM employees WHERE status = 'resigned';

删除多行

-- 删除所有实习生
DELETE FROM employees WHERE department = '实习生';
 
-- 删除薪资低于 8000 的员工
DELETE FROM employees WHERE salary < 8000;

删除所有数据

-- 方式1：DELETE（可回滚，保留表结构和自增值）
DELETE FROM employees;
 
-- 方式2：TRUNCATE（更快，重置自增值，不可回滚）
TRUNCATE TABLE employees;

DELETE vs TRUNCATE

特性DELETETRUNCATE
删除方式逐行删除删除整个表数据
速度慢（大数据量）快
WHERE 条件支持不支持
事务回滚支持不支持（DDL）
自增值保留重置
触发器触发不触发

删除注意事项

-- ⚠️ 危险：不加 WHERE 会删除所有数据！
DELETE FROM employees;  -- 删除所有员工！
 
-- ✅ 安全：先用 SELECT 验证
SELECT * FROM employees WHERE hire_date < '2018-01-01';
-- 确认后再删除
DELETE FROM employees WHERE hire_date < '2018-01-01';
 
-- ✅ 使用 LIMIT 限制删除数量
DELETE FROM employees WHERE status = 'inactive' LIMIT 100;

事务中的 DML

-- 开启事务
START TRANSACTION;  -- 或 BEGIN;
 
-- 执行多个 DML 操作
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
 
-- 如果都成功，提交
COMMIT;
 
-- 如果有问题，回滚
-- ROLLBACK;

返回受影响的行

-- MySQL: 使用 ROW_COUNT()
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';
SELECT ROW_COUNT();  -- 返回更新的行数
 
-- PostgreSQL: 使用 RETURNING
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = '技术部'
RETURNING *;  -- 返回更新后的数据
 
DELETE FROM employees 
WHERE id = 10
RETURNING id, name;  -- 返回删除的数据

实践示例

批量更新优化

-- 方式1：CASE WHEN（一条语句批量更新）
UPDATE employees
SET salary = CASE id
    WHEN 1 THEN 15000
    WHEN 2 THEN 18000
    WHEN 3 THEN 12000
    ELSE salary
END
WHERE id IN (1, 2, 3);
 
-- 方式2：临时表 JOIN 更新
CREATE TEMPORARY TABLE salary_updates (
    id INT,
    new_salary DECIMAL(10, 2)
);
 
INSERT INTO salary_updates VALUES (1, 15000), (2, 18000), (3, 12000);
 
UPDATE employees e
JOIN salary_updates s ON e.id = s.id
SET e.salary = s.new_salary;

安全删除（软删除）

-- 添加删除标记列
ALTER TABLE employees ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
 
-- 软删除：更新标记而非真正删除
UPDATE employees SET is_deleted = TRUE WHERE id = 10;
 
-- 查询时过滤已删除数据
SELECT * FROM employees WHERE is_deleted = FALSE;

总结

操作关键字示例
插入INSERTINSERT INTO t (a, b) VALUES (1, 2)
更新UPDATEUPDATE t SET a = 1 WHERE id = 1
删除DELETEDELETE FROM t WHERE id = 1
清空TRUNCATETRUNCATE TABLE t

最佳实践

  1. UPDATE/DELETE 前先 SELECT：验证 WHERE 条件
  2. 使用事务：保证数据一致性
  3. 避免无 WHERE 的 UPDATE/DELETE：防止误操作
  4. 考虑软删除：保留数据可追溯

上一节：02-数据查询基础 下一节：04-高级查询技巧 — JOIN 与子查询

sql dml insert update delete

关系图谱

反向链接