数据库存储

面试题：根据学生表要求：

1，当传入的参数（大于0）小于等于表里面数据的条数时，则根据分组显示班级的总成绩

2，当传入的参数大于表里面数据的条数时，则统计表里面的数据有多少条

3，当传入的其他值时，则查询表里面所有的数据

select*from student;

select count(1) from student;

select sum(chinese+math+english) from student group by id;

delimiter//

drop procedure if exists pro6;

create procedure pro6(in n int)

BEGIN

if n>0 and n<(select count(1) from student) then select class,sum(chinese+math+english) from student group by class;

else if n>(select count(1) from student) then select count(1) from student;

else select*from student;

end if;

end if;

END

call pro6(7);

 

传入数据大于表中已存在的数据的实际记录的行数，则往最高的分数上叠加？

方法一：

delimiter//

drop procedure if exists pro6;

create procedure pro6(in n int)

BEGIN

declare i int default (select count(1) from student);

declare k int default (select max(math) from student);

declare j int default (n-i);

while j>0 do

set k=k+1;

set i=i+1;

insert into student(id,math)values(i,k);

set j=j-1;

end while;

select id,math from student;

END

//

call pro6(11)

 

方法二：

 

delimiter //

drop procedure if exists pro7;

create procedure pro7(in n int)

begin

declare i int default (select count(1) from student);

declare j int default (select max(math) from student);

while n>i do

set i=i+1;

set j=j+1;

insert into student(id,math) values (i,j);

end while;

end

//

call pro7(11)