MySQL:VIEW视图

概述

MySQL 视图(View)是一种虚拟存在的表,同真实表一样,视图也由列和行构成,但视图并不实际存在于数据库中行和列的数据来自于定义视图的查询中所使用的表,并且是在使用视图时动态生成的

数据库中只存放了视图的定义,并没有存放视图中的数据,这些数据都存放在定义视图查询所引用的真实表中。

使用视图查询数据时,数据库会从真实表中取出对应的数据。视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变,显示在视图中的数据也会发生改变

视图可以从原有的表上选取对用户有用的信息,那些对用户没用,或者用户没有权限了解的信息,都可以直接屏蔽掉,作用类似于筛选。这样做既使应用简单化,也保证了系统的安全。

MySQL 的视图不支持输入参数的功能,因此交互性上还有欠缺。但对于变化不是很大的操作,使用视图可以很大程度上简化用户的操作。

如果经常需要从多个表查询指定字段的数据,可以在这些表上建立一个视图,通过这个视图显示这些字段的数据

视图与数据表的区别

视图不同于数据表

  • 视图不是数据库中真实的表,而是一张虚拟表,其结构和数据是建立在对数据中真实表的查询基础上的。

  • 存储在数据库中的查询操作 SQL 语句定义了视图的内容,列数据和行数据来自于视图查询所引用的实际表,引用视图时动态生成这些数据。

  • 视图没有实际的物理记录,不是以数据集的形式存储在数据库中的,它所对应的数据实际上是存储在视图所引用的真实表中的。

  • 视图是数据的窗口,而表是内容。表是实际数据的存放单位,而视图只是以不同的显示方式展示数据,其数据来源还是实际表。

  • 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些 SQL 语句的集合。从安全的角度来看,视图的数据安全性更高,使用视图的用户不接触数据表,不知道表结构

  • 视图的建立和删除只影响视图本身,不影响对应的基本表

视图的优点

视图与表在本质上虽然不相同,但视图经过定义以后,结构形式和表一样,可以进行查询、修改、更新和删除等操作。同时,视图具有如下优点:

  1. 定制用户数据

    聚焦特定的数据在实际的应用过程中,不同的用户可能对不同的数据有不同的要求。

    例如,当数据库同时存在时,如学生基本信息表、课程表和教师信息表等多种表同时存在时,可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图,安排课程人员查看修改课程表和教师信息的视图,教师查看学生信息和课程信息表的视图。

  2. 简化数据操作

    在使用查询时,很多时候要使用聚合函数,同时还要显示其他字段的信息,可能还需要关联到其他表,语句可能会很长,如果这个动作频繁发生的话,可以创建视图来简化操作。

  3. 提高数据的安全性

    视图是虚拟的,物理上是不存在的。可以只授予用户视图的权限,而不具体指定使用表的权限,来保护基础数据的安全。

  4. 共享所需数据

    通过使用视图,每个用户不必都定义和存储自己所需的数据,可以共享数据库中的数据,同样的数据只需要存储一次。

  5. 更改数据格式

    使用视图,可以重新格式化检索出的数据,并组织输出到其他应用程序中。

  6. 重用 SQL 语句:

    视图提供的是对查询操作的封装,本身不包含数据,所呈现的数据是根据视图定义从基础表中检索出来的,如果基础表的数据新增或删除,视图呈现的也是更新后的数据。视图定义后,编写完所需的查询,可以方便地重用该视图。

在 MySQL 中操作视图

CREATE VIEW 创建视图

通过 CREATE VIEW 语句在数据库中创建视图:

CREATE [OR REPLACE] VIEW [db_name.] view_name [column_list]
AS SELECT column_listsFROM table_name[WHERE condition];
  • view_name 在数据库中必须是唯一的,不能与其他表或视图同名
  • 使用 OR REPLACE 选项替换已存在的视图
  • [column_list] 也可以显式指定视图的列列表
  • 如果从视图中选择具有自己的 ORDER BY 子句的查询,则忽略它。
基于另一个视图创建视图

视图可以基于现有的视图创建,这样可以进一步抽象查询逻辑或添加额外的过滤条件。

"""也可以基于另一个视图创建视图"""
CREATE VIEW view_name [column_list]
ASSELECT column_listFROM another_view[...];

假设已经有了一个视图 view_sales_summary,它显示了销售数据的汇总信息,现在想创建一个新的视图 view_top_customers,该视图基于 view_sales_summary 并且只显示销售额最高的前10位客户。

CREATE VIEW view_top_customersASSELECT *FROM view_sales_summaryORDER BY total_sales DESCLIMIT 10;
创建基于多个表的视图

可以通过 INNER JOINLEFT JOINRIGHT JOIN 等方式基于多个表创建视图。

CREATE VIEW view_name [column_list]
AS SELECT column_listFROM table1 t1INNER JOIN table2 t2 ON t1.column1 = t2.column1INNER JOIN table3 t3 USING (column2)...;

假设有两个表 customers 和 orders,并且还有一个表 order_items,你想要创建一个视图 view_customer_orders,该视图显示每个客户的订单详情。

CREATE VIEW view_customer_orders 
ASSELECT c.customer_id, c.name, o.order_id, oi.item_name, oi.quantity, oi.priceFROM customers cINNER JOIN orders o ON c.customer_id = o.customer_idINNER JOIN order_items oi ON o.order_id = oi.order_id;
使用子查询创建视图

有时候需要在视图定义中使用子查询来进一步处理数据或进行复杂的逻辑运算。

CREATE VIEW view_name [column_list]
AS SELECT column_listFROM table_name [| subqueries]WHERE subqueries;

假设想创建一个视图 view_customer_totals,该视图显示每个客户的总销售额。

CREATE VIEW view_customer_totals ASSELECT customer_id, name, (SELECT SUM(price * quantity)FROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE customer_id = c.customer_id)) AS total_salesFROM customers c;

视图处理算法

MySQL 视图处理算法(View Processing Algorithms),包括 MERGETEMPTABLEUNDEFINED 三种模式

在创建和修改视图的时候,语句有一个可选项 ALGORITHM = 来指定视图处理将会使用的算法

'''CREATE'''
CREATE [OR REPLACE] [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW view_name [column_list]
AS SELECT-statement;'''ALTER'''
ALTER [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] 
VIEW view_name
ASSELECT-statement;
MERGE

合并(MERGE)算法:首先将输入查询与视图定义中的 SELECT 语句合并为单个查询。然后执行组合查询以返回结果集。

将输入查询和视图定义的 SELECT 语句组合到单个查询中称为视图解析

这种算法适用于简单的视图定义和基于单个表的查询。

示例:

CREATE ALGORITHM=MERGE VIEW contactPersons(customerName, firstName, lastName, phone
) AS
SELECT customerName, contactFirstName, contactLastName, phone
FROM customers;

执行如下语句:

SELECT * FROM contactPersons
WHERE customerName LIKE '%Co%';

MySQL执行以下步骤:

  • 将视图名称contactPersons转换为表名称customers
  • 将 askterisk (*) 转换为列表列名一一对应于 SELECT column_list
  • 添加 WHERE 子句。
TEMPTABLE

临时表(TEMPTABLE)算法:当使用临时表算法时,MySQL会在内存或磁盘上创建一个临时表来存储视图查询的结果集,并使用该临时表执行查询操作。

TEMPTABLE视图不能进行更新(updatable)

由于MySQL必须创建临时表来存储结果集,并将数据从基表移动到临时表,因此该算法的效率低于 MERGE 算法

这种算法适用于复杂的视图定义、多表关联和其他不适合合并算法的情况。

UNDEFINED

UNDEFINED 算法是创建视图时默认使用的算法

ALGORITHM = MERGE 创建一个视图,而MySQL只能用临时表处理这个视图时,MySQL会自动将算法设置为 UNDEFINED 并产生一个警告。

UNDEFINED 允许MySQL选择 MERGE 或 TEMPTABLE,通常会选择 MERGE ,因为其效率更高

创建可更新视图

在MySQL中,视图不仅是可查询的,而且是可更新的。这意味着您可以使用 INSERTUPDATE 语句通过可更新视图插入或更新基表的行。此外,还可以使用 DELETE 语句通过视图删除基础表的行

通过视图更新时转到基本表上进行更新,如果对视图增加或删除记录,实际上是对基本表增加或删除记录

若要创建可更新的视图,定义该视图的 SELECT 语句不得包含以下任何元素:

  • 聚合函数:如 MIN(), MAX(), SUM(), AVG(), 和 COUNT()

  • DISTINCT 子句

  • GROUP BY 子句.

  • HAVING 子句.

  • UNIONUNION ALL 子句.

  • LEFT JOIN 子句.

  • SELECT 子句或 WHERE 子句中引用 FROM 子句中出现的表的子查询。

  • FROM 子句中引用不可更新视图。

  • 只引用文字值.

  • 对基表的任意列的多个引用.

使用 TEMPTABLE 算法的视图无法被更新

查看可更新视图的信息

可以通过查询 information_schema 数据库中的 views 表中的is_updatable 列来检查数据库中的视图是否处于可更新状态。

下面的查询从classicmodels数据库中获取所有视图,并显示哪些视图是可更新的:

SELECT table_name, is_updatable
FROMinformation_schema.views
WHEREtable_schema = 'classicmodels';

WITH CHECK OPTION

创建一个视图可以显示表的部分数据。但是,简单视图是可更新的,可以更新视图中不可见的数据。此更新使视图不一致。为了确保视图的一致性,在创建或修改视图时使用 WITH CHECK OPTION 子句。

WITH CHECK 选项是 CREATE VIEW 语句的可选子句。WITH CHECK 选项可以防止视图更新或插入不可见的行。换句话说,无论何时通过视图更新或插入基表的一行,MySQL都确保插入或更新操作符合视图的定义

CREATE [OR REPLACE VIEW] view_name 
ASSELECT column_list FROM table_name WHERE condition
WITH [CASCADED | LOCAL] CHECK OPTION;

若 column 在视图不可见,在 WITH CHECK OPTION 下插入或更新不可见的数据,MySQL 会报错

对于 WITH CHECK OPTION ,MySQL提供了两个选项:LOCALCASCADED
默认情况下,使用 CASCADE 模式

WITH CASCADED CHECK OPTION

当使用 WITH CHECK OPTION CASCADED 模式时,更新视图时会检查所有与视图相关的表,确保更新的行满足视图定义的过滤条件和限制。

如果更新导致视图中的记录不再满足视图定义的条件,则更新将被拒绝

该模式下视图及其相关的表需在同一个数据库中。否则检查可能无法正常进行

![[MySQL WITH CASCADED CHECK OPTION.png]]

WITH LOCAL CHECK VIEW

当使用 WITH CHECK OPTION LOCAL 模式时,仅检查更新操作中涉及的行是否满足视图定义的过滤条件和限制其他与视图相关的行不会受到检查

这种模式下,只需要确保更新的行符合视图定义,而不需要考虑整个视图中的数据一致性。

![[Mysql WITH LOCAL CHECK OPTION.png]]

查看视图

DESCRIBE

查看视图的字段信息与查看数据表的字段信息一样,都是使用 DESCRIBE 关键字来查看的:

DESCRIBE | DESC view_name;
查看所有视图

MySQL将视图视为’VIEW’类型的表。因此,要显示当前数据库中的所有视图,可以使用如下的 SHOW FULL TABLES 语句:

SHOW FULL TABLES
[{FROM | IN } database_name]
WHERE table_types = 'VIEW'
LIKE pattern;
  • FROM | IN 子句 可以查看其他数据库中的视图

  • SHOW FULL TABLES 语句同时返回表和视图,需要添加WHERE子句来只获取视图

  • 使用 LIKE 关键字 查看特定视图

查看视图详细信息
SHOW CREATE TABLE

通过 SHOW CREATE VIEW 的语句,还可以查看创建视图的语句。创建视图的语句可以作为修改或者重新创建视图的参考,方便用户操作。

SHOW CREATE VIEW view_name;
视图元数据

所有视图的定义都是存储在 information_schema 数据库下的 views 表中

INFORMATION_SCHEMA 数据库提供对MySQL数据库元数据(如数据库、表、列的数据类型或特权)的访问。;

SELECT *
FROM information_schema.views;
SELECT *
FROM information_schema.tables

![[Pasted image 20231012221129.png]]

  • table_schema列 存储视图(或表)的模式或数据库。

  • table_name列 存储视图(或表)的名称。

  • table_type列 存储表的类型:

    • 表为BASE TABLE,视图为VIEW
    • 表为INFORMATION_SCHEMA SYSTEM,视图为 VIEW。

RENAME VIEW 重命名视图

在MySQL中,视图和表共享相同的命名空间。因此,可以使用 RENAME TABLE 语句重命名视图。

RENAME TABLE original_view_name
TO new_view_name;

通过查看视图语句来确定视图是否更名成功

不能使用 RENAME TABLE 语句将视图从一个数据库移动到另一个数据库。如果这样做,MySQL将发出一个错误。但通过间接方式可以这么做

间接重命名方式

重命名视图的另一种间接方法是使用 DROP VIEWCREATE VIEW 语句的序列:

  1. 使用 SHOW CREATE VIEW 语句获取CREATE VIEW语句。

  2. 复制 CREATE VIEW 语句并将其保存到一个文件中。

  3. 使用 DROP VIEW 语句删除视图。

  4. CREATE VIEW 语句中更改视图的名称。

  5. 执行 CREATE VIEW 语句以创建具有新名称的视图。

通过使用一系列DROP VIEW和CREATE VIEW语句,还可以将视图从一个数据库移动到另一个数据库

ALTER VIEW 修改视图

使用 ALTER VIEW 语句来修改已存在的视图定义(查询逻辑、列的选择和条件等内容):

ALTER [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] 
VIEW [dbname.name] | view_name
AS SELECT-statement;

ALTER VIEW 仅修改视图的定义,不会修改视图查询的结果集。因此,如果修改了视图的定义,需要重新执行对该视图的查询,以反映修改后的结果。

对于 ALTER VIEW 语句的使用,需要用户具有针对视图的 CREATE VIEW 和 DROP 权限,以及由 SELECT 语句选择的每一列上的某些权限。

修改视图的定义,也可以使用 DROP VIEW 语句先删除视图,再使用 CREATE VIEW 语句来实现

DROP VIEW 删除视图

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时,只能删除视图的定义,不会删除数据

-- 删除单个视图
DROP VIEW [IF EXISTS] view_name;-- 删除多个视图
DROP VIEW [IF EXISTS] view_name1, view_name2, ...;

如果列表包含一个不存在的视图,DROP VIEW 语句将失败,并且不会删除任何视图。

如果使用 IF EXISTS 选项,DROP VIEW 语句将为每个不存在的视图生成一个注释,而已存在的视图不会被删除。

在MySQL 5.7或更早的版本中,如果存在任何不存在的视图,DROP VIEW将返回一个错误。但它会删除已经存在的视图。

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

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Unity物理模块 之 2D效应器

本文仅作笔记学习和分享,不用做任何商业用途 本文包括但不限于unity官方手册,unity唐老狮等教程知识,如有不足还请斧正​ 1.什么是效应器 2D 效应器 - Unity 手册 2D 效应器是与 2D 碰撞器一起使用的组件,相当于预先编写好的插…

⭕️【论文阅读】《Interactive Class-Agnostic Object Counting》

[2309.05277] Interactive Class-Agnostic Object Counting (arxiv.org) code: cvlab-stonybrook/ICACount: [ICCV23] Official Pytorch Implementation of Interactive Class-Agnostic Object Counting (github.com) 目录 Abstract Abstract 我们提出了一个新…

【合并 K 个升序链表】python刷题记录

R4-分治篇 目录 最小堆方法 分治法 ps: 如果只是数组就很好处理了 # Definition for singly-linked list. # class ListNode: # def __init__(self, val0, nextNone): # self.val val # self.next next class Solution:def mergeKLists(self, lists…

Spring AOP 源码剖析

一.AOP基础概念 切面(Aspect):切面是跨越多个类的关注点模块化,如事务管理。切面由切点和通知组成。连接点(Join Point):在程序执行过程中某个特定的点,如方法调用或异常抛出。在Sp…

kafka基础概念二

1.Kafka中主题和分区的概念 1.主题Topic 主题-topic在kafka中是一个逻辑的概念,kafka通过topic将消息进行分类。不同的topic会被订阅该topic的消费者消费 但是有一个问题,如果说这个topic中的消息非常非常多,多到需要几T来存,因…

区块链的搭建和运维4

区块链的搭建和运维4 (1) 搭建基于MySQL分布式存储的区块链 1.构建单群组网络节点 使用开发部署工具构建单群组网络节点,命令如下: bash build_chain.sh -l 127.0.0.1:4 -p 30300,20200,85452. 启动 MySQL 并设置账户密码 输入如下命令,…

关于Git使用不成功的问题解决方案记录

关于Git使用不成功的问题解决方案记录 前言代理连接不成功总结 前言 项目中建立了Git小仓库,但是在使用中出现了无法push新的代码,显示端口出现问题,发现网站和端口都没有问题,可以打开网站。但是还是连接失败,无法下…

MySQL笔记(十):MySQL管理

一、用户管理 #用户管理 -- 原因:当我们做项目开发时,可以根据不同的开发人员,赋给她相应的mysql操作权限。 -- 所以,mysql数据库管理人员(root),根据需要创建不同的用户,赋给相应的…

android中打包apk体积优化方案

1.在配置文件AndroidManifest中新增 android:extractNativeLibs"true" 2.在模块build文件下配置支持的cpu,一般配置64的就行了,多配一种so库体积大一倍,择优。 ndk { abiFilters arm64-v8a } 3.在模块builde文件下配置混淆除去无用的资源文件 注:三种…

【Kubernetes】Deployment 的状态

Deployment 的状态 Deployment 控制器在整个生命周期中存在 3 3 3 种状态: 已完成(Complete)进行中(Progressing)失败(Failed) 通过观察 Deployment 的当前特征,可以判断 Deploym…

Win32注册表操作

注册表的概念 注册表是一个存储计算机配置信息的数据库,用于存储计算机上的硬件、安装的软件、系统设置以及用户账户配置等重要信息。对注册表的编辑不当可能会影响计算机的正常运行。应用程序可以调用API函数来对注册表进行增、删等操作。 注册表结构 运行Regedi…

Linux学习笔记:Linux基础知识汇总(个人复习版)

常用命令: 1、ls -a:显示所有文件(包括隐藏文件),简洁版 -l:显示所有文件,详细版 -R:显示所有文件以及子目录下文件,简洁版 可以搭配使用。 2、netstat -i&#x…

priority_queue模拟实现【C++】

文章目录 全部的实现代码放在了文章末尾什么是适配器模式?准备工作包含头文件定义命名空间类的成员变量什么是仿函数?比较仿函数在priority_queue中的作用通过传入不同的仿函数可以做到大堆和小堆之间的切换通过传入不同的仿函数可以做到改变priority_qu…

书生.浦江大模型实战训练营——(三)Git基本操作与分支管理

最近在学习书生.浦江大模型实战训练营,所有课程都免费,以关卡的形式学习,也比较有意思,提供免费的算力实战,真的很不错(无广)!欢迎大家一起学习,打开LLM探索大门&#xf…

Java设计模式(命令模式)

定义 将一个请求封装为一个对象,从而让你可以用不同的请求对客户进行参数化,对请求排队或者记录请求日志,以及支持可撤销的操作。 角色 抽象命令类(Command):声明用于执行请求的execute方法,通…

LeNet5模型搭建

文章目录 LeNet1 搭建模型2 训练模型3 测试模型3.1 预测一3.2 预测二 LeNet LeNet 诞生于 1994 年,是最早的卷积神经网络之一,并且推动了深度学习领域的发展。自从 1988 年开始,在许多次成功的迭代后,这项由 Yann LeCun 完成的开拓…

【最长递增子序列】python刷题记录

R4-dp 目录 常规方法遇到以下序列时就会变得错误 动态规划的思路 单调栈 ps: class Solution:def lengthOfLIS(self, nums: List[int]) -> int:#最简单的方法nlen(nums)if n<2:return nmx1for i in range(n):max_i1for j in range(i1,n):if nums[i]<nums[j]:nums…

RK3568平台(触摸篇)FT5X06驱动程序分析

一.设备树 &i2c1 {status "okay";myft5x06: my-ft5x0638 {compatible "my-ft5x06";reg <0x38>;reset-gpios <&gpio0 RK_PB6 GPIO_ACTIVE_LOW>;interrupt-parent <&gpio3>;interrupts-gpio <&gpio3 RK_PA5 GPI…

大数据-70 Kafka 高级特性 物理存储 日志存储 日志清理: 日志删除与日志压缩

点一下关注吧&#xff01;&#xff01;&#xff01;非常感谢&#xff01;&#xff01;持续更新&#xff01;&#xff01;&#xff01; 目前已经更新到了&#xff1a; Hadoop&#xff08;已更完&#xff09;HDFS&#xff08;已更完&#xff09;MapReduce&#xff08;已更完&am…

K8S资源之NameSpace

作用 隔离资源(默认不隔离网络) 查看所有的NS kubectl get ns创建NS kubectl create ns hello删除NS kubectl delete ns hello