MySQL编程

MySql

1.SQL编程(了解)

(1) if-elseif-else语句

#语法:
if 条件 then
    //代码1
elseif 条件 then
    //代码2
else
    //代码3
end if;

create procedure pro_8(in grade int)
begin
     if grade=1 then
        select '金牌会员' as '等级';
     elseif grade=2 then
        select '普通会员' as '等级';
     else
         select '游客' as '等级';
     end if;
end //
#调用
call pro_8(3)//

(2) case-when语句

create procedure pro_9(in num int)
begin
     case num
          when 1 then select '杀马特' as '气质';
          when 2 then select '屌丝' as '气质';
          when 3 then select '正常人' as '气质';
          when 4 then select '贵族' as '气质';
          else select '输入不正确' as '气质';
     end case;
end //

call pro_9(0)//

#显示学员的学号、姓名、性别、语文成绩、等级

select sid,sname,sex,ch,case
       when ch>=90 then '等级A'
       when ch>=80 then '等级B'
       when ch>=70 then '等级C'
       when ch>=60 then '等级D'
       else '等级E'
end as '等级' from stuinfo left join stumarks using(sid)//


select sid,sname,sex,ch from stuinfo left join stumarks using(sid)//

(3)loop循环

loop遇到leave退出
create procedure proc(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     sign:loop
         set total=total+i;
         set i=i+1;
         if i>=num then
            leave sign;# leave=break
         end if;
     end loop;
     select total from dual;
end //

call proc(100)//
#如果没有设置标签名,leave loop
#sign是循环名,用于结束循环,可以自己随意取名字

(4)while循环

#语法:
while 条件 do
    //代码
end while

create procedure pro_11(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     while num>=i do
           set total=total+i;
           set i=i+1;
     end while;
     select total from dual;
end //

call pro_11(100)//

(5)repeat循环

#语法
repeat
    代码
    until 条件    -- 直重复到条件为true才结束
end repeat

create procedure pro_12(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     repeat
           set total=total+i;
           set i=i+1;
           until i>num
     end repeat;
     select total from dual;
end //

call pro_12(100)//

(6)leave和iterate

leave类似于break，iterate类似于continue

create procedure pro_13()
begin
     declare i int default 0;
     sign:while i<5 do
           set i=i+1;
           if(i=3) then
                   leave sign;   -- 类似于break
                   #iterate sign;    -- 类似于continue
           end if;
           select i from dual;
     end while;
end //

call pro_13()//