MYSQL练习一

表结构

在native中的表数据如下

-- 1.查询01课程比02课程成绩高的学生学号（重点）
select a.s_id "s_no" ,a.s_score "01",b.s_score "02",c.s_name from
(select s_id,c_id,s_score from score where c_id='01') as a
inner join
(select s_id,c_id,s_score from score where c_id='02') as b
on a.s_id = b.s_id
inner join student as c on c.s_id = a.s_id
where a.s_score>b.s_score;

-- 2.查询平均成绩大于60的学生学号和平均成绩查
select s_id,avg(s_score) 
from score
group by s_id having avg(s_score)>60;

-- 3.查询所有学生的学号、姓名、选课数、总成绩
select a.s_id,a.s_name,count(b.c_id),sum(case when b.s_score is NULL then 0 else b.s_score END) "s_score"-- 如果成绩为NULL，输出为0
from student as a
left join score as b on a.s_id = b.s_id
group by s_id,a.s_name;-- 查询语句最好是统计字段或者group by里的字段

-- 4.查询没学过张三老师课的学生的学号、姓名（重点）
-- 嵌套子查询
select s_id,s_name from student where s_id not in(
    select s_id from score where c_id =(
        select c_id from course where t_id = (
        	select t_id from teacher where t_name='张三'
        )
    )
);
-- 链表查询
select s_id,s_name from student where s_id not in(
    select s_id from score as s
    inner join course as c on s.c_id=c.c_id
    inner join teacher as t on c.t_id=t.t_id
    where t.t_name='张三'
);

-- 5.查询学过张三老师所有课的学生的学号、姓名（重点）
select st.s_id,st.s_name,s.c_id,c.c_name,c.t_id,t.t_name
from student as st
inner join score as s on s.s_id=st.s_id
inner join course as c on s.c_id=c.c_id
inner join teacher as t on t.t_id=c.t_id
where t.t_name='张三'
order by st.s_id;

-- 6.查询学过编号为“01”的课程且也学过编号为“02”课程的学生学号姓名（重点）
select s_id,s_name from student where s_id in(
    select a.s_id from
        (select s_id from score where c_id='01') as a
	inner join
        (select s_id from score where c_id='02') as b
	on a.s_id=b.s_id);
-- 内联查询
select a.s_id,a.s_name from student as a
inner join
(select * from student where s_id in (select s_id from score where c_id='01')) as b
inner join
(select * from student where s_id in (select s_id from score where c_id='02')) as c
on a.s_id=b.s_id and b.s_id=c.s_id;

-- 7.查询课程编号为“02”的总成绩（不重点）sum,avg,count
select sum(s_score) from score where c_id='02';
select avg(s_score) from score where c_id='02';
select count(s_score) from score where c_id='02';

-- 8.查询所有课程成绩小于60分的学生学号姓名
select s_id,s_name from student where s_id in
(select a.s_id from
(select s_id,count(c_id) as cnt from score where s_score<60 group by s_id) as a
inner join
(select s_id,count(c_id) as cnt from score group by s_id) as b
on a.s_id=b.s_id
where a.cnt=b.cnt);
-- max聚集函数解决
select s_id,s_name from student where s_id in
(select s_id from score group by s_id having max(s_score)<60);

-- 9.查询没有学全所有课程的学生学号姓名（重点）
select st.s_id,st.s_name from student as st
left join score as sc
on st.s_id=sc.s_id
group by s_id having count(s_score)<(select count(distinct c_id) from score);

-- 10.查询至少有一门课与学号为“01”的学生所学课程相同的学生学号姓名（重点）
select s_id,s_name from student where s_id in
(select s_id from score where c_id in
(select c_id from score where s_id="01")
group by s_id) and s_id!='01';

-- 链表查询
select a.s_id,a.s_name from student as a
inner join
(select distinct s_id from score where c_id in
(select c_id from score where s_id="01")
and s_id!='01') as b
on a.s_id=b.s_id;

-- 11.查询“01”同学所学课程完全相同的其他同学的学号（重点）
select * from student where s_id in
(
    select s_id from score where s_id!='01' 
    group by s_id having count(c_id)=(select count(distinct c_id) from score)
) and s_id not in
(
    select s_id from score where c_id not in(select c_id from score where s_id='01')
);
-- 第一步先找出跟01同学所学课程有不同的学生排除掉，然后查找所选课程数量跟01同学一样的学生就是答案了

-- 12.查询两门及以上不及格课程的同学的学号姓名和平均成绩（重点）
select a.s_id,a.s_name,avg(s_score) from student as a
inner join score as b on a.s_id=b.s_id
where a.s_id in 
(select s_id from score where s_score<60 group by s_id having count(c_id)>=2)
group by a.s_id;
-- 注意，最后的group by起到的作用是按s_id分组计算平均分，不然的话算的是多位同学的平均分

-- 13.检索“01”课程分数小于60，按分数降序排列的学生信息
select a.*,b.s_score from student as a inner join score as b
on a.s_id=b.s_id 
where b.c_id='01' and b.s_score<60
order by b.s_score desc;

-- 14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点）
select a.s_id,a.s_score,b.avg_s_score from score as a
inner join 
(select s_id,avg(s_score) as avg_s_score from score group by s_id) as b
on a.s_id=b.s_id 
order by b.avg_s_score desc;
-- 修改版，显示语数英和平均成绩查!!!!!!!!!
select s_id,
max(case when c_id='01' then s_score else null end) "语文",
max(case when c_id='02' then s_score else null end) "数学",
max(case when c_id='03' then s_score else null end) "英语",
avg(s_score) "平均成绩"
from score group by s_id order by avg(s_score) desc;

-- 15.查询各科成绩最高分、最低分和平均分、及格率>=60、中等率70-80、优良率80-90、优秀率90-100(重点)
select s.c_id,c.c_name,
avg(s.s_score) "平均分",
min(s.s_score) "最低分",
max(s.s_score) "最高分",
sum(case when s.s_score>=60 then 1 else 0 end)/count(s.s_id) "及格率",
sum(case when s.s_score>=70 and s.s_score<80 then 1 else 0 end)/count(s.s_id) "中等率",
sum(case when s.s_score>=80 and s.s_score<90 then 1 else 0 end)/count(s.s_id) "优良率",
sum(case when s.s_score>=90 then 1 else 0 end)/count(distinct s.s_id) "优秀率"
from score as s
inner join course as c
on s.c_id=c.c_id
group by c.c_id;
-- count(s.s_id)是在score表和course表联合后的汇总表查找的，此时的每个c_id对应多个s_id，根据group by c_id后，count(s_id)的都是学了此门课的学生。

-- 16.按各科成绩进行排序，并显示排名（row_number(1234),rank(1224),dense_rank(1223)）
select *,rank() over(partition by c_id order by s_score desc) as 'rank' from score;
-- 窗口函数是mysql8.0后支持的

-- 17.查询学生的总成绩并进行排名
select st.s_id,st.s_name,sum(s_score) from student as st
inner join score as sc
on st.s_id=sc.s_id
group by st.s_id
order by sum(s_score) desc;

-- 18.查询不同老师所教不同课程平均分从高到低显示
-- 1.以课程为主体，求平均分
select c.c_id,c.c_name,avg(sc.s_score) as avg_score
from score as sc
inner join course as c on sc.c_id=c.c_id
group by c.c_id
order by avg_score desc;
-- 2.以老师为主体，求平均分
select t.t_id,t.t_name,avg(sc.s_score) as avg_score
from score as sc
inner join course as c on sc.c_id=c.c_id
inner join teacher as t on c.t_id=t.t_id
group by t.t_id,t.t_name
order by avg_score;

-- 19.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select * from
(select st.*,c_id,,s_score,row_number() over(partition by c_id order by s_score desc) m
from score as sc inner join student as st on sc.s_id=st.s_id) as a
where m in (2,3);
-- 窗口函数是mysql8.0后支持的

-- 20.使用分段[100-85]、[85-70]、[70-60]、[<60]来统计各科成绩，分别统计各分数段人数，课程id和课程名称（重点）
select c.c_id,c.c_name,
sum(case when s.s_score>=85 and s.s_score<=100 then 1 else 0 end) "100-85",
sum(case when s.s_score>=70 and s.s_score<85 then 1 else 0 end) "85-70",
sum(case when s.s_score>=60 and s.s_score<70 then 1 else 0 end) "70-60",
sum(case when s.s_score<60 then 1 else 0 end) "<60"
from course as c
inner join score as s 
on c.c_id=s.c_id
group by s.c_id;