MySQL数据查询操作实战：从单表查询到索引优化

简介：面向国家开放大学数据库课程学习者的一份MySQL数据查询实验训练资料，聚焦SELECT语句的完整应用体系，覆盖字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN聚合函数查询，以及内连接、外连接、复合条件连接和IN/EXISTS嵌套查询等典型场景，适合正在完成数据库实训作业或备战考试的学生参考。压缩包内仅含1个PDF文档，整体约1.75MB，内容按实验2.1至2.16逐项展开，每个实验均配有题目、SQL语句分析和实现要点，并以商品表、订单表、用户表、购物车表、评论表等业务数据为例，便于按步骤对照练习。该资源已被3336人浏览学习，实用度较高。通过这份PDF，读者可系统理清单表查询到多表连接、嵌套查询的编写思路，理解各聚合函数的使用场景与GROUP BY配合方式，并掌握去重、排序、分组和连接条件的细节，为后续数据库开发与管理打下扎实基础。

1. 数据查询操作：为什么实验训练2能把一半人卡在门外

某开放大学的《MySQL数据库应用》课程，实验训练2的主题是数据查询操作。很多人在实验训练1里建库建表顺手得很，一进这个实验就开始翻车：题目看得懂，SQL写不出；写出来了，结果对不上；结果对上了，又担心是不是蒙对的。其实数据查询操作翻来覆去就三件事——选哪张表、按什么条件过滤、按什么维度汇总。这个实验训练把单表查询、条件过滤、排序分页、聚合分组、多表连接和子查询串成一条线，是整门课里最值得认真练的基础功。它适合正在修这门课、需要把作业一次交对的人，也适合想系统补SQL查询功底的自学者。读完这篇，你能在自己的机器上把实验环境搭起来，把每类查询写成能跑的SQL，也知道结果不对时先从哪儿排查。

2. 查询前先搞懂执行顺序：实验库表准备与两条必须记的规则

写任何一条SELECT之前，先建立这个观念：你写在SQL里的句子顺序，和数据库真正执行的顺序不是一回事。做实验训练2时，不少“玄学”问题都出在执行顺序上——不是语法错，而是你按书写顺序去推理，推出来的结果自然对不上。

2.1 SQL 执行顺序：写对 WHERE 和 HAVING 的分水岭

一条完整查询的书写顺序是 SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT，但引擎的执行顺序不同：

这个顺序不是考点，却是所有“看着没错就是报错”问题的根源。先说最关键的两条规则。

第一条：WHERE在分组之前执行，所以WHERE里不能用聚合函数，比如WHERE COUNT(*) > 1一定会报错；而HAVING在分组之后执行，可以用聚合结果，但必须配合GROUP BY使用，单独写HAVING没意义。第二条：SELECT子句里的别名是最后才生成的，所以WHERE和HAVING里都不能引用别名。很多人在实验题里写WHERE 姓名 = '张明'，以为前面AS 姓名了就能用，结果直接报错。正确做法是回到原始列名：

-- 错误写法：WHERE 里用别名，MySQL 直接报错 SELECT name AS 姓名 FROM student WHERE 姓名 = '张明'; -- 正确写法：WHERE 里写原始列名 SELECT name AS 姓名 FROM student WHERE name = '张明';

这两条规则记牢，实验训练2里至少一半的报错都能自己定位。后面第5章的踩坑清单里，还有几个问题也是从这里长出来的。

2.2 建库建表与示例数据：让每条查询都有结果可验证

理论先立住，接下来动手。我建议你不要直接用课程平台自带的在线环境，而是在本地装一个MySQL，自己建库、自己插数据。原因很实际：在线环境的数据是固定的，你只能照着答案跑，跑完也不知道为什么；本地环境可以随意改数据，每条查询的结果你都能人工验算，这才叫练。

先建一个专用数据库，统一用utf8mb4字符集，避免中文乱码：

CREATE DATABASE IF NOT EXISTS lab_query DEFAULT CHARSET utf8mb4; USE lab_query;

实验训练2的查询操作需要至少两张有关联的表。最常见的设计是一张学生表、一张成绩表，通过学号关联。下面是完整的建表语句：

CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号', name VARCHAR(50) NOT NULL COMMENT '姓名', gender CHAR(1) COMMENT '性别', birth_date DATE COMMENT '出生日期', class_name VARCHAR(50) COMMENT '班级', enroll_date DATETIME COMMENT '入学时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL COMMENT '学号，对应 student.id', course_name VARCHAR(50) COMMENT '课程名', score DECIMAL(5,2) COMMENT '成绩', exam_date DATE COMMENT '考试日期', CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里要注意几个参数的用意：id用AUTO_INCREMENT自增，插入数据时不用手动写；score表里的student_id加了外键约束，保证它指向的学号一定存在于student表，插入非法学号会被拒绝；score字段用DECIMAL(5,2)，表示最多5位数字、小数点后保留2位，成绩这种带小数的数据用这个类型比FLOAT更稳。

然后插入几条能覆盖各种查询场景的示例数据：

INSERT INTO student (name, gender, birth_date, class_name, enroll_date) VALUES ('张明', '男', '2004-05-12', '计算机2401', '2024-09-01 08:30:00'), ('李静', '女', '2005-01-20', '计算机2401', '2024-09-01 08:30:00'), ('王强', '男', '2003-11-03', '软件2402', '2024-09-01 08:30:00'), ('赵敏', '女', '2004-07-18', '软件2402', '2024-09-01 08:30:00'), ('刘洋', '男', '2004-03-25', '大数据2403', '2024-09-01 08:30:00'); INSERT INTO score (student_id, course_name, score, exam_date) VALUES (1, 'MySQL数据库应用', 92.50, '2024-11-15'), (1, '数据结构', 88.00, '2024-12-20'), (2, 'MySQL数据库应用', 78.00, '2024-11-15'), (3, 'MySQL数据库应用', 85.00, '2024-11-15'), (3, '操作系统', 65.50, '2024-12-18'), (4, 'MySQL数据库应用', 95.00, '2024-11-15');

插入后先做一次验证，把两张表完整查出来看看。注意，这里已经用到了最简单的查询语句：

SELECT * FROM student; SELECT * FROM score;

数据量故意做得不大：5个学生、6条成绩，其中赵敏（学号4）只选了一门课，刘洋（学号5）一条成绩都没有。后面写连接和子查询时，这两条数据就是检验结果对错的锚点。比如你写LEFT JOIN，就应该能查出刘洋的名字且成绩为NULL，查不出来说明连接条件写错了。

3. 单表查询实操：条件过滤、排序分页与聚合分组的完整写法

单表查询是实验训练2里占比最大的部分，也是后面所有复杂查询的地基。这类题目的共同特征是：数据都在一张表里，不需要连表，靠SELECT、WHERE、ORDER BY、LIMIT和聚合函数就能完成。我把它们拆成四个小节，每一节都给可以直接抄的写法。

3.1 SELECT 列投影与别名：先决定输出什么

最简单的查询是取全部列，但实验题很少让你SELECT *，因为考试和作业看的是你对输出的控制能力。把不需要的列裁掉，是第一条职业习惯：

SELECT name, gender, birth_date FROM student;

只输出姓名、性别、出生日期三列。数据量小的时候SELECT *无所谓，但到了真实项目里，一张表几十个字段、上千万行，SELECT *会拖垮网络和内存，这个习惯要从实验训练就开始养。

别名的典型场景是把英文或计算表达式变成可读的中文标题。比如题目要求“查询学生姓名和年龄”，student表里没有年龄字段，只有出生日期，就得现算：

SELECT name AS 姓名, YEAR(CURDATE()) - YEAR(birth_date) AS 年龄 FROM student;

YEAR()从日期里抽出年份，CURDATE()取当前日期，两者相减就是近似年龄。注意，这个计算是在SELECT阶段完成的，所以别名“年龄”只能在显示结果里看到，不能在WHERE里用WHERE 年龄 > 20过滤，原因就是第2章讲的执行顺序。

3.2 WHERE 条件过滤：比较、范围与模糊匹配

WHERE是数据查询操作里最核心的语法，实验题一半以上的分值压在条件过滤上。需要掌握的比较运算符有这么几类：等值比较=、<>，大小比较>、<、>=、<=，范围判断BETWEEN AND，离散列表IN，模糊匹配LIKE，空值判断IS NULL。

先看最普通的等值比较，筛选出某个班的学生：

SELECT name, class_name FROM student WHERE class_name = '计算机2401';

再看范围判断，查出成绩在60到90之间（包含边界）的记录：

SELECT name, score FROM score WHERE score BETWEEN 60 AND 90;

BETWEEN包含两端边界，如果题目考“不及格”或“优秀”这种区间，直接拿边界值代入验算。模糊匹配是另一个高频考点，LIKE配合两个通配符：%表示任意多个字符，_表示恰好一个字符。比如查所有姓张的同学：

SELECT name FROM student WHERE name LIKE '张%';

如果需要区分“张”后面是一个字还是多个字，张_和张%就能给出不同的结果集。这个区别经常出现在选择题里，实验操作里也建议你两种都跑一遍看看。

多个条件组合时，AND和OR的优先级要小心。AND比OR优先级高，所以“计算机班且成绩大于80，或软件班”这个描述如果不加括号，会被理解成完全不同的意思。稳妥的写法是给每个分支都加括号：

SELECT name, score FROM score WHERE course_name = 'MySQL数据库应用' AND score >= 80;

这个查询的意思是：查出数据库课程里成绩不低于80分的记录。先按课程过滤，再按分数过滤，两步都在分组前完成。

3.3 排序、分页与去重：答案顺序也是评分点

查出来的数据默认按插入顺序排列，但实验题经常要求“按成绩从高到低”或“取前几名”，这就轮到ORDER BY和LIMIT上场。排序的写法很简单：

SELECT name, score FROM score WHERE course_name = 'MySQL数据库应用' ORDER BY score DESC;

DESC表示降序，ASC表示升序，不写默认升序。多个排序条件用逗号连接，比如ORDER BY score DESC, name ASC，意思是先按成绩排，成绩相同的按姓名升序排。这个细节在“取前N名”的题目里特别重要——成绩并列时，第二名是谁由第二个排序条件决定。

取前三名用LIMIT：

SELECT name, score FROM score ORDER BY score DESC LIMIT 3;

LIMIT放在整条语句的最后，表示只返回前3行。分页场景下LIMIT还可以带偏移量，LIMIT 2, 3表示跳过前2行取3行，偏移量从0开始计数，所以第1页是LIMIT 0, 3，第2页是LIMIT 3, 3。实验训练题里经常考“第三名到第五名”，用LIMIT 2, 3一次到位。

去重用DISTINCT，作用是把结果集中完全相同的行合并成一行。比如想看看成绩表里一共开过哪些课：

SELECT DISTINCT course_name FROM score;

注意DISTINCT作用于后面所有列的组合，SELECT DISTINCT course_name, score会按两列的组合去重，而不是单独对课程去重。这是一个容易看走眼的地方。

3.4 聚合统计与分组：COUNT、GROUP BY 和 HAVING 的配合

单表查询的最后一类核心是统计。MySQL的聚合函数有五个最常用：COUNT统计行数，SUM求和，AVG求平均，MAX求最大，MIN求最小。聚合函数本身不复杂，复杂的是和GROUP BY、HAVING的配合。

先看最简单的总数统计：

SELECT COUNT(*) AS 学生总数 FROM student;

COUNT()和COUNT(列名)有一个重要区别：COUNT()统计所有行，包括某个字段为NULL的行；COUNT(列名)会跳过NULL。实验题如果问“有成绩的学生人数”，用COUNT(DISTINCT student_id)而不是COUNT(*)，因为一个学生可以有多条成绩记录。

按课程统计选课人数和平均分，是GROUP BY的典型用法：

SELECT course_name, COUNT(*) AS 选课人数, AVG(score) AS 平均分 FROM score GROUP BY course_name;

GROUP BY把course_name相同的行合成一组，然后对每一组执行聚合函数。写这条语句时要记住一个规则：SELECT后面出现的非聚合列，必须都出现在GROUP BY里。比如你写了SELECT course_name, score, COUNT(*)，score不在GROUP BY里，MySQL会随机取这一组里的一行score，结果看起来没问题，实际是错的。

如果想筛选“平均分大于等于80的课程”，WHERE做不到，因为WHERE在分组前执行，那时候平均分还没算出来。必须用HAVING：

SELECT course_name, AVG(score) AS 平均分 FROM score GROUP BY course_name HAVING AVG(score) >= 80;

WHERE和HAVING的分工是：先WHERE过滤原始行，再GROUP BY分组，最后HAVING过滤分组结果。判断一个条件该放在哪里，就看它有没有用聚合函数。用了聚合函数，只能进HAVING；没用聚合函数，放WHERE效率更高。

4. 多表连接与子查询：实验题里最高频的组合查询套路

单表能完成的事有限，实验训练2里真正拉开分数差距的，是多表连接和子查询。前面建表时故意把学生信息和成绩分开，就是为了在这一章用起来。

4.1 内连接与左连接：分清“只要匹配”和“保留全部”

两张表之间靠外键关联，student表的id对应score表的student_id。连接查询的核心是告诉数据库“这两张表按什么条件对上”，这个条件写在ON后面。先看最常用的内连接：

SELECT s.name, sc.course_name, sc.score FROM student s INNER JOIN score sc ON s.id = sc.student_id;

s和sc是表别名，作用是少打字，也让长查询更清晰。INNER JOIN只保留两边都匹配得上的行：选了课的学生会出现，没选课的学生直接消失。对照第2章的示例数据，刘洋（学号5）一条成绩都没有，内连接的结果里不会出现他。这个预期结果就是验证查询是否正确的方法。

左连接则是另一套语义，它以左边的表为准，左表所有行都保留，右表没有匹配的行就补NULL：

SELECT s.name, sc.course_name, sc.score FROM student s LEFT JOIN score sc ON s.id = sc.student_id;

执行后对比内连接，你会发现刘洋的名字出现了，但course_name和score都是NULL。这个差异不是bug，是连接类型本身的定义。

左连接最有价值的应用场景是“找出没有匹配记录的行”，比如查出哪些学生一门课都没选：

SELECT s.name FROM student s LEFT JOIN score sc ON s.id = sc.student_id WHERE sc.student_id IS NULL;

先左连接把所有学生都带出来，再过滤掉有成绩的行，剩下的就是没选课的人。注意这里的过滤条件必须写IS NULL，不能写= NULL，NULL不能用等号判断，这是MySQL里一个经典易错点。

4.2 子查询：把一步拆成两步再拼回来

子查询就是先查一次，把结果当成另一个查询的条件。实验题的逻辑往往不止一层，子查询是理清嵌套逻辑的好工具。最常见的位置是IN后面：

SELECT name FROM student WHERE id IN ( SELECT student_id FROM score WHERE course_name = 'MySQL数据库应用' );

内层先查出所有选过数据库课程的学生学号，得到类似 (1, 2, 3, 4) 这样一个列表；外层再从这个列表里取姓名。这个写法其实就是内连接的另一种表达，但子查询的逻辑更直白——先拿名单，再查详情。

另一类常见子查询是“比平均分高”的题，它用比较运算符接一个返回单值的子查询：

SELECT s.name, sc.score FROM student s INNER JOIN score sc ON s.id = sc.student_id WHERE sc.score > ( SELECT AVG(score) FROM score );

内层算出全表平均分，外层比较每个成绩是否高于它。写这类查询有一个好习惯：先单独执行内层，确认它返回的是一个值还是一个列表，再决定外层用>还是IN。子查询返回多行时用=会直接报错，这是实验里很常见的翻车点。

4.3 连接加聚合：统计每门课程选课人数与平均分的典型写法

前面的聚合都在单表上做，实际题目往往要求先连接再分组。比如统计每门课的选课人数和平均分，虽然一张成绩表就能算，但一旦题目加上“按班级”这类维度，就必须连接学生表：

SELECT s.class_name, sc.course_name, AVG(sc.score) AS 平均分 FROM student s INNER JOIN score sc ON s.id = sc.student_id GROUP BY s.class_name, sc.course_name ORDER BY s.class_name, sc.course_name;

这条语句的执行过程是：先连接两张表得到完整明细，再按班级加课程的组合分组，然后对每个组算平均分，最后按班级和课程排序输出。这里要守的规矩还是第3章那条：GROUP BY多列时，SELECT里的非聚合列必须全部出现在GROUP BY里，漏掉一个，结果就是错的。

实验题偶尔会出现三张表甚至更多表连续连接的情况，思路完全一样：每多一张表就多一个JOIN、多一个ON条件。先确认每一对表之间的关联字段，再从小到大逐步把表加进去，不要一次写完。加表的顺序不影响最终结果，但影响你排查问题的难度。

5. 查询结果对不上？数据查询实操的 5 个经典避坑

实验训练2交上去的作业，最常见的反馈不是“不会写”，而是“结果对不上”。下面这5个坑，基本每一轮都会遇到，每个都按“现象 → 原因 → 解决”写清楚。建议你在自己的库里把每条都复现一遍，再对照解决，印象会深得多。

5.1 行数对不上：连接缺条件与 COUNT 的误用

坑1：连接查询结果行数爆炸。

现象：两张表都只有五六行，INNER JOIN的结果却返回几十行，甚至翻了好几倍。

原因：JOIN后面忘了写ON条件，或者条件里的关联字段写错了，两表做笛卡尔积，行数直接相乘。

解决：先分别执行SELECT COUNT(*) FROM student;和SELECT COUNT(*) FROM score;，确认基准行数。再检查ON后面的字段名，student表的id和score表的student_id是两个不同的字段名，但指的是同一个值，写错一个就匹配不上。连接查询写完，心里要预估算数：一对一关联，结果行数不可能超过右表匹配行数。

坑2：COUNT统计结果翻倍。

现象：想统计学生人数，结果比学生总数大。

原因：查询里带了连接或子查询，中间步骤把行复制了。比如统计“有成绩的学生人数”，直接COUNT(*)数的是成绩记录数，一个学生两门课就占两行。

解决：先想清楚统计口径。学生人数就应该在student表上COUNT(*)；有成绩的人数用COUNT(DISTINCT student_id)；按班级统计人数就GROUP BY class_name后COUNT(*)。口径定错了，SQL语法再对，答案也是错的。

5.2 查不出/查不对：字符集、空值与日期格式的隐性坑

坑3：中文条件查不出来。

现象：WHERE name = '张明'查不到记录，但SELECT * FROM student;明明能看到这行数据。

原因：最常见的是字段里混入了全角空格或不可见字符；其次是表的字符集不是utf8mb4，中文存进去时已经变了。

解决：先用SELECT name, HEX(name) FROM student WHERE id = 1;看原始字节，确认有没有隐藏字符。有空格就用TRIM(name)去掉再比较。建表时统一CHARSET=utf8mb4可以提前避开大部分乱码类问题。

坑4：按日期查询结果为空。

现象：WHERE exam_date = '2024-11-15'查不到，但数据确实存在。

原因：字段类型不匹配或数据带时间。如果exam_date是DATE类型，精确等值应该能查到；但如果实验数据里的exam_date实际是DATETIME，或者你用的是VARCHAR存日期，等值比较就会落空。

解决：用DATE()函数把字段转成纯日期再比较：WHERE DATE(exam_date) = '2024-11-15'。范围查询用BETWEEN更稳：WHERE exam_date BETWEEN '2024-11-01' AND '2024-11-30'，一次把整月数据捞出来，不会漏边界。

坑5：HAVING里用别名翻车。

现象：写了HAVING 平均分 > 80，有时候报错，有时候结果不对。明明SELECT里已经AS 平均分了。

原因：执行顺序里别名在SELECT阶段才生成，HAVING在分组后、SELECT之前执行，不同版本对别名的容忍度不一样。依赖别名就是靠运气。

解决：HAVING里老老实实重写聚合表达式：HAVING AVG(score) > 80。同理，WHERE里也不要用别名。规则只有一条：别名只能在SELECT和ORDER BY里用。

6. 用 EXPLAIN 验证自己的查询：一条命令看出 SQL 怎么跑

实验训练的数据量很小，查询都跑得飞快，所以很多人不关心SQL的执行质量。但这条命令值得从现在就养成习惯：在查询前面加一个EXPLAIN，MySQL会告诉你它打算怎么执行这条SQL，而不真的执行它。

EXPLAIN SELECT s.name, sc.score FROM student s INNER JOIN score sc ON s.id = sc.student_id WHERE sc.score > 80;

EXPLAIN输出的是一张执行计划表，字段很多，第一眼只需要盯三个：

type为ALL意味着MySQL要把整张表扫一遍才能找到目标行。数据只有几行时无所谓，但你要意识到：同样的SQL放到千万行的表上，全表扫描可能把查询拖到分钟级。索引的作用就是把ALL变成ref。给关联字段和过滤字段加上索引，是最立竿见影的优化：

ALTER TABLE score ADD INDEX idx_student_id (student_id); ALTER TABLE score ADD INDEX idx_course_name (course_name);

加完索引再跑一次EXPLAIN，对比type和rows的变化，你能直观看到索引带来的差距。注意索引不是越多越好，它会拖慢写入速度，实验数据量下感觉不到，但原理要清楚。

我后来做真实项目时有个习惯，一直保持到现在：任何一条查询，先在前面加EXPLAIN跑一遍再执行正式语句。不是为了优化这几行实验数据，而是为了建立对SQL执行代价的直觉。数据查询操作这门实验训练，我当年也是一路踩坑过来的，唯一让我后来工作中持续受益的习惯，就是每条SQL都先EXPLAIN一遍，再谈优化。希望帮到你。

本文还有配套的精品资源，点击获取