MySQL学习总结（十）变量/系统变量全局变量会话变量/自定义变量用户变量局部变量/存储过程/参数模式/函数/函数和存储过程的区别/流程控制结构/分支结构IF结构CASE结构/循环结构

2021/5/13 2:29:43

编程Tag： 变量 select Set 存储 Create 结构 语句 end begin

本文主要是介绍MySQL学习总结（十）变量/系统变量全局变量会话变量/自定义变量用户变量局部变量/存储过程/参数模式/函数/函数和存储过程的区别/流程控制结构/分支结构IF结构CASE结构/循环结构，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、变量

（一）系统变量

说明：系统变量由系统提供，不是用户定义，属于服务器层面。系统变量又分为全局变量和会话变量。

1.全局变量

必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对所有连接（会话）有效。

（1）查看所有的系统变量

语法：

SHOW GLOBAL VARIABLES;

（2）查看满足条件的系统变量

语法：

SHOW GLOBAL VARIABLES 条件;

案例：

SHOW GLOBAL VARIABLES LIKE '%character%';

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

语法：

SELECT @@global.系统变量名;

案例：

SELECT @@global.autocommit;

在这里插入图片描述

（4）为某个系统变量赋值

语法：

方式一：

SET GLOBAL 系统变量名=值;

方式二：

SET @@global.系统变量名=值;

案例：

SET GLOBAL autocommit=0;
SET @@global.autocommit=0;

2.会话变量

仅仅针对于当前会话（连接）有效。

（1）查看所有的系统变量

语法：

SHOW 【SESSION】 VARIABLES;

（2）查看满足条件的系统变量

语法：

SHOW 【SESSION】 VARIABLES 条件;

案例：

SHOW 【SESSION】 VARIABLES LIKE '%character%';

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

语法：

SELECT @@【session.】系统变量名;

案例：

SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

（4）为某个系统变量赋值

语法：

方式一：

SET 【SESSION】 系统变量名=值;

方式二：

SET @@【session.】系统变量名=值;

案例：

SET @@session.autocommit=0;
SET @@autocommit=0;
SET SESSION autocommit=0;
SET autocommit=0;

3.小结

  • 如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果都没加，默认是session。
  • 全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，修改全局变量针对于所有的会话（连接）有效，但不能跨重启（重启服务器全局变量恢复为默认值）。
  • 会话变量作用域：仅仅针对于当前会话（连接）有效。

（二）自定义变量

说明：自定义变量是用户自定义的，不是由系统定义的。

1.用户变量

（1）作用域

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

（2）应用场景

应用在任何地方，也就是BEGIN END（后面讲）里面或BEGIN END外面

（3）声明并初始化

三种

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

（4）赋值

方式一：与声明并初始化一样

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

方式二：通过SELECT INTO

SELECT 字段 INTO 变量名 
FROM 表;

（5）使用

SELECT @用户变量名;

（6）案例演示

案例1：

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

在这里插入图片描述
案例2：

SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;

在这里插入图片描述

2.局部变量

（1）作用域

仅仅在定义该局部变量的BEGIN END中有效

（2）应用场景

必须应用在BEGIN END中的第一句话

（3）声明并初始化

两种

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

（4）赋值

方式一：通过SET或SELECT（三种）

SET 局部变量名=值;
SET 局部变量名:=值;
SELECT @局部变量名:=值;

方式二：通过SELECT INTO

SELECT 字段 INTO 局部变量名 
FROM 表;

（5）使用

SELECT 局部变量名;

3.小结

作用域定义和使用的位置语法
用户变量当前会话会话中的任何地方必须加@符号，不用限定类型
局部变量BEGIN END中只能在BEGIN END中，且为第一句话一般不用加@符号，需要限定类型

二、存储过程

（一）含义

类似于Java中的方法，事先经过编译并存储在数据库中的一段SQL语句的集合。

（二）优点

  1. 提高代码重用性
  2. 简化应用开发人员的很多工作
  3. 减少数据在数据库和应用服务器之间的传输
  4. 减少了编译次数并且减少了和数据库服务器的连接次数
  5. 提高了数据处理的效率

（三）创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体 #一组合法的SQL语句
END

BEGIN END就类似于Java中方法体的两个大括号

注意：

  1. 参数列表包含三部分：参数模式、参数名、参数类型，例如IN stuname VARCHAR(20)
  2. 参数模式：IN、OUT、INOUT
  3. 如果存储过程体仅仅只有一句话，BEGIN END可以省略
  4. 存储过程体中的每条SQL语句的结尾要求必须添加分号，而整个存储过程的结尾要使用DELIMITER关键字重新设置，并且要写在创建存储过程的语句之前，例如DELIMITER $，那么整个存储过程的最后要加一个$，并且之后的结束标记都要用它

参数模式：

  • IN：该参数可以作为输入，也就是该参数是调用方传入的值
  • OUT：该参数可以作为输出，也就是该参数可以作为返回值
  • INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

（四）调用语法

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

（五）案例演示

1.空参的存储过程

案例：假设已有admin表，插入五条记录到admin表中
在这里插入图片描述

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
	INSERT INTO admin(username,pwd)
	VALUES('john','0000'),('mike','0100'),('lily','1234'),('tom','6666'),('rose','8888');
END $

CALL myp1()$
SELECT * FROM admin$

在这里插入图片描述

2.带IN模式参数的存储过程

案例1：假设有boy表和girl表，创建存储过程实现根据女生名，查询对应的男朋友信息
在这里插入图片描述在这里插入图片描述

DELIMITER $
CREATE PROCEDURE myp2(IN girlname VARCHAR(20))
BEGIN
	SELECT b.*
	FROM boy b
	RIGHT JOIN girl g ON b.id=g.boyfriend_id#因为有的女生没有男朋友，因此用外连接，女生是主表
	WHERE g.gname=girlname;
END $

CALL myp2('小红')$

在这里插入图片描述
案例2：假设有如下admin表，创建存储过程，实现检验用户是否登录成功
在这里插入图片描述

DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN pwd VARCHAR(4))
BEGIN
	DECLARE result INT DEFAULT 0;
	SELECT COUNT(*) INTO result
	FROM admin
	WHERE admin.username=username
	AND admin.pwd=pwd;
	SELECT IF(result>0,'成功','失败') 登录;
END $

CALL myp3('mike','0100')$

在这里插入图片描述

3.带OUT模式参数的存储过程

案例1：根据女生名，返回对应的男生名
在这里插入图片描述在这里插入图片描述

DELIMITER $
CREATE PROCEDURE myp4(IN girlname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
	SELECT b.bname INTO boyname
	FROM boy b
	RIGHT JOIN girl g ON b.id=g.boyfriend_id#因为有的女生没有男朋友，因此用外连接，女生是主表
	WHERE g.gname=girlname;
END $

SET @bname$#自定义一个用户变量，用来接收返回值，也可以省略这一步

CALL myp4('小玉',@bname)$

SELECT @bname$

在这里插入图片描述
案例2：根据女生名，返回对应的男朋友名和男朋友的年龄

DELIMITER $
CREATE PROCEDURE myp5(IN girlname VARCHAR(20),OUT boyname VARCHAR(20),OUT age INT)
BEGIN
	SELECT b.bname,b.age INTO boyname,age
	FROM boy b
	RIGHT JOIN girl g ON b.id=g.boyfriend_id#因为有的女生没有男朋友，因此用外连接，女生是主表
	WHERE g.gname=girlname;
END $

CALL myp5('小敏',@bname,@age)$

SELECT @bname,@age$

在这里插入图片描述

4.带INOUT模式参数的存储过程

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

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

SET @a=1$
SET @b=2$
CALL myp6(@a,@b)$
SELECT @a,@b$

在这里插入图片描述

（六）删除存储过程

语法：

DROP PROCEDURE 存储过程名;

案例：

DROP PROCEDURE myp1;

注意：一条删除存储过程的SQL语句只能删除一个存储过程，不能一次删除多个

（七）查看存储过程

语法：

SHOW CREATE PROCEDURE 存储过程名;

案例：

SHOW CREATE PROCEDURE myp1;

在这里插入图片描述

练习题1：创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回

DELIMITER $
CREATE PROCEDURE myp7(IN date1 DATE,OUT str VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(date1,'%y年%m月%d日') INTO str;
END $
CALL myp7(NOW(),@str)$
SELECT @str$

在这里插入图片描述

练习题2：创建存储过程实现传入女生名称，返回：“女生名 and 男生名” 格式的字符串

DELIMITER $
CREATE PROCEDURE myp8(IN girlname VARCHAR(20),OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(g.gname,' and ',IFNULL(b.bname,'null')) INTO str
	FROM girl g
	LEFT JOIN boy b ON b.id=g.boyfriend_id
	WHERE g.gname=girlname;
END $

CALL myp8('小婉',@str)$
SELECT @str$

在这里插入图片描述

练习题3：创建存储过程，根据传入的起始索引和条目数，查询girl表的记录

DELIMITER $
CREATE PROCEDURE myp9(IN startIndex INT,IN size INT)
BEGIN
	SELECT * FROM girl
	LIMIT startIndex,size;
END $

CALL myp9(2,2)$

在这里插入图片描述

三、函数

（一）含义

类似于Java中的方法，事先经过编译并存储在数据库中的一段SQL语句的集合。

（二）优点

  1. 提高代码重用性
  2. 简化应用开发人员的很多工作
  3. 减少数据在数据库和应用服务器之间的传输
  4. 减少了编译次数并且减少了和数据库服务器的连接次数
  5. 提高了数据处理的效率

（三）函数和存储过程的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做数据处理后返回一个结果

（四）创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

注意：

  1. 参数列表包含两个部分：参数名、参数类型
  2. 函数体必须有return语句，如果没有会报错
    如果return语句没有放在函数体最后的位置也不会报错，但是不建议
  3. 函数体中如果只有一句话，也可以省略BEGIN END
  4. 同样需要使用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.有参有返回

案例1：根据员工名，返回他的工资

DELIMITER $
CREATE FUNCTION myf1(empname VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;
	SELECT salary INTO @sal
	FROM employees
	WHERE last_name=empname;
	RETURN @sal;
END $

SELECT myf1('kochhar')$

在这里插入图片描述

案例2：根据部门名，返回该部门的平均工资

DELIMITER $
CREATE FUNCTION myf2(depname VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE avg_sal DOUBLE DEFAULT 0;
	SELECT AVG(salary) INTO avg_sal
	FROM employees e
	JOIN departments d ON e.department_id=d.department_id 
	WHERE d.department_name=depname;
	RETURN avg_sal; 
END $

SELECT myf2('IT')$

在这里插入图片描述

案例3：创建函数，实现传入两个float值，返回两值之和

DELIMITER $
CREATE FUNCTION myf3(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT myf3(1.33,2.453)$

在这里插入图片描述

（七）查看函数

SHOW CREATE FUNCTION 函数名;

（八）删除函数

DROP FUNCTION 函数名;

四、流程控制结构

（一）分类：

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

（二）分支结构

1.IF函数

功能：实现简单的双分支，也就是IF-ELSE，可应用于任何地方（BEGIN END中或者BEGIN END外面）

语法：

IF(表达式1,表达式2,表达式3)

如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值。

案例：查询所有员工名，如果有奖金备注“有奖金”，没奖金备注“没奖金”

SELECT last_name,IF(commission_pct IS NOT NULL,'有奖金','没奖金') AS 备注
FROM employees;

在这里插入图片描述

2.CASE结构

（1）作为表达式

①情况一：类似于Java中switch-case的效果，一般用于实现等值判断

语法：

CASE 变量|表达式|字段
WHEN 要判断的值1 THEN 返回的值1
WHEN 要判断的值2 THEN 返回的值2
...
ELSE 要返回的值n
END

案例：查询员工的工资，要求：
部门号=30，新工资为1.1倍
部门号=40，新工资为1.2倍
部门号=50，新工资为1.3倍
其他部门，新工资为原工资

SELECT last_name,department_id,salary AS 原工资,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
②情况二：类似于Java中的多重if语句，一般用于实现区间判断

语法：

CASE 
WHEN 要判断的条件1 THEN 返回的值1
WHEN 要判断的条件2 THEN 返回的值2
...
ELSE 要返回的值n
END

案例：查询员工的工资级别，要求：
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别

SELECT last_name,salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

（2）作为独立的语句

①情况一：类似于Java中switch-case的效果，一般用于实现等值判断

语法：

CASE 变量|表达式|字段
WHEN 要判断的值1 THEN 语句1;
WHEN 要判断的值2 THEN 语句2;
...
ELSE 语句n;
END CASE;
②情况二：类似于Java中的多重if语句，一般用于实现区间判断

语法：

CASE 
WHEN 要判断的条件1 THEN 语句1;
WHEN 要判断的条件2 THEN 语句2;
...
ELSE 语句n;
END CASE;

案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A；80-89，显示B；60-79，显示C；否则，显示D

DELIMITER $
CREATE PROCEDURE myp10(IN score INT)
BEGIN	
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A';
	WHEN score>=80 AND score<=89 THEN SELECT 'B';
	WHEN score>=60 AND score<=79 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE; 
END $
CALL myp10(78)$

在这里插入图片描述

3.IF结构

功能：实现多重分支，只能应用于BEGIN END中
语法：

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

ELSE也可以省略，如果ELSE省略了，并且所有IF的条件都不满足，则返回NULL

案例：创建函数，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A；80-89，显示B；60-79，显示C；否则，显示D

DELIMITER $
CREATE FUNCTION myf4(score INT) RETURNS CHAR
BEGIN
	IF score>=90 AND score<=100 THEN RETURN 'A';
	ELSEIF score>=80 AND score<=89 THEN RETURN 'B';
	ELSEIF score>=70 AND score<=79 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF;
END $

SELECT myf4(87)$

在这里插入图片描述

4.小结

CASE结构中，如果WHEN中的值满足或者条件成立，则执行对应的THEN后面的语句，并且结束CASE；如果都不满足，则执行ELSE中的语句或值。ELSE也可以省略，如果ELSE省略了，并且所有WHEN的值或条件都不满足，则返回NULL。

分支结构应用场景
IF函数BEGIN END中或者BEGIN END外面
IF结构只能在BEGIN END中
CASE作为表达式BEGIN END中或者BEGIN END外面
CASE作为独立的语句只能在BEGIN END中

（三）循环结构

1.分类

  • WHILE
  • LOOP
  • REPEAT

2.循环控制语句的关键字

  • ITERATE：类似于Java中的continue，继续，结束本次循环，继续下一次循环
  • LEAVE：类似于Java中的break，跳出，结束当前所在的循环

3.WHILE循环

（1）语法

【标签:】WHILE 循环条件 DO
	循环体;
END WHILE 【标签】;

类似于Java中的：

while(循环条件){
	循环体;
}

（2）案例演示

①没有添加循环控制语句

案例：批量插入，根据次数插入到admin表中多条记录
在这里插入图片描述

DELIMITER $
CREATE PROCEDURE pro_while1(IN count1 INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=count1 DO
		INSERT INTO admin VALUES(CONCAT('mike',i),'1234');
		SET i=i+1;
	END WHILE;
END $

CALL pro_while1(5)$
SELECT * FROM admin$

在这里插入图片描述

②添加 ITERATE 语句

案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次

DELIMITER $
CREATE PROCEDURE pro_while3(IN count1 INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=count1 DO
		SET i=i+1;
		IF MOD(i,2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO admin VALUES(CONCAT('mike',i),'1234');
	END WHILE a;
END $

CALL pro_while3(30)$
SELECT * FROM admin$
/*
int i=0;
while(i<=count1){
	i++;
	if(i%2!=0){
		continue;
	}
	插入
}
*/

在这里插入图片描述

③添加 LEAVE 语句

案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

DELIMITER $
CREATE PROCEDURE pro_while2(IN count1 INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=count1 DO
		INSERT INTO admin VALUES(CONCAT('mike',i),'1234');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL pro_while2(25)$
SELECT * FROM admin$

在这里插入图片描述

4.LOOP循环

（1）语法

【标签:】LOOP
	循环体;
END LOOP 【标签】;

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

5.REPEAT循环

（1）语法

【标签:】REPEAT
	循环体;
UNTIL 结束循环的条件
END REPEAT 【标签】;

6.小结

循环结构特点位置
WHILE先判断后执行BEGIN END中
LOOP没有条件的死循环BEGIN END中
REPEAT先执行后判断BEGIN END中

练习题：已知表stringcontent，其中字段：
id 自增长
content VARCHAR(20)
向该表插入指定个数的，随机位数的由a-z按顺序组成的字符串

DELIMITER $
CREATE PROCEDURE randstr(IN count1 INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
	DECLARE startindex INT DEFAULT 1;
	DECLARE len INT DEFAULT 1;
	WHILE i<=count1 DO
		SET len=FLOOR(RAND()*(26-startindex+1)+1);#产生一个随机的整数，代表截取长度：1-（26-startindex+1）
		SET startindex=FLOOR(RAND()*26+1);#产生一个随机的整数，代表起始索引1-26
		INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startindex,len));
		SET i=i+1;
	END WHILE;
END $

CALL randstr(10)$
SELECT * FROM stringcontent$

在这里插入图片描述



这篇关于MySQL学习总结（十）变量/系统变量全局变量会话变量/自定义变量用户变量局部变量/存储过程/参数模式/函数/函数和存储过程的区别/流程控制结构/分支结构IF结构CASE结构/循环结构的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程