玩转Mysql系列 - 第18篇:流程控制语句(高手进阶)
Mysql系列的目标是:通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。
欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。
这是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种写法
while:类似于java中的while循环
repeat:类似于java中的do while循环
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中类似,这块的示例算是给大家留的作业,写好的发在留言区,谢谢。
总结
本文主要介绍了mysql中控制流语句的使用,请大家下去了多练习,熟练掌握
if函数常用在select中
case语句有2种写法,主要用在select、begin end中,select中end后面可以省略case,begin end中使用不能省略case
if语句用在begin end中
3种循环体的使用,while类似于java中的while循环,repeat类似于java中的do while循环,loop类似于java中的死循环,都用于begin end中
循环中体中的控制依靠
leave
和iterate
,leave
类似于java中的break
可以退出循环,iterate
类似于java中的continue可以结束本次循环
Mysql系列目录
mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!
路人甲java
▲长按图片识别二维码关注
路人甲java:工作10年的前阿里P7分享Java、算法、数据库方面的技术干货!坚信用技术改变命运,让家人过上更体面的生活
来源:https://itsoku.blog.csdn.net/article/details/102383358