#1.学生表 Student create table 022Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); insert into 022Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into 022Student values('02' , '钱电' , '1990-12-21' , '男'); insert into 022Student values('03' , '孙风' , '1990-05-20' , '男'); insert into 022Student values('04' , '李云' , '1990-08-06' , '男'); insert into 022Student values('05' , '周梅' , '1991-12-01' , '女'); insert into 022Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into 022Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into 022Student values('09' , '张三' , '2017-12-20' , '女'); insert into 022Student values('10' , '李四' , '2017-12-25' , '女'); insert into 022Student values('11' , '李四' , '2017-12-30' , '女'); insert into 022Student values('12' , '赵六' , '2017-01-01' , '女'); insert into 022Student values('13' , '孙七' , '2022-01-01' , '女');
#2.科目表 Course create table 022Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); insert into 022Course values('01' , '语文' , '02'); insert into 022Course values('02' , '数学' , '01'); insert into 022Course values('03' , '英语' , '03');
#3.教师表 Teacher create table 022Teacher(TId varchar(10),Tname varchar(10)); insert into 022Teacher values('01' , '张三'); insert into 022Teacher values('02' , '李四'); insert into 022Teacher values('03' , '王五');
#4.成绩表 SC create table 022SC(SId varchar(10),CId varchar(10),score decimal(18,1)); insert into 022SC values('01' , '01' , 80) ; insert into 022SC values('01' , '02' , 90) ; insert into 022SC values('01' , '03' , 99) ; insert into 022SC values('02' , '01' , 70) ; insert into 022SC values('02' , '02' , 60) ; insert into 022SC values('02' , '03' , 80) ; insert into 022SC values('03' , '01' , 80) ; insert into 022SC values('03' , '02' , 80) ; insert into 022SC values('03' , '03' , 80) ; insert into 022SC values('04' , '01' , 50) ; insert into 022SC values('04' , '02' , 30) ; insert into 022SC values('04' , '03' , 20) ; insert into 022SC values('05' , '01' , 76) ; insert into 022SC values('05' , '02' , 87) ; insert into 022SC values('06' , '01' , 31) ; insert into 022SC values('06' , '03' , 34) ; insert into 022SC values('07' , '02' , 89) ; insert into 022SC values('07' , '03' , 98) ;
#5.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数 select t.,t2. from 022student t ,022sc t2 ,022sc t3 where t.Sid = t2.Sid and t.sid = t3.sid and t2.cid = '01' and t3.cid = '02' and t2.score > t3.score ; #6.查询同时存在" 01 "课程和" 02 "课程的情况 select * from 022student t ,022sc t2 ,022sc t3 where t.Sid = t2.Sid and t.sid = t3.sid and t2.cid = '01' and t3.cid = '02' ;
#7.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) select * from 022student t ,022sc t2 left join 022sc t3 on t3.cid = '02' and t2.sid = t3.sid where t.Sid = t2.Sid and t2.cid = '01' ;
#8.查询不存在" 01 "课程但存在" 02 "课程的情况 select * from 022student t ,022sc t2 ,022sc t3 where t.Sid = t2.Sid and t.sid = t3.sid and t2.cid != '01' and t3.cid = '02' ;
#9.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 select t.*,avg(s.score) sco from 022student t,022sc s where t.Sid = s.Sid group by t.Sid having sco >=60 ;
#10.查询在 SC 表存在成绩的学生信息 select * from 022student s where exists(select 1 from 022sc where sid = s.sid) ;
#11.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) select t.*,sum(s.score) ,count(s.Cid) from 022student t,022sc s where t.Sid = s.Sid group by t.Sid ;
#12.查有成绩的学生信息 select * from 022student s where exists(select 1 from 022sc where sid = s.sid) ;
#13. 查询「李」姓老师的数量? select count(*) from 022teacher where Tname like '李%';
#14.查询学过「张三」老师授课的同学的信息? select * from 022student s,022sc s1,022course c,022teacher t where s.sid = s1.Sid and s1.Cid = c.Cid and c.Tid = t.tid and t.Tname = '张三' ;
#没学过张三的学生信息 select * from 022student s where not exists (select * from 022sc s1,022course c,022teacher t where s.sid = s1.Sid and s1.Cid = c.Cid and c.Tid = t.tid and t.Tname = '张三') ;
#15.查询没有学全所有课程的同学的信息 select * from 022student t,022sc s where t.sid = s.sid group by t.sid having count(s.cid) < (select count(*) from 022course) ;
#16.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息? select DISTINCT s.* from 022student s,022sc s1 where s.sid = s1.sid and s1.Cid = any(select cid from 022sc where sid = '01') and s.Sid != '01' ;
#17.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息? select * from 022student s ,022sc s1 join 022sc s2 on s2.Sid = '01' and s1.Cid = s2.Cid where s.sid = s1.Sid group by s.sid having count(s1.cid) = (select count(*) from 022sc where sid = '01') ;
#18.查询没学过"张三"老师讲授的任一门课程的学生姓名? select * from 022student s where not exists (select * from 022sc t1,022course t2,022teacher t3 where t1.sid = s.sid and t2.cid = t1.cid and t3.tid = t2.tid and t3.tname = '张三') ;
#19.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩? select s.sid,count(s2.score) from 022student s ,022sc s2 where s.Sid = s2.Sid and s.sid = '5614' and not exists(select * from 022sc s3 where s3.sid = s.Sid and s3.score <60 group by s3.sid having count(*) >=2) group by s.Sid ;
#20.检索" 01 "课程分数小于 60，按分数降序排列的学生信息 select * from 022student s ,022sc s1 where s.sid = s1.Sid and s1.score < 60 and s1.Cid = '01' order by s1.score desc;
#21.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 select * from 022sc s ,( select s1.sid,avg(s1.score) ag from 022sc s1 group by s1.Sid ) t where t.sid = s.sid order by t.ag desc ;
#22.查询各科成绩最高分、最低分和平均分： select a.cid, max(a.score) as 最高分, min(a.score) as 最低分, avg(a.score) as 平均分, count() as 选修人数, sum(case when a.score>=60 then 1 else 0 end)/count() as 及格率, sum(case when a.score>=70 and a.score<80 then 1 else 0 end)/count() as 中等率, sum(case when a.score>=80 and a.score<90 then 1 else 0 end)/count() as 优良率, sum(case when a.score>=90 then 1 else 0 end)/count() as 优秀率 from 022sc a group by a.cid order by count() desc,a.cid ;
#23.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select cid,sid,score,row_number()over(partition by cid order by sid desc) rank from 022sc order by cid,rank;
select cid,sid,score,rank()over(partition by cid order by score desc)as rank from 022sc order by cid,rank;
select cid,sid,score,dense_rank()over(partition by cid order by score desc)as rank from 022sc order by cid,rank;
select s1.Sid,s1.cid,count(s2.score)+1 rank from 022sc s1 left join 022sc s2 on s1.Sid != s2.sid and s1.cid = s2.cid and s1.score < s2.score group by s1.sid,s1.Cid order by s1.Cid,rank;
#24.按各科成绩进行排序，并显示排名， Score 重复时合并名次 select s1.Sid,s1.cid,count(DISTINCT s2.score)+1 rank from 022sc s1 left join 022sc s2 on s1.Sid != s2.sid and s1.cid = s2.cid and s1.score < s2.score group by s1.sid,s1.Cid order by s1.Cid,rank;
#25.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 select t1.* ,count(t2.sco)+1 rank from (select sid,sum(score) sco from 022sc group by sid) t1 left join (select sid,sum(score) sco from 022sc group by sid) t2 on t1.sid!=t2.sid and t2.sco > t1.sco group by t1.sid order by rank,t1.sid,t2.sco desc ;
#26.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺 select b.*,rank()over(order by b.total desc) as rank from( select sid,sum(score) as total from 022sc group by sid ) b order by rank ;
#27.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比 select course.cname, course.cid, sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]", sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]", sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]", sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]" from 022sc as sc left join 022course course on sc.cid = course.cid group by cid;
#28.查询各科成绩前三名的记录 select * from 022sc s WHERE (select count(*) from 022sc where cid = s.cid and score > s.score) <3 ;
#29.列出同一门课内所有分数比较的情况 select a.cid,a.sid,a.score from 022sc a left join 022sc b on a.cid = b.cid and a.score< b.score group by a.cid,a.sid having count(b.cid) <3 order by a.cid ;
select c.cid,c.sid,c.score from( select *,rank()over (partition by a.cid order by score desc) b from 022sc a ) c where c.b<=3 order by c.cid,c.score desc ;
#30.查询每门课程被选修的学生数 select * , count(sid) from 022sc group by cid ;
#31.查询出只选修两门课程的学生学号和姓名 select * from 022student s1 where exists(select * from 022sc s where s.sid = s1.sid having count(*)=2) ;
#32.查询男生、女生人数 select ssex,count(*) from 022student group by Ssex;
#33.查询名字中含有「风」字的学生信息 select * from 022student s where s.Sname like '%风%'; select * from 022student s where s.Sname REGEXP '.风.';
#34.查询同名同性学生名单，并统计同名人数 select sname,count(1) from 022student group by Sname;
#35.查询 1990 年出生的学生名单 select * from 022student where year(Sage) = '1990' ;
#36.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 select cid,avg(score) a from 022sc group by cid order by a desc ,cid asc;
#37.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 select s1.sid,avg(s1.score) a from 022student s ,022sc s1 where s.sid = s1.Sid group by s1.sid having a >= 85 order by a desc ,s1.sid asc;
#38.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 select * from 022student s,022sc s1,022course c where s.sid = s1.Sid and s1.cid= c.Cid and s1.score <= 60 and c.Cname = '数学' ;
#39.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） select * from 022student s left join 022sc s1 on s.sid= s1.sid ;
#40.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 select * from 022student s ,022sc s1 where s.Sid = s1.Sid and s1.score >70 ;
#41.查询不及格的课程 select cid from 022sc where score< 60 group by cid;
#42.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名 select * from 022student s ,022sc s1 where s.sid = s1.Sid and s1.score > 80 ;
#43.求每门课程的学生人数 select c.cid,count(s.sid) from 022course c left join 022sc s on c.Cid = s.Cid group by c.cid
#44.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 select * from 022student s ,022sc s1,022course c ,022teacher t where s.sid = s1.sid and s1.cid = c.Cid and c.tid = t.tid and t.tname = '张三' order by s1.score DESC limit 1 ;
#45.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 select * from 022student s ,022sc s1,022course c ,022teacher t where s.sid = s1.sid and s1.cid = c.Cid and c.tid = t.tid and t.tname = '张三' and s1.score = (select max(score) from 022sc where cid= c.cid) ;
#46.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select * from 022student s ,022sc s1,022sc s2 where s.sid = s1.sid and s1.cid != s2.cid and s1.score = s2.score group by s1.sid,s1.cid ;
#47.查询每门功成绩最好的前两名 select * from 022sc s where exists (select * from 022sc s1 where s1.sid != s.sid and s.Cid = s1.cid and s.score > s1.score having count(*) <2 ) ;
#48.统计每门课程的学生选修人数（超过 5 人的课程才统计）。 select c.cid,count(s.sid) from 022course c left join 022sc s on c.Cid = s.Cid group by c.cid having count(s.sid)>5 ;
#49.检索至少选修两门课程的学生学号 select * from 022student s1 where exists(select * from 022sc s where s.sid = s1.sid having count(*)>=2) ;
#50.查询选修了全部课程的学生信息 select * from 022student s1 where exists(select * from 022sc s where s.sid = s1.sid having count()=( select count() from 022course)) ;
#51.查询各学生的年龄，只按年份来算 select student.sid as 学生编号,student.Sname as 学生姓名, timestampdiff(YEAR,student.Sage,curdate()) as 学生年龄 from 022student student ;
#52.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一 select *,year(CURDATE())-year(022student.sage)- case when DATE_FORMAT(CURDATE(),'%m%d')<DATE_FORMAT(022student.sage,'%m%d') then 1 else 0 end from 022student ;
select student.SId as 学生编号,student.Sname as 学生姓名,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄 from 022student student ;
#53.查询本周过生日的学生 select * from 022student student where weekofyear(student.Sage)=weekofyear(curdate()); #54.查询下周过生日的学生 select * from 022student student where weekofyear(student.Sage)=weekofyear(curdate())+1;
#55.查询本月过生日的学生 select * from 022student student where month(student.sage)=month(curdate());
select * from 022student student where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM CURDATE());
#56.查询下月过生日的学生 select * from 022student student where month(student.sage)=month(curdate())+1;
select * from 022student student where EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
