mysql练习-洪萨配资

数据表介绍

1.学⽣表 Student(SId,Sname,Sage,Ssex)
- SId 学⽣编号
- Sname 学⽣姓名
- Sage 出⽣年⽉
- Ssex 学⽣性别
2.课程表 Course(CId,Cname,TId)
- CId 课程编号
- Cname 课程名称
- TId 教师编号
3.教师表 Teacher(TId,Tname)
- TId 教师编号
- Tname 教师姓名
4.成绩表 SC(SId,CId,score)
- SId 学⽣编号
- CId 课程编号
- score 分数

建表语句

学⽣表 Student

create table Student( SId varchar(10), Sname varchar(10), Sage datetime, Ssex varchar(10) );

课程表 Course

create table Course( CId varchar(10), Cname nvarchar(10), TId varchar(10) );

教师表 Teacher

create table Teacher( TId varchar(10), Tname varchar(10) );

成绩表 SC

create table SC( SId varchar(10), CId varchar(10), score decimal(18,1) );

插入数据

学⽣表 Student

-- 学生表 Student insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙⻛' , '1990-12-20' , '男'); insert into Student values('04' , '李云' , '1990-12-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '⼥'); insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥'); insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥'); insert into Student values('09' , '张三' , '2017-12-20' , '⼥'); insert into Student values('10' , '李四' , '2017-12-25' , '⼥'); insert into Student values('11' , '李四' , '2012-06-06' , '⼥'); insert into Student values('12' , '赵六' , '2013-06-13' , '⼥'); insert into Student values('13' , '孙七' , '2014-06-01' , '⼥');

课程表 Course

-- 科⽬表 Course insert into Course values('01' , '语⽂' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03');

教师表 Teacher

-- 教师表 Teacher insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五');

成绩表 SC

-- 成绩表 SC insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);

1. 查询 "01" 课程比 "02" 课程成绩高的学生的信息及课程分数

sql

-- 步骤：先分别查询01和02课程成绩，再关联比较 SELECT s.*, sc1.score AS '01课程分数', sc2.score AS '02课程分数' FROM Student s JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01' JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02' WHERE sc1.score > sc2.score;

2. 查询同时存在 "01" 课程和 "02" 课程的情况

sql

-- 方法1：关联查询 SELECT s.SId, s.Sname, sc1.score AS '01课程', sc2.score AS '02课程' FROM Student s JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01' JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'; -- 方法2：分组筛选 SELECT SId FROM SC WHERE CId IN ('01', '02') GROUP BY SId HAVING COUNT(DISTINCT CId) = 2;

3. 查询存在 "01" 课程但可能不存在 "02" 课程的情况（不存在时显示为 null）

sql

SELECT s.SId, s.Sname, sc1.score AS '01课程', sc2.score AS '02课程' FROM Student s JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01' LEFT JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02';

4. 查询不存在 "01" 课程但存在 "02" 课程的情况

sql

SELECT s.*, sc2.score AS '02课程分数' FROM Student s JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02' WHERE s.SId NOT IN (SELECT SId FROM SC WHERE CId = '01');

5. 查询平均成绩大于等于 60 分的同学的学生编号、姓名和平均成绩

sql

SELECT s.SId, s.Sname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname HAVING AVG(sc.score) >= 60;

6. 查询在 SC 表存在成绩的学生信息

sql

-- 方法1：DISTINCT去重 SELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId = sc.SId; -- 方法2：IN子查询 SELECT * FROM Student WHERE SId IN (SELECT DISTINCT SId FROM SC);

7. 查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩（没成绩显示为 null）

sql

SELECT s.SId, s.Sname, COUNT(sc.CId) AS 选课总数, SUM(sc.score) AS 总成绩 FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname;

8. 查询「李」姓老师的数量

sql

SELECT COUNT(*) AS 李姓老师数量 FROM Teacher WHERE Tname LIKE '李%';

9. 查询学过「张三」老师授课的同学的信息

sql

-- 步骤：先找到张三老师的课程，再关联成绩表和学生表 SELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId = sc.SId JOIN Course c ON sc.CId = c.CId JOIN Teacher t ON c.TId = t.TId WHERE t.Tname = '张三';

10. 查询没有学全所有课程的同学的信息

sql

-- 步骤：先统计总课程数，再筛选选课数不足的学生 SELECT s.* FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) < (SELECT COUNT(*) FROM Course);

11. 查询至少有一门课与学号为 "01" 的同学所学相同的同学的信息

sql

SELECT DISTINCT s.* FROM Student s JOIN SC sc ON s.SId = sc.SId WHERE sc.CId IN (SELECT CId FROM SC WHERE SId = '01') AND s.SId != '01'; -- 排除01号学生自身

12. 查询和 "01" 号同学学习的课程完全相同的其他同学的信息

sql

-- 步骤：1. 获取01号课程列表 2. 筛选选课数相同且课程完全匹配的学生 SELECT s.* FROM Student s JOIN SC sc ON s.SId = sc.SId WHERE s.SId != '01' GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) = (SELECT COUNT(*) FROM SC WHERE SId = '01') AND GROUP_CONCAT(sc.CId ORDER BY sc.CId) = (SELECT GROUP_CONCAT(CId ORDER BY CId) FROM SC WHERE SId = '01');

13. 查询没学过 "张三" 老师讲授的任一课程的学生姓名

sql

-- 步骤：先找到张三老师的课程，再筛选未选这些课程的学生 SELECT s.Sname FROM Student s WHERE s.SId NOT IN ( SELECT DISTINCT sc.SId FROM SC sc JOIN Course c ON sc.CId = c.CId JOIN Teacher t ON c.TId = t.TId WHERE t.Tname = '张三' );

14. 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩

sql

SELECT s.SId, s.Sname, ROUND(AVG(sc_all.score), 1) AS 平均成绩 FROM Student s JOIN SC sc_fail ON s.SId = sc_fail.SId AND sc_fail.score < 60 JOIN SC sc_all ON s.SId = sc_all.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc_fail.CId) >= 2;

15. 检索 "01" 课程分数小于 60，按分数降序排列的学生信息

sql

SELECT s.*, sc.score FROM Student s JOIN SC sc ON s.SId = sc.SId WHERE sc.CId = '01' AND sc.score < 60 ORDER BY sc.score DESC;

16. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

sql

-- 步骤：先计算每个学生的平均成绩，再关联所有课程成绩 SELECT s.SId, s.Sname, MAX(CASE WHEN sc.CId = '01' THEN sc.score ELSE NULL END) AS 语文, MAX(CASE WHEN sc.CId = '02' THEN sc.score ELSE NULL END) AS 数学, MAX(CASE WHEN sc.CId = '03' THEN sc.score ELSE NULL END) AS 英语, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname ORDER BY 平均成绩 DESC;

17. 查询各科成绩最高分、最低分和平均分（含及格率 / 中等率 / 优良率 / 优秀率）

sql

SELECT c.CId, c.Cname, COUNT(sc.SId) AS 选修人数, MAX(sc.score) AS 最高分, MIN(sc.score) AS 最低分, ROUND(AVG(sc.score), 1) AS 平均分, -- 及格率（>=60） ROUND(SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 及格率, -- 中等率（70-80） ROUND(SUM(CASE WHEN sc.score BETWEEN 70 AND 80 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 中等率, -- 优良率（80-90） ROUND(SUM(CASE WHEN sc.score BETWEEN 80 AND 90 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 优良率, -- 优秀率（>=90） ROUND(SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS 优秀率 FROM Course c JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname ORDER BY 选修人数 DESC, c.CId ASC;

18. 按各科平均成绩排序并显示排名（Score 重复时保留名次空缺）

sql

-- 使用RANK()函数（保留空缺） SELECT CId, 平均成绩, RANK() OVER (ORDER BY 平均成绩 DESC) AS 排名 FROM ( SELECT CId, ROUND(AVG(score), 1) AS 平均成绩 FROM SC GROUP BY CId ) AS t;

19. 按各科平均成绩排序并显示排名（Score 重复时不保留名次空缺）

sql

-- 使用DENSE_RANK()函数（不保留空缺） SELECT CId, 平均成绩, DENSE_RANK() OVER (ORDER BY 平均成绩 DESC) AS 排名 FROM ( SELECT CId, ROUND(AVG(score), 1) AS 平均成绩 FROM SC GROUP BY CId ) AS t;

20. 查询学生的总成绩并排名（总分重复保留名次空缺）

sql

SELECT s.SId, s.Sname, IFNULL(SUM(sc.score), 0) AS 总成绩, RANK() OVER (ORDER BY IFNULL(SUM(sc.score), 0) DESC) AS 排名 FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname;

21. 查询学生的总成绩并排名（总分重复不保留名次空缺）

sql

SELECT s.SId, s.Sname, IFNULL(SUM(sc.score), 0) AS 总成绩, DENSE_RANK() OVER (ORDER BY IFNULL(SUM(sc.score), 0) DESC) AS 排名 FROM Student s LEFT JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname;

22. 统计各科成绩各分数段人数及百分比

sql

SELECT c.CId, c.Cname, -- [100-85] SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '100-85人数', ROUND(SUM(CASE WHEN sc.score BETWEEN 85 AND 100 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS '100-85百分比', -- [85-70] SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '85-70人数', ROUND(SUM(CASE WHEN sc.score BETWEEN 70 AND 85 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS '85-70百分比', -- [70-60] SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '70-60人数', ROUND(SUM(CASE WHEN sc.score BETWEEN 60 AND 70 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS '70-60百分比', -- [60-0] SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS '60-0人数', ROUND(SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END)/COUNT(sc.SId)*100, 1) AS '60-0百分比' FROM Course c JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname;

23. 查询各科成绩前三名的记录

sql

-- 方法：使用窗口函数ROW_NUMBER() SELECT * FROM ( SELECT sc.SId, sc.CId, sc.score, ROW_NUMBER() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS 排名 FROM SC sc ) AS t WHERE 排名 <= 3;

24. 查询每门课程被选修的学生数

sql

SELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 选修人数 FROM Course c LEFT JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname;

25. 查询出只选修两门课程的学生学号和姓名

sql

SELECT s.SId, s.Sname FROM Student s JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) = 2;

26. 查询男生、女生人数

sql

SELECT Ssex AS 性别, COUNT(*) AS 人数 FROM Student GROUP BY Ssex;

27. 查询名字中含有「风」字的学生信息

sql

SELECT * FROM Student WHERE Sname LIKE '%风%';

28. 查询同名同性学生名单，并统计同名人数

sql

SELECT Sname, Ssex, COUNT(*) AS 同名人数 FROM Student GROUP BY Sname, Ssex HAVING COUNT(*) > 1;

29. 查询 1990 年出生的学生名单

sql

-- 方法1：YEAR函数 SELECT * FROM Student WHERE YEAR(Sage) = 1990; -- 方法2：LIKE匹配 SELECT * FROM Student WHERE Sage LIKE '1990%';

30. 查询每门课程的平均成绩（降序，平均成绩相同按课程号升序）

sql

SELECT c.CId, c.Cname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Course c JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname ORDER BY 平均成绩 DESC, c.CId ASC;

31. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

sql

SELECT s.SId, s.Sname, ROUND(AVG(sc.score), 1) AS 平均成绩 FROM Student s JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname HAVING AVG(sc.score) >= 85;

32. 查询课程名称为「数学」且分数低于 60 的学生姓名和分数

sql

SELECT s.Sname, sc.score FROM Student s JOIN SC sc ON s.SId = sc.SId JOIN Course c ON sc.CId = c.CId WHERE c.Cname = '数学' AND sc.score < 60;

33. 查询所有学生的课程及分数情况（含没成绩 / 没选课的情况）

sql

-- 笛卡尔积生成所有学生-课程组合，再左连接成绩 SELECT s.SId, s.Sname, c.Cname, sc.score FROM Student s CROSS JOIN Course c LEFT JOIN SC sc ON s.SId = sc.SId AND c.CId = sc.CId ORDER BY s.SId, c.CId;

34. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

sql

SELECT DISTINCT s.Sname, c.Cname, sc.score FROM Student s JOIN SC sc ON s.SId = sc.SId AND sc.score > 70 JOIN Course c ON sc.CId = c.CId;

35. 查询不及格的课程

sql

SELECT s.SId, s.Sname, c.Cname, sc.score FROM Student s JOIN SC sc ON s.SId = sc.SId AND sc.score < 60 JOIN Course c ON sc.CId = c.CId;

36. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

sql

SELECT s.SId, s.Sname FROM Student s JOIN SC sc ON s.SId = sc.SId WHERE sc.CId = '01' AND sc.score > 80;

37. 求每门课程的学生人数

sql

-- 同24题，重复题目 SELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 学生人数 FROM Course c LEFT JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname;

38. 成绩不重复，查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩

sql

-- 步骤：先找到张三老师的课程，再筛选最高分（无重复） SELECT s.*, sc.score, c.Cname FROM Student s JOIN SC sc ON s.SId = sc.SId JOIN Course c ON sc.CId = c.CId JOIN Teacher t ON c.TId = t.TId WHERE t.Tname = '张三' AND sc.score = ( SELECT MAX(score) FROM SC sc2 JOIN Course c2 ON sc2.CId = c2.CId JOIN Teacher t2 ON c2.TId = t2.TId WHERE t2.Tname = '张三' );

39. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩

sql

-- 方法：使用窗口函数 SELECT * FROM ( SELECT s.*, sc.score, c.Cname, RANK() OVER (ORDER BY sc.score DESC) AS 排名 FROM Student s JOIN SC sc ON s.SId = sc.SId JOIN Course c ON sc.CId = c.CId JOIN Teacher t ON c.TId = t.TId WHERE t.Tname = '张三' ) AS t WHERE 排名 = 1;

40. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

sql

SELECT DISTINCT sc1.SId, sc1.CId, sc1.score FROM SC sc1 JOIN SC sc2 ON sc1.SId = sc2.SId AND sc1.CId != sc2.CId AND sc1.score = sc2.score ORDER BY sc1.score;

41. 查询每门课程成绩最好的前两名

sql

-- 同23题（前三名），调整为前两名 SELECT * FROM ( SELECT sc.SId, sc.CId, sc.score, ROW_NUMBER() OVER (PARTITION BY sc.CId ORDER BY sc.score DESC) AS 排名 FROM SC sc ) AS t WHERE 排名 <= 2;

42. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

sql

SELECT c.CId, c.Cname, COUNT(DISTINCT sc.SId) AS 选修人数 FROM Course c JOIN SC sc ON c.CId = sc.CId GROUP BY c.CId, c.Cname HAVING COUNT(DISTINCT sc.SId) > 5;

43. 检索至少选修两门课程的学生学号

sql

SELECT SId FROM SC GROUP BY SId HAVING COUNT(DISTINCT CId) >= 2;

44. 查询选修了全部课程的学生信息

sql

SELECT s.* FROM Student s JOIN SC sc ON s.SId = sc.SId GROUP BY s.SId, s.Sname HAVING COUNT(DISTINCT sc.CId) = (SELECT COUNT(*) FROM Course);

45. 查询各学生的年龄（只按年份来算）

sql

SELECT SId, Sname, YEAR(NOW()) - YEAR(Sage) AS 年龄 FROM Student;

46. 按出生日期算年龄（当前月日 < 出生年月的月日则年龄减一）

sql

SELECT SId, Sname, YEAR(NOW()) - YEAR(Sage) - CASE WHEN DATE_FORMAT(NOW(), '%m%d') < DATE_FORMAT(Sage, '%m%d') THEN 1 ELSE 0 END AS 实际年龄 FROM Student;

47. 查询本周过生日的学生

sql

-- MySQL中WEEKOFYEAR函数：本周（1-53） SELECT * FROM Student WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(NOW());

48. 查询下周过生日的学生

sql

SELECT * FROM Student WHERE WEEKOFYEAR(Sage) = WEEKOFYEAR(NOW()) + 1;

49. 查询本月过生日的学生

sql

SELECT * FROM Student WHERE MONTH(Sage) = MONTH(NOW());

50. 查询下月过生日的学生

sql

SELECT * FROM Student WHERE MONTH(Sage) = MONTH(NOW()) + 1;

补充说明

所有 SQL 基于 MySQL 8.0 + 语法（支持窗口函数：RANK ()/DENSE_RANK ()/ROW_NUMBER ()）；
涉及日期计算的函数（YEAR/MONTH/WEEKOFYEAR）为 MySQL 特有，其他数据库（如 Oracle/SQL Server）需调整；
百分比计算中使用ROUND(...,1)保留 1 位小数，可根据需求调整；
左连接（LEFT JOIN）确保无成绩 / 无选课的学生也能显示，符合题目要求。