mysql高级查询练习题 10 道 给我解析

<think>好的，我现在需要帮用户找10道MySQL高级查询的练习题，并且给出解析。首先，我得确定用户所说的“高级查询”具体指哪些内容。高级查询通常包括多表连接、子查询、聚合函数、窗口函数、分组筛选、联合查询等等。可能还涉及一些复杂的条件过滤和性能优化，但练习题的话，可能更侧重语法和逻辑的应用。 用户可能需要这些练习题来巩固他们的SQL技能，可能是学生或者正在自学数据库的人。他们可能已经掌握了基础的单表查询，现在想挑战更复杂的场景。所以，我需要设计涵盖不同高级概念的题目，同时确保题目有实际的应用场景，这样用户练习起来会更有意义。 接下来，我需要确定题目数量是10道，所以得合理分配不同的知识点。比如，可以包括内连接、外连接、子查询（嵌套查询）、EXISTS/NOT EXISTS、聚合函数与HAVING、CASE语句、窗口函数（如ROW_NUMBER、RANK）、联合查询（UNION）、分页查询（LIMIT/OFFSET）、以及多表更新或删除操作。 每个题目应该有一个具体的场景，比如销售数据、学生选课、员工部门等，这样用户更容易理解。然后，每个题目的解析需要详细说明使用的SQL语法，为什么这么写，以及可能的其他解法或注意事项。 比如，第一题可以使用学生表和成绩表，查询平均分高于85的学生，这就需要用到JOIN和GROUP BY，以及HAVING子句。解析的时候要解释每个部分的作用，比如JOIN连接两个表的条件，GROUP BY对学号分组，HAVING过滤聚合后的结果。 第二题可能需要找出没有选修任何课程的学生，这时候可以用LEFT JOIN和WHERE IS NULL，或者子查询中的NOT EXISTS。解析时要说明两种方法的区别，可能LEFT JOIN更直观，而NOT EXISTS在子查询效率上可能更好，但具体情况要看数据库的优化。 第三题可能涉及到分组排名，比如每个部门的工资排名，这时候可以使用窗口函数RANK() OVER，这在MySQL 8.0及以上版本支持。需要提醒用户注意版本差异，因为旧版本可能不支持窗口函数，可能需要使用变量来模拟。 第四题可能需要多表连接，比如订单、客户、产品表的联合查询，找出某个条件的客户。这时候要注意连接的顺序和条件，避免笛卡尔积。 第五题可以是使用CASE语句进行分类统计，比如不同分数段的人数。这时候要展示如何将CASE放在SELECT子句中，并结合GROUP BY进行分组。 第六题可能涉及分页查询，比如按时间排序后取第6-10条记录，这时候需要用LIMIT和OFFSET，并解释分页的原理及性能问题，比如大数据量下的优化方法。 第七题可能是联合查询，将两个不同的查询结果合并，比如UNION和UNION ALL的区别，去除重复或保留所有结果。 第八题可能使用子查询更新数据，比如根据某个条件更新表中的数据，这时候需要注意子查询的写法和更新时的条件限制，避免全表更新。 第九题可能涉及日期函数，比如查询最近一个月的数据，使用CURDATE()和INTERVAL，或者DATE_SUB函数，解析时要说明如何处理日期范围。 第十题可以是删除重复记录，保留最小或最大ID的情况，这时候可能需要使用自连接或者子查询来识别重复项，并执行删除操作，但要注意事务的使用，避免数据丢失。 在编写解析时，需要确保语言简洁明了，解释每个步骤的作用，并指出常见的错误或注意事项，比如在GROUP BY中使用非聚合字段，或者在JOIN时忘记连接条件导致的结果错误。同时，可能还需要对比不同的解决方法，分析它们的优缺点，帮助用户理解在不同情况下如何选择最佳方案。 另外，需要注意题目的难度递进，从较简单的多表连接到更复杂的窗口函数和子查询组合，逐步提升难度。每个题目之后给出答案，并且解析中说明使用的关键技术和思路，这样用户在做题后可以对照答案，理解自己是否掌握了相关知识点。 最后，检查所有题目是否符合MySQL的语法，特别是涉及窗口函数时，要确认用户使用的MySQL版本是否支持，如果不支持，可能需要提供替代方案。例如，使用变量来模拟ROW_NUMBER函数的功能，或者使用派生表来实现类似效果。</think>以下是 **10道MySQL高级查询练习题** 及详细解析，涵盖多表连接、子查询、窗口函数等核心知识点： --- ### **1. 查询平均分高于85的学生姓名和平均分** **表结构**： - 学生表 `students`（id, name） - 成绩表 `scores`（student_id, subject, score） **答案**： ```sql SELECT s.name, AVG(sc.score) AS avg_score FROM students s JOIN scores sc ON s.id = sc.student_id GROUP BY s.id HAVING avg_score > 85; ``` **解析**： - `JOIN` 连接学生表和成绩表，按学生分组后计算平均分。 - `HAVING` 过滤分组后数据（`WHERE` 不能用于聚合函数）。 --- ### **2. 查询没有选修任何课程的学生** **表结构**： - 学生表 `students`（id, name） - 选课表 `course_selections`（student_id, course_id） **答案**： ```sql SELECT s.name FROM students s LEFT JOIN course_selections cs ON s.id = cs.student_id WHERE cs.course_id IS NULL; ``` **解析**： - `LEFT JOIN` 会保留所有学生，未选课的学生在 `course_id` 字段为 `NULL`。 - 替代方案：使用 `NOT EXISTS` 子查询。 --- ### **3. 查询每个部门工资排名前三的员工** **表结构**： - 员工表 `employees`（id, name, salary, department_id） **答案（MySQL 8.0+）**： ```sql SELECT department_id, name, salary FROM ( SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rk FROM employees ) AS tmp WHERE rk <= 3; ``` **解析**： - 窗口函数 `RANK()` 按部门分区并排序工资。 - 旧版本需用变量模拟（如 `@rank`）。 --- ### **4. 查询购买了所有产品的客户** **表结构**： - 客户表 `customers`（id, name） - 订单表 `orders`（customer_id, product_id） **答案**： ```sql SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT product_id FROM products WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.product_id = products.product_id ) ); ``` **解析**： - 双重 `NOT EXISTS` 确保客户购买了所有产品（关系除法问题）。 --- ### **5. 更新订单金额，超过1000的打9折** **表结构**： - 订单表 `orders`（id, amount） **答案**： ```sql UPDATE orders SET amount = amount * 0.9 WHERE amount > 1000; ``` **解析**： - 直接使用 `UPDATE` + `WHERE` 条件筛选目标数据。 --- ### **6. 统计各分数段人数（如60以下、60-80、80+）** **表结构**： - 成绩表 `scores`（student_id, score） **答案**： ```sql SELECT CASE WHEN score < 60 THEN '不及格' WHEN score BETWEEN 60 AND 80 THEN '及格' ELSE '优秀' END AS level, COUNT(*) AS cnt FROM scores GROUP BY level; ``` **解析**： - `CASE` 语句实现数据分类，配合 `GROUP BY` 统计。 --- ### **7. 查询至少有两个下属的经理** **表结构**： - 员工表 `employees`（id, name, manager_id） **答案**： ```sql SELECT m.name FROM employees e JOIN employees m ON e.manager_id = m.id GROUP BY m.id HAVING COUNT(e.id) >= 2; ``` **解析**： - 自连接（同一表关联），统计每个经理的下属数量。 --- ### **8. 分页查询：按时间倒序取第6-10条记录** **表结构**： - 文章表 `articles`（id, title, publish_time） **答案**： ```sql SELECT id, title FROM articles ORDER BY publish_time DESC LIMIT 5 OFFSET 5; -- LIMIT 5,5（旧语法） ``` **解析**： - `OFFSET` 跳过前5条，`LIMIT` 取5条，实现分页。 --- ### **9. 删除重复邮箱（保留id最小的记录）** **表结构**： - 用户表 `users`（id, email） **答案**： ```sql DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email ); ``` **解析**： - 子查询找到每组邮箱的最小id，删除不在其中的记录。 --- ### **10. 查询连续3天登录的用户** **表结构**： - 登录表 `logins`（user_id, login_date） **答案**： ```sql SELECT DISTINCT a.user_id FROM logins a JOIN logins b ON a.user_id = b.user_id AND b.login_date = a.login_date + INTERVAL 1 DAY JOIN logins c ON a.user_id = c.user_id AND c.login_date = a.login_date + INTERVAL 2 DAY; ``` **解析**： - 自连接三次，通过日期差值判断连续性。 --- ### **总结** 以上题目覆盖了： - 多表连接（JOIN） - 子查询（EXISTS/NOT EXISTS） - 窗口函数（RANK） - 聚合与分组（GROUP BY + HAVING） - 数据更新与删除 - 分页与自连接技巧 练习时注意理解表关系，优先用 `EXPLAIN` 分析复杂查询的执行计划。