增删改查这四个字是每一个接触MySQL的人绕不过去的基础功。我不打算写那种从零到一的空泛教程这篇内容更像我在生产环境里摸爬滚打后的一次完整复盘。里面没有花哨的优化技巧只有日常开发里最常用、也最容易被忽略的SQL语法细节、执行逻辑和踩坑记录。如果你正在学数据库或者已经写了一段时间SQL却总在细节上犯迷糊把它当作手边的速查手册遇到问题随时回来翻。MySQL的增删改查核心套路其实就一句话先理解表再理解行最后理解SQL语句如何通过动词 对象 条件组合起来。INSERT、SELECT、UPDATE、DELETE对应着增、查、改、删四类操作我会逐个拆开讲配上可以直接跑的SQL代码以及真实项目中遇到过的边界情况。全篇不依赖任何图形界面工具全部用命令行方式演示因为命令行最不挑环境也最能帮助看清SQL到底在干什么。我用的演示环境是MySQL 8.0文中的语法在5.7及更高版本都适用个别版本差异我会另外标注。1. 学增删改查之前先弄懂这三件事1.1 表和行的本质拿Excel来理解MySQL很多教材一上来就讲数据库范式、关系模型、笛卡尔积讲得人头昏脑涨还记不住。我个人的经验是如果把MySQL中的表直接理解成一个Excel工作表一大半概念瞬间就落地了。一张表就是一张Excel表每一行是一条记录每一列是一个字段。你在Excel里做的筛选对应SQL里的WHERE你在Excel里做的排序对应SQL里的ORDER BY你修改某个单元格的内容对应SQL里的UPDATE你在Excel里删掉一行对应SQL里的DELETE。你把增删改查的任何一个操作先翻译成我在Excel里会怎么做再去看对应的SQL语法学习速度会快很多。这个类比虽然朴素但能帮你建立最直观的模型后续学JOIN、学索引也都用得着。但必须说清楚一个本质区别Excel是文件即数据而MySQL是服务器上的数据库管理系统。你发出的每一条SQL都会先经过MySQL的解析器、优化器再落到底层存储引擎真正读写数据。理解这一层你才会明白为什么有些SQL写得慢、有些SQL写得快——差的不是数据量本身而是你的条件写法能不能命中索引。索引是更进阶的话题本篇先把增删改查本身讲扎实索引不展开。1.2 主键所有修改和删除都绕不开的锚点这是我觉得初学者最容易忽略、但生产环境最要命的点。所谓主键PRIMARY KEY就是表中每一行的唯一身份标识比如用户的ID、订单的编号。一张表必须有主键这个设计不是为了让表好看而是为了让增删改查有据可依。举一个真实事故你要更新某个用户的信息如果UPDATE语句里没有用到主键只靠name 张三去定位结果表里有三个张三一次操作就会把三个人的数据全改掉。这就是经典的更新条件不精确导致误操作。同样的道理也适用于DELETE。所以我在写任何改、删语句时第一反应都是问自己一句这条语句有没有用主键定位用主键永远是最稳妥的。实际项目中主键通常是自增长的整数ID建表是这样写的CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT );AUTO_INCREMENT会让MySQL自动为每条新记录生成1、2、3……这样递增的ID你插入数据时不需要手动传ID。后面讲到INSERT时你会看到哪怕插入语句完全没写id字段数据也能正常进去这个id就是主键给的底气。1.3 常用数据类型建表选错类型后面全是泪增删改查的增和改本质上是往字段里填值。值填进去之前字段类型就已经决定了你能填什么。常见的有这几类类型用途举例INT整数年龄、数量、IDVARCHAR(n)可变长字符串用户名、邮箱DECIMAL(m, d)精确小数金额如DECIMAL(10,2)DATE / DATETIME日期和时间生日、创建时间TEXT长文本文章正文我强烈建议一个原则能用数字就别用字符串能用固定精度就别用浮点。钱这种数据如果用FLOAT或DOUBLE会出现0.1 0.2不等于0.3的精度问题那是纯给自己挖坑。DECIMAL是精确类型才是金额的正确选择。日期字段也一样别用字符串存时间否则排序、比较、按月统计全都别扭老老实实用DATE或DATETIME后面能省一堆事。2. 建库建表增删改查的地基2.1 连接MySQL以及最常见的连接报错要让增删改查先跑起来你得先能连上MySQL。命令行连接的标准写法是mysql -u root -p-p表示连接时要求输入密码回车后输入你的密码即可。连接成功后会看到mysql这个提示符说明你已经进入了MySQL命令行可以执行SQL了。很多初学者在这里会遇到一个最经典的报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)这个报错的意思是MySQL服务没启动或者客户端找不到socket文件。你先别急着怀疑密码按顺序做两件事先查看服务状态在Linux上执行systemctl status mysqld如果没启动就先启动再执行mysqladmin -u root -p ping检查连通性。这个错本质上不是SQL语法问题而是服务生命周期管理问题排查思路要摆对别在密码上浪费太多时间。2.2 CREATE DATABASE与CREATE TABLEMySQL安装后自带几个系统库比如mysql、sys、performance_schema那是MySQL自己管理的不要在里面建业务表。正确做法是先建自己的业务库CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4;IF NOT EXISTS的意思是如果这个库已经存在别报错继续执行。DEFAULT CHARSET utf8mb4是字符集配置这是面向所有新项目的推荐配置。utf8mb4能完整存储UTF-8字符包括emoji如果你用老旧的utf8会遇到表情符塞不进去或者中文乱码的问题。建完库后要先用USE进入这个库才能在里面建表USE shop;然后建一张最典型的用户表CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL COMMENT 用户名, age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 年龄, email VARCHAR(100) DEFAULT NULL COMMENT 邮箱, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;里面有四个细节值得展开讲。UNSIGNED表示无符号整数年龄和ID不可能为负加这个约束更合理COMMENT是字段注释写清楚字段含义将来看表结构能省大量沟通成本DEFAULT CURRENT_TIMESTAMP是让MySQL自动记录行的插入时间少写一条插入语句ENGINEInnoDB是存储引擎就用默认的InnoDB别改成MyISAM因为InnoDB支持事务和行级锁后面说的回滚、并发安全都依赖它。2.3 命名规范这是一份长期的代码债表名和字段名的命名很多人不当回事结果项目开发到一半各种大小写混用、拼音缩写满天飞维护成本直线上升。我个人的习惯是表名和字段名全部小写用下划线分隔例如user_orders而不是UserOrders或userOrders。表名单数和复数都能自圆其说重要的是团队统一。我个人倾向单数user比users更自然。字段名尽量见名知义created_at一看就知道是创建时间别用ct这种缩写三个月后你自己都忘。别用保留字当字段名order、group、desc在SQL里都有特殊含义虽然能加反引号硬用但纯属给自己挖坑改个名一劳永逸。这些规范看起来琐碎但其实和增删改查息息相关——你写的每条语句都要引用表名和字段名名字越规范语句越不容易错。我见过因为表名大小写导致程序在Linux上连不上、在Windows上正常的神奇案例根源就是MySQL在Linux下对表名大小写敏感。命名统一从一开始就规避掉这类环境差异问题。3. INSERT往表里塞数据的正确姿势3.1 单行插入与多行插入增删改查里的增在SQL里叫INSERT。最基本的单行插入INSERT INTO users (name, age, email) VALUES (小明, 18, xiaomingexample.com);注意我写INSERT时没有列出id和created_at。id有AUTO_INCREMENT会自动生成created_at有DEFAULT CURRENT_TIMESTAMP会自动填当前时间。这就是建表时认真设计字段约束带来的好处——插入语句可以更精简也少写容易出错的值。多行插一次的性能差异非常大INSERT INTO users (name, age, email) VALUES (小红, 19, xiaohongexample.com), (小王, 20, xiaowangexample.com), (小李, 21, xiaoliexample.com);一次INSERT插入多行能大幅减少客户端与MySQL服务器之间的网络往返次数。我曾经做一个数据导入需求原本是代码循环里逐条INSERT跑完全部数据花了十几分钟后来改成每500条拼一次批量插入整个流程直接降到几秒钟。量级差距就是这么明显所以数据初始化、批量导入场景能用多行插入就用多行插入。3.2 NULL、NOT NULL与默认值到底怎么填这是初学者最容易困惑的点为什么我插入数据时有些字段没写也能成功有些字段没写却报错答案就藏在建表时的字段约束里。往表里插入一条记录如果INSERT语句没有列出某个字段MySQL会按这个顺序处理如果建表时给了DEFAULT就用默认值。如果字段允许NULL就填NULL。如果字段是NOT NULL且没有默认值就报错。所以建表时把约束定义清楚插入时才会省心。NOT NULL表示这个字段业务上必须有值例如用户的nameDEFAULT表示不给值时有个合理兜底例如age默认为0。实际开发里我见过最别扭的表设计是所有字段都允许NULL结果查询时每个字段都要加IFNULLSQL写得又长又难维护那就是建表时省事的代价。3.3 插入时的坑唯一键冲突和字符集生产环境中插入报错最常见的是两类。第一类是唯一键冲突。你得先确认业务上哪些字段不能重复例如同一邮箱只能注册一个账号建表时就要加UNIQUE约束CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, ... );加了UNIQUE之后再插入相同邮箱会直接报Duplicate entry错误这个约束能挡住应用层的重复数据。当然如果你需要存在就更新、不存在就插入的语义MySQL有INSERT ... ON DUPLICATE KEY UPDATE语法一条语句搞定INSERT INTO users (id, name, age) VALUES (1, 小明, 19) ON DUPLICATE KEY UPDATE age VALUES(age);第二类是中文乱码或者报错Incorrect string value。报Incorrect string value八成是建库或建表时字符集没设成utf8mb4。事后补救的语句是ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;但这是补救措施根本办法还是建库建表时就选对字符集。数据库的字符集一旦在数据写入后再改老数据可能已经错乱转换会很痛苦。所以看到utf8mb4这几个字别再跳过了这是所有中文项目的默认配置。4. SELECT增删改查里的半壁江山4.1 全表查询与列过滤SELECT怎么用才高效SELECT是整个SQL语言里最复杂、也最常用的部分很多人做了几年开发写复杂SELECT仍然吃力。先把最简单的讲透SELECT * FROM users查询users表中所有行的所有列。SELECT name, age FROM users只查询name和age两列。我强烈建议在线上环境、数据量大的场景里尽量不要用SELECT *。它会把所有列都取出来哪怕你只需要一个字段。对MySQL来说这意味着更多的IO与网络传输对你来说结果集一宽定位关键信息反而更难。**需要什么列就写什么列是SQL开发的第一原则。**这句话我每次带新人都要重复一遍因为偷懒写星号的人实在太多了。4.2 WHERE条件精确、范围、模糊、IN与NULL判断WHERE是过滤数据行的关键直接决定查询结果集的大小。以下是几种最常见的写法-- 精确匹配 SELECT * FROM users WHERE name 小明; -- 范围查询age在18到25之间 SELECT * FROM users WHERE age BETWEEN 18 AND 25; -- 模糊匹配%代表任意个字符_代表一个字符 SELECT * FROM users WHERE name LIKE 小%; -- IN列表查询 SELECT * FROM users WHERE age IN (18, 20, 25); -- NULL判断不能用 NULL必须用 IS NULL SELECT * FROM users WHERE email IS NULL;这里有一个特别值得说的点NULL判断。很多新手会写WHERE email NULL结果一条都查不出来因为NULL不等于任何值和任何值比较的结果都是未知所以只能使用IS NULL或IS NOT NULL。这个知识点在数据库面试里属于高频考点很多人理论知道真写SQL时还是容易犯错。还有一个常见的组合用法是多个条件同时生效AND和OR可以任意组合但要注意用括号明确优先级SELECT * FROM users WHERE (age 18 AND name LIKE 小%) OR email IS NOT NULL;没有括号时AND的优先级高于OR这个顺序初学者特别容易搞反最终查出来的结果和自己想的对不上多半就是优先级问题。4.3 ORDER BY排序与LIMIT分页查询结果往往需要一个明确的顺序。排序的关键词是ORDER BY-- 按年龄降序 SELECT * FROM users ORDER BY age DESC; -- 先按age降序相同则按id升序 SELECT * FROM users ORDER BY age DESC, id ASC;DESC是降序ASC是升序不写默认升序。多字段排序时先按第一个字段排第一个字段相同的再按第二个字段排。注意如果想要按年龄降序但同年龄按id升序第二个字段必须写ASC因为默认升序虽然可以省略但写出来更清晰不会让人误解。分页是日常开发里离不开的操作。MySQL的分页用LIMIT标准写法是LIMIT 偏移量, 行数-- 第一页每页10条偏移0 SELECT * FROM users ORDER BY id LIMIT 0, 10; -- 第二页 SELECT * FROM users ORDER BY id LIMIT 10, 10;偏移量是跳过前面多少条所以第N页的偏移量就是(N-1)乘每页行数。很多新手把LIMIT 10, 20误以为查10条再取20条其实含义是跳过10条取20条。这个理解错位分页数据就会出现页面错乱。另外数据量大到一定级别后深分页比如LIMIT 1000000, 10会越来越慢那是另一个优化话题这里先提醒你有这么个现象等真正遇到再深挖也不迟。4.4 聚合函数、GROUP BY与DISTINCT去重当你不关心具体某一行的数据而是关心统计结果时就要用聚合函数。MySQL把数据先汇总再返回一个标量结果。常见的几个是COUNT计数、SUM求和、AVG平均值、MAX最大值、MIN最小值-- 总人数 SELECT COUNT(*) FROM users; -- 平均年龄 SELECT AVG(age) FROM users; -- 最大年龄 SELECT MAX(age) FROM users;GROUP BY则是把数据按某个字段归类再对每一组做统计SELECT age, COUNT(*) AS cnt FROM users GROUP BY age;这句话的意思是按年龄分组统计每个年龄各有多少人。结果里age列表示分组依据cnt列表示该组人数AS是给结果列起别名让列名更可读。分组之后还需要过滤就用HAVING它和WHERE的区别是WHERE在分组前过滤原始行HAVING在分组后过滤组。比如只要人数大于1的组SELECT age, COUNT(*) AS cnt FROM users GROUP BY age HAVING cnt 1;去重用DISTINCTSELECT DISTINCT age FROM users;它会把重复的年龄值合并成一个适合回答这个字段一共有多少个不同的取值这类问题。要注意DISTINCT作用于所有列出字段的组合如果是SELECT DISTINCT age, name那么只有当age和name两个字段组合完全重复时才会去重不是单独对某一列去重这个细节经常被误解。5. UPDATE动手改之前必须想清楚三件事5.1 UPDATE的标准语法条件决定影响范围UPDATE是改标准语法长这样UPDATE users SET age 19 WHERE id 1;这句话的意思是把id等于1的用户的年龄改成19。SET后面是把哪一列改成什么值WHERE后面是改哪些行。多个字段要同时修改时SET后面用逗号分隔UPDATE users SET age 19, email newexample.com WHERE id 1;在实际项目里我养成了一个习惯UPDATE之前先把同样的WHERE条件放到SELECT里查一遍。先看这条语句会命中哪些数据确认无误后再把这套条件原封不动搬到UPDATE里。这个过程虽然多一步操作但能有效防止一次全表误更新。尤其接手的项目里表结构不熟、数据含义不清楚时这个习惯能救你很多次。5.2 没有WHERE的UPDATE等于全表更新这是数据库新手事故排行榜的No.1UPDATE users SET age 19;没有WHERE意味着锁定users表的所有行把所有行的年龄都改成19。这个操作在某些场景下是有意为之比如全量初始化某个字段但绝大多数情况下是个灾难。一旦误执行影响的不是一条数据而是整张表。**修改、删除类操作没有WHERE就是全表被修改、被删除。**为了防手滑MySQL提供了安全模式在命令行执行SET sql_safe_updates 1;在MySQL 5.7及以上版本中开启safe_updates后UPDATE和DELETE语句如果没有携带基于主键或索引的WHERE条件会被拒绝执行。MySQL Workbench默认也会开启这个安全模式很多人在图形化工具里遇到怎么UPDATE报错的困惑根因就在这里。我的建议是开发环境每次都开safe_updates生产环境至少心里要有这根弦。5.3 联表更新需要从另一张表取值时的写法实际开发里偶尔会碰到要把一张表的值更新到另一张表的需求比如根据订单金额给用户划分等级。这是UPDATE的进阶用法UPDATE users u JOIN orders o ON u.id o.user_id SET u.level vip WHERE o.amount 5000;它的逻辑是先把users表和orders表按用户ID关联起来再把满足o.amount 5000条件的那部分用户的level改成vip。这种写法的好处是不用在应用层先查出ID集合、再发第二条UPDATE一条SQL完成两件事效率高逻辑也清晰。用到这个语法时说明你对表和表的关系已经有概念了对增删改查的理解也从单表走向了多表关联。6. DELETE永久消失的操作先说清楚后悔药6.1 DELETE语法与TRUNCATE的区别删除语句的标准形态DELETE FROM users WHERE id 1;意思是删除id为1的那一行。与UPDATE讲过的原则完全一致如果没有WHERE就会删除全表数据。生产环境中DELETE必须有WHERE条件且条件必须精确定位到目标数据。我见过有人写DELETE FROM users WHERE name test结果表里有100条测试数据一次全删了。删除之前用SELECT查一下命中范围这个操作和UPDATE一样重要。如果你确实要删除整张表的全部数据还有一个替代方案叫TRUNCATETRUNCATE TABLE users;DELETE FROM users和TRUNCATE TABLE users都能清空表但两者有本质区别用一张表就能看明白对比项DELETETRUNCATE是否可回滚在事务内可回滚不可回滚是否重置自增ID不清空AUTO_INCREMENT计数会把自增ID重置为1执行速度慢逐行删除快直接重建表空间所以清空一张表和删除某些行是两个不同场景不要混用。想清空并重置自增ID用TRUNCATE想删掉部分数据且保留后续自增计数用DELETE加WHERE。6.2 误删了怎么办事务是你的后悔药这部分重点讲抢救思路。MySQL的InnoDB存储引擎原生支持事务事务的关键词是BEGIN、COMMIT和ROLLBACK。BEGIN; DELETE FROM users WHERE id 100; -- 检查一下误删没有没有就撤销 ROLLBACK; -- 确认没问题再提交 COMMIT;在这一段事务里DELETE执行后如果发现删错了执行ROLLBACK就能把数据恢复回来确认没问题后再COMMIT删除才真正生效。这个能力是InnoDB最值钱的功能之一。我在生产环境里执行重要DELETE前一定会先开启事务然后DELETE接着用SELECT校验受影响的数据范围确认无误再COMMIT。这套操作听起来麻烦但能挡掉绝大多数误删事故。注意一个细节TRUNCATE不支持回滚因为它在多数MySQL版本里会隐式提交事务。所以越是彻底清空的操作越要谨慎执行前先确认是不是真的要清空。6.3 误删恢复的实践建议备份永远比技巧可靠再有效的回滚也救不了你没开事务直接删、还过了很久才发现的情况。真正的底线是备份。如果你的数据库连备份机制都没有那完善的优先级应该排在所有增删改查技巧之前。全量备份用mysqldump定时导出恢复时把备份导回去再补业务日志的数据。二进制日志binlog配合mysqlbinlog工具可以恢复到误删前的某个时间点这是精细恢复的核心手段。个人项目至少要做全量备份一条命令就能实现。比如每天凌晨导出一份当天数据mysqldump -u root -p shop /backup/shop_$(date %F).sql恢复时直接导入mysql -u root -p shop /backup/shop_2025-01-01.sql有备份和没备份面对误删时的心态完全不同。备份不是可选项而是底线工程。学增删改查时可以不做手上管着真实业务数据时必须做。7. 一个完整示例与我最想提醒的几点实务心得7.1 从头跑一遍完整的增删改查把上面讲的内容串起来走一遍完整的流程。假设我们要为一个小商城维护用户数据从建库到删数据总共五步-- 1. 建库建表 CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4; USE shop; CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, age TINYINT UNSIGNED NOT NULL DEFAULT 0, email VARCHAR(100) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 2. 增插入数据 INSERT INTO users (name, age, email) VALUES (小明, 18, xiaomingexample.com), (小红, 19, xiaohongexample.com), (小王, 20, xiaowangexample.com); -- 3. 查查询所有年龄大于18且姓名以小开头的用户 SELECT name, age, email FROM users WHERE age 18 AND name LIKE 小% ORDER BY age DESC; -- 4. 改把小明年龄改到19 UPDATE users SET age 19 WHERE name 小明; -- 5. 删删除邮箱为空的用户 DELETE FROM users WHERE email IS NULL;这一个流程走完增删改查就全覆盖了。强烈建议你打开命令行亲手敲一遍再改一改条件看看结果怎么变化。SQL这个东西看十遍不如亲手跑一遍语法错误、条件漏写、结果不对这些问题只有自己敲的时候才会暴露出来。7.2 我踩过的坑和面试里躲不开的几个点最后分享几个我在实际工作中反复踩过的坑。第一搞清楚客户端显示问题不等于库里的数据问题。中文乱码有两种来源一个是库里存的就是错的一个是客户端显示字符集不对。排查时先看表结构确认字符集是utf8mb4再检查连接字符集别一上来就改数据。改数据是最后的手段因为动了就回不去了。第二WHERE条件里用了函数索引大概率失效。比如WHERE YEAR(created_at) 2024这种写法会让MySQL无法利用created_at字段上的索引数据量大时性能会很难看。更好的写法是改成范围条件SELECT * FROM users WHERE created_at 2024-01-01 AND created_at 2025-01-01;这两种写法查出来的结果一样但执行效率完全不同。养成能写范围就不写函数的习惯对性能有立竿见影的帮助。第三增删改查相关的高频面试题往往集中在几个点上SELECT的完整执行顺序是FROM → WHERE → GROUP BY → HAVING → SELECT字段 → ORDER BY → LIMITWHERE和HAVING的区别在于过滤时机DELETE和TRUNCATE的区别在于可回滚性与自增重置NULL判断为什么不能用等号。这些问题都在这篇文章对应的章节里有出处理解了再去记比死背标准答案扎实得多。最后再分享一个我自己的小习惯在命令行里准备一套自己常用的增删改查模板每次写新SQL时直接改表名和字段名这个习惯让我的日常开发效率高了不少。增删改查不难真正难的是每次都写对、写稳、不出事故而这些都要靠平时一点一滴的规范和习惯积累。