网络安全|mysql中的视图、触发器和存储过程数据库|视图|触发器|存储过程

mysql中的视图、触发器和存储过程

1.视图
- 1.1 什么是视图
- 1.2 视图的特点
- 1.3 视图的使用场景
- 1.4 语法
- - 1）创建视图
  - 2）查询
  - 3）修改
  - 4）删除
- 1.5 视图的优点
- 1.6 视图的缺点
2.触发器
- 2.1 什么是触发器
- 2.2 语法
- - 1）创建
  - 2)查看
  - 3)删除
- 2.3 触发器的使用场景
- 2.4 Mysql中的触发器
3.存储过程
- 3.1 什么是存储过程
- 3.2 语法
- - 1）创建
  - 2）调用
  - 3）查看
  - 4）删除
- 3.3 变量
- - 1）系统变量
  - 2）用户定义变量
  - 3）局部变量
- 3.3 存储过程的优点
- 3.4 存储过程的缺点

1.视图 1.1 什么是视图为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。
视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。
视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，而不是视图所引用表中的数据，从而提高了数据库中数据的安全性。
1.2 视图的特点视图的特点如下:
【网络安全|mysql中的视图、触发器和存储过程】视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，不允许添加和删除数据。视图的操作包括创建视图，查看视图，删除视图和修改视图。
1.3 视图的使用场景视图根本用途：简化sql查询，提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。
下面是视图的常见使用场景：重用SQL语句；
简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；使用表的组成部分而不是整个表；
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
1.4 语法 1）创建视图

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

2）查询

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

3）修改

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

4）删除

DROP VIEW [ IF EXISTS ] 视图名称 [ ,视图名称] ……;

1.5 视图的优点 1、查询简单化：视图能简化用户的操作。
2、数据安全性：视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性。
1.6 视图的缺点 1、性能：数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
2、修改限制：当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的，这些视图有如下特征：
有UNIQUE等集合操作符的视图。
有GROUP BY子句的视图。
有诸如AVG\SUM\MAX等聚合函数的视图。
使用DISTINCT关键字的视图。
连接表的视图。
2.触发器 2.1 什么是触发器触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

文章图片

2.2 语法 1）创建

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

2)查看

show tirggers;

3)删除

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

2.3 触发器的使用场景

可以通过数据库中的相关表实现级联更改。实时监控某张表中的某个字段的更改而需要做出相应的处理。例如可以生成某些业务的编号。注意不要滥用，否则会造成数据库及应用程序的维护困难。

2.4 Mysql中的触发器

Before Insert After Insert Before Update After Update Before Delete

3.存储过程 3.1 什么是存储过程存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
3.2 语法 1）创建

create procedure 存储过程名称([参数列表]) begin ? --sql语句 end;

2）调用

call 名称([参数])；

3）查看

select * from information_schema.routines where routine_schema ='xxx'; --查询指定函数==数据库的存储过程及状态信息show create procedure 存储过程名称； --查询某个存储过程的定义

4）删除

drop procedure [ if exists ] 存储过程名称;

3.3 变量在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
1）系统变量
系统变量：是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL），会话变量（SESSION）。
(1)查看系统变量

文章图片
(2)设置系统变量

文章图片
注意：如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。
2）用户定义变量
用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以。其作用域为当前连接。
(1)赋值
方式一：

文章图片
赋值时，可以使用 = ，也可以使用 := 。推荐使用 := 赋值，因为在if判断语句中，判断两个值是否相等使用“=”，避免赋值和相等判断混淆使用。
方式二：

文章图片
(2)使用

文章图片

注：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
3）局部变量
局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN … END块。
(1)声明

文章图片
变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
(2)赋值

文章图片
（3）参数
参数的类型，主要分为以下三种：IN、OUT、INOUT。具体的含义如下：

文章图片
用法：

文章图片

（4）if
if 用于做条件判断，具体的语法结构为：

文章图片

在if条件判断的结构中，ELSE IF 结构可以有多个，也可以没有。 ELSE结构可以有，也可以没有。
3.3 存储过程的优点 1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，减少数据库开发人员的工作量。
3.4 存储过程的缺点 1）调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4）如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。