mysql学生成绩查询系统

use bank;
create table students(
    id int,
    name varchar(10),
    class int,
    Chinese int,
    math int);
insert into students(id,name,class,Chinese,math)
values 
(1,  '张航',  190811,  89,  97),
(2,	'谢欣欣',190811,	78,	67),
(3,	'王涛涛',190811,	99,	70),
(4,	'艾杰',	190811,	67,	90),
(5,	'张谷雨',190811,	78,	80),
(6,	'贝贝',	190811,	87,	70),
(7,	'徐鹏程',190811,	88,	77),
(8,	'范伟',	190811,	67,	89),
(9,	'张飞',	190811,	98,	88),
(10,	'周天',	190811,	78,	70),
(11,	'丁夏雨',190812,	98,	59),
(12,	'齐秦',	190812,	89,	88),
(13,	'叶子刚',190812,	88,	79),
(14,	'朱一鸣',190812,	78,	99),
(15,	'刘毅',	190812,	80,	96),
(16,	'杨蒲英',190812,	90,	69),
(17,	'吴彦琦',190812,	84,	99),
(18,	'陈丽雅',190812,	77,	87),
(19,	'郑华鑫',190812,	69,	84),
(20,	'周天明',190812,	80,	89),
(21,	'李欣',	190813,	60,	82),
(22,	'张子钰',190813,	88,	99),
(23,	'林明',	190813,	70,	78),
(24,	'刘一奇',190813,	99,	88),
(25,	'彭莉',	190813,	82,	78),
(26,	'顾华成',190813,	78,	60),
(27,	'张高飞',190813,	86,	94),
(28,	'吕行',	190813,	85,	93),
(29,	'田野',	190813,	92,	88),
(30,	'张锴',	190813,	84,	70);

select * from students;
#question1:
select class,max(Chinese),min(Chinese),round(avg(Chinese),1),max(math),min(math),round(avg(math),1)
    from students group by class;
#question2:
select id,name,class from students where math = (select max(math) 
    from students where class = 190812) and class = 190812 order by id;
#question3:
select class,
    sum(case when Chinese>=90 then 1 else 0 end) as 优秀数量,
    concat(round(sum(case when Chinese>=90 then 1 else 0 end)/(select count(*) from students)*100,2),'%') as 优秀比例,
    sum(case when Chinese>=80 then 1 else 0 end) as 优良数量,
    concat(round(sum(case when Chinese>=80 then 1 else 0 end)/(select count(*) from students)*100,2),'%') as 优良比例
    from students group by class;
#question4:
select class,id,name,Chinese,math 
    from students order by (Chinese+math) desc limit 9;

#code by teacher
select @rownum := @rownum +1, students.*
    from (select @rownum :=0) students 
    where @rownum <(select round(0.3*count(*)) from students);

# the former 30%
select t.* from (select @rownum:=0) r join students t
where (@rownum:=@rownum+1)<=(select round(count(*)*0.3) from students);

#the former 30% order by grade
select *from (
    select 
        a.*,
        @row_num:=@row_num+1 as row_num 
    from 
        students a, (select @row_num:=0) b  
    order by 
        (Chinese+math) desc
) base
where 
    base.row_num <= (@row_num*0.3);