-- 查询所有学生的学号、姓名、选课数和总成绩-- 当出现group by时,select后面跟着的字段应当是:-- 1.出现在group by后面的内容;2.统计函数;(这样才更严谨)-- 左连接是为了防止选课表中缺失的学生记录select stu.s_id, stu.s_name,count(sc.c_id),sum(casewhen sc.s_score isNULLthen0else sc.s_score end)from score as sc
leftjoin student as stu
on stu.s_id=sc.s_id
groupby stu.s_id, stu.s_name
04:查询姓张的老师有多少个
selectcount(t_id)from teacher where t_name like'张%'-- 若希望计数结果中不存在重复姓名的话,就可以使用distinct关键词selectcount(distinct t_id)from teacher where t_name like'张%'
05:查询所有未选过张三老师课程的学生
-- 查询所有未选过张三老师课程的学生-- 思路是:1.查询出选择张三老师课程的学生;2.去除这些学生就是结果。-- 方法1:嵌套方法select s.s_id, s.s_name from student as s where s.s_id notin(selectdistinct sc.s_id from score as sc where sc.c_id=(select c.c_id from course as c where c.t_id =(select t.t_id from teacher as t where t.t_name='张三')));-- 方法2:连接方法select s.s_id,s.s_name from student as s where s.s_id notin(select sc.s_id from score as sc
innerjoin course as c on c.c_id = sc.c_id
innerjoin teacher as t on t.t_id = c.t_id
where t.t_name ='张三');
06:查询选修了张三老师全部课程的学生学号和姓名
-- 查询选修了张三老师全部课程的学生学号和姓名-- 思路1:查询选修课程数等于张三老师课程数的学生select sc.s_id,s.s_name from score as sc
innerjoin course as c on c.c_id = sc.c_id
innerjoin teacher as t on t.t_id = c.t_id
innerjoin student as s on s.s_id = sc.s_id
where t.t_name='张三'groupby sc.s_id havingcount(sc.c_id)=(selectcount(c.c_id)from course as c
innerjoin teacher as t on t.t_id = c.t_id
where t.t_name ='张三')
07:查询选修过课程01也选修过课程02的学生学号和姓名
-- 查询选修过课程01也选修过课程02的学生学号和姓名select sc1.s_id, sc1.c_id, sc2.c_id from(select*from score where c_id ='01')as sc1
innerjoin(select*from score where c_id ='02')as sc2
on sc1.s_id = sc2.s_id
08:查询课程编号为02的总成绩
-- 查询课程编号为02的总成绩-- 方法1selectsum(sc.s_score)from score as sc
where sc.c_id ='02';-- 方法 2selectsum(sc.s_score)from score as sc
groupby sc.c_id having sc.c_id ='02';
09:查询所有课程小于60分的学生学号和姓名
-- 查询所有课程小于60分的学生学号和姓名-- 方法1:最高分小于60分的学生即可select s.s_id, s.s_name from score as sc
innerjoin student as s on s.s_id = sc.s_id
groupby sc.s_id havingmax(sc.s_score)<60;-- 方法2:选修课程数目等于成绩小于60分的课程数目-- 方法2实现1:select sc1.s_id,s.s_name from score as sc1
innerjoin student as s on s.s_id = sc1.s_id
groupby sc1.s_id havingcount(sc1.c_id)=(selectcount(sc.c_id)from score as sc where sc1.s_id = sc.s_id groupby sc.s_id);-- 方法2实现2:select s.s_id,s.s_name from(select sc1.s_id,count(sc1.c_id)as cnt from score as sc1 groupby sc1.s_id)as sc_c1
innerjoin(select sc2.s_id,count(sc2.c_id)as cnt from score as sc2 where sc2.s_score<60groupby sc2.s_id)as sc_c2
on sc_c1.s_id = sc_c2.s_id
innerjoin student as s on s.s_id = sc_c1.s_id
where sc_c1.cnt = sc_c2.cnt;
10:查询没有学全所有课程的学生学号和姓名
-- 10-查询没有学全所有课程的学生学号和姓名-- 方法1:选修课程数目等于课程总数(这个方法有错误)select sc.s_id, s.s_name from score as sc
innerjoin student as s on s.s_id = sc.s_id
groupby sc.s_id havingcount(sc.c_id)<(selectcount(c.c_id)from course as c);-- 方法2:修正版(student可能一门课也没选)select s.s_id,s.s_name from student as s
leftjoin score as sc on sc.s_id = s.s_id
groupby s.s_id havingcount(sc.c_id)<(selectcount(c.c_id)from course as c);
11:查询至少选修了和学生01有一门相同课程的学生学号和姓名
-- 查询至少选修了和学生01有一门相同课程的学生学号和姓名-- 方法1:使用inselect s.s_id,s.s_name from student as s where s.s_id in(selectdistinct sc.s_id from score as sc where sc.c_id in(select sc1.c_id from score as sc1 where sc1.s_id='01')and sc.s_id !='01');-- 方法2:使用inner joinselect s.s_id,s.s_name from student as s
innerjoin(selectdistinct sc.s_id from score as sc where sc.c_id in(select sc1.c_id from score as sc1 where sc1.s_id='01')and sc.s_id !='01')as b on s.s_id = b.s_id;
12:查询和01同学选修的课程完全一样的其他同学的学号和姓名
-- 查询和01同学选修的课程完全一样的其他同学的学号和姓名-- 方法1:直接用in的方法,存在错误,因为选课为其子集的同学会选出来(需要再inner join一次)selectdistinct a.s_id,a.s_name from(select s.s_id,s.s_name from student as s where s.s_id notin(selectdistinct sc1.s_id from score as sc1 where sc1.c_id notin(select sc.c_id from score as sc where sc.s_id ='01'))and s.s_id!='01')as a innerjoin score as b on a.s_id = b.s_id;-- 方法2:改为inner JOIN,同样存在错误(改为left join依然有问题)select s1.s_id,s1.s_name from student as s1 where s1.s_id notin(select s.s_id from student as s leftjoin(selectdistinct sc1.s_id from score as sc1 where sc1.c_id notin(select sc.c_id from score as sc where sc.s_id='01'))as b on s.s_id = b.s_id
)and s1.s_id!='01';-- 方法1和2是错误的,正确思路是方法3-- 方法3:排除只要有一门课不在学生01选课列表中之后,再加上两者选课数目一样的条件即可select s.s_id,s.s_name from student as s where s.s_id!='01'and
s.s_id in(select sc2.s_id from score as sc2
groupby sc2.s_id havingcount(sc2.c_id)=(selectcount(sc3.c_id)from score as sc3 groupby sc3.s_id having sc3.s_id='01'))and
s.s_id notin(selectdistinct sc1.s_id from score as sc1 where sc1.c_id notin(select sc.c_id from score as sc where sc.s_id='01'));
15:查询有两门及两门以上成绩不及格的学生学号姓名及平均成绩
-- 15-查询有两门及两门以上成绩不及格的学生学号姓名及平均成绩select s.s_id,s.s_name,avg(sc1.s_score)from student as s
innerjoin score as sc1 on sc1.s_id = s.s_id
where s.s_id in(select sc.s_id from score as sc where sc.s_score<60groupby sc.s_id havingcount(distinct sc.c_id)>=2)groupby s.s_id,s.s_name;
16:选修01课程且不及格的学生信息,按照分数降序排列
-- 16-选修01课程且不及格的学生信息,按照分数降序排列-- 先连接后where条件select s.s_id,s.s_name,sc.s_score from score as sc innerjoin student as s on s.s_id=sc.s_id
where sc.c_id='01'and sc.s_score<60orderby sc.s_score DESC;
17:按照平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 17-按照平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩-- 显示每个学生的学号、姓名、单个课程成绩、全部课程的平均成绩select sc1.s_id,s.s_name,sc1.c_id,sc1.s_score,sc2.sc_avg_s_score from score as sc1 innerjoin(select sc2.s_id,avg(sc2.s_score)as sc_avg_s_score from score as sc2 groupby sc2.s_id)as sc2 on sc1.s_id = sc2.s_id
innerjoin student as s on s.s_id = sc1.s_id
orderby sc2.sc_avg_s_score desc;-- 显示每个学生的学号、姓名、单个课程成绩、该课程平均成绩select s.s_id,s.s_name,sc.c_id,sc.s_score,sc2.sc_avg_c_score from student as s
innerjoin score as sc on sc.s_id = s.s_id
innerjoin(select sc.c_id,avg(sc.s_score)as sc_avg_c_score from score as sc groupby sc.c_id)as sc2 on sc.c_id = sc2.c_id
orderby sc2.sc_avg_c_score desc,sc.s_score desc;
18:查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率和优秀率
-- 18-查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率和优秀率select sc.c_id,c.c_name,max(sc.s_score)'最高分',min(sc.s_score)'最低分',avg(sc.s_score)'平均分',sum(casewhen sc.s_score>=60then1else0end)/count(sc.c_id)*100'及格率%',sum(casewhen sc.s_score>=70then1else0end)/count(sc.c_id)*100'中等率%',sum(casewhen sc.s_score>=80then1else0end)/count(sc.c_id)*100'良好率%',sum(casewhen sc.s_score>=90then1else0end)/count(sc.c_id)*100'优秀率%'from score as sc
innerjoin course as c on c.c_id = sc.c_id
groupby sc.c_id
19:按各科成绩排行,并显示排名
-- 19-按各科成绩排行,并显示排名select sc.s_id,sc.s_score,@cur_rank :=@cur_rank+1as rank
from score as sc,(select@cur_rank :=0)as a
orderby sc.s_score desc
20:查询学生的总成绩并进行排名
-- 20-查询学生的总成绩并进行排名select sc.s_id '学号',s.s_name '姓名',sum(sc.s_score)'总分'from score as sc innerjoin student as s
on s.s_id = sc.s_id
groupby sc.s_id
orderby'总分'desc
21:查询不同老师所教不同课程的平均分并按照平均分降序排列
-- 21-查询不同老师所教不同课程的平均分并按照平均分降序排列-- 以课程为主体,计算单个课程平均分select t.t_name '授课老师',c.c_name '课程名',avg(sc.s_score)'课程平均分1'from score as sc
innerjoin course as c
on c.c_id = sc.c_id
innerjoin teacher as t
on t.t_id = c.t_id
groupby sc.c_id
orderby 课程平均分1desc;-- 以教师为主体,计算老师所有课程平均分select t.t_name '授课老师',c.c_name '课程名',avg(sc.s_score)'课程平均分2'from teacher as t
innerjoin course as c
on c.t_id = t.t_id
innerjoin score as sc
on sc.c_id = c.c_id
groupby t.t_id, t.t_name
orderby 课程平均分2desc;
23:分段统计各科分数,并显示各科人数
-- 23-分段统计各科分数,并显示各科人数select c.c_id,c.c_name,sum(casewhen sc.s_score<=100and sc.s_score>85then1else0end)as'(85,100]',sum(casewhen sc.s_score<=85and sc.s_score>75then1else0end)as'(75,85]',sum(casewhen sc.s_score<=75and sc.s_score>=60then1else0end)as'(60,75]',sum(casewhen sc.s_score<60and sc.s_score>=0then1else0end)as'[0,60)'from score as sc
innerjoin course as c
on c.c_id = sc.c_id
groupby c.c_id
26:查询每门课程选课人数
-- 26-查询每门课程选课人数select c.c_name,sc.c_id,count(distinct sc.s_id)from score as sc
innerjoin course as c on c.c_id = sc.c_id
groupby sc.c_id,c.c_name
27:查询只选修了两门课程的学生学号和姓名
-- 27-查询只选修了两门课程的学生学号和姓名select s.s_id,s.s_name from score as sc
innerjoin student as s
on s.s_id = sc.s_id
groupby sc.s_id havingcount(distinct sc.c_id)=2
28:查询名字中存在“风”字的学生
-- 28-查询名字中存在“风”字的学生select*from student as s
where s.s_name like'%风%'
29:查询男生、女生人数
-- 29-查询男生、女生人数select s.s_sex,count(distinct s.s_id)from student as s
groupby s.s_sex
31:查询1990年出生的学生
-- 31-查询1990年出生的学生-- year()函数select*from student as s
whereyear(s.s_birth)=1990;select*from student as s
whereyear(s.s_birth)='1990';
32:查询平均分高于85的学生学号和姓名及其平均分
-- 32-查询平均分高于85的学生学号和姓名及其平均分select s.s_id,s.s_name,avg(sc.s_score)as avg_score
from score as sc
innerjoin student as s
on s.s_id = sc.s_id
groupby sc.s_id having avg_score>=85
33:查询课程平均成绩并按照课程成绩升序排序,若成绩相同按照学号降序排序
-- 33-查询课程平均成绩并按照课程成绩升序排序,若成绩相同按照学号降序排序select c.c_id,c.c_name,avg(sc.s_score)as avg_score_c from score as sc
innerjoin course as c
on c.c_id = sc.c_id
groupby sc.c_id,c.c_name
orderby avg_score_c asc,c.c_id desc
34:查询数学课不及格的学生学号和姓名
-- 34-查询数学课不及格的学生学号和姓名select s.s_id,s.s_name from score as sc
innerjoin student as s on s.s_id = sc.s_id
innerjoin course as c on c.c_id = sc.c_id
where c.c_name='数学'and sc.s_score<60
36:学生成绩高于70分的课程成绩及其学号姓名
-- 36-学生成绩高于70分的课程成绩及其学号姓名select s.s_id,s.s_name,sc.s_score from score as sc
innerjoin student as s on s.s_id = sc.s_id
where sc.s_score>=70
37:查询不及格的课程并按照课程号降序排列
-- 37-查询不及格的课程并按照课程号降序排列select c.c_id,c.c_name,sc.s_score from score as sc
innerjoin course as c on c.c_id = sc.c_id
where sc.s_score<60orderby c.c_id desc
38:查询课程03成绩在80分以上的学生学号和姓名
-- 38-查询课程03成绩在80分以上的学生学号和姓名select s.s_name,s.s_id,sc.c_id,sc.s_score from score as sc
innerjoin student as s on s.s_id = sc.s_id
where sc.s_score>=80and sc.c_id ='03'
39:求每门课程的选课人数
-- 39-求每门课程的选课人数select c.c_id,c.c_name,count(distinct sc.s_id)from score as sc
innerjoin course as c on sc.c_id = c.c_id
groupby sc.c_id,c.c_name
40:查询张三老师所授课程最高成绩学生姓名学号及其成绩
-- 40-查询张三老师所授课程最高成绩学生姓名学号及其成绩select s.s_id,s.s_name,c.c_name,max(sc.s_score)as max_score from score as sc
innerjoin student as s on s.s_id = sc.s_id
innerjoin course as c on c.c_id = sc.c_id
innerjoin teacher as t on t.t_id = c.t_id
where t.t_name='张三'groupby c.c_id;select s.s_id,s.s_name,c.c_name,max(sc.s_score)as max_score from score as sc
innerjoin student as s on s.s_id = sc.s_id
innerjoin course as c on c.c_id = sc.c_id
innerjoin teacher as t on t.t_id = c.t_id
where t.t_name='张三'orderby sc.s_score desclimit0,1;-- 从0开始取一行
43:查询每门课程选修人数(超过个人),按照人数降序排序,若选秀人数相同按照课程号升序
-- 43-查询每门课程选修人数(超过个人),按照人数降序排序,若选秀人数相同按照课程号升序排序select c.c_name,c.c_id,count(distinct sc.s_id)as count_sid
from score as sc
innerjoin course as c on c.c_id = sc.c_id
groupby sc.c_id having count_sid>5orderby count_sid desc,sc.c_id asc;
44:至少选修了两门课程的学生的学号和姓名
-- 44-至少选修了两门课程的学生的学号和姓名select s.s_id,s.s_name,count(distinct sc.c_id)as count_course
from score as sc
innerjoin student as s on s.s_id = sc.s_id
groupby sc.s_id having count_course>=2
45:查询选修了全部课程的学生的信息
-- 45-查询选修了全部课程的学生的信息select s.s_id,s.s_name,count(distinct sc.c_id)as count_course from score as sc
innerjoin student as s on s.s_id = sc.s_id
groupby s.s_id having count_course=(selectcount(distinct c.c_id)from course as c)
46:查询各学生的年龄
-- 46-查询各学生的年龄-- 方法1:使用datadiff和floor减出天数然后除以365select s.s_id,s.s_name,s.s_birth,FLOOR(datediff('2020-02-21',s.s_birth)/365)as stu_age from student as s;-- 方法2:使用now()函数而不用手动输入日期select s.s_id,s.s_name,s.s_birth,FLOOR(datediff(now(),s.s_birth)/365)as stu_age from student as s;-- 方法3:使用年份相减,用到year()函数--比起之前的结果会有所不同,更加粗略select s.s_id,s.s_name,s.s_birth,year(now())-year(s.s_birth)from student as s;
47:查询没学过张三老师任意一门课的学生姓名
-- 47-查询没学过张三老师任意一门课的学生姓名-- 方法1select s.s_id,s.s_name from student as s where s.s_id notin(select sc.s_id from score as sc
innerjoin course as c on c.c_id = sc.c_id
innerjoin teacher as t on t.t_id = c.t_id
where t.t_name ='张三');
48:查询下周过生日的同学
-- 48-查询下周过生日的同学select*from student;select*from student as s
where week(concat(substring(date(now()),1,4),substring(s.s_birth,5,6)),1)-week(now(),1)=-1or week(concat(substring(date(now()),1,4),substring(s.s_birth,5,6)),1)-week(now(),1)=-51;select*from student as s
where week(concat(substring(date('2019-12-31'),1,4),substring(s.s_birth,5,6)),1)-week('2019-12-31',1)=-1or week(concat(substring(date('2019-12-31'),1,4),substring(s.s_birth,5,6)),1)-week('2019-01-31',1)=-51;select week(concat(substring(date('2019-12-31'),1,4),substring(s.s_birth,5,6)),1)-week('2019-12-31',1)from student as s;select*from student as s
where
week(concat(substring(date('2020-12-28'),1,4),substring(s.s_birth,5,6)),1)-week('2020-12-28',1)=-1or
week(concat(substring(date('2020-12-28'),1,4),substring(s.s_birth,5,6)),1)-week('2020-01-28',1)=-51;
49:查询本月过生日的学生信息
-- 49-查询本月过生日的学生信息select*from student as s
wheremonth(s.s_birth)=month(now());select*from student as s
wheremonth(s.s_birth)=month('20201201');
50:查询下个月过生日的学生信息
-- 50-查询下个月过生日的学生信息select*from student as s
wheremonth(s.s_birth)-month(now())=1ormonth(s.s_birth)-month(now())=-11;select*from student as s
wheremonth(s.s_birth)-month('2020-11-01')=1ormonth(s.s_birth)-month('2020-11-01')=-11;