1、存储过程
1.1 变量
1、系统变量
变量由系统提供。
全局变量:
会话变量:针对一次连接
使用的语法:
- 查看系统变量
SHOW VARIABLES;
查看全局变量
SHOW GLOBAL VARIABLES;
查看会话变量【不写session默认就是会话变量】
SHOW SESSION VARIABLES;
- 查看满足条件的部分变量 使用like
SHOW GLOBAL VARIABLES LIKE '%char%';
- 查看指定的某个系统变量的值
# SELECT @@系统变量名
SELECT @@character_set_system
SELECT @@global.character_set_system
- 为某个系统变量赋值
# SET GLOBAL 系统变量名=值;
# SET @@global.系统变量名 = 值;
如果是全局变量,必须加 GLOBAL 关键字,如果不加GLOBAL 默认是 session
全局变量对所有连接有效,但是不能跨重启,想要重启依然生效需要修改配置文件。
SET @m = 1;
SET @n = 2;
SET @sum = @n + @m;
SELECT @sum;
2、自定义变量
用户自定义的,不是由系统提供的。作用域是当前会话有效
用户变量
- 声明并初始化
SET @用户变量名=值;
# 方式2
SET @用户变量名:=值;
# 方式3
SELECT @用户变量名:=值;
- 赋值
SET @用户变量名=值;
# 方式2
SET @用户变量名:=值;
# 方式3
SELECT @用户变量名:=值;
#方式4
SELECT 字段 INTO @变量名 FROM 表;
SET @count=100;
SELECT COUNT(*) INTO @count FROM employees;
- 查看
SELECT @用户变量名;
SELECT @count;
用户变量可以放在任意地方
局部变量:仅仅在定义它的begin end 中有效,应用在begin end 中的第一句话
声明:
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
赋值:
SET 局部变量名=值;
# 方式2
SET 局部变量名:=值;
# 方式3
SELECT @局部变量名:=值;
#方式4
SELECT 字段 INTO 变量名 FROM 表;
查看:
SELECT 局部变量名;
1.2 存储过程和函数
存储过程和函数类似于 java 中的方法
存储过程:一组预先编译好的sql语句的集合。
使用存储过程,能够减少编译次数(存储过程之后被编译一次),简化操作,减少了和数据库的连接次数,提供了效率。
1.2.1 创建
语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体
END
参数列表包含三部分:参数模式、参数名、参数类型
例如:
IN stuname VARCHAR(20)
1、参数模式有3个:
IN、 进口,该参数可以作为输入,需要调用方传来值
OUT、出口,该参数可以作为输出,即作为返回值
INOUT 进出口,该参数即可以作为输入、又可以作为输出,调用方即需要传递值过来,也会返回给调用方。
2、begin end 就相当于是一对大括号,当存储过程体只有一句话时, 可以省略不写。
3、存储过程体中的每条sql语句结尾都必须加上分号,而存储过程本身的结尾可以使用 delimiter 重新设置。语法
delimiter 结束标记
delimiter $
1.2.2 调用、删除、查看
CALL 存储过程名(实参列表);
1、创建无参的存储过程
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD)
VALUES('john1','0000'),('lily','0000'),
('rose','0000'),('jack','0000'),
('tom','0000');
END $
CALL myp1();
1、创建带IN的存储过程
创建存储过程 实现 根据女性信息,查询对应的男性信息
# 创建 IN类型参数的 存储过程
CREATE PROCEDURE myp2(IN beantyName VARCHAR(20))
BEGIN
SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beantyName;
END;
# 调用
CALL myp2('热巴');
创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ''; # 局部变量声明并初始化
SELECT COUNT(*) INTO result #赋值
FROM admin WHERE admin.username = username
AND admin.`password` = `password`;
SELECT result; # 查询变量值
END;
# 调用
CALL myp3('john1', '10000');
加上 IF 看看
CREATE PROCEDURE myp4(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, '成功', '失败') AS 结果; # 使用
END;
CALL myp4('john1', '10000');
3、创建带 OUT 的存储过程
# 创建存储过程 实现 根据女性信息,查询对应的男性名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END;
# 调用
CALL myp5('热巴', @boyName);
SELECT @boyName;
多个 OUT
# 创建存储过程 实现 根据女性信息,查询对应的男性名、魅力值
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
SELECT bo.boyName, bo.userCP INTO boyName, userCP
FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END;
CALL myp6('热巴', @boyName, @userCP);
SELECT @boyName, @userCP;
4、 带 INOUT 的存储过程
# 传入 a和b,最终a和b都翻倍
CREATE PROCEDURE myp7(INOUT a INT, INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END
# 调用
SET @a = 3;
SET @b = 5;
CALL myp7(@a, @b);
SELECT @a,@b;
如何删除存储过程呢?
DROP PROCEDURE 存储过程名;
# 例子
SHOW CREATE PROCEDURE myp1;
删除存储过程只能一次删除一个。
查看存储过程的信息
SHOW CREATE PROCEDURE 存储过程名;
# 例子
SHOW CREATE PROCEDURE myp2;
存储过程内部的逻辑语句是不能修改的,需要修改可以删除重新写一个。
# 创建存储过程实现传入一个日期,格式化输出。
CREATE PROCEDURE dateformatefunc(IN date DATETIME, OUT dateStr VARCHAR(20))
BEGIN
SELECT DATE_FORMAT(date, '%y-%m-%d') INTO dateStr;
END;
CALL dateformatefunc(NOW(), @str);
SELECT @str;
2、函数
函数与存储过程的区别:
函数可以有0个返回,也可以有多个返回。
函数:有且只有1个返回值。
存储过程适合批量插入、批量更新
函数适合处理数据后返回一个结果。
2.1 函数的创建
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
参数列表包含2部分:参数名,参数类型。没有IN、OUT、INOUT 这些了
函数体一定有return语句,如果没有会报错。
如果 return 不是放在函数体最后,不会报错,但是不建议。
当 函数体只有一句话时,可以省略 begin end
使用 delimiter 设置结束标记。
函数的调用 使用 SELECT
SELECT 函数名(参数列表)
1、无参
案例
# 返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT count(*) INTO c FROM employees;
RETURN c;
END;
SELECT myf1();
2、有参
案例
# 根据员工名返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT salary INTO c FROM employees WHERE employees.last_name = empName;
RETURN c;
END;
SELECT myf2('Ernst');
案例
# 根据部门名返回平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE c DOUBLE DEFAULT 0;
SELECT AVG(salary) INTO c FROM employees e JOIN departments d
ON e.department_id = d.department_id WHERE d.department_name=depName;
RETURN c;
END;
SELECT myf3('adm');
2.2 查看函数详情、删除
# SHOW CREATE FUNCTION 函数名;
SHOW CREATE FUNCTION myf3;
删除函数
# DROP FUNCTION 函数名;
DROP FUNCTION myf1;
2.3 函数的使用案例
# 创建函数,实现传入2个float 返回二者之和
CREATE FUNCTION testfunc1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE sum FLOAT DEFAULT 0;
SET sum = num1 + num2;
RETURN sum;
END;
SELECT testfunc1(1,4);
3、流程控制结构
顺序结构没啥说的。
3.1 分支结构
1、if 函数
SELECT IF(表达式1, 表达式2, 表达式3)
执行顺序:如果 表达式1 成立,返回表达式2 的值,否则返回表达式3的值
这个可以放在任意地方使用,包括上面的存储过程和函数。
2、case 结构
情况1:类似于switch 一般用于等值判断
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或者语句1;
WHEN 要判断的值 THEN 返回的值2或者语句2;
WHEN 要判断的值 THEN 返回的值3或者语句3;
else 要返回的值n或者语句n;
END Case;
情况2:多重IF,实现区间判断
CASE
WHEN 要判断的条件1 THEN 返回的值1或者语句1;
WHEN 要判断的条件2 THEN 返回的值2或者语句2;
WHEN 要判断的条件3 THEN 返回的值3或者语句3;
else 要返回的值n或者语句n;
END Case;
特点:
- 可以作为表达式,嵌套在其他语句中,可以放在任何地方;也可以作为单独的语句,但是只能放在BEGIN END 中。
- 执行时,WHEN中的值或者条件成立,则执行对应的THEN后面的语句,并结束case
如果都不满足,则执行else。
- else 可以省略,如果else省略且所有when条件都不满足,则返回null
案例:创建存储过程,根据传入的成绩,显示等级
CREATE PROCEDURE test_case(IN score INT, OUT class CHAR(1))
BEGIN
CASE
WHEN score BETWEEN 0 and 59 THEN SELECT 'D' INTO class;
WHEN score BETWEEN 60 and 69 THEN SELECT 'C' INTO class;
WHEN score BETWEEN 70 and 89 THEN SELECT 'B' INTO class;
WHEN score BETWEEN 90 and 95 THEN SELECT 'A' INTO class;
ELSE
SELECT 'S' INTO class;
END CASE;
END;
CALL test_case(85, @class);
SELECT @class;
3、IF 结构
if结构与if函数不同,功能就是实现多重分支
语法:
IF 条件1 then 语句1;
elseif 条件2 then 语句2;
elseif 条件3 then 语句3;
...
else 语句n;
end if;
else 也是可以省略的。
注意, if 结构只能应用在 begin end 中。
案例:创建函数,根据传入的成绩,显示等级
CREATE FUNCTION test_if(score INT) RETURNS VARCHAR(20)
BEGIN
IF score BETWEEN 0 and 59 THEN RETURN 'D' ;
ELSEIF score BETWEEN 60 and 69 THEN RETURN 'C' ;
ELSEIF score BETWEEN 70 and 89 THEN RETURN 'B' ;
ELSEIF score BETWEEN 90 and 95 THEN RETURN 'A';
ELSE
RETURN'S';
END IF;
END;
SELECT test_if(85);
3.2 循环结构
循环结构必须放在BEGIN END 之间
分类 while、loop、repeat
循环控制:iterate 类似于continue、leave 类似于break
while 语法:先判断后执行
【标签:】 while 循环条件
do 循环体;
end while【标签】;
如果需要搭配循环控制,就需要写上标签。
loop 语法:没有条件的死循环
【标签:】 loop 循环体;
end loop【标签】;
可以用来模拟死循环
repeat 语法:先执行后判断
【标签:】 repeat 循环体;
until 结束条件
end repeat【标签】;
案例:
DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<insertCount
DO
INSERT INTO admin(username, password) VALUES(concat('rose', i), '666');
SET i = i + 1;
END WHILE a;
END;
CALL pro_while(10);
添加循环控制的案例
DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<insertCount DO
INSERT INTO admin(username, password) VALUES(concat('haha', i), '000');
IF i > 5 THEN LEAVE a;
END IF;
SET i = i + 1;
END WHILE a;
END;
CALL pro_while(10);
DROP PROCEDURE IF EXISTS pro_while;
CREATE PROCEDURE pro_while(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<insertCount DO
SET i = i + 1;
IF MOD(i,2) = 0 THEN ITERATE a; # mod 函数是取余
END IF;
INSERT INTO admin(username, password) VALUES(concat('iter', i), '111');
END WHILE a;
END;
CALL pro_while(10);