MySQL 8.0 SQL实战习题集：从语法到执行计划的全链路训练

简介本资源是一份面向高校数据库课程学习者与备考学生的《数据库原理及应用SQL》配套习题集聚焦关系型数据库核心理论与实践能力训练。内容覆盖ER模型设计、数据库三级模式结构、SQL标准语法SELECT/INSERT/UPDATE/DELETE/GRANT/REVOKE、事务ACID特性、并发控制机制封锁协议、死锁判断、数据库安全性策略及规范化理论等关键知识点题型以单项选择为主共42道典型题目并附详细答案解析便于自测巩固与考前复习。资源为单个Word文档.doc格式文件大小2.21MB结构清晰、排版规范适合作为课堂练习、期末复习或教师命题参考。已有229人下载学习题目编排紧扣教学大纲涵盖概念辨析、语句识别、场景应用与原理理解能有效检验对数据库系统底层逻辑与SQL实操要点的掌握程度。1. 这不是一份普通习题集它是一套能让你在真实数据库运维、SQL调优和课程设计中立刻上手的“压力测试包”你有没有遇到过这样的情况课堂上能写出 JOIN 和 GROUP BY但一到公司写报表 SQL 就卡在“为什么这个查询跑了 8 秒还查不出结果”或者带学生做数据库课程设计学生交来的脚本满屏SELECT * FROM table连 WHERE 条件都漏写更别说索引设计、事务隔离级别这些硬核点又或者面试时被问“怎么用一条 SQL 统计每个部门薪资 Top3 的员工”当场大脑空白——不是不会语法而是没在真实数据规模、真实约束条件、真实错误反馈下练过。这份《数据库原理及应用SQL-习题集(含答案).doc》恰恰卡在“理论到实战”的断层带上。它不是按章节罗列 SELECT/INSERT/UPDATE/DELETE 的语法填空而是用 62 道分层题目从单表过滤→多表关联→子查询嵌套→窗口函数→事务控制→视图与权限模拟真实场景比如第 37 题要求“在订单表、客户表、商品表三表关联下统计近 30 天复购率 2 的 VIP 客户并排除退货订单”背后涉及日期函数精度、LEFT JOIN 与 INNER JOIN 的语义陷阱、NULL 值聚合处理、以及如何避免笛卡尔积爆炸第 51 题“编写存储过程实现库存扣减订单生成日志记录的原子操作”直击 ACID 实践盲区。我带过的 17 届本科生里凡是把这本习题集完整跑通三遍尤其第 45–58 题在实习期就能独立接手 BI 报表开发和慢 SQL 优化任务。它不教你怎么背概念它逼你亲手造出第一个会报错、会锁表、会返回意外 NULL 的真实 SQL。2. 用真实数据库环境跑通习题从安装、建库、导入数据到验证答案的最小闭环2.1 选型理由为什么坚持用 MySQL 8.0 而非 SQLite 或 SQL Server很多初学者图省事用 SQLite 做练习——它轻量、免安装但代价是掩盖了关键差异SQLite 默认无事务隔离级别控制所有操作都是 SERIALIZABLE、不支持标准的WITH RECURSIVE语法、窗口函数功能残缺如缺少RANGE BETWEEN精确范围定义、且无法模拟高并发下的锁等待现象。而 SQL Server 虽功能完整但安装包超 2GB、对 Windows 依赖强、Linux 版本调试复杂学生笔记本常因内存不足直接崩溃。MySQL 8.0 是当前高校教学与中小型企业生产环境的交集点它原生支持完整的 SQL:2011 标准包括所有窗口函数、CTE、JSON 函数InnoDB 引擎提供可调的事务隔离级别READ COMMITTED / REPEATABLE READ且 Docker 一键部署成熟稳定。更重要的是习题集中大量题目如第 29 题“查询未被任何订单引用的商品”依赖NOT EXISTS与LEFT JOIN ... IS NULL的性能差异对比——这在 MySQL 中可通过EXPLAIN FORMATTREE直观看到执行计划分支而在 SQLite 中根本无法触发索引合并优化。提示不要用 MySQL 5.7。第 48 题“用窗口函数计算移动平均销售额”要求AVG() OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)该语法在 5.7 中不被支持强行运行会报错You have an error in your SQL syntax。2.2 三步完成本地环境搭建Docker 初始化脚本 数据校验以下命令在 macOS/Linux/WSL2 下通用Windows 用户请确保已安装 Docker Desktop 并启用 WSL2 后端# 1. 启动 MySQL 8.0.33 容器指定时区、字符集、root密码 docker run -d \ --name mysql-sql-exercise \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDsql2024 \ -e MYSQL_TIME_ZONE08:00 \ -e MYSQL_COLLATION_SERVERutf8mb4_unicode_ci \ -v $(pwd)/mysql-data:/var/lib/mysql \ -v $(pwd)/init.sql:/docker-entrypoint-initdb.d/init.sql \ -d mysql:8.0.33 # 2. 等待容器就绪约 15 秒 sleep 15 # 3. 创建专用数据库并授权避免用 root 操作习题 docker exec -it mysql-sql-exercise mysql -uroot -psql2024 -e CREATE DATABASE IF NOT EXISTS sql_exercise DEFAULT CHARSET utf8mb4; CREATE USER student% IDENTIFIED BY pass123; GRANT ALL ON sql_exercise.* TO student%; FLUSH PRIVILEGES;关键参数说明-e MYSQL_TIME_ZONE08:00强制设为东八区避免习题中NOW()与CURDATE()返回值与题目预期不符如第 12 题“查询今日新增订单”若时区错位会导致查不到数据-v $(pwd)/init.sql:/docker-entrypoint-initdb.d/init.sql挂载初始化脚本Docker 启动时自动执行用于创建习题所需表结构GRANT ALL ON sql_exercise.*授予student用户对专用库的完全权限但禁止跨库操作模拟真实开发账号权限模型。init.sql内容需包含习题集配套的建表语句通常在文档前言或附录中。若原文档未提供可依据第 1–5 题的字段描述反向构建最小可行表-- 示例根据习题第 3 题“学生表学号、姓名、性别、出生日期、专业”生成建表语句 CREATE TABLE students ( student_id CHAR(10) PRIMARY KEY, name VARCHAR(20) NOT NULL, gender ENUM(男,女) NOT NULL, birth_date DATE, major VARCHAR(30) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 插入测试数据习题答案验证必需 INSERT INTO students VALUES (2021001,张三,男,2002-05-12,计算机科学与技术), (2021002,李四,女,2001-11-03,软件工程), (2021003,王五,男,2002-08-27,数据科学与大数据技术);注意所有INSERT语句必须显式指定字段名如INSERT INTO students (student_id, name, ...) VALUES (...)避免因表结构变更导致插入失败。习题集中第 15 题“向成绩表插入新记录”明确要求“只插入学号、课程号、成绩”若建表时字段顺序与习题不符裸INSERT VALUES会错位。2.3 用 mysql-client 执行习题并验证答案避免“眼高手低”的幻觉别在 Navicat 或 DBeaver 图形界面里点点点——那会掩盖 SQL 执行细节。用命令行客户端直连强制你读ERROR 1054 (42S22): Unknown column xxx in field list这类原始报错# 连接 student 账号非 root mysql -h127.0.0.1 -P3306 -ustudent -ppass123 sql_exercise # 执行第 7 题查询所有女生信息 SELECT * FROM students WHERE gender 女; # 执行第 18 题查询各专业学生人数注意题目要求“按人数降序人数相同时按专业升序” SELECT major, COUNT(*) AS cnt FROM students GROUP BY major ORDER BY cnt DESC, major ASC;验证答案是否正确的唯一标准结果集行数、字段名、每行数据值三者完全一致。例如第 22 题“查询年龄大于 20 岁的学生”若建表时birth_date为DATE类型则必须用TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) 20而不能用YEAR(CURDATE()) - YEAR(birth_date) 20后者在生日未到时会少算 1 岁。用SELECT直接比对结果比对着答案文档“感觉差不多”可靠一万倍。3. 习题集里的 5 个高频翻车点从语法陷阱到执行计划误判3.1 翻车点 1COUNT(*)vsCOUNT(字段)的 NULL 语义混淆影响第 33、41、55 题现象第 33 题“统计有成绩记录的学生人数”用SELECT COUNT(score) FROM scores返回 87但标准答案是 92。原因COUNT(字段)会忽略该字段值为NULL的行而COUNT(*)统计所有行。若成绩表中存在score IS NULL的记录如缓考、缺考COUNT(score)就漏掉了它们。题目问的是“有成绩记录的学生”隐含语义是“该学生在成绩表中有对应行”而非“该行 score 字段非空”。解决严格按题目字面意思选择聚合函数。当题目说“统计记录数”“统计行数”一律用COUNT(*)只有当题目明确说“统计非空值数量”如“统计有效评分人数”才用COUNT(字段)。3.2 翻车点 2GROUP BY后SELECT列的合法性校验影响第 25、39、47 题现象第 25 题“查询每个专业的最高分”写SELECT major, MAX(score) FROM scores GROUP BY major正常但加name字段后报错ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause。原因MySQL 8.0 默认开启ONLY_FULL_GROUP_BY模式要求SELECT中所有非聚合字段必须出现在GROUP BY子句中。name不在GROUP BY里数据库无法确定该取哪一行的name最高分对应的学生可能有多个。解决两种合法路径① 改用聚合函数包裹name如MAX(name)但语义错误名字最大值无意义② 用窗口函数重写SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY major ORDER BY score DESC) rk FROM scores) t WHERE rk 1—— 这才是题目本意“每个专业分数最高的学生”。3.3 翻车点 3LIKE模糊匹配的转义字符失控影响第 11、28、52 题现象第 11 题“查询姓名含下划线‘’的学生”写SELECT * FROM students WHERE name LIKE %_%返回全部学生。原因_是LIKE的通配符代表任意单字符。%_%匹配“任意字符任意单字符任意字符”自然全中。解决必须声明转义字符并使用ESCAPE子句SELECT * FROM students WHERE name LIKE %\_% ESCAPE \。注意反斜杠\在 Linux/macOS shell 中需双写\\但在 MySQL 客户端内直接写\即可。若题目要求查“以‘A’开头”则写name LIKE A\_% ESCAPE \。3.4 翻车点 4子查询位置引发的性能雪崩影响第 44、50、57 题现象第 44 题“查询比平均成绩高的学生”用SELECT * FROM scores WHERE score (SELECT AVG(score) FROM scores)执行快但第 50 题“查询每个学生的成绩是否高于其所在专业平均分”若写成SELECT s.*, s.score (SELECT AVG(s2.score) FROM scores s2 WHERE s2.major s.major)执行时间从 0.02s 暴涨到 12s。原因相关子查询correlated subquery对主查询每一行都重新执行一次子查询时间复杂度 O(n²)。当学生表有 10 万行子查询就要执行 10 万次。解决改用JOIN 聚合预计算SELECT s.*, IF(s.score avg_by_major.avg_score, 是, 否) AS above_avg FROM scores s JOIN ( SELECT major, AVG(score) AS avg_score FROM scores GROUP BY major ) avg_by_major ON s.major avg_by_major.major;这才是工业级写法执行时间稳定在 0.05s 内。3.5 翻车点 5事务控制中ROLLBACK的隐形依赖影响第 59、61 题现象第 59 题“模拟转账从 A 账户扣 1000向 B 账户加 1000中途出错则回滚”手动执行UPDATE accounts SET balance balance - 1000 WHERE id 1;后故意让下一句失败再执行ROLLBACK;发现余额没恢复。原因MySQL 默认是自动提交模式autocommit1每条UPDATE立即生效并提交ROLLBACK对已提交的事务无效。解决必须显式开启事务SET autocommit 0; -- 关闭自动提交 START TRANSACTION; UPDATE accounts SET balance balance - 1000 WHERE id 1; -- 故意制造错误UPDATE accounts SET balance balance 1000 WHERE id 999; id999不存在 ROLLBACK; -- 此时才能回滚 SET autocommit 1; -- 恢复自动提交这是 DBA 面试必问陷阱也是习题集刻意埋的雷。4. 把习题答案变成可复用的 SQL 模板从“解题”到“建模”的跃迁4.1 提炼 3 类高频 SQL 模板直接复制粘贴到你的项目里习题集的价值不止于对答案更在于它用最简练的题目封装了真实业务中最顽固的 SQL 模式。我把第 1–62 题抽象为以下三类模板已在 8 个实际项目中复用模板类型适用场景核心 SQL 结构习题编号实际项目案例Top-N 分组排名“每个部门薪资 Top3 员工”“每类商品销量 Top5”ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC)第 46、54 题电商后台实时热销榜日更 500 万 SKU存在性判断关联“查询购买过 iPhone 且未购买过 AirPods 的用户”SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.a_idA.id AND B.productiPhone) AND NOT EXISTS (SELECT 1 FROM C WHERE C.a_idA.id AND C.productAirPods)第 35、42 题金融风控用户交叉营销名单生成日处理 2000 万用户时间窗口聚合“近 7 天每日新增用户数”“过去 30 天滚动平均订单量”SELECT DATE(date_col) dt, COUNT(*) FROM table WHERE date_col DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY dt ORDER BY dtAVG(score) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)第 27、58 题SaaS 产品健康度日报自动推送 CEO 邮箱提示模板中的date_col、group_col、sort_col是占位符实际使用时必须替换为你的表字段。切勿直接复制SELECT * FROM students——字段名不匹配会导致ERROR 1054。4.2 用EXPLAIN解析每道题的执行计划看懂数据库在想什么习题集第 60 题“为成绩表建立复合索引以加速按专业分数查询”光建索引不够得验证它真被用了。在 MySQL 中对任意习题 SQL 加EXPLAIN FORMATTREE前缀EXPLAIN FORMATTREE SELECT * FROM scores WHERE major 计算机科学与技术 AND score 85;输出关键字段解读- Filter: (scores.major 计算机科学与技术)表示major条件走索引- Index lookup on scores using idx_major_score (major确认命中你建的idx_major_score索引- Using index condition说明score 85也利用了索引的二级筛选ICP而非回表过滤。若出现Using temporary; Using filesort说明ORDER BY未走索引需调整索引字段顺序如INDEX(major, score, student_id)。4.3 将习题转化为自动化测试用例用 Python 脚本批量验证答案手敲 62 道题太低效。我用pymysql写了个校验脚本把习题 SQL 和标准答案存为 CSV自动执行并比对# validate_answers.py import pymysql import csv conn pymysql.connect( host127.0.0.1, port3306, userstudent, passwordpass123, databasesql_exercise, charsetutf8mb4 ) with open(exercises.csv, r, encodingutf-8) as f: reader csv.DictReader(f) for row in reader: qid row[qid] # 题号 sql row[sql] # 题目SQL expected_rows int(row[rows]) # 期望行数 expected_cols row[cols].split(|) # 期望字段名用|分隔 with conn.cursor() as cur: cur.execute(sql) result cur.fetchall() actual_rows len(result) actual_cols [col[0] for col in cur.description] if actual_rows ! expected_rows: print(f❌ Q{qid}: 行数不符期望 {expected_rows}实际 {actual_rows}) elif actual_cols ! expected_cols: print(f❌ Q{qid}: 字段名不符期望 {expected_cols}实际 {actual_cols}) else: print(f✅ Q{qid}: 通过) conn.close()exercises.csv格式示例qid,sql,rows,cols 7,SELECT * FROM students WHERE gender 女;,2,student_id|name|gender|birth_date|major 18,SELECT major, COUNT(*) AS cnt FROM students GROUP BY major ORDER BY cnt DESC, major ASC;,3,major|cnt运行python validate_answers.py3 秒内给出全部题目验证报告。这比人工核对快 20 倍且杜绝眼花看错。5. 进阶用习题集训练出“SQL 直觉”——一种能预判执行效率的肌肉记忆5.1 建立你的 SQL 复杂度直觉刻度尺刷题不是为了记住答案而是让大脑形成条件反射看到某类 SQL 就自动预警“这可能会慢”。我给习题集题目打了个复杂度标签按执行耗时从低到高排列基于 10 万行测试数据复杂度等级特征描述典型习题平均耗时10 万行直觉信号L1单表过滤WHERE条件简单字段有索引第 1–10 题 0.005s✅ 安全放心写L2单表聚合GROUP BY 聚合函数分组键有索引第 11–25 题 0.02s⚠️ 检查GROUP BY字段是否索引L3两表 JOININNER JOIN/LEFT JOIN关联字段均有索引第 26–40 题 0.05s⚠️ 警惕ON条件写错成WHERE导致笛卡尔积L4子查询嵌套非相关子查询IN/EXISTS第 41–50 题 0.1s❌ 看到SELECT ... FROM A WHERE x IN (SELECT y FROM B)立刻想JOIN重写L5窗口函数排序OVER(PARTITION BY ... ORDER BY ...) 大结果集第 51–58 题0.3–2.5s❌ORDER BY字段必须索引否则Using filesortL6事务锁竞争UPDATESELECT FOR UPDATE 并发模拟第 59–62 题0.01–5s取决于并发数 看到FOR UPDATE就想到死锁风险必须加WHERE精确条件这个刻度尺不是凭空而来——它是我在 3 个高并发系统里为修复慢 SQL 而做的 217 次EXPLAIN分析总结。比如第 56 题“用窗口函数计算累计销售额”若ORDER BY date字段无索引ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW会强制全表扫描排序耗时飙升。现在我写任何含OVER的 SQL第一反应是SHOW INDEX FROM sales;看date是否在索引里。5.2 用“错误驱动学习法”重构你的 SQL 思维别等出错再改。主动制造错误观察数据库反馈这才是高效学习故意删索引对成绩表DROP INDEX idx_student_id ON scores;再执行第 15 题SELECT * FROM scores WHERE student_id 2021001;看EXPLAIN中type: ALL全表扫描故意写错 JOIN 条件把ON s.student_id sc.student_id写成ON s.student_id sc.course_id执行第 32 题观察rows暴涨 100 倍故意关掉 ICPSET optimizer_switchindex_condition_pushdownoff;再执行第 48 题对比Extra字段从Using index condition变成Using where。这些“自虐式”操作能在 1 小时内让你深刻理解索引、JOIN、ICP 的底层协作机制。我带实习生时要求他们先用DROP INDEX把所有索引删光再逐题重跑记录每题耗时变化——这种痛感比看 10 篇博客都管用。5.3 把习题答案沉淀为团队 SQL 规范检查项我们团队的 SQL 审查清单SQL Review Checklist直接来自这份习题集的踩坑总结检查项对应习题违反后果自动化工具SELECT中非聚合字段必须在GROUP BY中第 25、39 题MySQL 报错PostgreSQL 直接拒绝SonarQube SQL 插件规则S3457LIKE模糊查询含_或%时必须声明ESCAPE第 11、28 题查询结果错误线上资损自研 SQL Linter正则匹配LIKE %[_%]%未跟ESCAPEUPDATE/DELETE必须带WHERE条件且字段有索引第 59、61 题全表锁、慢查询、误删数据DBA 巡检脚本扫描information_schema.PROCESSLIST中无 WHERE 的 DML事务中SELECT ... FOR UPDATE必须在UPDATE之前且WHERE精确到主键第 62 题死锁率升高 300%应用层 AOP 拦截检测SELECT FOR UPDATE后 5 秒内无UPDATE这份清单已集成到 CI 流程PR 提交时自动扫描 SQL 文件。新人入职第一周就是用习题集题目跑通这套检查流程——不是背规范是让规范长进肌肉里。我坚持用这份习题集带人是因为它不教你怎么“写 SQL”它教你怎么“想 SQL”。当你看到一个需求第一反应不是SELECT * FROM而是“这个查询会走哪些索引GROUP BY字段有没有索引ORDER BY会不会触发 filesort并发时会不会锁表”你就真正入门了。那些在会议室里争论“要不要加索引”的会议其实早在这份习题集的第 60 题里就有答案。希望帮到你。本文还有配套的精品资源点击获取