Mysql之变量、过程、函数
变量分为系统变量和自定义变量
系统变量：说明：系统变量是由系统提供的，不是用户自定义的，属于服务器层面
1.全局变量 2.会话变量
使用语法：
1.查看所有的全局/[局部变量] 默认是局部变量
SHOW global|[session] VARIABLES;
2.查看某些满足条件的系统变量
SHOW GLOBAL|[session] VARIABLES LIKE '%char%';
3.查看指定的某个系统变量的值
SELECt @@global|[session].transaction_isolation;
4. 为系统变量赋值
方式一：
set global|[session] 系统变量名 = 值;
SET GLOBAL autocommit=0; SHOW GLOBAL VARIABLES LIKE 'autocommit';
方式二：
set @@global|[session].变量名 = 值;
SET @@global.autocommit = 1;
global和session作用域的讲解：
global的作用域：服务器每次重启都会对全局变量进行一个赋值，所以全局变量的修改只对所有会话(连接)有效，不能跨重启
session的作用域：仅对当前的会话有效
自定义变量：
说明：由用户自己定义，不是由系统
使用：①声明②赋值③使用
1.用户变量
声明：
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
赋值：
方式一：
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
方式二:
select 字段 into @变量名 from table where 条件;
使用：
select @变量名;
用户变量的作用域：当前会话有效
2.局部变量
声明：
declare 变量名 数据类型 [default 默认值];
赋值：
set 变量名 = 值;
set 变量名 := 值;
select @变量名 :=值;
select 字段 into 变量名 from table;
使用：
select 变量名;
局部变量的作用域：只能在begin-end中有效
用户变量与局部变量的对比：
作用域 定义与使用 语法
用户变量 当前会话 任何地方 必须加上@，不限定数据类型
局部变量 BEGIN END BEGIN-END且为第一句 一般不用加@，限定数据类型
案例：声明两个变量并赋初始值，求和，并打印
#使用用户变量实现 SET @num1=10; SET @num2=5; SET @sum=@num1+@num2; SELECT @sum;
#使用局部变量实现,由于局部变量的作用域仅在begin-end中所以这里只是个局部代码 DECLARE num1 INT DEFAULT 0; DECLARE num2 INT DEFAULT 0; SET num1=10; SET num2=5; SELECT num1+num2;存储过程：procedure
说明：一组预先编号的sql语句，可以看成是批处理
好处:
1.提高代码的重用性
2.简化 *** 作
3.减少编译次数和与服务器连接的次数，提高效率
语法：
create procedure 过程名(参数列表)
begin
过程体;
end
细节：
1.参数列表包含三部分:参数模式 参数名 参数类型
参数模式 :inoutinout
in:该参数可以作为输入，需要调用方传入参数
out:该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数即可输入也可输出
2.如果过程体中仅有一句语句，begin和end可以省略
存储体的每条语句结构需要加上' ; '
存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记
delimiter $
调用语法：call 存储过程名(实参列表);
说明：存储过程、函数需要在命令行窗口执行，数据库管理软件并不支持
案例1：向admin表中插入五条记录(无参列表)
DELIMITER $ CREATE PROCEDURE insert_admin_pro() BEGIN INSERT INTO admin VALUES(NULL,'Kobe','0000'),(NULL,'Oneal','0000'),(NULL,'Lebron','0000'),(NULL,'Wade','0000'),(NULL,'Paul','0000'); END $ CALL insert_admin_pro()$
#案例：创建存储过程，实现根据女神名，查询对应的男生信息(带in模式参数的存储过程)
CREATE PROCEDURE select_boysinfo_pro(IN NAME VARCHAr(20))
BEGIN
SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERe b.name=NAME;
END $
CALL select_boysinfo_pro('热巴')$
案例：创建存储过程，实现用户是否登录成功
CREATE PROCEDURE login_pro(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 login_pro('Kobe','0000')$
案例：根据女神名，返回对应的男生名(创建带out模式的存储过程)
CREATE PROCEDURE byGirlName_getBoysName_pro(IN beautyName VARCHAr(20),OUT boysName VARCHAr(20))
BEGIN
SELECT boys.boyName INTO boysName FROM boys RIGHT JOIN beauty ON boys.id=beauty.boyfriend_id WHERe beauty.`name`=beautyName;
END $
CALL byGirlName_getBoysName_pro('小昭',@boyName)$
SELECt @boysName$
案例：传入a和b两个值，最终a和b都翻倍并返回(带inout参数模式的存储过程)
CREATE PROCEDURE double_pro(INOUT a INT,INOUT b INT) BEGIN SELECT a*2,b*2 INTO a,b; END $ SET @a=10$ SET @b=5$ CALL double_pro(@a,@b)$ SELECT @a,@b$
1.创建存储过程或函数实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE my_pro1(IN username VARCHAr(20),IN PASSWORD VARCHAr(20))
BEGIN
INSERT INTO admin VALUES(NULL,username,PASSWORD);
END $
CALL my_pro1('阿粉','1111')$
2.创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE my_pro2(IN id INT,OUT beautyName VARCHAr(20),OUT phone VARCHAr(11)) BEGIN SELECT beauty.name,beauty.phone INTO beautyName,phone FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id WHERe beauty.id=id; END $ CALL my_pro2(4,@beautyName,@phone)$ SELECt @beautyName,@phone$
3.创建存储过程或函数实现传入女神编号返回两个女神生日的大小
CREATE PROCEDURE my_pro3(IN id1 INT,IN id2 INT,OUT result CHAr(2)) BEGIN DECLARE r INT; DECLARE b1 DATETIME; DECLARE b2 DATETIME; SELECT borndate INTO b1 FROM beauty WHERe id=id1 ; SELECt borndate INTO b2 FROM beauty WHERe id=id2; SELECt DATEDIFF(b1,b2) INTO result; END $ CALL my_pro3(1,2,@result)$ SELECT @result$
4.创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE my_pro4(IN d DATETIME,OUT result VARCHAr(20))
BEGIN
SELECT DATE_FORMAT(d,'%y年%m月%d日') INTO result;
END $
CALL my_pro4('2021-11-19',@result)$
SELECT @result$
5.创建一个存储过程实现传入女神的名称，返回：女神 AND 男神 格式的字符串
传入： 小昭
返回： 小昭 AND 张无忌
CREATE PROCEDURE my_pro5(IN NAME VARCHAr(20))
BEGIN
SELECT CONCAt(NAME,' AND ',boyName) FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id WHERe beauty.name = NAME;
END $
CALL my_pro5('小昭')$
6.创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE my_pro6(IN s INT,IN size INT) BEGIN SELECt * FROM beauty LIMIT s,size; END $ CALL my_pro6(0,5)$
存储过程的删除：
drop procedure 存储过程名;
存储过程的查看：
show create procedure 存储过程名;
函数：
说明：一组预先编译好的sql语句，可以看成批处理
优点：
1.提高代码的复用性
2.简化 *** 作
3.减少编译次数和数据库服务器的连接，提高效率
语法：
create function 函数名 returns 返回值类型
begin
函数体;
end
细节：
1.参数列表
参数名 返回值类型
2.必须要有返回值，没有返回值会报错
return语句如果不放在函数体的最后面不会报错但是不推荐
3.如果函数体只有一句话则可以省略begin和end
4.使用delimiter设置结束标记
存储过程和函数的区别：
1.存储过程可以没有返回值，也可以有多个返回值，适合做批插入，批更新
2.函数必须有一个返回值，适合做对数据的处理后返回一个结果
函数的调用：
select 函数名(参数列表);
案例：返回公司的员工个数
CREATE FUNCTION f1() RETURNS INT DETERMINISTIC BEGIN DECLARE c INT DEFAULT 0; SELECT COUNT(*) INTO c FROM employees; RETURN c; END $ SELECt f1()$
案例：根据员工名，返回它的工资
CREATE FUNCTION f2(NAME VARCHAr(20)) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE s DOUBLE;
SELECT salary INTO s FROM employees WHERe last_name=NAME;
RETURN s;
END $
SELECt f2('Kochhar')$
案例：根据部门名，返回该部门的平均工资
CREATE FUNCTION f3(dName VARCHAr(20)) RETURNS DOUBLE
DETERMINISTIC
BEGIN
DECLARE result DOUBLE;
SELECT AVG(salary) INTO result FROM employees JOIN departments ON employees.department_id=departments.department_id WHERe department_name=dName GROUP BY departments.department_id ;
RETURN result;
END $
SELECt f3('Adm')$
查看函数：
show create function 函数名;
删除函数:
drop function 函数名;
流程控制：
1.顺序结构：按照代码顺序从上到下执行
2.选择结构
单分支：IF -> 类似于java中的三元运算符
语法:if(exp1,exp2,exp3) ->若果exp1为真则执行exp2否则执行exp3
多分支：case
①类似于java中switch-case，一般用于值判断
语法：
case 表达式字段值
when 值1 then 值1语句1;
when 值2 then 值2语句2;
....
else 语句n;
end case;
②类似于java中的if-else if，一般用于范围判断
case
when exp1 then 值1语句1;
when exp2 then 值2语句2;
....
else 语句n;
end case;
案例:创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A，80-90，#显示B，60-80，显示C，否则，显示D
CREATE PROCEDURE my_pro1(IN score DOUBLE) BEGIN CASE WHEN score<=100 AND score>=90 THEN SELECT 'A'; WHEN score>=80 THEN SELECT 'B'; WHEN score>=60 THEN SELECT 'C'; ELSE SELECT 'D'; END CASE; END $ CALL my_pro1(85)$
if-elseif-else:
语法：
if exp1 then 语句1;
elseif exp2 then 语句2;
elseif exp3 then 语句3;
....
else 语句n;
end if;
案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，返回A，80-90，返回B，60-80，返回C，否则，返回D
CREATE PROCEDURE my_pro2(IN score INT) BEGIN IF score<=100 AND score>=90 THEN SELECT 'A'; ELSEIF score>=80 THEN SELECT 'B'; ELSEIF score>=60 THEN SELECT 'C'; ELSE SELECT 'D'; END IF; END $ CALL my_pro2(75)$
3.循环结构
1.while 2.loop 死循环 3.repeat 类比java中的do-while
leave: 类比java中的break;
iterate:类比java中的continue;
语法：
①[标志:]while 循环条件 do
循环体;
end while [标志];
②[标志:]loop
循环体;
end loop [标志:];
③[标志:]repeat
循环体;
util 结束循环的条件;
end repeat [标志];
案例：批量插入，根据次数插入到admin表中多条记录
CREATE PROCEDURE my_p1(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i案例:批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
CREATE PROCEDURE my_p2(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i=20 THEN LEAVE a; END IF; END WHILE a; END $ CALL my_p2(30)$ SELECt * FROM admin$ 案例：批量插入，根据次数插入到admin表中多条记录，值插入偶数次
CREATE PROCEDURE my_p3(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; a:WHILE i
DROp TABLE IF EXISTS stringcontent; CREATE TABLE stringcontent( id INT PRIMARY KEY AUTO_INCREMENT, content VARCHAr(20) );CREATE PROCEDURE my_p4(IN insertCount INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE str VARCHAr(26) DEFAULT 'abcdefghijklmnopqrxtuvwxyz'; DECLARE sta INT DEFAULT 0; DECLARE len INT DEFAULT 0; WHILE i
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）