一、学习目标
本节课是本次实训的第十八节课，本次课重点是openGauss视图相关知识,主要是创建视图、删除视图、查询视图信息、修改视图信息等相关内容。
1.1 视图概念
数据库视图是一个数据库子集，基于对一个或多个数据库表运行的查询。数据库视图作为命名的查询保存在数据库中，可用于保存频繁使用的复杂查询。视图是从一个或几个基本表（或视图）导出的表。
它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。
视图只供查询，数据不可更改。
1.2 视图分类
有两种数据库视图：动态视图和静态视图。
- 动态视图 可以包含一个或两个表中的数据，并自动包含指定表中的所有列。当创建或更改相关对象或扩展对象时，动态视图将自动更新。
静态视图 可以包含多个表中的数据，这些表中的必需列必须在静态视图的 SELECT 和 WHERE 子句中指定。当创建或更改相关对象或扩展对象时，必须手动更新静态视图。
1.3 视图的作用
- 视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
- 视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
- 从而加强了安全性，使用户只能看到视图所显示的数据。
- 视图还可以被嵌套，一个视图中可以嵌套另一个视图。
1.4 物化视图
物化视图是一种特殊的物理表，物化视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性较大，任何对视图的查询实际上都是转换为对SQL语句的查询，性能并没有实际上提高。物化视图实际上就是存储SQL执行语句的结果，起到缓存的效果。
1.4.1 物化视图分类
物化视图分两种：全量物化视图和增量物化视图
- 全量物化视图 全量物化视图仅支持对创建好的物化视图做全量更新，而不支持做增量更新。创建全量物化视图语法和CREATE TABLE AS语法一致，不支持对全量物化视图指定NodeGroup创建。
- 增量物化视图 增量物化视图顾名思义就是可以对物化视图增量刷新，需要用户手动执行语句完成对物化视图在一段时间内的增量数据进行刷新。与全量创建物化视图不同在于目前增量物化视图所支持场景较小，目前物化视图创建语句仅支持基表扫描语句或者UNION ALL语句。
二、测试练习
2.1 创建表及视图
[omm@opengauss-node1 ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
-- 切换到presdb库
openGauss=# \c presdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "presdb" as user "omm".
-- 创建schema tpcds
presdb=# create schema tpcds;
CREATE SCHEMA
presdb=# CREATE TABLE tpcds.customer
presdb-# ( c_customer_sk integer,
presdb(# c_customer_id char(5),
presdb(# c_first_name char(6),
presdb(# c_last_name char(8)
presdb(# ) ;
CREATE TABLE
presdb=# INSERT INTO tpcds.customer VALUES
presdb-# (6885, 1, 'Joes', 'Hunter'),
presdb-# (4321, 2, 'Lily','Carter'),
presdb-# (9527, 3, 'James', 'Cook'),
presdb-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
presdb=# select * from tpcds.customer;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(4 rows)
-- 根据查询条件创建视图tpcds.customer_details_view_v1
presdb=# create view tpcds.customer_details_view_v1 as select * from tpcds.customer where c_customer_sk > 5400;
CREATE VIEW
-- 通过视图来查询信息
presdb=# select * from tpcds.customer_details_view_v1;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(3 rows)
-- 通过\dp 可以查看视图信息
presdb=# \dp tpcds.customer_details_view_v1
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------------------+------+-------------------+--------------------------
tpcds | customer_details_view_v1 | view | |
(1 row)
2.2 使用视图创建新视图
-- 在视图基础上还可以创建新的视图
presdb=# \dp tpcds.customer_details_view_v1
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------------------+------+-------------------+--------------------------
tpcds | customer_details_view_v1 | view | |
(1 row)
presdb=# create view tpcds.part_view as select * from tpcds.customer_details_view_v1 where c_customer_sk =9527;
CREATE VIEW
presdb=# select * from tpcds.part_view;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
9527 | 3 | James | Cook
(1 row)
-- 查看新视图结构
presdb=# \dp tpcds.part_view
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------+------+-------------------+--------------------------
tpcds | part_view | view | |
(1 row)
-- 查询系统视图pg_views相关信息
presdb=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
schemaname | viewname | viewowner | definition
------------+--------------------------+-----------+------------------------------------------------------------------------------------------------------
public | bmsql_config_view | omm | SELECT bmsql_config.cfg_name, bmsql_config.cfg_value FROM bmsql_config;
tpcds | customer_details_view_v1 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_customer_sk > 5400);
tpcds | part_view | omm | SELECT * FROM tpcds.customer_details_view_v1 WHERE (customer_details_view_v1.c_customer_sk = 9527);
(3 rows)
2.3 创建物化视图
2.3.1 创建及刷新全量物化视图
-- 创建基表message
presdb=# create table message(id int, name char(20));
CREATE TABLE
presdb=# insert into message values(1, 'shanglei');
INSERT 0 1
presdb=# insert into message values(2, 'jacky');
INSERT 0 1
-- 创建全量刷新物化视图mv_all_message
presdb=# create materialized view mv_all_message as select * from message;
CREATE MATERIALIZED VIEW
-- 查询物化视图信息
presdb=# select * from mv_all_message;
id | name
----+----------------------
1 | shanglei
2 | jacky
(2 rows)
-- 向表中插入新数据
presdb=# insert into message values(3, 'shlei6067');
INSERT 0 1
-- 刷新全量物化视图
presdb=# refresh materialized view mv_all_message;
REFRESH MATERIALIZED VIEW
presdb=#
-- 查询全量物化视图信息
presdb=# SELECT * FROM mv_all_message;
id | name
----+----------------------
1 | shanglei
2 | jacky
3 | shlei6067
(3 rows)
-- 查看全量物化视图结构
presdb=# \dp mv_all_message
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+----------------+-------------------+-------------------+--------------------------
public | mv_all_message | materialized view | |
(1 row)
2.3.2 创建及刷新增量物化视图
-- 创建基表city
presdb=# create table city(id int, name char(20));
CREATE TABLE
presdb=# insert into city values(1, 'beijing');
INSERT 0 1
presdb=# insert into city values(2, 'shanghai');
INSERT 0 1
-- 创建增量物化视图mv_incr_city
presdb=# create incremental materialized view mv_incr_city as select * from city;
CREATE MATERIALIZED VIEW
--查询增量物化视图mv_incr_city信息
presdb=# select * from mv_incr_city;
id | name
----+----------------------
1 | beijing
2 | shanghai
(2 rows)
presdb=#
-- 向基表插入新增数据
presdb=# insert into city values(3, 'nanjing');
INSERT 0 1
-- 刷新增量物化视图
presdb=# refresh incremental materialized view mv_incr_city;
REFRESH MATERIALIZED VIEW
-- 查询增量物化视图信息
presdb=# select * from mv_incr_city;
id | name
----+----------------------
1 | beijing
2 | shanghai
3 | nanjing
(3 rows)
presdb=# insert into city values(4, 'guangzhou');
INSERT 0 1
-- 全量刷新增量物化视图mv_incr_city
presdb=# refresh materialized view mv_incr_city;
REFRESH MATERIALIZED VIEW
presdb=# select * from mv_incr_city;
id | name
----+----------------------
1 | beijing
2 | shanghai
3 | nanjing
4 | guangzhou
(4 rows)
presdb=# select * from pg_views where schemaname = 'tpcds';
schemaname | viewname | viewowner | definition
------------+--------------------------+-----------+------------------------------------------------------------------------------------------------------
tpcds | customer_details_view_v1 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_customer_sk > 5400);
tpcds | part_view | omm | SELECT * FROM tpcds.customer_details_view_v1 WHERE (customer_details_view_v1.c_customer_sk = 9527);
(2 rows)
presdb=# \dp mv_incr_city
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+-------------------+-------------------+--------------------------
public | mv_incr_city | materialized view | |
(1 row)
-- GS_MATVIEW系统表提供了关于数据库中每一个物化视图的信息
presdb=# select * from gs_matview;
matviewid | mapid | ivm | needrefresh | refreshtime
-----------+-------+-----+-------------+----------------------------
16784 | 0 | f | | 2022-12-11 19:52:47.98411
16795 | 16799 | t | | 2022-12-11 19:59:27.768275
(2 rows)
-- 可以看到每个视图被刷新的时间等信息
2.4 删除视图
-- 当在表上创建了视图或者物化视图，无法直接删除表，会提示有物化视图存在，需要先删除物化视图再删除表
presdb=# drop table city;
ERROR: cannot drop table city because other objects depend on it
DETAIL: materialized view mv_incr_city depends on table city
HINT: Use DROP ... CASCADE to drop the dependent objects too.
presdb=# drop materialized view mv_incr_city;
DROP MATERIALIZED VIEW
presdb=#
-- 只有删除了物化视图才能删除物化视图的基表
presdb=# drop table city;
DROP TABLE
presdb=# select * from pg_views where schemaname = 'tpcds';
schemaname | viewname | viewowner | definition
------------+--------------------------+-----------+------------------------------------------------------------------------------------------------------
tpcds | customer_details_view_v1 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_customer_sk > 5400);
tpcds | part_view | omm | SELECT * FROM tpcds.customer_details_view_v1 WHERE (customer_details_view_v1.c_customer_sk = 9527);
(2 rows)
三、学习心得
物化视图在数据库里起了很重要的作用，有时想要为一些业务人员赋予查询某些表的权限，而又不希望这些人员直接查看到基表以及对基表进行操作，另外有时因为权限的管理，不希望业务人员有操作这些基表的查询，就可以为他们创建一些相应的视图或物化视图。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
