MySql 语句练习50题
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SELECT st.*,sc1.s_score "语文",sc2.s_score "数学" FROM student st left join score sc1 on st.s_id = sc1.s_id and sc1.c_id ="01" left join score sc2 on st.s_id = sc2.s_id and sc2.c_id ="02" where sc1.s_score > sc2.s_score -- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT st.*,sc.s_score "语文", sc1.s_score "数学" from student st left join score sc on st.s_id = sc.s_id and sc.c_id="01" left join score sc1 on st.s_id =sc1.s_id and sc1.c_id="02" where sc.s_score < sc1.s_score -- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 select st.s_id,st.s_name,Round(avg(sc.s_score),2) "平均分" from student st left join score sc on st.s_id = sc.s_id group by st.s_id having avg(sc.s_score) > 60 -- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的) select st.s_id,st.s_name,(case WHEN Round(avg(sc.s_score),2) is null then 0 else Round(avg(sc.s_score),2) end) "平均分" from student st left join score sc on st.s_id = sc.s_id group by st.s_id HAVING avg(sc.s_score) < 60 or avg(sc.s_score) is null -- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 select st.s_id,st.s_name,count(c.c_id) "课程总数", (case WHEN sum(sc.s_score) is null or sum(sc.s_score) = "" then 0 else sum(sc.s_score) end) "总成绩" from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id group by st.s_id -- 6、查询"李"姓老师的数量 select t.t_name,count(1) from teacher t group by t.t_name having t.t_name like "李%" -- 7、查询学过"张三"老师授课的同学的信息 select st.* from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id left join teacher t on c.t_id = t.t_id where t.t_name = "张三" -- 8、查询没学过"张三"老师授课的同学的信息 -- 张三老师教的课 select s.* from student s where s.s_id not in ( select st.s_id from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id left join teacher t on c.t_id = t.t_id where t.t_name = "张三" ) -- 1.先查询张三老师教的所有课程 -- 2.然后通过所有可能,拿到成绩表对应的学生id -- 3.去掉所有张三老师教过的学生id -- 第1步 select * from course c left join teacher t on c.t_id = t.t_id where t.t_name = "张三" -- 第2步 select * from score sc where sc.c_id in ( select c.c_id from course c left join teacher t on c.t_id = t.t_id where t.t_name = "张三" ) -- 第3步 select * from student st where st.s_id not in ( select sc.s_id from score sc where sc.c_id in ( select c.c_id from course c left join teacher t on c.t_id = t.t_id where t.t_name = "张三" ) ) -- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 -- 先查询出"02"编号的学生,然后再重新查询学生表,将查询出来的学生当作条件,再查询"01"编号 select st.* from student st LEFT join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id WHERE c.t_id = "01" and st.s_id in ( select s.s_id from student s LEFT join score sc on s.s_id = sc.s_id left join course c on sc.c_id = c.c_id WHERE c.t_id = "02" ) -- 查询成绩表,使用if条件,判断课程id = 01 或者 =02 就 +1 -- 使用聚合函数必须分组 / 多行数据合并,必须分组 select st.* from student st left join score sc on st.s_id = sc.s_id group by st.s_id having sum(if(sc.c_id = "01" or sc.c_id = "02",1,0)) > 1 -- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 select st.* from student st left join score sc on st.s_id = sc.s_id where sc.c_id = "01" and st.s_id not in( select st.s_id from student st left join score sc on st.s_id = sc.s_id where sc.c_id = "02" ) -- 11、查询没有学全所有课程的同学的信息 -- 太复杂,下次换一种思路,看有没有简单点方法 -- 此处思路为查学全所有课程的学生id,再内联取反面 -- 方式一: select s.s_id from student s where s.s_id not in ( select st.s_id from student st left join score sc on st.s_id = sc.s_id left join score sc1 on st.s_id = sc1.s_id left join score sc2 on st.s_id = sc2.s_id where sc.c_id ="01" and sc1.c_id ="03" and sc2.c_id ="02" group by st.s_id ) -- 方式二: select st.* from student st where st.s_id not in( select s.s_id from student s left join score sc on s.s_id = sc.s_id where sc.c_id = "01" and s.s_id in( select s2.s_id from student s2 left join score sc on s2.s_id = sc.s_id where sc.c_id = "02" and s2.s_id in( select s3.s_id from student s3 left join score sc on s3.s_id = sc.s_id where sc.c_id = "03" ) ) ) -- 方式三:更灵活 先回课程的总数量,然后,成绩表通过学生id分组,再获取学生的课程数量,进行于课程表的总数量做对比 select st.* from student st left join score sc on st.s_id = sc.s_id group by st.s_id having count(c_id) < (select count(1) from course) -- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息\ select s.* from student s left join score scr on s.s_id = scr.s_id where scr.c_id in ( select sc.c_id from student st left join score sc on st.s_id = sc.s_id where st.s_id ="01" ) group by s.s_id -- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 select s.* from student s left join score s1 on s.s_id = s1.s_id group by s.s_id having GROUP_CONCAT(s1.c_id) = ( select GROUP_CONCAT(sc.c_id) from student st left join score sc on st.s_id = sc.s_id WHERE st.s_id = "01") -- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 select st.s_name from student st left join score sc on st.s_id = sc.s_id where st.s_id not in( select s1.s_id from score s1 left join course c on s1.c_id = c.c_id left join teacher t on c.t_id = t.t_id where t.t_name = "张三" group by st.s_id ) group by st.s_id -- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 select st.s_id, st.s_name,avg(sc.s_score) from student st left join score sc on st.s_id = sc.s_id where st.s_id in ( select s1.s_id from score s1 where s1.s_score < 60 or s1.s_score is null group by s1.s_id having count(s1.s_id) >= 2 ) group by st.s_id -- 16、检索"01"课程分数小于60,按分数降序排列的学生信息 select st.*,sc.s_score from student st left join score sc on st.s_id = sc.s_id where sc.c_id = "01" and sc.s_score < 60 ORDER BY sc.s_score desc -- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 -- 可加round,case when then else end 使显示更完美 select st.*,sc.s_score "数学", sc2.s_score "语文", sc3.s_score "英语", avg(sc4.s_score) "平均分" from student st left join score sc on st.s_id = sc.s_id and sc.c_id="01" left join score sc2 on st.s_id = sc2.s_id and sc2.c_id="02" left join score sc3 on st.s_id = sc3.s_id and sc3.c_id="03" left join score sc4 on st.s_id = sc4.s_id -- 不带查询条件,当前表所有数据 GROUP BY st.s_id order by avg(sc4.s_score) desc -- 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 -- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 select c.c_id "课程ID", c.c_name "课程名称", max(sc.s_score) "最高分", min(sc2.s_score) "最低分", avg(sc3.s_score) "平均分" , ((SELECT count(s_score) from score where s_score >= 60 and c_id = c.c_id) / (SELECT count(s_score) from score where c_id = c.c_id)) "及格率" , ((SELECT count(s_score) from score where s_score >= 70 and s_score < 80 and c_id = c.c_id) / (SELECT count(s_score) from score where c_id = c.c_id)) "中等率" , ((SELECT count(s_score) from score where s_score >= 80 and s_score < 90 and c_id = c.c_id) / (SELECT count(s_score) from score where c_id = c.c_id)) "优良率" , ((SELECT count(s_score) from score where s_score >= 90 and c_id = c.c_id) / (SELECT count(s_score) from score where c_id = c.c_id)) "优秀率" from course c left join score sc on sc.c_id = c.c_id left join score sc2 on sc2.c_id = c.c_id left join score sc3 on sc3.c_id = c.c_id group by c.c_id -- 20、查询学生的总成绩并进行排名 select st.*,(case WHEN sum(sc.s_score) is null then 0 else sum(sc.s_score) end) "总成绩" from student st left join score sc on st.s_id = sc.s_id group by st.s_id order by sum(sc.s_score) desc -- 21、查询不同老师所教不同课程平均分从高到低显示 select t.t_id,t.t_name,c.c_name, avg(sc.s_score) from score sc left join course c on sc.c_id = c.c_id left join teacher t on c.t_id = t.t_id group by t.t_id,c.c_name order by avg(sc.s_score) desc -- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 select s.* from ( select a.* from (select st.*,c.c_id, c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id = "01" order by sc.s_score desc limit 1,2) a UNION ALL select b.* from (select st.*,c.c_id, c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id = "02" order by sc.s_score desc limit 1,2) b UNION ALL select c.* from (select st.*,c.c_id, c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id = "03" order by sc.s_score desc limit 1,2) c ) s -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 select c.c_id,c.c_name, ((select count(1) from score s1 where s1.s_score BETWEEN 85 and 100 and s1.c_id = c.c_id) / (select count(1) from score s1 where s1.c_id = c.c_id)) "[100-85]", ((select count(1) from score s1 where s1.s_score BETWEEN 70 and 85 and s1.c_id = c.c_id) / (select count(1) from score s1 where s1.c_id = c.c_id)) "[85-70]", ((select count(1) from score s1 where s1.s_score BETWEEN 60 and 70 and s1.c_id = c.c_id) / (select count(1) from score s1 where s1.c_id = c.c_id)) "[70-60]", ((select count(1) from score s1 where s1.s_score BETWEEN 0 and 60 and s1.c_id = c.c_id) / (select count(1) from score s1 where s1.c_id = c.c_id)) "[0-60]" from course c GROUP BY c.c_id -- 24、查询学生平均成绩及其名次 select st.*,sum(sc.s_score) "总成绩" from student st left join score sc on st.s_id = sc.s_id group by st.s_id order by sum(sc.s_score) desc -- 25、查询各科成绩前三名的 学生信息及该课程成绩 select a.* from ( select st.*,c.c_id,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id ="01" order by sc.s_score desc limit 0,3) a union all select b.* from ( select st.*,c.c_id,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id ="02" order by sc.s_score desc limit 0,3 ) b union all select c.* from ( select st.*,c.c_id,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id inner join course c on sc.c_id = c.c_id and c.c_id ="03" order by sc.s_score desc limit 0,3) c -- 26、查询每门课程被选修的学生数 select c.c_id,c.c_name,count(sc.c_id) from score sc left join course c on sc.c_id = c.c_id group by c.c_id -- 27、查询出只有两门课程的全部学生的学号和姓名 select st.*,count(sc.c_id) from student st left join score sc on st.s_id = sc.s_id group by st.s_id having count(sc.c_id) = 2 -- 28、查询男生、女生人数 select st.s_sex,count(st.s_id) from student st GROUP BY st.s_sex -- 29、查询名字中含有"风"字的学生信息 select st.* from student st where st.s_name like "%风%" -- 30、查询同名同性学生名单,并统计同名人数 select st.*,count(1) from student st group by st.s_name,st.s_sex having count(1) > 1 -- 31、查询1990年出生的学生名单 select st.* from student st where st.s_birth like "1990%" -- 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列 select c.c_name, avg(sc.s_score) from score sc left join course c on sc.c_id = c.c_id group by c.c_name order by avg(sc.s_score) desc, c.c_id asc -- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 select st.*,avg(sc.s_score) from student st left join score sc on st.s_id = sc.s_id GROUP BY st.s_id having avg(sc.s_score) > 85 -- 34、查询课程名称为"数学",且分数低于60的学生姓名和分数 select st.*,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id where sc.s_score < 60 and c.c_name = "数学" -- 35、查询所有学生的课程及分数情况; select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id order by st.s_id,c.c_name -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数 select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id where sc.s_id in ( select sc2.s_id from score sc2 left join course c2 on sc2.c_id = c2.c_id where sc.s_score >= 70 group by sc2.s_id ) -- 37、查询不及格的课程 select st.s_id,st.s_name,sc.s_score,c.c_name from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id where sc.s_score < 60 -- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id where sc.c_id = "01" and sc.s_score >= 80 -- 39、求每门课程的学生人数 select c.c_name,count(1) from score sc left join course c on sc.c_id = c.c_id group by c.c_id -- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id left join teacher t on c.t_id = t.t_id where t.t_name = "张三" order by sc.s_score desc limit 0,1 -- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 select st.*,c.c_id,c.c_name from student st left join score sc on st.s_id = sc.s_id left join course c on sc.c_id = c.c_id where st.s_id in ( select st2.s_id from student st2 left join score sc2 on st2.s_id = sc2.s_id where sc.s_score = sc2.s_score and sc.c_id != sc2.c_id ) -- 42、查询每门功成绩最好的前两名 select * from ( select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id where c.c_id = "01" order by sc.s_score desc limit 0,2) a union all select * from ( select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id where c.c_id = "02" order by sc.s_score desc limit 0,2) b union all select * from ( select st.*,c.c_name,sc.s_score from student st left join score sc on st.s_id = sc.s_id left join course c on c.c_id = sc.c_id where c.c_id = "03" order by sc.s_score desc limit 0,2) c -- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列, -- 若人数相同,按课程号升序排列 select c.c_id,c.c_name,count(sc.c_id) from score sc left join course c on sc.c_id = c.c_id group by c.c_id having count(sc.c_id) > 5 order by count(sc.c_id) desc,c.c_id asc -- 44、检索至少选修两门课程的学生学号 select st.*,count(sc.c_id) from student st left join score sc on st.s_id = sc.s_id group by st.s_id having count(sc.c_id) >= 2 -- 45、查询选修了全部课程的学生信息 select st.*,count(sc.c_id) from student st left join score sc on st.s_id = sc.s_id group by st.s_id having count(sc.c_id) = (select count(1) from course) -- 46、查询各学生的年龄 select st.s_name,timestampdiff(year,st.s_birth,now()) from student st -- -- 47、查询本周过生日的学生 -- 此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w), -- 再判断本周是否会持续到下一个月进行判断,太麻烦,不会写 select st.*,week(st.s_birth) from student st where week(now()) = week(DATE_FORMAT(s_birth,"%y%m%d")) -- 48、查询下周过生日的学生 select st.* from student st where week(now())+1=week(date_format(st.s_birth,'%Y%m%d')) -- 49、查询本月过生日的学生 select st.*, month(now()) from student st where month(now()) = month(date_format(st.s_birth,"%y%m%d")) -- 50、查询下月过生日的学生 -- 注意:当 当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模 select st.*, month(now()) from student st where if(month(now()) +1 = 13,1,month(now()) +1) = month(date_format(st.s_birth,"%y%m%d"))