我做的超经典sql练习

首先初始化一波：

student表：

图片.png

teacher表：

图片.png

course表：

图片.png

sc表：

图片.png

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

select
stu.*
from
(select
A.*
from
(select * from sc where cid='01')A 
INNER JOIN
(SELECT * from sc where cid='02')B
on A.sid = B.sid
where A.score>B.score) A 
LEFT JOIN
student stu
on A.sid=stu.sid
;

2.查询同时存在" 01 "课程和" 02 "课程的情况

select
A.*
from
(select * from sc where cid='01') A
INNER JOIN
(SELECT * from sc where cid='02')B
on A.sid=B.sid

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

select
A.*,B.*
from
(select * from sc where cid='01') A
LEFT JOIN 
(SELECT * from sc where cid='02')B
on A.sid=B.sid

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

SELECT
sid
from sc
where
cid='02'
and
sid
not in (select sid from sc where cid='01')

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

select
sid,
avg(score) avg_score
from
sc
GROUP BY sid HAVING avg_score>60;

6. 查询在 SC 表存在成绩的学生信息

select * from student where sid in
(select max(sid) from sc GROUP BY sid)
;

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

select
X.*,Y.score
from
(select
A.*,B.*
from student A,course B) X
LEFT JOIN
sc Y on X.cid=Y.cid and X.sid=Y.sid

8.查询「李」姓老师的数量

select
count(1)
from teacher
where Tname like '李%';

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

select *
from student
where sid in
(
(select
sid
from
sc where cid in(
(select 
cid
from
course
where tid in
(SELECT tid from teacher where Tname='张三')))));

10.查询没有学全所有课程的同学的信息

select
A.*
from
student A INNER JOIN
(select sid from
sc
group by sid having count(1)<3) B
on A.sid=B.sid

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

select DISTINCT sid from
sc 
where sid!='01' and cid in
(select cid from sc 
where sid='01')

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

select 
*
from
sc
GROUP BY sid
HAVING count(1)=3 and sid not in(
select
sid
from
sc where cid
not in(select
cid
from
sc where sid='01'))

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

SELECT * from student
where sid not in(
select 
sid
from
sc
where
cid in(
select cid from course
where tid in(
select 
tid
from teacher where Tname='张三')))

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

select 
sid,
avg(score)
from
sc where sid in
(select
sid
from
sc where score<60 group by sid)
GROUP BY sid

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

select * from
sc where cid='01' and score<60 
order by score desc;

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

select 
A.*,B.avg_score
from
sc A INNER JOIN
(select sid,avg(score) avg_score from
sc group by sid
) B on A.sid=B.sid order by B.avg_score desc

17.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select
cid,
max(score) max,
min(score) min,
avg(score) avg_score,
sum(case when score>60 then 1 else 0 end)/count(1) as '及格率',
sum(case when 70<=score<80 then 1 else 0 end)/count(1) as '中等率',
sum(case when 80<=score<90 then 1 else 0 end)/count(1) as '优良率',
sum(case when score>=90 then 1 else 0 end)/count(1) as '优秀率'
from
sc
GROUP BY cid

18.查询各科成绩前三名的记录（取 a 的最高分与本表比较）

select
A.*
from
sc A left JOIN sc B
on A.cid=B.cid and A.score<B.score 
GROUP BY A.cid,A.sid HAVING count(1)<3
order by A.cid,A.score desc

19.查询出只选修两门课程的学生学号和姓名

select
* from
student
where
sid in
(select
sid
from
sc
group by sid 
HAVING count(1)=2)

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

select
A.*,B.同名人数
from
student A RIGHT JOIN
(select 
sid,count(1) as '同名人数'
from student GROUP BY name HAVING 同名人数>1) B
on A.sid=B.sid

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

select
*
from student
where YEAR(age)=1990

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

select
cid,
avg(score) avg_score
from
sc
group by cid 
order by avg_score desc,cid asc

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

select
A.*,B.avg_score
from
student A
RIGHT JOIN
(select 
sid,
avg(score) avg_score
from
sc
GROUP BY sid
HAVING avg_score>=85 )B
on A.sid=B.sid

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

select 
X.name,Y.*
from
student X RIGHT JOIN
(select
B.*,A.sid,A.score
from
sc A RIGHT JOIN
(select
cid,Cname
from
course
where Cname ='数学') B
on A.cid=B.cid
where A.score<60) Y
on X.sid=Y.sid

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

select
*
from
(select
A.sid,A.name,B.cid,B.Cname
from
student A,course B) X
LEFT JOIN sc Y
on X.cid=Y.cid and X.sid=Y.sid

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

select
A.name,B.*
from
student A RIGHT JOIN
(select
sid,cid,max(score) max_score
from
sc
where cid in
(select
cid
from course
where tid in
(select
tid
from
teacher where Tname='张三'))
GROUP BY cid) B
on A.sid=B.sid

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

select
A.*
from
sc A RIGHT JOIN
(SELECT
cid,max(score) max_score
from sc
where cid in
(select
cid
from course
where tid in
(select
tid
from
teacher
where Tname='张三'))
GROUP BY cid) B
on A.cid=B.cid and A.score=B.max_score

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

select 
A.*
from
sc A,sc B
where A.cid=B.cid and A.score=B.score
group by A.cid,A.sid
HAVING count(1)>1

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

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select
cid,count(1) count
from sc
GROUP BY cid
HAVING count>5
order by count desc,cid

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

select
sid
from
sc
GROUP BY sid
HAVING count>=2

31.查询选修了全部课程的学生信息

select
sid,count(1) count
from
sc
GROUP BY sid
HAVING count=
(select count(1) from course)

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

select
sid,name,year(now())-year(age) as age 
from
student

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

select
sid,name,TIMESTAMPDIFF(year,age,now()) as '年龄'
from
student

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

select
sid,name
from
student
where week(now())=week(age)

35.查询下周过生日的学生

select
sid,name
from
student
where (week(now())+1)=week(age)

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

select
sid,name
from
student
where month(now())=month(age)

37.查询各科成绩前三名的记录(用mysql8.0的引入的窗口函数)

rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

演示一下3种常见的窗口函数：

select
*
from
(select 
*,rank() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank

图片.png

select
*
from
(select 
*,dense_rank() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank

图片.png

select
*
from
(select 
*,row_number() over(PARTITION by cid order by score desc) as yourrank
from
sc) A where A.yourrank<=3 order by cid,yourrank

图片.png