做MySQL这一行不管是刚装好环境的新手还是已经接手过生产库的运维每天打交道最多的就是查询操作。你可能会背一大堆安装命令、调优参数但真正落到业务上最先要写利索的永远是那句SELECT。今天这篇不聊安装不讲架构就专门把MySQL最基础的查询操作掰开揉碎讲一遍。我会用实际的学生成绩场景做贯穿把常见的坑、容易搞混的语法、还有实际排查问题时的思路都过一过保证你看完能直接把写法搬到自己库里跑。我自己在带新人的时候说过很多次查询写得好不好不是看你会不会用LEFT JOIN而是看你面对一个需求时能不能用最简单的语句把结果取准确。复杂功能都是简单操作堆出来的今天先把地基打牢。1. 先弄明白查询到底在查什么很多初学者上来就背SELECT 列名 FROM 表名 WHERE 条件觉得这就是查询的全部。实际上一条查询语句背后MySQL要经历的东西远比表面看到的复杂。我先带你把查询逻辑拆开看这样后面写语句的时候脑子里就有一张完整的执行流程图不会迷迷糊糊试半天。1.1 一条查询语句的解剖结构一条完整的SQL查询核心部分就是三大块要哪几列、从哪张表来、按什么条件筛。对应到SQL语法上就是SELECT、FROM、WHERE这三个关键字。这是最基础的骨架。SELECT 列名1, 列名2 FROM 表名 WHERE 筛选条件;举个例子有一张学生表student你想查所有学生的姓名和年龄就这么写SELECT name, age FROM student;这里SELECT后面的name、age就是要取的列FROM后面指定了表。没有WHERE就代表全表所有行都返回。加了WHERE才开始做行的过滤。SELECT name, age FROM student WHERE age 18;不过你要是以为MySQL真的按SELECT、FROM、WHERE这个顺序执行那就错了。我面试时候经常拿这个问题试探候选人SQL的书写顺序和执行顺序不一样。MySQL真实的执行顺序大致是先FROM确定从哪张表取数据然后WHERE把不符合条件的行过滤掉接着GROUP BY、HAVING做分组和分组后的过滤再然后SELECT确定要输出的列最后ORDER BY排序、LIMIT做分页截断。这个顺序理解透以后很多奇怪的问题都能解释清楚。比如你问为什么WHERE里不能用SELECT后面才定义的别名就是因为执行到WHERE时SELECT的别名根本还不存在。1.2 用生活化类比拆解查询的思维把查询操作类比成去超市买菜你拿着购物清单进超市FROM告诉你超市是哪家在货架之间走动的时候按照你的要求把不想要的商品直接跳过WHERE过滤最后把想要的商品装进购物车结账SELECT输出结果。如果你还想最后按价格高低排个序那就是结账出门前再看一眼购物车按价格摆整齐ORDER BY。这个类比看着简单但我想强调一个关键点MySQL处理数据的单位是行不是列。哪怕你SELECT只取了一列它在内存里也是先把完整的行读出来再一层层过滤最后才把需要的列输出。所以写查询的时候要知道WHERE条件用得越准确MySQL提前过滤掉的行就越多效率自然就高了。这个阶段不用着急啃执行计划先把“先过滤后取列”这个思维刻在脑子里。后面写复杂查询时你只要记住MySQL最后才帮你把列整理出来就不会写出满屏全表扫描的语句了。2. 简单查询的常用姿势直接抄基础语法谁都会背但实际写的时候细节决定成败。这一节我把日常工作中用得最频繁的几种查询写法连同容易出岔子的角落一起整理出来。每条我都给了正反例你可以直接对照着抄。2.1 单表查询的写法与WHERE条件细节先看最简单的整表查询。开发环境里我常用来快速看数据SELECT * FROM student;注意生产环境我极不推荐用这个。SELECT *会把所有列都查出来一旦表里有大字段比如TEXT、BLOB或者列数特别多白白浪费IO和内存。更关键的是如果哪天表结构加了列你的程序拿到的结果集列数也会变容易出隐患。正确做法是明确列出你需要的列。WHERE条件里等值判断、范围判断、模糊匹配是三类最常见的需求。等值判断SELECT name, class_id FROM student WHERE student_id 20240001;范围判断用BETWEEN ANDSELECT name, score FROM score WHERE score BETWEEN 80 AND 90;这个写法等价于score 80 AND score 90注意它是包含边界的。我见过很多同事把边界记成开区间一查就漏数据。模糊匹配用LIKESELECT name FROM student WHERE name LIKE 张%;%代表任意多个字符_代表单个字符。所以张%查的是姓张的张_查的是名字只有两个字的张姓学生。这里有个性能大坑如果查询条件是LIKE %张或者LIKE %张%即使你在name列上建了索引也没法走索引MySQL只能全表扫。原因很简单索引是按列值从前往后建立的前缀不确定索引就无从定位。能避免就避免非用不可的时候再考虑全文索引或者其他方案。WHERE里还可以用IN表示匹配一个列表SELECT * FROM score WHERE course_id IN (1, 2, 3);与之相对的是NOT IN但有个坑我后面会专门讲当列表里包含NULL时NOT IN的结果会跟你想象的不一样。2.2 排序和分页最容易写错的两个地方排序用ORDER BY。默认是升序ASC降序要显式写DESC。多字段排序时从左到右依次起作用。SELECT name, score FROM score ORDER BY score DESC, name ASC;这个语句的意思是先按score降序排如果两条记录score一样再按name升序排。这就解决了一个新手常犯的错以为写两个ORDER BY字段就能分别控制方向实际只能靠逗号分隔每一列后单独写方向。分页用LIMIT这是面试和实际开发都高频出现的关键字。MySQL里分页的标准姿势SELECT * FROM score ORDER BY score DESC LIMIT 10 OFFSET 20;意思是跳过前20行从第21行开始取10行。也可以写成LIMIT 20, 10逗号前面的数字是偏移量后面是行数。这个顺序特别容易搞混我建议统一用OFFSET写法语义更清晰。分页有个性能隐患偏移量越大越慢。当你需要翻到第100000行时MySQL依然要把前面99999行全部扫过才能取到目标数据。实际业务里我见过不少翻页翻到后面就卡死的场景这时候要么限制最大翻页深度要么改用“上一页最后一条记录的ID WHERE”这种键集分页。不过这些都是后话先把简单分页写对。2.3 去重和条件组合别把DISTINCT用错地方去除重复行用DISTINCTSELECT DISTINCT class_id FROM student;这个语句会返回所有不重复的class_id。注意DISTINCT作用在SELECT后面所有列的组合上不是只作用于紧跟的那一列。如果你写SELECT DISTINCT class_id, name FROM student;返回的是class_id和name两列组合起来不重复的行不是“只对class_id去重”。这个我在实际项目里踩过不只一次同事拿它做班级列表去重发现结果里同一个班出现好多次就是因为组合去重的机制。条件组合用AND、OR、NOT。AND优先级高于OR所以想表达“A或B且C”时一定要加括号SELECT * FROM score WHERE (course_id 1 OR course_id 2) AND score 60;这个语句查的是课程1或课程2中成绩及格的记录。如果漏了括号写成了WHERE course_id 1 OR course_id 2 AND score 60实际含义就变成课程1的所有记录加上课程2中及格的记录。结果完全不一样。这不是小坑生产环境我真见过因为漏括号查出错误数据的。再提一个热词里的疑问“mysql的or能去重吗”。这个问题的答案是不能去重和OR压根不挨着。OR只是扩大筛选范围只要满足任何一个条件行就会被选中。如果同一行同时满足两个条件结果集里也只会出现一次因为查询返回的是行集合不是条件命中的次数。这个去重效果是行集本身的特性不是OR的功劳。3. 拿来就用的实战场景学生成绩查询光列语法没意思我用一个贯穿全文的实战例子把上面的知识点串起来。这个例子正好贴合实际项目里“学生课程成绩信息实体表设计mysql”的常见场景也方便你直接建表跑一跑验证。3.1 学生-课程-成绩三张表的准备先建三张最基础的表学生表、课程表、成绩表。学生表CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, class_id INT DEFAULT 1, age TINYINT );课程表CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL );成绩表CREATE TABLE score ( student_id INT, course_id INT, score DECIMAL(5,1), PRIMARY KEY (student_id, course_id) );成绩表用联合主键保证同一个学生同一门课只能有一条成绩。DECIMAL(5,1)的意思是总共5位数字小数点后保留1位最大能存9999.9足够用了。我见过有人用FLOAT存分数后来出现0.30000000000000004这种诡异结果就是因为浮点数的二进制精度问题。金额、分数这种需要精确的值直接用DECIMAL。插入几条测试数据方便后面查询INSERT INTO student VALUES (1, 张三, 1, 18), (2, 李四, 1, 19), (3, 王五, 2, 18), (4, 赵六, 2, 20); INSERT INTO course VALUES (1, 数学), (2, 英语), (3, 物理); INSERT INTO score VALUES (1, 1, 88.5), (1, 2, 75.0), (2, 1, 92.0), (2, 3, 68.5), (3, 2, 81.0), (3, 3, 95.0), (4, 1, 43.5);3.2 按分数段统计的完整写法需求一查出数学成绩大于等于80分的学生名字和分数。SELECT s.name, sc.score FROM score sc JOIN student s ON sc.student_id s.student_id WHERE sc.course_id 1 AND sc.score 80;这里我用了JOIN关联两张表逻辑很简单成绩表里先筛出课程1且分数达标的行再关联到学生表拿名字。JOIN的用法后面会展开你先感受一下查询从单表走向多表的过程。需求二统计每个班级的平均分、最高分、最低分。SELECT st.class_id, AVG(sc.score) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM score sc JOIN student st ON sc.student_id st.student_id GROUP BY st.class_id;GROUP BY把数据按class_id分组然后对每组分别算AVG、MAX、MIN。这是查询操作里从“取数据”跨向“汇总统计”的关键一步。注意SELECT后面除了聚合函数只能出现GROUP BY里写过的列这是SQL规范MySQL有些版本默认放开了一部分但写规范了总没错。需求三筛选出平均分大于80的班级这时候要用HAVING而不是WHERE。SELECT st.class_id, AVG(sc.score) AS avg_score FROM score sc JOIN student st ON sc.student_id st.student_id GROUP BY st.class_id HAVING AVG(sc.score) 80;WHERE和HAVING的区别是我在面试里必问的基础题。简单记WHERE在分组之前过滤行HAVING在分组之后过滤组。WHERE里不能用聚合函数HAVING专门服务于聚合结果。3.3 关联查询与子查询从单表走向多表日常业务很少真的只查一张表至少也要关联出个名称。JOIN大致分三种INNER JOIN、LEFT JOIN、RIGHT JOIN。我推荐你记住INNER和LEFT就够用了RIGHT JOIN完全可以改写为LEFT JOIN的镜像能少记一个就少记一个。INNER JOIN只返回两边都匹配上的行SELECT s.name, c.course_name, sc.score FROM score sc JOIN student s ON sc.student_id s.student_id JOIN course c ON sc.course_id c.course_id;LEFT JOIN返回左表全部行右表没匹配上就补NULLSELECT s.name, sc.score FROM student s LEFT JOIN score sc ON s.student_id sc.student_id;这个语句能查出所有学生包括没参加考试的学生没考的那几门成绩就是NULL。搞懂INNER和LEFT的区别就够覆盖九成以上的关联查询需求了。子查询也是简单查询的延伸。比如查成绩表里有记录的学生SELECT name FROM student WHERE student_id IN ( SELECT DISTINCT student_id FROM score );这种写法逻辑直观但要注意子查询的结果集越大性能越差。能用JOIN改写就尽量用JOIN上面这个功能写成SELECT DISTINCT s.name FROM student s JOIN score sc ON s.student_id sc.student_id;效果一样MySQL优化器执行起来通常更顺畅。初学者阶段不用过度担心两种写法性能差多少但养成习惯能用JOIN直连的就别套子查询后面量起来了你就会感谢这个习惯。4. 常见问题与排查技巧实录查询写出来只是第一步写出来的结果对不对、慢不慢才是真考验。这一节我整理几个实际运维和开发中高频出现的坑每个都是我或者我身边同事真金白银踩出来的你提前知道能省不少事。4.1 查询结果怎么跟业务对不上最典型的场景明明表里有数据查询却返回空。我先问一句你确认那张表里的数据提交了吗很多开发环境里INSERT后忘了COMMIT同一个连接里查能看到程序里换一条连接就查不到其实数据还在未提交状态。排查时先确认事务状态尤其MySQL默认InnoDB是自动提交的但如果你手动开启了事务就得格外小心。另一个常见原因是字符集和大小写。MySQL默认的排序规则下字符串比较不区分大小写WHERE name zhangsan能查到ZhangSan但如果你当时建表时指定了utf8mb4_bin这种二进制排序规则它就严格区分大小写查不到很正常。遇到这种怪问题先看表的字符集和排序规则。还有一个最容易忽略的WHERE条件里写错字段类型。比如字段是VARCHAR你直接拿数字去查SELECT * FROM student WHERE age 18;这里age如果定义成VARCHAR但你存的是18查询时MySQL可能做隐式转换导致索引失效。更危险的是如果字段是字符串类型你拿数值去匹配MySQL确实会转成数值后比较但一旦转换出错结果就是全表扫。排查这类问题直接看EXPLAIN有没有走索引。4.2 NULL值处理一不留神就翻车NULL是SQL世界里的三值逻辑怪物。查NULL不能用等于要用IS NULLSELECT * FROM score WHERE score IS NULL;反过来查非空用IS NOT NULL。千万不用score NULL这个条件永远返回空结果因为你是在拿值跟“未知”做等值比较结果永远也是“未知”行不会被选中。前面提到的NOT IN坑也在这儿。假如你想查没选任何课程的学生SELECT * FROM student WHERE student_id NOT IN (SELECT student_id FROM score);如果score表里恰好有student_id为NULL的记录那么这个NOT IN的结果会把你所有想查的学生都过滤掉。原因是NOT IN遇到NULL时整个条件结果变成“未知”一行也查不出来。解决办法是子查询里先排除NULLSELECT * FROM student WHERE student_id NOT IN ( SELECT student_id FROM score WHERE student_id IS NOT NULL );或者干脆用NOT EXISTS这个写法更稳后面有机会再展开。记住一条涉及NULL的判断永远用IS NULL或者IS NOT NULL别的写法都不靠谱。4.3 误操作后的查询还原思路热词里有人搜“mysql update 还原”我虽然没有时光机但可以分享一套应急思路。假设你刚执行了一条UPDATE没带WHERE把整表字段都改了先别慌第一步是确认有没有开启binlog有的话可以从binlog里找到误操作前的位置用mysqlbinlog解析出原始值再做反向修复。如果没有binlog唯一的希望是数据有没有备份。补救流程大概是先停止业务写入防止新数据污染现场然后直接从备份文件把那张表恢复到误操作前的时间点最后如果还有其他增量写入再把binlog里的增量SQL过滤掉误操作的部分后重放。这个场景的真话是什么真话是平时把备份做好比什么技巧都重要。查询操作练得再熟也挡不住手滑。我给自己的原则就是生产环境执行UPDATE、DELETE之前先写SELECT确认要改哪些行查出来看一眼没问题再改。这条习惯记到肌肉里能救你无数次。5. 从简单查询走向性能优化查询写正确了第二步追求的是写得快。MySQL的查询性能问题百分之九十都出在表扫描上。这一节我不展开讲调优全体系只讲简单查询能直接用的两个抓手EXPLAIN和索引。5.1 用EXPLAIN给查询做体检任何一条查询语句前面加EXPLAINMySQL就会告诉你它打算怎么执行。我常用的是看type、key、rows这三个字段。EXPLAIN SELECT name FROM student WHERE student_id 1;type字段如果出现ALL说明是没走全表扫表的数据量小还好数据量一大就要警觉。出现ref或range通常代表走了索引const代表等值命中主键这是比较理想的。key字段显示实际用到的索引名。如果显示NULL说明没走索引。rows字段是MySQL估算要扫的行数这个值越小越好。你把自己那条慢查询前面加个EXPLAIN基本一眼就能定位问题是不是出在“全表扫”上。第一次看EXPLAIN的人最容易犯的错是只看有没有走索引不看扫描行数。有时候明明走了一个索引但因为条件写得宽泛扫的行数还是接近全表。真正有效的优化是让扫描行数降下来。5.2 索引与简单查询的关系索引的概念可以理解成一本书的目录。MySQL里最常见的索引类型是B树它把字段值排好序查询时通过二分查找快速定位目标行。你在WHERE、JOIN条件里用到的字段如果建了索引查询效率通常会有质的提升。拿前面的例子说score表经常按student_id和course_id查就非常适合建联合索引CREATE INDEX idx_student_course ON score(course_id, student_id);这里我把course_id放在前面因为业务查询通常先按课程过滤再关联学生。联合索引有“最左前缀”原则索引的字段顺序决定了它能匹配的条件组合。如果查询条件只包含第二个字段这个索引就用不上。所以在设计索引顺序时一定要优先考虑最常见的那组等值条件。我见过不少人一听说索引能提速就在每一列上都建一个结果写入变慢、磁盘占用飙升查询效率反而没提升多少。索引不是越多越好它的本质是拿空间换时间。简单查询阶段你只要记住WHERE里常用的等值字段、JOIN的关联字段值得建索引频繁更新的字段、低选择度的字段比如性别建索引的意义不大。要从简单查询往更深的水域走方向大概就是这几条多表关联的JOIN顺序优化、子查询改JOIN、聚合查询的思路、以及EXPLAIN的深入使用。我后面打算专门写一篇关于索引设计和慢查询优化的内容可以先把这个坑占住。你在日常查询操作里遇到什么特别诡异的现象也欢迎留言聊一聊毕竟SQL这玩意儿经验基本都是从踩坑里攒起来的。