MySQL入门实战：从建表到增删查改的完整CRUD操作指南

MySQL入门绕不开的坎就是增删查改这四个动作也就是常说的CRUD。很多教程把增删查拆得七零八落讲插入的只讲插入讲查询的只讲查询读者看完感觉自己什么都见过可真到工作里要动手建表、写查询、改数据的时候还是不知道从哪里下手。这篇内容就是为了解决这个问题我拿一张学生信息表做贯穿案例把MySQL基础操作中的增删查改从语法到实战一路走通每一步都告诉你为什么这么写、不这么做会踩什么坑。适合刚接触数据库的同学也适合想把自己零碎的MySQL知识系统过一遍的人。1. 动手前的准备工作1.1 环境与连接工具先别急着敲SQL一上来就操作很容易被各种小问题劝退。MySQL本身要先装好装完以后你得有个趁手的连接工具。命令行是默认选项也是最稳妥的。Windows下打开cmdLinux/Mac下打开终端输入类似这样的命令就能连上本地MySQLmysql -uroot -p输入密码后看到mysql提示符就说明进来了。命令行适合练基本功所有SQL都能跑但缺点也很明显查询结果多了以后排版不够直观。图形化工具我建议新手至少准备一个。最常用的是Navicat和DBeaver另外MySQL官方也提供免费的MySQL Workbench。这三者选哪个都行只要能把数据库连上、能看表结构、能执行SQL就可以。DBeaver是免费的跨平台工具社区版就够用Navicat功能强大但很多功能需要付费授权而且网上流传的所谓“破解版”既不合规也可能带有安全风险我自己从来不用也建议你使用正版或免费工具。连接工具本质上只是个壳它把你要执行的SQL发给MySQL服务器再把结果渲染出来。所以不管用哪个工具核心还是SQL本身。1.2 建库建表数据类型先选对操作数据的顺序一般是先建数据库再建表最后才能往里塞数据。创建一个数据库很简单CREATE DATABASE school DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里一定要指定utf8mb4字符集。MySQL老版本默认的utf8其实是utf8mb3它存不了emoji和部分生僻字报错的时候往往只给你一个冷冰冰的“Incorrect string value”排查半天才发现是字符集问题。utf8mb4是utf8的超集能兼容更多字符现在新建库表直接用utf8mb4就行了。建表是增删查改之前最关键的一步表结构定得不好后面CRUD全都别扭。拿学生表来说基础字段设计如下CREATE TABLE student ( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, name VARCHAR(50) NOT NULL COMMENT 姓名, gender ENUM(male, female) DEFAULT NULL COMMENT 性别, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年龄, class_name VARCHAR(50) DEFAULT NULL COMMENT 班级, score DECIMAL(5,2) DEFAULT 0.00 COMMENT 总分, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生信息表;几个字段类型的选择思路要讲清楚。id用INT UNSIGNED主键一般不用负数无符号整数可以让正数范围翻倍age用TINYINT UNSIGNED因为人的年龄撑死一百多TINYINT范围是0~255完全够用还省空间score用DECIMAL(5,2)因为浮点数的精度在MySQL里不靠谱存金额、分数这类需要精确计算的数值一定要用定点数DECIMALcreated_at用DATETIME配合默认值CURRENT_TIMESTAMP插入数据的时候会自动记录当前时间。ENGINEInnoDB也必须写上。InnoDB是MySQL默认引擎支持事务、行级锁、外键生产环境基本都用它。MyISAM虽然查询快一点但不支持事务崩溃后恢复能力也差除非你有特殊理由否则一律InnoDB。2. INSERT把数据装进口袋2.1 单条插入与批量插入INSERT做的事就是给表里加数据。最简单的单条插入写法INSERT INTO student (name, gender, age, class_name, score) VALUES (张三, male, 18, 高一(3)班, 580.5);表名后面括号里写字段名VALUES后面写对应值。这里有个习惯问题我见不少人写INSERT时省略字段列表直接写INSERT INTO student VALUES (...),这种写法要求值的顺序必须和表结构完全一致一旦表加了字段或者调整了顺序这条SQL就废了。生产中表结构隔三差五会变所以强烈建议每次都用显式字段列表。一次插入多条数据时有更高效的做法把多个值组用逗号连起来INSERT INTO student (name, gender, age, class_name, score) VALUES (李四, female, 17, 高一(2)班, 592.0), (王五, male, 18, 高一(3)班, 561.0), (赵六, female, 17, 高一(1)班, 598.5);批量插入不仅是写法更紧凑更是性能上的选择。每条INSERT单独执行MySQL每次都要重新解析SQL、做权限检查、记录日志批量插入可以把这些开销摊薄到每条记录上。几千条数据场景下这个差别就已经很明显了。我实测往一张10万行级别的表里灌数据单条逐行插入可能要几分钟改成一次插入500条速度能提升一个数量级。2.2 插入时的常见坑第一字段和值的数量必须严格对应。少写一个值会报Column count doesnt match value count。这属于低级错误但越忙的时候越容易犯。第二要注意默认值的行为。上面表里的gender、class_name是允许为NULL的created_at有默认值。所以可以只插入部分字段INSERT INTO student (name, age) VALUES (周七, 18);这样gender、class_name自动是NULLscore会用默认值0.00created_at自动填充当前时间。反过来如果你连id都想自己指定也不是不行id字段因为是AUTO_INCREMENT你手动给个值也可以但除非有特殊场景我建议永远不要手动指定自增主键让MySQL自己分配就好。第三字符串里的单引号需要转义。比如学生叫“OBrien”直接写会语法错误要写成INSERT INTO student (name) VALUES (O\Brien);另外如果需要插入的数据在唯一键上冲突时想要“有则更新、无则插入”可以用ON DUPLICATE KEY UPDATEINSERT INTO student (id, name, age) VALUES (1, 张三, 19) ON DUPLICATE KEY UPDATE name VALUES(name), age VALUES(age);这个方法在批量同步数据时非常好用但注意它依赖唯一索引或主键没有索引限制的时候MySQL不知道“重复”指的是什么。3. SELECT把数据翻出来看3.1 基础查询和条件过滤查询是CRUD里最常写、也最值得花时间学的一部分。最简单的全表查询SELECT * FROM student;*表示所有字段开发调试的时候很方便但生产环境尽量少用SELECT *尤其是表字段多、数据量大的时候。原因有两个一是多查了很多用不到的字段白白增加网络传输和内存消耗二是如果哪天表结构加了字段代码里拿结果集的顺序可能就乱了。所以查什么字段就写什么字段SELECT id, name, age FROM student;条件过滤是查询的灵魂。WHERE子句用来指定过滤条件比如查所有18岁的学生SELECT name, age FROM student WHERE age 18;条件里可以用各种运算符、、、、、!以及逻辑运算符AND、OR、NOT。比如查高一(3)班且分数大于560的SELECT name, class_name, score FROM student WHERE class_name 高一(3)班 AND score 560;模糊查询用LIKE。比如查所有姓“张”的学生SELECT name FROM student WHERE name LIKE 张%;%是通配符代表任意长度的任意字符_代表单个字符。这里有个性能问题LIKE %张这样以通配符开头的写法会导致索引失效全表扫描。数据量小无所谓数据量大的时候一定要避免。范围查询可以这样写-- 用 AND 写法 SELECT name, age FROM student WHERE age 16 AND age 18; -- 用 BETWEEN 写法效果一样 SELECT name, age FROM student WHERE age BETWEEN 16 AND 18;3.2 排序、去重与分页查询结果默认是按物理存储顺序返回的大多数时候不是你想要的顺序。用ORDER BY控制排序-- 按分数从高到低 SELECT name, score FROM student ORDER BY score DESC; -- 按年龄从小到大年龄相同时按分数从高到低 SELECT name, age, score FROM student ORDER BY age ASC, score DESC;DESC表示降序ASC表示升序默认是ASC。多字段排序时按写的顺序逐级比较这也是考试里常考的知识点。去重用DISTINCT。比如想看看学生都分布在哪几个班级SELECT DISTINCT class_name FROM student;注意SELECT DISTINCT name, class_name去重的是(name, class_name)的组合不是只对第一个字段去重这个容易理解错。分页是生产环境非常高频的需求用LIMIT-- 跳过前0条取10条即第1页 SELECT name, score FROM student ORDER BY score DESC LIMIT 0, 10; -- 跳过前10条取10条即第2页 SELECT name, score FROM student ORDER BY score DESC LIMIT 10, 10;LIMIT offset, count的写法里offset是从第几条开始跳count是取多少条。记住分页公式第N页的数据offset (N-1) * pageSize。这里注意深分页是个性能杀手LIMIT 1000000, 10需要MySQL先数出前面100万条再丢弃非常慢。解决思路通常是先用索引定位到起始位置再往后取或者记录上一页最后一条数据的id作为下一页起点。3.3 聚合统计与GROUP BY光把数据列出来还不够业务上经常要“统计”。聚合函数就是干这个的常见的有COUNT、SUM、AVG、MAX、MIN。比如-- 学生总数 SELECT COUNT(*) FROM student; -- 分数平均值 SELECT AVG(score) FROM student; -- 最高分和最低分 SELECT MAX(score), MIN(score) FROM student;把统计按组拆开就得用GROUP BY。比如统计每个班有多少人、平均分多少SELECT class_name, COUNT(*) AS cnt, AVG(score) AS avg_score FROM student GROUP BY class_name;这里AS是给查询结果字段起别名显示的时候更好看懂。GROUP BY之后如果想过滤组不能再用WHERE得用HAVING。比如只要平均分大于580的班级SELECT class_name, AVG(score) AS avg_score FROM student GROUP BY class_name HAVING avg_score 580;WHERE和HAVING的区别很多新手搞混WHERE是在分组之前对每一行做过滤HAVING是在分组之后对每一组做过滤。这个顺序不能反否则语义就错了。3.4 多表查询的入门思路基础操作阶段可以只操作单表但实际开发中几乎没有只查一张表的业务。拿学生表来说如果每个学生还有一张成绩明细表查询“学生姓名他的各科成绩”就需要把两张表连起来。JOIN就是干这个的SELECT s.name, g.subject, g.score FROM student s INNER JOIN grade g ON s.id g.student_id;JOIN的核心逻辑是把左表的每一行拿出来跟右表的每一行做匹配匹配条件是ON后面写的等式。INNER JOIN只保留两边都匹配得上的行LEFT JOIN保留左表所有行匹配不上则右表字段补NULL。多表查询的细节能写好几篇文章这里先明白“JOIN是把两张表按关联条件拼成一张大表”这个思路就够了。4. UPDATE修改已有数据4.1 基本更新语法UPDATE用来修改表中的数据。基本语法UPDATE student SET age 19 WHERE name 张三;从逻辑上讲UPDATE做的事是找到符合WHERE条件的行把这些行的age字段改成19。这里最重要的就是WHERE条件。如果不写WHEREMySQL会把整张表的所有行都更新-- 危险把所有人的年龄都改成19 UPDATE student SET age 19;很多初学者都在这里栽过跟头。MySQL的默认安全模式下不带WHERE或WHERE里没有使用索引的UPDATE会直接报错You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column。这其实是保护你的机制它能拦下“忘记写WHERE”的低级失误。所以我建议新手把安全模式开着别急着关掉它。4.2 更新多个字段和批量更新技巧更新多个字段用逗号分隔UPDATE student SET age 19, score 590.5 WHERE id 1;批量更新不同记录的不同值有一种常见做法是用CASE WHEN。比如要把“高一(1)班”的分数统一加5分“高一(2)班”的分数统一加10分UPDATE student SET score CASE class_name WHEN 高一(1)班 THEN score 5 WHEN 高一(2)班 THEN score 10 ELSE score END;这种写法比写多条UPDATE语句更高效但业务逻辑复杂的时候可读性会下降需要配合注释使用。更新的时候还有一个关键点如果你更新多行但中途某一行失败前面的修改会怎样这取决于事务。默认情况下如果SQL语句本身出错这一条语句执行的修改会全部回滚不会只成功一半如果你把它们放在一个显式事务里就可以控制多条语句一起成功或一起失败。实际上在命令行里逐条执行不带提交的UPDATEMySQL默认是自动提交的也就是说每执行一条立刻生效没有后悔药。所以更新数据之前建议你先跑一遍相同的SELECT确认要改的就是这些行-- 先确认 SELECT id, name, age FROM student WHERE class_name 高一(1)班; -- 再更新 UPDATE student SET age age 1 WHERE class_name 高一(1)班;这个习惯看上去平平无奇但能救你无数次。5. DELETE把数据抹掉5.1 DELETE语法与安全性DELETE用来删除表中的数据DELETE FROM student WHERE id 1;跟UPDATE一样DELETE最关键的也是WHERE。不写WHERE就是清空整张表-- 危险删除所有学生 DELETE FROM student;关于DELETE我见过的最典型事故是想删一个学生条件写错了把整个班级的都删了或者干脆忘了写WHERE全表没了。预防办法就是前面说的“先SELECT后DELETE”先查出来看看要删的是不是这些。删除操作还有外键约束的问题。如果其他表里面有关联到student表的数据DELETE的时候可能会报Cannot delete or update a parent row: a foreign key constraint fails。这是InnoDB在保护数据完整性不能把别的表还在引用的父记录删掉。解决办法是先把子表里的关联数据处理掉或者在外键上配置ON DELETE CASCADE但这属于进阶内容新手阶段先把概念搞清楚。5.2 DELETE、TRUNCATE和DROP怎么选很多新人会把“删除数据”和“删除表”混在一起。实际上有三个操作长得像但作用完全不同操作作用能否带WHERE事务支持自增ID速度DELETE删除表数据能支持可回滚不会重置继续递增慢逐行删除TRUNCATE清空表不能大多数情况下隐式提交不可回滚重置为初始值快直接重建表DROP删除整张表不能不可回滚无备份的前提下—最快一句话总结想删部分数据用DELETE想清空表且希望自增ID重新从1开始用TRUNCATE想彻底删掉表结构和数据用DROP。后续还能不能用这张表取决于你是哪一种。TRUNCATE的“不可回滚”需要特别强调一下。虽然MySQL 8.0的文档显示TRUNCATE在某些情况下可以回滚但生产环境千万别赌这个行为。我身边真实案例有同事用TRUNCATE清了一张日志表后来发现数据还要用因为没有备份悔得肠子都青了。所以TRUNCATE和DROP这种高风险操作执行前最好先备份。6. 实战中常见的坑与排查6.1 高频报错速查表我在带新人和日常工作中收集了这些MySQL新手高频报错整理成表格遇到问题可以对照着排查报错信息片段含义解决思路Unknown column xxx in field list字段名不存在检查拼写SHOW COLUMNS FROM 表名Table xxx doesnt exist表不存在检查是否选对数据库USE xxxDuplicate entry 1 for key PRIMARY主键重复主键自动生成不要手动插入相同的值Incorrect string value字符集问题表和字段都用utf8mb4Data too long for column值超长检查字段长度定义是否合理Column count doesnt match value count插入的字段数和值数不一致检查INSERT语句字段列表Cannot delete or update a parent row外键约束阻止先处理子表数据You are using safe update mode缺少WHERE的安全保护判断是否真的需要全表更新或补充WHERE6.2 一定要养成的几个实操习惯第一生产环境操作前先备份。真正常犯的错不是SQL写错而是没给自己留退路。哪怕只是更新一张小表有备份就敢放手做没备份就只能靠祈祈祷。第二养成看执行计划的习惯。可以简单一点遇到查询变慢了在SQL前面加个EXPLAIN。它会告诉你MySQL是怎么执行这条语句的有没有用到索引扫描了多少行。这是从“会写SQL”到“会写好的SQL”的分水岭。EXPLAIN SELECT name, score FROM student WHERE age 18;不用怕看不懂输出先看type和rows两列就够起步了。type是ALL说明全表扫描值得警惕rows是预估扫描行数越小越好。第三事务处理要心里有数。InnoDB支持事务多步数据变更建议手动控制START TRANSACTION; UPDATE account SET balance balance - 100 WHERE id 1; UPDATE account SET balance balance 100 WHERE id 2; COMMIT;先跑一遍确认没问题再COMMIT有问题就ROLLBACK。这比一条条自动提交要安全得多。第四字段注释和SQL注释别偷懒。建表的COMMENT、SQL脚本里的注释看起来是小事但接手的人或者几个月后的你自己会非常感谢这些注释。我在实际使用中发现很多数据库问题其实不是SQL语法不会写而是对“这条语句到底会对哪些数据生效”缺乏感知。增删查改四个操作最危险的不是写不出来而是写出来之后一执行影响的范围和自己想的不一样。所以每次写UPDATE和DELETE我都会下意识地把前面的SELECT再执行一遍确认范围之后再动手。这套习惯帮我避过了很多次潜在事故。MySQL基础操作往后还能延伸很多方向比如索引优化、事务隔离级别、存储过程、主从复制但所有进阶内容都建立在今天这套CRUD的基础上。把增删查改练熟、把常见坑记住后面学什么都会顺畅很多。建议你把这篇文章里的SQL语句照着敲一遍敲完你就发现数据库这事儿没有想象中那么神秘。