MZH's Blog

大题复习

4 min read

— 学生表
student(sid, sname, sex, age, dept)
— 课程表
course(cid, cname, credit)
— 选课表
sc(sid, cid, score)

样例数据（自己脑补，不影响写 SQL）：

  • student: (1,‘张三’,‘男’,20,‘计算机’), (2,‘李四’,‘女’,21,‘计算机’), (3,‘王五’,‘男’,19,‘数学’)

  • course: (1,‘数据库’,3), (2,‘操作系统’,2), (3,‘数据结构’,4)

  • sc: (1,1,85), (1,2,90), (2,1,78), (3,3,92)

题目 1：简单查询（WHERE + 排序）

查询计算机系所有女生的姓名和年龄，按年龄降序排列。

SELECT sname,age FROM student
WHERE sex = '女'
ORDER BY DESE
SELECT sname, age
FROM student
WHERE dept = '计算机' AND sex = '女'
ORDER BY age DESC;

忘了要写计算机系，忘了写 age 急着写 DESC 了

题目 2：多表连接（INNER JOIN）

查询所有选课学生的姓名、课程名和成绩。

SELECT student.sname,course.cname,sc.score
FROM sc
JOIN student ON sc.sid = student.sid
JOIN course ON course.cid = sc.cid
SELECT s.sname, c.cname, sc.score
FROM student s
INNER JOIN sc ON s.sid = sc.sid
INNER JOIN course c ON sc.cid = c.cid;

JOIN 就是默认为 INNER JOIN

表名空一格再打可以起别名

题目 3：分组统计（GROUP BY + 聚合函数）

查询每门课程的平均成绩，显示课程名和平均分，按平均分降序排列。

SELECT c.cname, AVG(sc.score)
FROM sc
JOIN course c ON c.cid = sc.cid
GROUP BY sc.sid
ORDER BY AVG(sc.score) DESC
SELECT c.cname, AVG(sc.score) AS avg_score
FROM course c
INNER JOIN sc ON c.cid = sc.cid
GROUP BY c.cid, c.cname
ORDER BY avg_score DESC;

写错了，应该要记得各字段的含义，把 cid 写成 sid 了

字段起别名用 AS

GROUP BY 后面要包含 SELECT 中所有非聚合字段（cid 和 cname），否则会报错

题目 4：子查询（IN / EXISTS）

查询选修了“数据库”课程的学生姓名。

SELECT s.sname
FROM student s 
WHERE s.sid IN (SELECT sc.sid WHERE sc.cid = 1 FROM sc)
-- 方法1：IN 子查询
SELECT sname
FROM student
WHERE sid IN (
    SELECT sid
    FROM sc
    WHERE cid = (SELECT cid FROM course WHERE cname = '数据库')
);
 
-- 方法2：多表连接（更简洁，推荐）
SELECT s.sname
FROM student s
INNER JOIN sc ON s.sid = sc.sid
INNER JOIN course c ON sc.cid = c.cid
WHERE c.cname = '数据库';
 
-- 方法3：EXISTS（效率高，适合大数据）
SELECT sname
FROM student s
WHERE EXISTS (
    SELECT 1
    FROM sc
    WHERE sc.sid = s.sid
    AND cid = (SELECT cid FROM course WHERE cname = '数据库')
);

题目 5：综合大题（连接 + 分组 + 子查询）

查询每个学生的姓名、选课门数、最高分，只显示选课门数≥2 的学生，按最高分降序排列。

SELECT s.sname, COUNT(sc.cid) AS max_score, MAX(sc.cid)
FROM student s
JOIN sc ON c.sid = sc.sid
GROUP BY s.sid, s.sname
ORDER BY max_score DESC
HAVING COUNT(sc.score) >= 2
SELECT s.sname, COUNT(sc.cid) AS course_count, MAX(sc.score) AS max_score
FROM student s
INNER JOIN sc ON s.sid = sc.sid
GROUP BY s.sid, s.sname
HAVING COUNT(sc.cid) >= 2
ORDER BY max_score DESC;

看清楚了，我把 max 的别名写给 count 了

MAX()内部写成 sc.cid 了，应该是 sc.score

JOIN 的 ON 写成 c.sid 了，应该是 s.sid，这种小东西要看清了，我写错好几次了

HAVING 要放在 GROUP BY 后面，ORDER BY 前面，因为先分组，筛选，全部选完了排序