【一周入门MySQL—4】数据库进阶练习

数据库进阶练习

数据库进阶查询练习

素材：来自以下四个Table

数据准备（创建表，导入数据）

-- 数据准备，创建Table

use test;

-- 学生表

create table stu(s_id varchar(10) primary key,s_name varchar(10) not null,s_birth date,s_sex varchar(10));

-- 课程表

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

-- 教师表

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

-- 成绩表

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



数据导入：

insert into te values('01','张三'),

                                    ('02','李四'),

                     ('03','王五');

select * from te;



insert into co values('1','语文','02'),

                                    ('2','数学','01'),

                     ('3','英语','03');

select * from co;

                    

insert into stu values('1','赵雷','1990-01-01','男'),

                      ('2','钱电','1990-12-21','男'),

                      ('3','孙风','1990-05-20','男'),

                      ('4','李云','1990-08-06','男'),

                      ('5','周梅','1991-12-01','女'),

                      ('6','吴兰','1992-03-01','女'),

                      ('7','郑竹','1992-04-21','女'),

                      ('8','王菊','1990-01-20','女');

select * from stu;    



insert into sc values('1','1',80),

                     ('1','2',90),  

                     ('1','3',99),

                     ('2','1',70),

                     ('2','2',60),

                     ('2','3',80),

                     ('3','1',80),

                     ('3','2',80),

                     ('3','3',80),

                     ('4','1',50),

                     ('4','2',30),

                     ('4','3',20),

                     ('5','1',76),

                     ('5','2',87),

                     ('6','1',31),

                     ('6','3',34),

                     ('7','2',89),

                     ('7','3',98);

select * from sc;         

 【进阶练习】

-- 查询1#课程比2#课程成绩高的学生的信息及课程分数（选修的每一门分数）

select * from sc where c_id = '1';          

select * from sc where c_id = '2';



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

from (select * from sc where c_id = '1') t1

join (select * from sc where c_id = '2') 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;         

-- 查询平均成绩大于等于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;

-- 查询姓“李”的老师教授的课程数量

select count(c_id)

from te left join co on te.t_id = co.t_id

where t_name like '李%';

-- 查询名字中含有“风”字的学生信息

select *

from stu

where s_name like '%风%';

-- 查询学过张三老师课程的学生信息

# 首先找出满足条件的学生ID

select * from stu

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

-- 查询没有学过张三老师课程的学生信息

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

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

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

-- 查询学过课程1和课程2的学生信息

# 利用模糊查询匹配课程1和课程2

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 '1,2%';

-- 查询学过课程1但是没有学过课程2的学生信息

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 '%1%'

and group_concat(c_id order by c_id) not like '%2%';



select *

from stu

where s_id in (select s_id from sc where c_id = '1') and s_id not in (select s_id from sc where c_id = '2');



select stu.*

from stu

left join sc on stu.s_id = sc.s_id

where c_id in ('1','2')

group by stu.s_id

having group_concat(c_id) = '1';

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

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) = (select group_concat(c_id) from co);



select *

from stu

where s_id in (select s_id from sc group by s_id having count(s_id) = (select count(*) from co));

-- 查询没有学全全部课程的学生信息

select stu.*,count(c_id)

from stu

left join sc on stu.s_id = sc.s_id

group by stu.s_id

having count(c_id) < (select count(*) from co);

-- 查询至少有一门课程与学号为“1”的学生相同的学生信息

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='1')

  and stu.s_id <> '1';

-- 查询与学号为“1”的学生所学课程完全相同的其他学生的信息

select stu.*,group_concat(c_id order by c_id)

from stu

left join sc on stu.s_id = sc.s_id

where stu.s_id <> '1'

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

-- 查询所有学生的课程和分数的情况（一维表转为二维表）

select stu.s_id,

       sum(if(c_id='1',score,0)) '1#',

       sum(if(c_id='2',score,0)) '2#',

       sum(if(c_id='3',score,0)) '3#'

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='1' then score else 0 end) '1#',

       sum(case when c_id='2' then score else 0 end) '2#',

       sum(case when c_id='3' then score else 0 end) '3#'

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='1')*score),0) '1#',

       ifnull(sum((c_id='2')*score),0) '2#',

       ifnull(sum((c_id='3')*score),0) '3#'

from stu

left join sc on stu.s_id = sc.s_id

group by stu.s_id;

-- 按平均成绩从高到低显示所有学生的所有课程的考试成绩和平均成绩

select stu.s_id,

       sum(if(c_id='1',score,0)) '1#',

       sum(if(c_id='2',score,0)) '2#',

       sum(if(c_id='3',score,0)) '3#',

       ifnull(avg(score),0) 平均成绩

from stu

left join sc on stu.s_id = sc.s_id

group by stu.s_id

order by 平均成绩 desc;

-- 查询各科成绩最高分、最低分、平均分

-- 以如下形式显示课程ID、课程Name、最高分、最低分、平均分、及格率、中等率、优良率、优秀率

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

# 及格率 = 及格人数/考试总人数

select co.c_id,c_name,

       max(score) 最高分,

       min(score) 最低分,

       avg(score) 平均分,

       sum(score>=60)/count(sc.c_id) 及格率, # avg(score>=60) 同一个思路

       sum(score>=70 and score<80)/count(sc.c_id) 中等率, # avg(score>=70 and score<80) 同一个思路

       sum(score>=80 and score<90)/count(sc.c_id) 优良率, # avg(score>=80 and score<90) 同一个思路

       sum(score>=90)/count(sc.c_id) 优秀率 # avg(score>=90) 同一个思路

from co

left join sc on co.c_id = sc.c_id

group by co.c_id

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

select s_id 学号,sum(score) 总成绩,

       row_number() over(order by sum(score) desc) 排名

from sc

group by s_id;

-- 查询每个学生的平均成绩以及排名

select s_id 学号,avg(score) 平均成绩,

       row_number() over(order by avg(score) desc) 排名

from sc

group by s_id;

-- 按各科成绩进行排序，并显示排名

select * ,

rank() over(partition by c_id order by score desc) 排名

from sc;

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

select * from

(select * ,

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

from sc) t

where 排名<=3;

-- 查询每门功课成绩最好的前两名

select * from

(select * ,

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

from sc) t

where 排名<=2;

-- 查询每门课程成绩在第二名到第三名的学生信息及该课程成绩

select stu.*,c_id,score,排名

from stu

left join

(select * ,

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

from sc) t on stu.s_id = t.s_id

where 排名 between 2 and 3;

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

select *,count(s_id) 课程选修人数

from sc

group by c_id;

-- 查明同名同姓的学生名单，并统计同名人数

select *,count(s_name)-1 同名人数

from stu

group by s_name;

-- 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

select s_name,c_name,score

from stu

left join sc on stu.s_id = sc.s_id

left join co on sc.c_id = co.c_id

where score > 70;

-- 查询出现过学生考试成绩不及格的课程

select c_name,sc.c_id,score

from sc join co on sc.c_id = co.c_id

where score < 60;

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

select distinct t1.*

from sc t1 join sc t2 on t1.s_id = t2.s_id and t1.c_id <> t2.c_id and t1.score = t2.score;

-- 查询本周过生日的学生信息

select *,week(s_birth)

from stu

where week(s_birth) = week(curdate()) ;

-- 查询下周过生日的学生信息

select *,week(s_birth)

from stu

where week(s_birth) = if(week(curdate())=54,1,week(curdate())+1);

-- 查询本月过生日的学生信息

select *,month(s_birth)

from stu

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