变量、存储过程、函数、流程控制
一、变量
1. 概念
- 1）系统变量
- 全局变量
- 会话变量
- 2）自定义变量
- 用户变量
- 局部变量
2. 系统变量
① 简介
- 1）说明：变量由系统提供，不是用户定义，属于服务器层面
- 2）使用语法：
- ① 查看所有系统变量
show global|[session]（全局|会话） variables
- ② 查看满足条件的部分系统变量
show global|[session] variables like '%...%'
- ③ 查看指定的某个系统变量
select @@global|[session].系统变量名
- ④ 为某个系统变量赋值
set global|[session] 系统变量名 = 值set @@global|[session].系统变量名 = 值
- ① 查看所有系统变量
- 3）注意：
- 如果是全局级别，则需要加global。如果是会话级别，则需要加session。如果不写，默认session
② 使用
- 1）全局变量
- 作用域：服务器每次启动将所有全局变量赋初始值，针对于所有会话（连接）有效，但不能跨重启
- 使用实例：mysql8.0以上版本时区问题：
set global time_zone = '+8:00'
- 2）会话变量
- 作用域：仅仅针对于当前的会话（连接）有效
3. 自定义变量
① 简介
- 变量是由用户自定义的，不是由系统的
② 使用
- 1）用户变量
- 作用域：针对于当前会话（连接）有效。同与会话变量的作用域
- 声明并初始化：
set @用户变量名 = 值set @用户变量名: = 值select @用户变量名:=值
- 赋值
set @用户变量名 = 值set @用户变量名: = 值select @用户变量名:=值select 字段 into 变量名 from 表
- 查看用户变量的值
select @用户变量名
- 2）局部变量
- 作用域：仅仅在定义它的begin end中有效
- 应用在begin end中的第一句话！！！
- 声明
declare 变量名 类型declare 变量名 类型 default 值
- 赋值
set @用户变量名 = 值set @用户变量名: = 值select @用户变量名:=值select 字段 into 变量名 from 表
- 查看
select 局部变量名
③ 用户变量和局部变量对比
| 作用域 | 定义和使用位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话的任何地方 | 必须加@符号，不用限定类型 |
| 局部变量 | BEGIN END中 | 只能在BEGIN END，且第一句话中 | 一般不用加@符号，需要限定类型 |
二、存储过程
1. 简介
- 1）存储过程和函数：类似与java中的方法
- 好处：提高代码重用性、简化操作
- 2）存储过程含义：一组预先编译好的sql语句的集合，理解成批处理语句
- 提高代码重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
2. 语法
① 创建
-
create procedure 存储过程名(参数列表) BEGIN 方法体（一组sql语句） END -
注意：
- 1）参数列表包含三部分：参数模式、参数名、参数类型
- 参数模式
- in：该参数可以作为输入，也就是说该参数需要调用方法需要传入值
- out：该参数可以作为输出，也就是说该参数可以作为返回值
- inout：该参数既可以作为输入，又可以作为输出
- 参数模式
- 2）如果存储过程体只有一句话，BEGIN END可以省略
- 3）存储过程体中的每条SQL语句结尾必须加分号
- 4）存储过程的结尾可以使用DELIMITER重新设置
- 语法：
DELIMITER 结束标记
- 语法：
- 1）参数列表包含三部分：参数模式、参数名、参数类型
② 调用
-
call 存储过程名(实参列表);
③ 使用案例
-
1）存储过程的基本使用
-
#案例：插入到admin表中5条记录 use girls; select * from admin; delimiter $ create procedure myp1() begin insert into admin(username, `password`) values('zhangsan', '0000'),('lisi', '0000'); end $ #调用 call myp1();
-
-
2）创建带in的存储过程
-
#案例1：创建存储过程，根据女神名，查询对应的男神信息 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; end $ #调用 call myp2('金星'); #案例2:创建存储过程，用户是否登录成功 delimiter $ create procedure myp3(in username varchar(20), in password varchar(20)) begin declare result int default 0; select count(*) into result #给局部变量赋值 from admin where admin.username = username and admin.password = password; select if(result>0, '成功','失败');#使用局部变量 end $ #调用 call myp3('john', '6666');
-
-
3）创建带out的存储过程
-
#案例1:根据女神名，返回对应的男神名 delimiter $ create procedure myp4(in beautyName varchar(20), out boyName varchar(20)) begin select bo.boyName into boyName from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyName; end $ #调用 set @bname=''; call myp4('金星', @bname); select @bname; #案例2:根据女神名，返回对应的男神名和男神魅力值 delimiter $ create procedure myp5(in beautyName varchar(20), out boyName varchar(20), out userCP int) begin select bo.boyName,bo.userCP into boyName, userCP from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyName; end $ #调用 call myp5('苍老师', @bname, @usercp); select @bname,@usercp;
-
-
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 @m = 10; set @n = 20; call myp6(@m,@n); select @m,@n;
-
④ 删除
drop procedure myp1;
⑤ 查看存储过程
show create procedure myp2;
三、函数
1. 简介
- 函数与存储过程的不同：
- 函数：有且仅有一个返回值，适合做数据处理后返回一个结果
- 存储过程：可以有0个返回值，也可以有多个返回值，适合做批量插入、更新
2. 语法
① 创建
#mysql8.0之前的语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
#mysql8.0之后的语法
create function 函数名(参数列表) returns 参数类型
reads sql data begin
函数体
end
#mysql8.0之后的语法二
set global log_bin_trust_function_creators=true;
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
- 1）参数列表：参数名 参数类型
- 2）函数体：肯定有return语句，如果没有报错
- 3）函数体中只有一句话时，可以省略
- 4）delimiter语句设置结束标记
② 调用
select 函数名(参数列表)：执行函数体语句，并且显示返回值
③ 查看
show create function 函数名
④ 删除
drop function 函数名
⑤案例
#1.无参有返回
#案例：返回公司的员工个数
use myemployees;
set global log_bin_trust_function_creators=true;
delimiter $
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 myf2(empName varchar(20)) returns double
begin
set @sal = 0; #定义用户变量
select salary into @sal
from employees e
where e.last_name = empName;
return @sal;
end $
select myf2('Ernst');
#案例2：根据部门名，返回该部门的平均工资
delimiter $
create function myf3(depName varchar(20)) returns double
begin
declare av_salary double default 0;
select avg(salary) into av_salary
from employees e
inner join departments d on e.department_id = d.department_id
where d.department_name = depName;
return av_salary;
end $
select myf3('IT');
四、流程控制结构
1.分支结构
① if函数
- 语法：
if(表达式1，表达式2，表达式3) - 执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
② case结构
-
1）情况1：类似于switch语句，一般用于实现等值判断
-
case 变量|表达式|字段 when 要判断的值 then 返回的值1或语句1; when要判断的值 then 返回的值2或语句2; ... else 返回的值n或语句n; end case;
-
-
2）情况2：类似于多重if语句，一般用于实现区间判断
-
case when 要判断的条件1 then 返回的值1 或语句1; when 要判断的条件1 then 返回的值2 或语句2; ... else 返回的值n 或语句n; end case;
-
-
3）特点：
- 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
- 可以作为独立的语句去使用，只能放在begin end语句中
- 如果when中的值或条件成立，则执行对应的then后面的语句，并且结束case。如果都不满足，则执行else语句
- else可以省略，如果else省略，并且所有条件都不满足，则返回null
-
4）案例
-
#案例：创建存储过程，根据传入成绩，显示等级 delimiter $ create procedure myp1(in score int) begin case when score >= 90 and score <=100 then select 'A'; when score >= 80 then select 'B'; when score >= 60 then select 'C'; else select 'D'; end case; end $ call myp1(95);
-
③ if分支
-
1）语法
-
if 条件1 then 语句1； elseif 条件2 then 语句2； ... 【else 语句n；】 end if； -
只能应用在begin end中
-
2. 循环结构
-
1）分类
- while、loop、repeat
-
2）循环控制
- iterate类似于continue，结束本次循环，继续下一次
- leave类似于break，跳出，结束当前循环
-
3）语法
-
while
-
【标签:】while 循环条件 do 循环体; end while 【标签】；
-
-
loop
-
【标签：】loop 循环体; end loop【标签】; -
可以用来模拟死循环
-
-
repeat
-
【标签：】repeat 循环体; until 结束循环的条件 end repeat 【标签】;
-
-
-
4）案例
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
