课程学习内容
软件版本 :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=#
课程学习体会
通过本次的实训操作,掌握数据库的视图的管理:创建视图、删除视图、查询视图的信息、修改视图的信息。