Mysql高手系列 – 第18篇：mysql流程控制语句详解（高手进阶）

  • 2019 年 10 月 9 日
  • 筆記

Mysql系列的目标是：通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。

这是Mysql系列第18篇。

环境：mysql5.7.25，cmd命令中进行演示。

代码中被[]包含的表示可选，|符号分开的表示可选其一。

上一篇存储过程&自定义函数，对存储过程和自定义函数做了一个简单的介绍，但是如何能够写出复杂的存储过程和函数呢？

这需要我们熟练掌握流程控制语句才可以，本文主要介绍mysql中流程控制语句的使用，上干货。

本篇内容

  • if函数
  • case语句
  • if结构
  • while循环
  • repeat循环
  • loop循环
  • 循环体控制语句

准备数据

/*建库javacode2018*/  drop database if exists javacode2018;  create database javacode2018;    /*切换到javacode2018库*/  use javacode2018;    /*创建表：t_user*/  DROP TABLE IF EXISTS t_user;  CREATE TABLE t_user(    id int PRIMARY KEY COMMENT '编号',    sex TINYINT not null DEFAULT 1 COMMENT '性别,1:男,2:女',    name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名'  )COMMENT '用户表';    /*插入数据*/  INSERT INTO t_user VALUES  (1,1,'路人甲Java'),(2,1,'张学友'),(3,2,'王祖贤'),(4,1,'郭富城'),(5,2,'李嘉欣');    SELECT * FROM t_user;    DROP TABLE IF EXISTS test1;  CREATE TABLE test1 (a int not null);    DROP TABLE IF EXISTS test2;  CREATE TABLE test2 (a int not null,b int NOT NULL );

if函数

语法

if(条件表达式,值1,值2);

if函数有3个参数。

当参数1为true的时候，返回值1，否则返回值2。

示例

需求：查询t_user表数据，返回：编号、性别（男、女）、姓名。

分析一下：数据库中性别用数字表示的，我们需要将其转换为（男、女），可以使用if函数。

mysql> SELECT id 编号,if(sex=1,'男','女') 性别,name 姓名 FROM t_user;  +--------+--------+---------------+  | 编号   | 性别   | 姓名          |  +--------+--------+---------------+  |      1 | 男     | 路人甲Java    |  |      2 | 男     | 张学友        |  |      3 | 女     | 王祖贤        |  |      4 | 男     | 郭富城        |  |      5 | 女     | 李嘉欣        |  +--------+--------+---------------+  5 rows in set (0.00 sec)

CASE结构

2种用法。

第1种用法

类似于java中的switch语句。

case 表达式  when 值1 then 结果1或者语句1（如果是语句需要加分号）  when 值2 then 结果2或者语句2  ...  else 结果n或者语句n  end [case] （如果是放在begin end之间需要加case，如果在select后则不需要）
示例1：select中使用

查询t_user表数据，返回：编号、性别（男、女）、姓名。

/*写法1：类似于java中的if else*/  SELECT id 编号,(CASE sex WHEN 1 THEN '男' ELSE '女' END) 性别,name 姓名 FROM t_user;  /*写法2：类似于java中的if else if*/  SELECT id 编号,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) 性别,name 姓名 FROM t_user;
示例2：begin end中使用

写一个存储过程，接受3个参数：id，性别（男、女），姓名，然后插入到t_user表

创建存储过程：

/*删除存储过程proc1*/  DROP PROCEDURE IF EXISTS proc1;  /*s删除id=6的记录*/  DELETE FROM t_user WHERE id=6;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程proc1*/  CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16))    BEGIN      /*声明变量v_sex用于存放性别*/      DECLARE v_sex TINYINT UNSIGNED;      /*根据sex_str的值来设置性别*/      CASE sex_str        when '男' THEN        SET v_sex = 1;      WHEN '女' THEN        SET v_sex = 2;      END CASE ;      /*插入数据*/      INSERT INTO t_user VALUES (id,v_sex,name);    END $  /*结束符置为;*/  DELIMITER ;

调用存储过程：

CALL proc1(6,'男','郭富城');

查看效果：

mysql> select * from t_user;  +----+-----+---------------+  | id | sex | name          |  +----+-----+---------------+  |  1 |   1 | 路人甲Java    |  |  2 |   1 | 张学友        |  |  3 |   2 | 王祖贤        |  |  4 |   1 | 郭富城        |  |  5 |   2 | 李嘉欣        |  |  6 |   1 | 郭富城        |  +----+-----+---------------+  6 rows in set (0.00 sec)

示例3：函数中使用

需求：写一个函数，根据t_user表sex的值，返回男女

创建函数：

/*删除存储过程proc1*/  DROP FUNCTION IF EXISTS fun1;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程proc1*/  CREATE FUNCTION fun1(sex TINYINT UNSIGNED)    RETURNS varchar(8)    BEGIN      /*声明变量v_sex用于存放性别*/      DECLARE v_sex VARCHAR(8);      CASE sex      WHEN 1 THEN        SET v_sex:='男';      ELSE        SET v_sex:='女';      END CASE;      RETURN v_sex;    END $  /*结束符置为;*/  DELIMITER ;

看一下效果：

mysql> select sex, fun1(sex) 性别,name FROM t_user;  +-----+--------+---------------+  | sex | 性别   | name          |  +-----+--------+---------------+  |   1 | 男     | 路人甲Java    |  |   1 | 男     | 张学友        |  |   2 | 女     | 王祖贤        |  |   1 | 男     | 郭富城        |  |   2 | 女     | 李嘉欣        |  |   1 | 男     | 郭富城        |  +-----+--------+---------------+  6 rows in set (0.00 sec)

第2种用法

类似于java中多重if语句。

case  when 条件1 then 结果1或者语句1（如果是语句需要加分号）  when 条件2 then 结果2或者语句2  ...  else 结果n或者语句n  end [case] （如果是放在begin end之间需要加case，如果是在select后面case可以省略）

这种写法和1中的类似，大家用上面这种语法实现第1中用法中的3个示例，贴在留言中。

if结构

if结构类似于java中的 if..else if…else的语法，如下：

if 条件语句1 then 语句1;  elseif 条件语句2 then 语句2;  ...  else 语句n;  end if;

只能使用在begin end之间。

示例

写一个存储过程，实现用户数据的插入和新增，如果id存在，则修改，不存在则新增，并返回结果

/*删除id=7的记录*/  DELETE FROM t_user WHERE id=7;  /*删除存储过程*/  DROP PROCEDURE IF EXISTS proc2;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT)    BEGIN      DECLARE v_count TINYINT DEFAULT 0;/*用来保存user记录的数量*/      /*根据v_id查询数据放入v_count中*/      select count(id) into v_count from t_user where id = v_id;      /*v_count>0表示数据存在，则修改，否则新增*/      if v_count>0 THEN        BEGIN          DECLARE lsex TINYINT;          select if(lsex='男',1,2) into lsex;          update t_user set sex = lsex,name = v_name where id = v_id;          /*获取update影响行数*/          select ROW_COUNT() INTO result;        END;      else        BEGIN          DECLARE lsex TINYINT;          select if(lsex='男',1,2) into lsex;          insert into t_user VALUES (v_id,lsex,v_name);          select 0 into result;        END;      END IF;    END $  /*结束符置为;*/  DELIMITER ;

看效果：

mysql> SELECT * FROM t_user;  +----+-----+---------------+  | id | sex | name          |  +----+-----+---------------+  |  1 |   1 | 路人甲Java    |  |  2 |   1 | 张学友        |  |  3 |   2 | 王祖贤        |  |  4 |   1 | 郭富城        |  |  5 |   2 | 李嘉欣        |  |  6 |   1 | 郭富城        |  +----+-----+---------------+  6 rows in set (0.00 sec)    mysql> CALL proc2(7,'男','黎明',@result);  Query OK, 1 row affected (0.00 sec)    mysql> SELECT @result;  +---------+  | @result |  +---------+  |       0 |  +---------+  1 row in set (0.00 sec)    mysql> SELECT * FROM t_user;  +----+-----+---------------+  | id | sex | name          |  +----+-----+---------------+  |  1 |   1 | 路人甲Java    |  |  2 |   1 | 张学友        |  |  3 |   2 | 王祖贤        |  |  4 |   1 | 郭富城        |  |  5 |   2 | 李嘉欣        |  |  6 |   1 | 郭富城        |  |  7 |   2 | 黎明          |  +----+-----+---------------+  7 rows in set (0.00 sec)    mysql> CALL proc2(7,'男','梁朝伟',@result);  Query OK, 1 row affected (0.00 sec)    mysql> SELECT @result;  +---------+  | @result |  +---------+  |       1 |  +---------+  1 row in set (0.00 sec)    mysql> SELECT * FROM t_user;  +----+-----+---------------+  | id | sex | name          |  +----+-----+---------------+  |  1 |   1 | 路人甲Java    |  |  2 |   1 | 张学友        |  |  3 |   2 | 王祖贤        |  |  4 |   1 | 郭富城        |  |  5 |   2 | 李嘉欣        |  |  6 |   1 | 郭富城        |  |  7 |   2 | 梁朝伟        |  +----+-----+---------------+  7 rows in set (0.00 sec)

循环

mysql中循环有3种写法

  1. while：类似于java中的while循环
  2. repeat：类似于java中的do while循环
  3. loop：类似于java中的while(true)死循环，需要在内部进行控制。

循环控制

对循环内部的流程进行控制，如：

结束本次循环

类似于java中的continue

iterate 循环标签;
退出循环

类似于java中的break

leave 循环标签;

下面我们分别介绍3种循环的使用。

while循环

类似于java中的while循环。

语法

[标签:]while 循环条件 do  循环体  end while [标签];

标签：是给while取个名字，标签和iterate、leave结合用于在循环内部对循环进行控制：如：跳出循环、结束本次循环。

注意：这个循环先判断条件，条件成立之后，才会执行循环体，每次执行都会先进行判断。

示例1：无循环控制语句

根据传入的参数v_count向test1表插入指定数量的数据。

/*删除test1表记录*/  DELETE FROM test1;  /*删除存储过程*/  DROP PROCEDURE IF EXISTS proc3;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc3(v_count int)    BEGIN      DECLARE i int DEFAULT 1;      a:WHILE i<=v_count DO        INSERT into test1 values (i);        SET i=i+1;      END WHILE;    END $  /*结束符置为;*/  DELIMITER ;

见效果：

mysql> CALL proc3(5);  Query OK, 1 row affected (0.01 sec)    mysql> SELECT * from test1;  +---+  | a |  +---+  | 1 |  | 2 |  | 3 |  | 4 |  | 5 |  +---+  5 rows in set (0.00 sec)

示例2：添加leave控制语句

根据传入的参数v_count向test1表插入指定数量的数据，当插入超过10条，结束。

/*删除存储过程*/  DROP PROCEDURE IF EXISTS proc4;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc4(v_count int)    BEGIN      DECLARE i int DEFAULT 1;      a:WHILE i<=v_count DO        INSERT into test1 values (i);        /*判断i=10，离开循环a*/        IF i=10 THEN          LEAVE a;        END IF;          SET i=i+1;      END WHILE;    END $  /*结束符置为;*/  DELIMITER ;

见效果：

mysql> DELETE FROM test1;  Query OK, 20 rows affected (0.00 sec)    mysql> CALL proc4(20);  Query OK, 1 row affected (0.02 sec)    mysql> SELECT * from test1;  +----+  | a  |  +----+  |  1 |  |  2 |  |  3 |  |  4 |  |  5 |  |  6 |  |  7 |  |  8 |  |  9 |  | 10 |  +----+  10 rows in set (0.00 sec)

示例3：添加iterate控制语句

根据传入的参数v_count向test1表插入指定数量的数据，只插入偶数数据。

/*删除test1表记录*/  DELETE FROM test1;  /*删除存储过程*/  DROP PROCEDURE IF EXISTS proc5;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc5(v_count int)    BEGIN      DECLARE i int DEFAULT 0;      a:WHILE i<=v_count DO        SET i=i+1;        /*如果i不为偶数，跳过本次循环*/        IF i%2!=0 THEN          ITERATE a;        END IF;        /*插入数据*/        INSERT into test1 values (i);      END WHILE;    END $  /*结束符置为;*/  DELIMITER ;

见效果：

mysql> DELETE FROM test1;  Query OK, 5 rows affected (0.00 sec)    mysql> CALL proc5(10);  Query OK, 1 row affected (0.01 sec)    mysql> SELECT * from test1;  +----+  | a  |  +----+  |  2 |  |  4 |  |  6 |  |  8 |  | 10 |  +----+  5 rows in set (0.00 sec)

示例4：嵌套循环

test2表有2个字段（a,b），写一个存储过程（2个参数：v_a_count，v_b_count)，使用双重循环插入数据，数据条件：a的范围[1,v_a_count]、b的范围[1,v_b_count]所有偶数的组合。

/*删除存储过程*/  DROP PROCEDURE IF EXISTS proc8;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc8(v_a_count int,v_b_count int)    BEGIN      DECLARE v_a int DEFAULT 0;      DECLARE v_b int DEFAULT 0;        a:WHILE v_a<=v_a_count DO        SET v_a=v_a+1;        SET v_b=0;          b:WHILE v_b<=v_b_count DO            SET v_b=v_b+1;          IF v_a%2!=0 THEN            ITERATE a;          END IF;            IF v_b%2!=0 THEN            ITERATE b;          END IF;            INSERT INTO test2 VALUES (v_a,v_b);          END WHILE b;        END WHILE a;    END $  /*结束符置为;*/  DELIMITER ;

代码中故意将ITERATE a;放在内层循环中，主要让大家看一下效果。

见效果：

mysql> DELETE FROM test2;  Query OK, 6 rows affected (0.00 sec)    mysql> CALL proc8(4,6);  Query OK, 1 row affected (0.01 sec)    mysql> SELECT * from test2;  +---+---+  | a | b |  +---+---+  | 2 | 2 |  | 2 | 4 |  | 2 | 6 |  | 4 | 2 |  | 4 | 4 |  | 4 | 6 |  +---+---+  6 rows in set (0.00 sec)

repeat循环

语法

[标签:]repeat  循环体;  until 结束循环的条件 end repeat [标签];

repeat循环类似于java中的do…while循环，不管如何，循环都会先执行一次，然后再判断结束循环的条件，不满足结束条件，循环体继续执行。这块和while不同，while是先判断条件是否成立再执行循环体。

示例1：无循环控制语句

根据传入的参数v_count向test1表插入指定数量的数据。

/*删除存储过程*/  DROP PROCEDURE IF EXISTS proc6;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc6(v_count int)    BEGIN      DECLARE i int DEFAULT 1;      a:REPEAT        INSERT into test1 values (i);        SET i=i+1;      UNTIL i>v_count END REPEAT;    END $  /*结束符置为;*/  DELIMITER ;

见效果：

mysql> DELETE FROM test1;  Query OK, 1 row affected (0.00 sec)    mysql> CALL proc6(5);  Query OK, 1 row affected (0.01 sec)    mysql> SELECT * from test1;  +---+  | a |  +---+  | 1 |  | 2 |  | 3 |  | 4 |  | 5 |  +---+  5 rows in set (0.00 sec)

repeat中iterate和leave用法和while中类似，这块的示例算是给大家留的作业，写好的发在留言区，谢谢。

loop循环

语法

[标签:]loop  循环体;  end loop [标签];

loop相当于一个死循环，需要在循环体中使用iterate或者leave来控制循环的执行。

示例1：无循环控制语句

根据传入的参数v_count向test1表插入指定数量的数据。

/*删除存储过程*/  DROP PROCEDURE IF EXISTS proc7;  /*声明结束符为$*/  DELIMITER $  /*创建存储过程*/  CREATE PROCEDURE proc7(v_count int)    BEGIN      DECLARE i int DEFAULT 0;      a:LOOP        SET i=i+1;        /*当i>v_count的时候退出循环*/        IF i>v_count THEN          LEAVE a;        END IF;        INSERT into test1 values (i);      END LOOP a;    END $  /*结束符置为;*/  DELIMITER ;

见效果：

mysql> DELETE FROM test1;  Query OK, 5 rows affected (0.00 sec)    mysql> CALL proc7(5);  Query OK, 1 row affected (0.01 sec)    mysql> SELECT * from test1;  +---+  | a |  +---+  | 1 |  | 2 |  | 3 |  | 4 |  | 5 |  +---+  5 rows in set (0.00 sec)

loop中iterate和leave用法和while中类似，这块的示例算是给大家留的作业，写好的发在留言区，谢谢。

总结

  1. 本文主要介绍了mysql中控制流语句的使用，请大家下去了多练习，熟练掌握
  2. if函数常用在select中
  3. case语句有2种写法，主要用在select、begin end中，select中end后面可以省略case，begin end中使用不能省略case
  4. if语句用在begin end中
  5. 3种循环体的使用，while类似于java中的while循环，repeat类似于java中的do while循环，loop类似于java中的死循环，都用于begin end中
  6. 循环中体中的控制依靠leave和iterate，leave类似于java中的break可以退出循环，iterate类似于java中的continue可以结束本次循环

Mysql系列目录

  1. 第1篇：mysql基础知识
  2. 第2篇：详解mysql数据类型（重点）
  3. 第3篇：管理员必备技能(必须掌握)
  4. 第4篇：DDL常见操作
  5. 第5篇：DML操作汇总（insert,update,delete）
  6. 第6篇：select查询基础篇
  7. 第7篇：玩转select条件查询，避免采坑
  8. 第8篇：详解排序和分页(order by & limit)
  9. 第9篇：分组查询详解（group by & having）
  10. 第10篇：常用的几十个函数详解
  11. 第11篇：深入了解连接查询及原理
  12. 第12篇：子查询
  13. 第13篇：细说NULL导致的神坑，让人防不胜防
  14. 第14篇：详解事务
  15. 第15篇：详解视图
  16. 第16篇：变量详解
  17. 第17篇：存储过程&自定义函数详解
  18. 第18篇：流程控制语句
  19. 第19篇：游标详解
  20. 第20篇：异常捕获及处理详解
  21. 第21篇：什么是索引？

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

VirMach 便宜 VPS

QNews

QNews