MySQL基础-变量/流程控制/游标/触发器

文章目录

  • MySQL基础-变量/流程控制/游标/触发器
    • 一、变量
      • 1、系统变量
      • 2、用户变量
    • 二、流程控制
      • 1、分支语句
      • 2、循环语句
      • 3、跳转语句
    • 三、游标
      • 1、概念
      • 2、使用
    • 四、触发器
      • 1、触发器概念
      • 2、触发器使用
      • 3、触发器的优缺点

MySQL基础-变量/流程控制/游标/触发器

一、变量

image-20230331210925724

在MySQL数据库的存储过程和函数中,可以使用变量来存储查询或计算的中间结果数据,或者输出最终的结果数据

在 MySQL 数据库中,变量分为 系统变量 以及 用户自定义变量

1、系统变量

变量由系统定义,不是用户定义,属于 服务器 层面

启动MySQL服务,生成MySQL服务实例期间,MySQL将为MySQL服务器内存中的系统变量赋值,这些系统变量定义了当前MySQL服务实例的属性、特征

  • 查看变量:
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
#或 
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
  • 修改系统变量的值:

方式1:修改MySQL 配置文件 ,继而修改MySQL系统变量的值(该方法需要重启MySQL服务)

方式2:在MySQL服务运行期间,使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1:
SET @@global.变量名=变量值;
#方式2:
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1:
SET @@session.变量名=变量值;
#方式2:
SET SESSION 变量名=变量值;

2、用户变量

  1. 用户变量是用户自己定义的,作为 MySQL 编码规范,MySQL 中的用户变量以 一个“@” 开头
  2. 根据作用范围不同,又分为 会话用户变量 和 局部变量
  3. 会话用户变量:作用域和会话变量一样,只对 当前连接 会话有效
  4. 局部变量:只在 BEGIN 和 END 语句块中有效,局部变量只能在 存储过程和函数 中使用
  • 定义用户变量:
#方式1:“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2:“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
  • 定义局部变量:
  1. 定义:可以使用 DECLARE 语句定义一个局部变量
  2. 作用域:仅仅在定义它的 BEGIN … END 中有效
  3. 位置:只能放在 BEGIN … END 中,而且只能放在第一句
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END
  • 案例:声明局部变量,并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE set_value(IN emp_id INT)
BEGIN
	DECLARE emp_name VARCHAR(25);
	DECLARE sal DOUBLE(10,2);
	
	SELECT last_name,salary INTO emp_name,sal
	FROM employees
	WHERE employee_id=emp_id;
	
	SELECT emp_name,sal;
END //
DELIMITER ;

image-20230331212613671

二、流程控制

流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序,是我们完成复杂操作必不可少的一部分

  • 只要是执行的程序,流程就分为三大类:
  1. 顺序结构 :程序从上往下依次执行
  2. 分支结构 :程序按条件进行选择执行,从两条或多条路径中选择一条执行
  3. 循环结构 :程序满足一定条件下,重复执行一组语句
  • 针对于MySQL 的流程控制语句主要有 3 类:
  1. 条件判断语句 :IF 语句和 CASE 语句(类似switch语句)
  2. 循环语句 :LOOP(类似while(true)语句)、WHILE (while循环)和 REPEAT 语句(do-while语句)
  3. 跳转语句 :ITERATE(continue) 和 LEAVE (break)语句

注:只能用于存储程序

1、分支语句

  • IF 语句的语法结构是:
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
  • CASE 语句的语法结构:
#情况一:类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)
#情况二:类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)

2、循环语句

LOOP语句的基本格式:

[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]
  • 案例:声明存储过程“update_salary_loop()”,存储过程中实现循环给大家涨薪,薪资涨为原来的1.1倍,直到全公司的平均薪资达到12000结束,并统计循环次数
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employees;

label_loop:LOOP
    IF avg_salary >= 12000 THEN LEAVE label_loop;
    END IF;
    UPDATE employees SET salary = salary * 1.1;
    SET loop_count = loop_count + 1;
    SELECT AVG(salary) INTO avg_salary FROM employees;
END LOOP label_loop;

SET num = loop_count;
END //
DELIMITER ;
  • WHILE语句的基本格式:
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
  • REPEAT语句的基本格式:
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
  • 对比三种循环语句:
  1. 这三种循环都可以省略名称,但如果循环中添加了循环控制语句(LEAVE或ITERATE)则必须添加名称
  2. LOOP:一般用于实现简单的"死"循环;WHILE:先判断后执行;REPEAT:先执行后判断,无条件至少执行一次

3、跳转语句

  • LEAVE语句:可以把 LEAVE 理解为 break
LEAVE 标记名
#label参数表示循环的标志
  • ITERATE语句:可以把 ITERATE 理解为 continue
ITERATE label
  • 案例:定义局部变量num,初始值为0,循环结构中执行num + 1操作,如果num < 10,则继续执行循环;如果num > 15,则退出循环结构
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE num INT DEFAULT 0;
    my_loop:LOOP
        SET num = num + 1;
        IF num < 10
        THEN ITERATE my_loop;
        ELSEIF num > 15
        THEN LEAVE my_loop;
        END IF;
    END LOOP my_loop;
END //
DELIMITER ;

三、游标

1、概念

  1. 游标,提供了一种灵活的操作方式,让我们能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构,游标让 SQL 这种面向集合的语言有了面向过程开发的能力
  2. 在 SQL 中,游标是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针。这里游标 充当了指针的作用 ,我们可以通过操作游标来对数据行进行操作

MySQL中游标可以在存储过程和函数中使用

  • 游标优点缺点:
  1. 游标是 MySQL 的一个重要的功能,为 逐条读取 结果集中的数据,提供了完美的解决方案,跟在应用层面实现相同的功能相比,游标可以在存储程序中使用,效率高,程序也更加简洁
  2. 但同时也会带来一些性能问题,比如在使用游标的过程中,会对数据行进行 加锁 ,这样在业务并发量大的时候,不仅会影响业务之间的效率,还会 消耗系统资源 ,造成内存不足,这是因为游标是在内存中进行的处理

2、使用

  • 声明游标:
DECLARE cursor_name CURSOR FOR select_statement;
#适用于 MySQL,SQL Server,DB2 和 MariaDB

注:select_statement 代表的是SELECT 语句,返回一个用于创建游标的结果集

  • 打开游标:
OPEN cursor_name
  • 使用游标:
FETCH cursor_name INTO var_name [, var_name] ...

注:使用 cursor_name 这个游标来读取当前行,并且将数据保存到 var_name 这个变量中,游标指针指到下一行。如果游标读取的数据行有多个列名,则在 INTO 关键字后面赋值给多个变量名即可

  • 关闭游标:
CLOSE cursor_name

注:游标会占用系统资源 ,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率

  • 案例:创建存储过程“get_count_by_limit_total_salary()”,函数的功能可以实现累加薪资最高的几个员工的薪资值,直到薪资总和达到limit_total_salary参数的值,返回累加的人数给total_count
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)
BEGIN
    DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
    DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
    DECLARE emp_count INT DEFAULT 0; #记录循环个数
    #定义游标
    DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    #打开游标
    OPEN emp_cursor;
    REPEAT
        #使用游标(从游标中获取数据)
        FETCH emp_cursor INTO cursor_salary;
        SET sum_salary = sum_salary + cursor_salary;
        SET emp_count = emp_count + 1;
    UNTIL sum_salary >= limit_total_salary
    END REPEAT;
    SET total_count = emp_count;
    #关闭游标
    CLOSE emp_cursor;
END //
DELIMITER ;

四、触发器

在实际开发中,我们经常会遇到这样的情况:有 2 个或者多个相互关联的表,如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性,必须同时在库存表中添加一条库存记录

创建一个触发器,让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来,就不用担心因为忘记添加库存数据而导致的数据缺失了

1、触发器概念

  1. MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样,都是嵌入到MySQL服务器的一段程序
  2. 触发器是由 事件来触发 某个操作,这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序,当数据库执行这些语句时候,就相当于事件发生了,就会 自动 激发触发器执行相应的操作
  3. 当对数据表中的数据执行插入、更新和删除操作,需要自动执行一些数据库逻辑时(例如执行添加日志等),可以使用触发器来实现

2、触发器使用

  • 创建触发器:
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
  1. 表名 :表示触发器监控的对象
  2. BEFORE|AFTER :表示触发的时间,BEFORE 表示在事件之前触发;AFTER 表示在事件之后触发
  3. INSERT|UPDATE|DELETE :表示触发的事件
  4. INSERT 表示插入记录时触发/UPDATE 表示更新记录时触发/DELETE 表示删除记录时触发
  5. 当执行语句块只有一个动作时可以不用添加BEGIN-END块
  • 案例:创建名称为before_insert的触发器,向test_trigger数据表插入数据之前,向test_trigger_log数据表中插入before_insert的日志信息
DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');
END //
DELIMITER ;
#或者 触发单一动作
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');

image-20230401231646432

  • 查看数据库触发器信息:
#查看当前数据库的所有触发器的定义  
SHOW TRIGGERS\G
#查看当前数据库中某个触发器的定义  
SHOW CREATE TRIGGER 触发器名
#从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息  
SELECT * FROM information_schema.TRIGGERS;
  • 删除触发器:
DROP TRIGGER IF EXISTS 触发器名称;

3、触发器的优缺点

  • 优点:

1、触发器可以确保数据的完整性

2、触发器可以帮助我们记录操作日志

3、触发器还可以用在操作数据前,对数据进行合法性检查

  • 缺点:

1、触发器最大的一个问题就是可读性差

2、相关数据的变更,可能会导致触发器出错

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.mfbz.cn/a/7242.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

RocketMQ水平扩展及负载均衡详解

文章目录 Broker端水平扩展Broker负载均衡commit logProducer负载均衡Consumer负载均衡集群模式广播模式RocketMQ是一个分布式具有高度可扩展性的消息中间件。本文旨在探索在broker端,生产端,以及消费端是如何做到横向扩展以及负载均衡的。 Broker端水平扩展 Broker负载均衡…

前端项目-05-轮播图banner和Floor组件开发-全局轮播图组件抽取

目录 1-轮播图模块数据开发 2-floor组件开发 3-抽取全局轮播图组件 1-轮播图模块数据开发 轮播图需要用到swiper插件&#xff0c;先安装5.4.5版本的swiper&#xff1a;npm install --save swiper^5.4.5 --force 模拟从服务器获取数据&#xff1b; 1-编写mockRequests的js…

【ACWing算法课】二分查找

前言&#x1f349; 二分查找一个简单的算法&#xff0c;但是因为边界问题往往写不好。特此记录模板&#xff0c;以便快捷使用。 [二分查找]从列表q找到第一个>k的数&#xff0c;返回位置&#x1f451; [二分查找]从列表q找到第一个>k的数&#xff0c;返回位置def bsear…

three.js实现3d球体树状结构布局——树状结构的实现

目录系列文章安装依赖基本分析实体类场景相机渲染器辅助线环境光点光源球形几何体球形几何体的材质线几何体线几何体的材质物体文本轨道控制实现效果实现源码参考文档系列文章 three.js实现3d球体树状结构布局——添加入场、出场、点击放大等动画 安装依赖 npm i three three…

Adaptive AUTOSAR——Time Synchronization(VRTE 3.0 R21-11)

15 Time Synchronization 15.1 What is Time Synchronization? 时间同步是自适应平台基础中的一个功能集群。时间同步通过库向应用程序提供C API&#xff0c;该库作为RTA-VRTE入门套件的一部分提供&#xff0c;并与应用程序链接以访问该功能。 本版本包含非常少量的时间同步…

ASIC-WORLD Verilog(1)一日Verilog

写在前面 在自己准备写一些简单的verilog教程之前&#xff0c;参考了许多资料----asic-world网站的这套verilog教程即是其一。这套教程写得极好&#xff0c;奈何没有中文&#xff0c;在下只好斗胆翻译过来&#xff08;加了自己的理解&#xff09;分享给大家。 这是网站原文&…

Helm学习笔记

文章目录概念定义helm组件helm的工作流程helm安装helm仓库helm部署应用helm应用的更新或回退或卸载概念 定义 学习helm首先得了解helm是什么&#xff0c;我们先来看一下helm的定义&#xff1a;helm是将kubernetes的各种资源对象打包&#xff0c;类似于Linux中的yum工具&#…

【HTML系列】第六章 · 框架标签、HTML实体、HTML全局属性和meta元信息

写在前面 Hello大家好&#xff0c; 我是【麟-小白】&#xff0c;一位软件工程专业的学生&#xff0c;喜好计算机知识。希望大家能够一起学习进步呀&#xff01;本人是一名在读大学生&#xff0c;专业水平有限&#xff0c;如发现错误或不足之处&#xff0c;请多多指正&#xff0…

【前端面试题——微信小程序】

目录1.请谈谈wxml与标准的html的异同&#xff1f;2.请谈谈WXSS和CSS的异同&#xff1f;3.请谈谈微信小程序主要目录和文件的作用&#xff1f;4.请谈谈小程序的双向绑定和vue的异同&#xff1f;5.简单描述下微信小程序的相关文件类型&#xff1f;6.微信小程序有哪些传值(传递数据…

jsp+javaEE+mysql校园物品租赁系统dzkf5294程序

1&#xff0e;物品信息管理&#xff1a;管理员发布物品信息后&#xff0c;普通用户便可以查询到该物品信息&#xff0c;用户选择某个物品信息&#xff0c;查询物品信息&#xff0c;管理员审核添加&#xff0c;或删除物品信息。 2&#xff0e;租赁管理&#xff1a;管理员发布租赁…

ChatGPT大解密:带您探讨机器学习背后的秘密、利用与发展

一、什么是机器学习&#xff1f;二、ChatGPT 的运作原理三、ChatGPT 生活利用1、自然语言处理2、翻译3、自动回复四、ChatGPT vs 其他相关技术五、ChatGPT 的未来1、未来发展2、职业取代3、客服人员4、翻译人员5、语言学家6、机遇与挑战六、结语这篇文章&#xff0c;将带着各位…

ThreeJS-投影、投影模糊(十七)

无投影&#xff1a; 完整的代码&#xff1a; <template> <div id"three_div"></div> </template> <script> import * as THREE from "three"; import { OrbitControls } from "three/examples/jsm/controls/Or…

再不转型为ChatGPT程序员,有遭受降维打击的危险

Open AI在演示GPT-4的时候&#xff0c;有这么一个场景&#xff1a;给一个界面草图&#xff0c;就可以生成网页代码。这个演示非常简单&#xff0c;如果界面原型比较复杂呢&#xff1f;像这样&#xff1a;ChatGPT能不能直接生成HTML, CSS,JavaScript代码&#xff0c;把这个网页给…

MongoDB副本集Command failed with error 10107 (NotMaster):on server

问题 通过DataGrip连接的MongoDB节点&#xff0c;之前可以新增或更新数据。某天突然不能新增或更新数据&#xff0c;报错信息如下&#xff1a; 具体的报错信息&#xff1a; Command failed with error 10107 (NotMaster): not master on server 10.19.21.11:30386. The full…

HNU-电路与电子学-实验3

实验三 模型机组合部件的实现&#xff08;二&#xff09;&#xff08;实验报告格式案例&#xff09; 班级 计XXXXX 姓名 wolf 学号 2021080XXXXX 一、实验目的 1&#xff0e;了解简易模型机的内部结构和工作原理。 2&#xff0e;分析模型机的功能&am…

【Linux】LVM与磁盘配额

文章目录1.LVM1.1 LVM概述1.2 LVM机制1.3 LVM管理命令1.4 LVM应用实例2. 磁盘配额2.1 磁盘配额概述2.2 磁盘配额管理2.3 启用磁盘配额支持2.4 磁盘配额应用实例1.LVM 1.1 LVM概述 Logical Volume Manager&#xff0c;逻辑卷管理 ● 能够在保持现有数据不变的情况下动态调整磁盘…

43掌握自动化运维工具 Puppet 的基本用法,包括模块编写、资源管理

Puppet是一种自动化配置管理工具&#xff0c;可以自动化地部署、配置和管理大规模服务器环境。本教程将介绍Puppet的基本用法&#xff0c;包括模块编写和资源管理。 模块编写 在Puppet中&#xff0c;模块是一组相关的类、文件和资源的集合。模块可以用于部署和配置应用程序、服…

图形编辑器:排列移动功能的实现

大家好&#xff0c;我是前端西瓜哥。这次来实现一下图形编辑器排列&#xff08;arrange&#xff09;功能的实现。 先看效果。 有四种移动方式&#xff1a; 置顶&#xff08;Front&#xff09;&#xff1a;将指定的图形移动到顶部&#xff1b;置底&#xff08;Back&#xff09…

嵌入式学深度学习:1、Pytorch框架搭建

嵌入式学深度学习&#xff1a;1、Pytorch框架搭建1、介绍2、Pytorch开发环境搭建2.1、查看GPU是否支持CUDA2.2、安装Miniconda2.3、使用Conda安装pytorch2.4、安装常用库3、简单使用验证1、介绍 深度学习是机器学习的一种&#xff0c;如下图&#xff1a; 目前深度学习应用场景…

MySQL-四大类日志

目录 &#x1f341;MySQL日志分为4大类 &#x1f341;错误日志 &#x1f343;修改系统配置 &#x1f341;二进制日志 &#x1f343;查看二进制日志 &#x1f343;删除二进制日志 &#x1f343;暂时停止二进制日志的功能 &#x1f341;事务日志(或称redo日志) &#x1f341;慢查…
最新文章