-- 创建数据库school
create database school;

-- 选择进入school数据库
use school;


-- ------------建表导数-------------
-- 创建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10),
s_birth date,
s_sex varchar(10));

-- 导入数据
insert into stu values
(\'01\' , \'赵雷\' , \'1990-01-01\' , \'男\'),
(\'02\' , \'钱电\' , \'1990-12-21\' , \'男\'),
(\'03\' , \'孙风\' , \'1990-05-20\' , \'男\'),
(\'04\' , \'李云\' , \'1990-08-06\' , \'男\'),
(\'05\' , \'周梅\' , \'1991-12-01\' , \'女\'),
(\'06\' , \'吴兰\' , \'1992-03-01\' , \'女\'),
(\'07\' , \'郑竹\' , \'1992-04-21\' , \'女\'),
(\'08\' , \'王菊\' , \'1990-01-20\' , \'女\');

select * from stu; -- 检查数据
select count(*) from stu; -- 检查总行数


-- 创建co
create table co(
c_id varchar(10) primary key,
c_name varchar(10),
t_id varchar(10));

-- 导入数据
insert into co values
(\'01\' , \'语文\' , \'02\'),
(\'02\' , \'数学\' , \'01\'),
(\'03\' , \'英语\' , \'03\');

select * from co; -- 检查数据
select count(*) from co; -- 检查总行数


-- 创建te
create table te(
t_id varchar(10) primary key,
t_name varchar(10));

-- 导入数据
insert into te values
(\'01\' , \'张三\'),
(\'02\' , \'李四\'),
(\'03\' , \'王五\');

select * from te; -- 检查数据
select count(*) from te; -- 检查总行数


-- 创建sc
create table sc(
s_id varchar(10),
c_id varchar(10),
score int);

-- 导入数据
insert into sc values
(\'01\' , \'01\' , 80),
(\'01\' , \'02\' , 90),
(\'01\' , \'03\' , 99),
(\'02\' , \'01\' , 70),
(\'02\' , \'02\' , 60),
(\'02\' , \'03\' , 80),
(\'03\' , \'01\' , 80),
(\'03\' , \'02\' , 80),
(\'03\' , \'03\' , 80),
(\'04\' , \'01\' , 50),
(\'04\' , \'02\' , 30),
(\'04\' , \'03\' , 20),
(\'05\' , \'01\' , 76),
(\'05\' , \'02\' , 87),
(\'06\' , \'01\' , 31),
(\'06\' , \'03\' , 34),
(\'07\' , \'02\' , 89),
(\'07\' , \'03\' , 98);

select * from sc; -- 检查数据
select count(*) from sc; -- 检查总行数


######################################################################################################################################################
-- 1、查询\"01\"课程比\"02\"课程成绩高的学生信息及课程分数
#查询选修\"01\"课程的学生及分数
select * from sc where c_id=\'01\';
#查询选修\"02\"课程的学生及分数
select * from sc where c_id=\'02\';

select stu.*,sc.c_id,sc.score
from (select * from sc where c_id=\'01\') t1
join (select * from sc where c_id=\'02\') t2 on t1.s_id=t2.s_id
join stu on t1.s_id=stu.s_id
join sc on stu.s_id=sc.s_id
where t1.score>t2.score;

######################################################################################################################################################
-- 2、查询\"01\"课程比\"02\"课程成绩低的学生的信息及课程分数
select stu.*,sc.c_id,sc.score
from (select * from sc where c_id=\'01\') t1
join (select * from sc where c_id=\'02\') t2 on t1.s_id=t2.s_id
join stu on t1.s_id=stu.s_id
join sc on stu.s_id=sc.s_id
where t1.score<t2.score;

select stu.*,sc.c_id,sc.score
from sc t1
join sc t2 on t1.s_id=t2.s_id and t1.c_id=\'01\' and t2.c_id=\'02\' and t1.score<t2.score
join stu on t1.s_id=stu.s_id
join sc on stu.s_id=sc.s_id;

######################################################################################################################################################
-- 3、查询平均成绩大于等于60分的同学的学生编号、学生姓名和平均成绩
select stu.s_id,s_name,avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having avg(score)>=60;

######################################################################################################################################################
-- 4、查询平均成绩小于60分的同学的学生编号、学生姓名和平均成绩
select stu.s_id,s_name,avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
having avg(score)<60;

######################################################################################################################################################
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
#要查询所有学生的信息，无论某些学生是否有选课都要查询出来，因此以stu为主表连接sc
select stu.s_id,s_name,count(c_id) 选课门数,sum(score) 总成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id;

######################################################################################################################################################
-- 6、查询\"李\"姓老师的数量
select count(t_id) from te where t_name like \'李%\';

######################################################################################################################################################
-- 7、查询学过\"张三\"老师授课的同学的信息
select stu.*
from sc
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
left join stu on sc.s_id=stu.s_id
where t_name=\'张三\';
######################################################################################################################################################
-- 8、查询没学过\"张三\"老师授课的同学的信息
#查询学过\"张三\"老师授课的同学
select s_id
from sc
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
where t_name=\'张三\';

select *
from stu
where s_id not in (select s_id
                                        from sc
                                        left join co on sc.c_id=co.c_id
                                        left join te on co.t_id=te.t_id
                                        where t_name=\'张三\');
######################################################################################################################################################
-- 9、查询学过编号为\"01\"并且也学过编号为\"02\"的课程的同学的信息
select stu.*
from sc
left join stu on sc.s_id=stu.s_id
where c_id in (\'01\',\'02\')
group by sc.s_id
having count(c_id)=2;

######################################################################################################################################################
-- 10、查询学过编号为\"01\"但是没有学过编号为\"02\"的课程的同学的信息
select stu.*
from sc
left join stu on sc.s_id=stu.s_id
where c_id in (\'01\',\'02\')
group by sc.s_id
having group_concat(c_id)=\'01\';

######################################################################################################################################################
#-- 11、查询没有学全所有课程的同学的信息
#查询课程总数
select count(c_id) from co;

#查询每个学生的选课门数
select s_id,count(c_id) from sc group by s_id;

#查询选课门数小于课程总数的学生
select s_id,count(c_id) from sc group by s_id having count(c_id)<(select count(c_id) from co);

select stu.*
from (select s_id,count(c_id) from sc group by s_id having count(c_id)<(select count(c_id) from co)) t
join stu on t.s_id=stu.s_id;

######################################################################################################################################################
#--12、查询至少有一门课与学号为\"01\"的同学所学相同的同学的信息
#查询学号为\"01\"的同学所学课程编号
select c_id from sc where s_id=\'01\';

select distinct stu.*
from sc
left join stu on sc.s_id=stu.s_id
where c_id in (select c_id from sc where s_id=\'01\') and stu.s_id<>\'01\';

######################################################################################################################################################
#--13、查询和\"01\"号的同学学习的课程完全相同的其他同学的信息
#查询学号为\"01\"的同学所学课程编号
select group_concat(c_id order by c_id) from sc where s_id=\'01\';

select stu.*,group_concat(c_id order by c_id)
from sc
left join stu on sc.s_id=stu.s_id
group by sc.s_id
having group_concat(c_id order by c_id)=(select group_concat(c_id order by c_id) from sc where s_id=\'01\') and stu.s_id<>\'01\';

######################################################################################################################################################
#--14、查询没学过\"张三\"老师讲授的任一门课程的学生姓名
select s_name
from stu
where s_id not in (select s_id from sc where c_id=(select c_id from te left join co on te.t_id=co.t_id where t_name=\'张三\'));

######################################################################################################################################################
#--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
#查询每个学生的不及格门数
select s_id,count(c_id) 不及格门数 from sc where score<60 group by s_id having 不及格门数>=2;

#查询每个学生的平均成绩
select s_id,avg(score) 平均成绩 from sc group by s_id;

#将查询结果作为两个表t1,t2和stu连接t1.s_id=t2.s_id,t2.s_id=stu.s_id
select t1.s_id,s_name,平均成绩
from (select s_id,count(c_id) 不及格门数 from sc where score<60 group by s_id having 不及格门数>=2) t1
join (select s_id,avg(score) 平均成绩 from sc group by s_id) t2 on t1.s_id=t2.s_id
join stu on t2.s_id=stu.s_id;

######################################################################################################################################################
#----16、检索\"01\"课程分数小于60，按分数降序排列的学生信息
#首先查询出01课程分数小于60的学生id
select s_id ,score from sc where c_id=\'01\' and score<60;
#将查询结果作为一个表t跟stu连接t.s_id=stu.s_id，同时以分数降序显示学生信息
select stu.*,score
from (select s_id ,score from sc where c_id=\'01\' and score<60) t
left join stu on t.s_id=stu.s_id
order by score desc;

######################################################################################################################################################
#--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.s_id,
                sum(case c_id when\'01\' then score else 0 end) \'01\',
                sum(case c_id when\'02\' then score else 0 end) \'02\',
                sum(case c_id when\'03\' then score else 0 end) \'03\',
                avg(score) 平均成绩
from stu left join sc on stu.s_id=sc.s_id
group by stu.s_id
order by 平均成绩 desc;

######################################################################################################################################################
#--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select
        sc.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,
        avg(score>=60) as 及格率,avg(score>=70 and score<80) as 中等率,
    avg(score>=80 and score<90) as 优良率,avg(score>=90) as 优秀率
from sc
left join co on sc.c_id=co.c_id
group by sc.c_id;

######################################################################################################################################################
#--19、查询学生的总成绩并进行排名
select s_id,sum(score) 总成绩,dense_rank() over(order by sum(score) desc) 排名
from sc
group by s_id;
######################################################################################################################################################
#--20、按各科成绩进行排序，并显示排名
select *,dense_rank() over(partition by c_id order by score desc) 排名
from sc;

######################################################################################################################################################
#--21、查询不同老师所教不同课程平均分从高到低显示
select te.t_id,t_name,c_name,avg(score) 课程平均分
from te left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
group by te.t_id,co.c_id
order by 课程平均分 desc;

######################################################################################################################################################
#--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *
from
(select *,dense_rank() over(partition by c_id order by score desc) 排名
from sc) t
where 排名 between 2 and 3;

#######################################################################################################################################
#--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比  
select sc.c_id,c_name,
avg(score>=0 and score<60) as \'[0-60]所占百分比\',
avg(score>=60 and score<70) as \'[60-70]所占百分比\',
avg(score>=70 and score<85) as \'[70-85]所占百分比\',
avg(score>=85 and score<=100) as \'[85-100]所占百分比\'
from sc
left join co on sc.c_id=co.c_id
group by sc.c_id;

#######################################################################################################################################
#--24、查询每个学生平均成绩及其名次
select s_id,avg(score) 平均成绩,dense_rank() over(order by avg(score) desc) 排名
from sc
group by s_id;

#######################################################################################################################################
#--25、查询各科成绩前三名的记录
select *
from
(select *,dense_rank() over(partition by c_id order by score desc) 排名
from sc) t
where 排名<=3;

#######################################################################################################################################
#--26、查询每门课程被选修的学生数
select c_id,count(s_id) as 选修人数
from sc
group by c_id;

#######################################################################################################################################
#--27、查询出只有两门课程成绩的全部学生的学号和姓名
#查询只有两门课程成绩的学生编号
select s_id,count(score) from sc group by s_id having count(score)=2;

#将查询结果作为一个表t跟stu连接t.s_id=stu.s_id显示学生姓名
select t.s_id,s_name
from (select s_id from sc group by s_id having count(s_id)=2) t
left join stu on t.s_id=stu.s_id;

#######################################################################################################################################
#--28、查询男生、女生人数
select count(s_sex) 人数
from stu
group by s_sex;

#######################################################################################################################################
#--29、查询名字中含有\"风\"字的学生信息
select *
from stu
where s_name like \'%风%\';

#######################################################################################################################################
#--30、查询同名同姓学生名单，并统计同名人数
select s_name,count(s_name) 重复次数
from stu
group by s_name
having 重复次数>1;

#######################################################################################################################################
#--31、查询1990年出生的学生名单
select s_name
from stu
where year(s_birth)=1990;

#######################################################################################################################################
#--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,avg(score) 平均成绩
from sc
group by c_id
order by 平均成绩 desc,c_id;

#######################################################################################################################################
#--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
#查询平均成绩大于等于85的学生编号
select s_id,avg(score) 平均成绩 from sc group by s_id having 平均成绩>=85;

#将查询结果作为一个表t跟stu连接t.s_id=stu.s_id显示学号、姓名和平均成绩
select t.s_id,s_name,平均成绩
from (select s_id,avg(score) 平均成绩 from sc group by s_id having 平均成绩>=85) t  
left join stu on t.s_id=stu.s_id;

#########################################################################################################################
#--34、查询课程名称为\"数学\"，且分数低于60的学生姓名和分数
#将stu,sc,co三张表连接stu.s_id=sc.s_id,sc.c_id=co.c_id每个学生每门课程的成绩，筛选数学不及格的学生
select s_name,score
from stu join sc on stu.s_id=sc.s_id join co on sc.c_id=co.c_id
where c_name=\'数学\' and score<60;

#########################################################################################################################
#--35、查询所有学生的课程及分数情况
select
        s_id,
        sum(if(c_id=\'01\',score,0)) as \'01\',
    sum(if(c_id=\'02\',score,0)) as \'02\',
    sum(if(c_id=\'03\',score,0)) as \'03\'
from sc
group by s_id;

select
        s_id,
    sum((c_id=\'01\')*score) as \'01\',
    sum((c_id=\'02\')*score) as \'02\',
    sum((c_id=\'03\')*score) as \'03\'
from sc
group by s_id;

#########################################################################################################################
#--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
#查询任何一门课程成绩在70分以上的记录
select * from sc where score>70;

#将查询结果作为一张表t跟stu,co连接t.s_id=stu.s_id,t.c_id=co.c_id显示姓名、课程名称和分数
select s_name,c_name,t.score
from (select * from sc where score>70) t join stu on t.s_id=stu.s_id join co on t.c_id=co.c_id;

#########################################################################################################################
#--37、查询出现过学生考试不及格的课程
#查询成绩不合格的课程编号
select distinct c_id from sc where score<60;

#将查询结果作为一张表t跟co连接t.c_id=co.c_id显示课程名称
select t.c_id,c_name
from (select distinct c_id from sc where score<60) t
join co on t.c_id=co.c_id;

#########################################################################################################################
#--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select stu.s_id,s_name
from stu left join sc on stu.s_id=sc.s_id
where c_id=\'01\' and score>80;

#########################################################################################################################
#--39、求每门课程的学生人数
select c_id,count(s_id) 学生人数
from sc
group by c_id;

#########################################################################################################################
#--40、查询选修\"张三\"老师所授课程的学生中，成绩最高的学生信息及其成绩
#查询选修\"张三\"老师所授课程的学生中的最高分
select max(score)
from sc
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
left join stu on sc.s_id=stu.s_id
where t_name=\'张三\';

select stu.*,score
from sc
left join co on sc.c_id=co.c_id
left join te on co.t_id=te.t_id
left join stu on sc.s_id=stu.s_id
where t_name=\'张三\' and score=(select max(score)
                                                                from sc
                                                                left join co on sc.c_id=co.c_id
                                                                left join te on co.t_id=te.t_id
                                                                left join stu on sc.s_id=stu.s_id
                                                                where t_name=\'张三\');

#########################################################################################################################
#--41、查询课程不同、成绩相同的学生的学生编号、课程编号、学生成绩  
select * from sc t1
where s_id in (select s_id from sc t2 where t1.s_id=t2.s_id and t1.c_id<>t2.c_id and t1.score=t2.score);

select distinct t1.* from sc t1,sc t2 where t1.s_id=t2.s_id and t1.c_id<>t2.c_id and t1.score=t2.score;
#########################################################################################################################
#--42、查询每门功成绩最好的前两名
select *
from
(select *,dense_rank() over(partition by c_id order by score desc) 排名
from sc) t
where 排名<=2;

#########################################################################################################################
#--43、统计每门课程的学生选修人数（超过5人的课程才统计）。
#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select c_id,count(s_id) as 选修人数
from sc
group by c_id
having count(s_id)>5
order by 选修人数 desc,c_id;

#########################################################################################################################
#--44、检索至少选修两门课程的学生学号  
select s_id
from sc
group by s_id
having count(s_id)>=2;

#########################################################################################################################
#--45、查询选修了全部课程的学生信息  
select *
from stu
where s_id in (select s_id from sc group by s_id having count(s_id)=(
select count(c_id) from co));

#########################################################################################################################
#--46、查询各学生的年龄
select s_id,s_name,(year(now())-year(s_birth)) as 年龄
from stu;

#########################################################################################################################
#--47、查询本周过生日的学生
#今天是一周中的周几
select date_format(curdate(),\'%w\');
#本周第一天
select date_sub(curdate(),interval date_format(curdate(),\'%w\') day);
#本周最后一天
select date_add(curdate(),interval 6-date_format(curdate(),\'%w\') day);

select *
from stu
where date_format(s_birth,\'2020-%m-%d\') between date_sub(curdate(),interval date_format(curdate(),\'%w\') day) and date_add(curdate(),interval 6-date_format(curdate(),\'%w\') day);

select *
from stu
where week(date_format(s_birth,\'2020-%m-%d\'))=week(curdate());
#########################################################################################################################
#--48、查询下周过生日的学生
#今天距离下周第一天还有几天
select 7-date_format(curdate(),\'%w\');
#下周第一天
select date_add(curdate(),interval 7-date_format(curdate(),\'%w\') day);
#下周最后一天
select date_add(curdate(),interval 13-date_format(curdate(),\'%w\') day);

select *
from stu
where date_format(s_birth,\'2020-%m-%d\') between date_add(curdate(),interval 7-date_format(curdate(),\'%w\') day) and date_add(curdate(),interval 13-date_format(curdate(),\'%w\') day);

select *
from stu
where week(date_format(s_birth,\'2020-%m-%d\'))=if(week(curdate())=52,0,week(curdate())+1);
#########################################################################################################################
#--49、查询本月过生日的学生
select *
from stu
where month(s_birth)=month(curdate());
#########################################################################################################################
#--50、查询下月过生日的学生
select *
from stu
where month(s_birth)=if(month(curdate())=12,1,month(curdate())+1);