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.参数列表包含三部分:参数模式? ? ?参数名? ? ? ? 参数类型
参数模式 :in\out\inout
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<insertCount DO
INSERT INTO admin VALUES(NULL,CONCAT('Kobe',i),CONCAT(i,i,i,i));
SET i=i+1;
END WHILE;
END $
CALL my_p1(5)$
SELECT * FROM admin$
?案例:批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
CREATE PROCEDURE my_p2(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<insertCount DO
SET i=i+1;
INSERT INTO admin VALUES(NULL,CONCAT('Maryna',i),CONCAT(i,i,i,i));
IF 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<insertCount DO
SET i=i+1;
IF i%2!=0 THEN ITERATE a;
END IF;
INSERT INTO admin VALUES(NULL,CONCAT('Maryna',i),CONCAT(i,i,i,i));
END WHILE a;
END $
CALL my_p3(10)$
SELECT * FROM admin$
?
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<insertCount DO
SET sta=FLOOR(RAND()*26+1);
SET len=20-FLOOR(RAND()*20+1)+1;
INSERT INTO stringcontent VALUES(NULL,SUBSTR(str,sta,len));
SET i=i+1;
END WHILE;
END $
CALL my_p4(5);
SELECT * FROM stringcontent$
?