MySQL精简50题练习

最新推荐文章于 2023-08-12 14:41:48 发布

嘻嘻你好呀

最新推荐文章于 2023-08-12 14:41:48 发布

阅读量363

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_46049759/article/details/116209025

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

写在前面：有的题目有可能有别的更好的解法，欢迎补充指正

数据表介绍

1.学生表

Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2.课程表

Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号

3.教师表

Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名

4.成绩表

SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数

=====学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

=====科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

=====教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

=====成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

# 查看四个表的内容

select * from Student
select * from SC
select * from Course
select * from Teacher

_________________________________________________________________________

练习题目

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

查找的01和02课程是在同一张表中且在同一列，所以使用一张表进行查询比较不是很好做
思路：使用子查询，将01，02课程的分数和sid分别查询出来做成两个表，然后再通过两张表中相同的sid进行分数比较

select s.*, a.score as score_01, b.score as score_02
from Student s,
     (select sid, score from SC where cid=01) a,
     (select sid, score from SC where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid;

+------+--------+---------------------+------+----------+----------+
| SId  | Sname  | Sage                | Ssex | score_01 | score_02 |
+------+--------+---------------------+------+----------+----------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |     70.0 |     60.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |     50.0 |     30.0 |
+------+--------+---------------------+------+----------+----------+

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

可以根据上题进行类推

select * FROM
	(select sid,score from SC where cid=01) A,
	(select sid,score from SC where cid=02) B
where A.sid = B.sid;

+------+-------+------+-------+
| sid  | score | sid  | score |
+------+-------+------+-------+
| 01   |  80.0 | 01   |  90.0 |
| 02   |  70.0 | 02   |  60.0 |
| 03   |  80.0 | 03   |  80.0 |
| 04   |  50.0 | 04   |  30.0 |
| 05   |  76.0 | 05   |  87.0 |
+------+-------+------+-------+

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

存在01可能不存在02 不存在显示null 只需要用左连接 left join … on
左链接是以第一个left join前面的表为主，后面的表如果没有和前面的表匹配的数据，则显示NULL

select * from 
	(select sid,score from SC where cid=01) A
LEFT JOIN
	(select sid,score from SC where cid=02) B
on A.sid = B.sid;

+------+-------+------+-------+
| sid  | score | sid  | score |
+------+-------+------+-------+
| 01   |  80.0 | 01   |  90.0 |
| 02   |  70.0 | 02   |  60.0 |
| 03   |  80.0 | 03   |  80.0 |
| 04   |  50.0 | 04   |  30.0 |
| 05   |  76.0 | 05   |  87.0 |
| 06   |  31.0 | NULL |  NULL |
+------+-------+------+-------+

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

和上题同理只不过是用右链接

select * from 
	(select sid,score from SC where cid=01) A
right JOIN
	(select sid,score from SC where cid=02) B
on A.sid = B.sid
where A.sid is NULL;
+------+-------+------+-------+
| sid  | score | sid  | score |
+------+-------+------+-------+
| NULL |  NULL | 07   |  89.0 |
+------+-------+------+-------+

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

查询平均成绩需要用分组（group by），题目要求是学生信息和成绩，而成绩是需要分组的，只需要进行一次子表查询就行

#1
select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid) p
where s.sid=p.sid and p.avg>=60;
#2
select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
where s.sid=p.sid ;
#3
select p.sid,s.sname,p.avg from 
(select sid,avg(score) avg from SC GROUP BY sid having avg>=60) p
left join
Student s
on s.sid=p.sid ;

+------+--------+----------+
| sid  | sname  | avg      |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 02   | 钱电   | 70.00000 |
| 03   | 孙风   | 80.00000 |
| 05   | 周梅   | 81.50000 |
| 07   | 郑竹   | 93.50000 |
+------+--------+----------+

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

存在成绩则在SC表中存在sid，只需要将学生表中sid和SC表中sid相匹配即可

select distinct Student.*
from
Student,SC
where Student.sid = SC.sid;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+

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

同2.
先创建一个子表-cs-表中将SC中数据整理因为要求是没成绩显示为 null,所以在cs子表在前情况下应该使用右链接进行操作

select s.sid,s.sname,cs.count,cs.sum from 
(select sid,count(score) count,sum(score) sum from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid;

+------+--------+-------+-------+
| sid  | sname  | count | sum   |
+------+--------+-------+-------+
| 01   | 赵雷   |     3 | 269.0 |
| 02   | 钱电   |     3 | 210.0 |
| 03   | 孙风   |     3 | 240.0 |
| 04   | 李云   |     3 | 100.0 |
| 05   | 周梅   |     2 | 163.0 |
| 06   | 吴兰   |     2 |  65.0 |
| 07   | 郑竹   |     2 | 187.0 |
| 09   | 张三   |  NULL |  NULL |
| 10   | 李四   |  NULL |  NULL |
| 11   | 李四   |  NULL |  NULL |
| 12   | 赵六   |  NULL |  NULL |
| 13   | 孙七   |  NULL |  NULL |
+------+--------+-------+-------+

4.1查有成绩的学生信息

case when实现行列转换时会出现多条记录，如果不用聚合函数直接进行group by分组，那么检索的是基表里分组字段的第一条记录
如果使用max()函数之后再进行group by分组，那么就会检索每个字段的最大值然后再分组

select sid,
max(case when cid='01' then score else "" end) as "cid_01",
max(case when cid='02' then score else "" end) as "cid_02",
max(case when cid='03' then score else "" end) as "cid_03"
from SC
group by sid;

===为什么要用max函数
= 如果不用max的话  数据很糟糕 可以看出数据是不完整的,或者说只显示的第一个数据
select sid,
(case when cid='01' then score else "" end) as "cid_01",
(case when cid='02' then score else "" end) as "cid_02",
(case when cid='03' then score else "" end) as "cid_03"
from SC
group by sid;

+------+--------+--------+--------+
| sid  | cid_01 | cid_02 | cid_03 |
+------+--------+--------+--------+
| 01   | 80.0   |        |        |
| 02   | 70.0   |        |        |

= 查看未分组的数据,可以得出，分组时默认是选第一个，如果使用max()函数后，就会检索某个字段最大值，然后进行分组
select sid,
(case when cid='01' then score else "" end) as "cid_01",
(case when cid='02' then score else "" end) as "cid_02",
(case when cid='03' then score else "" end) as "cid_03"
from SC;

+------+--------+--------+--------+
| sid  | cid_01 | cid_02 | cid_03 |
+------+--------+--------+--------+
| 01   | 80.0   |        |         |
| 01   |        | 90.0   |        |
| 01   |        |        | 99.0   |
| 02   | 70.0   |        |        |
| 02   |        | 60.0   |        |
| 02   |        |        | 80.0   |

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

使用like进行模糊匹配

select count(*) 李姓老师数量 from Teacher 
where tname LIKE '李%';

+--------------------+
| 李姓老师数量         |
+--------------------+
|                  1 |
+--------------------+

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

通过张三老师找到他教的课程的cid
只需要将学生上课的cid与老师课程cid相匹配

select s.* from Student s,SC
where SC.cid =
(select cid from Course
where tid = (select tid from Teacher
where tname='张三')) and s.sid=SC.sid;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+

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

先查出所有课程数量，计算每个学生上的课程，再筛选学的课程数<课程总数的人
该表应当是以学生表为主，所以应该使用左连接或右链接，如果学生没有选课，关联查询后的表是会出现为NULL，为了方便统计数据，引入IFNULL（a,b）函数(判断字段值a是否为空，若是，则为该字段值则为b)

select s.sid,s.sname,IFNULL(cs.count,0) num from 
(select sid,count(score) count from SC GROUP BY sid) cs
right JOIN
Student s
on s.sid=cs.sid
where IFNULL(cs.count,0)<(select count(distinct cid) as cid_num from Course);

+------+--------+-----+
| sid  | sname  | num |
+------+--------+-----+
| 05   | 周梅   |   2 |
| 06   | 吴兰   |   2 |
| 07   | 郑竹   |   2 |
| 09   | 张三   |   0 |
| 10   | 李四   |   0 |
| 11   | 李四   |   0 |
| 12   | 赵六   |   0 |
| 13   | 孙七   |   0 |
+------+--------+-----+

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

先用SC表查看01同学学的所有课程的cid
通过判断SC表中和01同学cid相同的sid即可，在通过sid去重得到最终sid与student表左链接（忽略student中没有分数的人）

select Student.* from
(select distinct(sid) from SC
where cid in 
(select cid from SC where sid = '01')) A
left join 
Student
on A.sid = Student.sid;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
+------+--------+---------------------+------+

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

把每个人的课程数都罗列出来，然后合并字符串，找到跟01同学的课程字符串相同的人(排除01同学)

===1.
select sid, 
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having cids = 
(select 
concat(max(case when cid='01' then cid else "" end),max(case when cid='02' then cid else "" end),max(case when cid='03' then cid else "" end) ) as cids
from SC
group by sid
having sid = "01") and sid <> "01"; # <>为不等于!=

+------+--------+
| sid  | cids   |
+------+--------+
| 02   | 010203 |
| 03   | 010203 |
| 04   | 010203 |
+------+--------+

===2.
select s.*, 
concat(max(case when sc.cid='01' then sc.cid else "" end),max(case when sc.cid='02' then sc.cid else "" end),max(case when sc.cid='03' then sc.cid else "" end) ) as cids
from SC sc,Student s
where sc.SId=s.SId
group by sid
having cids = 
(select 
concat(max(case when sc.cid='01' then sc.cid else "" end),max(case when sc.cid='02' then sc.cid else "" end),max(case when sc.cid='03' then sc.cid else "" end) ) as cids
from SC sc
group by sid
having sid = "01") and sid <> "01"; # <>为不等于!=

+------+--------+---------------------+------+--------+
| SId  | Sname  | Sage                | Ssex | cids   |
+------+--------+---------------------+------+--------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   | 010203 |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   | 010203 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   | 010203 |
+------+--------+---------------------+------+--------+

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

先找到老师教课程的cid
再通过SC表筛选出学过这些课的学生的sid，最后再通过Student表排除学过些sid,并匹配sname

select sname 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 = "张三")));

+--------+
| sname  |
+--------+
| 吴兰   |
| 张三   |
| 李四   |
| 李四   |
| 赵六   |
| 孙七   |
+--------+

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

先将分数表中不及格的筛选出来
然后再进行分组，限制不及格的科目数应是两门及其以上

select s.sid,s.sname,avg(SC.score) avg from Student s,SC
where s.sid=SC.sid AND SC.score<60
GROUP BY SC.sid
having count(*)>1;

+------+--------+----------+
| sid  | sname  | avg      |
+------+--------+----------+
| 04   | 李云   | 33.33333 |
| 06   | 吴兰   | 32.50000 |
+------+--------+----------+

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

先将01课程且分数小于60的sid，score筛选出来进行排序，然后在进行连接加上限制条件是student的sid=子表的sid

select Student.sname,A.* from Student
right join
(select sid,score from SC 
where score < 60 and cid = '01'
order by score desc) A
on Student.sid = A.sid;

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

利用 case when 将一列变成多列
利用max（）进行全部匹配，否则只匹配第一个

select sid,avg(score),
max(case when cid='01' then score else '' end) score_01,
max(case when cid='02' then score else '' end) score_02,
max(case when cid='03' then score else '' end) score_03
from SC
GROUP BY sid
ORDER BY avg(score) desc;

+-----+------------+----------+----------+----------+
| sid | avg(score) | score_01 | score_02 | score_03 |
+-----+------------+----------+----------+----------+
| 07  | 93.50000   |          | 89.0     | 98.0     |
| 01  | 89.66667   | 80.0     | 90.0     | 99.0     |
| 05  | 81.50000   | 76.0     | 87.0     |          |
| 03  | 80.00000   | 80.0     | 80.0     | 80.0     |
| 02  | 70.00000   | 70.0     | 60.0     | 80.0     |
| 04  | 33.33333   | 50.0     | 30.0     | 20.0     |
| 06  | 32.50000   | 31.0     |          | 34.0     |
+-----+------------+----------+----------+----------+

14.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

===1.
select 
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from SC sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC;

+-----+--------+--------+----------+----------+--------+--------+--------+--------+
| CId | 最高分 | 最低分 | 平均分   | 选修人数 | 及格率 | 中等率 | 优良率 | 优秀率 |
+-----+--------+--------+----------+----------+--------+--------+--------+--------+
| 01  | 80.0   | 31.0   | 64.50000 |        6 | 0.6667 | 0.3333 | 0.3333 | 0.0000 |
| 02  | 90.0   | 30.0   | 72.66667 |        6 | 0.8333 | 0.0000 | 0.5000 | 0.1667 |
| 03  | 99.0   | 20.0   | 68.50000 |        6 | 0.6667 | 0.0000 | 0.3333 | 0.3333 |
+-----+--------+--------+----------+----------+--------+--------+--------+--------+

===2.
select 
sc.CId ,c.Cname,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率 
from SC sc,Course c
where c.cid = sc.cid
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+
| CId  | Cname  | 最高分    | 最低分    | 平均分    | 选修人数     | 及格率    | 中等率    | 优良率    | 优秀率    |
+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+
| 01   | 语文   |      80.0 |      31.0 |  64.50000 |            6 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02   | 数学   |      90.0 |      30.0 |  72.66667 |            6 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03   | 英语   |      99.0 |      20.0 |  68.50000 |            6 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+------+--------+-----------+-----------+-----------+--------------+-----------+-----------+-----------+-----------+

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

select a.sid, a.cid, a.score, count(a.score<b.score)+1 as rank
from SC as a 
left join SC as b 
on a.score<b.score and a.cid = b.cid
group by a.cid, a.sid,a.score
order by a.cid, rank ASC;

+------+------+-------+------+
| sid  | cid  | score | rank |
+------+------+-------+------+
| 03   | 01   |  80.0 |    1 |
| 01   | 01   |  80.0 |    1 |
| 05   | 01   |  76.0 |    3 |
| 02   | 01   |  70.0 |    4 |
| 04   | 01   |  50.0 |    5 |
| 06   | 01   |  31.0 |    6 |
| 01   | 02   |  90.0 |    1 |
| 07   | 02   |  89.0 |    2 |
| 05   | 02   |  87.0 |    3 |
| 03   | 02   |  80.0 |    4 |
| 02   | 02   |  60.0 |    5 |
| 04   | 02   |  30.0 |    6 |
| 01   | 03   |  99.0 |    1 |
| 07   | 03   |  98.0 |    2 |
| 03   | 03   |  80.0 |    3 |
| 02   | 03   |  80.0 |    3 |
| 06   | 03   |  34.0 |    5 |
| 04   | 03   |  20.0 |    6 |
+------+------+-------+------+

===过程分析
left join… on a.score<b.score
输出内容是:
是循环a表中的全部行, 与b表第一行比, 显示出满足条件的行, 行的列包括b第一行内容
循环a表中的全部行, 与b表中第二行比, 显示出满足条件的行, 行的列包括b第二行内容
……
选好a表中的全部行, 与b表中最后一行比.

==先进行简单查看
select *
from SC a
left join SC b
on a.cid = b.cid
and a.score < b.score   === 通过统计b表中比a表中大的分数的人数来确定名ci
order by a.sid,a.cid;
+------+------+-------+------+------+-------+
| SId  | CId  | score | SId  | CId  | score |
+------+------+-------+------+------+-------+
| 01   | 01   |  80.0 | NULL | NULL |  NULL |
| 01   | 02   |  90.0 | NULL | NULL |  NULL |
| 01   | 03   |  99.0 | NULL | NULL |  NULL |
| 02   | 01   |  70.0 | 03   | 01   |  80.0 |
| 02   | 01   |  70.0 | 01   | 01   |  80.0 |
| 02   | 01   |  70.0 | 05   | 01   |  76.0 |
| 02   | 02   |  60.0 | 03   | 02   |  80.0 |
| 02   | 02   |  60.0 | 01   | 02   |  90.0 |
| 02   | 02   |  60.0 | 07   | 02   |  89.0 |
| 02   | 02   |  60.0 | 05   | 02   |  87.0 |
| 02   | 03   |  80.0 | 01   | 03   |  99.0 |

这里只是截取01，02号学生的成绩与其他同学比较，很明显，01号同学是学霸，3科成绩没人比他高的，02号同学01科目比他高的有3个（03、05、01），02科目比他高的有4个（05、07、03、01），03科目比他高的有2个（01、07）
01学生的01class,分数为80分, 因为没有人比他高, 所以, b. score都是null.

那, 01 01 80自然就是排行第一. 是count(b.score) + 1.

 

02学生 01课程 70分,  这个前缀的行数是3. 证明整张sc表中, 有三个该课程的学生比他分数高, 那他的名字自然就是 3+1 =4. 好了现在明白排名的原理了.

本题参考文章：传送门

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

这个重复时合并名次，distinct（）函数
80，80，80，90 这样排可以看出90应该在重复时合并名次应该是第二，就需要把三个一样的80进行去重

select  a.sid ,a.cid, a.score, count( distinct b.score) as rank
from SC a 
left join   SC b  
on a.cid=b.cid  and a.score<=b.score
group by a.cid, a.sid
order by a.cid, a.score desc;

+------+------+-------+------+
| sid  | cid  | score | rank |
+------+------+-------+------+
| 03   | 01   |  80.0 |    1 |
| 01   | 01   |  80.0 |    1 |
| 05   | 01   |  76.0 |    2 |
| 02   | 01   |  70.0 |    3 |
| 04   | 01   |  50.0 |    4 |
| 06   | 01   |  31.0 |    5 |
| 01   | 02   |  90.0 |    1 |
| 07   | 02   |  89.0 |    2 |
| 05   | 02   |  87.0 |    3 |
| 03   | 02   |  80.0 |    4 |
| 02   | 02   |  60.0 |    5 |
| 04   | 02   |  30.0 |    6 |
| 01   | 03   |  99.0 |    1 |
| 07   | 03   |  98.0 |    2 |
| 03   | 03   |  80.0 |    3 |
| 02   | 03   |  80.0 |    3 |
| 06   | 03   |  34.0 |    4 |
| 04   | 03   |  20.0 |    5 |
+------+------+-------+------+

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

select a.* ,count(a.cj<b.cj)+1 as rank
from 
(select sid,sum(score)  as cj from SC group by sid) a
left join 
(select sid, sum(score) as cj from SC group by sid) b
on a.cj<b.cj
group by a.sid
order by a.cj desc;

+------+-------+------+
| sid  | cj    | rank |
+------+-------+------+
| 01   | 269.0 |    1 |
| 03   | 240.0 |    2 |
| 02   | 210.0 |    3 |
| 07   | 187.0 |    4 |
| 05   | 163.0 |    5 |
| 04   | 100.0 |    6 |
| 06   |  65.0 |    7 |
+------+-------+------+

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

set @crank=0;
select q.sid, total, @crank := @crank +1 as rank from(
select sc.sid, sum(sc.score) as total from SC sc
group by sc.sid
order by total desc)q;

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

分数段所占百分比=分数段人数/总人数

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end) as "[60-0]"
from SC sc left join Course course
on sc.cid = course.cid
group by sc.cid;

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

统计比自身小的行数有多少行
< 3 也就是取前三

select * from SC sc
where (
select count(*) from SC as a 
where sc.cid = a.cid and sc.score<a.score 
)< 3
order by cid asc, sc.score desc;

19.查询每门课程被选修的学生数

只需要在成绩表中按照cid进行分组查询统计即可

select cid,count(sid) from SC
group by cid;

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

通过sid锁定学生信息
通过sc表进行sid分组查询限制选课科数位2

select s.sid,s.sname from Student s
where sid in
(select sid from SC
group by SC.sid
having count(SC.cid)=2
);

21.查询男生、女生人数

select ssex, count(*) from Student
group by ssex;

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

select * from Student 
where Student.sname like '%风%';

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

查找相同姓名，相同性别的学生，只需要按照姓名，性别进行分组，把相同的人数筛选统计出来

=== 这个是筛选同名的
select *,count(*) from Student 
GROUP BY sname 
HAVING count(*)>1;

=== 筛选同名同性别
select *,count(*) from Student 
GROUP BY sname,Ssex
HAVING count(*)>1 ;

+------+--------+---------------------+------+----------+
| SId  | Sname  | Sage                | Ssex | count(*) |
+------+--------+---------------------+------+----------+
| 10   | 李四   | 2017-12-25 00:00:00 | 女   |        2 |
+------+--------+---------------------+------+----------+

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

select * from Student
where year(sage) =1990;

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
+------+--------+---------------------+------+

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

先用子表查询出每科的平均成绩
之后直接进行排序就可以了

select Course.cname,s.cid,s.avg FROM
Course,(select cid,avg(score) avg from SC GROUP BY cid) s
where Course.cid=s.cid
order by s.avg desc,s.cid asc;

+--------+------+----------+
| cname  | cid  | avg      |
+--------+------+----------+
| 数学   | 02   | 72.66667 |
| 英语   | 03   | 68.50000 |
| 语文   | 01   | 64.50000 |
+--------+------+----------+

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

待补充

===1
select s.sid,s.sname,p.avg from 
Student s,(select sid,avg(score) avg from SC GROUP BY sid) p
where s.sid=p.sid and p.avg>=85;

===2
select s.SId, s.Sname,avg(sc.score) avg from 
Student s
RIGHT JOIN
SC sc  
on (s.sid = sc.SId)
group by sc.sid
having avg(sc.score)>85;

+------+--------+----------+
| SId  | Sname  | avg      |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 07   | 郑竹   | 93.50000 |
+------+--------+----------+

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

学生姓名和分数，多表关联查询
限制条件是课程为数学，分数低于60分

select s.sname,SC.score from Student s,SC,Course
where s.sid = SC.sid
and Course.cid = SC.cid
and Course.cname = "数学"
and SC.score < 60;

+--------+-------+
| sname  | score |
+--------+-------+
| 李云   |  30.0 |
+--------+-------+

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

查询所有学生，所以应该使用连接，这里是左连接

select sname, cid, score from Student
left join SC
on Student.sid = SC.sid;

+--------+------+-------+
| sname  | cid  | score |
+--------+------+-------+
| 赵雷   | 01   |  80.0 |
| 赵雷   | 02   |  90.0 |
| 赵雷   | 03   |  99.0 |
| 钱电   | 01   |  70.0 |
| 钱电   | 02   |  60.0 |
| 钱电   | 03   |  80.0 |
| 孙风   | 01   |  80.0 |
| 孙风   | 02   |  80.0 |
| 孙风   | 03   |  80.0 |
| 李云   | 01   |  50.0 |
| 李云   | 02   |  30.0 |
| 李云   | 03   |  20.0 |
| 周梅   | 01   |  76.0 |
| 周梅   | 02   |  87.0 |
| 吴兰   | 01   |  31.0 |
| 吴兰   | 03   |  34.0 |
| 郑竹   | 02   |  89.0 |
| 郑竹   | 03   |  98.0 |
| 张三   | NULL |  NULL |
| 李四   | NULL |  NULL |
| 李四   | NULL |  NULL |
| 赵六   | NULL |  NULL |
| 孙七   | NULL |  NULL |
+--------+------+-------+

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

select s.sname,c.cname,SC.score from Student s,Course c,SC
where s.sid=SC.sid and SC.cid=c.cid and SC.score>70;

+--------+--------+-------+
| sname  | cname  | score |
+--------+--------+-------+
| 赵雷   | 语文   |  80.0 |
| 赵雷   | 数学   |  90.0 |
| 赵雷   | 英语   |  99.0 |
| 钱电   | 英语   |  80.0 |
| 孙风   | 语文   |  80.0 |
| 孙风   | 数学   |  80.0 |
| 孙风   | 英语   |  80.0 |
| 周梅   | 语文   |  76.0 |
| 周梅   | 数学   |  87.0 |
| 郑竹   | 数学   |  89.0 |
| 郑竹   | 英语   |  98.0 |
+--------+--------+-------+

30.查询不及格的课程

直接进行查就可以了

select cid from SC
where score<60
GROUP BY cid;

+------+
| cid  |
+------+
| 01   |
| 02   |
| 03   |
+------+

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

select s.sid,s.sname from Student s
where sid in 
(select sid from SC 
where cid=01 and score>80);

Empty set (0.00 sec)

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

select cid,count(cid) from SC
GROUP BY cid;

+------+------------+
| cid  | count(cid) |
+------+------------+
| 01   |          6 |
| 02   |          6 |
| 03   |          6 |
+------+------------+

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

首要目标就是要知道张三教的课程cid，要获取cid则需要通过name进行判断
接下来限制条件便是cid in 张三老师的cid就可以了

select s.sid,s.sname,max(SC.score) from Student s,SC 
where cid in 
(select cid from Course
where tid in
(select tid from Teacher where tname='张三')) and s.sid =SC.sid;

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

==== 表中没有重复数据，手动添加一条
UPDATE SC SET score=90
where sid = "07"
and cid ="02";
==== 成绩重复情况下，可以直接通过最高分来匹配同学信息
select s.*, sc.score, sc.cid from Student s,Teacher t,Course c,SC sc 
where t.tid = c.tid
and sc.sid = s.sid
and sc.cid = c.cid
and t.tname = "张三"
and sc.score = (
    select Max(sc.score) 
    from Student s,Teacher t,Course c,SC sc 
    where t.tid = c.tid
    and sc.sid = s.sid
    and sc.cid = c.cid
    and t.tname = "张三"
);

+------+--------+---------------------+------+-------+------+
| SId  | Sname  | Sage                | Ssex | score | cid  |
+------+--------+---------------------+------+-------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |  90.0 | 02   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |  90.0 | 02   |
+------+--------+---------------------+------+-------+------+

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

这个我的理解是，判断一个学生的不同课程是否有相同成绩，所以对SC表中的sid和score进行分组即可

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc.cid , sc.sid, sc.score
from SC sc join  (select cid, score from SC  group by sid, score having count(cid)>=2) a 
on sc.cid=a.cid and sc.score=a.score
order by sid ;

+------+------+-------+
| cid  | sid  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 01   | 03   |  80.0 |
+------+------+-------+

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

select * from SC sc
where (
select count(*) from SC as a 
where sc.cid = a.cid and sc.score<a.score 
)< 2
order by cid asc, sc.score desc;

+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 03   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 07   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 07   | 03   |  98.0 |
+------+------+-------+

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

select cid,count(sid) from SC
group by cid
having count(sid)>5;

+------+------------+
| cid  | count(sid) |
+------+------------+
| 01   |          6 |
| 02   |          6 |
| 03   |          6 |
+------+------------+

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

select s.sid,s.sname from Student s
where sid in
(select sid from SC
group by SC.sid
having count(SC.cid)>=2
);

+------+--------+
| sid  | sname  |
+------+--------+
| 01   | 赵雷   |
| 02   | 钱电   |
| 03   | 孙风   |
| 04   | 李云   |
| 05   | 周梅   |
| 06   | 吴兰   |
| 07   | 郑竹   |
+------+--------+

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

=== 这种貌似不行
select s.sid,s.sname from Student s
where sid in
(select sid from SC
group by SC.sid
having count(SC.cid)=count(select cid from Course));

=== 这个可以
select Student.*
from SC sc join Student on sc.sid=Student.sid
group by sid 
having count(*) in (
select count(*)  from Course);

+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
+------+--------+---------------------+------+

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

YEAR()	获取年份
NOW()	返回当前的日期和时间
datediff()	时间差函数，只能精确到天
TIMESTAMPDIFF(精确范围,xxxx,xxxx) 时间差函数，可以精确天、时、分、秒
CURDATE() 函数返回当前的日期
weekofyear() 函数是计算出当前日期所在周数
month() 获取指定日期中的月份

select sname,(YEAR(NOW())-YEAR(sage)) age from Student;

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

select Student.SId as 学生编号,Student.Sname  as  学生姓名,
TIMESTAMPDIFF(YEAR,Student.Sage,CURDATE()) as 学生年龄
from Student

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

select *
from Student 
where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE());

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

select *
from Student 
where WEEKOFYEAR(Student.Sage)=WEEKOFYEAR(CURDATE())+1;

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

select *
from Student 
where MONTH(Student.Sage)=MONTH(CURDATE());

45.查询下月过生日的学生

select *
from Student 
where MONTH(Student.Sage)=MONTH(CURDATE())+1;