学生表：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;