一、变量
1.1 系统变量
系统变量分为全局global以及会话session两种
1.查看系统变量 非指定
show global | session variables 【like '%xxx%'】
2.查看系统变量 指定
select @@global.tx_isolation;
select @@tx_isolation;
3.修改系统变量
set @@global.autocommit=0 针对全局
set@@autocommit=0 针对当前连接
1.2 自定义变量
自定义变量分为用户变量与局部变量
一、用户变量
作用域:针对于当前会话(连接)有效,作用域同于会话变量
1.创建用户变量
set @user = 'wang';
select @v1:=1;
select count(*) into @v2 from employees;
2.修改用户变量
同上,不再赘述
3.查看用户变量
select @v1;
注意:可以看到用户变量是一种若类型的变量,不需要指定变量类型,并且可以随时转变。
二、局部变量
1.声明并初始化局部变量
declare m1 int default 0
2.修改局部变量
set m1 = 2
3.查看局部变量
select m1
注意:局部变量的作用域只限于begin-end块中,而用户变量的作用域是任何地方
二、存储过程
存储过程类似于Java中的函数,其作用是将复杂的业务逻辑封装成一个过程,并且编译好,之后执行此业务就更加方便,且不用再次编译,提高了效率。
1.创建语法
create procedure p1(参数列表)
begin
代码块....
end
2.参数列表包含三部分
参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
in:该参数可以作为输入,也就是该参数需要调用方传入值
out:该参数可以作为输出,也就是该参数可以作为返回值
inout:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
注意:
1.如果存储过程体仅仅只有一句话,begin end可以省略
2.存储过程体中的每条sql语句的结尾要求必须加分号。
3.存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter $
案例
#1.空参列表
#案例:插入到admin表中五条记录
delimiter $ #设置语句结束标识
CREATE PROCEDURE myp1()
BEGIN
DECLARE i int DEFAULT 1;
WHILE i<=5 DO
INSERT INTO admin(uName,pwd) VALUES('fff','fffff');
set i = i+1;
end while;
END$
call myp1()$
#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现 根据女神名,查询对应的男神信息
create PROCEDURE myp2(in gName VARCHAR(20))
BEGIN
select bo.* from beauty b left join boys bo on b.boyfriend_id = bo.id
where b.name = gName;
end$
call myp2()$
#案例2 :创建存储过程实现,用户是否登录成功
create PROCEDURE myp3(in name VARCHAR(20),in pwd VARCHAR(20))
BEGIN
DECLARE res int DEFAULT 0;
select count(*) into res from admin
where admin.uName = name and admin.pwd = pwd;
select if(res>0,'登录成功','登录失败');
END$
call myp3('fff','fffff')$
#3.创建out 模式参数的存储过程
#案例1:根据输入的女神名,返回对应的男神名
create PROCEDURE myp4(in gName varchar(20),out bName VARCHAR(20))
BEGIN
select bo.boyName into bName
from beauty b left join boys bo on b.boyfriend_id=bo.id
where b.name = gName;
END$
call myp4('王语嫣',@bf)$
select @bf $
#案例2:根据输入的女神名,返回对应的男神名和魅力值
create procedure myp5(in gName varchar(20),out bName VARCHAR(20),out cp int)
BEGIN
select bo.boyName,bo.userCP into bName,cp
from beauty b left join boys bo on b.boyfriend_id=bo.id
where b.name = gName;
END$
call myp5('王语嫣',@bf,@cp)$
select @bf,@cp$
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
#三、删除存储过程
#语法:drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;错误,一次只能删一个
#四、查看存储过程的信息
DESC myp2;×
SHOW CREATE PROCEDURE myp2;
2.2存储过程与函数的区别
- 存储过程是将一组操作封装起来,起目的是为了执行那一系列操作,可以有返回值,也可以没有返回值。
- 函数的目的是为了得到一个返回值,必须有返回值
语法:create function fun1(参数列表) returns 返回类型
begin
函数体…
return 返回值
end$
三、流程控制
3.1 case
case分两种情况讨论
情况一:
1.类似于switch
case 变量或表达式
when v1 then _v1
when v2 then _v2
...
else _vn
end
2.类似if-else
case
when 表达式1 then v1
when 表达式1 then v2
...
else vn
end
情况二:
1.类似于switch
case 变量或表达式
when v1 then 表达式1;
when v2 then 表达式2;
...
else 表达式n;
end
2.类似if-else
case
when 表达式1 then 表达式1;
when 表达式1 then 表达式2;
...
else 表达式n;
end
#案例1:创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE;
RETURN ch;
END $
SELECT test_case(56)$
位置:
可以放在任何位置,
如果放在begin end 外面,作为表达式结合着其他语句使用(也就是结束不带;)
如果放在begin end 里面,一般作为独立的语句使用(结束带;)
3.2 if-else
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
else 语句3;
end if;
#案例1:创建函数,实现传入成绩,如果成绩>90,返回A,如果成绩>80,返回B,如果成绩>60,返回C,否则返回D
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF;
RETURN ch;
END $
SELECT test_if(87)$
3.3循环结构
分类:
while、loop、repeat
循环控制:
iterate类似于 continue,继续,结束本次循环,继续下一次
leave 类似于 break,跳出,结束当前所在的循环
*/
#1.while
/*
语法:
【标签:】while 循环条件 do
循环体;
end while【 标签】;
联想:
while(循环条件){
循环体;
}
*/
#2.loop
/*
语法:
【标签:】loop
循环体;
end loop 【标签】;
可以用来模拟简单的死循环
*/
#3.repeat
/*
语法:
【标签:】repeat
循环体;
until 结束循环的条件
end repeat 【标签】;
*/
#案例:批量插入,根据次数插入到admin表中多条记录
DROP PROCEDURE pro_while1$
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE 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 $
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $