数据库学习笔记（8）——变量、存储过程和函数、流程控制语句

1.变量

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

1.1 系统变量

说明：变量由系统提供，不是用户定义，属于服务器层面
使用的语法：
1.查看所有的系统变量
show global|【session】 variables;
2.查看满足条件的部分系统变量
show global|【session】 variable like '%char%';3
3.查看指定的某个系统变量的值
select @@global|【session】系统变量名;
4.为某个系统变量赋值
方式一：
set global|【session】系统变量名 = 值;
方式二：
set @@global|【session】.系统变量名 = 值;

注意：如果是全局级别，则需要加global，如果是会话级别，加session，如果什么都不写，则默认是会话级别。

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

1.2 自定义变量

说明：变量是用户自定义的，不是由系统的
使用步骤：
声明
赋值

#1.用户变量
作用域：针对当前会话（连接）有效，同于会话变量的作用域
应用在任何地方，也就是begin end里面或begin end外面
#①声明并初始化
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#②赋值(更新用户变量的值)
方式一：通过set或select
	set @用户变量名=值;
	set @用户变量名:=值;
	select @用户变量名:=值;
方式二：通过select into
	select 字段 into 变量名
	from 表;
#③查看
select @用户变量名;

#2.局部变量
作用域：仅仅在定义它的begin end中有效
应用在begin end中的第一句话。

#①声明
declare 变量名 类型;
declare 变量名 类型 default 值;
#②赋值
方式一：通过set或select
	set 局部变量名=值;
	set 局部变量名:=值;
	select @局部变量名:=值;
方式二：通过select into
	select 字段 into 局部变量名
	from 表;
#③使用
select 局部变量名;

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

2.存储过程

存储过程和函数：类似于java中的方法
好处：提高代码的重用性，简化操作

2.1创建

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1.提高了代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数

# 创建
create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的Sql语句)
end

注意:

1.参数列表包含三部分
参数模式		参数名		参数类型
举例：
IN	stuname	varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条SQL语句结尾必须要求加分号。
存储过程的结尾可以使用delimiter重新设置
语法:
delimiter 结束标记

# 调用
call 存储过程名(实参列表);

#1.空参列表
#案例：插入到admin表中五条记录
#创建
delimiter $
create procedure myp1()
begin
	insert into admin(username,'password')
	values('join1','0000'),('lily','0000');
end $
#调用
call myp1()$

#2.创建带in模式参数的存储
#案例：创建存储过程实现，根据女神名，查询对应的男神信息
#创建
create procedure myp2(in beautyName varchar(20))
begin
	select bo.*
	from boys bo
	right join beauty b on bo.id = b.id
	where b.name = beautyname;
end$
#调用
call myp2('柳岩')$

#3.创建带out模式的存储过程
#案例一：根据女神名，返回对应的男神名
#创建
create procedure myp3(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.id
	where b.name=beautyName;
end$
#调用
set @bName$
call myp5('小昭'，@bName)$

#4.创建带inout模式的存储过程
#案例一：传入a和b两个值，最终a和b都翻倍返回
create procedure myp8(inout a int,inout b int)
begin
	set a=a*2;
	set b=b*2;
end$

#练习1：创建存储过程实现传入用户名和密码，插入到admin表中
DELIMITER $
CREATE PROCEDURE myp1(IN userName VARCHAR(20),IN psw VARCHAR(20))
BEGIN
	INSERT INTO admin(admin.userName,pwd)
	VALUES(userName,psw);
END$

#练习2：创建存储过程实现传入女神编号，返回女神姓名和电话
CREATE PROCEDURE myp2(IN id VARCHAR(20),OUT NAME VARCHAR(20),OUT telNum VARCHAR(20))
BEGIN
	SELECT b.name,b.phone INTO NAME,telNum
	FROM beauty b
	WHERE b.id = id;
END$

2.2 删除和查看

删除：
语法：drop procedure 存储过程名;
查看：
语法：show create procedure myp2; 

3.函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1.提高了代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数

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

3.1 函数的创建

#一、创建语法：
create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

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

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不会报错，但不建议

return值：
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记

#二、调用语法
select 函数名(参数列表)

---------------------------案例演示------------------------
1.无参有返回
#案例：返回公司的员工个数
create function myf1() returns int
begin
	declare c int;
	select count(*) into c
	from employees;
	return c;
end$
select myf1()$

2.有参有返回
#案例：根据员工名返回工资
create function myf2(empName varchar(20)) returns double
begin
	declare salary double;
	select e.salary into salary
	from employees e
	where e.name = empName;
	return salary;
end$
select myf2('King')$

3.2 查看和删除

查看函数：
show create function 函数名;
删除函数：
drop function 函数名;

4.流程控制结构

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

4.1 分支结构

#1.if函数
功能：实现简单的双分支
语法：
if(表达式1，表达式2，表达式3)
执行顺序：
如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方

#2.case结构
情况1：类似于java中的switch语句，一般用于实现的等值判断
语法：
	case 变量|表达式|字段
	when 要判断的值 then 返回的值1或语句1;
	when 要判断的值 then 返回的值2或语句2;
	...
	else 返回的值n或语句n;
	end case;
	
情况2：类似于java中的多重if语句，一般用于实现区间判断
语法：
	case
	when 要判断的条件1 then 返回的值1或语句1;
	when 要判断的条件2 then 返回的值2或语句2;
	...
	else 返回的值n或语句n;
	end case;

特点：
1.可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end中或begin end外面
可以作为独立的语句使用放在begin end中
2.如果when中的值满足或条件成立，则执行对应的then后面的语句，并结束case
如果都不满足执行else语句
3.else可以省略，如果else 省略，并且所有when语句不满足，则返回null

#if结构
功能：实现多重分支

语法：if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
应用在begin end中

4.2 循环结构

分类：
while、loop、repeat
循环控制：
iterate 类似于continue
leave 类似于 break

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

#2.loop
【标签:】loop
	循环体;
end loop【标签】;
可以用来模拟简单的死循环

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

#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
create procedure test_while(in insertCount int)
begin
	declare i int default 1;
	a:while i <=insertCount do
		insert into admin(userName,`password`)
		values(concat('xxxx',i),'666');
		if i>=20 then leave a;
		end if;
		set i = i+1;
	end while a;
end$