2020-06-05 阅读量: 760
-- 创建数据库school
create database school;
-- 选择进入school数据库
use school;
-- ------------建表导数-------------
-- 创建stu
create table stu(
s_id varchar(10) primary key,
s_name varchar(10) not null,
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; -- 检查总行数8
-- 创建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; -- 检查总行数3
-- 创建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; -- 检查总行数3
-- 创建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; -- 检查总行数18
-- ----------------------------------------------------------------------------------
-- 1、查询"01"课程比"02"课程成绩高的学生信息及课程分数(选修的每一门课程的分数)
select s_id,score from sc where c_id=01; -- 选修01课程的学生成绩
select s_id,score from sc where c_id=02; -- 选修02课程的学生成绩
select stu.*,sc.c_id,sc.score
from stu
join (select s_id,score from sc where c_id=01) sc1 on sc1.s_id=stu.s_id
join (select s_id,score from sc where c_id=02) sc2 on sc1.s_id=sc2.s_id
join sc on sc.s_id=stu.s_id
where sc1.score>sc2.score;
-- ----------------------------------------------------------------------------------
-- 2、练习：查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select stu.*,sc.c_id,sc.score
from stu
join (select s_id,score from sc where c_id=01) sc1 on sc1.s_id=stu.s_id
join (select s_id,score from sc where c_id=02) sc2 on sc1.s_id=sc2.s_id
join sc on sc.s_id=stu.s_id
where sc1.score<sc2.score;
-- ----------------------------------------------------------------------------------
-- 7、查询学过"张三"老师授课的同学的信息
select stu.* from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on stu.s_id=sc.s_id
where t_name='张三';
-- ----------------------------------------------------------------------------------
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.*, max(score) from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on stu.s_id=sc.s_id
where t_name='张三'; -- 最高分只有一个适应
select stu.*,score from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on stu.s_id=sc.s_id
where t_name='张三' and score=(select max(score) from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on stu.s_id=sc.s_id
where t_name='张三'); -- 最高分有几个人是一样的值，数据更新也可应用，利用性高
-- ----------------------------------------------------------------------------------
-- 8、练习：查询没学过"张三"老师授课的同学的信息
select distinct stu.* from stu
left join sc on stu.s_id=sc.s_id
left join co on co.c_id=sc.c_id
left join te on te.t_id=co.t_id
where s_name not in (select s_name from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
left join stu on stu.s_id=sc.s_id
where t_name='张三'); -- 查询比较累赘（连接4张表）
select * from stu
where s_id not in (select s_id from te
left join co on te.t_id=co.t_id
left join sc on co.c_id=sc.c_id
where t_name='张三') ; -- 简单（连接3张表）
-- ----------------------------------------------------------------------------------
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select stu.* from stu
right join sc on stu.s_id=sc.s_id
where c_id in (01,02)
group by stu.s_id
having count(c_id)=2;
-- ----------------------------------------------------------------------------------
-- 10、练习：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select stu.*
from stu
right join sc on stu.s_id=sc.s_id
where c_id in (01,02)
group by stu.s_id
having group_concat(c_id)='01';
-- ----------------------------------------------------------------------------------
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select c_id from sc where s_id='01';
select distinct stu.*
from sc
left join stu on stu.s_id=sc.s_id
where c_id in (select c_id from sc where s_id='01') and stu.s_id<>'01';
-- ----------------------------------------------------------------------------------
-- 13、练习：查询和"01"号的同学学习的课程完全相同的其他同学的信息
select stu.*
from sc
left join stu on stu.s_id=sc.s_id
group by stu.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';
-- ----------------------------------------------------------------------------------
-- 35、查询所有学生的课程及分数情况（一维转二维）
select stu.s_id,
sum((c_id='01')*score) 语文,
sum((c_id='02')*score) 数学,
sum((c_id='03')*score) 英语
from sc right join stu on sc.s_id=stu.s_id
group by stu.s_id; -- 常用方法
select stu.s_id,
sum(if(c_id='01',score,0)) 语文,
sum(if(c_id='02',score,0)) 数学,
sum(if(c_id='03',score,0)) 英语
from sc
right join stu on sc.s_id=stu.s_id
group by stu.s_id;
-- ----------------------------------------------------------------------------------
-- 17、练习：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s_id,
sum((c_id='01')*score) 语文,
sum((c_id='02')*score) 数学,
sum((c_id='03')*score) 英语,
avg(score) 平均成绩
from sc
group by s_id
order by avg(score) desc ; -- avg=sum/count(非空单元计算)
select s_id,
sum((c_id='01')*score) '01',
sum((c_id='02')*score) '02',
sum((c_id='03')*score) '03',
sum(score)/(select count(c_id) from co) 平均成绩
from sc
group by s_id
order by 平均成绩 desc ;
-- ----------------------------------------------------------------------------------
-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
select co.c_id,c_name,
max(score) 最高分,
min(score) 最低分,
avg(score) 平均分,
avg(score>=60) 及格率,
avg(score>=70 and score<80) 中等率,
avg(score>=80 and score<90) 优良率,
avg(score>=90) 优秀率
from co right join sc on sc.c_id=co.c_id
group by co.c_id;
-- ----------------------------------------------------------------------------------
-- 23、练习：统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select sc.c_id,c_name,
sum(if(score<60,1,0)) 不及格人数,
concat((sum(if(score<60,1,0))/count(score))*100,'%') 不及格率,
sum(if(score>=60 and score<70,1,0)) 中等人数,
concat((sum(if(score>=60 and score<70,1,0))/count(score))*100,'%') 中等率,
sum(if(score>=70 and score<85,1,0)) 良好人数,
concat((sum(if(score>=70 and score<85,1,0))/count(score))*100,'%') 良好率,
sum(if(score>=85 and score<=100,1,0)) 优秀人数,
concat((sum(if(score>=85 and score<=100,1,0))/count(score))*100,'%') 优秀率
from co right join sc on sc.c_id=co.c_id
group by c_id;
select sc.c_id,c_name,
avg(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) as '[85-100]所占百分比'
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;
-- ----------------------------------------------------------------------------------
-- 24、练习：查询每个学生平均成绩及其名次
select s_id,avg(score) 平均成绩,dense_rank() over(order by avg(score) desc) 排名
from sc
group by s_id;
-- ----------------------------------------------------------------------------------
-- 20、按各科成绩进行排序，并显示排名
select *,dense_rank() over(partition by c_id order by score desc) 排名
from sc;
-- ----------------------------------------------------------------------------------
-- 25、查询各科成绩前三名的记录
select *
from (select *,dense_rank() over(partition by c_id order by score desc) 排名 from sc) t
where 排名<=3;
-- ----------------------------------------------------------------------------------
-- 42、练习：查询每门功课成绩最好的前两名
select *
from (select *,dense_rank() over(partition by c_id order by score desc) 排名 from sc) t
where 排名<=2;
-- ----------------------------------------------------------------------------------
-- 22、练习：查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select *
from (select c_id,stu.*,score,dense_rank() over(partition by c_id order by score desc) 排名
from sc
left join stu on stu.s_id=sc.s_id) t
where 排名 between 2 and 3;
-- ----------------------------------------------------------------------------------
-- 41、查询课程不同、成绩相同的学生的学生编号、课程编号、学生成绩
select distinct sc1.*
from sc sc1
join sc sc2 on sc1.s_id=sc2.s_id and sc1.c_id<>sc2.c_id and sc1.score=sc2.score;
-- ----------------------------------------------------------------------------------
-- 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);
-- ----------------------------------------------------------------------------------
-- 48、练习：查询下周过生日的学生
-- 方法1
-- 下周的第一天
select date_add(curdate() ,interval 7-date_format(curdate(),'%w') day);
-- 下周的最后一天
select date_add(curdate() ,interval 7-date_format(curdate(),'%w')+6 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 7-date_format(curdate(),'%w')+6 day);
-- 方法2
-- 下周是一年中的第几周
select week(curdate())+1;
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)=if(month(curdate())=12,1,month(curdate()));
-- ----------------------------------------------------------------------------------
-- 50、练习：查询下月过生日的学生
select *
from stu
where month(s_birth)=month(curdate()+1);
