MySQL中的判断(if)和循环(while,repeat,loop)

创建用到的表

create table stuscore
(
    stuID int auto_increment,
    stuName varchar(20) not null,
    gender enum('男','女') default '男' not null,
    -- gender enum('1','0') default '1' not null, -- 1为男性
    score float not null,
    primary key(stuID)
)engine=innodb charset=utf8;

if语句，通过help if查看帮助
if语句的使用

drop procedure if exists sp1;
delimiter //
create procedure sp1(sco int)
begin
    -- declare sco int;
    -- set sco = 99;
    if sco between 90 and 100 then
    select 'perfect';
    elseif sco>=80 && sco<90 then
    select 'nice';
    elseif sco>=60 && sco<80 then
    select 'normal';
    else
    select 'bad';
    end if;
end//
delimiter ;

if方法的使用

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    select stuName,gender,score,if(score>=60,'是','否') '是否及格' from stuscore;
end//
delimiter ;

Mysql中的case语句
查看帮助
help case operator;
help case statement;

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    -- select stuName, case gender when 1 then '男' else '女' end '性别' from stuscore;
    select stuName, case when gender=1 then '男' else '女' end '性别' from stuscore;
end//
delimiter ;

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    declare weekNum int;
    select weekday(curdate()) into weekNum;
    case weekNum
    when 0 then select '星期一';
    when 1 then select '星期二';
    when 2 then select '星期三';
    when 3 then select '星期四';
    when 4 then select '星期五';
    when 5 then select '星期六';
    when 6 then select '星期日';
    end case;
end//
delimiter ;

Mysql中的循环
循环中的leave(相当与C语言中的break)    iterate(相当与C语言中的continue)
进行leave和iterate操作的时候必须指定label
while(推荐使用)    repeat...until(如果需要循环至少执行一次)        loop(只有通过leave来结束循环，可以作为死循环使用)
while语句

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    declare sco,gen,ind int;
    set sco = 56;
    set ind = 1;
    wh1:while ind <=10
    do
    if sco=60 then    -- 成绩为60的不进行添加
        -- leave wh1;
        set sco = sco + 1; -- 注意死循环
        set ind = ind + 1;
        iterate wh1;
    end if;
    set gen = mod(sco,2);
    insert into stuscore(stuName,gender,score) values(concat('stu',sco),concat(gen),sco);
    set sco = sco + 1;
    set ind = ind + 1;
    end while;
end//
delimiter ;

repeat语句

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    set @a = 1;
    repeat
    select @a;
    set @a = @a + 1;
    until @a>5 end repeat;
end //
delimiter ;

loop语句

drop procedure if exists sp1;
delimiter //
create procedure sp1()
begin
    declare a int;
    set a = 1;
    select a;
    l1:loop
    -- leave l1;
    select a;
    if a=99 then
    leave l1;
    end if;
    set a = a+1;
    end loop;
end //
delimiter ;