学习目标
掌握 openGauss 视图的管理：创建视图、删除视图、查询视图的信息、修改视图的信息。
前面每日一练链接
openGauss 每日一练第 1 天 | openGauss 数据库状态查看
openGauss 每日一练第 2 天 | 学习 gsql 命令行的使用
openGauss 每日一练第 3 天 | openGauss 数据库状态查看
openGauss 每日一练第 4 天 | openGauss 中一个数据库可以被多个用户访问
openGauss 每日一练第 5 天 | openGauss 中一个用户可以访问多个数据库
openGauss 每日一练第 6 天 | openGauss 中用户一次只能连接到一个数据库
openGauss 每日一练第 7 天 | openGauss 中一个数据库中可以创建多个模式
openGauss 每日一练第 8 天 | openGauss 中一个数据库可以存储在多个表空间中
openGauss 每日一练第 9 天 | openGauss 中一个表空间可以存储多个数据库
openGauss 每日一练第 10 天 | openGauss 逻辑结构：表空间管理
openGauss 每日一练第 11 天 | openGauss 逻辑结构：数据库管理
openGauss 每日一练第 12 天 | openGauss 逻辑结构：模式管理
openGauss 每日一练第 13 天 |openGauss 逻辑结构：表管理 1
openGauss 每日一练第 14 天 |openGauss 逻辑结构：表管理 2
openGauss 每日一练第 15 天 |openGauss 逻辑结构：表管理 3
openGauss 每日一练第 16 天 |openGauss 逻辑结构：表管理 4
openGauss 每日一练第 17 天 |openGauss 逻辑结构：索引管理
背景信息
当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。
视图与基本表不同，不是物理上实际存在的，是一个虚表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。视图每次被引用的时候都会运行一次。
物化视图 是一种特殊的物理表，物化视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性较大，任何对视图的查询实际上都是转换为对 SQL 语句的查询，性能并没有实际上提高。物化视图实际上就是存储 SQL 执行语句的结果，起到缓存的效果。
目前 Ustore 引擎不支持创建、使用物化视图。
课程学习
1.创建视图
--创建模式、表和插入数据
su - omm
gsql -d omm -p 5432 -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.
Create schema tpcds;
CREATE TABLE tpcds.customer
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO tpcds.customer VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
--创建视图
CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk > 5400;
--查看视图内容
select * from tpcds.customer_details_view_v1;
2.基于视图建立新的视图
create VIEW tpcds.part_view as SELECT * FROM tpcds.customer_details_view_v1 where c_customer_sk =9527;
--查看新的视图
select * from tpcds.part_view;
3.创建物化视图
普通视图在查询中是实时进行计算的。如果建立视图的基表数据很多，使用视图的时候，进行实时计算视图表示的结果集，将消耗很大的计算机资源，并且费时很长。
物化视图提前计算出视图的结果集，并将该结果集保存在数据库里。
如果更新了基表,物化视图将过期。也就是说，基表更新后，物化视图不能反映最新的数据情况。因此在基表发生变化的时候，需要对物化视图进行更新。
--创建基表
drop materialized view if exists mv_test;
drop table if exists test;
create table test(id serial primary key,testnum serial);
insert into test(testnum) values(generate_series(1,100000));
--创建物化视图：
create materialized view mv_test as
select * from test where testnum%2=0;
--查看物化视图目前有多少行记录：
select count(*) from mv_test;
4.普通视图相关的系统表 pg_views
--系统视图 pg_views
omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
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_v
1.c_customer_sk = 9527);
(2 rows)
5.物化视图相关的系统表 GS_MATVIEW
--GS_MATVIEW 系统表提供了关于数据库中每一个物化视图的信息。
select * from GS_MATVIEW;
6.手动更新物化视图
--向基表 test 插入数据，查看物化视图有多少行记录：
insert into test(testnum) values(generate_series(1,100000));
select count(*) from mv_test;
--手动更新物化视图，并查看更新物化视图后，物化视图有多少行记录：
refresh materialized view mv_test;
select count(*) from mv_test;
注意：物化视图不支持增删改操作，只支持查询语句。全量物化视图的刷新、删除过程中会给基表加高级别锁，若物化视图的定义涉及多张表，需要注意业务逻辑，避免死锁产生。增量物化视图可以对物化视图增量刷新，需要用户手动执行语句完成对物化视图在一段时间内的增量数据刷新。与全量创建物化视图的不同在于目前增量物化视图所支持场景较小。目前物化视图创建语句仅支持基表扫描语句或者 UNION ALL 语句。
7.删除视图
--删除普通视图
DROP view tpcds.part_view;
DROP view TPCDS.customer_details_view_v1;
--删除物化视图
DROP MATERIALIZED VIEW mv_test;
课程作业
1.创建表，创建普通视图
CREATE TABLE tpcds.cust
(c_customer_sk integer,
c_customer_id integer,
c_first_name char(6),
c_last_name char(8),
primary key (c_customer_id)
);
INSERT INTO tpcds.cust VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker'),
(9876, 5, 'Lili', 'Baker');
CREATE VIEW tpcds.cust_view_v1 AS
SELECT * FROM tpcds.cust
WHERE c_customer_sk > 5400;
--查看视图内容
select * from tpcds.cust_view_v1;
omm=# CREATE TABLE tpcds.cust
omm-# (c_customer_sk integer,
omm(# c_customer_id integer,
omm(# c_first_name char(6),
omm(# c_last_name char(8),
omm(# primary key (c_customer_id)
omm(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cust_pkey" for table "cust"
CREATE TABLE
omm=# INSERT INTO tpcds.cust VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker'),
omm-# (9876, 5, 'Lili', 'Baker');
INSERT 0 5
omm=# CREATE VIEW tpcds.cust_view_v1 AS
omm-# SELECT * FROM tpcds.cust
omm-# WHERE c_customer_sk > 5400;
CREATE VIEW
omm=# select * from tpcds.cust_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
9876 | 5 | Lili | Baker
(4 rows)
2.使用视图创建新的视图
omm=# create view tpcds.new_view1 as select * from tpcds.cust_view_v1 where c_customer_sk =9876;
CREATE VIEW
omm=# select * from tpcds.new_view1;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
9876 | 5 | Lili | Baker
(1 row)
omm=# 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_v
1.c_customer_sk = 9527);
tpcds | cust_view_v1 | omm | SELECT * FROM tpcds.cust WHERE (cust.c_customer_sk > 5400);
tpcds | new_view1 | omm | SELECT * FROM tpcds.cust_view_v1 WHERE (cust_view_v1.c_customer_sk = 9876);
(4 rows)
3.创建物化视图
create materialized view tpcds.mv_cust_v1 as
select * from tpcds.cust where c_customer_id >= 2;
4.手动更新物化视图
--向基表 cust 插入数据，查看物化视图有多少行记录：
INSERT INTO tpcds.cust VALUES
(1234, 6, 'Joes', 'Hunter'),
(4321, 7, 'Lily','Carter'),
(4567, 8, 'James', 'Cook'),
(6789, 9, 'Lucy', 'Baker'),
(5678, 10, 'Lili', 'Cook');
select count(*) from tpcds.cust;
--手动更新物化视图，并查看更新物化视图后，物化视图有多少行记录：
refresh materialized view tpcds.mv_cust_v1;
select count(*) from tpcds.mv_cust_v1;
omm=# select count(*) from tpcds.cust;
count
-------
5
(1 row)
omm=# select * from tpcds.cust;
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
9876 | 5 | Lili | Baker
(5 rows)
omm=# INSERT INTO tpcds.cust VALUES
omm-# (1234, 6, 'Joes', 'Hunter'),
omm-# (4321, 7, 'Lily','Carter'),
omm-# (4567, 8, 'James', 'Cook'),
omm-# (6789, 9, 'Lucy', 'Baker'),
omm-# (5678, 10, 'Lili', 'Cook');
INSERT 0 5
omm=# select * from tpcds.cust;
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
9876 | 5 | Lili | Baker
1234 | 6 | Joes | Hunter
4321 | 7 | Lily | Carter
4567 | 8 | James | Cook
6789 | 9 | Lucy | Baker
5678 | 10 | Lili | Cook
(10 rows)
omm=# refresh materialized view tpcds.mv_cust_v1;
REFRESH MATERIALIZED VIEW
omm=# select * from tpcds.mv_cust_v1;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
9876 | 5 | Lili | Baker
1234 | 6 | Joes | Hunter
4321 | 7 | Lily | Carter
4567 | 8 | James | Cook
6789 | 9 | Lucy | Baker
5678 | 10 | Lili | Cook
(9 rows)
omm=# select * from GS_MATVIEW;
matviewid | mapid | ivm | needrefresh | refreshtime
-----------+-------+-----+-------------+----------------------------
16548 | 0 | f | | 2022-12-11 22:32:43.676741
16569 | 0 | f | | 2022-12-11 23:15:31.0988
(2 rows)
5.删除创建的视图
--删除普通视图
DROP view tpcds.cust_view_v1;
DROP view tpcds.new_view1;
--删除物化视图
DROP MATERIALIZED VIEW tpcds.mv_cust_v1;
欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
