数据分析之MySql篇{练习题}

 关于MySql基础学习的链接,如下:

• 数据分析之MySql篇{数据库基础概念}
• 数据分析之MySql篇{数据定义DDL与数据操作DML}
• 数据分析之MySql篇{数据查询语言DQL}
• 数据分析之MySql篇{常用函数}

-- 创建数据库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

  上述操作，我们在所创建的数据库中，分别创建了stu学生表，co课程表，sc成绩表，教师表te。

-- ----------------------------------------------------------------------------------
-- 1、查询"01"课程比"02"课程成绩高的学生信息及课程分数
 # 涉及到了两张表，一个stu，一个sc
 # 在sc表中分别筛选出01和02的课程成绩，分别作为t1和t2表，对这两个表进行表连接，得到的结果与stu表进行连接

select stu.*,sc.c_id,sc.score 
from 
(select * from sc where c_id = '01') as t1 
inner join (select *from sc where c_id = '02') as t2 
on t1.s_id=t2.s_id
inner join stu on t1.s_id=stu.s_id
inner 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') as t1 
inner join (select *from sc where c_id = '02') as t2 
on t1.s_id=t2.s_id
inner join stu on t1.s_id=stu.s_id
inner join sc on stu.s_id = sc.s_id
where t1.score < t2.score;



-- ----------------------------------------------------------------------------------
-- 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、查询"李"姓老师的教授的课程数量
select count(c_id) 
from te left join co on te.t_id = co.t_id
where t_name like '李%';



-- ----------------------------------------------------------------------------------
-- 6、练习：查询名字中含有"风"字的学生信息
select *
from stu
where s_name like '%风%';


-- ----------------------------------------------------------------------------------
-- 7、查询学过"张三"老师授课的同学的信息
# 学生信息在stu ，"张三"老师在te表，两个表需要连接，两个表没有相同的字段，需要中间表进行连接
 
 select *
 from stu
where s_id in( select s_id
 from (select t_id,s_id  from sc left join co on sc.c_id = co.c_id) as t1  left join te on t1.t_id = te.t_id
 where t_name = '张三');


-- ----------------------------------------------------------------------------------
-- 练习：查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.* ,score 
from stu
left join sc on stu.s_id = sc.s_id
left join co on sc.c_id = co.c_id
left join te on co.t_id = te.t_id
where t_name = '张三'
order by score desc
limit 1;
  
  # 或者可以这样写
  select stu.*,score
from stu
left join sc on stu.s_id = sc.s_id
left join co on sc.c_id = co.c_id
left join te on co.t_id = te.t_id
where t_name = '张三' and score = (select max(score)
								   from stu
								   left join sc on stu.s_id = sc.s_id
                                   left join co on sc.c_id = co.c_id
								   left join te on co.t_id = te.t_id
								   where t_name = '张三' );

-- ----------------------------------------------------------------------------------
-- 8、练习：查询没学过"张三"老师授课的同学的信息
select *
from stu
where s_id not in( select s_id
from (select t_id,s_id  from sc left join co on sc.c_id = co.c_id) as t1  left join te on t1.t_id = te.t_id
 where t_name = '张三');


-- ----------------------------------------------------------------------------------
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select stu.*,group_concat(c_id order by c_id)
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '01,02%';



-- ----------------------------------------------------------------------------------
-- 练习：查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select stu.*
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '%01%' and group_concat(c_id order by c_id) not like '%02%';

# 或者
select * 
from stu
where s_id in (select s_id from sc where c_id = 01) and s_id not in (select s_id from sc where c_id = 02); 

#或者
select stu.*
from stu left 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';

# 或者
select stu.*
from stu left join sc on stu.s_id = sc.s_id
where c_id = '01' and stu.s_id not in (select s_id from sc where c_id = '02');

-- ----------------------------------------------------------------------------------
-- 10、查询选修了全部课程的学生信息

select stu.*,group_concat(c_id order by c_id)
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having group_concat(c_id order by c_id) like '01,02,03';

#或者
select *
from stu
where s_id in (select s_id  from sc group by s_id having count(c_id)= (select count(c_id) from co ));

# 或者
select stu.*,count(c_id) as 选课门数
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having count(c_id) = (select count(c_id) from co);

-- ----------------------------------------------------------------------------------
-- 11、练习：查询没有学全所有课程的同学的信息

select stu.*,count(c_id) as 选课门数
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
having count(c_id) < (select count(c_id) from co);

-- ----------------------------------------------------------------------------------
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 

select distinct  stu.*
from stu left join sc 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 s_id,group_concat(c_id) 
from sc
group by s_id
having group_concat(c_id) = (select group_concat(c_id)
                             from stu left join sc on stu.s_id = sc.s_id
							 where sc.s_id ='01');


-- ----------------------------------------------------------------------------------
-- 14、查询所有学生的课程及分数情况（一维转二维）
select stu.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 stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;

# 或者

select stu.s_id,
     sum(case when c_id= '01' then score else 0 end )as '01',
    sum( case when c_id= '02' then score else 0 end) as '02',
     sum(case when c_id= '03' then score else 0 end) as '03'
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;

# 或者

select stu.s_id,
	 ifnull(sum((c_id='01') * score),0) as '01', # (c_id='01')  是一个判断表达式，sql语句中，结果为真，返回1，结果为假，返回0
     ifnull(sum((c_id='02') * score),0) as '02',
     ifnull(sum((c_id='03') * score),0) as '03'
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id;


-- ----------------------------------------------------------------------------------
-- 15、练习：按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select stu.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',
        ifnull(avg(score),0) as 平均成绩
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
order by avg(score) desc;

#或者
select stu.s_id,
	 ifnull(sum((c_id='01') * score),0) as '01', # (c_id='01')  是一个判断表达式，sql语句中，结果为真，返回1，结果为假，返回0
     ifnull(sum((c_id='02') * score),0) as '02',
     ifnull(sum((c_id='03') * score),0) as '03',
     ifnull(avg(score),0) as 平均成绩
from stu left join sc on stu.s_id = sc.s_id
group by stu.s_id
order by avg(score) desc;

-- ----------------------------------------------------------------------------------
-- 16、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
#及格为：>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select co.c_id,c_name,max(score),min(score),avg(score),
           avg(score>=60) as 及格率, # 我们知道(score>=60)是返回1或者0，avg()就是先求和然后除以个数，求和就是多个1和多个0相加。
           avg(score >= 70 and score < 80) as 中等率 , 
           avg(score >=80 and score <90) as 优良率 ,
           avg(score>=90) as 优秀率
from co left join sc on co.c_id=sc.c_id
group by co.c_id;

# 其次 及格率，还可以这么写，其他的类似。
sum(score>=60)/count(score) 

-- ----------------------------------------------------------------------------------
-- 17、练习：统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select co.c_id,c_name,
           sum((score>=85)) as  '[100-85]',
           sum((score >= 70 and score < 85)  ) as  '[85-70]',
           sum((score >= 60 and score < 70) ) as  '[60-70]',
           sum((score <60 ) ) as  '[0-60]',
           concat(avg(score>=85) * 100,'%') as '[100-85]百分比',
           concat(avg(score >= 70 and score < 85) * 100,'%') as '[85-70]百分比',
           concat(avg(score >= 60 and score < 70) * 100,'%') as '[60-70]百分比',
           concat(avg(score <60 ) * 100,'%') as '[0-60]百分比'
from co left join sc on co.c_id=sc.c_id
group by co.c_id;


-- ----------------------------------------------------------------------------------
-- 18、查询学生的总成绩并进行排名]

select s_id,sum(score) as 总成绩, row_number() over(order by sum(score) desc) as 排名
from sc
group by s_id;

-- ----------------------------------------------------------------------------------
-- 19、练习：查询每个学生平均成绩及其名次

select s_id,avg(score) as 平均成绩, row_number() over(order by avg(score) desc) as 排名
from sc
group by s_id;

-- ----------------------------------------------------------------------------------
-- 20、按各科成绩进行排序，并显示排名
select * ,rank() over(partition by c_id order by score desc ) as 排名  
from sc;



-- ----------------------------------------------------------------------------------
-- 21、查询各科成绩前三名的记录

select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名  from sc) as t
where 排名<=  3;


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

select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名  from sc) as t
where 排名<= 2;


-- ----------------------------------------------------------------------------------
-- 23、练习：查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select*
from (select * ,rank() over(partition by c_id order by score desc ) as 排名  from sc) as t
where 排名>= 2 and 排名 <=3;


-- ----------------------------------------------------------------------------------
-- 24、查询每门课程被选修的学生数

select c_id,count(c_id)
from sc
group by c_id;

-- ----------------------------------------------------------------------------------
-- 25、练习：统计每门课程的学生选修人数（超过5人的课程才统计）

#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(c_id)
from sc
group by c_id
having count(c_id) >= 5;

-- ----------------------------------------------------------------------------------
-- 26、查询同名同姓学生名单，并统计同名人数
select s_name,count(s_name)-1
from stu
group by s_name;


-- ----------------------------------------------------------------------------------
-- 27、查询课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,score
from stu inner join sc  on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where score >70 ;



-- ----------------------------------------------------------------------------------
--28、查询任一门课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,score 
from stu inner join sc  on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where stu.s_id in (select distinct s_id
				   from sc
                   where score >70);


-- ----------------------------------------------------------------------------------
-- 29、练习：查询出现过学生考试不及格的课程

select c_name,count(s_name)
from stu inner join sc  on stu.s_id = sc.s_id inner join co on sc.c_id = co.c_id
where score < 60
group by c_name;



-- ----------------------------------------------------------------------------------
-- 30、查询课程不同、成绩相同的学生的学生编号、课程编号、学生成绩

select distinct t1.*
from sc as t1 join sc as t2 on t1.c_id != t2.c_id and t1.score =t2.score;





-- ----------------------------------------------------------------------------------
-- 31、查询本周过生日的学生
select *
from stu
where week(s_birth) = week(curdate());


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

select *
from stu
where week(s_birth) = if(week(curdate()) = 54, 1, week(curdate())+1);


-- ----------------------------------------------------------------------------------
-- 33、查询本月过生日的学生
select *
from stu
where month(s_birth)= month(curdate());


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

select *
from stu
where month(s_birth) = if(month(curdate()) = 12, 1, month(curdate())+1);