当前位置：首页 > article >正文

Mysql练习题

article 2025/4/1 9:29:36

先创建对应数据表

#先创建表
#学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
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
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

#教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

#成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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"课程成绩高的学生的信息及课程分数
使用子查询分别获取01和02课程的成绩，然后进行比较，最后与Student表进行JOIN获取学生信息
SELECT *
FROM Student
RIGHT JOIN (
SELECT t1.SId, class1, class2
FROM (
SELECT SId, score AS class1 FROM sc WHERE sc.CId = '01'
) AS t1,
(
SELECT SId, score AS class2 FROM sc WHERE sc.CId = '02'
) AS t2
WHERE t1.SId = t2.SId AND t1.class1 > t2.class2
) r
ON Student.SId = r.SId;

1.1 查询同时存在"01"课程和"02"课程的情况
使用INNER JOIN获取同时选修了01和02课程的学生
SELECT *
FROM (
SELECT * FROM sc WHERE sc.CId = '01'
) AS t1,
(
SELECT * FROM sc WHERE sc.CId = '02'
) AS t2
WHERE t1.SId = t2.SId;

1.2 查询存在"01"课程但可能不存在"02"课程的情况
使用LEFT JOIN保留01课程的所有学生，02课程不存在时显示为NULL
SELECT *
FROM (
SELECT * FROM sc WHERE sc.CId = '01'
) AS t1
LEFT JOIN
(
SELECT * FROM sc WHERE sc.CId = '02'
) AS t2
ON t1.SId = t2.SId;

1.3 查询不存在"01"课程但存在"02"课程的情况
使用NOT IN排除选修了01课程的学生
SELECT * FROM sc
WHERE sc.SId NOT IN (
SELECT SId FROM sc
WHERE sc.CId = '01'
)
AND sc.CId = '02';

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
使用GROUP BY和HAVING计算每个学生的平均成绩，并筛选出大于60分的学生
SELECT Student.SId, Student.Sname, r.ss
FROM Student
RIGHT JOIN (
SELECT SId, AVG(score) AS ss FROM sc
GROUP BY SId
HAVING AVG(score) >= 60
) r
ON Student.SId = r.SId;

3. 查询在SC表存在成绩的学生信息
使用DISTINCT和JOIN获取在SC表中有成绩的学生信息
SELECT DISTINCT student.*
FROM student, sc
WHERE student.SId = sc.SId;

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
使用LEFT JOIN确保显示所有学生，即使他们没有选课
SELECT s.sid, s.sname, r.coursenumber, r.scoresum
FROM (
SELECT student.sid, student.sname
FROM student
) s
LEFT JOIN
(
SELECT sc.sid, SUM(sc.score) AS scoresum, COUNT(sc.cid) AS coursenumber
FROM sc
GROUP BY sc.sid
) r
ON s.sid = r.sid;

4.2 查有成绩的学生信息
使用EXISTS和IN关键字查询有成绩的学生
EXISTS用于检查子查询是否至少会返回一行数据
IN适合小表，EXISTS适合大表
SELECT * FROM student
WHERE EXISTS (SELECT sc.sid FROM sc WHERE student.sid = sc.sid);

5. 查询「李」姓老师的数量
使用LIKE匹配姓氏为'李'的老师
SELECT COUNT(*)
FROM teacher
WHERE tname LIKE '李%';

6. 查询学过「张三」老师授课的同学的信息
多表JOIN查询张三老师授课的学生信息
SELECT student.*
FROM student, teacher, course, sc
WHERE
student.sid = sc.sid
AND course.cid = sc.cid
AND course.tid = teacher.tid
AND tname = '张三';

7. 查询没有学全所有课程的同学的信息
使用NOT IN排除选了所有课程的学生
SELECT * FROM student
WHERE student.sid NOT IN (
SELECT sc.sid FROM sc
GROUP BY sc.sid
HAVING COUNT(sc.cid) = (SELECT COUNT(cid) FROM course)
);

8. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
使用IN子查询查找与01号同学有相同课程的学生
SELECT * FROM student
WHERE student.sid IN (
SELECT sc.sid FROM sc
WHERE sc.cid IN (
SELECT sc.cid FROM sc
WHERE sc.sid = '01'
)
);

9. 查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT s.*
FROM student s
WHERE s.sid IN (
SELECT sc.sid
FROM sc
WHERE sc.cid IN (
SELECT sc.cid
FROM sc
WHERE sc.sid = '01'
)
GROUP BY sc.sid
HAVING COUNT(DISTINCT sc.cid) = (
SELECT COUNT(DISTINCT cid)
FROM sc
WHERE sid = '01'
)
) AND s.sid != '01';

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
使用NOT IN排除学过张三老师课程的学生
SELECT * FROM student
WHERE student.sid NOT IN (
SELECT sc.sid FROM sc WHERE sc.cid IN (
SELECT course.cid FROM course WHERE course.tid IN (
SELECT teacher.tid FROM teacher WHERE tname = "张三"
)
)
);

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
使用子查询统计不及格课程数量，并计算平均成绩
SELECT student.SId, student.Sname, b.avg
FROM student
RIGHT JOIN (
SELECT sid, AVG(score) AS avg
FROM sc
WHERE sid IN (
SELECT sid FROM sc
WHERE score < 60
GROUP BY sid
HAVING COUNT(score) > 1
)
GROUP BY sid
) b ON student.sid = b.sid;

12. 检索"01"课程分数小于60，按分数降序排列的学生信息
使用ORDER BY按分数降序排列
SELECT student.*, sc.score
FROM student, sc
WHERE student.sid = sc.sid
AND sc.score < 60
AND cid = "01"
ORDER BY sc.score DESC;

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
使用LEFT JOIN添加平均成绩，并按平均成绩排序
SELECT *
FROM sc
LEFT JOIN (
SELECT sid, AVG(score) AS avscore FROM sc
GROUP BY sid
) r
ON sc.sid = r.sid
ORDER BY avscore DESC;

14. 查询各科成绩最高分、最低分和平均分
使用聚合函数和CASE语句计算各统计值
SELECT
sc.CId,
MAX(sc.score) AS 最高分,
MIN(sc.score) AS 最低分,
AVG(sc.score) AS 平均分,
COUNT(*) AS 选修人数,
SUM(CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS 及格率,
SUM(CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS 中等率,
SUM(CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优良率,
SUM(CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优秀率
FROM sc
GROUP BY sc.CId
ORDER BY COUNT(*) DESC, sc.CId ASC;

15. 按各科成绩进行排序，并显示排名
使用自连接计算每门课程的成绩排名
SELECT a.cid, a.sid, a.score, COUNT(b.score) + 1 AS rank
FROM sc AS a
LEFT JOIN sc AS b
ON a.score < b.score AND a.cid = b.cid
GROUP BY a.cid, a.sid, a.score
ORDER BY a.cid, rank ASC;

16. 查询学生的总成绩，并进行排名
使用变量计算总成绩排名
SET @crank = 0;
SELECT q.sid, total, @crank := @crank + 1 AS rank
FROM (
SELECT sc.sid, SUM(sc.score) AS total FROM sc
GROUP BY sc.sid
ORDER BY total DESC
) q;

17. 统计各科成绩各分数段人数
使用CASE WHEN返回1后用SUM统计各分数段人数
SELECT course.cname, course.cid,
SUM(CASE WHEN sc.score <= 100 AND sc.score > 85 THEN 1 ELSE 0 END) AS "[10085]",
SUM(CASE WHEN sc.score <= 85 AND sc.score > 70 THEN 1 ELSE 0 END) AS "[8570]",
SUM(CASE WHEN sc.score <= 70 AND sc.score > 60 THEN 1 ELSE 0 END) AS "[7060]",
SUM(CASE WHEN sc.score <= 60 AND sc.score > 0 THEN 1 ELSE 0 END) AS "[600]"
FROM sc LEFT JOIN course
ON sc.cid = course.cid
GROUP BY sc.cid;

18. 查询各科成绩前三名的记录
使用自连接计算每门课程的成绩排名，筛选出排名前三的记录
SELECT a.sid, a.cid, a.score
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid AND a.score < b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid) < 3
ORDER BY a.cid;

19. 查询每门课程被选修的学生数
使用GROUP BY统计每门课程的学生数
SELECT cid, COUNT(sid) FROM sc
GROUP BY cid;

20. 查询出只选修两门课程的学生学号和姓名
使用HAVING子句筛选出选修两门课程的学生
SELECT student.sid, student.sname
FROM student
WHERE student.sid IN (
SELECT sc.sid FROM sc
GROUP BY sc.sid
HAVING COUNT(sc.cid) = 2
);

21. 查询男生、女生人数
使用GROUP BY统计男生和女生的人数
SELECT ssex, COUNT(*) FROM student
GROUP BY ssex;

22. 查询名字中含有「风」字的学生信息
使用LIKE匹配名字中包含「风」字的学生
SELECT *
FROM student
WHERE student.Sname LIKE '%风%';

23. 查询同名学生名单，并统计同名人数
使用GROUP BY和HAVING统计同名学生
SELECT sname, COUNT(*)
FROM student
GROUP BY sname
HAVING COUNT(*) > 1;

24. 查询1990年出生的学生名单
使用YEAR函数提取出生年份
SELECT *
FROM student
WHERE YEAR(student.Sage) = 1990;

25. 查询每门课程的平均成绩，结果按平均成绩降序排列
使用GROUP BY和ORDER BY计算每门课程的平均成绩
SELECT sc.cid, course.cname, AVG(SC.SCORE) AS average
FROM sc, course
WHERE sc.cid = course.cid
GROUP BY sc.cid
ORDER BY average DESC, cid ASC;

26. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
使用HAVING子句筛选平均成绩大于85的学生
SELECT student.sid, student.sname, AVG(sc.score) AS aver
FROM student, sc
WHERE student.sid = sc.sid
GROUP BY sc.sid
HAVING aver > 85;

27. 查询课程名称为「数学」，且分数低于60的学生姓名和分数
多表JOIN查询数学课程分数低于60的学生
SELECT student.sname, sc.score
FROM student, sc, course
WHERE student.sid = sc.sid
AND course.cid = sc.cid
AND course.cname = "数学"
AND sc.score < 60;

28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
使用LEFT JOIN确保显示所有学生
SELECT student.sname, cid, score
FROM student
LEFT JOIN sc
ON student.sid = sc.sid;

29. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
多表JOIN查询成绩在70分以上的学生信息
SELECT student.sname, course.cname, sc.score
FROM student, course, sc
WHERE sc.score > 70
AND student.sid = sc.sid
AND sc.cid = course.cid;

30. 查询存在不及格的课程
使用DISTINCT获取不及格的课程
SELECT DISTINCT sc.CId
FROM sc
WHERE sc.score < 60;

31. 查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名
查询01号课程成绩在80分及以上的学生
SELECT student.sid, student.sname
FROM student, sc
WHERE cid = "01"
AND score >= 80
AND student.sid = sc.sid;

32. 求每门课程的学生人数
使用GROUP BY统计每门课程的学生人数
SELECT sc.CId, COUNT(*) AS 学生人数
FROM sc
GROUP BY sc.CId;

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
使用ORDER BY和LIMIT获取最高分的学生
SELECT student.*, sc.score, sc.cid
FROM student, teacher, course, sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
ORDER BY score DESC
LIMIT 1;

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
使用子查询获取最高分，然后筛选出所有达到该分数的学生
SELECT student.*, sc.score, sc.cid
FROM student, teacher, course, sc
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
AND sc.score = (
SELECT MAX(sc.score)
FROM sc, student, teacher, course
WHERE teacher.tid = course.tid
AND sc.sid = student.sid
AND sc.cid = course.cid
AND teacher.tname = "张三"
);

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
使用INNER JOIN查找不同课程中成绩相同的学生
SELECT a.cid, a.sid, a.score
FROM sc AS a
INNER JOIN sc AS b
ON a.sid = b.sid
AND a.cid != b.cid
AND a.score = b.score
GROUP BY a.cid, a.sid;

36. 查询每门课程成绩最好的前两名
使用自连接计算每门课程的成绩排名，筛选出排名前两名的记录
SELECT a.sid, a.cid, a.score
FROM sc AS a
LEFT JOIN sc AS b
ON a.cid = b.cid AND a.score < b.score
GROUP BY a.cid, a.sid
HAVING COUNT(b.cid) < 2
ORDER BY a.cid;

37. 统计每门课程的学生选修人数（超过5人的课程才统计）
使用HAVING子句筛选出选修人数超过5人的课程
SELECT sc.cid, COUNT(sid) AS cc
FROM sc
GROUP BY cid
HAVING cc > 5;

38. 检索至少选修两门课程的学生学号
使用HAVING子句筛选出选修两门及以上课程的学生
SELECT sid, COUNT(cid) AS cc
FROM sc
GROUP BY sid
HAVING cc >= 2;

39. 查询选修了全部课程的学生信息
使用GROUP BY和HAVING确保学生选修了所有课程
SELECT student.*
FROM sc, student
WHERE sc.SId = student.SId
GROUP BY sc.SId
HAVING COUNT(*) = (SELECT COUNT(*) FROM course);

40. 查询各学生的年龄，只按年份来算
使用TIMESTAMPDIFF计算年龄
SELECT student.SId AS 学生编号, student.Sname AS 学生姓名,
TIMESTAMPDIFF(YEAR, student.Sage, CURDATE()) AS 学生年龄
FROM student;