全部标签
写点什么
MySQLOpenHarmony云计算程序员行业资讯算法Python人工智能区块链Linux前端个人成长面试架构师编程企业动态新基建敏捷安全读书笔记高效工作团队管理创业生涯规划知识管理运维产品经理 查看更多

MySQL 高级

作者：楠羽
  • 2022 年 9 月 05 日
    福建

  • 本文字数：5191 字

    阅读完需：约 17 分钟

📦个人主页：楠羽

🏆简介：一个大二的科班出身的，主要研究 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类型变量、并赋默认值为10DELIMITER $
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


-- 标准语法CASEWHEN 判断条件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 函数名称;
复制代码


划线
评论
复制
发布于: 刚刚阅读数: 3

版权声明: 本文为 InfoQ 作者【楠羽】的原创文章。

原文链接:【http://xie.infoq.cn/article/1e337631f997c5888a5737764】。文章转载请联系作者。

笔记MySQL 数据库9月月更
用户头像

楠羽

关注

还未添加个人签名 2022.08.04 加入

还未添加个人简介

评论

发布
暂无评论
Copyright © 2022, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号
MySQL高级_笔记_楠羽_InfoQ写作社区