前言
- 该笔记是学习 尚硅谷-李玉婷老师的MySQL基础部分所记录的,纯手码~若对您有帮助,请给个赞,谢谢!
- 相关视频:尚硅谷MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门
- 课上相关代码已上传到个人
Github
,如有需要请自取:Github版MySQL笔记和代码
各章节笔记对应链接:
【MySQL数据库】第五章(下):存储过程和函数、流程控制结构
5 其他
5.3 存储过程和函数
- 类似于C++函数
5.3.1 存储过程
含义:一组预先编译号的SQL语句的集合(批处理语句)
好处:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并减少了和数据库服务器的连接次数,提高了效率
#一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
/*注意:
1、参数列表包含三部分
参数模式 参数名 参数类型 */
# 举例:
IN stuname VARCHAR(20)
# 参数模式:
IN:该参数可以作为输入,需调用方传入值
OUT:该参数可以作为输出,可以用作返回值
INOUT:可作为输入又可作为输出,该参数既需要传入值,又可以返回值
2、如果存储过程体仅一句话,BEGIN END 可以省略;
存储过程体中的每条SQL语句的结尾要求必须加分号;
存过程的结尾可以使用DELIMITER 重新设置
语法:
DELIMITER 结束标记
# 如:
DELIMITER $
#二、调用语法
CALL 存储过程名(实参列表);
案例:
#1.空参列表
#案例:插入到admin表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, `password`)
VALUES('john1', '0000'),
('lily', '0000'),('rose', '0000'),('jack', '0000'),('tom', '0000');
END $
#调用
CALL myp1()$
#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现 根据女神名,查询对应的男神信息
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL myp2('小昭')$
#案例2:创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp3(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 myp3('张飞', '8888')$
#3.创建带out模式的存储过程
#案例1:根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL myp4('小昭',@bName)$
SELECT @bName$
#案例2:根据女神名,返回对应的男神名和男神魅力值
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
SELECT bo.boyName, bo.userCP INTO boyName, userCP
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用
CALL myp5('小昭', @bName, @userCP)$
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最终a和b翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
#调用
SET @m=10, @n=20$ #定义两个变量用于输入值 + 存储返回值
CALL myp6(@m, @n)$
SELECT @m, @n$
删除和查看 存储过程
#二、删除存储过程(做不到删除多个)
语法:DROP PROCEDURE 存储过程名
DROP PROCEDURE myp1$
#三、查看存储过程的信息
DESC myp3$ #不行
SHOW CREATE PROCEDURE myp3$
5.3.2 函数
含义:一组预先编译号的SQL语句的集合(批处理语句)
好处:与存储过程相同
区别:
- 存储过程:可以有0个返回,或多个返回(适合批量插入、更新)
- 函数:有且仅有1个返回(适合做处理数据后返回一个结果)
#一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END $
# 注意:
1.参数列表,包含两部分:参数名、参数类型
2.函数体:肯定会有return语句,如果没有会报错
若return语句没有放在函数体的最后也不报错,但不建议
3.函数体只有一句话时,begin end可以省略
4.使用delimiter语句设置结束标记
#二、调用语法
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.有参数有返回
#案例1:根据员工名,返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal;
END $
#调用
SELECT myf2('Kochhar')$
#案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
WHERE d.department_name = deptName;
RETURN sal;
END $
#调用
SELECT myf3('IT')$
#三、查看函数
SHOW CREATE FUNCTION myf3;
#四、删除函数
DROP FUNCTION myf3;
#案例:创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM = num1 + num2;
RETURN SUM;
END $
SELECT test_fun1(1.3, 2.5)$
5.4 流程控制结构
- 顺序结构:程序从上往下依次执行
- 分支结构:程序从两条或多条路径中选择一条去执行
- 循环结构:程序在满足一定条件的基础上,重复执行一段代码
5.4.1 分支结构
1.if
函数
- 功能:实现简单的双分支
- 语法:
if
(表达式1, 表达式2, 表达式3);表达式1为true
,返回表达式2,否则返回表达式3的值 - 应用:任何地方
2.case
结构
情况1:类似C++的Switch case,一般用于实现等值判断
- 语法:
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回的值1或语句1;
WHEN 要判断的值 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
情况2:类似if、if else、if,一般用于实现区间判断
- 语法:
CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
...
ELSE 要返回的值n或语句n;
END CASE;
特点:
-
可以作为表达式,嵌套在其他语句中使用:
可以放在任何地方,begin end
中或外面
可以作为独立的语句去使用,只能放在begin end
中 -
如果
when
中的值满足或条件成立,则执行对应的then
后面的语句,并且结束case
;如果都不满足,则执行·中的语句 -
else
可以省略;若else
省略,并且所有when
都不满足,则返回null
#案例:创建存储过程,根据传入的成绩,显示等级:90-100(A),80-90(B),60-80(C),其余D
CREATE PROCEDURE test_case1(IN score INT)
BEGIN
CASE
WHEN score>=90 THEN SELECT 'A';
WHEN score>=80 THEN SELECT 'B';
WHEN score>=60 THEN SELECT 'C';
ELSE SELECT 'D';
END CASE;
END $
CALL test_case1(95)$
3.if
结构
-
功能:实现多重分支
-
语法:
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n;】
end if;
- 应用场合:只能用在
begin end
中
#案例1:创建存储过程,根据传入的成绩,返回级别:90-100(A),80-90(B),60-80(c),其余D
CREATE FUNCTION test_if1(score INT) RETURNS CHAR
BEGIN
IF score>=90 THEN RETURN 'A';
ELSEIF score>=80 THEN RETURN 'B';
ELSEIF score>=60 THEN RETURN 'C';
ELSE RETURN 'D';
END IF;
END $
SELECT test_if1(86);
5.4.2 循环结构
- 分类:
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【标签】;
类似do..while,先执行一次再判断
案例:
#1.无添加循环控制语句
#案例1:批量插入,根据次数插入到admin表中多条记录
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 $
CALL pro_while1(100)$
#2.添加leave语句
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE pro_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 $
CALL pro_while1(100)$
#3.添加iterate语句
#案例:批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止
CREATE PROCEDURE pro_While1(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;
END IF;
INSERT INTO admin(username,`password`)
VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL pro_while1(100)$