基础查询

1. 查询所有男学生，显示学号、姓名、性别、生日，按生日降序排列

SELECT s_id,s_name,gender,birth
FROM student
WHERE gender = '男'
ORDER BY birth DESC

2. 查询名字带伟的学生，输出学号、姓名

SELECT s_id,s_name
FROM student
WHERE s_name like '%伟%'

3. 查询分数在 60‑80 之间的选课记录，显示学生 id、课程 id、分数

SELECT s_id,c_id,score
FROM sc
WHERE score BETWEEN 60 AND 80

4. 查询课程表，只显示前 10 条课程，按学分从大到小排序

SELECT * 
FROM course 
ORDER BY credit DESC LIMIT 10

5. 查询生日在2002‑05‑01之后的学生

SELECT * 
FROM student 
WHERE birth>'2002-05-01';

多表连接

1. 查询所有学生姓名、班级名称、所属学院名称（学生即使没有选课也要查出来）

SELECT s.s_name,c.class_name,d.dept_name
FROM student s
JOIN class c ON s.class_id=c.class_id
JOIN department d ON c.dept_id=d.dept_id;

2. 查询 MySQL数据库 这门课，所有选课学生姓名、分数、授课老师名字

SELECT s.s_name,sc.score,t.t_name
FROM sc
JOIN student s ON sc.s_id=s.s_id
JOIN course co ON sc.c_id=co.c_id
JOIN teacher t ON co.t_id=t.t_id
WHERE co.c_name='MySQL数据库';

3. 查询计算机学院全部班级的学生名单：学生姓名、班级名

SELECT s.s_name,c.class_name
FROM student s
JOIN class c ON s.class_id=c.class_id
JOIN department d ON d.dept_id=c.dept_id
WHERE d.dept_name="计算机学院";

4. 查询有成绩的学生：学生姓名、课程名、分数、教师姓名

SELECT s.s_name,co.c_name,sc.score,t.t_name
FROM sc
JOIN student s ON sc.s_id=s.s_id
JOIN course co ON sc.c_id=co.c_id
JOIN teacher t ON co.t_id=t.t_id;

5. 左连接练习：查询哪些学生没有任何选课记录（sc 表无数据）

SELECT s.*
FROM student s
LEFT JOIN sc ON s.s_id=sc.s_id
WHERE sc.s_id IS NULL;

- class（<u>class_id</u>，class_name，<u>dept_id</u>）

- course（<u>c_id</u>，c_name，<u>t_id</u>，credit）

- department（<u>dept_id</u>，dept_name）

- sc（<u>s_id</u>，<u>c_id</u>，score）

- student（<u>s_id</u>，s_name，gender，birth，<u>class_id</u>）

- teacher（<u>t_id</u>，t_name，dept_id）

​

- 班级表（<u>班级编号</u>，班级名称，<u>学院编号</u>）
- 课程表（<u>课程编号</u>，课程名称，<u>教师编号</u>，学分）
- 学院表（<u>学院编号</u>，学院名称）
- 选课成绩表（<u>学号</u>，<u>课程编号</u>，课程分数）
- 学生表（<u>学号</u>，姓名，性别，出生日期，<u>班级编号</u>）

- 教师表（<u>教师编号</u>，教师姓名，<u>学院编号</u>）

​

分组聚合

where：过滤原始数据；having：过滤分组后的结果

1. 统计每个学院的学生人数，输出学院名、人数，人数降序

SELECT d.dept_name,COUNT(s.s_id) AS stu_count
FROM department d
JOIN class c ON d.dept_id=c.dept_id
JOIN student s ON c.class_id=s.class_id
GROUP BY d.dept_name
ORDER BY stu_count DESC;

2. 统计每门课：课程名称、选课人数、课程平均分、最高分、最低分；只筛选选课人数大于 10 的课程

SELECT co.c_name,COUNT(sc.s_id) AS cnt,AVG(sc.score) avg_score,MAX(sc.score) max_score,MIN(sc.score) min_score
FROM sc
JOIN course co ON sc.c_id=co.c_id
GROUP BY co.c_id,co.c_name
HAVING COUNT(sc.s_id)>10;

3. 统计每个学生的选课门数、平均分；只显示平均分大于 75 的学生，按平均分降序

SELECT s.s_name,COUNT(sc.c_id) course_cnt,AVG(sc.score) avg_score
FROM student s
LEFT JOIN sc ON s.s_id=sc.s_id
GROUP BY s.s_id,s.s_name
HAVING AVG(sc.score)>75
ORDER BY avg_score DESC;

4. 统计每个老师教授课程的总学分，输出教师姓名、总学分

SELECT t.t_name,SUM(co.credit) total_credit
FROM teacher t
JOIN course co ON t.t_id=co.t_id
GROUP BY t.t_id,t.t_name;

5. 统计男生、女生分别的人数

SELECT gender,COUNT(*) num 
FROM student 
GROUP BY gender;

四、子查询

1. 查询选过MySQL数据库课程的所有学生姓名（用IN子查询）

SELECT s_name FROM student
WHERE s_id IN (
    SELECT s_id FROM sc
    JOIN course co ON sc.c_id=co.c_id
    WHERE co.c_name='MySQL数据库'
);

2. 查询分数高于全体学生平均分的选课记录

SELECT * FROM sc
WHERE score > (SELECT AVG(score) FROM sc);

3. 查询没有选修高等数学的学生姓名（NOT IN）

SELECT s_name FROM student
WHERE s_id NOT IN (
    SELECT s_id FROM sc
    JOIN course co ON sc.c_id=co.c_id
    WHERE co.c_name='高等数学'
);

4. 使用EXISTS改写第 16 题

SELECT s_name FROM student s
WHERE EXISTS (
    SELECT 1 FROM sc
    JOIN course co ON sc.c_id=co.c_id
    WHERE sc.s_id=s.s_id AND co.c_name='MySQL数据库'
);

5. 查询平均分最高的学生姓名和平均分（标量子查询）

SELECT s_name,avg_score FROM (
    SELECT s.s_name,AVG(sc.score) avg_score
    FROM student s JOIN sc ON s.s_id=sc.s_id
    GROUP BY s.s_id,s.s_name
) t
WHERE t.avg_score=(
    SELECT MAX(temp.avg_score)
    FROM (SELECT AVG(score) avg_score FROM sc GROUP BY s_id) temp
);

五、窗口函数

⚠️MySQL5.7 没有窗口函数，会报错

1. 每门课程内部，给学生成绩排名，显示：课程名，学生姓名，分数，RANK()排名

SELECT co.c_name,s.s_name,sc.score,
RANK() OVER(PARTITION BY co.c_id ORDER BY sc.score DESC) AS rk
FROM sc
JOIN student s ON sc.s_id=s.s_id
JOIN course co ON sc.c_id=co.c_id;

2. 获取每门课成绩前 3 名的学生（允许并列）

SELECT * FROM (
    SELECT co.c_name,s.s_name,sc.score,
    RANK() OVER(PARTITION BY co.c_id ORDER BY sc.score DESC) rk
    FROM sc
    JOIN student s ON sc.s_id=s.s_id
    JOIN course co ON sc.c_id=co.c_id
) t WHERE t.rk <=3;

3. 对全部学生按平均分做排名，输出学生名，平均分，排名

SELECT s_name,avg_score,
RANK() OVER(ORDER BY avg_score DESC) rk
FROM (
    SELECT s.s_name,AVG(sc.score) avg_score
    FROM student s JOIN sc ON s.s_id=sc.s_id
    GROUP BY s.s_id,s.s_name
) t;

贡献者: zhaohui

上一页引言

下一页安装