数据库对象介绍与实践:视图、函数、存储过程、触发器和物化视图

2023-12-13 05:11:23

一、视图(View)

1、概念

视图是数据库中的一个虚拟表,它是基于一个或多个表的查询结果构建的。视图可以被视为存储在数据库中的预定义查询。通过创建视图,您可以简化复杂的查询,隐藏底层表的细节,并提供更简洁和易于理解的数据访问方式。视图可以像表一样使用,可以查询、插入、更新和删除视图中的数据,但实际上它们并不存储任何数据。

2、基本操作

1)创建视图

语法如下:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明:

  • CREATE: 这是创建视图的关键字。
  • OR REPLACE: 这是一个可选的选项,表示如果已经存在同名的视图,则替换它。
  • ALGORITHM: 这也是一个可选的选项,用于指定用于视图的算法。它可以取UNDEFINED、MERGE或TEMPTABLE这三种值。UNDEFINED表示由数据库选择算法,这是默认值。MERGE表示将使用与普通查询相同的算法。TEMPTABLE表示将创建一个临时表,并将查询结果存储在该临时表中。
  • DEFINER: 这是可选的选项,用于指定定义视图的用户。通常,这个值应该是创建视图的用户的用户名。
  • SQL SECURITY: 这也是可选的选项,用于指定视图的安全性选项。它可以是DEFINER或INVOKER,表示视图的定义者和调用者的安全级别。
  • VIEW: 这是创建视图的关键词。view_name: 这是要创建的视图的名称。column_list: 可选的参数,用于指定视图的列名。如果省略,则视图的列名将与查询语句的输出列名相同。
  • AS select_statement: 这是指定视图内容的语句。视图的内容是通过一个或多个SELECT语句定义的。
  • WITH [CASCADED | LOCAL] CHECK OPTION: 可选的选项,用于指定对视图进行插入、更新和删除操作的约束。如果选择CASCADED,则将对视图的所有后续查询都强制执行该约束。如果选择LOCAL,则只对直接查询该视图的用户强制执行该约束。如果省略,则默认为CASCADED。

2)修改视图

语法如下:

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

参数和创建视图差不多,只不过创建是关键字CREATE,修改是ALTER

3)删除视图

语法如下:

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

参数说明:

  • DROP VIEW: 这是删除视图的关键字。
  • IF EXISTS: 这是一个可选的参数,用于指定在尝试删除视图之前是否检查视图是否存在。如果存在,则执行删除操作,否则忽略该操作。
  • view_name: 这是要删除的视图的名称。您可以指定一个或多个视图名称,用逗号分隔。
  • RESTRICT: 这是一个可选的参数,用于指定如果存在依赖于该视图的任何对象(例如其他视图或存储过程),则不允许删除该视图。如果未指定该参数,并且存在依赖于该视图的对象,则删除操作将被禁止。
  • CASCADE: 这是一个可选的参数,用于指定删除视图以及所有依赖于该视图的对象。如果未指定该参数,并且存在依赖于该视图的对象,则删除操作将被禁止。

例子:DROP VIEW IF EXISTS view1, view2 CASCADE;

这个示例将尝试删除名为view1和view2的两个视图。如果这两个视图存在,则它们将被删除,并且所有依赖于它们的对象也将被删除(因为指定了CASCADE参数)。如果这两个视图不存在,则该命令将被忽略(因为指定了IF EXISTS参数)。

4)使用视图

使用视图时,可以像使用表一样进行查询操作,例如:SELECT * FROM my_view;

3、使用场景

  1. 简化复杂查询:视图可以封装复杂的查询逻辑,简化应用程序中的查询操作,提高查询的可读性和复用性。

  2. 数据安全性和权限控制:通过视图可以隐藏底层表的结构和数据,只向用户暴露必要的信息,从而提高数据安全性和权限控制。

  3. 简化应用程序逻辑:视图可以在数据库层面实现一些通用的逻辑,简化应用程序中的代码,提高代码复用性。

  4. 数据报表和分析:视图可以用于创建数据报表和分析视图,方便用户进行数据分析和报表生成。

  5. 数据结构的抽象:视图可以将多个表的数据结构抽象为一个虚拟的表,简化数据访问和操作。

总的来说,MySQL视图适合于需要简化复杂查询、提高数据安全性、简化应用程序逻辑和进行数据报表分析的场景。

4、实践

这里我们将下面这个复杂查询写到视图里面
在这里插入图片描述
首先创建视图

CREATE VIEW vw_forlan_student AS SELECT
 A.`name`,A.age,B.class_name 
FROM
 forlan_student A INNER JOIN forlan_class B ON A.class_id = B.id;

然后,直接查询即可,可以看到效果,和前面的复杂查询得到了一模一样的内容
在这里插入图片描述
那么,我们直接查和通过视图查,有啥区别,我们先来看看执行计划,发现没啥不同?
在这里插入图片描述
但总的来说,官方是给出了以下区别:

  • 可读性:将复杂SQL定义在视图中可以提高查询语句的可读性。通过将复杂的逻辑封装在视图中,可以使查询语句更简洁、易于理解和维护。
  • 重用性:定义视图后,可以在多个查询中重复使用该视图,而不需要重复编写复杂的SQL语句。这样可以提高代码的重用性和减少代码冗余。
  • 安全性:通过将复杂SQL定义在视图中,可以限制用户对底层数据的直接访问。只有授予对视图的访问权限的用户才能查询视图中的数据,而无法直接访问底层表。
  • 性能:在某些情况下,将复杂SQL定义在视图中可能会提高查询性能。视图可以使用索引和其他优化技术来加速查询,而直接查询复杂SQL可能需要更多的计算资源和时间。

需要注意的是,将复杂SQL定义在视图中也可能带来一些额外的开销,例如视图的创建和维护成本。因此,在选择使用哪种方式时,需要综合考虑查询的复杂性、可读性、重用性和性能等因素。

二、函数(Function)

1、概念

函数是一段可重复使用的代码,用于执行特定的操作或计算,并返回结果。函数可以接受参数,并根据这些参数执行一系列的操作,最后返回一个结果。

MySQL提供了许多内置函数,用于执行各种操作,例如数学计算、字符串处理、日期和时间操作等。除了内置函数,MySQL还支持自定义函数,以满足特定的需求。

以下是一些常见的MySQL内置函数的示例:

数学函数:例如SUM、AVG、MAX、MIN等用于执行数值计算的函数。
字符串函数:例如CONCAT、SUBSTRING、LENGTH等用于处理字符串的函数。
日期和时间函数:例如NOW、DATE_FORMAT、DATE_ADD等用于处理日期和时间的函数。

2、基本操作

1)创建函数

CREATE FUNCTION function_name ([parameter1 data_type [, parameter2 data_type, ...]])
  RETURNS return_data_type
  [DETERMINISTIC]
  [COMMENT 'string']
  BEGIN
    -- 函数体,包含具体的操作和逻辑
    -- 使用RETURN语句返回结果
  END;

参数说明:

  • CREATE FUNCTION:指示创建一个函数。
  • function_name:自定义函数的名称。
  • parameter1, parameter2, …:函数的参数列表,每个参数由参数名称和数据类型组成。
  • return_data_type:函数的返回值数据类型。可以选择函数返回一个字符串(STRING)、整数(INTEGER)、实数(REAL)或十进制数(DECIMAL)。
  • DETERMINISTIC:可选项,指示函数是否是确定性的(即,对于给定的输入,是否总是返回相同的结果)。
  • COMMENT ‘string’:可选项,用于为函数添加注释。
  • BEGIN和END:用于定义函数体的开始和结束。

拓展- 加载函数

CREATE [AGGREGATE] FUNCTION function_name
    RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

参数说明:

  • CREATE FUNCTION是用于定义一个新函数的语句。当你在这里使用AGGREGATE FUNCTION时,你正在创建一个聚合函数,它会对一组值执行特定的操作。
  • SONAME shared_library_name: 这是指定函数所在的共享库的部分。shared_library_name是你要引用的共享库的名称。共享库是一种存储预先编译的函数代码的方式,这样在调用函数时可以更快地执行。

2)使用函数

函数可以在SQL查询中使用,也可以在存储过程和触发器中使用。一般在SELECT语句中使用函数来计算和转换数据,或者在WHERE子句中使用函数来过滤数据。

例如:SELECT function_name(arguments) FROM table_name;

3、使用场景

  1. 数据处理和计算:函数可以用于执行特定的数据处理、计算和转换,例如日期处理、字符串操作、数学运算等。

  2. 查询优化:通过函数可以封装复杂的查询逻辑,提高查询的可读性和复用性,同时也有助于优化查询性能。

  3. 业务规则和验证:函数可以用于实现特定的业务规则和验证逻辑,例如数据有效性检查、权限控制等。

  4. 简化应用程序逻辑:函数可以在数据库层面实现一些通用的逻辑,简化应用程序中的代码,提高代码复用性。

  5. 自定义聚合函数:通过自定义聚合函数,可以实现特定的数据聚合逻辑,满足特定的业务需求。

总的来说,MySQL函数适合于需要在数据库层面执行特定的数据处理、计算和验证逻辑,以及优化查询性能、简化应用程序逻辑的场景。

4、实践

创建一个函数,判断年龄是否成年,代码如下:

CREATE FUNCTION IsAdult(age INT)
RETURNS VARCHAR(10)
BEGIN
    DECLARE result VARCHAR(10);
    
    IF age >= 18 THEN
        SET result = '成年';
    ELSE
        SET result = '未成年';
    END IF;
    
    RETURN result;
END;

测试效果如下:
在这里插入图片描述

三、存储过程(Stored Procedure)

1、概念

存储过程是一组预定义的SQL语句集合,可以在数据库中进行重复使用。存储过程类似于函数,但不返回结果。它可以接受参数,并且可以包含条件逻辑、循环、异常处理等。

存储过程由以下组成:

  • 存储过程名称:用于唯一标识存储过程的名称。
  • 参数列表:可选项,用于传递给存储过程的输入参数。
  • 存储过程体:包含一系列SQL语句和控制结构,用于实现特定的功能。
  • 存储过程结束标记:表示存储过程的结束。

2、基本操作

1)创建存储过程

CREATE PROCEDURE procedure_name[ (IN parameter1 datatype1 [, OUT|INOUT parameter2 datatype2 [, ...]] ) ]
BEGIN
    -- 存储过程的逻辑代码
END;

参数说明:

  • CREATE PROCEDURE:指示创建一个存储过程。
  • procedure_name:存储过程的名称。
  • parameter1, parameter2, …:存储过程的参数列表,每个参数由参数名称和数据类型组成。
  • IN参数:用于将值传递给存储过程。在存储过程内部,IN参数的值是只读的,不能在存储过程中修改。
  • OUT参数:用于从存储过程中返回值。在存储过程内部,OUT参数的值可以被修改,并在存储过程执行完毕后返回给调用者。
  • INOUT参数:既可以传递值给存储过程,也可以从存储过程中返回值。在存储过程内部,INOUT参数的值可以被修改,并在存储过程执行完毕后返回给调用者。
  • BEGIN和END:用于定义存储过程体的开始和结束。

2)使用存储过程

使用CALL语句来调用存储过程。在调用存储过程时,您可以传递参数值,根据需要指定参数的模式(IN、OUT或INOUT)。

3、使用场景

  1. 数据处理和业务逻辑:存储过程可以封装复杂的数据处理逻辑,例如数据清洗、转换、计算等,以及实现特定的业务规则和流程。

  2. 数据安全性和权限控制:通过存储过程,可以对数据库操作进行封装和控制,限制用户对数据的访问和操作,提高数据安全性。

  3. 提高性能:存储过程可以减少网络通信开销,提高数据库操作的性能,尤其是在需要频繁执行相同逻辑的情况下。

  4. 代码复用和维护:存储过程可以被多个应用程序共享和调用,提高代码的复用性,同时也方便对逻辑进行统一管理和维护。

  5. 批量操作和事务管理:存储过程可以实现批量数据操作,同时也可以管理事务,确保一系列操作的原子性和一致性。

总的来说,存储过程适合于需要在数据库层面实现复杂逻辑、提高性能、增强安全性和方便维护的场景。

4、实践

使用存储过程插入新班级新学生数据

1)创建一个存储过程,用于插入班级信息并返回班级ID

CREATE PROCEDURE insert_class(IN class_name VARCHAR(255), OUT class_id INT)
BEGIN
    INSERT INTO forlan_class (class_name) VALUES (class_name);
    SET class_id = LAST_INSERT_ID();
END;

2)创建另一个存储过程,用于插入学生信息并使用上一步中返回的班级ID:

CREATE PROCEDURE insert_student(IN name VARCHAR(255), IN age INT, IN class_id INT)
BEGIN
    INSERT INTO forlan_student (name, age, class_id) VALUES (name, age, class_id);
END;

3)调用存储过程

CALL insert_class('存储过程学习班', @class_id);
CALL insert_student('forlan', 20, @class_id);

4)通过查看视图验证,可以查到我们刚刚插入的,如下:
在这里插入图片描述

四、触发器(Trigger)

1、概念

触发器是一种数据库对象,它在指定的表上自动执行特定的操作。当满足特定的事件(如插入、更新或删除数据)时,触发器会触发并执行预定义的操作。这些操作可以是SQL语句、存储过程或其他自定义逻辑。触发器通常用于实现数据完整性约束、审计跟踪和自动化业务逻辑等功能

2、基本操作

1) 创建触发器

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name]
    trigger_body

参数说明:

  • DEFINER = user: 这部分指定了触发器的定义者
  • TRIGGER trigger_name:指定触发器的名称,用于标识触发器对象。
  • BEFORE或AFTER:指定触发器在事件之前或之后执行。BEFORE表示在事件执行之前触发,AFTER表示在事件执行之后触发。
  • INSERT、UPDATE或DELETE:指定触发器要响应的事件类型。INSERT表示在插入数据之前或之后触发,UPDATE表示在更新数据之前或之后触发,DELETE表示在删除数据之前或之后触发。
  • ON table_name:指定触发器所属的表名,即触发器要监视的表。
  • trigger_order:指定触发器的执行顺序。
  • FOR EACH ROW:表示触发器将为每一行数据执行操作。这意味着触发器的操作逻辑将在每次插入、更新或删除数据时执行。
  • { FOLLOWS | PRECEDES } other_trigger_name 表示该触发器与其他触发器之间的执行顺序关系

2) 使用触发器

一旦创建触发器,它将自动与指定的表关联。当触发器所监视的事件发生时,触发器的操作逻辑将被执行。

3、使用场景

  1. 数据完整性和约束:触发器可以用于实现数据完整性和约束,例如在插入、更新、删除数据时进行验证和限制。

  2. 数据审计和日志记录:触发器可以用于记录数据变更的日志,包括谁在什么时间做了什么操作。

  3. 数据复制和同步:触发器可以用于在数据变更时触发数据复制和同步操作,确保数据的一致性。

  4. 自动化任务和业务逻辑:触发器可以用于执行特定的业务逻辑和自动化任务,例如在特定条件下触发某些操作。

  5. 数据转换和处理:触发器可以用于在数据变更时进行特定的数据转换和处理,例如数据格式转换、计算字段值等。

总的来说,MySQL触发器适合于需要在特定的数据变更时执行特定的逻辑、记录数据变更日志、实现数据复制和同步、以及执行特定的自动化任务和业务逻辑的场景。

4、实践

我们写一个触发器,来实现当班级信息发生变化时,触发更新学生信息
1)创建触发器

CREATE TRIGGER update_forlan_student
AFTER UPDATE ON forlan_class
FOR EACH ROW
BEGIN
    -- 更新学生表中对应班级的信息
    UPDATE forlan_student
    SET class_name = NEW.class_name
    WHERE class_id = NEW.id;
END;

注:NEW关键字表示触发器所监视的事件中的新数据
2)修改班级信息

UPDATE `forlan_class` SET `class_name` = '触发器学习班' WHERE `id` = 7

3)查看触发器监情况,效果如下:
在这里插入图片描述

五、物化视图(Materialized View)

1、概念

在MySQL中,物化视图(Materialized View)是一种预先计算和存储的查询结果集。与普通视图不同,物化视图在创建时会将查询结果保存在磁盘上,而不是在每次查询时动态计算。这样可以显著提高查询性能,并减少对原始表的查询次数。

总的来说,通常用于存储预先计算的结果,以提高查询性能。然而,MySQL本身并不直接支持物化视图,但可以通过函数、存储过程、触发器、视图来实现类型的功能

2、使用场景

  1. 复杂查询优化:当数据库中存在复杂的查询,需要多个表的联接和聚合操作时,可以使用物化视图来预先计算并存储查询结果,以提高查询性能。
  2. 数据汇总和报表生成:物化视图可以用于汇总和聚合大量数据,以生成报表和分析结果。通过预先计算和存储汇总数据,可以加快报表生成的速度。
  3. 数据仓库和决策支持系统:在数据仓库和决策支持系统中,物化视图可以用于存储预先计算的指标和维度数据,以支持复杂的分析和决策过程。
  4. 实时数据更新:物化视图可以定期或实时地更新,以保持与源数据的同步。这对于需要实时或近实时数据的应用程序非常有用。

需要注意的是,物化视图的创建和维护可能会增加数据库的存储空间和更新成本。因此,在选择使用物化视图时,需要权衡查询性能的提升和资源消耗之间的平衡。

3、实践

学生表如下:

CREATE TABLE `forlan_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(200) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `class_id` bigint(20) DEFAULT NULL COMMENT '所属班级',
  `class_name` varchar(255) DEFAULT NULL COMMENT '所属班级名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生信息表';

假设我们需要在页面查看是否成年,这时候会有多种实现方案,我们来分析下:

  • 方案一:代码里面写if…else…判断,但这样每次查询都要计算一遍
  • 方案二:在学生表新增一个字段,但这不符合数据库第三范式了,因为是否成年”可以由“年龄”推出,具体数据库范式可以去看数据库基础概念与范式反范式总结
  • 方案三:新增一张表,但是需要改代码,增删改,都要重新计算去维护多一张表信息,挺麻烦的,这里就可以通过物化视图来弄,自动触发维护即可

1)创建学生年龄表


CREATE TABLE `materialized_student_adult_status` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `student_id` bigint(20) DEFAULT NULL COMMENT '学生id',
  `is_adult` varchar(255) DEFAULT NULL COMMENT '是否成年',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生年龄表';

2)创建一个存储过程,用于更新物化视图的数据:

CREATE PROCEDURE update_student_adult_status()
BEGIN
    DELETE FROM materialized_student_adult_status;
    INSERT INTO materialized_student_adult_status (student_id, is_adult)
    SELECT id, isAdult(age) FROM forlan_student;
END;

注:isAdult是我们前面自定义的函数

3)创建一个触发器,当学生表中的年龄字段发生变化时,自动触发更新物化视图的存储过程:

CREATE TRIGGER student_age_update_trigger
AFTER UPDATE ON forlan_student
FOR EACH ROW
BEGIN
    IF NEW.age <> OLD.age THEN
        CALL update_student_adult_status();
    END IF;
END;

4)创建一个普通视图,用于展示我们需要的信息

CREATE VIEW vw_student_adult_status AS
SELECT A.*,B.is_adult
FROM forlan_student A JOIN materialized_student_adult_status B ON A.id = B.student_id;

5)查看效果,是否返回了学生表的信息,还返回了对应的是否成年字段,而且我们修改了年龄后,自动维护了is_adult字段
在这里插入图片描述

六、总结

从上面的理论与实现相结合,我们大概知道了视图、函数、存储过程、触发器和物化视图是干嘛的,总结如下:

  • 视图:一般用于预定义一些复杂查询操作,隐藏底表实现细节,本身不存储实际数据;
  • 函数:把重复代码抽取为一个公共方法,方便SQL语句、视图、存储过程、触发器等调用;
  • 存储过程:一般用于实现复杂业务逻辑和数据处理,需要显示使用CALL语句调用;
  • 触发器:类似于监听器,监听表的某些行为,触发相应的动作;
  • 物化视图:类似于视图,只不过它本身存储数据,会把计算结果落库,而不是每次查询动态计算;

文章来源:https://blog.csdn.net/qq_36433289/article/details/134861389
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。