sql-6-DDL-视图

什么是视图

视图是从一个或多个表或视图中导出的虚拟表。视图不存储实际数据，而是基于定义它的SQL查询结果。当你查询视图时，数据库会动态地执行视图的定义查询，将结果呈现出来。视图的主要目的是简化数据访问、提供数据安全性和抽象复杂查询结构。

视图的优点主要包括：

数据安全性：视图可以用来限制用户访问敏感数据，只显示他们需要看到的信息，而不暴露底层表的完整结构或敏感字段。
简化复杂查询：通过视图，可以将复杂的多表联接和筛选逻辑封装起来，使得用户只需要查询一个简单的视图即可得到结果。
逻辑数据隔离：视图可以隐藏表的结构变化，对于应用程序来说，即使底层表结构发生变化，只要视图接口保持不变，程序仍能正常工作。
简化数据操作：视图可以作为数据操作（如INSERT、UPDATE或DELETE）的入口点，简化对复杂数据的增删改操作。
数据聚合：视图可以预先完成一些计算或聚合操作，提高查询效率。
重复使用查询逻辑：视图可以作为一个查询模板，避免重复编写相同的SQL语句。

视图的缺点包括：

性能影响：每次查询视图时，数据库都需要执行底层的查询操作，这可能导致性能下降，特别是在视图定义复杂时。
不可更新性：某些视图可能由于其定义（如包含GROUP BY、DISTINCT或子查询）而不支持INSERT、UPDATE或DELETE操作，或者这些操作可能会变得复杂和受限。
数据不一致：如果底层表的数据发生变化，视图中的数据也会随之更新，但视图本身不会保存任何历史数据，可能会导致用户看到的数据不是最新的。
额外的管理开销：视图需要维护和更新，特别是当源表结构或权限发生变化时。
隐藏复杂性：视图可能使问题更难诊断，因为它掩盖了底层表和查询的复杂性。

创建视图

create  view 视图名
as
查询语句 ;

示例：采用视图查询员工编号、年薪、部门名称

select empno,sal*12,dname from emp e,dept d where e.deptno = d.deptno ;

改写为：

create  view myempview
as
select empno 编号,sal*12 年薪,dname 部门名称 from emp e,dept d
where e.deptno = d.deptno ;

注意：视图中的查询语句中，如果存在字段的表达式，则必须给该表达式起别名.

视图分类

简单视图
- 表的数量 1个
- 函数没有
- 分组没有
复杂视图
- 表的数量 1个/多个
- 函数有
- 分组有

对视图的增删改

一般建议：视图只建议用于查询、分析，不要用于增删改。

视图不建议用于DML。但如果非要用DML，则必须满意以下条件：

①当出现以下之一时，不能Insert/update
- 组函数、group by、distinct、rownum、列的定义为表达式（sal*12）
②当出现以下之一时，不能delete
- 组函数、group by、distinct、rownum

create view testview
as 
select avg(sal) 平均工资,sum(sal) 总工资,job from emp group by job ;
--没有出现在组函数中的列，必须出现在group by后。

没有权限创建视图

普通用户第一次创建视图时，权限不足。

oracle：必须通过SYS授权。

grant create view to scott;

子句

with read only 确保只能对视图进行查询，不能增删改，oracle支持

create view baseview
as 
select empno,ename,job from emp
with read only;//设置只读

with check option 确保对视图的操作，必须满足where子句，oracle和mysql都支持

create view empdeptno20
as
select * from emp where deptno=20
with check option;

WITH CHECK OPTION 子句简介

很多时候，创建一个视图以仅显示表的部分数据。但是，简单视图是可更新的。

因此可以通过视图更新不可见的数据。此更新使视图不一致。

要确保视图的一致性，请在创建或修改视图时使用 WITH CHECK OPTION 子句。

WITH CHECK OPTION子句是CREATE VIEW声明的可选部分。

WITH CHECK OPTION子句阻止您更新或插入通过视图不可见的行。换句话说，每当通过视图更新或插入基表的一行时，MySQL都会确保插入或更新操作符合视图的定义。

WITH CHECK OPTION：这个选项用于确保任何试图通过视图进行的INSERT或UPDATE操作必须满足视图定义中的WHERE子句或其他限制。在你给出的例子中，empdeptno20视图只包含deptno为20的员工记录。如果尝试通过这个视图插入或更新一条记录，系统会检查新记录的deptno是否仍然等于20。如果不符合条件，操作将被拒绝。

mysql的视图处理算法

算法属性允许您控制MySQL在创建视图时使用的机制。MySQL提供三种算法： MERGE，TEMPTABLE，和UNDEFINED。

使用MERGE算法，MySQL首先将输入查询与SELECT定义视图的语句组合成单个查询。然后MySQL执行组合查询以返回结果集。如果SELECT语句包含聚合函数，例如 MIN，MAX，SUM，COUNT，AVG 或DISTINCT，GROUP BY，HAVING，LIMIT，UNION，UNION ALL，子查询，则不允许使用MERGE算法。如果SELECT语句引用无表，则也不允许MERGE算法。如果不允许MERGE算法，MySQL将算法更改为UNDEFINED。请注意，将视图定义中的输入查询和查询组合到一个查询中称为视图分辨率。
使用TEMPTABLE算法，MySQL首先根据定义视图的SELECT语句创建临时表，然后对临时表执行输入查询。因为MySQL必须创建一个临时表来存储结果集并将数据从基表移动到临时表，所以TEMPTABLE 算法的效率低于MERGE算法。此外，使用TEMPTABLE 算法的视图不可更新。
在未指定显式算法的情况下创建视图时，这是默认UNDEFINED算法。UNDEFINED算法允许MySQL选择使用 MERGE或TEMPTABLE 算法。MySQL更喜欢MERGE 算法在TEMPTABLE 算法中，因为 MERGE算法效率更高。

posted @ 2022-07-19 17:31 姬雨晨阅读(83) 评论(0) 编辑收藏举报

刷新页面返回顶部

jiyuchen1