4.多表查询

1. 业务需求

学校希望统计每门课程的平均成绩，显示结果包含:

课程编号
课程名称
平均成绩

2.SQL思路

需要Enrollments表获取成绩(score)
需要Courses表获取课程名称。
将两张表通过课程编号course_id连接起来
用聚合函数AVG()计算平均成绩
按课程分组GROUP BY

3.SQL查询示例

SELECT
    c.course_id,
    c.course_name,
    AVG(e.score) AS average_score
FROM
    Courses c
JOIN
    Enrollments e
ON
    c.course_id = e.course_id
GROUP BY
    c.course_id,c.course.name;

4.查询结果示例

course_id	course_name	average_score
2001	数学	81.75
2002	语文	90.0
2003	英语	77.5
2004	物理	92.0

5.拓展练习

过滤条件:只显示平均分低于80的课程
1
HAVING AVG(e.score) < 80
排序:按平均成绩从高到低排序
1
ORDER BY average_score DESC
练习变化:尝试显示每个班级每门课的平均成绩(需要Students表+JOIN+GROUP BY多列)

5.筛选不及格学生(多表条件查询)

1.业务查询

学校要查出所有成绩低于60分的不及格学生，并提示以下信息:

学生姓名
所属班级
课程名称
成绩
2.思路分析
我们要从三张表取数据:
Students->学生信息
Courses->课程信息

Enrollments->成绩
关系如下:

1 2	Students.student_id = Enrollments.student_id=Enrollments.course_id

筛选条件

1	WHERE e.score < 60

3.SQL查询示例

1.修改赵六的成绩

UPDATE Enrollments
SET score =56.5
WHERE student_id = (SELECT student_id FROM Students WHERE name = `赵六`)
AND course_id = (SELECT course_id FROM Courses WHERE course_name = `英语`);

2.SQL查询

SELECT
    s.name AS student_name,
    s.class AS student_class,
    c.course_name,
    e score
FROM
    Enrollments e
JOIN
    Students s ON e.student_id = s.student_id
JOIN
    Courses c ON e.course_id = c.course_id
WHERE
    e.score < 60;

4.查询结果示例

student_name	student_class	course_name	score
赵六	二班	英语	56.5

5.扩展练习

练习一:统计每个班的不及格人数

SELECT
    s.class,
    COUNT(*) AS fail_count
FROM
  Enrollments e
JOIN
  Students s ON e.student_id = s.student_id
WHERE
  e.score < 60
GROUP BY
s.class;
1
2
3
4
5
6
7
8
9
10
11
* 练习二:显示每门课的不及格率
* ``` mysql
  SELECT
      c.course_name,
      ROUND(SUM(CASE WHERE e.score < 60 THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS fail_rate
  FROM
      Enrollments e
  JOIN
      Courses c ON e.course_id = c.course_id
  GROUP BY
          c.course_name;

  结果示例:

course_name	fail_rate
英语	25.00
数学	0.00