图片 10

流程控制语句(MySQL/MariaDB )

图片 1
一、分支结构
1.If函数
语法:if(条件,值1,值2)
功能:实现双分支
应用范围:begin – 中 或者 外面
2.case结构
语法一:结构switch结构
case 变量或者表达式
when 值1 then 语句1;
when 值2 then 语句2;

else 语法n;
end case;
语法二:case
when 条件1 then 语句1;
when 条件2 then 语句2;

else 语句n;
end case;
应用在begin – end 中或者外面
3.if结构
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;

else 语句n;
end if;
功能:类似于多重if
【只能应用在begin end中】
例1:创建函数,传入成绩,返回学生成绩等级
图片 2
图片 3
二、循环结构
图片 4
1.while
语法:【标签:】while 循环条件 do
循环体;
End while【标签】;
2.loop
语法:【标签:】loop
循环体;
End loop 【标签】;
【常用于模拟死循环】【借助标签来结束循环】
3.repeat
语法:【标签:】repeat
循环体;
Until 结束循环的条件
End repeat 【标签:】;
例2 向admin表中批量插入数据【不含循环控制语句】
图片 5
图片 6
例3 向admin表中批量插入数据,插入第6条的时候停止while循环
图片 7
图片 8
例4 只插入偶数次
图片 9
图片 10

在MySQL数据库中使用PHP语句可以直接得到想要的结果

MySQL/MariaDB中的符合语句结构有:BEGIN…END,if,case,while,loop,repeat,cursor,condition,handler。

注:loop和repeat用法和while类似。

  存储过程中定义局部变量:  declare语句

在MySQL中,它们都只能在存储程序(procedure,function,trigger)内部使用,但在MariaDB中,从10.1.1版本之后,BEGIN…END,IF,CASE,LOOP,REPEAT,WHILE可以在存储程序的外部使用。但实际上,除了begin、case和if能正常单独定义在存储程序之外,loop、repeat、while都是鸡肋,因为无法给单独定义的结构打标签,只能无限循环而无法退出。

  declare 变量名 类型(长度) default 默认值;

1.BEGIN…END

[label:] BEGIN [NOT ATOMIC]
    [statement_list]
END [label]

begin…end默认只能在存储程序内部使用,此时可以使用label为begin…end打上标签。可以在end处使用标签来结束begin…end。如果end后指定了label,则end处的label必须和begin前的label完全相同。

可以使用NOT
ATOMIC关键字让BEGIN在存储程序外部使用,但此时不能为begin…end打标签,且一定注意先修改delimiter,免得begin…end中的语句中途终止。

例如,下面三个begin…end语句块:

delimiter $$
begin not atomic        /* 在存储程序外部单独使用begin...end */
    declare a int;
    set a=3;
    select a;
end$$

create or replace procedure p1()
my_label: begin           /* 为begin打上标签 */
    declare b int;
    set b=3;
    select b;
end$$                  /* 可以不使用标签结束begin */

create or replace procedure p2()
my_label: begin
    declare c int;
    set c=3;
    select c;
end my_label$$         /* 使用的结束标签必须和开始标签一致 */
delimiter ;
call p1;
call p2;

在begin…end中,除了可以写一些SQL语句,还可以使用declare定义变量、条件、处理器和游标。但它们之间有顺序要求,顺序如下:

  • DECLARE local variables;
  • DECLARE CONDITIONs;
  • DECLARE CURSORs;
  • DECLARE HANDLERs;

还可以begin嵌套,内层的begin可以使用外层begin定义的变量。

delimiter $$
CREATE PROCEDURE t()
BEGIN
   DECLARE x TINYINT UNSIGNED DEFAULT 1;
   BEGIN
      DECLARE x CHAR(2) DEFAULT '02';
       DECLARE y TINYINT UNSIGNED DEFAULT 10;
       SELECT x, y;
   END;
   SELECT x;
END;
delimiter ;

  #num1 num2值互换#

2.true和false

在MySQL和MariaDB中,true和false是无条件的真、假。

true和1等价,false和0等价,且它们不区分大小写。任何时候,都可以互换它们,但任何时候,它们的结果都换转换为数值的1或0。即使将数据类型定义为int,也可以为其传递true字符串。

例如:

mysql> select true,false;
+------+-------+
| TRUE | FALSE |
+------+-------+
|    1 |     0 |
+------+-------+
begin not atomic 
    declare a int;
    set a=true;
    select a;
end$$
+------+
| a    |
+------+
|    1 |
+------+
delimiter //
CREATE PROCEDURE exchange (
    INOUT num1 INT (5),
    INOUT num2 INT (5)
)
BEGIN

DECLARE tmp INT (5) DEFAULT 0 ;
SET tmp = num1 ;
SET num1 = num2 ;
SET num2 = tmp ;
END ;//


SET @a = 100 ,@b = 200 ;//

SELECT
    @a ,@b ;//

CALL exchange (@a ,@b) ;//

SELECT
    @a ,@b ;//

3.if语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

其中search_condition是条件判断语句,statement_list是要执行的语句。

在MySQL中,if语句必须作为语句块写在存储过程或函数等程序结构中。在MariaDB
10.1.1之后,可以独立使用,但注意修改delimiter。

-- 独立使用if结构
delimiter $$
if 1>2 then 
    select 'true'; 
else 
    select 'false'; 
end if$$
delimiter ;

-- 在存储过程中使用
DELIMITER $$
CREATE or replace PROCEDURE proc1(a INT,OUT i INT)
BEGIN
    IF a > 0 THEN 
        SET @a1 = a + 100; 
    ELSEIF a = 0 THEN 
        SET @a1 = a + 200;
    ELSE 
        SET @a1 = a + 300; 
    END IF ;
    SET i:=@a1;
END$$
DELIMITER ;

-- 测试if结构
CALL proc1(1,@a);
CALL proc1(0,@b);
CALL proc1(-1,@c);

查看结果。

SELECT @a,@b,@c;
    @a      @b      @c  
------  ------  --------
   101     200       299

注意,还有一个if(expr,true_value,false_value)函数,如果expr为真,则返回true_value,否则返回false_value,所以这里的if函数和if流程控制语句是不同的。例如:

MariaDB [test]> select if(1>2,'true','false') as a;
+-------+
| a     |
+-------+
| false |
+-------+
MariaDB [test]> select if(1<2,'true','false') as a; 
+------+
| a    |
+------+
| true |
+------+

 

4.case语句

在MySQL中,有case表达式和case语句两种结构。

mysql> help case
topics:
   CASE OPERATOR
   CASE STATEMENT

它们的区别有:

  • case表达式使用end关键字作为结束符,而case语句使用end
    case作为结束符;
  • case表达式有可选项else,省略它的时候其默认值为null,case语句也有可选项else,但是省略它的时候没有默认值,且else语句中不能是null;
  • case表达式可以在任何地方使用(例如在select中),而case语句只能在存储过程或函数这样类型的编程结构中使用。

mysql> help case operator
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] END

Examples:
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

mysql> help case statement
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

虽然case表达式和case语句在性质上不一样,但是使用它们的思路是一样的。CASE value WHEN...只能与value进行等同性检查,case when ... then...则要灵活的多。

case operator的使用示例:

MariaDB [test]> select * from Student;
+------+----------+------+--------+
| sid  | name     | age  | class  |
+------+----------+------+--------+
|    1 | chenyi   |   22 | Java   |
|    2 | huanger  |   23 | Python |
|    3 | zhangsan |   21 | Java   |
|    4 | lisi     |   20 | C#     |
|    5 | wangwu   |   21 | Python |
|    6 | zhaoliu  |   19 | Java   |
|    7 | qianqi   |   22 | C      |
|    8 | sunba    |   20 | C++    |
|    9 | yangjiu  |   24 | Java   |
+------+----------+------+--------+

MariaDB [test]> select name,case when age>21 then 'older' else 'younger' end as oy from Student;     
+----------+---------+
| name     | oy      |
+----------+---------+
| chenyi   | older   |
| huanger  | older   |
| zhangsan | younger |
| lisi     | younger |
| wangwu   | younger |
| zhaoliu  | younger |
| qianqi   | older   |
| sunba    | younger |
| yangjiu  | older   |
+----------+---------+

case statement的使用示例:

DELIMITER $$
CREATE OR REPLACE PROCEDURE proc1(a INT,OUT i INT)
BEGIN
    CASE 
        WHEN a>0 THEN 
            SET @a1=a+100;
        WHEN a=0 THEN
            SET @a1=a+200;
        ELSE
            SET @a1=a+300;
    END CASE;
    SET i=@a1;
END$$
DELIMITER ;
-- 调用存储过程进行测试
CALL proc1(1,@x);
CALL proc1(0,@y);
CALL proc1(-1,@z);
-- 查看测试结果
SELECT @x,@y,@z;
    @x      @y      @z  
------  ------  --------
   101     200       299

  【if】  #根据学生编号和学生成绩判断学生成绩等级90分-A
80分-B 70分-C 60分-D 60分以下-E#

5.loop、leave和iterate

loop是循环,leave是跳出整个循环(类似于break),iterate是跳出当前循环进入下一个循环(类似于continue)。

 [begin_label:] LOOP
    statement_list
END LOOP [end_label]

在loop结构中,使用end loop关键字作为结束标记。

在loop中可以使用标签来标注该循环。如果要标记loop循环,则使用”begin_label:”和”end_label”,但要求begin_label和end_label是完全相同的字符串标记(或者end不用标签直接结束也可以)。

add_num: loop
    set @a=@a+1
end loop add_num;

该语句由于没有跳出循环语句,所以将会死循环。

因此,loop一般会配合leave来使用。leave的作用是退出指定标记的语句结构,一般用在循环中用来退出循环(不仅是用在loop循环,其他循环结构也一样),相当于break。因为begin…end中也能使用标记,所以leave也能用在begin…end中。
例如下面的例子,计算从1加到给定的数。通过if判断退出循环的条件,达到退出条件时使用leave来退出。

delimiter $$
create or replace procedure proc1(n int)
begin
    declare sum,i int default 0;
    loop_label: loop
        set sum=sum+i;
        if i=n then
            leave loop_label;
        end if;
        set i=i+1;
    end loop loop_label;
    select sum;
end$$
delimiter ;

call proc1(100);   /* 从1加到100 */
   sum  
--------
    5050

iterate和leave的用法一样,只不过iterate是退出当前循环直接进入下一循环。

例如下面的存储过程,它的功能是计算传入数值范围内的奇数和。当超出传入值的范围时直接退出循环,当遇到偶数时直接进入下一循环。

delimiter $$
create or replace procedure proc2(n int)
begin
    declare i,sum int default 0;
    my_loop: loop
        set i=i+1;
        if i>n then 
            leave my_loop;
        elseif mod(i,2)=0 then
            iterate my_loop;
        else 
            set sum=i+sum;
        end if;
    end loop;
    select sum;
end$$
delimiter ;

调用存储过程来测试。

CALL proc4(9);
   sum  
--------
      25

虽然在MariaDB中,loop能单独定义在存储程序之外,但是由于定义在外面的时候无法打标签,而退出循环的leave却必须接标签才能退出loop,因此loop定义在存储程序之外完全是一个鸡肋的功能。同理,repeat和while也是一样的。

  select 字段列表 into 变量列表 from 表
where 条件 
将某条记录中的字段列表中的值存入到变量列表中,变量列表中的变量一定是提前定义的。

6.repeat循环

loop循环结构中没有退出循环的条件,只能人为书写条件判断语句来判断何时退出循环。而repeat循环结构提供了直接定义退出循环条件的功能。

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

其中until部分定义的是退出循环的条件。注意,until语句后面不需要结束提示符,给出了结束符反而报错。

例如下面的存储过程中,当a变量等于传入参数i的时候退出循环。

delimiter $$
create or replace procedure proc5(i int)
begin
    declare a int default 1;
    declare sum int default 1;
    addnum: repeat
        set a=a+1;
        if mod(a,2)=1 then
            set sum=sum+a;
        end if;
    until a=i
    end repeat addnum;
    select sum;
end$$
delimiter ;
call proc5(10);
   sum  
--------
      25

    格式:

7.while循环

while循环和repeat循环使用方法一样,但是思路不一样。repeat是满足条件时退出,而while是满足条件时才执行循环中定义的语句。

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

例如下面的存储过程,仍然是计算传入值范围内的奇数和。但使用的是while循环。

DROP PROCEDURE IF EXISTS proc6;
DELIMITER $$
CREATE PROCEDURE proc6(i INT)
BEGIN
    SET @a=1,@sum=1;
    addnum: WHILE @a<i DO
        SET @a=@a+1;
        IF MOD(@a,2)=1 THEN
            SET @sum=@a+@sum;
        END IF;
    END WHILE addnum;
    SELECT @sum AS SUM;
END$$
DELIMITER ;

      If 条件 then

        执行代码

      Else if 条件 then

        执行代码

        …..

      Else

        执行代码

      End if; 

delimiter //
CREATE PROCEDURE marks_level_if (
    IN stu_no INT (4),
    IN cla_no INT (4),
    OUT _level CHAR (4)
)
BEGIN

DECLARE _mark INT (4) DEFAULT 0 ; SELECT
    mark INTO _mark
FROM
    marks
WHERE
    sid = stu_no
AND cid = cla_no ;

IF _mark >= 90 THEN
SET _level = "A" ;

ELSEIF _mark >= 80 THEN
SET _level = "B" ;

ELSEIF _mark >= 70 THEN
SET _level = "C" ;

ELSEIF _mark >= 60 THEN
SET _level = "D" ;

ELSE
SET _level = "E" ;

ENDIF ;
END ;//


SET @result = "" ;//

CALL marks_level_if (1, 1 ,@result) ;//

SELECT
    @result ;//

 

  【case】  #根据学生编号和学生成绩判断学生成绩等级90分-A
80分-B 70分-C 60分-D 60分以下-E#

    CASE 变量

      WHEN 值1 THEN 代码1

      WHEN 值2  THEN 代码2 …

      ELSE 代码n

    END CASE

  Or:

    CASE

      WHEN  条件1 THEN 代码1

      WHEN  条件2  THEN 代码2 …

    ELSE 代码n