MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解

栗子
栗子
发布时间:2026-11-16| 5人浏览过
原创

全面讲解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;
MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解
图1:MySQL CRUD 四大核心语句功能职责与操作风险特征

一、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');
MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解
图2:INSERT 字段匹配执行逻辑与批量插入机制对比

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)会导致扫描过多无效记录,可通过子查询关联主键进行优化。

MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解
图3:SELECT 查询各个子句的书写次序与引擎底层执行流转顺序

关键认知:为什么 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 条件),整张表所有用户的状态将被全部改写!在生产环境中执行修改前,请务必遵守以下步骤:

  1. 同条件查询核对:在执行 UPDATE 前,先使用相同的 WHERE 条件执行 SELECT COUNT(*) 或 SELECT id, ...,核实受影响的数据行是否符合预期。
  2. 添加 LIMIT 约束:若明确知道本次修改只针对单行(例如修改指定用户的邮箱),在语句末尾追加 LIMIT 1 作为最后一道保险。
  3. 开启安全更新模式:在 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;
MySQL增删改查基础教程:INSERT、UPDATE、DELETE与SELECT语法详解
图4:MySQL 增删改查日常开发与生产操作安全准则速查

五、全语法速查与实践总结

为了便于日常编码快速查阅,以下将四大语句的核心结构整理为标准语法模板:

-- 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)、删除留退路(优选逻辑删除并辅以事务与限制)。遵循这些基本规则,即可在保证数据库高效运行的同时,彻底规避因误操作引发的系统故障与数据丢失。

免责声明:本文内容仅供参考,不代表本站观点。转载或引用请注明出处。

Python 相关产品和应用

全部下载
Selenium

Selenium

4.51.0 · Windows

用于浏览器自动化和 Web 测试的 Python 工具。

scikit-learn

scikit-learn

1.9.1 · Windows

常用 Python 机器学习算法与模型评估工具库。

Black

Black

26.10.1 · Windows

强调一致性的 Python 自动代码格式化工具。

Ruff

Ruff

0.17.1 · macOS

由 Rust 编写的高速 Python 代码检查和格式化工具。

pytest

pytest

9.1.1 · Windows

简洁而强大的 Python 自动化测试框架。

FastAPI

FastAPI

0.146.0 · Windows

高性能 Python API 框架,支持类型提示和自动接口文档。

Flask

Flask

3.1.3 · Windows

轻量灵活的 Python Web 应用框架。

Django

Django

6.1.2 · Windows

成熟的 Python Web 框架,适合构建内容站点和业务系统。

Spyder

Spyder

6.1.7 · Windows

面向科学计算与数据分析的 Python 集成开发环境。

SciPy

SciPy

1.18.1 · Windows

面向科学、工程和技术计算的 Python 算法库。