1.查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数
##分析首先需要分别获取成绩表中01课程和02课程的成绩
#获取01课程的成绩
select Sid,score from SC where Cid='01'; --t1
#获取02课程的成绩
select Sid,score from SC where Cid='02'; --t2
##然后进行关联比较，以01课程为准找01课程分数比02课程分数高的
select t1.Sid
,t1.score as '01课程的成绩'
,t2.score as '02课程的成绩'
from (
select Sid
,score
from SC
where Cid='01'
)as t1
left join (
select Sid
,score
from SC
where Cid='02'
) as t2
on t1.Sid=t2.Sid; --tt1
##然后再和学生表进行内连接
select tt2.*
,tt1.01课程的成绩
,tt1.02课程的成绩
from (
select t1.Sid
,t1.score as '01课程的成绩'
,t2.score as '02课程的成绩'
from (
select Sid
,score
from SC
where Cid='01'
)as t1
left join (
select Sid
,score
from SC
where Cid='02'
) as t2
on t1.Sid=t2.Sid
)as tt1
join Student as tt2
on tt1.Sid=tt2.Sid;
##涉及到的表： 分数表 学生表
##需要显示的列学生信息及分数
2.查询同时存在" 01 “课程和” 02 "课程的情况
##分析首先需要分别获取成绩表中01课程和02课程的成绩
#获取01课程的成绩
select Sid,score from SC where Cid='01'; --t1
#获取02课程的成绩
select Sid,score from SC where Cid='02'; --t2
##然后两者之间以Sid为条件进行内连接
select t1.Sid
,t1.score as '01课程的成绩'
,t2.score as '02课程的成绩'
from (
select Sid
,score
from SC
where Cid='01'
)as t1
join (
select Sid
,score
from SC
where Cid='02'
)as t2
on t1.Sid=t2.Sid;
3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
##涉及到的表：成绩表
##分析首先在分数表中要找到'01课程的成绩'，'02课程的成绩'
#获取01课程的成绩
select Sid,score from SC where Cid='01'; --t1
#获取02课程的成绩
select Sid,score from SC where Cid='02'; --t2
##然后由题目(不存在时显示为 null )得两表以'01课程的成绩'为准进行左连接
select t1.Sid
,t1.score as '01课程的成绩'
,t2.score as '02课程的成绩'
from (
select Sid
,score
from SC
where Cid='01'
)as t1
left join (
select Sid
,score
from SC
where Cid='02'
)as t2
on t1.Sid=t2.Sid;
4.查询不存在" 01 “课程但存在” 02 "课程的情况
##分析涉及到分数表
##和上题类似只需要查询有01课程不存在但是02课程存在的情况
##所以只需要将先查询01课程和02课程存在的情况然后以02课程为准进行连接即可
#存在02课程的情况
select Sid,Cid,score from SC where Cid ='02'; --t2
#存在01课程的情况
select Sid,Cid,score from SC where Cid ='01'; --t1
#以02课程为准进行连接
select t2.Sid
,t2.Cid
,t2.score
,t1.Cid
,t1.score
from (
select Sid
,Cid
,score
from SC
where Cid ='02'
)as t2
left join (
select Sid
,Cid
,score
from SC
where Cid ='01'
)as t1
on t1.Sid=t2.Sid;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
##需要显示 学生编号、学生姓名、学生平均成绩
##涉及到：学生表和成绩表
##分析：先在成绩表中找出平均成绩>=60分的同学的学生编号，再和学生表关联
#先在成绩表中找出平均成绩>=60分的同学的学生编号
select Sid,avg(score)as avg_score from SC group by Sid having avg(score)>=60 ;--t1
#再和学生表做内连接
select t1.Sid
,t2.Sname
,t1.avg_score
from (
select Sid
,avg(score)as avg_score
from SC
group by Sid
having avg(score)>=60
) as t1
join
Student as t2
on t1.Sid=t2.Sid;
6.查询在 SC 表存在成绩的学⽣信息
##涉及到成绩表和学生表
##分析将成绩表中的Sid去重之后和学生表内连接
#将成绩表中的Sid去重
select distinct Sid from SC; --t1
#之后和学生表内连接
select t1.Sid
,t2.Sname
,t2.Sage
,t2.Ssex
from (
select distinct Sid
from SC
)as t1
join
Student as t2
on t1.Sid=t2.Sid;
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
##涉及到的表：学生表、成绩表
##需要显示：学生编号、姓名、选课总数、总成绩(没成绩的显示为 null )
##分析：先在成绩表中按id分组显示选课总数和总成绩，然后以学生表为准进行关联
#在成绩表中按id分组显示选课总数和总成绩
select Sid,count(Cid)as '选课总数',sum(score)as '总分' from SC group by Sid;--t2
#以学生表为准进行关联
select t1.Sid
,t1.Sname
,t2.选课总数
,t2.总分
from Student as t1
left join (
select Sid
,count(Cid)as '选课总数'
,sum(score)as '总分'
from SC
group by Sid
) as t2
on t1.Sid=t2.Sid;
8.查询「李」姓⽼师的数量
##涉及到的表：老师表
##用模糊查询即可
select count(Tname) as '李姓老师的数量'
from Teacher
where Tname like '张_';
9.查询学过「张三」⽼师授课的同学的信息
##涉及到的表：成绩表（张三老师-Cid 01）、学生表
##分析：先在成绩表中找出学过张三老师课的同学，再和学生表进行内连接
#在成绩表中找出学过张三老师课的同学
select Sid from SC where Cid='01';--t2
#和学生表进行内连接
select t1.*
from Student as t1
join (
select Sid
from SC
where Cid='01'
)as t2
on t2.Sid=t1.Sid;
10.查询没有学全所有课程的同学的信息
##涉及到的表：学生表、成绩表
##分析：先找学完所有课程的学生，然后在学生表中排除即可
#找学完所有课程的学生
select Sid,count(Cid) from SC group by Sid having count(Cid)=3;
#为了拿出学完所有课程的学生id，再套一层子查询
select t1.Sid from (select Sid,count(Cid) from SC group by Sid having count(Cid)=3)as t1;
#在学生表中排除即可
select t2.Sid
,t2.Sname
from Student as t2
where t2.Sid not in(
select t1.Sid
from(
select Sid
,count(Cid)
from SC
group by Sid
having count(Cid)=3
)as t1
);
11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
##涉及到成绩表、学生表
##分析先找学号为01的学生的所学课程
##再找至少有一门课和他相同的学生的同学信息
#先找学号为01的学生的所学课程
select Sid,Cid from SC where Sid='01';
#观察发先01学生学全了所有课程
#那么只要成绩表中有成绩的学生都符合条件(把01排除掉)
select Sid,count(Cid) from SC where Sid!='01' group by Sid ;--t1
#将t1表和学生表进行关联
select t2.*
from (
select Sid
,count(Cid)
from SC
where Sid!='01'
group by Sid
)as t1
join
Student as t2
on t1.Sid=t2.Sid;
12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
##涉及到：学生表和成绩表
##分析：由上一题得知，01同学学完了所有课程，那么只需要在成绩表中找出学完所有课程的同学并把01同学排除，之后与学生表关联
#在成绩表中找出学完所有课程的同学并把01同学排除
select Sid,count(Cid) from SC where Sid!='01' group by Sid having count(Cid)=3; --t1
#与学生表内连接
select t2.*
from Student as t2
join (
select Sid
,count(Cid)
from SC
where Sid!='01'
group by Sid
having count(Cid)=3
)as t1
on t1.Sid=t2.Sid;
13.查询没学过"张三"⽼师讲授的任⼀⻔课程的学⽣姓名
##涉及到的表：老师表、课程表、成绩表、学生表
##分析：先找出张三老师教授的课，然后将学过张三老师的课的同学排除掉
#找出张三老师教授的课
#先找出张三老师的教师id
select Tid
from Teacher
where Tname='张三'；--t1
#然后和课程表进行连接，以Tid为标准
select t2.Cid
from(
select Tid
from Teacher
where Tname='张三'
)as t1
join
Course as t2
on t1.Tid=t2.Tid;--tt1
#将学过张三老师的课的同学排除掉
#先找学过张三老师课的学生的Sid
select tt2.Sid
from (
select t2.Cid
from(
select Tid
from Teacher
where Tname='张三'
)as t1
join
Course as t2
on t1.Tid=t2.Tid
)as tt1
join
SC as tt2
on tt1.Cid=tt2.Cid;
#将学过张三老师的课的同学的Sid拿出来，套个子查询
select ttt1.Sid
from (
select tt2.Sid
from (
select t2.Cid
from(
select Tid
from Teacher
where Tname='张三'
)as t1
join
Course as t2
on t1.Tid=t2.Tid
)as tt1
join
SC as tt2
on tt1.Cid=tt2.Cid
) as ttt1;
#将学过张三老师课的同学从成绩表中剔除(再去个重)
select distinct Sid
from SC
where Sid not in (
select ttt1.Sid
from (
select tt2.Sid
from (
select t2.Cid
from(
select Tid
from Teacher
where Tname='张三'
)as t1
join
Course as t2
on t1.Tid=t2.Tid
)as tt1
join
SC as tt2
on tt1.Cid=tt2.Cid
) as ttt1
);
#再将没学过"张三"⽼师课的学生姓名拿出来
#和学生表进行内连接
select t6.*
from Student as t6
join (
select distinct Sid
from SC
where Sid not in (
select ttt1.Sid
from (
select tt2.Sid
from (
select t2.Cid
from(
select Tid
from Teacher
where Tname='张三'
)as t1
join
Course as t2
on t1.Tid=t2.Tid
)as tt1
join
SC as tt2
on tt1.Cid=tt2.Cid
) as ttt1
)
)as t7
on t6.Sid=t7.Sid;
14.查询两⻔及其以上不及格课程的同学的学号，姓名及其平均成绩
##涉及到：学生表和成绩表
##需要显示 学号 姓名 平均分
##分析：
#先在成绩表中找出不及格的课程 显示 分数、课程、sid
select Sid
,Cid
,score
from SC
where score<60 ; --t1
#将上表套一个子查询 按sid分组 求不及格的课程数、平均成绩
select t1.Sid
,count(t1.Cid) as cnt
,avg(t1.score) as '平均分'
from (
select Sid
,Cid
,score
from SC
where score<60
) as t1
group by Sid
having cnt>=2; --t2
#将t2表与学生表进行内连接 显示 学号 姓名 平均分
select t2.Sid
,tt1.Sname
,t2.平均分
from (
select t1.Sid
,count(t1.Cid) as cnt
,avg(t1.score) as '平均分'
from (
select Sid
,Cid
,score
from SC
where score<60
) as t1
group by Sid
having cnt>=2
)as t2
join Student as tt1
on t2.Sid=tt1.Sid;
15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
##涉及到：学生表和成绩表
##需要显示： 学生信息
##分析：
#先在成绩表中筛选出01课程的分数 并且对筛选结果做处理用having ，分数小于60分
select Sid
,score
from SC
where Cid='01'
having score <60; -- t1
#在将t1表与学生表关联，并按照分数降序排列
select t2.*
,t1.score
from (
select Sid
,score
from SC
where Cid='01'
having score <60
) as t1
join Student as t2
on t1.Sid=t2.Sid
order by t1.score desc;
16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩
##显示：所有课程的成绩以及平均成绩
##涉及到：成绩表
##分析：
#先在成绩表中按sid分组，求每个学生的平均分
select Sid
,avg(score) as avg_score
from SC
group by Sid ; --t1
#在成绩表中取出学生所有课程的成绩 语数英
select Sid
,score
from SC
where Cid='01'; --语文成绩 t2
select Sid
,score
from SC
where Cid='02'; --数学成绩 t3
select Sid
,score
from SC
where Cid='03'; --英语成绩 t4
#将t2、t3、t4表与t1表右连接 显示 各科成绩及平均分 并按照平均分降序排列
select t1.Sid
,t2.score as '语文成绩'
,t3.score as '数学成绩'
,t4.score as '英语成绩'
,t1.avg_score as '平均成绩'
from (
select Sid
,avg(score) as avg_score
from SC
group by Sid
)as t1
left join (
select Sid
,score
from SC
where Cid='01'
)as t2
on t1.Sid=t2.Sid
left join (
select Sid
,score
from SC
where Cid='02'
)as t3
on t1.Sid=t3.Sid
left join (
select Sid
,score
from SC
where Cid='03'
)as t4
on t1.Sid=t4.Sid
order by t1.avg_score desc ;
17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若⼈数相同，按课程号升序排列
##需要显示 课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 、选修⼈数
##涉及到的表：课程表、成绩表
##分析：
#先将课程表与成绩表进行内连接，on Cid 再按照课程ID和课程名进行分组
#然后显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 、选修⼈数 都是对字段进行操作
select t1.CId
,t2.Cname
,max(score) as max_score
,min(score) as min_score
,avg(score) as avg_score
,concat(round(sum(if(score>=60,1,0))*100/count(score),2),"%") as 及格率
,concat(round(sum(if(score>=70 and score<80,1,0))*100/count(score),2),"%") as 中等率
,concat(round(sum(if(score>=80 and score<90,1,0))*100/count(score),2),"%") as 优良率
,concat(round(sum(if(score>=90,1,0))*100/count(score),2),"%") as 优秀率
,count(score) as num
from SC t1 join Course t2 on t1.CId = t2.CId
group by t1.CId,t2.Cname
order by num desc,CId asc;
#解析
concat(round(sum(if(score>=60,1,0))*100/count(score),2),"%") as 及格率
concat 字符串的拼接——将算出来的结果拼接上%
round 四舍五入保留两位小数
sum 统计共有几个人及格
if 判断是不是及格的，是返回1
*100 为了结果以百分制表示
count 统计人数
18.按各科平均成绩进⾏排序，并显示排名
##涉及到 成绩表
##显示 学生ID 平均分 排名
##分析：
#将成绩表以sid进行分组 并以平均分降序排列
select Sid
,avg(score)as avg_score
from SC
group by Sid
order by avg_score desc; --t1
# 另外设置一个变量用来显示排名
set @i := 0;
select Sid
,avg(score)as avg_score
,@i := @i + 1 as rank
from SC
group by Sid
order by avg_score desc;
#但是这样会出现排名乱了的情况,因为排序是最后做的 所以要套个子查询
set @i := 0;
select t1.Sid
,t1.avg_score
,@i := @i + 1 as rank
from (
select Sid
,avg(score)as avg_score
from SC
group by Sid
order by avg_score desc
)as t1
19.按各科平均成绩进⾏排序，并显示排名
##十九题和十八题一样
##涉及到 成绩表
##需要显示 Sid 平均分 排名
#首先将成绩表按sid分组 并按照平均分降序排列
select Sid
,avg(score)as avg_score
from SC
group by Sid
order by avg_score desc ; --t1
#然后设置一个变量 用来显示排名
#为了防止排名乱序 将t1表套一个子查询
set @i := 0 ;
select t1.Sid
,t1.avg_score as '平均分'
,@i := @i + 1 as '排名'
from (
select Sid
,avg(score)as avg_score
from SC
group by Sid
order by avg_score desc
) as t1;
20.查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺
##这题和上两题类似 就是排名比较复杂
##涉及到 成绩表
##需要显示 Sid 总成绩 排名 并且总分重复时保留名次的空缺 例如 1 2 2 4 5 6
##分析：
#先将成绩表 按照sid 分组 之后按照总分降序排名
select Sid
,sum(score) as sum_score
from SC
group by Sid
order by sum_score desc ; --t1
#然后为了防止排序乱序 将t1表套一个子查询
select t1.Sid
,t1.sum_score
from (
select Sid
,sum(score) as sum_score
from SC
group by Sid
order by sum_score desc
) as t1 ;
#关键到了排序 需要设置四个变量
set @i := 0 ;
set @j := 0 ;
set @q := 0 ;
set @p := 0 ;
select t1.Sid
,t1.sum_score
,@j := @j +1 -- j 是用来 记录当前是第几个学生的
,@p = t1.sum_score -- p 是用来 记当前学生的总分的
,if(@p=@q,@i,@i := @j) as rank -- rank 是用来 总分重复时使用上一个同学的排名
,@q := @p -- q 是用来 记录上一位学生的总分的
from (
select Sid
,sum(score) as sum_score
from SC
group by Sid
order by sum_score desc
) as t1 ; --t2
