mysql高级
【摘要】 MySQL
📦个人主页：楠慧
🏆简介：一个大二的科班出身的，主要研究Java后端开发
⏰座右铭：成功之前我们要做应该做的事情，成功之后才能做我们喜欢的事
💕 过客的你，可以给博主留下一个小小的关注吗？这是给博主最大的支持。以后博主会更新大量的优质的作品！！！！
一、MySQL存储过程和函数
1.存储过程和函数的概念
- 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合
2.存储过程和函数的好处
- 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
- 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
- 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
- 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理
3.存储过程和函数的区别
- 函数必须有返回值
- 存储过程没有返回值
4.创建存储过程
- 小知识
/*
该关键字用来声明sql语句的分隔符，告诉MySQL该段命令已经结束！
sql语句默认的分隔符是分号，但是有的时候我们需要一条功能sql语句中包含分号，但是并不作为结束标识。
这个时候就可以使用DELIMITER来指定分隔符了！
*/
-- 标准语法
DELIMITER 分隔符
- 数据准备
-- 创建db8数据库
CREATE DATABASE db8;
-- 使用db8数据库
USE db8;
-- 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
gender VARCHAR(5), -- 学生性别
score INT -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);
-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
- 创建存储过程语法
-- 修改分隔符为$
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
sql语句;
END$
-- 修改分隔符为分号
DELIMITER ;
- 创建存储过程
-- 修改分隔符为$
DELIMITER $
-- 创建存储过程，封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$
-- 修改分隔符为分号
DELIMITER ;
5.调用存储过程
- 调用存储过程语法
-- 标准语法
CALL 存储过程名称(实际参数);
-- 调用stu_group存储过程
CALL stu_group();
6.查看存储过程
- 查看存储过程语法
-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';
7.删除存储过程
- 删除存储过程语法
-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- 删除stu_group存储过程
DROP PROCEDURE stu_group;
8.存储过程语法
8.1存储过程语法介绍
- 存储过程是可以进行编程的。意味着可以使用变量、表达式、条件控制语句、循环语句等，来完成比较复杂的功能！
8.2变量的使用
- 定义变量
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- 定义一个int类型变量、并赋默认值为10
DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
DECLARE num INT DEFAULT 10; -- 定义变量
SELECT num; -- 查询变量
END$
DELIMITER ;
-- 调用pro_test1存储过程
CALL pro_test1();
- 变量的赋值1
-- 标准语法
SET 变量名 = 变量值;
-- 定义字符串类型变量，并赋值
DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
DECLARE NAME VARCHAR(10); -- 定义变量
SET NAME = '存储过程'; -- 为变量赋值
SELECT NAME; -- 查询变量
END$
DELIMITER ;
-- 调用pro_test2存储过程
CALL pro_test2();
- 变量的赋值2
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
-- 定义两个int变量，用于存储男女同学的总分数
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE men,women INT; -- 定义变量
SELECT SUM(score) INTO men FROM student WHERE gender='男'; -- 计算男同学总分数赋值给men
SELECT SUM(score) INTO women FROM student WHERE gender='女'; -- 计算女同学总分数赋值给women
SELECT men,women; -- 查询变量
END$
DELIMITER ;
-- 调用pro_test3存储过程
CALL pro_test3();
8.3if语句的使用
- 标准语法
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
-
输出参数
- 标准语法
DELIMITER $ -- 标准语法 CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型) BEGIN 执行的sql语句; END$ DELIMITER ;- 案例演示
/* 输入总成绩变量，代表学生总成绩 输出分数描述变量，代表学生总成绩的描述 根据总成绩判断： 380分及以上 学习优秀 320 ~ 380 学习不错 320以下 学习一般 */ DELIMITER $ CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10)) BEGIN -- 判断总分数 IF total >= 380 THEN SET description = '学习优秀'; ELSEIF total >= 320 AND total < 380 THEN SET description = '学习不错'; ELSE SET description = '学习一般'; END IF; END$ DELIMITER ; -- 调用pro_test6存储过程 CALL pro_test6(310,@description); -- 查询总成绩描述 SELECT @description;- 小知识
@变量名: 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。 @@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量
8.5case语句的使用
- 标准语法1
-- 标准语法
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
- 标准语法2
-- 标准语法
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
8.6while循环
- 标准语法
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
8.7repeat循环
- 标准语法
-- 标准语法
初始化语句;
REPEAT
循环体语句;
条件控制语句;
UNTIL 条件判断语句
END REPEAT;
-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行
8.8loop循环
- 标准语法
-- 标准语法
初始化语句;
[循环名称:] LOOP
条件判断语句
[LEAVE 循环名称;]
循环体语句;
条件控制语句;
END LOOP 循环名称;
-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
-- 如果不加退出循环的语句，那么就变成了死循环。
8.9游标
-
游标的概念
- 游标可以遍历返回的多行结果，每次拿到一整行数据
- 在存储过程和函数中可以使用游标对结果集进行循环的处理
- 简单来说游标就类似于集合的迭代器遍历
- MySQL中的游标只能用在存储过程和函数中
-
游标的语法
- 创建游标
-- 标准语法 DECLARE 游标名称 CURSOR FOR 查询sql语句;- 打开游标
-- 标准语法 OPEN 游标名称;- 使用游标获取数据
-- 标准语法 FETCH 游标名称 INTO 变量名1,变量名2,...;- 关闭游标
-- 标准语法 CLOSE 游标名称; -
游标的基本使用
-- 创建stu_score表
CREATE TABLE stu_score(
id INT PRIMARY KEY AUTO_INCREMENT,
score INT
);
/*
将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $
CREATE PROCEDURE pro_test11()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 创建游标,查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 开启游标
OPEN stu_result;
-- 使用游标，遍历结果,拿到第1行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第2行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第3行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第4行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 关闭游标
CLOSE stu_result;
END$
DELIMITER ;
-- 调用pro_test11存储过程
CALL pro_test11();
-- 查询stu_score表
SELECT * FROM stu_score;
-- ===========================================================
/*
出现的问题：
student表中一共有4条数据，我们在游标遍历了4次，没有问题！
但是在游标中多遍历几次呢？就会出现问题
*/
DELIMITER $
CREATE PROCEDURE pro_test11()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 创建游标,查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 开启游标
OPEN stu_result;
-- 使用游标，遍历结果,拿到第1行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第2行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第3行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第4行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 使用游标，遍历结果,拿到第5行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
-- 关闭游标
CLOSE stu_result;
END$
DELIMITER ;
-- 调用pro_test11存储过程
CALL pro_test11();
-- 查询stu_score表,虽然数据正确，但是在执行存储过程时会报错
SELECT * FROM stu_score;
- 游标的优化使用(配合循环使用)
/*
当游标结束后，会触发游标结束事件。我们可以通过这一特性来完成循环操作
加标记思想：
1.定义一个变量，默认值为0(意味着有数据)
2.当游标结束后，将变量值改为1(意味着没有数据了)
*/
-- 1.定义一个变量，默认值为0(意味着有数据)
DECLARE flag INT DEFAULT 0;
-- 2.当游标结束后，将变量值改为1(意味着没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
/*
将student表中所有的成绩保存到stu_score表中
*/
DELIMITER $
CREATE PROCEDURE pro_test12()
BEGIN
-- 定义成绩变量
DECLARE s_score INT;
-- 定义标记变量
DECLARE flag INT DEFAULT 0;
-- 创建游标，查询所有学生成绩数据
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 游标结束后，将标记变量改为1
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
-- 开启游标
OPEN stu_result;
-- 循环使用游标
REPEAT
-- 使用游标，遍历结果,拿到数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES (NULL,s_score);
UNTIL flag=1
END REPEAT;
-- 关闭游标
CLOSE stu_result;
END$
DELIMITER ;
-- 调用pro_test12存储过程
CALL pro_test12();
-- 查询stu_score表
SELECT * FROM stu_score;
9.存储过程的总结
- 存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理
- 说白了存储过程其实就是将sql语句封装为方法，然后可以调用方法执行sql语句而已
- 存储过程的好处
- 安全
- 高效
- 复用性强
10.存储函数
-
存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到！
-
存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)
-
标准语法
- 创建存储函数
DELIMITER $ -- 标准语法 CREATE FUNCTION 函数名称([参数 数据类型]) RETURNS 返回值类型 BEGIN 执行的sql语句; RETURN 结果; END$ DELIMITER ;- 调用存储函数
-- 标准语法 SELECT 函数名称(实际参数);- 删除存储函数
-- 标准语法 DROP FUNCTION 函数名称;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）