题目如下，代码均为亲手打的，可以得到结果，但不保证是最简便的方法，如有更精简的语句，欢迎交流。
学生表（stu）数据如下：
课程表（sco）数据如下：
教师表（te）数据如下：
成绩表（sc）数据如下：
题目如下：
-- 1.查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select s_name,c_name,score
from stu left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
where c_name="数学" and score<60;
-- 2.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select stu.s_id,s_name
from stu left join sc on stu.s_id=sc.s_id
where c_id="01" and score>=80;
-- 3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩；
select stu.s_id,s_name,count(score) 选课总数,ifnull(sum(score),0) 总成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;
-- 4.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩；
select stu.s_id,s_name,avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having avg(score)>=60;
-- 5.查询平均成绩大于等于85的所有学生的学号、课程编号和成绩；
select stu.s_id,c_id,score
from stu left join sc on stu.s_id=sc.s_id
where stu.s_id in
(select s_id from sc
group by s_id
having avg(score)>=85);
-- 6.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；
select stu.s_id,s_name,avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having sum(score<60)>=2;
-- 7.检索“01”课程分数小于60，按分数降序排列的学生信息；
select stu.*
from stu left join sc on stu.s_id=sc.s_id
where c_id="01" and score<60
order by score desc;
-- 8.查询学过“张三”老师授课的同学的信息；
select stu.*
from stu left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
where t_name="张三";
-- 9.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩；
方法一：
select sc.*,平均成绩 from sc left join
(select s_id,avg(score) 平均成绩 from sc
group by s_id) aa on sc.s_id=aa.s_id
order by avgs desc;
方法二：
select stu.s_id,
max(if(c_id=01,score,0)) "语文",
max(if(c_id=02,score,0)) "数学",
max(if(c_id=03,score,0)) "英语",
avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by sc.s_id order by avg(score) desc;
-- 10.查询不同老师所教不同课程平均分从高到低显示；
select t_id,sc.c_id,avg(score) 平均成绩
from sc left join co on sc.c_id=co.c_id
group by t_id,c_id
order by avg(score) desc;
-- 11.查询出只有两门课程的全部学生的学号和姓名；
方法一：
select stu.s_id,s_name
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having count(c_id)=2;
方法二：
select s_id,s_name from stu
where s_id in
(select s_id from sc
group by s_id
having count(*)=2);
-- 12.查询没有学全所有课程的同学的信息；
方法一：
select * from stu
where s_id in
(select s_id from sc
group by s_id
having count(*)<(select count(*) from co));
方法二：
select stu.* from
stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having count(*)<(select count(*) from co);
-- 13.查询没学过“张三”老师授课的同学的信息；
方法一：
select stu.*
from stu left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
group by stu.s_id
having sum(ifnull(t_name="张三",0))=0;
方法二：
select * from stu where s_id not in(
select stu.s_id
from stu left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
where t_name="张三");
-- 14.查询至少有一门课与学号为“01”的同学所学相同的同学的信息；
方法一：
select * from stu
where s_id in
(select s_id from sc where c_id in
(select c_id from sc where s_id="01"));
方法二：
select stu.*
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having sum(sc.c_id in (select c_id from sc where s_id="01"))>=1;
方法三：
select distinct stu.*
from stu left join sc on stu.s_id=sc.s_id
where c_id in(
select c_id from sc where s_id="01");
-- 15.查询学过编号为“01”并且也学过编号为“02”的课程的同学的信息；
方法一：
select * from stu where s_id in
(select s_id from sc where c_id in ("01","02")
group by s_id
having count(*)=2);
方法二：
select * from stu where s_id in
(select s_id from sc
group by s_id
having sum(c_id in("01","02"))=2);
方法三：
select stu.* from stu left join sc on stu.s_id=sc.s_id
where c_id in("01","02")
group by stu.s_id
having count(*)=2;
方法四：
select stu.* from
stu join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"
join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02" ;
-- 16.查询“01”课程比“02”课程成绩高的学生的信息及课程分数；
方法一：
select * from
stu left join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"
left join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02"
where ifnull(sc1.score,0)>ifnull(sc2.score,0);
方法二：
select stu.*,
max(if(sc.c_id="01",score,0)) as "01",
max(if(c_id="02",score,0)) as "02"
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having max(if(sc.c_id="01",score,0))>max(if(c_id="02",score,0));
-- 17.查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息；
方法一：
select stu.* from
stu left join sc sc1 on stu.s_id=sc1.s_id and sc1.c_id="01"
left join sc sc2 on stu.s_id=sc2.s_id and sc2.c_id ="02"
where sc1.score is not null and sc2.score is null;
方法二：
select stu.*
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having sum(c_id="01")=1 and sum(c_id="02")=0;
方法三：
select * from stu
where s_id in (select s_id from sc where c_id="01")
and s_id not in (select s_id from sc where c_id="02");
方法四：
select stu.* from stu left join sc on stu.s_id=sc.s_id
where c_id in("01","02")
group by stu.s_id
having group_concat(c_id)="01";
-- 18.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
方法一：
select s_name,c_name,score
from stu left join sc on stu.s_id=sc.s_id left join co on sc.c_id=co.c_id
where stu.s_id in
(select s_id from sc
group by s_id
having max(score)>70);
方法二：
select stu.*,
max(if(sc.c_id=01,score,0)) 语文,
max(if(sc.c_id=02,score,0)) 数学,
max(if(sc.c_id=03,score,0)) 英语
from stu left join sc on stu.s_id=sc.s_id
group by sc.s_id
having 语文>70 or 数学>70 or 英语>70;
-- 19.查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩；
select stu.*,score
from stu left join sc on stu.s_id=sc.s_id
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
where t_name="张三"
order by score desc
limit 1;
-- 20.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩；
select * from sc
where (select sum(score>sc.score) from sc sc1 where c_id=sc.c_id) in (1,2);
-- 21. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率；
select sc.c_id,c_name,max(score)最高分,min(score)最低分,avg(score)平均分, avg(score>=60)及格率,avg(score>=70 and score<80)中等率,avg(score>=80 and score<90)优良率,avg(score>=90) 优秀率
from sc left join co on sc.c_id=co.c_id
group by sc.c_id;
-- 22.查询所有和01选修完全一样的同学信息；
select stu.* from stu
left join sc on stu.s_id=sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id)=(select group_concat(c_id) from sc where s_id=01);
-- 23.将成绩表转换为二维表；
select s_id,
sum(if(c_id=01,score,0)) "01",
sum(if(c_id=02,score,0)) "02",
sum(if(c_id=03,score,0)) "03“
from sc
group by s_id;
-- 24.查询至少两门课程及格的学生学号；
select s_id from sc group by s_id having sum(score>=60)>1；
-- 25.按各科成绩进行排序，并显示排名；
select s_id,c_id,score,
(select sum(score>sc.score) from sc sc1 where c_id=sc.c_id)+1 排名
from sc
order by c_id,排名;
