必须掌握的MySQL命令系列,无聊的时候多看看啊小兄弟
我使用的Mysql版本是5.7.19。答案可能会因版本会有少许出入。
本文参考自:https://blog.csdn.net/flycat296/article/details/63681089
练习数据
数据表
–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
1 | create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); |
科目表 Course
1 | create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)) |
教师表 Teacher
1 | create table Teacher(TId varchar(10),Tname varchar(10)) |
成绩表 SC
1 | create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)) |
练习题目
- 查询”01”课程比”02”课程成绩高的学生的信息及课程分数
1.1 查询同时存在”01”课程和”02”课程的情况
1.2 查询存在”01”课程但可能不存在”02”课程的情况(不存在时显示为 null )
1.3 查询不存在”01”课程但存在”02”课程的情况 - 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
- 查询在 SC 表存在成绩的学生信息
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1 查有成绩的学生信息 - 查询「李」姓老师的数量
- 查询学过「张三」老师授课的同学的信息
- 查询没有学全所有课程的同学的信息
- 查询至少有一门课与学号为”01”的同学所学相同的同学的信息
- 查询和”01”号的同学学习的课程 完全相同的其他同学的信息
- 查询没学过”张三”老师讲授的任一门课程的学生姓名
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
- 检索”01”课程分数小于 60,按分数降序排列的学生信息
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 - 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次 - 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺 - 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
- 查询各科成绩前三名的记录
- 查询每门课程被选修的学生数
- 查询出只选修两门课程的学生学号和姓名
- 查询男生、女生人数
- 查询名字中含有「风」字的学生信息
- 查询同名同性学生名单,并统计同名人数
- 查询 1990 年出生的学生名单
- 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
- 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
- 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
- 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
- 查询不及格的课程
- 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
- 求每门课程的学生人数
- 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
- 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 查询每门功成绩最好的前两名
- 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
- 检索至少选修两门课程的学生学号
- 查询选修了全部课程的学生信息
- 查询各学生的年龄,只按年份来算
- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
- 查询本周过生日的学生
- 查询下周过生日的学生
- 查询本月过生日的学生
- 查询下月过生日的学生
参考答案
1.查询”01”课程比”02”课程成绩高的学生的信息及课程分数
1 | select * |
1.1 查询同时存在”01”课程和”02”课程的情况
1 | select * |
1.2 查询存在”01”课程但可能不存在”02”课程的情况(不存在时显示为 null )
1 | select * |
1.3 查询不存在”01”课程但存在”02”课程的情况
1 | select * |
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
1 | select student.*,t1.avgscore |
- 查询在 SC 表存在成绩的学生信息
1 | select DISTINCT student.* |
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
1 | select student.SId,student.Sname,t1.sumscore,t1.coursecount |
4.1 查有成绩的学生信息
1 | select * |
- 查询「李」姓老师的数量
1 | select count(*) |
- 查询学过「张三」老师授课的同学的信息
1 | select student.* |
- 查询没有学全所有课程的同学的信息
- 解法1
1 | select student.* |
但这种解法得出来的结果不包括什么课都没选的同学。
- 解法2
1 | select DISTINCT student.* |
利用笛卡尔积可以把什么课都没选的同学查询出来
- 查询至少有一门课与学号为”01”的同学所学相同的同学的信息
1 | select DISTINCT student.* |
9.查询和”01”号的同学学习的课程完全相同的其他同学的信息
1 | select DISTINCT student.* |
10.查询没学过”张三”老师讲授的任一门课程的学生姓名
1 | select * |
11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
1 | select student.SId,student.Sname,avg(sc.score) |
- 检索”01”课程分数小于 60,按分数降序排列的学生信息
1 | select student.* |
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
1 | select |
- 查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
1 | 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 and sc.score<80 then 1 else 0 end )/count(*)as 优良率,sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 |
- 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
1 | select sc.CId ,@curRank:=@curRank+1 as rank,sc.score |
15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
1 | select sc.CId , case when @fontscore=score then @curRank when @fontscore:=score then @curRank:=@curRank+1 end as rank,sc.score |
- 查询学生的总成绩,并进行排名,总分重复时保留名次空缺
1 | select t1.*,@currank:= @currank+1 as rank |
16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
1 | select t1.*, case when @fontscore=t1.sumscore then @currank when @fontscore:=t1.sumscore then @currank:=@currank+1 end as rank |
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
1 | select course.CId,course.Cname,t1.* |
- 查询各科成绩前三名的记录
思路:前三名转化为若大于此成绩的数量少于3即为前三名。
1 | select * |
- 查询每门课程被选修的学生数
1 | select sc.CId,count(*) |
- 查询出只选修两门课程的学生学号和姓名
1 | select student.SId,student.Sname |
21.查询男生、女生人数
1 | select student.Ssex ,count(*) as 人数 |
- 查询名字中含有「风」字的学生信息
1 | select * |
23.查询同名同性学生名单,并统计同名人数
1 | select * |
24.查询 1990 年出生的学生名单
1 | select * |
25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
1 | select sc.CId,AVG(sc.score) |
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
1 | select student.SId,student.Sname,t1.avgscore |
- 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
1 | select student.Sname ,t1.score |
- 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
1 | select student.SId,sc.CId,sc.score from Student left join sc on student.SId=sc.SId |
- 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
1 | select student.Sname,course.Cname,sc.score |
30.查询存在不及格的课程
1 | select DISTINCT sc.CId |
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
1 | select student.SId,student.Sname |
- 求每门课程的学生人数
1 | select sc.CId,count(*) as 学生人数 |
- 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
1 | select student.*,sc.score |
- 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
1 | select student.*,t1.score |
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
1 | select * |
36.查询每门功成绩最好的前两名
1 | select * |
37.统计每门课程的学生选修人数(超过 5 人的课程才统计)
1 | select sc.CId as 课程编号,count(*) as 选修人数 |
38.检索至少选修两门课程的学生学号
1 | select DISTINCT t1.SId |
- 查询选修了全部课程的学生信息
1 | select student.* |
40.查询各学生的年龄,只按年份来算
1 | select student.SId as 学生编号,student.Sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄 |
- 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
1 | select student.SId as 学生编号,student.Sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄 |
42.查询本周过生日的学生
1 | select * |
- 查询下周过生日的学生
1 | select * |
44.查询本月过生日的学生
1 | select * |
45.查询下月过生日的学生
1 | select * |