show databases ; use schoolSql; create table if not exists student( stu_id int(10), stu_name varchar(10), stu_age datetime, stu_gender varchar(10) ); insert into student values ('1','刘邦','1000-01-01','男'), ('2','吕雉','1000-01-02','女'), ('3','项羽','1000-01-03','男'), ('4','虞姬','1000-01-04','女'); create table if not exists course( cou_id int(10), cou_name varchar(10), tea_id int(10) ); insert into course values ('1','语文','2'), ('2','数学','3'), ('3','英语','1'); create table if not exists teacher( tea_id int(10) primary key , tea_name varchar(20) ); insert into teacher values ('1','张三'), ('2','李四'), ('3','王五'); select * from teacher; drop table result; create table if not exists result( stu_id int(10), cou_id int(10), score decimal (18,1) ); insert into result values ('1','2',90), ('1','3',74), ('2','1',85), ('2','2',65), ('2','3',75), ('3','1',43), ('3','2',50), ('4','1',88), ('4','2',43), ('4','3',99); # 1、查询"1"课程比"2"课程成绩高的学生的信息及课程分数 select s.* ,b.score as '1分数',c.score as '2分数' from student as s inner join result b on b.stu_id=s.stu_id and b.cou_id='1' inner join result c on c.stu_id=s.stu_id and c.cou_id='2' where b.score>c.score; #2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 select s.*,r.score '1分数',c.score '2分数' from student s left join result r on s.stu_id = r.stu_id and r.cou_id='1' left join result c on c.stu_id=s.stu_id and c.cou_id='2' where r.score<c.score; #3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 select s.stu_id,s.stu_name ,avg(score) as avg_score from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name HAVING avg_score>=60; #4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 select s.stu_id,s.stu_name,avg(score) as avg_score from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name having avg_score<60; #5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 select s.stu_id,s.stu_name,count(r.cou_id) as count,sum(r.score) as sum_score from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name; #6、查询"李"姓老师的数量 select count(t.tea_name) from teacher as t where t.tea_name like '李%'; #7、查询学过"张三"老师授课的同学的信息 select s.* from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id left join teacher t on c.tea_id = t.tea_id where t.tea_name='张三'; #8、查询没学过"张三"老师授课的同学的信息 /* 错误 select s.* from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id left join teacher t on c.tea_id = t.tea_id where t.tea_name!='张三';*/ select s2.* from student as s2 where s2.stu_id not in (select s.stu_id from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id left join teacher t on c.tea_id = t.tea_id where t.tea_name='张三'); #9、查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息 select s.* from student as s left join result r on s.stu_id = r.stu_id left join result r2 on s.stu_id=r2.stu_id where r.cou_id='1' and r2.cou_id='2'; #10、查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息 select s.* from student as s left join result r on s.stu_id = r.stu_id left join result r2 on s.stu_id=r2.stu_id where r.cou_id='1' and r2.cou_id is null ; #11、查询没有学全所有课程的同学的信息 #方法1 select s.* from student as s left join result r1 on s.stu_id = r1.stu_id and r1.cou_id='1' left join result r2 on s.stu_id=r2.stu_id and r2.cou_id='2' left join result r3 on s.stu_id=r3.stu_id and r3.cou_id='3' where r1.score is null or r2.score is null or r3.score is null ; #方法2 SELECT st.stu_id,st.stu_name,st.stu_age,stu_gender FROM student st LEFT JOIN result sc ON st.stu_id=sc.stu_id GROUP BY st.stu_id ,stu_name,stu_age,stu_gender HAVING COUNT(sc.score)<3; #12、查询至少有一门课与学号为"1"的同学所学相同的同学的信息 select distinct s.* from student as s left join result r on s.stu_id = r.stu_id where r.cou_id in( select r2.cou_id from result r2 left join student s2 on s2.stu_id = r2.stu_id where r2.stu_id='1' ); #13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 #使用GROUP_CONCAT函数和GROUP BY select s.* from student as s where stu_id in( select r.stu_id from (select * from result where stu_id='1') as r join result as r2 on r.cou_id=r2.cou_id group by r2.stu_id having count(*)=(select count(*) from result where r.stu_id='1' group by r.stu_id) ) and stu_id<>'1'; #14、查询没学过"张三"老师讲授的任一门课程的学生姓名 select s.stu_name from student as s where s.stu_id not in (select s2.stu_id from student as s2 left join result r on s2.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id left join teacher t on c.tea_id = t.tea_id where t.tea_name='张三' ); #15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 select s.stu_id, s.stu_name,avg(r.score) from student as s left join result r on s.stu_id = r.stu_id where r.stu_id in( select r.stu_id from result r where r.score<60 or r.score is null group by r.stu_id having count(1)>=2 ) group by s.stu_id, s.stu_name; #16、检索"01"课程分数小于60,按分数降序排列的学生信息 select s.* from student as s left join result r on s.stu_id = r.stu_id where r.stu_id in( select r.stu_id from result where r.cou_id='1' and r.score<60 ) order by r.score desc ; #17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 select s.stu_id,s.stu_name, (case when avg(r4.score) is null then 0 else avg(r4.score) end) "平均分", (case when r1.score is null then 0 else r1.score end) "语文", (case when r2.score is null then 0 else r2.score end) "数学", (case when r3.score is null then 0 else r3.score end) "英语" from student as s left join result r1 on s.stu_id = r1.stu_id and r1.cou_id='1' left join result r2 on s.stu_id=r2.stu_id and r2.cou_id='2' left join result r3 on s.stu_id=r3.stu_id and r3.cou_id='3' left join result r4 on s.stu_id=r4.stu_id group by s.stu_id, s.stu_name, (case when r1.score is null then 0 else r1.score end), (case when r2.score is null then 0 else r2.score end), (case when r3.score is null then 0 else r3.score end) order by avg(r4.score) desc ; #18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 # 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 select c.cou_id,c.cou_name,max(r.score) "最高分" ,min(r.score) "最高分",avg(r.score) "平均分", sum(case when r.score>=60 then 1 else 0 end)/count(1) "及格率", sum(case when r.score>=70 and r.score<80 then 1 else 0 end)/count(1) "中等率", sum(case when r.score>=80 and r.score<90 then 1 else 0 end)/count(1) "优良率", sum(case when r.score>=90 then 1 else 0 end)/count(1) "优秀率" from course as c left join result r on c.cou_id = r.cou_id group by c.cou_id, c.cou_name; #19、按各科成绩进行排序,并显示排名 select c.cou_name ,r.score from course as c left join result r on c.cou_id = r.cou_id group by c.cou_name, r.score order by c.cou_name, r.score desc; #20、查询学生的总成绩并进行排名 select s.stu_id,s.stu_name,sum(r.score) as "总成绩" from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name order by sum(r.score) desc; #21、查询不同老师所教不同课程平均分从高到低显示 select t.tea_id,t.tea_name,c.cou_id,avg(r.score) from teacher as t left join course c on t.tea_id = c.tea_id left join result r on c.cou_id = r.cou_id group by t.tea_id, t.tea_name,c.cou_id order by avg(r.score) desc; #22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 select t1.* from (select s.*,c.cou_id,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id inner join course c on r.cou_id = c.cou_id and c.cou_id='1' order by r.score desc limit 1,2)t1 union all select t2.* from (select s.*,c.cou_id,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id inner join course c on r.cou_id = c.cou_id and c.cou_id='2' order by r.score desc limit 1,2)t2 union all select t3.* from (select s.*,c.cou_id,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id inner join course c on r.cou_id = c.cou_id and c.cou_id='3' order by r.score desc limit 1,2) t3; #23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 select c.cou_id,c.cou_name, sum(case when r.score >=0 and r.score<60 then 1 else 0 end) as "0-60", sum(case when r.score>=60 and r.score<70 then 1 else 0 end) as "70-60", sum(case when r.score>=70 and r.score<85 then 1 else 0 end) as "85-70", sum(case when r.score>=85 and r.score<100 then 1 else 0 end) as"100-85", sum(case when r.score >=0 and r.score<60 then 1 else 0 end)/count(1) as "0-60百分比", sum(case when r.score>=60 and r.score<70 then 1 else 0 end)/count(1) as "70-60百分比", sum(case when r.score>=70 and r.score<85 then 1 else 0 end)/count(1) as "85-70百分比", sum(case when r.score>=85 and r.score<100 then 1 else 0 end)/count(1) as"100-85百分比" from course as c left join result r on c.cou_id = r.cou_id group by c.cou_id, c.cou_name; #24、查询学生平均成绩及其名次 #自己对自己左交,查看比自己分数高的有几个 select s.stu_name,avg(r.score) from student as s left join result r on s.stu_id = r.stu_id group by s.stu_name order by avg(r.score) desc ; #25、查询各科成绩前三名的记录 SELECT a.*,COUNT(b.cou_id)+1 AS ascore FROM result a LEFT JOIN result b ON a.cou_id=b.cou_id AND a.score<b.score GROUP BY 1,2,3 HAVING ascore<=3 ORDER BY a.cou_id,ascore; #方法二 select s.stu_name,c.cou_name,count(r2.cou_id)+1 as ascore from result r left join result r2 on r.cou_id=r2.cou_id and r.score<r2.score left join student s on r.stu_id = s.stu_id left join course c on r.cou_id = c.cou_id group by s.stu_name, c.cou_name having ascore<=3 order by c.cou_name,ascore; #26、查询每门课程被选修的学生数 select c.cou_name,count(r.cou_id) as "选修人数" from course as c left join result r on c.cou_id = r.cou_id group by c.cou_name; #27、查询出只有两门课程的全部学生的学号和姓名 select s.stu_id,s.stu_name from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name having count(r.cou_id)=2; #28、查询男生、女生人数 select s.stu_gender,count(1) from student as s group by s.stu_gender; #29、查询名字中含有"羽"字的学生信息 select s.* from student as s where s.stu_name like '%羽%'; #30、查询同名同姓学生名单,并统计同名人数 select s.stu_name,count(1) from student as s group by s.stu_name having count(1)>1; #31、查询1000年出生的学生名单(注:Student表中Sage列的类型是datetime) select s.* from student as s where year(s.stu_age)=1000; #32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号 select r.cou_id,c.cou_name,avg(r.score) as avg_score from result as r left join course c on r.cou_id = c.cou_id group by r.cou_id, c.cou_name order by avg_score desc ,r.cou_id ; #33、查询平均成绩大于等于80的所有学生的学号、姓名和平均成绩 select s.stu_id,s.stu_name,avg(r.score) "平均分" from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name having avg(r.score)>=80; #34、查询课程名称为"数学",且分数低于60的学生姓名和分数 select s.stu_id,s.stu_name,r.score from student as s left join result r on s.stu_id = r.stu_id inner join course c on r.cou_id = c.cou_id where c.cou_name='数学' having r.score<60; #35、查询所有学生的课程及分数情况 select s.stu_id,s.stu_name,c.cou_id,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id group by s.stu_id, s.stu_name, c.cou_id, c.cou_name, r.score; #36、查询任何一门课程成绩在80分以上的姓名、课程名称和分数 select s.stu_name,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id group by s.stu_name, c.cou_name, r.score having r.score>80; #37、查询不及格的课程 select s.stu_id,s.stu_name,c.cou_name,r.score from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id where r.score<60 group by s.stu_id, s.stu_name, c.cou_name, r.score; #38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 select s.stu_id,s.stu_name,r.score from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id where c.cou_id='1' and r.score>80; #39、求每门课程的学生人数 select c.cou_name,count(c.cou_id) from course as c left join result r on c.cou_id = r.cou_id group by c.cou_name; #40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 select s.*,r.score from student as s left join result r on s.stu_id = r.stu_id left join course c on r.cou_id = c.cou_id left join teacher t on c.tea_id = t.tea_id where t.tea_name='张三' order by r.score desc limit 1; #41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select r.stu_id,r.cou_id,r.score from result as r left join result r2 on r.score=r2.score and r.cou_id <>r2.cou_id #group by 1,2,3 group by r.stu_id, r.cou_id, r.score having count(r2.stu_id)>0; #方法2 select r.* from result as r left join result r2 on r.score=r2.score and r.cou_id <>r2.cou_id group by 1,2,3 having count(r2.stu_id)>0; #42、查询每门功成绩最好的前两名 select r1.stu_id,r1.cou_id,r1.score from result as r1 left join result r2 on r1.cou_id=r2.cou_id and r1.score<r2.score group by r1.stu_id, r1.cou_id, r1.score having count(r2.cou_id)<2 order by r1.cou_id,r1.score desc; #43、统计每门课程的学生选修人数(超过2人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列 select c.cou_id,count(r.cou_id) as acount from course as c left join result r on c.cou_id = r.cou_id group by c.cou_id having acount>2 order by acount desc ,c.cou_id; #44、检索至少选修两门课程的学生学号 select s.stu_id,s.stu_name ,count(r.cou_id) as acount from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name having acount>=2; #45、查询选修了全部课程的学生信息 select s.stu_id,s.stu_name,s.stu_age,s.stu_gender from student as s left join result r on s.stu_id = r.stu_id group by s.stu_id, s.stu_name, s.stu_age, s.stu_gender having count(r.cou_id)=3; #46、查询各学生的年龄 select s.*,(YEAR(CURDATE())-YEAR(s.stu_age))as "年龄" from student as s; #47、查询本周过生日的学生 select s.* from student as s where weekofyear(s.stu_age)=weekofyear(curdate()); #48、查询下周过生日的学生 select s.* from student as s where weekofyear(s.stu_age)=weekofyear(curdate())+1; #49、查询本月过生日的学生 select s.* from student as s where month(s.stu_age)=month(curdate()); #50、查询上月过生日的学生 select s.* from student as s where month(s.stu_age)=month(curdate())-1;
乐于奉献共享,帮助你我他!