扫码加入数据分析学习群

-- 创建数据库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 stu.*,sc.c_id,sc.score

from sc as t1

join sc as t2 on t1.s_id=t2.s_id and t1.c_id='01' and t2.c_id='02'

join stu on t1.s_id=stu.s_id

join sc on stu.s_id=sc.s_id

where t1.score>t2.score;

select t3.*,sc.c_id,sc.score

from stu as t3

left join

(select * from sc where c_id ='01') t1 on t3.s_id = t1.s_id

left join

(select * from sc where c_id ='02') t2 on t1.s_id = t2.s_id

left join sc on t3.s_id=sc.s_id

where t1.score >t2.score

;

-- ----------------------------------------------------------------------------------

-- 2、练习：查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select stu.*,sc.c_id,sc.score

from sc as t1

join sc as t2 on t1.s_id=t2.s_id and t1.c_id='01' and t2.c_id='02'

join stu on t1.s_id=stu.s_id

join sc on stu.s_id=sc.s_id

where t1.score<t2.score;

select t3.*,sc.c_id,sc.score

from stu as t3

left join

(select * from sc where c_id ='01') t1 on t3.s_id = t1.s_id

left join

(select * from sc where c_id ='02') t2 on t1.s_id = t2.s_id

left join sc on t3.s_id=sc.s_id

where t1.score <t2.score;

-- ----------------------------------------------------------------------------------

-- 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='张三';

select b.*

from

(

select s_id from sc where c_id in(

select c_id from co where t_id in(

select t_id from te where t_name = '张三'

))) a

left join stu b on a.s_id = b.s_id

;

-- ----------------------------------------------------------------------------------

-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select max(sc.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='张三');

-- ----------------------------------------------------------------------------------

-- 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.*,count(*)

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(*)=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';

-- ----------------------------------------------------------------------------------

-- 12、查询至少有一门课与学号为"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 sc.s_id<>'01';

-- ----------------------------------------------------------------------------------

-- 13、练习：查询和"01"号的同学学习的课程完全相同的其他同学的信息

select group_concat(c_id order by c_id) from sc where s_id='01';

select stu.*

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 sc.s_id<>'01';

-- ----------------------------------------------------------------------------------

-- 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(case when c_id='01' then score else 0 end) as '01',-- 或者max也可以

sum(case when c_id='02' then score else 0 end) as '02',-- 或者max也可以

sum(case when c_id='03' then score else 0 end) as '03'-- 或者max也可以

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;

-- ----------------------------------------------------------------------------------

-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select sc.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,

avg(score>=60) 及格率,-- avg(score>=60) = sum(score>=60)/count(score>=60)

avg(score>=70 and score<80) 中等率,

avg(score>=80 and score<90) 优良率,

avg(score>=90) 优秀率

from sc

left join co on sc.c_id=co.c_id

group by sc.c_id;

-- ----------------------------------------------------------------------------------

-- 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;

-- ----------------------------------------------------------------------------------

-- 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 *,dense_rank() over(partition by c_id order by score desc) 排名

from sc

where 排名<=3;-- 报错：SQL执行顺序

select *

from

(select *,dense_rank() over(partition by c_id order by score desc) 排名

from sc) as t

where 排名<=3;

-- ----------------------------------------------------------------------------------

-- 42、练习：查询每门功成绩最好的前两名

select *

from

(select *,dense_rank() over(partition by c_id order by score desc) 排名

from sc) as t

where 排名<=2;

-- ----------------------------------------------------------------------------------

-- 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 *,date_format(s_birth,'%m-%d')

from stu

where date_format(s_birth,'%m-%d') between date_format(date_sub(curdate(),interval date_format(curdate(),'%w') day),'%m-%d') and date_format(date_add(curdate(),interval 6-date_format(curdate(),'%w') day),'%m-%d');

-- ----------------------------------------------------------------------------------

-- 48、练习：查询下周过生日的学生

#下周的第一天

select date_add(curdate(),interval 7-date_format(curdate(),'%w') day);

#下周的最后一天

select date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 6 day);

select *

from stu

where date_format(s_birth,'%m-%d') between date_format(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),'%m-%d') and date_format(date_add(date_add(curdate(),interval 7-date_format(curdate(),'%w') day),interval 6 day),'%m-%d');

-- ----------------------------------------------------------------------------------

-- 49、查询本月过生日的学生

#今天是几月

select month(curdate());

select *

from stu

where month(s_birth)=month(curdate());

-- ----------------------------------------------------------------------------------

-- 50、练习：查询下月过生日的学生

#下个月是几月

select month(curdate())+1;

select *

from stu

where month(s_birth)=if(month(curdate())=12,1,month(curdate())+1);