MySQL CRUD 操作

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

知识速查

操作	语法示例	说明
INSERT	`INSERT INTO tbl (col) VALUES (val);`	插入新行
SELECT	`SELECT col FROM tbl WHERE cond;`	查询数据
UPDATE	`UPDATE tbl SET col=val WHERE cond;`	更新现有行
DELETE	`DELETE FROM tbl WHERE cond;`	删除行
REPLACE	`REPLACE INTO tbl (col) VALUES (val);`	替换（删除+插入）
MERGE	`INSERT ... 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 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
推荐始终指定列名，使代码更清晰且不依赖列顺序

插入多行数据

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

多行插入的优势：

减少网络往返次数
减少 SQL 解析次数
性能优于多次单行插入

INSERT ... SET 语法

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

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 IGNORE

忽略重复键错误：

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

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;

REPLACE

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

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

REPLACE vs ON DUPLICATE KEY UPDATE：

特性	REPLACE	ON DUPLICATE KEY UPDATE
原有 ID	会被删除，生成新 ID	保持不变
触发器	触发 DELETE 和 INSERT	触发 UPDATE
性能	较低（两次操作）	较高

SELECT（查询数据）

基本语法

SELECT 列1, 列2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 分组]
[HAVING 分组条件]
[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 条

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 通配符说明：

通配符	说明
`%`	匹配任意多个字符
`_`	匹配单个字符

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 列排序

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;

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	分组后	过滤分组结果

多表连接查询

INNER JOIN（内连接）

返回两表中匹配的记录：

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

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;

RIGHT JOIN（右连接）

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

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

多表连接

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';

子查询

标量子查询

返回单个值：

-- 查询薪资高于平均薪资的用户
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);

行子查询

返回单行多列：

-- 查询与张三同部门同职位的用户
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;

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;

注意

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

安全更新模式

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

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

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

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;

特性	TRUNCATE	DELETE
速度	快（DDL）	慢（逐行）
条件	不支持	支持 WHERE
回滚	不支持	支持
自增 ID	重置	不重置
触发器	不触发	触发

事务处理

基本事务操作

-- 开始事务
START TRANSACTION;

-- 或
BEGIN;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

保存点

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	不可能	不可能	不可能

最佳实践

查询优化建议

**避免 SELECT ***

-- 不推荐
SELECT * FROM users;

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

合理使用索引

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

分页查询优化

-- 传统分页（数据量大时性能差）
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);

小结

本章我们学习了：

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

练习

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

知识速查​

演示数据​

INSERT（插入数据）​

基本语法​

插入单行数据​

插入多行数据​

INSERT ... SET 语法​

INSERT ... SELECT​

INSERT IGNORE​

ON DUPLICATE KEY UPDATE​

REPLACE​

SELECT（查询数据）​

基本语法​

基础查询​

WHERE 条件​

ORDER BY 排序​

GROUP BY 分组​

HAVING 子句​

多表连接查询​

INNER JOIN（内连接）​

LEFT JOIN（左连接）​

RIGHT JOIN（右连接）​

多表连接​

子查询​

标量子查询​

列子查询​

行子查询​

FROM 子句子查询​

UPDATE（更新数据）​

基本语法​

更新示例​

多表更新​

ORDER BY 和 LIMIT​

安全更新模式​

DELETE（删除数据）​

基本语法​

删除示例​

多表删除​

ORDER BY 和 LIMIT​

TRUNCATE vs DELETE​

事务处理​

基本事务操作​

保存点​

事务隔离级别​

最佳实践​

查询优化建议​

批量操作建议​

小结​

练习​

参考资源​

知识速查

演示数据

INSERT（插入数据）

基本语法

插入单行数据

插入多行数据

INSERT ... SET 语法

INSERT ... SELECT

INSERT IGNORE

ON DUPLICATE KEY UPDATE

REPLACE

SELECT（查询数据）

基本语法

基础查询

WHERE 条件

ORDER BY 排序

GROUP BY 分组

HAVING 子句

多表连接查询

INNER JOIN（内连接）

LEFT JOIN（左连接）

RIGHT JOIN（右连接）

多表连接

子查询

标量子查询

列子查询

行子查询

FROM 子句子查询

UPDATE（更新数据）

基本语法

更新示例

多表更新

ORDER BY 和 LIMIT

安全更新模式

DELETE（删除数据）

基本语法

删除示例

多表删除

ORDER BY 和 LIMIT

TRUNCATE vs DELETE

事务处理

基本事务操作

保存点

事务隔离级别

最佳实践

查询优化建议

批量操作建议

小结

练习

参考资源