openGauss每日一练第18天

openGauss逻辑结构-视图管理

1.创建表，创建普通视图

--创建schema
omm=# create schema tpcds ;
CREATE SCHEMA

--创建表
omm=# CREATE TABLE tpcds.customer( c_customer_sk integer,c_customer_id char(5),c_first_name char(6),c_last_name char(8));
CREATE TABLE

--插入数据
omm=# INSERT INTO tpcds.customer VALUES (6885, 1, 'Joes', 'Hunter'),(4321, 2, 'Lily','Carter'),(9527, 3, 'James', 'Cook'),(9500, 4, 'Lucy', 'Baker');
INSERT 0 4

--创建视图
omm=# CREATE VIEW tpcds.customer_details_view_v1 AS SELECT * FROM tpcds.customer
omm-# ;
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  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

2.使用视图创建新的视图

---视图上创建视图
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;
omm=#  c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          9527 | 3             | James        | Cook    
(1 row)

3.创建物化视图

普通视图相当于SQL的逻辑集合，实际查询数据的时候还是从基表中查询。 物化视图，是真实的存储数据在磁盘中，基表变化，就需要更新物化视图

--创建基表
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;
CREATE MATERIALIZED VIEW
omm=# select count(*) from mv_test;
 count 
-------
 50000
(1 row)

4.手动更新物化视图

--再插入新的数据
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;
REFRESH MATERIALIZED VIEW

--查询刷新过后的物化视图，发现已经更新了数据
omm=#  select count(*) from mv_test;
 count  
--------
 100001
(1 row)

5.删除创建的视图

--drop普通视图
omm=# DROP view tpcds.part_view;
DROP VIEW
omm=# DROP view TPCDS.customer_details_view_v1;
DROP VIEW

--drop物化视图
omm=# DROP MATERIALIZED VIEW mv_test;
DROP MATERIALIZED VIEW