------------创建数据库---------------
create database datafrog charset=utf8;
----------- 建表语句-----------------
# 学生表 Student：
create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));
# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10));
# 科目表 Course
create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10));
# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1));
------------ 插入数据语句-----------------
# 学生表 Student：
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
# 科目表 Course
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
# 教师表 Teacher
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
# 成绩表 SC
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);
1.1、查询同时存在01课程和02 课程的情况
提示：1、左边是01课程的记录，右边是02课程的记录；2、sid能关联上就是说明01，02课程记录都有，用子查询+inner join
select
*
from (select * from sc where cid='01') a
inner join (select * from sc where cid='02') b
on a.sid=b.sid;
优化：
select
*
from sc a
inner join sc b
on a.sid=b.sid
where a.cid='01' and b.cid='02';
1.2、查询存在01课程但可能不存在02课程的情况（不存在时显示为null）
提示：1、先找出存在01课程记录，然后和自己的其他课程做关联；
2、如果是02课程就关联上了，如果不是02课程就关联不上。 left join
select
*
from (select * from sc where cid='01') a
left join sc b
on a.sid=b.sid and b.cid='02';
简化：
select
*
from sc a
inner join sc b
on a.sid=b.sid and b.cid='02'
where a.cid='01';
1.3、查询不存在01课程但存在02课程的情况
提示：1、找出不存在01课程的记录后，然后再进行关联找存在02课程；
2、用子查询筛选出不存在01课程的记录后，然后再进行关联找存在02课程；
3、可以用子查询筛选+inner join
不存在01课程的记录：
not in (select sid from sc where cid='01')
select
*
from (select * from sc where sid not in (select sid from sc where cid='01')) a
inner join sc b
on a.sid=b.sid and b.cid='02';
简化：
1、找出不存在01课程的学生；
2、这些学生里面学习过02课程的就是我们想要的；
select
*
from sc a
where sid not in (select sid from sc where cid='01')
and cid='02';
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
提示：要求查询平均成绩大于等于60分的同学信息，首先确定是在成绩表里面找。
找到了这样的同学后，是不是用sid去学生信息表里面关联，就可以得到学生的姓名信息。那这里关键就是找sid。
select
sid,
avg(score) as avg_score
from sc
group by sid
having avg(score)>=60;---找出平均分>=60的同学sid
select
a.sid,
a.sname,
b.avg_score
from student a
inner join
(select
sid,
avg(score) as avg_score
from sc
group by sid
having avg(score)>=60) b
on a.sid=b.sid;
3、查询在sc表存在成绩的学生信息
提示：成绩表肯定都是有学生的，所以用成绩表左关联就可以得到学生信息
select
b.* 这里的b*只能查询出b.sid
from sc a
left join student b
on a.sid=b.sid
group by b.sid; #group by是去重作用；这个语句不完善。
月考分班级查询班级数据情况：
能查询的字段：分组（分班级）的一个数据情况
1、班级名（分组字段值）
2、成绩最大值、最小值，平均值；
查一条数据，查什么呢？
group by语法：
只能查询分组字段，其他字段要以聚合函数的形式被查询出来
select
b.*
from
(select
sid
from sc
group by sid) a
left join student b
on a.sid=b.sid;
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示null）
提示：所有同学存在于学生表，课程表，成绩情况在成绩表。
那就是要学生表左关联成绩总汇表得到记录，关联不上的说明就是没成绩。
select
sid,
count(cid) as cons,
sum(score) as sum_score
from sc
group by sid; ----成绩汇总表
select
a.sid,
a.sname,
b.cons，
b.sum_score
from student a
left join
(select
sid,
count(cid) as cons,
sum(score) as sum_score
from sc
group by sid) b
on a.sid=b.sid;
简化：
select
a.sid,
a.sname,
count(b.cid) as cons,
sum(b.score) as sum_score
from student a
left join sc b
on a.sid=b.sid
group by a.sid;
5、查询‘李’姓老师的数量
提示：like语法的使用
select * from teacher where tname like '李%'；----查询出李姓的老师
select count(1) as cons from teacher where tname like '李%'；---李姓的老师的总数
6、查询学习过‘张三’老师授课的同学的信息
提示：关联教师表、课程表、成绩表、学生表，得到每个同学学习每门课程的老师是谁，然后筛选张三老师的记录。
教师表：teacher tid
课程表：course tid+cid
成绩表：sc cid+sid
学生表：student sid
1、得到教师和课程的关系
select * from course a inner join teacher b on a.tid=b.tid;
2、得到教师和成绩的关系
select * from sc a inner join (select a.*,b.tname from course a inner join teacher b on a.tid=b.tid) b on a.cid=b.cid;
3、得到学生和教师的关系
select * from student a inner join (select a.*,b.cname,b.tid,b.tname from sc a inner join (select a.*,b.tname from course a inner join teacher b on a.tid=b.tid) b on a.cid=b.cid) b
on a.sid=b.sid;
4、筛选出‘张三’老师的学生记录
select a.*,b.tname from student a inner join (select a.*,b.cname,b.tid,b.tname from sc a inner join (select a.*,b.tname from course a inner join teacher b on a.tid=b.tid) b on a.cid=b.cid) b
on a.sid=b.sid
where tname='张三';
或者：
select
b.sid,
b.sname,
b.sage
from sc a
left join student b
on a.sid=b.sid
where cid in
(select cid from course
where tid=(select tid from teacher where tname='张三'))
group by sid;
7、查询没有学全所有课程的同学的信息
提示：只需要用学习信息关联到成绩sc表得到每个人的课程信息，然后分组计算就知道是不是满足全部学了。这里需要成绩表一门功课只有一条记录。
select
*
from student a
left join sc b
on a.sid=b.sid
group by a.sid
having count(b.cid)<(select count(cid) from course);
或者：
select
a*，
b.*
from sc a
inner join student b
on a.sid=b.sid
group by a.sid
having count(1)<总课程数；
8、查询至少有一门课程与学号为‘01’的同学所学相同的同学的信息
select
distinct b.*
from sc a
left join student b
on a.sid=b.sid
where cid in(select cid from sc where sid='01');
或者：
--01同学学习了什么课程？
select cid from sc where sid='01';
--其他同学有没有学习上面的课程？
select
distinct
b.*
from sc a
inner join student b
on a.sid=b.sid
where a.cid in (select cid from sc where sid='01');
或者：
select
c.*
from student c
inner join
(select
a.sid
from sc a
where a.cid in (select cid from sc where sid='01')
group by a.sid) d
on c.sid=d.sid;
9、查询和01号同学学习的课程完全相同的其他的同学的信息
提示：可以从两个方面来保证，第一没有01号课程外的课程记录，第二课程数量一致；
select
b.*
from (select * from sc where sid not in (select sid from sc where sid='01'))
left join student b
on a.sid=b.sid
group by a.sid
having count(cid)=(select count(cid) from sc where sid='01');
或者：
---没有学习01号同学外的其他课程的同学
01号同学学习了哪些课程？
select cid from where sid='01';
找到学习了01号同学外课程的同学
select sid from sc where cid not in (select cid from where sid='01')
---学习课程数量要一致
select
sid
from sc
where sid not in (***)
group by sid
having count(1)=(select count(cid) from sc where sid='01')
汇总：
select
b.*
from sc a
inner join student b
on a.sid=b.sid
where a.sid not in (select sid from sc where cid not in (select cid from where sid='01'))
and a.sid!='01'
group by a.sid
having count(1)=(select count(1) from sc where sid='01');
10、查询没学过‘张三’老师讲授的任一门课程的学生姓名
提示：没有学习过，那找出学习过的，在用not in 来判断一下就好了
教师表：teacher tid
课程表：course tid+cid
成绩表：sc cid+sid
学生表：student sid
select
*
from student a
where a.sid not in
(select
sid
from sc a
left join course c
on a.cid=c.cid
inner join teacher d
on c.tid=d.tid and d.tname='张三');
或者：
1、找出学习过“张三”课程的sid记录
select
a.sid
from sc a
left join course b
on a.cid=b.cid
left join teacher c
on b.tid=c.tid
left join student d
on a.sid=d.sid
where c.tname='张三';
2、使用not in 判断没有学习过张三老师课程的信息
select
a.*
from student a
where sid not in
(select
a.sid
from sc a
left join course b
on a.cid=b.cid
left join teacher c
on b.tid=c.tid
left join student d
on a.sid=d.sid
where c.tname='张三')
11、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
提示：学生表、成绩表，肯定要用到分组聚合
select
*,
avg(score) as avg_sco
from sc a
left join student b
on a.sid=b.sid
where a.score<60
group by a.sid
having count(cid)>=2; -----标准答案
或者：
1、首先可以先求平均值
select
sid,
avg(score) as avg_score
from sc
group by sid;
2、求出有两门以上课程不及格的同学
select
sid
from sc
where score<60
group by sid
having count(1)>1;
3、是不是可以在求平均值的时候就筛选出我们要的同学
select
from sc a
left join student b
on a.sid=b.sid
inner join (两门课程以上不及格的同学) c
on a.sid=c.sid
group by a.sid;
汇总：
select
a.sid,
b.sname,
avg(score) as avg_score
from sc a
left join student b
on a.sid=b.sid
inner join (select
sid
from sc
where score<60
group by sid
having count(1)>1) c
on a.sid=c.sid
group by a.sid;
12、检索01课程分数小于60分，按分数降序排列的学生信息
提示：成绩表，学生表
1、筛选01课程分数小于60分的学生的sid
select
sid
from sc
where cid='01' and score <60;
2、关联学生信息，并进行分数降序排序
select
a.sid,
a.score,
b.*
from (select
sid,score
from sc
where cid='01' and score <60) a
left join student b
on a.sid=b.sid
order by a.score desc;
13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
提示：成绩表、学生表，
1、求学生的平均成绩
select
sid,
avg(score) as avg_score
from sc
group by sid;
2、给每一条学习成绩信息加上平均成绩，然后排序
select
a.*,
avg_score
from sc a
left join (select
sid,
avg(score) as avg_score
from sc
group by sid) b
on a.sid=b.sid
order by avg_score desc;
14、查询各科成绩最高分、最低分和平均分
比如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
及格为：>=60,中等为：[70-80)，优良为：[80-90)，优秀为：>=90.要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
提示：分组聚合，这里考察一个条件计数的技巧
1、各科的平均分、最高分、及格率
select
cid,
max(score) as 最高分,
min(score) as 最低分,
avg(score) as 平均分,
count(1) as 选修人数,
sum(case when score>=60 then 1 else 0 end)/count(1) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(1) as 中等率,
sum(case when score>=80 and 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
order by 选修人数 desc,cid asc;
15、按各科成绩进行排序，并显示排名，Score重复时继续排序
select @rank:=1;
select @rank;
select
sid,
cid,
score,
@rank:=@rank+1 as rn
from sc,(select @rank:=0) as t
order by score desc;
15.1、按各科成绩进行排序，并显示排名，Score重复时合并名次
select
*,
case when (@sco=score) then @rank
else @rank:=@rank+1 end as rn,
@scor:=score #---保存上一次的分数
from sc,(select @rank:=0,@sco:=null) as t order by score desc;
或者：
select
a.*,
case when @sco=score then @rank
when @sco:=score then @rank:=@rank+1
end as rn
from sc a,(select @rank:=0,@score:=null) as t
order by a.score desc;
16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺
提示：自定义变量
定义方法一：
set @a:=2;---定义a=2;
定义方法二：
select @b:=4;
修改值：重新赋值：select @b:=6;
1、先求学生总成绩
select
sid,
sum(score) as scos
from sc
group by sid
order by scos desc;
2、名次排序
select @sco:=null,@rank:=0;
select
a.*,
@rank:=if(@sco=score,'',@rank+1) as rank,
@sco:=scos
from (select
sid,
score,
sum(score) as scos
from sc
group by sid
order by scos desc) a
inner join
(select @sco:=null,@rank:=0) b ;
17、统计各科成绩各分段人数：课程编号，课程名称，
[100,85),[85,70),[70,60),[60,0)及所占百分比
提示：分组统计技术，判断各分数段的人数情况
1、直接进行分组统计
select
cid，
sum(case when 0<score and score<=60 then 1 else 0 end)/count(1) as '(0-60]', # 该分数段人数/该课程总人数=该分数段人数百分比，
sum(case when 60<score and score<=70 then 1 else 0 end)/count(1) as '(60-70]',
sum(case when 70<score and score<=85 then 1 else 0 end)/count(1) as '(70-85]',
sum(case when 85<score and score<=100 then 1 else 0 end)/count(1) as '(85-100]'
from sc
group by cid;
2、加一个百分号%
select
cid，
concat(sum(case when 0<score and score<=60 then 1 else 0 end)/count(1)*100,'%') as '(0-60]', # 该分数段人数/该课程总人数=该分数段人数百分比，
concat(sum(case when 60<score and score<=70 then 1 else 0 end)/count(1)*100,'%') as '(60-70]',
concat(sum(case when 70<score and score<=85 then 1 else 0 end)/count(1)*100,'%') as '(70-85]',
concat(sum(case when 85<score and score<=100 then 1 else 0 end)/count(1)*100,'%) as '(85-100]'
from sc
group by cid;
18、查询各科成绩前三名的记录
提示：前三名转化为若大于此成绩的数量少于3即为前三名。
#这种其实就是笛卡儿积的写法，只不过灵活好用很多，但是不好查看名次
select
*
from sc a
where (select count(1) from sc b where a.cid=b.cid and b.score>a.score)<3;
或者：
select
a.*
from sc a
where #大于此条成绩的记录少于3条(select count(1) from sc b where b.cid=a.cid and b.score>a.score)<3
order by cid desc,score desc;
19、查询每门课程被选修的学生数
提示：按课程id分组统计数据量
select
cid,
count(1) as cons
from sc
group by cid;
20、查询出只选修两门课程的学生学号和姓名
提示：分组查询，根据分组情况进行筛选
select
a.sid,
b.sname,
count(1) as cons
from sc a
left join student b
on a.sid=b.sid
group by a.sid,b.sname
having count(1)=2;
21、查询男生和女生人数
select
ssex,
count(1) as cons
from student
group by ssex;
22、查询名字中含有‘风’字的学生信息
提示：使用like语法
select * from student where sname like'%风%';
23、查询出同名同性学生名单，并统计同名同性人数
提示：自关联筛选出符合条件的记录，要求名字，性别相等，但是sid不同。
select
a.sname,
a.ssex,
count(1) as cons
from student a
inner join student b
on a.sname=b.sname and a.ssex=b.ssex and a.sid!=b.sid
group by a.sname,a.ssex;
24、查询1990年出生的学生名单
提示：关键字段“出生日期Sage”，year函数
select year("2020-12-01");
select
*
from student
where year(sage)='1990';
25、查询每门课程的平均成绩，结果按照平均成绩降序排列，平均成绩相同时，按照课程编号升序排列
提示：分组聚合然后排序
select
cid,
avg(score) as avg_score
from sc
group by cid
order by avg_score desc,cid asc;
26、查询平均成绩大于等于85分的所有学生的学号、姓名和平均成绩
提示：需要分组聚合求平均值，然后筛选学生信息
1、先找出符合要求的sid
select
sid,
avg(score) as avg_score
from sc
group by sid
having avg(score)>=85;
2、关联学生信息
select
a.sid,
b.sname,
b.ssex,
a.avg_score
from (select
sid,
avg(score) as avg_score
from sc
group by sid
having avg(score)>=85) a
left join student b
on a.sid=b.sid;
27、查询课程名称为数学，且分数低于60分的学生姓名和分数
提示：条件关联查询
1、先查询数学的课程 cid
select cid from course where cname='数学'；
2、求分数低于60分的成绩记录
select
a.sid,
a.cid,
b.sname,
b.ssex,
a.score
from sc a
left join student b
on a.sid=b.sid
where cid=(select cid from course where cname='数学')
and a.score<60;
28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
提示：给学生信息加上课程情况
select
*
from student a
left join sc b
on a.sid=b.sid
29、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
提示：条件查询结果
1、先找出成绩大于70分的成绩记录，这里面的sid就是要找的学生信息
select
*
from sc a
left join course c
in a.cid=c.cid
where a.score>70;
2、和学生表进行一个关联得到学生的信息记录
select
a.sname,
a.score,
c.cname
from sc a
left join course c
on a.cid=c.cid
left join student s
on a.sid=s.sid
where a.score>70;
30、查询存在不及格的课程
提示：条件查询结果
1、筛选出小于60分的成绩记录
select score,cid from sc where score<60;
2、去重得到cid,如果需要课程信息还可以和course表做关联
select
a.*
from course a
where a.cid in (select score,cid from sc where score<60);
31、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
提示：关联筛选
select
a.sid,
a.sname,
b.score,
b.cid
from student a
inner join sc b
on a.sid=b.sid
where b.cid='01'
and b.score>80;
32、求每门课程的学生人数
提示：分组聚合
select
cid,
count(1) as cons
from sc
group by cid;
//33、成绩不重复，查询选修‘张三’老师所授课程的学生中，成绩最高的学生信息及其成绩
select
*
from sc a
left join student b
on a.sid=b.sid
left join course c //
33、假设成绩不重复，查询选修‘张三’老师所授课的学生中，成绩最高的学生信息及其成绩
提示：条件筛选查询，只需要一个人
select
*
from sc a
left join student b
on a.sid=b.sid
left join course c
on a.cid=c.cid
left join teacher t
on c.tid=t.tid
where d.tname='张三'
order by a.score desc
limit 1;
34、成绩有重复的情况下，查询选修‘张三’老师所授课的学生中，成绩最高的学生信息及其成绩
select
*
from
(select
a.*,
case when @score=score then @rank
when @score:=score then @rank:=@rank+1 end as rn
from
(select
a.sid,
b.sname,
a.score,
c.cid,
d.tname
from sc a
left join student b
on a.sid=b.sid
left join course c
on a.cid=c.cid
left join teacher t
on c.tid=t.tid
where d.tname='张三') a ,(select @score:=null,@rank:=0) t) s
where rn=1;
35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
提示：条件关联筛选
select
a.sid,
a.cid
from sc a
inner join sc b
on a.sid=b.sid
where a.cid!=b.cid and a.score=b.score
group by a.sid,a.cid;
36、查询每门科目成绩最好的前两名
提示：用户变量排序
select
sid,
cid,
score,
rank
from
(select
sc.*,
@rank:=if(@c_cid=cid,if(@sco=score,@rank,@rank+1),1) as rank,
@sco:=score,
@c_cid:=cid
from sc,(select @sco:=null,@rank:=0,@c_cid:=null) b
order by cid,score desc) a
where a.rank<3;
37、统计每门课程的学生选修人数（超过5人的课程才统计）
提示：分组统计
select
cid,
count(1) as cons
from sc
group by cid
having count(1)>5;
38、检索至少选修两门课程的学生学号
提示：分组查询
select
sid,
count(1) as cons
from sc
group by sid
having count(1)>=2;
39、查询选修了全部课程的学生信息
select
*
from student a
where (select count(1) from sc b where a.sid=b.sid)
=(select count(1) from course)
或者：
select
b.*
from sc a
inner join student b
on a.sid=b.sid
group by a.sid
having count(1)=(select count(1) from course);
或者：
select
a.*
from student a
where #这个学生的成绩数量和课程数据一致，就保留下来 (select * from sc b where a.sid=b.sid)
=(select count(1) from course);
40、查询各个学生的年龄，只按照年份来算。
提示：使用日期函数进行相减
select year('2020-12-01');
select now();
select curdate();
select
*,
year(now())-year(sage) as age
from student;
41、要求按照出生日期来算年龄，当前月日<出生年月的月日，则年龄减一
---timestampdiff日期相减函数
select timestampdiff(year,"2020-05-01","2020-06-01"); -- -1
select timestampdiff(day,"2020-05-01","2019-01-01"); -- -485
select timestampdiff(hour,"2020-08-08 12:00:00","2020-08-08 00:00:00"); -- -12
---获取当前时间
select now();
select curdate();
select
*,
timestampdiff(year,sage,now()) as age
from student;
42、查询本周过生日的学生
select week("2004-06-03")
select
*,
week(sage),
week(now)
from student
where week(sage)=week(now());
43、查询下周过生日的学生
select
*,
week(sage),
week(now)
from student
where week(sage)=week(now())+1;
44、查询本月过生日的学生
select
*,
month(sage),
month(now())
from student
where month(sage)=month(now());
45、查询下月过生日的学生
select
*,
month(sage),
month(now())
from student
where month(sage)=month(now())+1;
