openGauss每日一练第18天 - 学习心得体会

课后作业

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

omm=# create table test (i int, id number, name char(6) ,class varchar(10));
CREATE TABLE
omm=# create view v_test as select i , name from test;
CREATE VIEW
omm=# insert into test values (1,1,'xiaomi','1 class');
INSERT 0 1
omm=# select * from v_test;
(1 row)

omm=#  i |  name  
---+--------
 1 | xiaomi

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

omm=# create view v_v_test as select name from v_test;
CREATE VIEW
omm=# select * from v_v_test;
  name  
--------
 xiaomi
(1 row)

3.创建物化视图

omm=# create materialized view mv_test as select * from test where class = '1 class';
CREATE MATERIALIZED VIEW
omm=# select * from mv_test;
 i | id |  name  |  class  
---+----+--------+---------
 1 |  1 | xiaomi | 1 class
(1 row)

4.手动更新物化视图

omm=# insert into test values (2,2,'huawei','1 class');
omm=# INSERT 0 1

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

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

5.删除创建的视图

omm=# drop view v_test;
ERROR:  cannot drop view v_test because other objects depend on it
DETAIL:  view v_v_test depends on view v_test
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop view v_v_test;
DROP VIEW
omm=# drop view v_test;
DROP VIEW
omm=# drop materialized view mv_test;
DROP MATERIALIZED VIEW
omm=#