MySql练习题（50）

创建表

学生表：student

• Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

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' , '女');

教师表：teacher

• Tid 教师编号,Tname 教师姓名

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

课程表：course

• Cid 课程编号,Cname 课程名称,Tid 教师编号

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');

成绩表：sc

• Sid 学生编号,Cid 课程编号,score 分数

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);

练习题

详细解析链接：点我

作者：daydayupupupup
链接：https://zhuanlan.zhihu.com/p/60216741
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

#1.1查询01课程比02课程成绩高的学生信息及课程分数 01 02都存在
select student.*,
max(case when sc.cid='01' then score end) as C1,
max(case when sc.cid='02' then score  end)as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c2 is not null
and c1>c2;

select * from student right join(
      select t1.sid, C1,C2 from
            (select sid,score as C1 from sc where sc.cid='01') as t1,
             (select sid,score as C2 from sc where sc.cid='02') as t2
             where t1.sid=t2.sid and t1.c1>t2.c2
)r
on student.sid=r.sid;
#1.2查询01课程比02课程成绩高的学生信息及课程分数 01存在 02可能不存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is not null
and c1>c2
or c2 is null;

#1.3查询01课程分数不存在但02存在
select student.*,
max(case when cid='01' then score end) as C1,
max(case when cid='02' then score end) as C2
from student left join sc
on student.sid=sc.sid
group by sid
having c1 is null
and c2 is not null;

##2.查询平均成绩大于等于60的同学的学生编号和学生姓名和平均成绩
select Sname,SId,(select avg(score) 
                  from sc 
                  where sc.SId=student.SId 
                  group by SId
                  having AVG(score)>=60)as avg_score 
from student;

##3.查询SC表存在成绩的学生信息
select Sname,Sage,Ssex
from student 
where SId IN(select SId from sc where score IS NOT NULL);

#4 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student left join sc
on sc.SId=student.SId
group by SId;

#4.1 查询所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩(有成绩)
select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score
from student inner join sc
on sc.SId=student.SId
group by SId;

#5.查询李姓老师的数量
select count(*) as num_li
from teacher
where Tname like '李%';

#6.查询学过张三老师课程的学生的信息
select *
from student 
where SId in(
select SId from sc where CId in(select CId 
from course 
where TId=(select TId
from teacher 
where Tname='张三'))); #子查询

select student.SId,Sname,Sage,Ssex
from student,sc,course,teacher
where student.SId=sc.SId
and sc.CId=course.CId
and teacher.TId=course.TId
and teacher.Tname='张三'; #多表联结

#7.查询没有学全所有课程的同学的信息
select student.*,count(sc.SId) as num_course
from student left join sc
on student.SId=sc.SId
group by student.SId
having num_course<(select count(CId) from course); #分组后筛选用having

#8.查询至少一门课与学号为‘01’的同学所学相同的同学的信息
select *
from student
where SId in (
select distinct SId from sc
where CId in (select CId from sc where SId='01'));#课程总数

#9.查询和01同学课程完全相同的学生的信息
select SId,Count(SId) as num_course
from sc  
where CId in('01','02','03')
group by SId
having num_course=3;

select *
from student
where SId in(select SId
                    from sc  
                    where CId in(select CId from sc where SId='01')
                    group by SId
                    having Count(SId)=(select count(CId)from sc where SId='01'));
                    
#10.查询没学过‘张三’老师教授的任一门课程的学生姓名
select CId,teacher.TId,Tname
from course inner join teacher
on course.Tid=teacher.tid
where Tname='张三'; #教授的课程编号

select student.*
from student
where SId not in (select distinct student.SId
                  from student left join sc
                  on student.sid=sc.sid
                  where cid  in (select CId
                                 from course inner join teacher 
                                 on course.Tid=teacher.tid
                                 where Tname='张三')); #学过张三老师任一门课的同学)
select student.*
from student
where SId not in (select distinct student.SId
                  from student,sc,course,teacher
                  where student.sid=sc.sid
                  and course.cid=sc.cid
                  and course.Tid=teacher.tid
                  and  Tname='张三');#学过张三老师任一门课的同学                  

#11.查询两门及以上不及格课程的同学的学号，姓名和平均成绩
select student.SId,Sname,avg(score) as avg_score
from sc inner join student
on sc.SId=student.SId
group by sc.SId
having sc.SId in (select sid
                 from sc 
                 where score<60
                 group by SId
                 having count(CId)>=2);    #2门及以上课程不及格同学的ID

#12.检索01课程分数小于60，按分数降序排列的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and cid='01'
and score<60
order by score desc;

#13.查询平均成绩由高到低显示所有学生的所有课程的成绩及平均成绩
select * from sc
left join (select sid,avg(score) as avg_score from sc 
           group by sid)r 
on r.sid=sc.sid
order by avg_score desc;

#14.查询各科成绩的最高分、最低分和平均分
/*create view course_score as
select course.CId,course.Cname,sc.score
from course left join sc
on course.cid=sc.cid;*/

select cid,cname,max(score) as max_score,
min(score) as min_score,
avg(score) as avg_score,
sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when 80>score&score>=70 then 1 else 0 end)/count(*) as 中等率,
sum(case when 90>score&score>=80 then 1 else 0 end)/count(*) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(*) as 优秀率,
count(*) as num
from course_score
group by cid
order by num desc,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 B.score>A.score and B.cid=A.cid
group by A.cid,A.sid
order by A.cid,rank asc;
#15.1 按各科成绩进行排序，并显示排名，成绩重复时合并名次
select a.*,count(distinct b.score)+1 as rank
from sc as a
left join sc as b
on b.score>a.score and b.cid=a.cid
group by a.cid,a.sid
order by a.cid,rank;

#16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select a.sid,a.total,count(b.total)+1 as rank 
from (select sid,sum(score) as total 
                        from sc 
                        group by sid )a
left join (select sid,sum(score) as total 
                        from sc 
                        group by sid )b
on b.total>a.total
group by a.sid;

#16.1查询学生的总成绩，并进行排名，总分重复时合并名次
select a.sid,a.total,count(distinct b.total)+1 as rank 
from (select sid,sum(score) as total 
                        from sc 
                        group by sid )a
left join (select sid,sum(score) as total 
                        from sc 
                        group by sid )b
on b.total>a.total
group by a.sid;

#17.统计各科成绩各分数段的人数：课程编号，课程名称，[100-85],[85-70],[70-60],[60-0]
select sc.cid,cname,
sum(case when score<=100 and score>=85 then 1 else 0 end)/count(*) as s1, 
sum(case when score<85 and score>=70 then 1 else 0 end)/count(*) as s2,
sum(case when score<70 and score>=60 then 1 else 0 end)/count(*) as s3,
sum(case when score<60 then 1 else 0 end)/count(*) as s4
from sc,course
where sc.cid=course.cid
group by sc.cid;

#18.查询各科成绩前三名的记录
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b
on a.cid=b.cid and b.score>a.score
group by a.cid,a.sid
having rank<=3
order by a.cid,a.score desc;

#19.查询每门课选修的学生人数
select cid,count(sid)
from sc
group by cid;

#20.查询出只修两门课程的学生学号和姓名
select student.sid,student.Sname
from student inner join sc
on sc.sid=student.sid
group by sc.sid
having count(cid)=2;

#21.查询男生、女生人数
select Ssex,count(*) as num
from student
group by Ssex;

#22.查询名字中带有风字的学生信息
select *
from student
where Sname like '%风%';

#24.查询1990年出生的学生名单
select *
from student
where year(sage)=1990;

#25.查询每门课的平均成绩，结果按平均成绩降序排列，若平均成绩相同时，则按课程号升序排列
select cid,avg(score) as avg_score
from sc
group by cid
order by avg_score desc,cid asc;

#26.查询平均成绩≥85的所有学生的学号、姓名和平均成绩
select student.sid,student.sname,avg(score) as avg_score
from student,sc
where student.sid=sc.sid
group by student.sid 
having avg_score>=85;

#27.查询课程名称为数学，且分数低于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.查询所有学生的课程及分数情况
select student.sid,student.sname,cid,score
from student left join sc
on student.sid=sc.sid;

#29.查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select student.sname,course.cname,sc.score
from student,sc,course
where student.sid=sc.sid
and course.cid=sc.cid
and student.sid not in (select sid from sc group by sid having max(score)<=70);

#30.查询存在不及格的课程
select distinct course.cname,sc.cid
from course,sc
where course.cid=sc.cid
having sc.cid not in (select sid from sc group by cid having min(score)>=60);

#31.查询课程编号为01且课程成绩在80分以上的学生学号和姓名
select student.sid,sname
from student,sc
where sc.sid=student.sid
and sc.score>80
and sc.cid='01';

#32.求每门课程的学生人数
select cid,count(sid) as num
from sc
group by cid;

#33.成绩不重复，查询选张三老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score 
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='张三'
order by score desc
limit 0,1; #降序排列取第一名

#34.成绩重复，查询选李四老师授课的学生中，成绩最高的学生信息及其成绩
select student.*,score 
from sc,student,course,teacher
where sc.sid=student.sid
and course.cid=sc.cid
and course.tid=teacher.tid
and tname='李四'
and sc.score=(select max(score)from sc,student,course,teacher
               where sc.sid=student.sid
               and course.cid=sc.cid
               and course.tid=teacher.tid
               and tname='李四') #找出最高分; 

#35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select *
from sc as a
left join sc as b
on a.sid=b.sid and b.cid<>a.cid
where a.score=b.score
group by a.cid;

#36.查询每门课程成绩最好的前两名
select a.*,count(b.sid)+1 as rank
from sc as a
left join sc as b 
on b.score>a.score and a.cid=b.cid #左连结b表
group by a.cid,a.sid
having rank<=2
order by cid;

#37.统计每门课程的选修人数，超过5人
select cid,count(sid) as num
from sc
group by cid
having num>5;

#38.查询至少选修了两门课程的学生学号
select sid
from sc
group by sid
having count(sid)>=2;

#39.查询选修了全部课程的学生信息
select student.*
from student,sc
where student.sid=sc.sid
and sc.cid in (select cid from course)
group by sc.sid
having count(sc.cid)=(select count(distinct cid) from course);

#40.查询各学生的年龄，只按年份计算
select sname,year(now())-year(sage) as Age
from student;

#41.按出生日期来算，当前月日<出生月日，则年龄减1
select sid,case when month(now())<month(sage) or 
         ( month(now())=month(sage) and day(now())<day(sage)  )
       then year(now())-year(sage)-1  else  year(now())-year(sage) end as Age
from student;

#42.查询本周过生日的同学
select date_format(now(),'%w'); #查询今天星期几
set @mon=(select subdate(curdate(),date_format(curdate(),'%w')-1)); #本周一的日期
set @sun=(select adddate(curdate(),7-date_format(now(),'%w'))); #本周日的日期
select sid
from student
where 
 date_format(sage,'%m-%d')<=date_format(@sun,'%m-%d')
 and date_format(sage,'%m-%d')>=date_format(@mon,'%m-%d');

#43.查询下周过生日的同学
set @Nextmon=(select adddate(curdate(),7-date_format(curdate(),'%w')+1)); #下周一的日期
set @Nextsun=(select adddate(curdate(),7-date_format(now(),'%w')+7)); #下周日的日期
select sid
from student
where 
 date_format(sage,'%m-%d')<=date_format(@Nextsun,'%m-%d')
 and date_format(sage,'%m-%d')>=date_format(@Nextmon,'%m-%d');

#44.查询本月过生日的同学
select sid
from student
where month(sage)=month(now());

#45.查询下月过生日的同学
select sid
from student
where month(sage)=month(now())+1;