2020-07-09 阅读量: 737
-- 创建数据库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);
在第一节基础上 更新了函数和执行顺序：实操了字符数函数、时间函数、字段截取函数、interval函数等，除最后顺序实操因未导入数据库，导致无法执行
