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$