最全50个Mysql数据库查询练习题

数据运维 2023-05-01 共饮一杯 手机阅读

此数据库查询语句是网络上50个数据库查询练习题目，网上有些版本是oracle语句写的，大多数公司还是用免费的mysql数据库，以下都是mysql版本，全部都有验证过。 表名和字段 –1.学生表

此数据库查询语句是网络上50个数据库查询练习题目，网上有些版本是oracle语句写的，大多数公司还是用免费的mysql数据库，以下都是mysql版本，全部都有验证过。

表名和字段

–1.学生表 Student(s#, sname, sage,ssex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c#,cname,t#) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t#,tname) –教师编号,教师姓名 –4.成绩表 Sc(s#,c#,score) –学生编号,课程编号,分数

测试数据

用数据库可视化工具做练习非常方便，推荐使用sqlyog，软件图标是一只海豚。

在新连接种填上本机地址，用户名，密码和端口就直接连上mysql。

所有测试数据如下：

  1. --插入学生表测试数据 INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男'); INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男'); INSERT INTO student VALUES('03' , '孙风' , '1990-05-20' , '男'); INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男'); INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女'); INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女'); INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女'); INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');

  2. --插入课程表测试数据 INSERT INTO course VALUES('01' , '语文' , '02'); INSERT INTO course VALUES('02' , '数学' , '01'); INSERT INTO course VALUES('03' , '英语' , '03');

  3. --插入教师表测试数据 INSERT INTO teacher VALUES('01' , '张三'); INSERT INTO teacher VALUES('02' , '李四'); INSERT INTO teacher VALUES('03' , '王五');

  4. --插入成绩表测试数据 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);

最后是50个数据库查询练习，已经验证过，是mysql版本的。

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

SELECT * FROM (SELECT s# AS sno1, c#AS cno1, score FROM sc WHERE c#=01) a LEFT JOIN (SELECT s# AS sno2, c#AS cno2, score FROM sc WHERE c#=02) b ON a.sno1 = b.sno2 WHERE a.score > b.score

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

SELECT * FROM (SELECT s# AS sno1, c#AS cno1, score FROM sc WHERE c#=01) a LEFT JOIN (SELECT s# AS sno2, c#AS cno2, score FROM sc WHERE c#=02) b ON a.sno1 = b.sno2 WHERE sno2 IS NOT NULL

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT * FROM (SELECT s# AS sno1, c#AS cno1, score FROM sc WHERE c#=01) a LEFT JOIN (SELECT s# AS sno2, c#AS cno2, score FROM sc WHERE c#=02) b ON a.sno1 = b.sno2

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

SELECT * FROM     sc WHERE `c#`='02' AND `s#` NOT IN (SELECT `s#` FROM sc WHERE `c#`='01')

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

SELECT a.s#,b.sname, a.avg_score FROM (SELECT s# ,AVG(score) AS avg_score FROM sc GROUP BY s#) AS a LEFT JOIN student AS b ON a.s# = b.s# WHERE a.avg_score >=60

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

SELECT * FROM student WHERE `s#` IN (SELECT DISTINCT `s#` FROM sc)

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 NULL )

SELECT s# ,sname , course_num , score_sum FROM (SELECT s#, sname FROM student ) AS a LEFT JOIN (SELECT s# AS sno ,COUNT(c#) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.s# = b.sno

4.1 查有成绩的学生信息

# 在最外面一层select的时候，不可以用函数 1. 如果两张表连接之后，有相同的字段，这时候select就需要把其中一个字段改名

SELECT s# ,sname , course_num , score_sum FROM (SELECT s#, sname FROM student ) AS a LEFT JOIN (SELECT s# AS sno ,COUNT(c#) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.s# = b.sno WHERE course_num IS NOT NULL

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

SELECT COUNT(*) FROM teacher WHERE tname LIKE '李%'

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

  1. 张三老师是01号 SELECT * FROM student WHERE s# IN (SELECT s# FROM sc WHERE c# = (SELECT c# FROM course WHERE t# = (SELECT t# FROM teacher WHERE tname='张三')))

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

SELECT `s#`,COUNT(`c#`) AS course_num FROM sc GROUP BY `s#`     HAVING course_num < (SELECT COUNT(*) FROM course)

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

SELECT * FROM student WHERE s# IN (SELECT DISTINCT s# FROM sc WHERE c# IN (SELECT c# FROM sc WHERE s#=01)) AND s#!= 01

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

SELECT s# FROM (SELECT * FROM sc LEFT JOIN (SELECT c# AS cno FROM sc WHERE s# =01) a ON sc.c# = a.cno) AS b GROUP BY s#
HAVING COUNT(b.s#) = (SELECT COUNT(c#) AS cno FROM sc WHERE s# =01)

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

  1. 张三是01
  2. 01老师是教数学，c#是02 SELECT * FROM student WHERE s# NOT IN (SELECT DISTINCT s# FROM sc WHERE c# IN (SELECT c# FROM course WHERE t# IN (SELECT t# FROM teacher WHERE tname = '张三')))

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

SELECT s#, sname, avg_score FROM (SELECT s#, sname FROM student WHERE s# IN (SELECT a.s# FROM (SELECT s#,COUNT(c#) AS num FROM sc WHERE score <60 GROUP BY s#) a WHERE num >=2)) AS b LEFT JOIN (SELECT s# AS sno ,AVG(score) AS avg_score FROM sc GROUP BY s#) AS c ON b.s# = c.sno

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

SELECT s#, sname, score FROM student AS a LEFT JOIN (SELECT s# AS sno,c#,score FROM sc WHERE c#= 01 AND score <60 )b ON a.s#= b.sno WHERE score IS NOT NULL ORDER BY score DESC

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

SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#` ORDER BY avg_score DESC

# 14. 查询各科成绩最高分、最低分和平均分： 1. 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 1. 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 1. 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT DISTINCT a.c#,cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 FROM sc a LEFT JOIN course ON a.c#=course.c# LEFT JOIN (SELECT c#, MAX(score)最高分, MIN(score)最低分, AVG(score)平均分 FROM sc GROUP BY c#)b ON a.c#=b.c# LEFT JOIN (SELECT c#, ROUND( r1 /cnt 100, 2 ) AS 及格率 FROM (SELECT c#, (SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)1.00) AS r1 , COUNT() AS cnt FROM sc GROUP BY c#) c1) c ON a.c#=c.c# LEFT JOIN (SELECT c#, ROUND( r2 /cnt 100, 2 ) AS 中等率 FROM (SELECT c#, (SUM(CASE WHEN score >=70 AND score<80 THEN 1 ELSE 0 END)1.00) AS r2 , COUNT() AS cnt FROM sc GROUP BY c#) d1) d ON a.c#=d.c#
LEFT JOIN (SELECT c#, ROUND( r3 /cnt 100, 2 ) AS 优良率 FROM (SELECT c#, (SUM(CASE WHEN score >=80 AND score<90 THEN 1 ELSE 0 END)1.00) AS r3 , COUNT() AS cnt FROM sc GROUP BY c#) e1) e ON a.c#=e.c# LEFT JOIN (SELECT c#, ROUND( r4 /cnt 100, 2 ) AS 优秀率 FROM (SELECT c#, (SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)1.00) AS r4 , COUNT() AS cnt FROM sc GROUP BY c#) f1) f ON a.c#=f.c#

# 15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

  1. mysql中没有rank()函数
  2. 这种是重复时候保留名次，所以最后名次和人数是一样的 SELECT s#, c#, score, rank FROM (SELECT s#, c#, score, @currank := IF(@prevrank = score, @currank, @incrank) AS rank, @incrank := @incrank + 1, @prevrank := score FROM sc , ( SELECT @currank :=0, @prevrank := NULL, @incrank := 1 ) r ORDER BY score DESC) s

# 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

  1. 这种是当有重复名次的时候变成只有一个名次，所以排名的数量会变少 SELECT s#, c#, score, CASE WHEN @prevrank = score THEN @currank WHEN @prevrank := score THEN @currank := @currank + 1 END AS rank FROM sc, (SELECT @currank :=0, @prevrank := NULL) r ORDER BY score DESC

# 16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺

  1. from后面不需要加表的别名 SELECT s#, sum_score, rank FROM (SELECT s#, sum_score, @currank := IF(@prevrank = sum_score, @currank, @incrank) AS rank, @incrank := @incrank + 1, @prevrank := sum_score FROM (SELECT s#, SUM(score) AS sum_score FROM sc GROUP BY s#) c , (SELECT @currank :=0, @prevrank := NULL, @incrank := 1) r ORDER BY sum_score DESC) s

# 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT c.*, CASE WHEN @prevrank = c.sum_score THEN @currank WHEN @prevrank := c.sum_score THEN @currank := @currank + 1 END AS rank FROM (SELECT a.s#,a.sname,SUM(score) AS sum_score FROM (student AS a RIGHT JOIN sc AS b ON a.s# = b.s#) GROUP BY a.s# ) c , (SELECT @currank := 0 , @prevrank :=NULL ) d ORDER BY sum_score DESC

# 17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT a.c# , b.cname, SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END ) '[100-85]', SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END )1.00/COUNT() AS '[100-85]percent', SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END ) '(85-70]', SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END )1.00/COUNT() AS '(85-70]percent', SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END ) '(70-60]', SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END )1.00/COUNT() AS '(85-70]percent', SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END ) '(60-0]', SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END )1.00/COUNT() AS '(85-70]percent', COUNT(*) AS counts FROM sc a LEFT JOIN course b ON a.c# = b.c# GROUP BY c#

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

SELECT FROM sc a WHERE (SELECT COUNT() FROM sc WHERE c#=a.c# AND score>a.score)<3 ORDER BY a.c#, a.score DESC;

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

SELECT c#, COUNT(s#) FROM (SELECT s#,c# FROM sc ORDER BY c#)a GROUP BY c#

SELECT a.c# , b.cname ,COUNT(*) AS num FROM sc a LEFT JOIN course b ON a.c# = b.c# GROUP BY a.c#;

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

SELECT a.s#, a.sname ,cnt FROM student a LEFT JOIN (SELECT s#,COUNT(c#) AS cnt FROM sc GROUP BY s#) b ON a.s#=b.s# WHERE cnt=2

# 21. 查询男生、女生人数

SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex

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

SELECT * FROM student WHERE sname LIKE '%风%'

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

SELECT a.,b.同名人数 FROM student a LEFT JOIN (SELECT sname,ssex,COUNT() AS 同名人数 FROM student GROUP BY sname,ssex)b ON a.sname=b.sname AND a.ssex=b.ssex WHERE b.同名人数>1

# 24. 查询 1990 年出生的学生名单

SELECT * FROM student WHERE YEAR(sage) = 1990

# 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT `c#`, ROUND(AVG(score),2) AS avg_score FROM sc GROUP BY `c#` ORDER BY `c#` ASC

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

SELECT c.s#,sname ,avg_score FROM (student c LEFT JOIN (SELECT s#, avg_score FROM (SELECT s# ,ROUND(AVG(score),2) AS avg_score FROM sc GROUP BY s# ORDER BY avg_score DESC)a WHERE avg_score >=85) b ON c.s# =b.s#) WHERE avg_score IS NOT NULL

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

SELECT a.s#,a.sname,b.math, b.score FROM student a LEFT JOIN (SELECT s#,c# AS math ,score FROM sc WHERE c# IN (SELECT c# FROM course WHERE cname = '数学') AND sc.score <60) b ON a.s#=b.s# WHERE b.score IS NOT NULL

# 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT a.s#,a.sname,a.sage,a.ssex,b.c#,b.score FROM student a LEFT JOIN sc b ON a.s# = b.s# LEFT JOIN course c ON c.c# = b.c#

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

SELECT a.s#,a.sname,a.sage,a.ssex,b.c#,b.score FROM student a LEFT JOIN (SELECT s#,c#,score FROM sc WHERE score >70) b ON a.s#=b.s# LEFT JOIN course c ON c.c#=b.c# WHERE score IS NOT NULL

# 30. 查询不及格的课程

SELECT * FROM sc WHERE score < 60

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

SELECT a.s#, a.sname ,b.score FROM student a LEFT JOIN (SELECT * FROM sc WHERE c#='01' AND score >= 80) b ON a.s# = b.s# WHERE score IS NOT NULL

# 32. 求每门课程的学生人数

SELECT `c#`,COUNT(`c#`) FROM sc GROUP BY `c#`

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

SELECT a.s#, a.sname ,b.c#, b.max_score FROM student a LEFT JOIN (SELECT s# AS sid,c# ,MAX(score) AS max_score FROM sc WHERE c# IN (SELECT c# FROM course WHERE t# IN (SELECT t# FROM teacher WHERE tname = '张三'))) b ON a.s#=b.sid WHERE max_score IS NOT NULL

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

SELECT FROM (SELECT dd., CASE WHEN @prevrank = dd.score THEN @currank WHEN @prevrank := dd.score THEN @currank := @currank + 1 END AS rank FROM (SELECT a.*,b.score FROM student a LEFT JOIN sc b ON a.s# = b.s# LEFT JOIN course c ON b.c# = c.c# LEFT JOIN teacher d ON c.t# = d.t# WHERE d.tname = '张三' ) dd,(SELECT @currank := 0 , @prevrank :=NULL ) ff ORDER BY score DESC) AS dddddddd WHERE rank = 1;

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

SELECT DISTINCT a.s#, a.c#, a.score FROM sc AS a JOIN sc AS b WHERE a.c# != b.c# AND a.score = b.score AND a.s# != b.s# ORDER BY a.s#, a.c#, a.score

# 36. 查询每门功课成绩最好的前两名

  1. 此题和18题相同 SELECT FROM sc a WHERE (SELECT COUNT() FROM sc WHERE c#=a.c# AND score>a.score)<2 ORDER BY a.c#, a.score DESC;

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

  1. 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 SELECT a.c#, COUNT(*) AS num FROM course a LEFT JOIN sc b ON a.c# = b.c# GROUP BY a.c# HAVING num > 5 ORDER BY num,a.c#

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

SELECT DISTINCT`s#`,COUNT(`c#`) AS num FROM sc GROUP BY `s#` HAVING num >=2

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

SELECT FROM (SELECT s#,COUNT() AS num FROM sc GROUP BY s# ) b WHERE num = (SELECT COUNT(*) FROM course)

# 40. 查询各学生的年龄，只按年份来算

SELECT *, YEAR(NOW()) - YEAR(sage) AS age FROM student

# 41. 查询本周过生日的学生

SELECT FROM (SELECT , WEEK(sage), MONTH(sage),DAY(sage), WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a WHERE w = WEEK(NOW())

# 42. 查询下周过生日的学生

SELECT FROM (SELECT , WEEK(sage), MONTH(sage),DAY(sage),WEEK(NOW()), WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a WHERE w + 2 = WEEK(NOW())

# 43. 查询本月过生日的学生

SELECT * , MONTH(sage),MONTH(NOW()) FROM student WHERE MONTH(sage) = MONTH(NOW())

# 44. 查询下月过生日的学生

SELECT * , MONTH(sage),MONTH(NOW()) FROM student WHERE MONTH(sage) = MONTH(NOW()) + 1

到此这篇关于最全50个Mysql数据库查询练习题的文章就介绍到这了,更多相关Mysql数据库查询内容请搜索每日运维以前的文章或继续浏览下面的相关文章希望大家以后多多支持每日运维！