mysql数据库的常见SQL面试题

作者

MySQL 常见 SQL 面试题完全指南

本文档整理了常见的 SQL 语句类面试题（重点在“会写 SQL”），从简单查询到多表 JOIN、分组统计、子查询等，特别适合新手准备面试或系统复习。每题都给出：

  • 题目
  • 解题思路
  • 示例答案 SQL

建议使用统一的示例表结构在本地 MySQL 中边看边写边跑，效果最好。

目录

  1. 示例表结构与数据准备
  2. 基础查询题（SELECT + WHERE + ORDER BY + LIMIT）
  3. 聚合与分组题（COUNT / SUM / AVG / GROUP BY / HAVING）
  4. 多表连接题（JOIN）
  5. 子查询题
  6. 字符串、日期函数题
  7. 更新与删除题（UPDATE / DELETE）
  8. 设计类 SQL 题（略偏综合）
  9. 练习建议与常犯错误

1. 示例表结构与数据准备

以下示例题默认使用三张表：学生表 students、课程表 courses、选课/成绩表 scores。你可以在自己的 school 数据库中执行下面建表与插入语句。

CREATE TABLE IF NOT EXISTS students (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(50) NOT NULL,
    gender      CHAR(1) DEFAULT '男',
    age         INT,
    class_name  VARCHAR(20)
);

INSERT INTO students (name, gender, age, class_name) VALUES
('张三', '男', 18, '高一1班'),
('李四', '女', 17, '高一2班'),
('王五', '男', 18, '高一1班'),
('赵六', '女', 17, '高一2班'),
('钱七', '男', 19, '高一1班'),
('孙八', '女', 18, '高一2班');

CREATE TABLE IF NOT EXISTS courses (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(50) NOT NULL
);

INSERT INTO courses (name) VALUES
('语文'),
('数学'),
('英语');

CREATE TABLE IF NOT EXISTS scores (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    student_id  INT NOT NULL,
    course_id   INT NOT NULL,
    score       DECIMAL(5,2),
    exam_date   DATE,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

INSERT INTO scores (student_id, course_id, score, exam_date) VALUES
(1, 1, 85, '2025-01-10'),
(1, 2, 92, '2025-01-11'),
(1, 3, 78, '2025-01-12'),
(2, 1, 88, '2025-01-10'),
(2, 2, 76, '2025-01-11'),
(2, 3, 90, '2025-01-12'),
(3, 1, 72, '2025-01-10'),
(3, 2, 85, '2025-01-11'),
(3, 3, 88, '2025-01-12'),
(4, 1, 90, '2025-01-10'),
(4, 2, 82, '2025-01-11'),
(5, 1, 95, '2025-01-10'),
(5, 2, 88, '2025-01-11'),
(6, 1, 80, '2025-01-10'),
(6, 2, NULL, '2025-01-11');  -- 缺考

后续题目若无特别说明，均基于上述三张表。

2. 基础查询题（SELECT + WHERE + ORDER BY + LIMIT）

2.1 查询所有学生的姓名和班级

题目： 写 SQL 查询所有学生的 name 和 class_name。

SELECT name, class_name
FROM students;

2.2 查询高一1班的学生信息，按年龄从大到小排序

题目： 查询 class_name = '高一1班' 的学生，显示姓名、年龄、班级，按年龄从大到小排序。

SELECT name, age, class_name
FROM students
WHERE class_name = '高一1班'
ORDER BY age DESC;

2.3 查询年龄在 18 岁及以上的学生姓名和年龄

题目： 查询 age >= 18 的学生。

SELECT name, age
FROM students
WHERE age >= 18;

可以顺带写一条带 BETWEEN 的变形题：

SELECT name, age
FROM students
WHERE age BETWEEN 18 AND 20;

2.4 查询名字中包含“张”的学生（模糊匹配）

题目： 查询姓名中包含“张”的学生。

SELECT *
FROM students
WHERE name LIKE '%张%';

如只要“姓张”（以张开头）：

SELECT *
FROM students
WHERE name LIKE '张%';

2.5 查出年龄最大的 3 位学生

题目： 查询年龄最大排名前 3 的学生姓名和年龄。

SELECT name, age
FROM students
ORDER BY age DESC, id ASC
LIMIT 3;

说明：当年龄相同时，按 id 升序保证结果稳定。

3. 聚合与分组题（COUNT / SUM / AVG / GROUP BY / HAVING）

3.1 统计学生总人数

题目： 查询学生总人数。

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

3.2 统计每个班级的学生人数

题目： 按班级统计人数，显示“班级名”和“人数”。

SELECT class_name AS 班级, COUNT(*) AS 人数
FROM students
GROUP BY class_name;

3.3 查询人数大于 2 的班级

题目： 在 3.2 的基础上，只显示人数 > 2 的班级。

SELECT class_name AS 班级, COUNT(*) AS 人数
FROM students
GROUP BY class_name
HAVING COUNT(*) > 2;

考点：HAVING 在分组之后过滤“组”，WHERE 在分组之前过滤“行”。

3.4 求每位学生的总分和平均分

题目： 基于 scores 表，按 student_id 分组，求总分（SUM）、平均分（AVG）。

SELECT student_id AS 学号,
       SUM(score) AS 总分,
       AVG(score) AS 平均分
FROM scores
GROUP BY student_id;

若要带姓名，可与 students 连接（详见 JOIN 部分）：

SELECT s.name AS 姓名,
       SUM(sc.score) AS 总分,
       AVG(sc.score) AS 平均分
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name;

3.5 查询平均分大于 85 分的学生

题目： 找出平均分 > 85 的学生（显示学号、平均分）。

SELECT student_id AS 学号,
       AVG(score) AS 平均分
FROM scores
GROUP BY student_id
HAVING AVG(score) > 85;

3.6 统计每门课程的最高分、最低分、平均分

题目： 以 course_id 为分组依据，统计每门课的最高分、最低分、平均分。

SELECT course_id AS 课程ID,
       MAX(score) AS 最高分,
       MIN(score) AS 最低分,
       AVG(score) AS 平均分
FROM scores
GROUP BY course_id;

若想带课程名：

SELECT c.name AS 课程,
       MAX(sc.score) AS 最高分,
       MIN(sc.score) AS 最低分,
       AVG(sc.score) AS 平均分
FROM courses c
JOIN scores sc ON c.id = sc.course_id
GROUP BY c.id, c.name;

4. 多表连接题（JOIN）

4.1 查询每位学生的“姓名 + 班级 + 语文成绩”

题目： 使用 JOIN，把 students、courses、scores 关联起来，只显示语文成绩。

思路：

  • students.id ↔ scores.student_id
  • courses.id ↔ scores.course_id
  • 只要 courses.name = ‘语文’
SELECT s.name AS 姓名,
       s.class_name AS 班级,
       sc.score AS 语文成绩
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.name = '语文';

4.2 查询每位学生的“姓名 + 每门课的成绩”

题目： 查询学生姓名、科目名、成绩。

SELECT s.name AS 姓名,
       c.name AS 科目,
       sc.score AS 分数
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
ORDER BY s.id, c.id;

4.3 查询“至少选修了两门课”的学生姓名

题目： 在 scores 中统计每个 student_id 的课程数 ≥ 2，再和 students 关联出姓名。

写法一：子查询 + IN

SELECT name
FROM students
WHERE id IN (
    SELECT student_id
    FROM scores
    GROUP BY student_id
    HAVING COUNT(DISTINCT course_id) >= 2
);

写法二：JOIN 子查询

SELECT s.name
FROM students s
JOIN (
    SELECT student_id
    FROM scores
    GROUP BY student_id
    HAVING COUNT(DISTINCT course_id) >= 2
) t ON s.id = t.student_id;

4.4 查出所有“没有选课记录”的学生

题目： 使用 LEFT JOIN 找出 students 中在 scores 里没有记录的学生。

SELECT s.*
FROM students s
LEFT JOIN scores sc ON s.id = sc.student_id
WHERE sc.id IS NULL;

考点：左连接 + WHERE 右表主键 IS NULL 找“左有右无”。

4.5 查询“每个班级的学生人数 + 班级平均总分”

题目： 需要统计每个学生总分，再按班级聚合。

步骤：

  1. 先算每个学生的总分。
  2. 再按班级分组统计人数和总分平均值。
-- 步骤 1：每个学生总分
SELECT s.id, s.class_name, SUM(sc.score) AS 总分
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.class_name;

在此基础上嵌套：

SELECT class_name AS 班级,
       COUNT(*) AS 学生数,
       AVG(总分) AS 班级平均总分
FROM (
    SELECT s.id, s.class_name, SUM(sc.score) AS 总分
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    GROUP BY s.id, s.class_name
) t
GROUP BY class_name;

考点：多表 + 分组 + 派生表（FROM 子查询）。

5. 子查询题

5.1 查询“年龄大于所有学生平均年龄”的学生

题目： 使用子查询得到平均年龄，然后比较。

SELECT *
FROM students
WHERE age > (SELECT AVG(age) FROM students);

5.2 查询“语文成绩高于本班语文平均分”的学生

题目： 需要先按班级统计语文平均分，再比较每个学生的语文成绩。

写法一：JOIN 子查询

先算各班语文平均分：

SELECT s.class_name, AVG(sc.score) AS 班级语文平均分
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.name = '语文'
GROUP BY s.class_name;

外层 JOIN：

SELECT s.name, s.class_name, sc.score AS 语文成绩
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
JOIN (
    SELECT s.class_name, AVG(sc.score) AS avg_score
    FROM students s
    JOIN scores sc ON s.id = sc.student_id
    JOIN courses c ON sc.course_id = c.id
    WHERE c.name = '语文'
    GROUP BY s.class_name
) t ON s.class_name = t.class_name
WHERE c.name = '语文'
  AND sc.score > t.avg_score;

写法二（相关子查询，思路清晰）：

SELECT s.name, s.class_name, sc.score AS 语文成绩
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.name = '语文'
  AND sc.score > (
      SELECT AVG(sc2.score)
      FROM students s2
      JOIN scores sc2 ON s2.id = sc2.student_id
      JOIN courses c2 ON sc2.course_id = c2.id
      WHERE c2.name = '语文'
        AND s2.class_name = s.class_name
  );

5.3 查询“没有数学成绩记录”的学生（NOT EXISTS）

SELECT s.*
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM scores sc
    JOIN courses c ON sc.course_id = c.id
    WHERE sc.student_id = s.id
      AND c.name = '数学'
);

考点：NOT EXISTS 替代 NOT IN，避免 NULL 问题。

6. 字符串、日期函数题

6.1 将学生姓名显示为“姓+同学”，例如“张同学”

题目： 姓取姓名第一个字符。

SELECT CONCAT(SUBSTRING(name, 1, 1), '同学') AS 显示名
FROM students;

6.2 查询“本月参加考试的记录”

假设 exam_date 为考试日期。

写法一：用 YEAR 和 MONTH

SELECT *
FROM scores
WHERE YEAR(exam_date) = YEAR(CURDATE())
  AND MONTH(exam_date) = MONTH(CURDATE());

写法二：用日期范围

SELECT *
FROM scores
WHERE exam_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
  AND exam_date < DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 MONTH);

6.3 统计“每位学生与 2000-01-01 相差的天数”

SELECT name,
       DATEDIFF(CURDATE(), '2000-01-01') AS 与基准相差天数
FROM students;

更常见场景是 DATEDIFF(CURDATE(), birth_date)，这里示意用固定日期。

7. 更新与删除题（UPDATE / DELETE）

7.1 将所有“高一2班”的学生班级改为“高一3班”

UPDATE students
SET class_name = '高一3班'
WHERE class_name = '高一2班';

7.2 将所有“缺考（score 为 NULL）”的数学成绩改为 0

UPDATE scores sc
JOIN courses c ON sc.course_id = c.id
SET sc.score = 0
WHERE c.name = '数学'
  AND sc.score IS NULL;

7.3 删除所有“总分低于 120 分”的学生成绩记录（只删成绩，不删学生）

思路：

  1. 先找出总分 < 120 的学生 id；
  2. 再删这些学生在 scores 中的记录。
DELETE FROM scores
WHERE student_id IN (
    SELECT student_id
    FROM scores
    GROUP BY student_id
    HAVING SUM(score) < 120
);

注意：InnoDB 下，MySQL 不允许在同一个表上既作为 DELETE 目标又在子查询中被读（严格模式下会报错）。实际中通常改写成 JOIN 删除或用中间表：

DELETE sc
FROM scores sc
JOIN (
    SELECT student_id
    FROM scores
    GROUP BY student_id
    HAVING SUM(score) < 120
) t ON sc.student_id = t.student_id;

8. 设计类 SQL 题（略偏综合）

8.1 查询“每位学生第 N 高的成绩”（TOP N）

最常问的是“第二高分”，这里示意“第二高的成绩”，不区分科目。

写法一：相关子查询计数

SELECT s.name, sc.score AS 第二高分
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE (
    SELECT COUNT(DISTINCT sc2.score)
    FROM scores sc2
    WHERE sc2.student_id = s.id
      AND sc2.score > sc.score
) = 1;

解释：对每个学生 s 和某个分数 sc.score，数一数“比这个分高的不同分数有多少种”，等于 1 时，这个分就是第二高。

（更复杂的 TOP N 一般用窗口函数 ROW_NUMBER() / DENSE_RANK()，但 MySQL 8 之前需要子查询或变量模拟，面试时讲思路即可。）

8.2 查询“选修了所有课程的学生”

思路：

  • 先统计总共有多少门课程；
  • 对每个学生统计他选了多少不同课程；
  • 若两者相等，则表示选修了所有课程。
SELECT s.name
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name
HAVING COUNT(DISTINCT sc.course_id) = (
    SELECT COUNT(*) FROM courses
);

9. 练习建议与常犯错误

9.1 练习建议

  • 按模块刷题：
    • 先熟练写单表查询（SELECT + WHERE + ORDER BY + LIMIT）。
    • 再练聚合与分组（GROUP BY + HAVING）。
    • 然后练两表、三表 JOIN（含 LEFT JOIN）。
    • 最后练子查询、嵌套、派生表。
  • 每写完一条 SQL，都在本机 MySQL 上实际跑一遍，看结果对不对；
  • 尝试用两种写法实现同一需求，如：
    • JOIN 写法 vs 子查询写法；
    • WHERE + GROUP BY vs FROM 子查询再 GROUP BY。
访问数: 13

发表评论