绵阳银行网站建设,wordpress 页面上显示文章,租外国服务器做的网站要备案吗,恩施做网站公司文章目录 一、视图#xff08;View#xff09;1、概念2、基本操作1#xff09;创建视图2#xff09;修改视图3#xff09;删除视图4#xff09;使用视图 3、使用场景4、实践 二、函数#xff08;Function#xff09;1、概念2、基本操作1#xff09;创建函数2#xff… 文章目录 一、视图View1、概念2、基本操作1创建视图2修改视图3删除视图4使用视图 3、使用场景4、实践 二、函数Function1、概念2、基本操作1创建函数2使用函数 3、使用场景4、实践 三、存储过程Stored Procedure1、概念2、基本操作1创建存储过程2使用存储过程 3、使用场景4、实践 四、触发器Trigger1、概念2、基本操作1 创建触发器2 使用触发器 3、使用场景4、实践 五、物化视图Materialized View1、概念2、使用场景3、实践 六、总结 一、视图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、使用场景 简化复杂查询视图可以封装复杂的查询逻辑简化应用程序中的查询操作提高查询的可读性和复用性。 数据安全性和权限控制通过视图可以隐藏底层表的结构和数据只向用户暴露必要的信息从而提高数据安全性和权限控制。 简化应用程序逻辑视图可以在数据库层面实现一些通用的逻辑简化应用程序中的代码提高代码复用性。 数据报表和分析视图可以用于创建数据报表和分析视图方便用户进行数据分析和报表生成。 数据结构的抽象视图可以将多个表的数据结构抽象为一个虚拟的表简化数据访问和操作。
总的来说MySQL视图适合于需要简化复杂查询、提高数据安全性、简化应用程序逻辑和进行数据报表分析的场景。
4、实践
这里我们将下面这个复杂查询写到视图里面 首先创建视图
CREATE VIEW vw_forlan_student AS SELECTA.name,A.age,B.class_name
FROMforlan_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_nameRETURNS {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、使用场景 数据处理和计算函数可以用于执行特定的数据处理、计算和转换例如日期处理、字符串操作、数学运算等。 查询优化通过函数可以封装复杂的查询逻辑提高查询的可读性和复用性同时也有助于优化查询性能。 业务规则和验证函数可以用于实现特定的业务规则和验证逻辑例如数据有效性检查、权限控制等。 简化应用程序逻辑函数可以在数据库层面实现一些通用的逻辑简化应用程序中的代码提高代码复用性。 自定义聚合函数通过自定义聚合函数可以实现特定的数据聚合逻辑满足特定的业务需求。
总的来说MySQL函数适合于需要在数据库层面执行特定的数据处理、计算和验证逻辑以及优化查询性能、简化应用程序逻辑的场景。
4、实践
创建一个函数判断年龄是否成年代码如下
CREATE FUNCTION IsAdult(age INT)
RETURNS VARCHAR(10)
BEGINDECLARE result VARCHAR(10);IF age 18 THENSET result 成年;ELSESET 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、使用场景 数据处理和业务逻辑存储过程可以封装复杂的数据处理逻辑例如数据清洗、转换、计算等以及实现特定的业务规则和流程。 数据安全性和权限控制通过存储过程可以对数据库操作进行封装和控制限制用户对数据的访问和操作提高数据安全性。 提高性能存储过程可以减少网络通信开销提高数据库操作的性能尤其是在需要频繁执行相同逻辑的情况下。 代码复用和维护存储过程可以被多个应用程序共享和调用提高代码的复用性同时也方便对逻辑进行统一管理和维护。 批量操作和事务管理存储过程可以实现批量数据操作同时也可以管理事务确保一系列操作的原子性和一致性。
总的来说存储过程适合于需要在数据库层面实现复杂逻辑、提高性能、增强安全性和方便维护的场景。
4、实践
使用存储过程插入新班级新学生数据
1创建一个存储过程用于插入班级信息并返回班级ID
CREATE PROCEDURE insert_class(IN class_name VARCHAR(255), OUT class_id INT)
BEGININSERT 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)
BEGININSERT 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、使用场景 数据完整性和约束触发器可以用于实现数据完整性和约束例如在插入、更新、删除数据时进行验证和限制。 数据审计和日志记录触发器可以用于记录数据变更的日志包括谁在什么时间做了什么操作。 数据复制和同步触发器可以用于在数据变更时触发数据复制和同步操作确保数据的一致性。 自动化任务和业务逻辑触发器可以用于执行特定的业务逻辑和自动化任务例如在特定条件下触发某些操作。 数据转换和处理触发器可以用于在数据变更时进行特定的数据转换和处理例如数据格式转换、计算字段值等。
总的来说MySQL触发器适合于需要在特定的数据变更时执行特定的逻辑、记录数据变更日志、实现数据复制和同步、以及执行特定的自动化任务和业务逻辑的场景。
4、实践
我们写一个触发器来实现当班级信息发生变化时触发更新学生信息 1创建触发器
CREATE TRIGGER update_forlan_student
AFTER UPDATE ON forlan_class
FOR EACH ROW
BEGIN-- 更新学生表中对应班级的信息UPDATE forlan_studentSET class_name NEW.class_nameWHERE class_id NEW.id;
END;注NEW关键字表示触发器所监视的事件中的新数据 2修改班级信息
UPDATE forlan_class SET class_name 触发器学习班 WHERE id 73查看触发器监情况效果如下
五、物化视图Materialized View
1、概念
在MySQL中物化视图Materialized View是一种预先计算和存储的查询结果集。与普通视图不同物化视图在创建时会将查询结果保存在磁盘上而不是在每次查询时动态计算。这样可以显著提高查询性能并减少对原始表的查询次数。
总的来说通常用于存储预先计算的结果以提高查询性能。然而MySQL本身并不直接支持物化视图但可以通过函数、存储过程、触发器、视图来实现类型的功能
2、使用场景
复杂查询优化当数据库中存在复杂的查询需要多个表的联接和聚合操作时可以使用物化视图来预先计算并存储查询结果以提高查询性能。数据汇总和报表生成物化视图可以用于汇总和聚合大量数据以生成报表和分析结果。通过预先计算和存储汇总数据可以加快报表生成的速度。数据仓库和决策支持系统在数据仓库和决策支持系统中物化视图可以用于存储预先计算的指标和维度数据以支持复杂的分析和决策过程。实时数据更新物化视图可以定期或实时地更新以保持与源数据的同步。这对于需要实时或近实时数据的应用程序非常有用。
需要注意的是物化视图的创建和维护可能会增加数据库的存储空间和更新成本。因此在选择使用物化视图时需要权衡查询性能的提升和资源消耗之间的平衡。
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
) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8mb4 ROW_FORMATDYNAMIC 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
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 ROW_FORMATDYNAMIC COMMENT学生年龄表;2创建一个存储过程用于更新物化视图的数据
CREATE PROCEDURE update_student_adult_status()
BEGINDELETE 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
BEGINIF NEW.age OLD.age THENCALL 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语句调用触发器类似于监听器监听表的某些行为触发相应的动作物化视图类似于视图只不过它本身存储数据会把计算结果落库而不是每次查询动态计算
