变量,存储过程和函数,流程控制函数
十、变量
- 系统变量:全局变量,会话变量
- 自定义变量:用户变量,局部变量
1. 系统变量
- 定义:变量由系统提供的,不是用户定义,属于服务器层面。
- 使用语法:
- 查看所有的系统变量
SHOW [ GLOBAL | SESSION] VARIABLES;
默认查看会话 - 查看满足条件的部分系统变量
SHOW GLOBAL | [SESSION] variables like '%char%';
- 查看指定的某个系统变量的值
select @@ [global| [session].] 系统变量名;
- 为某个系统变量赋值
方式一:set global | session 系统变量名 = 值;
方式二:set @@global | session.系统变量名 = 值;
- 查看所有的系统变量
1.1 全局变量
- 必须有super权限才能为系统变量赋值。
- 作用域:服务器每次启动都将为所有的全局变量赋初始值,只针对当前所有会话有效,重启服务器后失效(除非在配置文件修改)。
1.2 会话变量
- 作用域:仅仅针对当前会话(连接)有效
- 查看所有会话变量
SHOW SESSION VARIABLE;
2. 自定义变量
- 定义:变量是用户自定义的,不是由系统的。
- 使用步骤:声明,赋值,使用(查看,比较,运算等)
2.1 用户变量
- 作用域:针对于当前会话连接有效,同于会话变量的作用域。应用在任何地方,也就是begin end里面或者begin end外面。
- 使用
# 1.声明并初始化 SET @用户变量名=值;或 SET @用户变量名:=值;或 SELECT @用户变量名:=值; # 2. 赋值(更新用户变量的值) 方式一:通过SET或SELECT,步骤和声明相同。 方式二:SELECT 字段 INTO 变量名 FROM 表; SELECT COUNT(*) INTO @count FROM employees #赋值 # 3.使用(查看) SELECT @count;#查看
2.2 局部变量
- 作用域:仅仅在定义它的begin end中有效,必须begin end的第一句话。
- 使用
# 1.declare DECLARE variablename 类型; DECLARE variablename 类型 DEFAULT 值 # 2. 赋值(更新用户变量的值) 方式一:通过SET或SELECT, SET 用户变量名=值;或 # 注意两个set不加@,select需要加@ SET 用户变量名:=值;或 SELECT @用户变量名:=值; 方式二:SELECT 字段 INTO 变量名 FROM 表; SELECT COUNT(*) INTO @count FROM employees #赋值 # 3.使用(查看) SELECT count;#查看 不用加@
3. 对比用户变量和局部变量
十一、存储过程和函数
- what: 存储过程和函数类似于Java中的方法,不能修改语句,要么就直接删除,重新写。
- 好处
- 提高代码的重用性。
- 简化操作。
- 减少了编译次数并且减少了和数据库数据库的连接次数,提供了效率。
1. 存储过程
- 含义:一组预先编译好的sql语句的集合,理解成批处理语句。
- 语法:创建,调用
1.1 创建语法
- 语法
CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法的SQL语句) END
- 注意:
- 参数列表包含三部分:参数模式,参数名,参数类型。举例:
IN stuname VARCHAR(20)
- 参数模式:IN可以省略
IN:该参数可以作为输入,也就是该参数需要调用方传入值。
OUT:该参数可以作为输出,也就是该参数可以作为返回值。
INOUT:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值。 - 如果存储过程体仅仅只有一句话,BEGIN END可以省略
- 存储过程体中的每条SQL语句的结尾要求必须加分号。
- 存储过程的结尾可以用DELIMITER重新设置。语法:
DELIMITER 结束标记 例如:DELIMITER $
- 参数列表包含三部分:参数模式,参数名,参数类型。举例:
1.2 调用语法
CALL 存储过程名(实际参数列表);
1.3 空参的存储过程
#案例:插入admin表中五条记录
SELECT * FROM admin;
# 创建过程
DELIMITER $
CREATE PROCEDURE myp1 ()
BEGIN
INSERT INTO admin(username, `password`)
VALUES(`john1`, `0000`), (), (), (), ()#省略
END $
# 调用
CALL myp1()$
1.4 带in模式的存储过程
#案例:创建存储过程实现根据女神名,查询对应的男神信息
# 创建过程
DELIMITER $
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; # beautyName相当于形式参数
END $
# 调用
CALL myp2('liuyan')$
1.5 带out模式的存储过程
支持多个返回值。
1.6 带inout模式的存储过程
1.7 存储过程的删除
- 语法:
DROP PROCEDURE 存储过程名;
1.8 存储过程的查看
SHOW CREATE PROCEDURE myp2;
2. 函数
2.1 函数的介绍
- 含义:一组预先编译好的sql语句的集合,理解成批处理语句。
- 好处
- 提高代码的重用性。
- 简化操作。
- 减少了编译次数并且减少了和数据库数据库的连接次数,提供了效率。
- 函数和过程的区别
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入,批量更新。
函数:有且仅有一个返回,适合做处理数据后返回一个结果。
2.2 函数的创建和调用
2.2.1函数的创建
CREATE FUNCTION 函数名(函数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
1.参数列表 包含两部分:
参数名 参数类型
2.函数体:肯定会有return语句,如果没有会报错。
3.函数体中仅有一句话,则可以省略begin end
4.使用delimiter语句设置结束标记。
2.2.2函数的调用
二、调用语法
SELECT 函数名(参数列表)
后续版本sql,可能不需要加上$.
2.3 函数的查看和删除
查看函数:SHOW CREATE FUNCTION myf3;
删除函数:DROP FUNCTION myf3;
十二、流程控制结构
1. 流程控制结构的介绍
顺序结构:程序从上到下执行
分支结构:从两条或者多条中选择一条去执行
循环结构:在满足一定条件的基础上,重复执行一段代码
2. 分支结构:if函数
- 功能:实现简单的双分支
- 语法:
SELECT IF(表达式1, 表示式2,表达式3)
表达式1成立,执行表达式2,否则执行3. - 应用:可以放在任何地方
3. 分支结构:case结构
- 情况1: 类似java的switch结构,一般用于实现的等值判断
语法:CASE 变量 | 表达式 | 字段 WHEN 要判断的值 THEN 返回值1或语句1 WHEN 要判断的值 THEN 返回值2或语句2 。。。 ELSE 要返回的值n END CASE; # 记得加上case
- 情况2:类似于java中的多重IF语句,一般用于实现区间判断
语法:CASE WHEN 要判断的值 THEN 返回值1或语句1 WHEN 要判断的值 THEN 返回值2或语句2 。。。 ELSE 要返回的值n END CASE # 记得加上case
- 特点:
- 可以作为表达式,嵌套在其他语句中使用,可以放在
任何地方
,BEGIN END中或BEGIN END的外面
;
可以作为独立的语句
去使用,只能放在BEGIN END
中。 ELSE
可以省略,如果省略了,并且所有WHEN条件不满足,则返回NULL
。
- 可以作为表达式,嵌套在其他语句中使用,可以放在
4. 分支结构:if结构
- 功能:实现多重分支;只能用在BEGIN END中。
- 语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2
。。。
【else 语句n】
5.循环结构
- 分类:while,loop,repeat
- 循环控制:
iterate类似于continue,结束本次循环,继续下一次
leave 类似于break,跳出,结束当前所在的循环,没有下一次。 - 语法:
5.1 while loop
# 1. while
【标签:】while 循环条件 do
循环体
end while 【标签】;
# 案例: 批量插入,根据次数插入到admin表中多条记录
CREATE PROCEDURE pro_while1(IN insertCOunt INT)
BEGIN
DECALRE i INT DEFAULT 1;
WHILE i<insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('ROSE',i), '666');
SET i=i+1;
END WHILE
END $
CALL pro_while1(100)$
# 2. loop
【标签:】loop
循环体
end loop 【标签】;
用于描述简单的死循环
# 3. repeat
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
5.2 leave(类break)的使用实例
5.3 Iterate(类continues)的使用实例
5.4 循环结构总结比较
案例示范: