经典SQL查询问题的练习第二天

📝 第二天SQL打卡题目
表结构不变：  
- `student(studentId, studentName)`  
- `course(courseId, courseName, teacher)`  
- `score(score, studentId, courseId)`  

1. 查询未选修任何课程的学生姓名（考察：NOT EXISTS / LEFT JOIN + NULL） 
```sql
-- 方案1：NOT EXISTS
SELECT studentName 
FROM student s
WHERE NOT EXISTS (
    SELECT 1 FROM score sc WHERE sc.studentId = s.studentId
);

-- 方案2：LEFT JOIN + IS NULL
SELECT s.studentName
FROM student s
LEFT JOIN score sc ON s.studentId = sc.studentId
WHERE sc.courseId IS NULL;
```

2. 查询每门课程成绩最高的学生姓名及分数（考察：窗口函数 RANK() / 子查询） 
```sql
-- 窗口函数方案（推荐）
SELECT courseName, studentName, score
FROM (
    SELECT 
        c.courseName, 
        s.studentName, 
        sc.score,
        RANK() OVER (PARTITION BY sc.courseId ORDER BY sc.score DESC) AS rk
    FROM score sc
    JOIN student s ON sc.studentId = s.studentId
    JOIN course c ON sc.courseId = c.courseId
) t
WHERE t.rk = 1;
```

3. 查询所有课程平均分高于全体学生总平均分的课程名称（考察：聚合函数嵌套 + HAVING）
```sql
SELECT 
    c.courseName,
    AVG(sc.score) AS avg_score
FROM score sc
JOIN course c ON sc.courseId = c.courseId
GROUP BY c.courseId, c.courseName
HAVING AVG(sc.score) > (
    SELECT AVG(score) FROM score  -- 全体学生总平均分
);
```

4. 查询至少有一门课成绩超过90分的学生中，总成绩排名前5的学生（考察：子查询 + 复合条件排序）
```sql
SELECT 
    s.studentId,
    s.studentName,
    SUM(sc.score) AS total_score
FROM student s
JOIN score sc ON s.studentId = sc.studentId
WHERE s.studentId IN (
    SELECT DISTINCT studentId 
    FROM score 
    WHERE score > 90  -- 至少一门课>90分
)
GROUP BY s.studentId, s.studentName
ORDER BY total_score DESC
LIMIT 5;
```

5. 查询每个学生成绩最高的课程及对应教师（考察：关联子查询 + 多表JOIN）
```sql
SELECT 
    s.studentName,
    c.courseName,
    c.teacher,
    sc.score
FROM score sc
JOIN student s ON sc.studentId = s.studentId
JOIN course c ON sc.courseId = c.courseId
WHERE sc.score = (
    SELECT MAX(score) 
    FROM score 
    WHERE studentId = sc.studentId  -- 关联当前学生
);
```

6. 优化题：如何为`score`表设计索引以加速以下查询？（考察：索引设计原则）
```sql
-- 查询语句：
SELECT studentId, AVG(score) 
FROM score 
WHERE courseId = '0006'
GROUP BY studentId;
```
参考答案：  
```sql
CREATE INDEX idx_course_student ON score(courseId, studentId, score);
```
原理：  
- `WHERE courseId` 作为第一条件用等值查询  
- `GROUP BY studentId` 需有序访问，故作为第二列  
- 覆盖索引（包含`score`）避免回表查数据   

题目一解释：
找那些"啥课都没选"的学生。想象一个班级点名册（student表），我们要找出名字从来没出现在任何课程成绩单（score表）上的学生。

• 方法1：用"不存在"（NOT EXISTS）逻辑，相当于问"这个学生在成绩单上完全不存在记录吗？"

• 方法2：用"左连接+空值"（LEFT JOIN + IS NULL），相当于把点名册和成绩单并排摊开，看哪些学生右边成绩单位置是空的

题目二解释：
找出每门课的"状元"（最高分学生）。就像学校公布的光荣榜，每科都要展示第一名学生和分数。

• 关键技巧：用"窗口函数"（RANK()）给每门课的学生按分数排名（类似Excel的分组排序）

• 最后只取每科排名第1的学生

题目三解释：
找出"学霸课程"——这些课的平均分比全校总平均分还高。就像比较每个班级平均分和年级平均分。

• 先算全校总平均分（子查询）

• 再算每门课平均分（GROUP BY课程）

• 最后筛选出班级平均分 > 全校平均分的课程

题目四解释：
先找出"有单科90分以上"的学生，再在这些学霸中找"总分前5名"。

• 第一步：用子查询筛出至少有一门90+的学生（成绩单里挑出90+的学号）

• 第二步：计算这些学生的总分并排名

• 第三步：取前5名（LIMIT 5）

题目五解释：
给每个学生找出"最拿手的科目"（最高分的课程）和任课老师。

• 核心思路：对每个学生，找到他的最高分记录

• 实现方式：关联子查询（WHERE sc.score = 该学生最高分）

• 最后关联课程表获取老师信息

题目六解释：
给成绩单（score表）建"快速查询通道"（索引）。

• 问题：要快速查某个课程（如0006）所有学生的平均分

• 解决方案：建三层快速通道

  1. 第一层按课程ID分类（courseId）

  2. 第二层按学生ID排序（studentId）

  3. 第三层直接带分数值（score）避免翻原始成绩单

• 效果：像图书馆先按楼层→区域→书架找书，比全馆瞎找快10倍