MySQL学习笔记(8)——变量、存储过程和函数

一、变量

系统变量	全局变量
	会话变量
自定义变量	用户变量
	局部变量

一、系统变量：

有系统提供，不是用户定义，属于服务器层面。

使用语法：

1.查看所有的系统变量

SHOW VARIABLES;#会话变量
SHOW GLOBAL VARIABLES;#系统变量
SHOW SESSION VARIABLES;#会话变量

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

SHOW VARIABLES LIKE '%char%';

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

SELECT @@系统变量名;#会话变量
SELECT @@global.系统变量名;#系统变量
SELECT @@session.系统变量名;#会话变量

4.为某个系统变量赋值

#方式一：
SET 系统变量=值;
#方式二：
SET @@系统变量=值;

全局变量（整个服务器，对任何客户端都有效）

作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有会话（连接）有效，但不能跨服务器

1.查看所有全局变量

2.查看部分全局变量

3.查看指定的全局变量的值

SELECT @@global.autocommit;
select @@tx_isolation;

4.为某个指定的全局变量赋值

SET @@global.autocommit=0;

会话变量（针对客户端一次连接）

作用域：仅仅针对于当前会话（连接）有效

1.查看所有会话变量

2.查看部分会话变量

3.查看指定会话变量的值

4.为某个会话变量赋值

set session tx_isolation   8.0

二、自定义变量

变量是用户自定义的，不是系统的

使用步骤：

声明

赋值

使用（查看、比较、运算）

1.用户变量

作用域：针对于当前会话（连接）有效，同于会话变量的作用域

应用于任何地方，也就是begin end里面或外面

①声明并初始化：

赋值操作符 =或:=
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;

②赋值

方式一同声明

方式二：用select into

SELECT 字段 INTO 变量名#查询出的字段为一个值而不是一组值
FROM 表;

③使用：查看用户变量的值

SELECT @用户变量名;

#案例：
#声明并初始化
SET @count=1;
#赋值
SELECT COUNT(*) INTO @count
FROM employees;
#使用
SELECT @count;

2.局部变量

作用域：仅仅在定义它的begin end中有效

应用于begin end中的第一句话

①声明

declare 变量名 类型；

declare 变量名 类型 default 值；值与类型一致或兼容

②赋值set 或select into

方式一：通过set 或select

set 局部变量=值；

set 局部变量：=值；

select @局部变量：=值

方式二：通过select into

select 字段 into 局部变量名

from 表；

③使用

select 局部变量名

对比用户变量和局部变量：

                        作用域                        定义和使用的位置                        语法

用户变量        当前会话                        会话中的任何地方                必须加@符号，不用限定类型

局部变量        begin end中                只能在begin end中，且为第一句话                一般不用加@除非，需要限定类型

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

好处：

1.提高代码的重用性

2.简化操作

含义：一组预先编译好的sql语句的集合，理解成批处理语句

2.简化操作

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

语法：

一。创建语法

create procedure 存储过程名(参数列表)

begin

        存数过程体（一组合法有效的sql语句）

end

注意

1.参数列表包含三部分

参数模式 参数名 参数类型

in stuname varchar（20）

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值

out：该参数可以作为输出，也就是该参数可以作为返回值

inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.如果存储过程体仅仅只有一句话，begin end可以省略

3.存储过程体的每条sql语句的结尾要求必须加分号。

存储过程的结尾可以使用delimiter重新设置

语法：

delimiter 结束标记

二、调用语法

call 存储过程名（实参列表）；

空参列表

#插入到admin表中五条记录
#创建
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`)
    VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');
END $
#调用
CALL myp1()$

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

#根据女生名，查询对应男生信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
    WHERE b.name=beautyName;
END $
 
CALL myp2('女生名')$#变量或常数

3.创建带out模式的存储过程

#根据女生名返回对应男生名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName
    FROM boys bo
    INNER JOIN beauty b
    ON bo.id=b.boyfriend_id
    WHERE b.name=beautyName
END $
 
SET @bName#可以不写
CALL myp5('小昭',@bName)$
SELECT @bName$
#根据女生名，返回对应的男生名和数值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
    SELECT bo.boyName,bo.useCP INTO boyName,userCP
    FROM beauty b
    INNER JOIN boys bo
    ON b.boyfriend_id=bo.id
    WHERE b,brautyName=beautyName
END $
 
CALL myp6('小昭',@bName,@uCP)$
SELECT @bName,@uCP$

4.创建带inout模式参数的存储过程

调用：

定义变量set

传入call

输出select

#传入a和b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
    SET a=*2;
    SET b=b*2;
END $
 
SET @m=10$
SET @m=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

三、函数

区别：

存储过程可以有0个返回或多个返回，函适合做批量插入、批量更新，增删改

函数只能有且仅有1个返回，适合处理数据后返回一个结果，查询

创建语法：

create function 函数名(参数列表) returns 返回类型

begin

        函数体

end

注意：参数列表 包含两部分：

参数名 参数类型

函数体：肯定会有return语句，没有会报错

如果return没有放在函数体的最后也不报错，但不建议

函数体中只有一句话可以省略beginend

使用delimiter语句设置结束标记

delimiter $;

二、调用语法

select 函数名(参数列表)

执行函数中所有语句，把返回值显示出来

案例演示：

1.无参有返回

#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;#定义变量
	SELECT COUNT(*) INTO c;#赋值
	FROM employees;
	RETURN c
END $

SELECT myf1()$

2.有参有返回

#案例：根据员工名，返回他的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量
	SELECT salary INTO @sal#赋值
	FROM employees
	WHERE last_name=empName;
	RETURN @sal;
END$
SELECT myf2('l_ing') $
#案例2：根据部门名，返回该部门的平均部门工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE avg_sal DOUBLE
	SELECT AVG(salary) INTO avg_sal
	FROM employees
	GROUP BY department_id;
	WHERE department_id=(
		SELECT department_id
		FROM departments
		WHERE department_name=depName
	);
	RETURN avg_sal;
END $;

SELECT myf3('Adm')$

查看函数

SHOW CREATE FUNCTION myf3;

删除函数

DROP FUNCTION myf3;

#案例：创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $
SELECT test1(1,2) $

四、流程控制结构

顺序结构：程序从上往下依次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

一、分支结构

1.if函数

功能：能够实现简单的双分支

语法：

if(表达式1，表达式2，表达式3)

执行顺序：如果表达式1成立，则返回表达式2的值。否则返回表达式3的值

应用：任何地方

2.case结构

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

语法：case 变量|表达式|字段

when 要判断的值 then 返回的值1或语句1；

when 要判断的值 then 返回的值2或语句2；

。。。

else 要返回的值n

end case;

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

语法：case

when 要判断的条件1 then 返回的值1或语句1；

when 要判断的条件2 then 返回的值2或语句2；

。。。

else 要返回的值n

end case;

特点：

①可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，beginend中或外（值）

可以作为独立的语句去使用，只能放在beginend中（语句）

②如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值，

③如果else省略，并且所有when条件否不满足，则返回null

3.if结构

功能：实现多重分支

语法：

if 条件1 then 语句1

elseif 条件2 then 语句2

...

【else 语句n】

end if

只能应用在begin end中

二、循环结构

分类：

while loop repeat

循环控制：iterate 类似于continue，结束本次循环，继续下一次

leave 类似于break ，跳出，结束当前所在循环

1.while

语法：

【标签】while 循环条件 do

循环体；

end while【标签】

有循环控制写标签

while（循环条件）｛

循环体

｝

先判断再执行

2.loop

【标签：】loop

        循环体；

end loop【标签】；

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

中途跳出leave

3.repeat

语法：

【标签：】repeat

循环体

until 结束循环的条件

end repeat【标签】；

先执行后循环 至少执行一次

3.添加iterate语句