多表查询及其注意事项

student表

score表

course表

操作

一.两表查询

1. 内连接：查询「有成绩的学生」的姓名和对应的分数

SELECT
s.name AS 学生姓名,
sc.score AS 分数
FROM student s
INNER JOIN score sc
ON s.student_id = sc.student_id;*

2. 左连接：查询「所有学生」的选课情况（包括没选课的学生）

SELECT
s.name AS 学生姓名,
sc.course_id AS 课程ID,
sc.score AS 分数
FROM student s
LEFT JOIN score sc
ON s.student_id = sc.student_id;

3. 右连接：查询「所有成绩记录」对应的学生信息（包括无效成绩）

SELECT
s.name AS 学生姓名,
sc.course_id AS 课程ID,
sc.score AS 分数
FROM student s
RIGHT JOIN score sc
ON s.student_id = sc.student_id;

二.三表查询

1. 内连接：查询「有成绩的学生」的姓名、课程名和分数
   **
   SELECT
   s.name AS 学生姓名,
   c.course_name AS 课程名称,
   sc.score AS 分数
   FROM student s
   INNER JOIN score sc ON s.student_id = sc.student_id
   INNER JOIN course c ON sc.course_id = c.course_id;**

2. 左连接：查询「所有学生」的完整选课信息（包括没选课的）
   **
   SELECT
   s.name AS 学生姓名,
   c.course_name AS 课程名称,
   sc.score AS 分数
   FROM student s
   LEFT JOIN score sc ON s.student_id = sc.student_id
   LEFT JOIN course c ON sc.course_id = c.course_id;
   **

三.分组排序分页

1. 分组查询：查询「每门课程」的平均分、最高分

SELECT
c.course_name AS 课程名称,
AVG(sc.score) AS 平均分,
MAX(sc.score) AS 最高分
FROM course c
INNER JOIN score sc ON c.course_id = sc.course_id
GROUP BY c.course_name;

2. 排序 + 分页：查询「所有学生的成绩」，按分数降序，每页 2 条，查第 2 页

SELECT
s.name AS 学生姓名,
c.course_name AS 课程名称,
sc.score AS 分数
FROM student s
INNER JOIN score sc ON s.student_id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id
ORDER BY sc.score DESC
LIMIT 2, 2;

四.子查询

1. 标量子查询：查询「高等数学」课程的最高分

SELECT MAX(score) AS 高等数学最高分
FROM score
WHERE course_id = (
SELECT course_id
FROM course
WHERE course_name = '高等数学'
);

2. 列子查询：查询「选了高等数学或大学英语」的学生姓名
**
SELECT DISTINCT s.name AS 学生姓名
FROM student s
INNER JOIN score sc ON s.student_id = sc.student_id
WHERE sc.course_id IN (
SELECT course_id
FROM course
WHERE course_name IN ('高等数学', '大学英语')
);

补充说明：
1.一般三表以上不用右连接，不符合常人的阅读习惯
2.FROM后表的选择不是随便写，要按照业务逻辑来，（有些内连接的from和inner交换顺序后结果不变）