MySQL复习笔记（十三）：变量、存储过程和函数、流程控制结构

变量

分类：

• 系统变量
  • 全局变量：服务器每次启动都将为所有全局变量设置初始值，对于所有会话（连接）都有效，但不能跨重启
  • 会话变量：仅仅对当前的会话（连接）有效
• 自定义变量
  • 用户变量
  • 局部变量

一、 系统变量

说明：变量由系统提供，非用户自定义，属于服务器层面

使用语法：

• 如果是全局级别需要加global
• 如果是绘画级别，需要加session
• 如果不写，默认是session

1、查看所有的系统变量

show global|【session】 variables;

2、查看满足条件的部分系统变量

show global|【session】 variables like '%char%';

3、查看指定的某个系统变量的值

select @@global|【session】.系统变量名;

4、为某个系统变量赋值

set global|【session】 系统变量名=值

set @@global|【session】.系统变量名=值;

二、自定义变量

说明：变量由用户自定义，非系统提供

使用步骤：声明、赋值、使用（查看、比较、运算）

用户变量

作用域：针对当前会话有效，同于绘画变量的作用域

1、声明和赋初始值(三种方式)

SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

2、赋值（更新）用户变量的值

#方式一（与声明相同）：
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

#方式二：通过SELECT INTO
SELECT 字段 INTO @变量名 FROM 表;

3、使用：查看用户变量的值

SELECT @用户变量名;

案例：

#声明加赋值
SET @name='john';
SET @name=100;
SET @count=1;
#赋值
SELECT count(*) INTO @count FROM employees;
#查看
SELECT @count

局部变量

作用域：仅仅定义在他的begin end中

应用在Begin end中的第一句话

1、声明变量

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

2、赋值

#方式一：
SET 用户变量名=值;
SET 用户变量名:=值;
SELECT @用户变量名:=值;

#方式二：通过SELECT INTO
SELECT 字段 INTO 变量名 FROM 表;

3、使用

SELECT 局部变量名

存储过程和函数

存储过程和函数：类似于java中的方法

一、存储过程

含义：一组预先编译好的SQL语句集合，类似于批处理函数

好处：

• 提高代码重用性

• 简化操作

• 减少了编译次数并减少了和数据库服务器的连接次数，提高了效率

创建存储过程

语法:

create procedure 存储过程名(参数列表)
begin
	存储过程体(其实就是一组合法的SQL语句)
end

注意：

1、参数列表包含三部分：

参数模式	参数名	参数类型

举例：IN stuname varchar(20)

参数模型：

IN:该参数可以作为输入，也就是该参数需要调用者传入值
OUT:该参数可以作为输出，也就是参数作为返回值
INOUT:该参数既可以作为输入也可以作为输出，也就是该参数既需要传入值，也作为返回值

2、如果存储过程体中只有一句话，BEGIN END可以省略。存储过程中每条SQL语句结尾必须加分号，存储过程结尾可以使用DELIMITER重新设置。

语法：

DELIMITER 结束标志
案例:
DELIMITER $

调用存储过程

语法：

CALL 存储过程名(实参列表);

删除存储过程

语法：

DROP PROCEDURE 存储过程名

查看存储过程

语法：

show create PROCEDURE 存储过程名

实例

1、空参列表

案例：插入到girls的admin表中五条记录

CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`) 
	VALUES('john1','0000'),('john2','0000'),('john3','0000'),('john4','0000'),('john5','0000');
END

CALL myp1();
SELECT * FROM admin;

2、创建带in模式参数的存储过程

案例1：创建存储过程实现根据女神名，查询对应的男神信息

DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT b.*
	FROM boys b 
	RIGHT JOIN beauty g 
	ON b.id = g.boyfriend_id
	WHERE g.`name`=beautyName;
END $

CALL myp2('王语嫣');

案例2：创建存储过程，判断用户是否登陆成功

#创建存储过程，判断用户是否登陆成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN password VARCHAR(20))
BEGIN
	DECLARE result VARCHAR(20) DEFAULT '失败'; #声明变量
	
	SELECT COUNT(*) INTO result 	#变量赋值
	FROM admin a
	WHERE a.username = username
	AND a.`password` = `password`;
	
	SELECT IF(result>0, '成功', '失败');	#使用变量
END $

CALL myp3('lyt','6666'); #成功
CALL myp3('lyt','666'); #失败

3、创建带OUT返回值的存储过程

案例1：根据女神名，返回对应的男神名和男神魅力值

DELIMITER $
CREATE PROCEDURE myp4(IN beatyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT b.boyName, b.userCP INTO boyName, userCP
	FROM beauty g
	LEFT OUTER JOIN boys b
	ON b.id = g.boyfriend_id
	WHERE g.`name` = beatyName;
END $

CALL myp4('王语嫣',@bName, @userCP);
SELECT @bName, @userCP;

4、创建带INOUT模式参数的存储过程

案例1：传入a和b两个值，最终a和都翻倍并返回

DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

SET @m=10;
SET @n=20;
CALL myp6(@m, @n);
SELECT @m,@n;

二、函数

含义：一组预先编译好的SQL语句集合，类似于批处理函数

好处：

• 提高代码重用性

• 简化操作

• 减少了编译次数并减少了和数据库服务器的连接次数，提高了效率

与存储过程的区别：

• 存储过程：可以有0个结果，也可以有多个结果返回，适合做批量插入，批量更新

• 函数：有且仅有一个返回值，适合做处理数据后返回一个结果

创建函数

语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体（其中最后包含RETURN语句）
END

注意：

1、参数列表包含两部分：参数名 参数类型

2、函数体必须包含RETURN语句，否则报错。RETURN语句可以不放在最后，但是没效果不建议

3、如果函数体只有一条语句，可以省略BEGIN END

4、使用DELIMITER设置结束标记符

调用函数

语法：

SELECT 函数名(参数列表)

查看函数

语法：

show create function 函数名

删除函数

语法：

DROP function 函数名

实例

1、无参有返回函数

案例：返回公司员工个数

DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;	#声明变量
	SELECT count(*) INTO c	#为变量赋值
	FROM employees;
	RETURN c;			#使用变量
END $

SELECT myf1();

2、有函数的函数

案例：根据员工名返回工资

DELIMITER $
CREATE FUNCTION myf2(lname VARCHAR(20)) RETURNS INT
BEGIN
	DECLARE s INT DEFAULT 0;
	SELECT salary INTO s
	FROM employees e
	WHERE e.last_name = lname;
	
	RETURN s;
END $

SELECT myf2('Kochhar')

流程控制结构

分类：

• 顺序结构：程序从上往下依次执行
• 分支结构：程序从两条或者多条路径中选择一条去执行
• 循环结构：程序在满足一定条件的基础上，重复执行一段代码

一、分支函数

1、if函数

功能：实现简单的双分支

语法：

if(判断条件,表达式1,表达式2)

执行顺序：如果判断条件成立则执行表达式1，否则执行表达式2

2、case结构

情况1：类似于java中的switch语句，一般用于等值判断

语法：

CASE 变量|表达式|字段
WHEN 要判断的值1 THEN 返回值1（或语句1;)
WHEN 要判断的值2 THEN 返回值2（或语句2;)
...
ELSE 返回值n（或语句3;)
END

情况2：类似于java中的多重IF语句，一般用于区间判断

语法：

CASE 
WHEN 条件判断1 THEN 返回值1（或语句1;)
WHEN 条件判断2 THEN 返回值2（或语句2;)
...
ELSE 返回值n（或语句n;)
END

特点：

• 可以作为表达式，嵌套在其他语句中使用。此时可以放在BEGIN END（存储过程或函数）中，或者BEGIN END外
• 可以作为独立的语句去使用，只能放在BEGIN END中
• 当某个WHEN中条件判断或值成立，则只执行这一条（自动break）。当都不成立时，执行else。
• ELSE可以省略。当WHEN条件都不满足，且没有ELSE时，返回NULL

案例：创建一个存储过程，根据输入成绩显示等级

CREATE PROCEDURE test_case(IN g INT)
BEGIN
	CASE
	WHEN g<60 THEN SELECT 'D';
	WHEN g<70 THEN SELECT 'C';
	WHEN g<80 THEN SELECT 'B';
	ELSE SELECT 'A';
	END CASE;
END $

CALL test_case(88);

3、IF…ELSE语句

功能：实现多重分支

语法：

IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
...
ELSE 语句n;
END IF;

应用场景：只能放在BEGIN END（存储过程或函数）中

案例：创建一个函数，根据输入成绩显示等级

CREATE FUNCTION test_if(g INT) RETURNS CHAR
BEGIN
	IF g<=100 AND g >=90 THEN RETURN 'A';
	ELSEIF g>=80 THEN RETURN 'B';
	ELSEIF g>=60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END

CALL test_case(60)

二、循环函数

分类：while，loop，repeat

循环控制：

• iterate类似于continue：结束本次循环，继续下一次
• leave类似于break：跳出循环

1、while

语法：

【标签:】while 循环条件 do
	循环体
end while【标签】;

没有添加控制语句

案例：批量插入，根据次数插入admin表多条语句

DELIMITER $
CREATE PROCEDURE test_while1(IN cnt INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i < CNT DO
		INSERT INTO admin(username, `password`) 
		VALUES(CONCAT('rose',i), '666');
		SET i=i+1;
	END WHILE;
END $

CALL test_while1(10);
SELECT * FROM admin;

添加leave控制语句

案例：批量插入，根据次数插入admin表多条语句，最多插入10条

DELIMITER $
CREATE PROCEDURE test_while2(IN cnt INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i < CNT DO
		INSERT INTO admin(username, `password`) VALUES(CONCAT('xiaohua',i), '666');
		SET i=i+1;
		IF i>10 THEN LEAVE a;
		END IF;
	END WHILE a;
END $

CALL test_while2(15);
SELECT * FROM admin;

添加iterate控制语句

案例：批量插入，根据次数插入admin表多条语句，直插入偶数次

DELIMITER $
CREATE PROCEDURE test_while3(IN cnt INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i < CNT DO
		SET i=i+1;
		IF i%2=1 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username, `password`) VALUES(CONCAT('daniu',i), '666');
	END WHILE a;
END $

CALL test_while3(10);
SELECT * FROM admin;

2、loop

语法：

【标签:】loop 
	循环体
end loop【标签】;

可以用来模拟简单的死循环

3、repeat

语法：

【标签:】repeat
	循环体
until 结束循环的条件
end repeat【标签】;