openGauss之视图的管理

课程学习内容

软件版本 ：openGauss 3.0.0

掌握openGauss视图的管理：创建视图、删除视图、查询视图的信息、修改视图的信息。

数据库实操内容

环境准备

–创建模式、表和插入数据

su - omm

sql -r

root@modb:~# su - omm omm@modb:~$ gsql -r gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. omm=#

创建视图

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’);

omm=# Create schema tpcds; CREATE SCHEMA omm=# CREATE TABLE tpcds.customer omm-# ( c_customer_sk integer, omm(# c_customer_id char(5), omm(# c_first_name char(6), omm(# c_last_name char(8) omm(# ) ; CREATE TABLE omm=# omm=# INSERT INTO tpcds.customer VALUES omm-# (6885, 1, 'Joes', 'Hunter'), omm-# (4321, 2, 'Lily','Carter'), omm-# (9527, 3, 'James', 'Cook'), omm-# (9500, 4, 'Lucy', 'Baker'); INSERT 0 4 omm=#

–创建视图

CREATE VIEW tpcds.customer_details_view_v1 AS SELECT * FROM tpcds.customer WHERE c_customer_sk > 5400;

omm=# CREATE VIEW tpcds.customer_details_view_v1 AS omm-# SELECT * FROM tpcds.customer omm-# WHERE c_customer_sk > 5400; CREATE VIEW omm=# 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) omm=#

–查看视图内容

select * from tpcds.customer_details_view_v1;

omm=# 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) omm=#

基于视图建立新的视图

create VIEW tpcds.part_view as SELECT * FROM tpcds.customer_details_view_v1 where c_customer_sk =9527;

–查看新的视图

select * from tpcds.part_view;

omm=# create VIEW tpcds.part_view as SELECT * FROM tpcds.customer_details_view_v1 where c_customer_sk =9527; CREATE VIEW omm=# select * from tpcds.part_view; c_customer_sk | c_customer_id | c_first_name | c_last_name ---------------+---------------+--------------+------------- 9527 | 3 | James | Cook (1 row) omm=#

创建物化视图

普通视图在查询中是实时进行计算的。如果建立视图的基表数据很多，使用视图的时候，进行实时计算视图表示的结果集，将消耗很大的计算机资源，并且费时很长。
物化视图提前计算出视图的结果集，并将该结果集保存在数据库里。
如果更新了基表,物化视图将过期。也就是说，基表更新后，物化视图不能反映最新的数据情况。因此在基表发生变化的时候，需要对物化视图进行更新。

–创建基表

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));

omm=# drop materialized view if exists mv_test; NOTICE: materialized view "mv_test" does not exist, skipping DROP MATERIALIZED VIEW omm=# drop table if exists test; NOTICE: table "test" does not exist, skipping DROP TABLE omm=# create table test(id serial primary key,testnum serial); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id" NOTICE: CREATE TABLE will create implicit sequence "test_testnum_seq" for serial column "test.testnum" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE omm=# insert into test(testnum) values(generate_series(1,100000)); INSERT 0 100000 omm=#

–创建物化视图：

create materialized view mv_test as select * from test where testnum%2=0;

omm=# create materialized view mv_test as omm-# select * from test where testnum%2=0; CREATE MATERIALIZED VIEW omm=#

–查看物化视图目前有多少行记录：

select count(*) from mv_test;

omm=# select count(*) from mv_test; count ------- 50000 (1 row) omm=#

普通视图相关的系统表pg_views

–系统视图pg_views

select * from pg_views where schemaname = ‘tpcds’ or schemaname = ‘public’;

omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public'; schemaname | viewname | viewowner | definitio n ------------+--------------------------+-----------+------------------------------------------------------- ----------------------------------------------- tpcds | customer_details_view_v1 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_custom er_sk > 5400); tpcds | part_view | omm | SELECT * FROM tpcds.customer_details_view_v1 WHERE (c ustomer_details_view_v1.c_customer_sk = 9527); (2 rows) omm=#

物化视图相关的系统表GS_MATVIEW

–GS_MATVIEW系统表提供了关于数据库中每一个物化视图的信息。

select * from GS_MATVIEW;

omm=# select * from GS_MATVIEW; matviewid | mapid | ivm | needrefresh | refreshtime -----------+-------+-----+-------------+------------- 16412 | 0 | f | | (1 row) omm=#

手动更新物化视图

–向基表test插入数据，查看物化视图有多少行记录：

insert into test(testnum) values(generate_series(1,100000));

select count(*) from mv_test;

omm=# insert into test(testnum) values(generate_series(1,100000)); INSERT 0 100000 omm=# select count(*) from mv_test; count ------- 50000 (1 row) omm=#

–手动更新物化视图，并查看更新物化视图后，物化视图有多少行记录：

refresh materialized view mv_test;

select count(*) from mv_test;

omm=# refresh materialized view mv_test; REFRESH MATERIALIZED VIEW omm=# select count(*) from mv_test; count -------- 100000 (1 row) omm=#

删除视图

–删除普通视图

DROP view tpcds.part_view;

DROP view TPCDS.customer_details_view_v1;

omm=# DROP view tpcds.part_view; DROP VIEW omm=# DROP view TPCDS.customer_details_view_v1; DROP VIEW omm=#

–删除物化视图

DROP MATERIALIZED VIEW mv_test;

omm=# DROP MATERIALIZED VIEW mv_test; DROP MATERIALIZED VIEW omm=#

课程作业

创建表，创建普通视图

omm=# Create schema tpcds1; CREATE SCHEMA omm=# CREATE TABLE tpcds1.customer omm-# ( c_customer_sk integer, omm(# c_customer_id char(5), omm(# c_first_name char(6), omm(# c_last_name char(8) omm(# ) ; CREATE TABLE omm=# INSERT INTO tpcds1.customer VALUES omm-# (6885, 1, 'Joes', 'Hunter'), omm-# (4321, 2, 'Lily','Carter'), omm-# (9527, 3, 'James', 'Cook'), omm-# (9500, 4, 'Lucy', 'Baker'); INSERT 0 4 omm=# CREATE VIEW tpcds1.customer_details_view_v1 AS omm-# SELECT * FROM tpcds1.customer omm-# WHERE c_customer_sk > 5400; CREATE VIEW omm=# select * from tpcds1.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) omm=#

使用视图创建新的视图

omm=# create VIEW tpcds1.part_view as SELECT * FROM tpcds1.customer_details_view_v1 where c_customer_sk =9527; CREATE VIEW omm=# select * from tpcds1.part_view; c_customer_sk | c_customer_id | c_first_name | c_last_name ---------------+---------------+--------------+------------- 9527 | 3 | James | Cook (1 row) omm=#

创建物化视图

omm=# drop materialized view if exists mv_test; NOTICE: materialized view "mv_test" does not exist, skipping DROP MATERIALIZED VIEW omm=# drop table if exists test; DROP TABLE omm=# create table test1(id serial primary key,testnum serial); NOTICE: CREATE TABLE will create implicit sequence "test1_id_seq" for serial column "test1.id" NOTICE: CREATE TABLE will create implicit sequence "test1_testnum_seq" for serial column "test1.testnum" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test1_pkey" for table "test1" CREATE TABLE omm=# insert into test1(testnum) values(generate_series(1,100000)); INSERT 0 100000 omm=# create materialized view mv_test1 as omm-# select * from test1 where testnum%2=0; CREATE MATERIALIZED VIEW omm=# omm=# select count(*) from mv_test1; omm=# count ------- 50000 (1 row) omm=#

手动更新物化视图

omm=# insert into test1(testnum) values(generate_series(1,100000)); INSERT 0 100000 omm=# select count(*) from mv_test1; count ------- 50000 (1 row) omm=# refresh materialized view mv_test1; REFRESH MATERIALIZED VIEW omm=# select count(*) from mv_test1; omm=# count -------- 100000 (1 row) omm=#

删除创建的视图

--删除普通视图 omm=# DROP view tpcds1.part_view; DROP VIEW omm=# DROP view TPCDS1.customer_details_view_v1; DROP VIEW omm=# --删除物化视图 omm=# DROP MATERIALIZED VIEW mv_test1; DROP MATERIALIZED VIEW omm=#

课程学习体会

 通过本次的实训操作，掌握数据库的视图的管理：创建视图、删除视图、查询视图的信息、修改视图的信息。