概述

本文档记录openGauss 3.0.0数据库每日一练第18天课程作业，掌握openGauss视图的管理：创建视图、删除视图、查询视图的信息、修改视图的信息。

课程练习

创建表，创建普通视图

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day18 RELATIVE LOCATION 'tablespace/day18';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb18 WITH TABLESPACE = day18;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user18 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user18  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb18 user18
Password for user user18: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb18" as user "user18".
user18@local:/opt/huawei/tmp [musicdb18]=>
You are now connected to database "musicdb18" as user "user18".
user18@local:/opt/huawei/tmp [musicdb18]=>create table tab_day18(id int,name varchar(20));
CREATE TABLE
user18@local:/opt/huawei/tmp [musicdb18]=>insert into tab_day18 values(1,'aaa'),(2,'bbb'),(3,'ccc'),(4,'ddd');
INSERT 0 4
user18@local:/opt/huawei/tmp [musicdb18]=>select * from tab_day18;
 id | name 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
  4 | ddd
(4 rows)

user18@local:/opt/huawei/tmp [musicdb18]=>create view view_day18 as select * from tab_day18 where id > 3;
CREATE VIEW
user18@local:/opt/huawei/tmp [musicdb18]=>select * from view_day18;
 id | name 
----+------
  4 | ddd
(1 row)
复制

使用视图创建新的视图

user18@local:/opt/huawei/tmp [musicdb18]=>create view view_day18_new as select * from view_day18;
CREATE VIEW
user18@local:/opt/huawei/tmp [musicdb18]=>select * from view_day18_new;
 id | name 
----+------
  4 | ddd
(1 row)
复制

创建物化视图

user18@local:/opt/huawei/tmp [musicdb18]=>create table tab_day181(id serial primary key,testnum serial);
NOTICE:  CREATE TABLE will create implicit sequence "tab_day181_id_seq" for serial column "tab_day181.id"
NOTICE:  CREATE TABLE will create implicit sequence "tab_day181_testnum_seq" for serial column "tab_day181.testnum"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tab_day181_pkey" for table "tab_day181"
CREATE TABLE
user18@local:/opt/huawei/tmp [musicdb18]=>insert into tab_day181(testnum) values(generate_series(1,100000));
INSERT 0 100000
user18@local:/opt/huawei/tmp [musicdb18]=>select count(1) from tab_day181;
 count  
--------
 100000
(1 row)

user18@local:/opt/huawei/tmp [musicdb18]=>create materialized view mv_day18 as 
musicdb18-> select * from tab_day181 where testnum%2=0;
CREATE MATERIALIZED VIEW
user18@local:/opt/huawei/tmp [musicdb18]=>select count(1) from mv_day18;
 count 
-------
 50000
(1 row)
--系统视图pg_views
user18@local:/opt/huawei/tmp [musicdb18]=>select * from pg_views where  schemaname = 'public';
 schemaname |    viewname    | viewowner |                     definition                     
------------+----------------+-----------+----------------------------------------------------
 public     | view_day18     | user18    | SELECT  * FROM tab_day18 WHERE (tab_day18.id > 3);
 public     | view_day18_new | user18    | SELECT  * FROM view_day18;
(2 rows)

--物化视图相关的系统表GS_MATVIEW
user18@local:/opt/huawei/tmp [musicdb18]=>select * from GS_MATVIEW;
 matviewid | mapid | ivm | needrefresh | refreshtime 
-----------+-------+-----+-------------+-------------
     24790 |     0 | f   |             | 
(1 row)
复制

手动更新物化视图

--向基表tab_day181插入数据，查看物化视图有多少行记录：
user18@local:/opt/huawei/tmp [musicdb18]=>insert into tab_day181(testnum) values(generate_series(1,1000));
INSERT 0 1000
user18@local:/opt/huawei/tmp [musicdb18]=>select count(*) from tab_day181;
 count  
--------
 101000
(1 row)

--手动更新物化视图，并查看更新物化视图后，物化视图有多少行记录：
user18@local:/opt/huawei/tmp [musicdb18]=>  refresh materialized view mv_day18;
REFRESH MATERIALIZED VIEW

user18@local:/opt/huawei/tmp [musicdb18]=>select count(*) from mv_day18;
 count 
-------
 50500
(1 row)
复制

删除创建的视图

--删除普通视图
user18@local:/opt/huawei/tmp [musicdb18]=>\dv
                 List of relations
 Schema |      Name      | Type | Owner  | Storage 
--------+----------------+------+--------+---------
 public | view_day18     | view | user18 | 
 public | view_day18_new | view | user18 | 
(2 rows)

user18@local:/opt/huawei/tmp [musicdb18]=>DROP view view_day18_new;
DROP VIEW
user18@local:/opt/huawei/tmp [musicdb18]=>DROP view view_day18;
DROP VIEW
--删除物化视图
user18@local:/opt/huawei/tmp [musicdb18]=>DROP MATERIALIZED VIEW mv_day18;
DROP MATERIALIZED VIEW
复制