MySQL从基础到高级四
1. 变量
系统变量：
说明：变量由系统提供，不用自定义
语法：
①查询系统变量
——show 【global | session】 variables like ''; # 如果没有显示声明 global 还是 session，则默认是 session
②查看指定的系统变量
——select @@【global | session】, 变量名; # 如果没有显示声明 global 还是 session，则默认是 session
③为系统变量赋值
方式一：
——set 【global | session】变量名 = 值; # 如果没有显示声明 global 还是 session，则默认是 session
方式二：
——set @@global.变量名 = 值; // 全局变量
——set @@变量名 = 值; # 会话变量
全局变量——服务器层面上的，必须拥有 super 权限才能为系统变量赋值，作用域为整个服务器，也就是真的所有连接(会话)有效
会话变量——服务器为每个连接(会话)提供的客户端提供了系统变量，作用域为当前连接(会话)自定义变量
用户变量：
作用域：针对当前连接(会话)有效
位置：begin end 里面，也可以放在外面
使用：
①声明并赋值
set @变量名 = 值;或
set @变量名 := 值;或
select @变量名 := 值;
②更新值
方式一：
set @变量名 = 值;或
set @变量名 := 值;或
select @变量名 := 值;
方式二：
select xx into @变量名 from 表;
③使用
select @变量名;
局部变量
作用域：
仅仅在定义它的 begin end 中有效
位置：只能放在 begin end 中，而且只能放在第一句
使用：
①声明
declare 变量名 类型 【default 默认值】;
②赋值或更新
方式一：
set @变量名 = 值;或
set @变量名 := 值;或
select @变量名 := 值;
方式二：
select xx into @变量名 from 表;
③使用
select 变量;系统变量：变量由系统提供，不是用户定义，属于服务器层面
1、全局变量
语法：
#查看1：查看所有的系统变量
——show global variables;
#查看2：查看满足条件的部分系统变量
——show global variables like '%指定变量名%';
#查看3：查看指定的某个系统变量的值
——select @@global.系统变量名;
#设置1：为某个系统变量赋值
——set global 系统变量名 = 值;
——set global .系统变量名 = 值;
注意：如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认session。
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨服务器重启
2、会话变量
作用域：仅仅作用于当前的会话（连接）有效，重新连接会恢复默认情况
语法：
#查看1：查看所有的系统变量
——show session variables;
#查看2：查看满足条件的部分系统变量
——show session variables like '%指定变量名%';
——show session variables like '%char%';
#查看3：查看指定的某个系统变量的值
——select @@session .系统变量名;
——select @@session.isolation;
#设置1：为某个系统变量赋值
——set session 系统变量名 = 值;
——set session isolation = 'read-uncommitted';
——set session.系统变量名 = 值;
——set session.isolation='read-committed';自定义变量： 变量是由用户自定义的，不是由系统定义的
语法：
声明
赋值
使用（查看、比较、运算等）用户变量
作用域：针对于当前会话（连接）有效，同于会话变量的作用域
声明位置：可以在存储过程（begin-end）里面，也可以在存储过程外面
#案例1：声明并初始化用户变量
——set @变量名=值;
——set @name='join';
——set @变量名:=值;
——set @name:='join';
——select @变量名:=值;
——select @name:='lucy';
#案例2：赋值（更新用户变量的值）
方式一：通过set 和 select
——set @变量名=值;
——set @name='join';
——set @变量名:=值;
——set @name:='join';
——select @变量名:=值;
——select @name:='lucy';
方式二：通过 select into
——select 字段 into @变量名 from 表;
——select avg(score) into @ag from student; ##设置一个ag 变量并赋值为学生平均分数
#案例3：使用（查看用户变量的值）
——select @用户变量名;
——select @ag; ##查看ag 变量的值局部变量
作用域：仅仅在定义它的存储过程（begin-end）中有效
声明位置：只能在存储过程（begin-end）中的第一句话
#案例1：声明一个局部变量
——declare 变量名 类型;
——declare 变量名 类型 declare 值;
#案例2：赋值
方式一：通过set 和 select
——set 变量名=值;
——set name='join';
——set 变量名:=值;
——set name:='join';
——select @变量名:=值;
——select @name:='lucy';
方式二：通过 select into
——select 字段 into 变量名 from 表;
——select avg(score) into ag from student; ##设置一个ag 变量并赋值为学生平均分数
#案例3：使用
——select 局部变量名;
#案例4：声明两个变量并赋初始值，求和，并打印
——set @a=10;
set @b=15;
set @sum=@a+@b;
select @sum;用户变量和局部变量的比较
作用域 | 定义和使用的位置 | 语法 | |
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@符号，不用限定类型 |
局部变量 | begin-end 中 | 只能在begin-end，且为第一句话 | 一般不用加@符号，需要限定类型 |
2. 存储过程和函数：类似于java 中的方法
优点：
类似于java中的方法，将一组特定功能的逻辑语句包装起来，对外暴露方法名字
1）、提高代码的重用性
2）、sql 模板简化操作
3）、减少了编译的次数并且减少了和数据库服务器的连接次数，提高了效率一、创建存储过程
含义：一组预先编译好的SQL 语句的集合，理解成批处理语句
语法：
① 创建语法
——create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL 语句）
end
注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
in stu_name varchar(20)
参数模式：
in：该参数可以作为输入（java 方法中的形参），也就是该参数需要调用方存入值
out：该参数可以作为输出（java 方法中的return），也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，begin-end 可以省略
3、存储过程体中的每条语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter 重新设置
语法：
——delimiter 结束标记;
——delimiter $;
② 调用语法
——call 存储过程名(实参列表);
1、空参列表
#案例1：插入到admin 表中五条记录
——delimiter $ ##设置一个结束标记
——create procedure pro_admin() ##创建pro_admin 存储过程
begin
insert into admin(`username`, `password`) values('张飞', '123456');
insert into admin(`username`, `password`) values('李白', '123456');
insert into admin(`username`, `password`) values('郭靖', '123456');
insert into admin(`username`, `password`) values('丘处机', '123456');
insert into admin(`username`, `password`) values('黄老邪', '123456');
end $
——call pro_admin(); ##调用pro_admin 存储过程
2、创建带in 模式参数列表的存储过程
#案例1：创建存储过程实现根据指导课程名，查询对应的指导信息信息
——delimiter $ ##设置一个结束标记
——create procedure pro_stu(in course varchar(20)) ##创建pro_stu 存储过程
begin
select s.*
from student as s
left join teacher as t
on t.id=s.tech_id;
where t.course=course;
end $
——call pro_stu('体育'); ##调用
#案例2：创建存储过程实现，用户是否登录成功
——delimiter $ ##设置一个结束标记
——create procedure login_succ(in username varchar(20), in password varchar(20))
begin
declare result int; ##定义result 变量
select count(*) into result ##给result 变量赋值
from admin as a
where a.username=username
and a.password=password;
select if(result>0, '成功', '失败'); ##判断
end $
——CALL login_succ('李白', '123456');
——CALL login_succ('lyt', '6666');
3、创建带out 模式的存储过程
#案例1：创建存储过程实现根据学生名字，查询对应的指导老师名字，并输出指导老师名字
——delimiter $ ##设置一个结束标记
——create procedure pro_stu(in _name varchar(20), out last_name varchar(20)) ##创建pro_stu 存储过程
begin
select t.last_name into last_name
from student as s
left join teacher as t
on t.id=s.tech_id;
where s.last_name=_name;
end $
——call pro_stu('刘备', @last_name); ##调用
——select @last_name; ##打印
#案例2：创建存储过程实现根据学生名字，查询对应的指导老师名字，并输出指导老师名字，年龄
——delimiter $ ##设置一个结束标记
——create procedure pro_stu(in _name varchar(20), out last_name varchar(20), out age int) ##创建pro_stu 存储过程
begin
select t.last_name, t.age into last_name, age
from student as s
left join teacher as t
on t.id=s.tech_id;
where s.last_name=_name;
end $
——call pro_stu('刘备', @last_name, @age); ##调用
——select @last_name, @age; ##打印
4、创建带inout 模式参数的存储过程
#案例1：传入a 和b 两个值，最终a 和b 都翻倍并返回
——delimiter $ ##设置一个结束标记
——create procedure num_test(inout a int, inout b int)
begin
set a=a*2;
set b=b*2;
end $
——set @a=4, @b=5;
——call num_test(@a, @b);
——select @a, @b;
练习题：
#案例1：创建存储过程实现传入用户名和密码，插入到admin 表中
——delimiter $ ##设置一个结束标记
——create procedure pro_admin(in username varchar(20), in password varchar(20))
begin
insert into admin(`username`, `password`) values(username, password);
end $
——call pro_admin('lcy', '123456')$
#案例2：创建存储过程或函实现传入女神编号，返回女神名字和电话号码
——delimiter $ ##设置一个结束标记
——create procedure pro_girls(in g_id int, out g_name varchar(20), out phone varchar(20))
begin
select b.name, b.phone into g_name, phone
from beauty as b
where id=g_id;
end $
——call pro_girls(6, @name, @phone)$
——select @name, @phone$
#案例3：创建存储过程或函数实现传入两个女神生日，返回大小
——delimiter $ ##设置一个结束标记
——create procedure pro_girls(in birth1 datetime, in birth2 datetime, out result int)
begin
select datediff(birth1, birth2) into result;
end $
——call pro_girls(''1996-02-13", "1996-07-06", @result)$
——select @result$
#案例4：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
——delimiter $ ##设置一个结束标记
——create procedure pro_date(in _date datetime, out str_date varchar(50))
begin
select date_format(_date, '%Y年%m月%d日') into str_date;
end $
——call pro_date(now(), @date);
——select @date;
#案例5：创建存储过程或函数实现传入女神名字，返回：【女神 and 男神】 格式的字符串
——delimiter $ ##设置一个结束标记
——create procedure pro_girls(in _date varcahr(20), out str varchar(50))
begin
select concat(b.name, ' and ', ifnull(bo.boyName, 'null')) into str
from boys as bo
inner join beauty as b
on bo.id=b.boyfriend_id
where b.name=_date;
end $
——call pro_girls('小昭', @str);
——select @str;
#案例6：创建存储过程或函数，根据传入的条目数和起始索引，查询beauty 表的记录
——delimiter $ ##设置一个结束标记
——create procedure pro_count(in startIndex int, in size int)
begin
select * from beauty limit startIndex, size;
end $
——call pro_count(5,2)；二、删除存储过程
语法：drop procedure 存储过程名称
——drop procedure pro_girls;三、查看存储过程的信息
语法：show create procedure 存储过程名称
——show create procedure pro_stu;四、函数
含义：一组预先编译好的SQL 语句的集合，理解成批处理语句
区别：
①存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
②函数：有且仅有1个返回，适合做处理数据后返回一个结果
语法：
——create function 函数名(参数列表) returns 返回类型
begin
函数体;
end
注意：
1、参数列表包含两部分（参数名 参数类型）
2、函数体：肯定会有return 语句，如果没有会报错，如果return 语句没有房子函数体的最后也不会报错，不建议。（return 值）
3、函数体中仅有一句话，则可以省略begin-end
4、使用delimiter 语句设置结束标记
调用：
——select 函数名(参数列表)
测试：
#案例1：返回student 表的学生个数（无参数有返回）
——delimiter $ ##设置一个结束标记
——create function fun_stu() returns int
begin
declare c int default 0; ##定义变量并设置默认值
select count(*) as c into c
from student;
return c;
end $
——select fun_stu();
#案例2：根据学生名字，返回指定老师名字
——create fun_stu(stu_name varchar(20)) returns varchar(20)
begin
declare tn varchar(20) default 'null'；
select t.last_name into tn
from student as s
inner join teacher as t
on s.tech_id=t.id
where s.last_name=stu_name;
return tn;
end $
——select fun_stu('张飞'); ## 调用函数
# 案例3：创建一个函数，实现传入两个 float 类型参数，返回二者之和的值
——create function sum_data(param1 float, param2 float) returns float
begin
declare sum float default 0;
select sum = param1 + param2;
return sum;
end $
——select sum_data(1, 3) $五、查看函数
——show create function 函数名;
——show create function fun_stu;六、删除函数
——drop function 函数名;
——drop function fun_stu;3. 流程控制结构
分类：
顺序结构：程序从上往下执行的正常流程
分支结构：程序从两条路径或者多条路径中选择一条路径执行
循环结构：程序在满足一定的条件的基础上重复执行一段代码分支结构
if 函数、if 结构、case 结构1. if 函数
功能：
实现简单的双分支语法：
if (表达式, 结果1, 结果2);执行顺序：
如果表达式成立，则返回结果1，否则返回结果2应用：
可以应用在任意地方2. case 结构
情况一：类似于 java 中的 switch 语句，一般用于等值判断
语法：
case 变量 | 表达式 | 字段
when 要判断的值1 then 结果1/语句1;
when 要判断的值2 then 结果2/语句2;
......
else 结果n/语句n;
end case
情况二：类似于 java 中的多重 if 语句，一般用于区间判断
语法：
case
when 条件1 then 结果1/语句1;
when 条件2 then 结果2/语句2;
......
else 结果n/语句n;
end case
特点：
①可以作为表达式，嵌套在其他语句中使用，可以放在任意地方，begin end 中或者 begin end 外面使用，也可以作为独立的语句去使用，但是只能放在begin end 中使用
②如果 when 中的值满足或者条件成立，则执行对应 then 后面的结果，并结束当前case，如果不满足，则执行 else 后面的结果，并结束当前case
③else 可以省略，如果 else 省略了，并且所有 when 条件都不成立，则返回 null
# 案例：创建一个存储过程，根据传入的成绩，显示对应的成绩等级，比如传入的成绩：A[90, 100]、B[80, 90)、C[60, 80)、D[0, 60)
——create procedure flag_grade(in score float)
begin
case
when score >= 90 and score <= 100 then select 'A';
when score >= 80 then select 'B';
when cscore >= 60 then select 'C';
else select 'D'
end case;
end $
——call flag_grade(89) $3. if 结构
功能：
实现多重分支语法：
if 条件1 then 语句1;
elseif 条件2 then 语句2;
.....
【else 语句n;】
end if应用：
在 begin end 中使用案例：
# 案例1：设计一个功能(因为有返回值，所以使用函数)，根据传入的成绩，返回对应的成绩等级，比如传入的成绩：[90, 100]返回A、[80, 90)返回B、[60, 80)返回C、[0, 60)返回D
——create function return_grade(score float) returns char
begin
if score >= 90 and score <= 100 then return 'A';
elseif score >= 80 then return 'B';
elseif score >= 60 then return 'C';
else return 'D';
end if;
end $循环结构
分类：while、loop、repeat
iterate 类似于 java 中的 continue(继续)，结束本次循环内容，继续下一次循环内容
leave 类似与 java 中的 break(损坏)，结束当前所在的循环1. while
语法：
【标签: 】while 循环条件 do
循环体;
end while 【标签】;
联想：java 中的 while
while(条件){
循环体;
}
# 案例1：设计一个可以实现批量插入数据到 admin 表的功能
——create procedure batch_save(in size int)
begin
decade i int default 1;
while i <= size do
insert into admin(username, `password`) values(cancat('小酒窝', i), '123456');
set i = i+1;
end while;
end $
# 案例2：设计一个可以实现批量插入数据到 admin 表的功能，插入次数到达20次停止
——create procedure batch_save2(in size int)
begin
decade i int default 1;
a : while i <= size do
if i >= 20 then levea a;
end if;
insert into admin(username, `password`) values(cancat('小酒窝', i), '123456');
set i = i+1;
end while a;
end $
# 案例3：设计一个可以实现批量插入数据到 admin 表的功能，只插入偶数次数的记录
——create procedure batch_save2(in size int)
begin
decade i int default 0;
a : while i < size do
if mod(i, 2) != 0 then iterate a;
end if;
insert into admin(username, `password`) values(cancat('小酒窝', i), '123456');
set i = i+1;
end while a;
end $2. loop
语法：
【标签: 】loop
循环体;
end loop 【标签】;3. repeat
语法：
【标签: 】repeat
循环体;
until 循环条件
end repeat 【标签】;经典案例
# 案例：已知表 string_content，存在字段有 id—自增—int(10)，content—随机—varchar(20)，向表中插入指定个数的随机字符串到 content 字段。
——drop table if exist string_content;
——create table string_content(
id int(10) primary key auto_increment,
content varchar(20)
);
——create procedure insert_random_string(in num int)
begin
declare i int default 1;
declare str varchar(26) default "abcdefghijklmnopqrstuvwxyz";
declare startIndex int default 1; # 起始索引
declare size int default 1; # 长度
while i<=num do
set startIndex = floor(ran() * 26 + 1); # 获取 0 - 26 的随机整数作为起始索引
set size = 20 - floor(ran() * (26 - startIndex) + 1);
insert into string_content(content) values(substr(str, startIndex, size)));
set i = i + 1;
end while;
end $
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
