跳到主要内容

MySQL CRUD 操作

本章将详细介绍 MySQL 中的数据操作：插入（INSERT）、查询（SELECT）、更新（UPDATE）和删除（DELETE）。

知识速查​

操作语法示例说明
INSERTINSERT INTO tbl (col) VALUES (val);插入新行
SELECTSELECT col FROM tbl WHERE cond;查询数据
UPDATEUPDATE tbl SET col=val WHERE cond;更新现有行
DELETEDELETE FROM tbl WHERE cond;删除行
REPLACEREPLACE INTO tbl (col) VALUES (val);替换（删除+插入）
MERGEINSERT ... ON DUPLICATE KEY UPDATE ...存在则更新，否则插入

演示数据​

为了便于演示，我们先创建示例表和数据：

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age TINYINT UNSIGNED,
    salary DECIMAL(10, 2),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    total_amount DECIMAL(12, 2),
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT（插入数据）​

INSERT 语句用于向表中插入新数据。理解 INSERT 的各种用法对于数据操作至关重要。

基本语法​

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

这个语法清晰地说明了：我们需要指定目标表名、要插入数据的列名，以及对应的值。列名和值的顺序必须一一对应。

插入单行数据​

-- 指定列名插入
INSERT INTO users (username, email, age, salary)
VALUES ('张三', '[email protected]', 25, 8000.00);

-- 省略列名（按表定义顺序）
INSERT INTO users
VALUES (NULL, '李四', '[email protected]', 30, 9000.00, 'active', NOW());

-- 使用 DEFAULT 关键字
INSERT INTO users (username, email, age)
VALUES ('王五', '[email protected]', DEFAULT);

要点说明：

  • NULL 或不指定自增列，让 MySQL 自动生成 ID。自增列会自动分配一个唯一的递增值。
  • 未指定的列会使用默认值（如果有）或 NULL。如果列定义为 NOT NULL 且没有默认值，插入时会报错。
  • 推荐始终指定列名，使代码更清晰且不依赖列顺序。当表结构变化时，指定列名的语句更不容易出错。

插入多行数据​

-- 一次插入多行（推荐，性能更好）
INSERT INTO users (username, email, age, salary) VALUES
    ('赵六', '[email protected]', 28, 7500.00),
    ('孙七', '[email protected]', 35, 12000.00),
    ('周八', '[email protected]', 22, 6000.00);

多行插入的优势：

  • 减少网络往返次数：客户端与服务器之间只需要一次通信
  • 减少 SQL 解析次数：MySQL 只需解析一次语句
  • 性能优于多次单行插入：批量插入的效率通常是单行插入的数倍

在实际应用中，当需要插入大量数据时，应该优先考虑多行插入或使用 LOAD DATA INFILE。

INSERT ... SET 语法​

-- MySQL 特有语法，更清晰
INSERT INTO users
SET username = '吴九',
    email = '[email protected]',
    age = 27,
    salary = 8500.00;

这种语法是 MySQL 的扩展，不是标准 SQL。它的优点是每个字段和值对应清晰，特别适合字段较多的场景。缺点是只能插入单行数据。

INSERT ... SELECT​

从其他表复制数据：

-- 从另一个表复制数据
INSERT INTO users_backup (username, email, age)
SELECT username, email, age FROM users WHERE status = 'active';

-- 结合条件复制
INSERT INTO vip_users (username, email)
SELECT username, email FROM users WHERE salary > 10000;

INSERT ... SELECT 非常适合数据迁移、数据备份、数据汇总等场景。需要注意的是，SELECT 查询的列数和数据类型必须与目标表的列匹配。

INSERT IGNORE​

忽略重复键错误：

-- 如果 email 重复，忽略此条记录，不报错
INSERT IGNORE INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25);

当表中存在唯一索引或主键约束时，插入重复值会报错。使用 INSERT IGNORE 可以让 MySQL 静默忽略这些错误，继续执行后续操作。这在批量导入数据时很有用，可以跳过已存在的记录。

需要注意的是，INSERT IGNORE 不仅忽略重复键错误，还会忽略其他非致命错误（如数据类型转换警告），使用时要谨慎。

ON DUPLICATE KEY UPDATE​

存在则更新，不存在则插入：

-- 如果 email 存在，则更新用户名和年龄
INSERT INTO users (username, email, age, salary)
VALUES ('张三', '[email protected]', 26, 8500.00)
ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    age = VALUES(age),
    salary = VALUES(salary);

-- MySQL 8.0.20+ 可以使用别名
INSERT INTO users (username, email, age, salary)
VALUES ('张三', '[email protected]', 26, 8500.00) AS new
ON DUPLICATE KEY UPDATE
    username = new.username,
    age = new.age,
    salary = new.salary;

这是实现 "upsert"（update or insert）操作的标准方式。当插入的记录与现有记录的主键或唯一键冲突时，执行 UPDATE 操作；否则执行 INSERT 操作。

VALUES() 函数返回的是 INSERT 语句中尝试插入的值。在 MySQL 8.0.20+ 中，推荐使用别名语法，更加清晰易读。

REPLACE​

替换数据（先删除再插入）：

-- 如果 email 存在，先删除原记录，再插入新记录
REPLACE INTO users (username, email, age, salary)
VALUES ('张三', '[email protected]', 25, 8000.00);

REPLACE vs ON DUPLICATE KEY UPDATE：

特性REPLACEON DUPLICATE KEY UPDATE
原有 ID会被删除，生成新 ID保持不变
触发器触发 DELETE 和 INSERT触发 UPDATE
性能较低（两次操作）较高
影响影响自增ID和关联数据仅更新指定字段

大多数情况下，推荐使用 ON DUPLICATE KEY UPDATE 而不是 REPLACE，因为它不会改变记录的主键值，也不会影响关联的外键数据。

SELECT（查询数据）​

SELECT 是 SQL 中最常用的语句，用于从数据库中检索数据。掌握 SELECT 的各种用法是数据库操作的基础。

基本语法​

SELECT 列1, 列2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 分组]
[HAVING 分组条件]
[ORDER BY 排序]
[LIMIT 限制];

SELECT 语句的执行顺序是：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。理解这个顺序对于编写复杂查询很重要。

基础查询​

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT username, email FROM users;

-- 使用别名
SELECT 
    username AS 用户名,
    email AS 邮箱,
    salary AS 薪资
FROM users;

-- 计算字段
SELECT 
    username,
    salary,
    salary * 12 AS 年薪,
    CONCAT(username, ' - ', email) AS 用户信息
FROM users;

-- 去重
SELECT DISTINCT status FROM users;

-- 限制行数
SELECT * FROM users LIMIT 5;           -- 前 5 条
SELECT * FROM users LIMIT 5 OFFSET 10; -- 跳过 10 条，取 5 条
SELECT * FROM users LIMIT 10, 5;       -- 从第 11 条开始，取 5 条

SELECT * 会返回表中的所有列，在生产环境中不推荐使用，因为：

  1. 当表结构变化时，查询结果会改变
  2. 返回不需要的数据会浪费网络带宽
  3. 可能暴露敏感字段

WHERE 条件​

WHERE 子句用于过滤数据，只返回满足条件的行。

-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age < 30;
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age <=> NULL;  -- NULL 安全比较

-- 逻辑运算符
SELECT * FROM users WHERE age > 25 AND salary > 8000;
SELECT * FROM users WHERE age < 25 OR age > 30;
SELECT * FROM users WHERE NOT status = 'inactive';

-- BETWEEN 范围
SELECT * FROM users WHERE age BETWEEN 25 AND 35;
SELECT * FROM users WHERE salary NOT BETWEEN 5000 AND 10000;

-- IN 列表
SELECT * FROM users WHERE age IN (25, 30, 35);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- LIKE 模糊匹配
SELECT * FROM users WHERE username LIKE '张%';    -- 以张开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以 @gmail.com 结尾
SELECT * FROM users WHERE username LIKE '%三';  -- 包含三
SELECT * FROM users WHERE username LIKE '_三';   -- 两个字符，第二个是三

-- NULL 处理
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;

LIKE 通配符说明：

通配符说明
%匹配任意多个字符（包括零个字符）
_匹配单个字符

需要注意的是，NULL 值在 SQL 中是一个特殊的值，表示"未知"。任何与 NULL 的比较（如 = NULL、<> NULL）都会返回 NULL（不是 true 也不是 false）。必须使用 IS NULL 或 IS NOT NULL 来判断，或者使用 <=> 进行 NULL 安全比较。

ORDER BY 排序​

-- 单列排序
SELECT * FROM users ORDER BY age;           -- 升序（默认）
SELECT * FROM users ORDER BY age ASC;       -- 升序
SELECT * FROM users ORDER BY age DESC;      -- 降序

-- 多列排序
SELECT * FROM users ORDER BY status, salary DESC;

-- 按表达式排序
SELECT * FROM users ORDER BY salary * 12 DESC;

-- 按列位置排序
SELECT username, salary FROM users ORDER BY 2 DESC;  -- 按第 2 列排序

多列排序时，MySQL 先按第一列排序，如果第一列的值相同，再按第二列排序，以此类推。按列位置排序的语法虽然方便，但可读性较差，不推荐在生产代码中使用。

GROUP BY 分组​

GROUP BY 用于将数据按指定列分组，通常与聚合函数一起使用。

-- 基本分组
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status;

-- 多列分组
SELECT status, age, COUNT(*) AS count
FROM users
GROUP BY status, age;

-- 聚合函数
SELECT 
    status,
    COUNT(*) AS 总数,
    AVG(salary) AS 平均薪资,
    SUM(salary) AS 薪资总和,
    MAX(salary) AS 最高薪资,
    MIN(salary) AS 最低薪资
FROM users
GROUP BY status;

常用的聚合函数包括：

  • COUNT(*)：统计行数
  • COUNT(列名)：统计非 NULL 值的数量
  • SUM(列名)：求和
  • AVG(列名)：求平均值
  • MAX(列名)：求最大值
  • MIN(列名)：求最小值

HAVING 子句​

HAVING 用于过滤分组后的结果（类似 WHERE，但作用于分组）：

-- 查询平均薪资大于 8000 的状态组
SELECT status, AVG(salary) AS avg_salary
FROM users
GROUP BY status
HAVING avg_salary > 8000;

-- WHERE 与 HAVING 结合
SELECT status, COUNT(*) AS count
FROM users
WHERE age >= 25
GROUP BY status
HAVING count > 2;

WHERE vs HAVING：

子句作用阶段说明
WHERE分组前过滤原始行
HAVING分组后过滤分组结果

WHERE 在分组前执行，不能使用聚合函数；HAVING 在分组后执行，可以使用聚合函数。在实际使用中，应该优先使用 WHERE 过滤数据，这样可以减少参与分组的数据量，提高查询效率。

多表连接查询​

在实际应用中，数据通常分散在多个表中，需要通过连接操作将数据组合起来。

INNER JOIN（内连接）​

返回两表中匹配的记录：

SELECT 
    users.username,
    orders.product_name,
    orders.total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

INNER JOIN 只返回两个表中都有匹配的记录。如果某用户没有订单，或者某订单没有对应的用户，这些记录都不会出现在结果中。

LEFT JOIN（左连接）​

返回左表所有记录，右表无匹配则为 NULL：

-- 查询所有用户及其订单（包括没有订单的用户）
SELECT 
    u.username,
    o.product_name,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

LEFT JOIN 返回左表的所有记录，即使右表中没有匹配的记录。这在查找"没有关联数据"的记录时很有用，比如找出没有订单的用户。

RIGHT JOIN（右连接）​

返回右表所有记录，左表无匹配则为 NULL：

SELECT 
    u.username,
    o.product_name
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN 与 LEFT JOIN 相反，返回右表的所有记录。实际上，RIGHT JOIN 可以通过交换表的位置用 LEFT JOIN 实现，所以实际使用中 RIGHT JOIN 较少使用。

多表连接​

SELECT 
    u.username,
    o.product_name,
    p.category
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
WHERE o.status = 'completed';

多表连接时，需要注意连接的顺序和条件。每个 JOIN 都需要指定连接条件，否则会产生笛卡尔积。

子查询​

子查询是嵌套在其他查询中的查询，可以出现在 WHERE、FROM、SELECT 等子句中。

标量子查询​

返回单个值：

-- 查询薪资高于平均薪资的用户
SELECT * FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

标量子查询返回单个值（一行一列），可以像使用常量一样使用它。如果子查询返回多行，会报错。

列子查询​

返回单列多行：

-- 查询有订单的用户
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- 使用 EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

IN 和 EXISTS 都可以用于检查子查询的结果。EXISTS 通常在子查询返回大量数据时性能更好，因为它只需要判断是否存在匹配，不需要返回所有值。

行子查询​

返回单行多列：

-- 查询与张三同部门同职位的用户
SELECT * FROM users
WHERE (department, position) = (
    SELECT department, position FROM users WHERE username = '张三'
);

FROM 子句子查询​

-- 统计每个用户的订单数
SELECT 
    u.username,
    o.order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

FROM 子句子查询也称为"派生表"，必须在派生表上使用别名。这种方式适合需要在子查询中进行复杂计算后再与主表连接的场景。

UPDATE（更新数据）​

UPDATE 语句用于修改表中已有的数据。

基本语法​

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
[WHERE 条件];

更新示例​

-- 更新单个字段
UPDATE users SET salary = 9000 WHERE username = '张三';

-- 更新多个字段
UPDATE users 
SET salary = 9500, age = 26 
WHERE username = '张三';

-- 使用表达式更新
UPDATE users SET salary = salary * 1.1 WHERE status = 'active';

-- 基于其他列更新
UPDATE users SET salary = salary + age * 100;

-- 更新为 NULL
UPDATE users SET age = NULL WHERE username = '李四';

多表更新​

-- 根据另一个表的数据更新
UPDATE users u
INNER JOIN orders o ON u.id = o.user_id
SET u.status = 'vip'
WHERE o.total_amount > 10000;

多表更新允许基于另一个表的数据来更新当前表。这在需要关联多个表的数据进行更新时非常有用。

ORDER BY 和 LIMIT​

-- 更新薪资最低的 3 个用户
UPDATE users
SET salary = salary + 1000
ORDER BY salary ASC
LIMIT 3;

ORDER BY 和 LIMIT 可以控制更新的顺序和数量。这在进行批量更新时很有用，可以分批次更新数据，避免长时间锁表。

注意

执行 UPDATE 时，务必确保 WHERE 条件正确。没有 WHERE 条件会更新所有行！

在执行 UPDATE 前，建议先用 SELECT 验证 WHERE 条件是否正确：

-- 先验证
SELECT * FROM users WHERE status = 'inactive';
-- 确认无误后再执行
UPDATE users SET status = 'deleted' WHERE status = 'inactive';

安全更新模式​

MySQL 默认启用安全更新模式，防止误操作：

-- 查看安全模式状态
SHOW VARIABLES LIKE 'sql_safe_updates';

-- 临时禁用（不推荐）
SET sql_safe_updates = 0;

安全更新模式要求：

  1. UPDATE 和 DELETE 必须有 WHERE 条件
  2. WHERE 条件必须使用键列或 LIMIT

这是一个重要的保护机制，在生产环境中应该保持启用。

DELETE（删除数据）​

DELETE 语句用于删除表中的数据。

基本语法​

DELETE FROM 表名 [WHERE 条件];

删除示例​

-- 删除指定记录
DELETE FROM users WHERE id = 5;

-- 删除符合条件的记录
DELETE FROM users WHERE status = 'inactive';

-- 删除所有数据（不推荐）
DELETE FROM users;

多表删除​

-- 删除没有订单的用户
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 从多个表删除
DELETE users, orders
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.status = 'deleted';

多表删除可以一次删除多个表中的相关数据。这在处理关联数据时很有用，但需要谨慎使用。

ORDER BY 和 LIMIT​

-- 删除最早创建的 10 条记录
DELETE FROM logs
ORDER BY created_at ASC
LIMIT 10;

TRUNCATE vs DELETE​

-- TRUNCATE：快速清空表
TRUNCATE TABLE users;

-- DELETE：逐行删除
DELETE FROM users;
特性TRUNCATEDELETE
速度快（DDL）慢（逐行）
条件不支持支持 WHERE
回滚不支持支持
自增 ID重置不重置
触发器不触发触发

TRUNCATE 是 DDL 语句，它会删除表中的所有数据并重置自增 ID，但不能回滚。DELETE 是 DML 语句，可以指定条件、可以回滚，但速度较慢。

事务处理​

事务是一组数据库操作的逻辑单元，要么全部成功，要么全部失败。事务保证了数据的完整性。

基本事务操作​

-- 开始事务
START TRANSACTION;

-- 或使用
BEGIN;

-- 执行操作
INSERT INTO users (username, email) VALUES ('测试用户', '[email protected]');
UPDATE users SET status = 'active' WHERE username = '测试用户';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务的 ACID 特性：

  • 原子性（Atomicity）：事务中的操作要么全部执行，要么全部不执行
  • 一致性（Consistency）：事务执行前后，数据库状态保持一致
  • 隔离性（Isolation）：并发事务之间互不影响
  • 持久性（Durability）：事务提交后，数据永久保存

保存点​

START TRANSACTION;

INSERT INTO users (username, email) VALUES ('用户A', '[email protected]');

-- 设置保存点
SAVEPOINT point_a;

INSERT INTO users (username, email) VALUES ('用户B', '[email protected]');

-- 回滚到保存点
ROLLBACK TO SAVEPOINT point_a;

-- 提交
COMMIT;  -- 只有用户A被插入

保存点允许在事务中创建多个回滚点，可以部分回滚而不影响整个事务。

事务隔离级别​

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

隔离级别说明：

隔离级别脏读不可重复读幻读
READ UNCOMMITTED可能可能可能
READ COMMITTED不可能可能可能
REPEATABLE READ不可能不可能可能
SERIALIZABLE不可能不可能不可能

MySQL 默认使用 REPEATABLE READ 隔离级别，通过 MVCC（多版本并发控制）和 Next-Key Lock 实现了在 RR 级别下防止幻读。

最佳实践​

查询优化建议​

  1. **避免 SELECT ***

    -- 不推荐
    SELECT * FROM users;

    -- 推荐：只查询需要的列
    SELECT id, username, email FROM users;

  2. 合理使用索引

    -- 为常用查询条件创建索引
    CREATE INDEX idx_status ON users(status);
    CREATE INDEX idx_created_at ON orders(created_at);

  3. 分页查询优化

    -- 传统分页（数据量大时性能差）
    SELECT * FROM users LIMIT 10000, 20;

    -- 优化：使用覆盖索引
    SELECT u.* FROM users u
    INNER JOIN (SELECT id FROM users LIMIT 10000, 20) tmp
    ON u.id = tmp.id;

    -- 或使用 WHERE 条件
    SELECT * FROM users WHERE id > 10000 LIMIT 20;

批量操作建议​

-- 批量插入（推荐）
INSERT INTO users (username, email) VALUES
    ('user1', '[email protected]'),
    ('user2', '[email protected]'),
    ('user3', '[email protected]');

-- 大批量数据导入
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(username, email, age);

LOAD DATA INFILE 是导入大量数据最快的方式，比 INSERT 语句快 20-100 倍。

小结​

本章我们学习了：

  1. INSERT：插入数据，包括单行、多行、从其他表复制
  2. SELECT：查询数据，包括条件、排序、分组、连接、子查询
  3. UPDATE：更新数据，包括表达式更新、多表更新
  4. DELETE：删除数据，包括条件删除、多表删除
  5. 事务处理：事务的开始、提交、回滚和隔离级别

练习​

  1. 向 users 表插入多条数据
  2. 查询年龄在 25-35 之间的活跃用户
  3. 统计每个状态的用户数量和平均薪资
  4. 使用连接查询获取用户及其订单信息
  5. 使用事务完成一个转账操作（从一个账户扣款，向另一个账户存款）

参考资料​