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

此数据库查询语句是网络上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。

所有测试数据如下：

# --插入学生表测试数据
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' , '女');
# --插入课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
 
# --插入教师表测试数据
insert into teacher values('01' , '张三');
insert into teacher values('02' , '李四');
insert into teacher values('03' , '王五');
 
# --插入成绩表测试数据
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的时候，不可以用函数
# 如果两张表连接之后，有相同的字段，这时候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. 查询学过「张三」老师授课的同学的信息

# 张三老师是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. 查询没学过”张三”老师讲授的任一门课程的学生姓名

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

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 重复时保留名次空缺

# mysql中没有rank()函数
# 这种是重复时候保留名次，所以最后名次和人数是一样的
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 重复时合并名次

# 这种是当有重复名次的时候变成只有一个名次，所以排名的数量会变少
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.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺

# 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. 查询每门功课成绩最好的前两名

# 此题和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 人的课程才统计）

# 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 
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数据库查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！