MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解
全面讲解MySQL数据库中增删改查(CRUD)四大核心操作:INSERT写入、SELECT查询、UPDATE更新与DELETE删除的标准语法、执行逻辑、批量操作及生产安全注意事项。
在关系型数据库的管理与开发中,最核心、最频繁的操作统称为 CRUD——即增加(Create)、读取(Read)、更新(Update)和删除(Delete)。在 MySQL 中,这四项能力分别由 INSERT、SELECT、UPDATE 和 DELETE 四个标准 SQL 语句承载。掌握这四类语句的基本语法、参数组合与边界约束,是所有数据库开发、数据分析与运维任务的基石。
为了让所有操作具备一致且可验证的执行上下文,本文以一张标准的会员用户表 users 为基准进行演示。在开始学习具体语句前,先了解该表的定义:
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
age TINYINT UNSIGNED DEFAULT 18,
status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
一、INSERT 语句:新增记录详解
INSERT INTO 语句用于向表中添加一行或多行新数据。在编写写入语句时,最稳健的做法是始终显式列出待插入的目标字段名称,避免因表结构新增列或调整字段顺序而导致插入失败或数据错位。
1. 标准单行插入语法
-- 规范写法:显式指定插入字段
INSERT INTO users (username, email, age, status)
VALUES ('zhangsan', 'zhangsan@example.com', 25, 'active');
在上述语句中,id 字段由于具备 AUTO_INCREMENT 属性,MySQL 会自动计算下一个可用序号递增填入;created_at 拥有默认值 CURRENT_TIMESTAMP,未声明时会自动填充当前时间戳。
2. 批量多行插入语法
当需要向表中同时写入成百上千条记录时,使用一条包含多个 VALUES 括号元组的批量插入语句,其性能远高于逐行执行单个 INSERT 语句。单次网络往返即可完成事务提交,极大降低了 I/O 损耗与连接开销:
-- 单语句写入多行记录
INSERT INTO users (username, email, age, status)
VALUES
('lisi', 'lisi@example.com', 22, 'active'),
('wangwu', 'wangwu@example.com', 30, 'inactive'),
('zhaoliu', 'zhaoliu@example.com', 28, 'active');
3. 主键与唯一索引冲突解决方案
当批量导入或并发写入时,若目标表存在 UNIQUE 索引,写入可能触发重复键错误(Duplicate entry)。MySQL 提供了两种典型语法处理此类冲突:
- INSERT IGNORE INTO:遇到冲突行时直接跳过并产生警告,继续写入后续无冲突数据行。
- ON DUPLICATE KEY UPDATE:若检测到唯一键冲突,则转为更新该行的指定字段,常用于“存在则更新,不存在则插入”的计数或同步场景:
-- 若邮箱已存在,则将对应记录的 age 更新为 26
INSERT INTO users (username, email, age)
VALUES ('zhangsan', 'zhangsan@example.com', 26)
ON DUPLICATE KEY UPDATE age = VALUES(age);
二、SELECT 语句:数据检索与投影详解
SELECT 是 SQL 中功能最丰富、使用最频繁的查询语句。掌握 SELECT 不仅要熟悉书写结构,更需要理解数据库底层对各个子句的执行顺序。
1. 字段投影控制
在开发与生产环境中,应杜绝使用 SELECT *。全字段检索会导致无法利用覆盖索引,增加网络传输带宽,并在表结构扩容时带来意外的内存占用。始终明确指定需要读取的列名:
-- 仅查询业务实际使用的字段
SELECT id, username, email FROM users;
2. WHERE 条件过滤与常用操作符
WHERE 子句用于筛选满足条件的行集合。常用操作符包括比较符号、区间筛选与集合匹配:
-- 综合查询:激活状态、年龄在 20 至 30 岁之间,且用户名包含 'zhang' 的前缀匹配
SELECT id, username, age, status
FROM users
WHERE status = 'active'
AND age BETWEEN 20 AND 30
AND username LIKE 'zhang%';
3. 排序与分页:ORDER BY 与 LIMIT
数据库返回的结果集默认是无序的(取决于存储引擎的物理存储与扫描路径)。若需要确定顺序,必须显式使用 ORDER BY:
-- 按注册时间倒序排列,并获取第一页的 10 条记录
SELECT id, username, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 0, 10;
LIMIT offset, count 中,第一个参数代表起始偏移量(从 0 开始),第二个参数代表读取的最大记录数。在大数据量分页时,深分页(例如 LIMIT 100000, 10)会导致扫描过多无效记录,可通过子查询关联主键进行优化。
关键认知:为什么 WHERE 子句中不能使用 SELECT 中定义的列别名?从图3清晰可见,WHERE 的执行阶段先于 SELECT 投影,在执行过滤时该别名尚未被计算生成;而在 ORDER BY 阶段,SELECT 已经完成计算,因此可以正常使用别名。
三、UPDATE 语句:修改现有记录详解
UPDATE 语句用于修改表中符合条件的已存在记录。与 INSERT 相比,UPDATE 具备更强的破坏性,因为一条缺少条件限制的更新语句会瞬间修改全表的全部行。
1. 标准更新语法与多字段更新
-- 将指定 id 为 1 的用户状态设为 active,并将年龄增加 1 岁
UPDATE users
SET status = 'active',
age = age + 1
WHERE id = 1;
SET 子句支持多个字段同时赋值,各字段之间使用半角逗号分隔。右侧表达式可以是固定常量,也可以基于当前列原有的数值进行递增、拼接等运算。
2. 防范全表误更新的生产规范
重大安全警示:若执行 UPDATE users SET status = 'inactive';(未附带 WHERE 条件),整张表所有用户的状态将被全部改写!在生产环境中执行修改前,请务必遵守以下步骤:
- 同条件查询核对:在执行
UPDATE前,先使用相同的WHERE条件执行SELECT COUNT(*)或SELECT id, ...,核实受影响的数据行是否符合预期。 - 添加 LIMIT 约束:若明确知道本次修改只针对单行(例如修改指定用户的邮箱),在语句末尾追加
LIMIT 1作为最后一道保险。 - 开启安全更新模式:在 MySQL 客户端中开启安全更新模式(
SET sql_safe_updates = 1;)。开启后,如果UPDATE没有使用主键或索引字段作为WHERE条件,或者完全缺失WHERE,数据库将直接拒绝执行并报错。
四、DELETE 语句:数据删除详解
DELETE 语句用于从数据表中按条件移除整行记录。与 UPDATE 类似,DELETE 同样高度依赖 WHERE 条件的精准约束。
1. 物理删除基础语法
-- 删除封禁状态且注册时间超过 1 年的用户,并限制单次最多删除 50 行
DELETE FROM users
WHERE status = 'banned'
AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 50;
在大数据量场景下,如果要清理大量历史过期数据,切忌一条无 LIMIT 的 DELETE 直接执行。大事务会导致长时间行锁甚至表锁,阻塞业务写入,并导致主从复制严重延迟。标准做法是通过带 LIMIT 的循环小批次逐步清理。
2. DELETE、TRUNCATE 与 DROP 的区别
| 操作命令 | 操作级别 | 是否记录行级日志 / 支持回滚 | 自增主键重置 | 执行性能 |
|---|---|---|---|---|
DELETE FROM table |
DML(行级) | 记录详细 undo/redo 日志,事务内可回滚 | 不重置原有计数器 | 逐行标记删除,慢 |
TRUNCATE TABLE |
DDL(表级) | 仅记录数据页释放日志,不可事务回滚 | 重置 AUTO_INCREMENT 为 1 | 直接重建数据表,极快 |
DROP TABLE |
DDL(对象级) | 删除整张表结构及索引数据,不可回滚 | 表结构已不存在 | 瞬间完成 |
3. 生产实践:逻辑删除设计
为了保障数据审计安全与可追溯性,现代业务系统中极少直接使用物理 DELETE,而是普遍采用“软删除”(逻辑删除)。通过在表中增加一个 is_deleted(TINYINT)或 deleted_at(DATETIME)字段:
-- 业务中“删除”用户实质上是一次 UPDATE
UPDATE users
SET is_deleted = 1
WHERE id = 100;
-- 业务常规查询时主动过滤已删除数据
SELECT id, username, email
FROM users
WHERE is_deleted = 0
AND id = 100;
五、全语法速查与实践总结
为了便于日常编码快速查阅,以下将四大语句的核心结构整理为标准语法模板:
-- 1. INSERT 新增
INSERT INTO [表名] ([列1], [列2], ...)
VALUES ([值1], [值2], ...), ([值3], [值4], ...);
-- 2. SELECT 查询
SELECT [列1], [列2], [聚合函数]
FROM [表名]
WHERE [行过滤条件]
GROUP BY [分组列]
HAVING [聚合过滤条件]
ORDER BY [排序列] [ASC|DESC]
LIMIT [偏移量], [返回数量];
-- 3. UPDATE 修改
UPDATE [表名]
SET [列1] = [新值1], [列2] = [表达式2]
WHERE [定位条件]
LIMIT [行数上限];
-- 4. DELETE 删除
DELETE FROM [表名]
WHERE [定位条件]
LIMIT [行数上限];
熟练运用 MySQL 的增删改查不仅在于记住这几个关键字,更在于树立严谨的操作习惯:查询不贪多(避免 *)、批量省资源(善用多值插入)、修改先核实(先 SELECT 再 UPDATE)、删除留退路(优选逻辑删除并辅以事务与限制)。遵循这些基本规则,即可在保证数据库高效运行的同时,彻底规避因误操作引发的系统故障与数据丢失。
本站声明
本文内容仅供参考,不代表本站观点。转载或引用请注明出处。如发现内容问题,请通过联系我们页面反馈。













