麦克斯仇
Think different
159
文章
34735
阅读
首页
INDEX
文章
ARTICLE
关于
ABOUT
MySQL变量、存储过程、函数、流程控制
创建日期:
2021/03/14
修改日期:
2023/11/01
MySQL
> 本文档整理自视频教程:[尚硅谷_MySQL核心技术](http://www.atguigu.com/download_detail.shtml?v=3) # 变量 ## 系统变量 > 说明:变量由系统定义,不是用户定义,属于服务器层面 注意:全局变量需要添加`global`关键字,会话变量需要添加`session`关键字;如果不写则**默认会话级别**。 ### 全局变量 > 作用域:针对于所有会话(连接)有效,但不能跨重启 - 查看所有全局变量<br>`SHOW GLOBAL VARIABLES;` - 查看满足条件的部分系统变量<br>`SHOW GLOBAL VARIABLES LIKE '%char%';` - 查看指定的系统变量的值<br>`SELECT @@global.autocommit;` - 为某个系统变量赋值 - `SET @@global.autocommit=0;` - `SET GLOBAL autocommit=0;` ### 会话变量 > 作用域:针对于当前会话(连接)有效 - 查看所有会话变量 - `SHOW SESSION VARIABLES;` - `SHOW VARIABLES;` - 查看满足条件的部分会话变量 - `SHOW SESSION VARIABLES LIKE '%char%';` - `SHOW VARIABLES LIKE '%char%';` - 查看指定的会话变量的值 - `SELECT @@autocommit;` - `SELECT @@session.autocommit;` - 为某个会话变量赋值 - `SET @@session.autocommit=1;` - `SET SESSION autocommit=1;` - `SET autocommit=1;` ## 自定义变量 > 说明:变量由用户自定义,而不是系统提供的 使用步骤: 1. 声明 2. 赋值 3. 使用(查看、比较、运算等) ### 用户变量 作用域:针对于当前会话(连接)有效,作用域同于会话变量 > 赋值操作符:`=` 或 `:=` 1. 声明并初始化 - SET @变量名=值; - SET @变量名:=值; - SELECT @变量名:=值; 2. 赋值(更新变量的值) - 方式一: - SET @变量名=值; - SET @变量名:=值; - SELECT @变量名:=值; - 方式二: - SELECT 字段 INTO @变量名 - FROM 表; 3. 使用(查看变量的值) - SELECT @变量名; > 使用示例:声明两个变量,求和并打印 ```bash mysql> SET @m=1; Query OK, 0 rows affected (0.00 sec) mysql> SET @n=1; Query OK, 0 rows affected (0.00 sec) mysql> SET @sum=@m+@n; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @sum; +------+ | @sum | +------+ | 2 | +------+ 1 row in set (0.00 sec) ``` ### 局部变量 作用域:仅仅在定义它的`begin end`块中有效,应用在`begin end`中的第一句话 1. 声明 - DECLARE 变量名 类型; - DECLARE 变量名 类型 【DEFAULT 值】; 2. 赋值(更新变量的值) - 方式一: - SET 局部变量名=值; - SET 局部变量名:=值; - SELECT 局部变量名:=值; - 方式二: - SELECT 字段 INTO 具备变量名 FROM 表; 3. 使用(查看变量的值) - SELECT 局部变量名; > 使用示例: 后面在**函数**中举例 ### 用户变量与局部变量的比较 类型 | 作用域 | 定义位置 | 语法 ---|---|---|--- 用户变量 | 当前会话 | 会话的任何地方 | 加`@`符号,不用指定类型 局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加`@`,需要指定类型 # 存储过程 ## 介绍 > 含义:一组预先编译好的SQL语句的集合,理解成批处理语句 1. 提高代码的重用性 2. 简化操作 3. 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率 ## 语法 ### 创建和调用 #### 创建语法 ```sql CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法的SQL语句) END ``` > 注意: 1. 参数列表包含三部分 - 参数模式 - in:该参数可以作为输入,也就是该参数需要调用方传入值 - out:该参数可以作为输出,也就是该参数可以作为返回值 - inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值又可以返回值 - 参数名 - 参数类型 2. 如果存储过程体仅仅只有一句话,begin end可以省略 3. 存储过程体中的每条sql语句的结尾要求必须加分号。 4. 存储过程的结尾可以使用 delimiter 重新设置<br>语法:`delimiter 结束标记` > 举例 - 参数列表举例:`in stuname varchar(20)` - 结束标记举例:`delimiter $` #### 调用语法 `CALL 存储过程名(实参列表);` #### 示例 注:以下示例中 - 若在`cmd`脚本中,需要使用了`DELIMITER $`修改结束标记,后面的语句结束符变更为`$`,即调用使用`CALL myp1()$` - 若在`SQLyog`或者`Navicat`等客户端中,建议使用**存储过程/函数设计工具**,工具中不需要使用`DELIMITER $`,且`END`后不需要添加分隔符,保存后`CALL`使用正常的`;`分隔符,即`CALL myp1();` ##### 空参列表 > 案例1:插入到user表中三条记录 ```sql DELIMITER $ # 创建 CREATE PROCEDURE myp1 () BEGIN INSERT INTO USER ( username, `password` ) VALUES ( 'john1', '123' ), ( 'lily', '123' ), ( 'rose', '123' ) ; END $ # 调用 CALL myp1 ()$ ``` > 注意事项 该事项下同 ##### 创建带`in`模式参数的存储过程 > 案例2:根据用户名称查询对应的用户(单参数) ```sql DELIMITER $ # 创建 CREATE PROCEDURE myp2 ( IN username VARCHAR ( 20 ) ) BEGIN SELECT u.* FROM user u WHERE u.username LIKE CONCAT( '%', username, '%' ); END $ # 调用 CALL myp2('rose')$ ``` > 案例3:验证用户密码是否正确(多参数) ```sql DELIMITER $ # 创建 CREATE PROCEDURE myp3 ( IN username VARCHAR ( 20 ), IN PASSWORD VARCHAR ( 20 ) ) BEGIN DECLARE result INT DEFAULT 0;#声明并初始化 SELECT COUNT(*) INTO result #赋值 FROM `user` u WHERE u.username = username AND u.`password` = PASSWORD; SELECT IF ( result > 0, '成功', '失败' );#使用 END $ #调用 CALL myp3 ( '张飞', '8888' )$ ``` ##### 创建`out`模式参数的存储过程 > 案例4:根据输入的用户名返回对应的用户ID(单参数,多参数同上) 注意:若`SELECT`返回多条结果,则执行报错 ```sql DELIMITER $ # 创建 CREATE PROCEDURE myp4(IN username VARCHAR(20),OUT id INT(11)) BEGIN SELECT u.id INTO id FROM user u WHERE u.username = username; END $ # 调用 CALL myp4('小昭',@userId) $ SELECT @userId $ ``` ##### 创建带`inout`模式参数的存储过程 > 案例5:传入a和b两个值,最终a和b都翻倍并返回 ```sql DELIMITER $ # 创建 CREATE PROCEDURE myp8 ( INOUT a INT, INOUT b INT ) BEGIN SET a = a * 2; SET b = b * 2; END $ # 调用 SET @m = 10 $ SET @n = 20 $ CALL myp8 ( @m, @n ) $ SELECT @m, @n$ ``` ### 删除 > 语法:`DROP PROCEDURE 存储过程名` ### 查看 - `DESC myp2;` - `SHOW CREATE PROCEDURE myp2;` # 函数 ## 介绍 > 含义同存储过程 区别: - 存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新 - 函数:有且仅有1个返回,适合做处理数据后返回一个结果 ## 语法 ### 创建和调用 #### 创建 ```sql CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END ``` > 注意: 1. 参数列表 包含两部分: - 参数名 - 参数类型 2. 函数体肯定会有return语句,如果没有会报错。如果return语句没有放在函数体的最后也不报错,但不建议 3. 函数体中仅有一句话,则可以省略begin end 4. 使用`delimiter`语句设置结束标记 #### 调用 `SELECT 函数名(参数列表)` #### 示例 ##### 无参有返回值 > 案例1:返回用户个数 ```sql # 创建 CREATE FUNCTION myf1() RETURNS INT BEGIN DECLARE c INT DEFAULT 0; # 定义局部变量 SELECT COUNT(*) INTO c # 赋值 FROM `user`; RETURN c; END # 调用 SELECT myf1() ``` ##### 有参有返回值 > 案例2:查询密码为`123`的用户数(单个参数) ```sql # 创建 CREATE FUNCTION myf2 ( `password` VARCHAR ( 20 ) ) RETURNS DOUBLE BEGIN SET @sal = 0;#定义用户变量 SELECT COUNT( u.id ) INTO @sal #赋值 FROM `user` u WHERE u.`password` = PASSWORD; RETURN @sal; END # 调用 SELECT myf2 ('123') ``` > 案例3:创建函数,实现传入两个float,返回二者之和(多个参数) ``` # 创建 CREATE FUNCTION myf3 ( num1 FLOAT, num2 FLOAT ) RETURNS FLOAT BEGIN DECLARE SUM FLOAT DEFAULT 0; SET SUM = num1 + num2; RETURN SUM; END # 调用 SELECT test_fun1(1,2) ``` ### 查看 `SHOW CREATE FUNCTION myf2;` ### 删除 `DROP FUNCTION myf3;` # 流程控制 - 顺序 - 分支 - 循环 ## 分支结构 ### if函数 > 语法: if(条件,值1,值2) 实现双分支,应用在`begin end`中或外面 > 案例1:创建函数,实现传入成绩,成绩>90返回A,成绩>80返回B,成绩>60返回C,否则返回D ```sql # 创建 CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR BEGIN DECLARE ch CHAR DEFAULT 'A'; IF score>90 THEN SET ch='A'; ELSEIF score>80 THEN SET ch='B'; ELSEIF score>60 THEN SET ch='C'; ELSE SET ch='D'; END IF; RETURN ch; END # 调用 SELECT test_if(87) ``` ### case结构 > 语法: 情况1:类似于switch case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end 情况2: case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end 应用在`begin end`中或外面 > 案例2:创建存储过程,如果工资<2000则删除,如果5000>工资>2000则涨工资1000,否则涨工资500 ```sql # 准备表结构和数据 DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `salary` double(10, 2) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB; INSERT INTO `employees` VALUES (1, 300.00); INSERT INTO `employees` VALUES (2, 3000.00); INSERT INTO `employees` VALUES (3, 6000.00); # 创建 CREATE PROCEDURE test_if_pro(IN sal DOUBLE) BEGIN IF sal<2000 THEN DELETE FROM employees WHERE employees.salary=sal; ELSEIF sal>=2000 AND sal<5000 THEN UPDATE employees SET salary=salary+1000 WHERE employees.`salary`=sal; ELSE UPDATE employees SET salary=salary+500 WHERE employees.`salary`=sal; END IF; END # 调用 CALL test_if_pro(300); ``` ### if结构 > 语法: if 条件1 then 语句1; elseif 条件2 then 语句2; .... else 语句n; end if; 类似于多重if,只能应用在begin end 中 > 案例3:创建函数,实现传入成绩,成绩>90返回A,成绩>80返回B,成绩>60返回C,否则返回D ```sql # 创建 CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR BEGIN DECLARE ch CHAR DEFAULT 'A'; CASE WHEN score>90 THEN SET ch='A'; WHEN score>80 THEN SET ch='B'; WHEN score>60 THEN SET ch='C'; ELSE SET ch='D'; END CASE; RETURN ch; END # 调用 SELECT test_case(56) ``` ## 循环结构 > 分类: - while - loop - repeat > 循环控制: - `iterate`类似于`continue`继续,结束本次循环,继续下一次 - `leave`类似于`break`跳出,结束当前所在的循环 ### while > 语法: 【标签:】while 循环条件 do 循环体; end while【 标签】; 联想: while(循环条件){ 循环体; } ### 2.loop > 语法: 【标签:】loop 循环体; end loop 【标签】; 可以用来模拟简单的死循环 ### repeat > 语法: 【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】; ### 案例 #### 没有添加循环控制语句 > 批量插入,根据次数插入到admin表中多条记录 ```sql # 创建 CREATE PROCEDURE pro_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i<=insertCount DO INSERT INTO user(username,`password`) VALUES(CONCAT('Rose',i),'666'); SET i=i+1; END WHILE; END # 调用 CALL pro_while1(100) ``` #### 添加leave语句 > 批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止 ```sql # 创建 CREATE PROCEDURE test_while1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 1; a:WHILE i<=insertCount DO INSERT INTO user(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); IF i>=20 THEN LEAVE a; END IF; SET i=i+1; END WHILE a; END # 调用 CALL test_while1(100) ``` #### 添加iterate语句 > 批量插入,根据次数插入到admin表中多条记录,只插入偶数次 ```sql # 创建 CREATE PROCEDURE test_while2(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i<=insertCount DO SET i=i+1; IF MOD(i,2)!=0 THEN ITERATE a; END IF; INSERT INTO user(username,`password`) VALUES(CONCAT('xiaohua',i),'0000'); END WHILE a; END # 调用 CALL test_while2(100) ```
4
全部评论