MySQL进阶知识：二

目录

视图

基本语法

视图的更新

视图的作用

存储过程

介绍

存储过程基本语法

存储过程的变量

系统变量

用户自定义变量

局部变量

存储过程的判断逻辑

存储过程的参数

存储过程中的流程控制

存储过程中的循环

while的基本语法

repeat的基本语法

loop的基本语法

游标

条件处理程序

存储函数

触发器

总结

视图

视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的进，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

基本语法

创建视图语法

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]

查询视图语句

# 查看视图创建语句
SHOW CREATE VIEW 视图名称;
# 查看视图数据
SELECT * FROM 视图名称 ……;

修改视图语句

# 方式一: 
CREATE [OR REPLACE] VEW [视图名称(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]
# 方式二: 
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED | LOCAL] CHECK OPTION]

删除视图语句

DROP VIEW [IF EXISTS] 视图名称;

我们可以操作视图表想操作基本表一样，如果对视图表执行insert或是update语句，实际上都是对基本表进行操作。

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和 LOCAL，默认值为 CASCADED

CASCADED：级联

如果指定了WITH配置项，那么在执行插入，更新，删除时，会进行自身以及依赖的视图表的条件判断，如果满足才会执行成功，不满足执行失败。

LOCAL：本地

如果指定了WITH配置项，那么在执行插入，更新，删除时，只会进行自身以及依赖的视图表中同样添加了WITH配置项的条件判断，如果满足才会执行成功，不满足执行失败。

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新:

聚合函数或窗口函数(SUM()、 MIN()、MAX()、COUNT()等)

  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION 或者 UNION ALL

视图的作用

  • 简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

  • 数据独立

视图可以帮助用户屏蔽真实表结构变化带来的影响。

存储过程

现在存在一个业务，需要查询数据库后，需要对多条数据进行修改。这就意味着，客户端需要发起多次网络请求。而我们可以通过对SQL语句的封装，实现一次请求就实现业务需求。

介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点如下：

  • 封装，复用。
  • 可以接受参数，也可以返回数据。
  • 减少网络交互，效率提升。

存储过程基本语法

创建存储过程

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
	--SQL语句
END;

调用存储过程

CALL 名称([参数]);

查看存储过程

 --查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名';
 -- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

删除存储过程

DROP PROCEDURE [IF EXISTS] 存储过程名;

需要配注意的是，如果使用命令行创建存储过程，那么在输入SQL语句后添加分号后会认为语句已经输出完毕，不会再记录END;命令。因此我们需要修改MySQL的结束符

delimiter 指定结束符号

存储过程的变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量 (GLOBAL)、会话变量(SESSION)

全局变量的生效范围，两个查询控制台都可以访问到。

会话变量的生效范围，只能在当前控制台进行访问。

查看系统变量

-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;
-- 可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......'; 
-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;

设置系统变量

SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL] 系统变量名 = 值;

注意：

  • 如果没有指定SESSION | GLOBAL，默认是SESSION会话变量。
  • mysql服务重新启动之后，所设置的全局参数会失效要想不失效，可以在 /etc/my.cnf 中配置

用户自定义变量

用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

赋值语句

# 推荐使用:= 因为可以与比较符号区分开
SET @var_name = expr [,@var_name = expr];
SET @var_name := expr [, @var name := expr];
SELECT @var_name := expr [, @var name := expr];
SELECT 字段名 INTO @var_name FROM 表名; -- 将表中查找的值赋值给该变量

使用语句

SELECT @var_name;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN... END块。

声明语法

DECLARE 变量名 变量类型[DEFAULT ...];

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

存储过程的判断逻辑

if判断基本语法

IF 条件1 THEN
	……
ELSEIF 条件2 THEN
	……
ELSE
	……
END IF;

存储过程的参数

类型

含义

IN（默认）

该类参数作为输入，也就是需要调用时传入值

OUT

该类参数作为输出，也就是该参数可以作为返回值

INOUT

既可以作为参数，也可以作为输出参数

使用语法

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
	--SQL语句
END;

 

传入参数为score，返回参数为result

调用参数，并使用自定义变量接收，查询返回结果。

存储过程中的流程控制

case的基本语法

-- 语法一
CASE case_value
	WHEN when_value1 THEN statement_list1
	[WHEN when_value2 THEN statement_list2] …
	[ELSE statement_list]
END CASE;
-- 语法二
CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2]…
  [ELSE statement_list]
END CASE;

存储过程中的循环

while的基本语法

WHILE 条件 DO
	SQL逻辑
EDN WHILE;

repeat的基本语法

-- 与WHILE的区别在于，满足条件退出循环，相当于do……while
REPEAT
	SQL逻辑…
  UNTIL 条件
END REPEAT;

loop的基本语法

LOOP实现简单的循环：如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用

  • LEAVE：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
[begin_label:]LOOP
	SQL逻辑…
END LOOP[end_label];
-- label相当于给循环起名字

 

游标

游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和CLOSE，其语法分别如下

-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标
OPEN 游标名称;
-- 获取游标记录
FETCH 游标名称 INTO 变量[,变量];
-- 关闭游标
CLOSE 游标名称;

案例

从emp表中，获取sal>=2000的数据集。

创建一个新表，并将获取到的数据集存放到新表中

create procedure p(in usvl double)

begin 
	-- 声明变量，必须在游标之前声明
	declare e_ename varchar(10);
	declare e_sal double;
	
	-- 声明游标
	declare e_cursor cursor for select ename,sal from emp where sal >=2000;
	open e_cursor;
	-- 创建新表
	drop table if exists tb_emp;
	create table tb_emp(
		id int primary key auto_increment,
		name varchar(10),
		sal double
	);
	
	while true do
		-- 将游标中值赋值给变量
		fetch e_cursor into e_ename,e_sal;
		insert into tb_emp values(null,e_ename,e_sal);
	end while;
	
	close e_cursor;
end;

call p(2000);

 

可以正常存放，但存在一个问题就是，上述的代码中，while是死循环，我们没有给其结束循环的逻辑。该问题我们在下面讲解handler后解决。

条件处理程序

条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

具体语法为

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;

handler_action

  • CONTINUE：继续执行当前程序
  • EXIT：终止执行当前程序

condition_value

  • SQLSTATE sglstate_value：状态码，如 02000
  • SQLWARNING：所有以01开头的SQLSTATE代码的简写
  • NOT FOUND：所有以02开头的SQLSTATE代码的简写
  • SQLEXCEPTION：所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

根据handler我们可以解决上面游标的while死循环的问题

create procedure p(in usvl double)

begin 
	-- 声明变量，必须在游标之前声明
	declare e_ename varchar(10);
	declare e_sal double;
  
	-- 声明游标
	declare e_cursor cursor for select ename,sal from emp where sal >=2000;
  -- 创建handler
  declare exit handler for sqlstate '02000' close e_cursor;
  
	open e_cursor;
	-- 创建新表
	drop table if exists tb_emp;
	create table tb_emp(
		id int primary key auto_increment,
		name varchar(10),
		sal double
	);
	
	while true do
		-- 将游标中值赋值给变量
		fetch e_cursor into e_ename,e_sal;
		insert into tb_emp values(null,e_ename,e_sal);
	end while;
	
	close e_cursor;
end;

call p(2000);

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下:

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic...]
BEGIN
	-- SQL语句
	RETURN ...;
END ;

characteristic说明

  • DETERMINISTIC：相同的输入参数总是产生相同的结果
  • NO SQL：不包含 SQL 语句。
  • READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

通常我们使用存储过程，而不使用存储函数。因为使用不方便。因此不多叙述

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名 OLD 和NEW 来引用发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

  • 行级触发：一条update语句修改了五条数据，那么触发器触发五次
  • 语句触发：相同的update语句只触发一次。

触发器类型

NEW和OLD

INSERT型触发器

NEW表示将要或是已经新增的数据

UPDATE型触发器

OLD表示修改之前的数据，NEW表示将要或是已经修改的数据

DELETE型触发器

OLD表示将要或是已经删除的数据

基本语法

创建触发器

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
	trigger_stmt ;
END;

查看触发器

SHOW TRIGGERS;

删除触发器

DROP TRIGGER [schema_name.]trigger_name; -- 如果没有指定 schema_name，默认为当前数据库。

接下来测试日志记录

-- 创建日志表
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

-- 创建更新触发器测试
CREATE TRIGGER emp_update_trigger
	AFTER UPDATE ON `emp` FOR EACH ROW
BEGIN
	INSERT INTO user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
	(NULL,'update',NOW(),old.empno,CONCAT('更新前的数据：id=',old.EMPNO,',ename=',old.ename,
	'||更新后的数据：id=',new.EMPNO,',ename=',new.ename));
END;

-- 更新表
UPDATE emp SET ENAME = '张三' WHERE ename = 'SMITH';

 

日志表中成功插入信息。

总结

视图

  • 虚拟存在的表，不保存查询结果，只保存查询的SQL逻辑
  • 简单、安全、数据独立

存储过程

  • 事先定义并存储在数据库中的一段SQL语句的集合
  • 减少网络交互，提高性能、封装重用
  • 变量、if、case、参数(in/out/inout)、while、repeat、loop、cursor、handler

存储函数

  • 存储函数是有返回值的存储过程，参数类型只能为IN类型
  • 存储函数可以被存储过程替代

触发器

  • 可以在表数据进行INSERT、UPDATE、DELETE之前或之后触发
  • 保证数据完整性、日志记录、数据校验

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

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

相关文章

Flink-执行拓扑图与作业调度

Flink-执行拓扑图与作业调度

算子与作业提交 一、Flink执行模式1.流执行模式2.批执行模式 二、Flink拓扑图1.基本概念2.拓扑图生成过程 三、拓扑生成和优化1.应用程序2.逻辑视图3.算子链4.Task Slots 四、作业调度1.调度2.拓扑图数据结构3.Job状态转化4.Task状态转化 总结参考链接 一、Flink执行模式 Flin…
阅读更多...
Python语言学习笔记之四（Python文档化）

Python语言学习笔记之四（Python文档化）

本课程对于有其它语言基础的开发人员可以参考和学习，同时也是记录下来，为个人学习使用，文档中有此不当之处，请谅解。 Python文档化是指在Python代码中添加注释和文档字符串，以提供有关代码的详细信息和说明。 文档的…
阅读更多...
这个变量要不要用volatile修饰呢？

这个变量要不要用volatile修饰呢？

正文 大家好，又见面了，我是bug菌~ 在嵌入式软件开发过程中，如果对volatile不熟，那可以你应该是个"假嵌入式程序员"，因为一个变量需不需要使用volatile考虑的场景挺多的，如果在某些场景下乱用&…
阅读更多...
编写安全 JavaScript 代码的最佳实践

编写安全 JavaScript 代码的最佳实践

编写安全 JavaScript 代码的最佳实践 JavaScript 的动态特性使其成为事实上的浏览器语言和世界上最流行的编程语言。 JS 最受欢迎的有用功能之一是即时分析。这意味着浏览器在下载内容的同时执行代码，这显然有其优势。然而，这种程度的自由也伴随着问题…
阅读更多...
使用STM32微控制器实现光电传感器的接口和数据处理

使用STM32微控制器实现光电传感器的接口和数据处理

光电传感器在许多领域中被广泛应用，例如工业自动化、智能家居等。本文将介绍如何使用STM32微控制器实现光电传感器的接口和数据处理的方案，包括硬件设计、引脚配置、数据采集、滤波和阈值判断等关键步骤，并给出相应的代码示例。 一、引言 光…
阅读更多...
深入浅出 Vue 中的插槽 slot

深入浅出 Vue 中的插槽 slot

深入浅出 Vue 中的插槽 slot start 最近被问到好几次 Vue 中的插槽相关知识，掌握的还是有些不全面。抱着重新学习的心态，写这篇博客。首先对基础知识做一个回顾，然后再对源码实现做一个学习。作者：番茄编写时间：2023…
阅读更多...
【藏经阁一起读】（78）__《Apache Tomcat 的云原生演进》

【藏经阁一起读】（78）__《Apache Tomcat 的云原生演进》

【藏经阁一起读】（78） __《Apache Tomcat 的云原生演进》 目录 __《Apache Tomcat 的云原生演进》 一、读后感 二、文章知识点摘要 2.1、Tomcat的技术内幕和在喜马拉雅的实践 2.2、GraalVM static compilation in web container application&…
阅读更多...
企业级开发链表思路

企业级开发链表思路

项目结构 头文件代码 头文件代码LinkList.h #ifndef LINKLIST_H #define LINKLIST_H #include <stdio.h> #include <stdlib.h> #include <iostream> // 链表小节点 typedef struct LINKBODE {struct LINKBODE* next;}LinkNode; // 遍历的函数指针 typedef …
阅读更多...
UE5、CesiumForUnreal实现加载GeoJson绘制多面（MultiPolygon）功能（支持点选高亮）

UE5、CesiumForUnreal实现加载GeoJson绘制多面（MultiPolygon）功能（支持点选高亮）

文章目录 1.实现目标2.实现过程2.1 数据与预处理2.2 GeoJson解析2.3 Mesh构建与属性存储2.4 核心代码2.5 材质2.6 蓝图应用测试3.参考资料1.实现目标 在之前的文章中，基于GeoJson数据加载，实现了绘制单面功能，但只支持单个要素Feature。本文这里实现对Geojson内所有面要素的…
阅读更多...
Python字典类型

Python字典类型

目录 目标 版本 官方文档 简介 实战 创建 循环 常用方法 目标 掌握字典类型的使用方法&#xff0c;包括&#xff1a;创建、循环、常用方法等操作。 版本 Python 3.12.0 官方文档 Mapping Types — dicthttps://docs.python.org/3/library/stdtypes.html#mapping-type…
阅读更多...
绝地求生：成长型皮肤异色定价是否有些夸张？

绝地求生：成长型皮肤异色定价是否有些夸张？

大家好&#xff0c;我闲游盒小盒子&#xff01; 自从26.2更新上架回归的黑市中四款成长型皮肤以后&#xff0c;能看到社区里很多玩家都分享抽中了自己心仪的成长型皮肤。 但是对于异色很少有人去实装&#xff0c;大多数玩家都是选择去分解异色换取五张图纸然后追求升级原皮等级…
阅读更多...
【PyQt】(自定义类)阴影遮罩

【PyQt】(自定义类)阴影遮罩

写了一个感觉有些用的小玩具。 用于给控件添加阴影遮罩(强调主控件的同时屏蔽其余控件的点击) 自定义阴影遮罩Mask&#xff1a; from PyQt5.QtCore import QPoint,QRect,Qt,QPoint,QSize from PyQt5.QtWidgets import QWidget,QLabel,QPushButton,QVBoxLayout from PyQt5.QtGu…
阅读更多...
L型骨牌覆盖问题。

L型骨牌覆盖问题。

问题&#xff1a;解决一个2k*2k的特殊棋牌上的L型骨牌覆盖问题。 思路&#xff1a; 棋盘覆盖实现的基本方法为分治法 当k0时(1ⅹ1棋盘)&#xff0c;及特殊方格&#xff0c;骨牌数为0 当k >0时&#xff0c;将2kⅹ2k棋盘分割为4个2k-1ⅹ2k-1子棋盘了 特殊方格位于4个较小…
阅读更多...
【3D程序软件】SideFX与上海道宁一直为设计师提供程序化 3D动画和视觉效果工具，旨在创造高质量的电影效果

【3D程序软件】SideFX与上海道宁一直为设计师提供程序化 3D动画和视觉效果工具，旨在创造高质量的电影效果

Houdini是一个 从头开始构建的程序系统 使艺术家能够自由工作 创建多次迭代 并与同事快速共享工作流程 Houdini FX为 视觉特效艺术家创作故事片 广告或视频游戏 凭借其基于程序节点的工作流程 Houdini FX可让 您更快地创建更多内容 从而缩短时间并 在所有创意任务中…
阅读更多...
制作电脑微信双开快捷方式

制作电脑微信双开快捷方式

一、介绍 电脑下载的微信正常只能打开一个&#xff0c;那有时候需要双开甚至多开微信我们需要怎么操作呢&#xff1f; 我这里就讲一个制作微信双开快捷键方式的办法&#xff0c;争对其他应用也是一样的原理。 二、制作过程 1、右击微信快捷方式 2、点击属性 3、复制目标 …
阅读更多...
微服务实战系列之Cache

微服务实战系列之Cache

前言 欢迎来到Cache&#xff08;缓存&#xff09;的世界&#xff01; 自从世界第一台计算机诞生之日起&#xff0c;人们对效率的渴望逐步增强。从CPU到存储&#xff0c;从芯片到内存&#xff0c;一批又一批的先驱以一种孜孜不倦的“工匠”精神&#xff0c;为计算机运行效率的提…
阅读更多...
MyBatis-Plus及多数据源入门教程

MyBatis-Plus及多数据源入门教程

开发环境配置 JDK 1.8、Maven 3.8.8、 IDEA CE 2023.2、MySQL 8.0.34 框架介绍 MyBatis-Plus&#xff08;简称 MP&#xff09;是一个 MyBatis的增强工具&#xff0c;在 MyBatis 的基础上只做增强不做改变&#xff0c;为简化开发、提高效率而生。MyBatis 是一款非常优秀的开源…
阅读更多...
快速了解Spring AOP的概念及使用

快速了解Spring AOP的概念及使用

文章目录 1. AOP概念1.1 什么是AOP&#xff1f;1.2 什么是Spring AOP&#xff1f; 2. Spring AOP的使用2.1 引入Spring AOP依赖2.2 编写AOP程序 3. Spring AOP详解3.1 Spring AOP核心概念1. 切点&#xff08;Pointcut&#xff09;2. 连接点&#xff08;Join Point&#xff09;3…
阅读更多...
C++ day37 贪心算法 单调递增的数字 监控二叉树

C++ day37 贪心算法 单调递增的数字 监控二叉树

题目1&#xff1a;738 单调递增的数字 题目链接&#xff1a;单调递增的数字 对题目的理解 返回小于或等于n的最大数字&#xff0c;且数字是单调递增&#xff08;单调递增数字的定义&#xff1a;每个相邻位上的数字满足x<y&#xff09; 贪心算法 注意本题的遍历顺序是从…
阅读更多...
【算法刷题】Day7

【算法刷题】Day7

文章目录 283. 移动零1089. 复写零 283. 移动零 原题链接 看到题目&#xff0c;首先看一下题干的要求&#xff0c;是在原数组内进行操作&#xff0c;平切保持非零元素的相对顺序 这个时候我们看到了示例一&#xff1a; [ 0, 1, 0, 3,12 ] 这个时候输出成为了 [ 1, 3, 12, 0, …
阅读更多...
最新文章

Copyright @ 2022~2023