5.13mysql

drop procedure if exists if_demo; -- 删除存储过程 delimiter $$ create procedure if_demo() begin -- { declare avg decimal(5,2); -- 计算平均分 SELECT @avg:=avg(result) FROM `score`; -- 分支语法 IF @avg>=70 THEN select * from score order by result desc limit 3; ELSE select * from score order by result asc limit 3; END IF; end $$ -- } delimiter ; call if_demo(); -- 调用存储过程 drop procedure if exists while_demo; delimiter $$ create procedure while_demo() begin declare pelple_count int; select @pelple_count:=count(*) from score where result < 90; WHILE @pelple_count>0 DO update score set result = result + 2 where result <= 98; -- 进入循环后重新赋值没及格数量 select @pelple_count:=count(*) from score where result < 90; END WHILE; select '所有人员均已达标'; end $$ delimiter ; call while_demo(); SELECT case when result > 90 then 'A' when result > 80 then 'B' when result > 70 then 'C' when result > 60 then 'D' else 'E' end FROM `score`; select count(case when result between 90 and 100 then 1 end) 'A的数量', count(case when result between 80 and 89 then 1 end) 'B的数量', count(case when result between 70 and 79 then 1 end) 'C的数量', count(case when result between 60 and 69 then 1 end) 'D的数量', count(case when result between 0 and 59 then 1 end) 'E的数量' from score; select case when gender = '0' then '女性的性别是0' else '男性的性别是1' end from student;