MySQL 经典50题
两天累计花了6个小时。黄色背景都不是自己独立解答出来的,自己写的语句都是笨办法,便于以后复习。
1. 训练数据库
--创建测试数据
-- 学生表
create table Student(
S varchar(10),
Sname varchar(10),
Sage datetime,
Ssex nvarchar(10)
) ;
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('09' , '张三' , '1990-12-20', '女');
insert into Student values('10' , '李四' , '1990-01-20' , '女');
insert into Student values('11' , '李四' , '1990-01-20' , '女');
insert into Student values('12' , '赵六' , '1990-01-20' , '女');
insert into Student values('13' , '孙七' , '1990-01-20' , '女');
insert into Student values('14' , '王八' , '1990-10-23' , '男');
insert into Student values('15' , '留九' , '1990-10-29' , '男');
insert into Student values('16' , '苟十' , '1990-11-05' , '男');
--成绩表
create table SC(
S varchar(10),
C 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);
--课程表
create table Course(
C varchar(10),
Cname varchar(10),
T varchar(10)
);
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表
create table Teacher(
T varchar(10),
Tname varchar(10)
);
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
2. 50题目
-
查询" 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 重复时保留名次空缺
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-
查询各科成绩前三名的记录
-
查询每门课程被选修的学生数
-
查询出只选修两门课程的学生学号和姓名
-
查询男生、女生人数
-
查询名字中含有「风」字的学生信息
-
查询同名同性学生名单,并统计同名人数
-
查询 1990 年出生的学生名单
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-
查询不及格的课程
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
-
求每门课程的学生人数
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-
查询每门功成绩最好的前两名
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
-
检索至少选修两门课程的学生学号
-
查询选修了全部课程的学生信息
-
查询各学生的年龄,只按年份来算
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
-
查询本周过生日的学生
-
查询下周过生日的学生
-
查询本月过生日的学生
-
查询下月过生日的学生
3. 解题
-
查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select a.Sname as 姓名,b.score '01', c.score '02' from student a join (select * from sc where CId='01')as b on a.SId=b.SId join (select * from sc where CId='02') as c on b.SId=C.SId where b.score = c.score;
1.1 查询同时存在" 01 “课程和” 02 "课程的情况
select a.Sname as 姓名,b.score '01', c.score '02' from student a join (select * from sc where CId='01')as b on a.SId=b.SId join (select * from sc where CId='02') as c on b.SId=C.SId;
1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select a.Sname as 姓名,b.score '01', c.score '02' from student a join (select * from sc where CId='01')as b on a.SId=b.SId left join (select * from sc where CId='02') as c on b.SId=C.SId;
1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
select a.Sname as 姓名,b.score '02', c.score '01' from student a join (select * from sc where CId='02')as b on a.SId=b.SId left join (select * from sc where CId='01') as c on b.SId=C.SId;
-
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.SId '学生编号', a.Sname '姓名', s.avgSC '平均分' from student a join (select SId, avg(score) 'avgSC' from sc group by SId) as s on a.SId=s.SId where s.avgSC>=60;
-
查询在 SC 表存在成绩的学生信息
select distinct a.Sname '姓名' from student a right join sc b on a.SID=b.SId;
-
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.SId '编号', a.Sname '姓名', count(b.CId) '选课数', sum(score) '总成绩' from student a left join sc b on a.SId=b.SId group by a.SId, a.Sname;
4.1 查有成绩的学生信息
select a.SId '编号', a.Sname '姓名', count(b.CId) '选课总数', sum(score) '总成绩' from student a join sc b on a.SId=b.SId group by a.SId, a.Sname;
-
查询「李」姓老师的数量
select count(TId) '人数' from teacher where Tname like '李%';
-
查询学过「张三」老师授课的同学的信息
select a.SId, a.Sname from student a join sc b on a.SId=b.SId join course c on b.CId=c.CId join teacher d on c.TId=d.TId where d.Tname='张三';
-
查询没有学全所有课程的同学的信息
select a.SId '学生编号', a.Sname '姓名' from student a join (select SId, count(score) 'numSC' from sc group by SId) as s on a.SId=s.SId where s.numSC < 3;
-
查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select distinct a.SId, a.Sname from student a join sc b on a.SId=b.SId where a.SId != '01' and b.CId in (select CId from student a join sc b on a.SId=b.SId where a.SId='01');
-
查询和" 01 "号的同学学习的课程完全相同的其他同学的信息(难倒我了:思路从与01号修相同课程及门数解题)
select * from student where SId in (select sc.SId from (select CId from sc where SId='01')as a join sc on a.CId=sc.CId group by sc.SId having count(*)=(select count(*) from sc where SId='01')) and SId != '01';
-
查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct a.Sname from student a join sc b on a.SId=b.SId join course c on b.CId=c.CId where c.CId not in (select a.CId from sc a join course b on a.CID=b.CId join Teacher c on b.TId=c.TId where c.Tname='张三');
-
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select a.SId, a.Sname, avg(score) '平均分' from student a join sc b on a.SId=b.SId where b.score < 60 group by SId having count(*) >= 2;
-
检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select a.* from student a join sc b on a.SId=b.SId where b.CId='01' and b.score < 60 order by score desc;
-
按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.SId, score, avg from (select SId, avg(score) 'avg' from sc group by SId) as a join sc b on a.SId=b.SId order by a.avg desc;
-
查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select sc.CId '课程ID', Cname '课程Name', max(score) '最高分', min(score) '最低分', avg(score) '平均分', count(*) '选修人数', sum(case when sc.score >= 60 then 1 else 0 end)/count(*)as '及格率', sum(case when sc.score between 70 and 79 then 1 else 0 end)/count(*) as '中等率', sum(case when sc.score between 80 and 89 then 1 else 0 end)/count(*) as '优良率', sum(case when sc.score>=90 then 1 else 0 end)/count(*) as '优秀率' from sc join course b on sc.CId=b.CId group by sc.CId;
-
按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺 (只会笨办法)
(select t.*, (select count(score)+1 from (select * from sc where CId='01') s where s.score>t.score) rank from (select * from sc where CId='01') as t order by score desc) union all (select t.*, (select count(score)+1 from (select * from sc where CId='02') s where s.score>t.score) rank from (select * from sc where CId='02') as t order by score desc) union all (select t.*, (select count(score)+1 from (select * from sc where CId='03') s where s.score>t.score) rank from (select * from sc where CId='03') as t order by score desc) order by CId, score desc;
-
查询学生的总成绩,并进行排名,总分重复时保留名次空缺
select t.SId, t.score, (select count(*)+1 from (select SId, sum(score) 'score' from sc group by SId) as s where s.score>t.score) 'rank' from (select SId, sum(score) 'score' from sc group by SId) as t order by score desc;
-
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
select a.CId '课程编号', Cname '课程名称', sum(case when score between 0 and 59 then 1 else 0 end) '[60-0]', sum(case when score between 0 and 59 then 1 else 0 end)/count(*) '[60-0]百分比', sum(case when score between 60 and 69 then 1 else 0 end) '[70-60]', sum(case when score between 60 and 69 then 1 else 0 end)/count(*) '[70-60]百分比', sum(case when score between 70 and 84 then 1 else 0 end) '[85-70]', sum(case when score between 70 and 84 then 1 else 0 end)/count(*) '[85-70]百分比', sum(case when score between 85 and 100 then 1 else 0 end) '[100-85]', sum(case when score between 85 and 100 then 1 else 0 end)/count(*) '[100-85]百分比' from sc a join course b on a.CId=b.CId group by a.CId;
-
查询各科成绩前三名的记录(括号一定不能少)
(select * from sc where CId='01' order by score desc limit 0,3) union all (select * from sc where CId='02' order by score desc limit 0,3) union all (select * from sc where CId='03' order by score desc limit 0,3);
-
查询每门课程被选修的学生数
select CId, count(SId) '选课人数' from sc group by CId;
-
查询出只选修两门课程的学生学号和姓名
select a.SId, Sname from sc a join student b on a.SId=b.SId group by SId having count(a.SId)=2;
-
查询男生、女生人数
select Ssex, count(*) from student group by Ssex;
-
查询名字中含有「风」字的学生信息
select * from student where Sname like '%风'
-
查询同名同性学生名单,并统计同名人数
select a.Sname, count(*) '同名人数' from student a join student b on a.Sname=b.Sname where a.SId != b.SId group by a.Sname;
-
查询 1990 年出生的学生名单
select Sname from student where year(Sage)=1990;
-
查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select CId, avg(score) '平均成绩' from sc group by CId order by avg(score) desc,CId;
-
查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.SId, Sname '姓名', avgSC '平均成绩' from student a join (select SId, avg(score) 'avgSC' from sc group by SId) as b on a.SId=b.SId where b.avgSC >= 85;
-
查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select Sname, score from student a join sc b on a.SId=b.SId join course c on b.CId=c.CId where c.Cname='数学' and score < 60;
-
查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select a.SId, Sname, Cname, score from student a left join sc b on a.SId=b.SId left join course c on b.CId=c.CId;
-
查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Sname, Cname, score from student a join sc b on a.SId=b.SId join course c on b.CId=c.CId where score > 70;
-
查询不及格的课程
select Sname, Cname, score from student a join sc b on a.SId=b.SId join course c on b.CId=c.CId where score < 60;
-
查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select a.SId, Sname from student a join sc b on a.SId=b.SId where b.CId='01' and b.score>=80;
-
求每门课程的学生人数
select CId, count(SId) '人数' from sc group by CId;
-
成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select s.*, score from student s join sc b on s.SId=b.SId join course c on b.CId=c.CId join teacher t on t.TId=c.TId where t.Tname='张三' order by score desc limit 0,1;
-
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select DISTINCT a.SId, a.CId, a.score from sc a join sc b on a.score=b.score where a.CId != b.CId;
-
查询每门功成绩最好的前两名
(select Sname, CId, score from student a join sc b on a.SId=b.SId where b.CId='01' order by score desc limit 0,2) union all (select Sname, CId, score from student a join sc b on a.SId=b.SId where b.CId='02' order by score desc limit 0,2) union all (select Sname, CId, score from student a join sc b on a.SId=b.SId where b.CId='03' order by score desc limit 0,2);
-
统计每门课程的学生选修人数(超过 5 人的课程才统计)。
select CId, count(SId) '人数' from sc group by CId having count(*)>5;
-
检索至少选修两门课程的学生学号
select SId from sc group by SId having count(*)>=2;
-
查询选修了全部课程的学生信息
select a.* from student a join sc b on a.SId=b.SId group by SId having count(*)=(select count(*) from course);
-
查询各学生的年龄,只按年份来算
select Sname, year(now())-year(Sage) 'year' from student;
-
按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select Sname, (case when month(now())>=month(Sage) then year(now())-year(Sage) else year(now())-year(Sage)-1 end) as 'age' from student;
-
查询本周过生日的学生
-- A:不全对 select Sname from student where month(Sage)=month(now()) and abs(day(now())-day(Sage)) <7-dayofweek(now()); --B select Sname from student where weekofyear(Sage)=weekofyear(now());
-
查询下周过生日的学生
select Sname from student where weekofyear(Sage)=weekofyear(now())+1;
-
查询本月过生日的学生
select Sname from student where month(Sage)=month(now());
-
查询下月过生日的学生
select Sname from student where month(Sage)=month(now())+1;