1.1、 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

``````# 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select t3.*
,t1.CId
,t1.score
,t2.cid
,t2.score
from sc t1
join sc t2 on t1.SId=t2.SId and t1.CId='01' and t2.CId='02'
join student t3 on t1.SId=t3.SId
where t1.score>t2.score;
``````
1.1

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

``````select *
from sc as t1
left join sc t2 on t1.sid = t2.sid and t2.cid='02'
where t1.cid='01';
``````
``````select *
from
(select sc.sid
,sc.cid
from sc where sc.cid='01') as t1
left join
(select sc.sid
,sc.cid
from sc where sc.cid='02'
) as t2 on t1.sid=t2.sid
``````
1.2

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

``````select *
from sc where sc.sid not in
(select sc.sid from sc where sc.cid='01')
and sc.cid='02';
``````
1.3

2、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

``````select sc.sid
,st.Sname
,avg(sc.score)
from sc
join student as st on sc.sid=st.SId
group by sc.SId having avg(sc.score)>60
order by avg(sc.score) desc;
``````
2

3、查询在 SC 表存在成绩的学生信息

``````# 答案一
select *
from student st where st.sid in
(select sc.sid from sc);
# 答案二
select distinct student.*
from student,sc
where student.SId=sc.sid;
# 答案三
select distinct st.*
from sc
inner join student st on sc.sid=st.sid
``````
3

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

``````select st.sid
,st.Sname
,count(distinct sc.cid) as courses
,sum(sc.score) as score
from student as st
left join sc on st.SId=sc.SId
group by st.sid
,st.Sname;
``````
4

5、查询「李」姓老师的数量

``````select count(*)
from teacher t
where t.Tname like '李%';
``````
5

6、查询学过「张三」老师授课的同学的信息

``````select st.*
,t.Tname
from teacher as t
join course as c on t.TId=c.TId
join sc on c.CId=sc.CId
join student as st on sc.SId=st.SId
where t.Tname='张三';
``````
6

7、查询没有学全所有课程的同学的信息

``````select st.*
,count(distinct sc.cid) as 课程数
from student st
left join sc on st.sid = sc.sid
group by st.sid having 课程数 <3;
``````
7

8、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

``````select distinct st.*
from sc
join student as st on sc.sid=st.sid
where sc.cid in
(select sc.CId from sc) ;
``````
8
``````select student.*
from student
where sid in
(select sid from sc group by sid where and sid <> "01" having count(cid) = (select count(cid) from sc where sid = '01')
and sid not in(select sid from sc where cid not in(select cid from sc where sid = '01')));
``````

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

1、选出所学课程不在（01，02，03）的同学-排除
2、剩下的同学肯定选了01，02，03中的某几门，判断所学课程数是否等于3

``````select st.*
from sc
join student st on sc.sid=st.sid
where sc.sid !='01' and sc.sid  not in (select sc.sid
from sc where sc.cid not in(select sc.CId from sc where sc.sid='01'))
group by sc.sid
having count(distinct sc.cid)=(select count(distinct sc.cid) from sc where sc.sid='01')
``````
9

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

``````select distinct st.*
from sc
right join student st on sc.sid = st.sid
where st.sid not in
(select sc.sid
from  teacher as t
join course as c on t.tid=c.tid
join sc on c.cid = sc.cid
where t.Tname='张三');

``````
10

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

``````select st.sid
,st.sname
,avg(sc.score)
from student st
left join sc on sc.sid=st.sid
where sc.score<60 or sc.score is null
group by st.sid having count(distinct sc.cid)>=2;
``````
11
``````# 我的解法
select t.sid
,t.sname
,avg(t.score) as avg_score
from
(select sc.score
,sc.cid
,st.sid
,st.sname
from student st
left join sc on sc.sid=st.sid) t
where t.score<60 or t.score is null
group by t.sid,t.sname having count(distinct t.cid)>=2 or avg_score is null;
``````
11

12、检索" 01 "课程分数小于 60，按分数降序排列的学生信息

``````select *
from student st
left join sc on st.sid=sc.sid
where sc.cid='01' and score<60
order by sc.score desc;
``````

13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

``````select t1.sid
,t1.sname
,max(case when t1.cname='语文' then t1.score else 0 end) as 语文
,max(case when t1.cname='数学' then t1.score else 0 end) as 数学
,max(case when t1.cname='英语' then t1.score else 0 end) as 英语
,ifnull(avg(t1.score) ,0) as avg_score
from
(
select st.sid
,st.sname
,sc.cid
,c.cname
,sc.score
from sc
join course as c on sc.cid=c.cid
right join student as st on st.sid = sc.sid) t1
group by t1.sid
,t1.sname
order by avg_score desc;
``````
13
``````select sc.cid
,c.cname
,count(distinct sc.sid) as 选修人数
,max(sc.score) as 最高分
,min(sc.score) as 最低分
,round(avg(sc.score),2) as 平均分
,concat(round(sum(case when sc.score>=60 then 1 else 0 end)/count(distinct sc.sid)*100,2),'%') as 及格率
,concat(round(sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(distinct sc.sid)*100,2),'%') as 中等率
,concat(round(sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(distinct sc.sid)*100,2),'%') as 优良率
,concat(round(sum(case when sc.score>=90 then 1 else 0 end)/count(distinct sc.sid)*100,2),'%') as 优秀率
from sc
join course as c on sc.cid=c.cid
group by sc.cid
,c.cname
order by 选修人数 desc,sc.cid
``````
14
``````select sc.cid
,c.cname
,count(distinct sc.sid) as 选修人数
,max(sc.score) as 最高分
,min(sc.score) as 最低分
,round(avg(sc.score),2) as 平均分
,concat(round(sum(if(sc.score>60,1,0))/count(distinct sc.sid)*100,2),'%') as 及格率
,concat(round(sum(if(sc.score>=70 and sc.score<80 , 1 , 0 ))/count(distinct sc.sid)*100,2),'%') as 中等率
,concat(round(sum(if(sc.score>=80 and sc.score<90 , 1 , 0 ))/count(distinct sc.sid)*100,2),'%') as 优良率
,concat(round(sum(if(sc.score>=90 , 1 , 0 ))/count(distinct sc.sid)*100,2),'%') as 优秀率
from sc
join course as c on sc.cid=c.cid
group by sc.cid
,c.cname
order by 选修人数 desc,sc.cid
``````
14

15、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

``````select @rank := 0;
select *
,@rank:=@rank+1
from sc
ORDER BY sc.score desc;
``````

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

``````select sc.SId
,sc.CId
,@dense_rank:=if(@font=sc.score,@dense_rank,@dense_rank+1) as 排名
,@font:=sc.score as score
from sc
join (select @dense_rank :=0, @font:=NULL) a
ORDER BY sc.score desc;
``````
15

16 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

``````select a.sid
,@rank:=if(@sco=总成绩,'',@rank+1) as 排名
,@sco :=总成绩 as 总成绩
from(
SELECT sc.SId
,sum(sc.score) as 总成绩
from sc
GROUP BY sc.SId
ORDER BY sum(sc.score) desc) a
join (select @rank:=0 , @sco :=null) b
``````
16

17 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]

``````SELECT sc.CId
,c.cname
,case when sc.score<60 then '(60-0]'
when sc.score>=60 and sc.score<70 then '[60-70)'
when sc.score>=70 and sc.score<85 then '[70-85)'
when sc.score>=85 then '[100-85]'
end as 区间
,count(DISTINCT sc.SId) as 人数
FROM sc
join course c on sc.CId=c.cid
GROUP BY sc.CId
,c.cname
,case when sc.score<60 then '(60-0]'
when sc.score>=60 and sc.score<70 then '[60-70)'
when sc.score>=70 and sc.score<85 then '[70-85)'
when sc.score>=85 then '[100-85]'
end
``````
17
``````# 17.1 [100-85]，[85-70]，[70-60]，[60-0] 各区间段所占比例
SELECT sc.CId
,c.cname
,concat(round(avg(case when sc.score<60 then 1 else 0 end )*100,2),'%') as '(60-0]'
,concat(round(avg(case when sc.score>=60 and sc.score<70 then 1 else 0 end )*100,2),'%') as '[60-70)'
,concat(round(avg(case when sc.score>=70 and sc.score<85 then 1 else 0 end )*100,2),'%') as '[70-85)'
,concat(round(avg(case when sc.score>=85 then 1 else 0 end )*100,2),'%') as '[100-85]'
FROM sc
join course c on sc.CId=c.cid
GROUP BY sc.CId
,c.cname
``````
17.1

18、查询各科成绩前三名的记录

``````select sc.cid
,c.cname
,st.sname
,sc.score
from sc
join course as c on sc.cid=c.cid
join student as st on st.sid=sc.sid
where (select count(*) from sc as a where a.cid=sc.cid and sc.score <a.score) <3
order by sc.cid ,sc.score desc;
``````
18

21.查询男生、女生人数

``````select sum(case when st.Ssex='男' then 1 else 0 end) as 男生人数
,sum(case when st.Ssex='女' then 1 else 0 end) AS 女生人数
from student as st
``````
21
``````select st.ssex
,count(*)
from student as st
group by st.ssex
``````
21

22、查询名字中含有「风」字的学生信息

``````select *
from student as st
where st.sname like '%风%';
``````
22

23.查询同名同性学生名单，并统计同名人数

``````select sid
,sname
,ssex
,count(sname)
from student
group by sname,ssex having count(sname)>1
``````
23

24.查询 1990 年出生的学生名单

``````select *
from student
where year(Sage)='1990';
``````
24

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

``````select cid
,avg(score)
from sc
group by cid
order by avg(score) desc,cid desc
``````
25

26 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

``````select st.sid
,st.sname
,avg(sc.score)
from sc
join student as st on sc.sid=st.sid
group by st.sid
having avg(sc.score)>=85;
``````
26

27查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

``````select st.sid
,st.sname
,a.score
from student as st
join
(select sc.sid
,sc.score
from sc
join course as c on sc.cid=c.cid
where c.cname='数学' and sc.score<60) a
on st.sid = a.sid;
``````
27

28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

``````select *
from student st
left join sc on st.sid=sc.sid;
``````
28

29、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

``````select sc.sid
,st.sname
,c.cname
,sc.score
from sc
join course as c on c.cid=sc.cid
join student st on st.sid=sc.sid
where sc.score>70;
``````
29

30.查询存在不及格的课程

``````select *
from sc
join course as c on sc.cid=c.cid
where sc.score<60;
``````
30

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

``````select sc.sid
,st.sname
,sc.cid
,sc.score
from sc
join student as st on sc.sid=st.sid
where sc.cid='01' and sc.score>=80;
``````
31

32、求每门课程的学生人数

``````select sc.cid
,count(sc.sid)
from sc
group by sc.cid;
``````
32

33、成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

``````select st.*
,max(sc.score)
,c.cname
,sc.score
from teacher as t
join course as c on t.tid=c.tid
join sc on sc.cid=c.cid
join student as st on st.sid=sc.sid
where t.tname='张三';
``````
33

34、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

``````select t1.*
from
(select    a.*
,@rank_num :=if(@score=a.score, @rank_num, @rank_num:=@rank_num+1) as rn
,@score:=a.score as r_score
from
(select st.sid
,st.sname
,sc.cid
,c.cname
,t.tname
,sc.score
from teacher as t
join course as c on t.tid=c.tid
join sc on sc.cid=c.cid
join student as st on st.sid=sc.sid
where t.tname='张三') a
join (select @score := null, @rank_num :=0) b
order by a.score desc) t1
where t1.rn=1;
``````
34

35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

``````select *
from sc
where sc.sid
in (select a.sid from sc as a where a.sid=sc.sid and sc.cid != a.cid and sc.score=a.score);
``````
35

36.查询每门功成绩最好的前两名

``````select a.*
from
(select *, rank() over(partition by sc.cid order by sc.score desc) as rank_num
from sc) a
where rank_num=1 or rank_num=2;
``````
36

``````select *
from sc as t1
where (select count(*) from sc as t2 where t1.cid=t2.cid and t2.score>t1.score)<2
order by t1.cid;
``````
36-2

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）

``````select sc.cid
,count(distinct sc.sid) as person_num
from sc
group by sc.cid having person_num>5;
``````
37

38.检索至少选修两门课程的学生学号

``````select sc.sid
,count(distinct sc.cid) as cos_num
from sc
group by sc.sid having cos_num>=2;
``````
38

39、查询选修了全部课程的学生信息

``````select st.*
,count(distinct sc.cid) as 课程数
from sc
join student as st on sc.sid=st.sid
group by sc.sid having count(distinct sc.cid)=(select count(*) from course);
``````

40.查询各学生的年龄，只按年份来算

``````select *
,year(now())-year(Sage) as age
from student;
``````
40

41、按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

``````select *
,case when substr(sage,6,5)<substr(now(),6,5) then year(now())-year(sage)
when substr(sage,6,5)>=substr(now(),6,5) then year(now())-year(sage)-1
end as age
from student;
``````
41

42.查询本周过生日的学生

``````select *
,substr(yearweek(sage),5,2) as birth_week
,substr(yearweek(curdate()),5,2) as now_week
from student
where substr(yearweek(sage),5,2)=substr(yearweek(curdate()),5,2)
``````
42

43、查询下周过生日的学生

``````select *
,substr(yearweek(sage),5,2) as birth_week
,substr(yearweek(curdate()),5,2)+1 as next_week
from student
where substr(yearweek(sage),5,2)=substr(yearweek(curdate()),5,2)+1;
``````
43

44.查询本月过生日的学生

``````select *
,month(sage) as bir_month
,month(curdate()) as now_month
from student
where month(sage) = month(curdate())
``````
44

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。EXTRACT(unit FROM date)

``````select *