MYSQL练习篇1-20题
一、经典45题之1-20题
myql数据源
可视化Navicat
总结：开始看题的看到题目就有种无力感，不知道怎么去关联书写，单表查询简单。做题速度很慢，思路也不是很清晰
知识点：出现之前没有不熟悉的知识点exist、case when、排序号函数
对同一张表查询重复数据的不是很理解（题9）
1、 01 "课程比" 02 "课程成绩高的所有学生的学号
思路：分解为简单语句，确定需要构建那种理想表select * from table where score01 > score02; 思路转换，先确定需要哪些表哪些字段再怎么关联
知识点：读懂题意
SELECT a.SId ,b.score "02",a.score "01",c.Sname FROM
(select SId,CId,score from sc where CId='01') as a
INNER JOIN
(select SId,cid,score from sc where CId='02' )as b
on a.SId=b.SId
INNER JOIN student as c on c.SId=a.SId
where a.score>b.score;
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
简单思路：select * from table where cid=01 and cid=02;
sid score01 score02
学生编号，课程01，成绩课程，02成绩 select* from table where 01>02，需要创造两个表分别课程是01和02的在筛选出分数
知识点：多表连接
SELECT a.SId ,b.score "02",a.score "01",c.Sname FROM
(select SId,CId,score from sc where CId='01') as a
INNER JOIN
(select SId,cid,score from sc where CId='02' )as b
on a.SId=b.SId
INNER JOIN student as c on c.SId=a.SId
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
知识点：左连接left join on ,左表全右表符合的有
SELECT *FROM
(select SId,CId,score from sc where CId='01') as a
LEFT JOIN
(select SId,cid,score from sc where CId='02' )as b
on a.SId=b.SId
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
考点：not in不存在
select *from sc
where SId not in (select SId from sc where sc.CId='01')
and CId='02';
2、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
考点：group by及多表关联
SELECT t1.*,avg(t2.score)
from student as t1
INNER JOIN sc as t2
on t1.SId=t2.SId
GROUP BY t1.SId
having avg(t2.score)>60;
SELECT t1.SId,t1.sname ,t2.avgsc from student as t1
INNER JOIN (SELECT SId,avg(score) as avgsc from sc
GROUP BY SId HAVING avgsc>=60) as t2 on t1.SId=t2.SId ;
3、查询在 SC 表存在成绩的学生信息
考点：distinct去重
SELECT DISTINCT t1.* from student as t1 INNER JOIN sc as t2
on t1.SId=t2.SId GROUP BY t1.SId
4、所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)
知识点：group by，表关联
SELECT t1.SId,t1.Sname,sum(t2.score),count(t2.cid)
from student as t1
LEFT JOIN sc as t2
on t1.SId=t2.SId
group by t1.SId;
4.1查询有成绩学生信息
知识点:EXISTS，此题还有的exists不熟悉
MySQL中EXISTS的用法
select *from student
where EXISTS(select * from sc where student.SId=sc.SId)
5、查询「李」姓老师的数量
考点：计数count，模糊查询like
Select count(*) from teacher where Tname like "李%";
6、查询学过「张三」老师授课的同学的信息
考点：表之间的关联:innner join on /where关联
方式一、
SELECT t1.*
from student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
INNER JOIN course as t3 on t2.CId=t3.CId
INNER JOIN teacher as t4 on t3.TId=t4.TId
where t4.Tname="张三";
方式二、
select student.*
from teacher ,course ,student,sc
where teacher.Tname='张三'
and teacher.TId=course.TId
and course.CId=sc.CId
and sc.SId=student.SId
7、查询没有学全所有课程的同学的信息
考点：left join on，易错点如果才用inner join on /where容易遗漏什么课都没选的同学
左连接保全那边表那边表就在前
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1
LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2
on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and t1.SId=student.SId
易错解法：但这种解法得出来的结果不包括什么课都没选的同学。
SELECT t1.*
FROM student as t1
INNER JOIN
(SELECT SId,count(CId)
FROM sc
GROUP BY SId
having count(CId)<3) as t2
on t1.SId=t2.sid
因此可以反向思维，求出大于等于3门课程，再在student表中去掉这些学号的学生not in
8、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
考点：in
方式一：
SELECT *from student where SId
in(
SELECT DISTINCT SId from sc
where CId in ( SELECT CId from sc where SId='01') and SId!='01');
方式二：
select DISTINCT student.* from sc ,student
where sc.CId in (select CId from sc where sc.SId='01')
and sc.SId=student.SId
9、查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
此题存在疑问
select DISTINCT student.*
from (
select student.SId,t.CId
from student ,(select sc.CId from sc where sc.SId='01') as t) as t1
LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student
where sc.SId is null
and t1.SId=student.SId
10、查询没学过张三老师讲授的任一门课程的学生姓名
思路：先查询张三老师的教师编号→然后对应教的课程编码→查出学过这个的学生编号→最后not in
方式二、先梳理出需要用到的表关联后，根据条件筛选
方式一：
SELECT * from student where SId NOT in(
SELECT SId
from sc where CId=(
SELECT CId
from course where TId=
(SELECT TId
from teacher WHERE Tname='张三')))
方式二：
SELECT * from student
where SId not in (
SELECT SId from sc
INNER JOIN course on sc.CId=course.CId
INNER JOIN teacher on teacher.TId=course.TId
where teacher.Tname='张三')
11、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
思路：查出有两门及其以上不合格同学编号找出来→姓名需要从student查，平均成绩要从sc→求平均要先group BY
知识点：分组函数需要用到group by；表关联
出错点：①avg (t2.score) 这里容易写成 t2.avg(score)；②容易遗漏where过滤条件score<60
SELECT t1.SId,t1.Sname,avg (t2.score) from
student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
where t1.SId in
(
SELECT SId
from sc
WHERE score<60 GROUP BY SId having count( DISTINCT CId)>=2)
GROUP BY t1.SId,t1.Sname;
12、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
思路：需要用到两张表student、sc，那么考虑先关联inner join on，再使用条件进行条件筛选
也可以采用where关联两张表（在需要多表时可先尝试关联所有表）
方式一、
SELECT t1.*,t2.score
from student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
where t2.CId='01' and t2.score<60
ORDER BY t2.score DESC;
方式二（未做排序）、
select student.*from student,sc
where sc.CId ='01'
and sc.score<60
and student.SId=sc.SId
13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
思路：需要用到course和sc表，平均成绩需要把学生按编号分组求
特别注意：此题需要再看，case when和max的用法
错误解题：
SELECT t2.*,t1.Cname
from course as t1
INNER JOIN (
SELECT sc.CId,sc.SId,avg(sc.score) as "平均分"
from sc
GROUP BY SId)
as t2
on t1.CId=t2.CId
ORDER BY t2.`平均分` DESC
答案（需要重点在查询下case when）：
SELECT SId as '学号'
,MAX(case when CId='01' THEN score ELSE null end )'语文'
,MAX(case when CId='02' THEN score ELSE null end)'数学'
,max(case when CId='03' THEN score ELSE null end )'英语'
,avg(score)'平均成绩'
from sc
GROUP BY SId
ORDER BY 平均成绩 DESC;
14、查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
特别注意：划重点case when 的用法，此题表达是当满足...条件记为1然后求和除以总数，得到及格率等
-- 及格率=count(>=60)/count(总数）
SELECT t1.CId,t2.Cname
,max(t1.score) '最高分'
,min(t1.score) '最低分'
,avg(t1.score) '平均分'
,sum(case when t1.score>=60 then 1 else 0 end)/COUNT(t1.SId) '及格率'
,sum(case when t1.score>=70 and t1.score<80 then 1 else 0 end)/COUNT(t1.SId) '中等率'
,sum(case when t1.score>=80 and t1.score<90 then 1 else 0 END)/COUNT(t1.SId) '优良率'
,sum(case when t1.score>=90 then 1 else 0 end)/COUNT(t1.SId) '优秀率'
from sc as t1
INNER JOIN course as t2 on t1.CId=t2.CId
GROUP BY CId
15、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select *,RANK()over(order by score desc)排名 from SC
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select *,RANK()over(order by score desc) '排名' from SC
窗口函数
rank()：跳跃排序,比如有两个第二名，就会跳跃到第四名排序
dense_rank()：连续排序；相同名次并列并且下一个是连续的
row_number()：没有重复值的排序（记录相等也是不重复的），可以进行分页使用。
eg:row_number() over (partition by col1 order by col2) 表示根据col1分组，在分组内部根据 col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
1、MySQL8.0窗口函数：rank()、dense_rank()、row_number()的区别
2、rank() over,dense_rank() over,row_number() over的区别
eg:rank() over：查出指定条件后的进行排名。
特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。
select name,subject,score,rank() over
(partition by subject order by score desc) rank from student_score;
16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select *,RANK()over(order by 总成绩 desc)排名
from (select SId,SUM(score)总成绩 from SC group by SId) as t1;
**注意：需要取别名**
*疑问点：①窗口函数的语法及位置；②使用嵌套子查询的表为什么窗口函数运行报错
报错！！！
select *,RANK()over(order by 总分 desc)排名 from(
select SId sum(score) as '总分'
from sc
GROUP BY SId);
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select *,RANK()over(order by 总成绩 desc)排名
from (select SId,SUM(score)总成绩 from SC group by SId) as t1；
17、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
知识点：分段统计可采用case when ...then...else...end
特别注意：在case when...then...else...end中容易漏写end
SELECT t2.cid,t2.cname
,SUM(case when t1.score<=100 and t1.score>85 then 1 else 0 END)/count(t1.sid) as '[100,85)'
,sum(case when t1.score<=85 and t1.score>70 then 1 ELSE 0 END)/count(t1.sid) as '[85,75)'
,sum(case when t1.score<=70 and t1.score>60 then 1 ELSE 0 END)/count(t1.sid) as '[70,60)'
,sum(case when t1.score<=60 and t1.score>0 then 1 ELSE 0 end) /count(t1.sid)as '[60,0)'
from sc as t1
INNER JOIN course as t2 on t1.cid=t2.cid
GROUP BY t2.cid,t2.cname
18、查询各科成绩前三名的记录
select * from
(select *,rank()over (partition by CID order by score desc) 排名 from SC) as t1
where t1.排名<=3
19、查询每门课程被选修的学生数
SELECT sc.CId,course.Cname,count(DISTINCT sc.SId)
from sc,course
where sc.CId=course.CId
GROUP BY sc.CId,course.Cname
20、查询出只选修两门课程的学生学号和姓名
思路：只选修两门count(distinct cid)=2，需要使用学生分组→查到学生编号，然后和student关联
方式一、
SELECT sid ,Sname
from student
WHERE SId in
(SELECT SId
from sc
GROUP BY SId having count(DISTINCT CId)=2)
方式二、
SELECT sc.SId,student.Sname
from student
INNER JOIN sc on student.SId=sc.SId
GROUP BY sc.SId HAVING count(DISTINCT CId)=2
