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 $