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

学习目标

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

学习内容

通过本章节学习了解了视图和表之间的管理，视图基本管理。物化视图的创建和更新。

课程作业

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

创建表

openGauss=# create table student(id int primary key, name varchar(50) not null, age int default 19);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "student_pkey" for table "student"
CREATE TABLE
openGauss=# alter table student add column class  bigint;
ALTER TABLE
openGauss=# \d+ student
                               Table "public.student"
 Column |         Type          | Modifiers  | Storage  | Stats target | Description
--------+-----------------------+------------+----------+--------------+-------------
 id     | integer               | not null   | plain    |              |
 name   | character varying(50) | not null   | extended |              |
 age    | integer               | default 19 | plain    |              |
 class  | bigint                |            | plain    |              |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=# create index idex_stu_class on student(class);
CREATE INDEX
openGauss=# \di
                        List of relations
 Schema |      Name      | Type  |   Owner   |  Table  | Storage
--------+----------------+-------+-----------+---------+---------
 public | idex_stu_class | index | opengauss | student |
 public | student_pkey   | index | opengauss | student |
(2 rows)

openGauss=#
openGauss=# insert into student (id,name,class) values (10001,'测试0001',10001),(10002,'测试0002',10001),(10003,'测试0003',10001);
INSERT 0 3

创建视图：

openGauss=# create view view_student as select * from student;
CREATE VIEW
openGauss=# select * from view_student;
  id   |   name   | age | class
-------+----------+-----+-------
 10001 | 测试0001 |  19 | 10001
 10002 | 测试0002 |  19 | 10001
 10003 | 测试0003 |  19 | 10001
(3 rows)

openGauss=# insert into student (id,name,class) values (10004,'测试0004',10001);
INSERT 0 1  ^
openGauss=# select * from student;
  id   |   name   | age | class
-------+----------+-----+-------
 10001 | 测试0001 |  19 | 10001
 10002 | 测试0002 |  19 | 10001
 10003 | 测试0003 |  19 | 10001
 10004 | 测试0004 |  19 | 10001
(4 rows)

openGauss=# select * from view_student;
  id   |   name   | age | class
-------+----------+-----+-------
 10001 | 测试0001 |  19 | 10001
 10002 | 测试0002 |  19 | 10001
 10003 | 测试0003 |  19 | 10001
 10004 | 测试0004 |  19 | 10001
(4 rows)

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

openGauss=# create VIEW part_view as SELECT * FROM student where id =10004;
CREATE VIEW
openGauss=# select * from part_view ;
  id   |   name   | age | class
-------+----------+-----+-------
 10004 | 测试0004 |  19 | 10001
(1 row)

openGauss=# SELECT * FROM student where id =10004;
  id   |   name   | age | class
-------+----------+-----+-------
 10004 | 测试0004 |  19 | 10001
(1 row)

3.创建物化视图

openGauss=# create table t2(id serial,id2 int);
NOTICE:  CREATE TABLE will create implicit sequence "t2_id_seq" for serial column "t2.id"
CREATE TABLE
openGauss=# insert into t2(id2) values(generate_series(1,10000));
INSERT 0 10000
openGauss=# --创建物化视图：
openGauss=# create materialized view mv_t2 as select * from t2 where id2<1000;
CREATE MATERIALIZED VIEW
openGauss=# select count(*) from mv_t2;
 count
-------
   999
(1 row)

4.手动更新物化视图

openGauss=# insert into t2(id2) values(generate_series(1,10000));
INSERT 0 10000
openGauss=# select count(*) from mv_t2;
 count
-------
   999
(1 row)
openGauss=# select count(*) from t2 where id2<1000;
 count
-------
  1998
(1 row)
openGauss=# refresh materialized view mv_t2;
REFRESH MATERIALIZED VIEW
openGauss=# select count(*) from mv_t2;
 count
-------
  1998
(1 row)

5.删除创建的视图

openGauss=# --系统视图pg_views
openGauss=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
 schemaname |         viewname         | viewowner |                                              definition

------------+--------------------------+-----------+------------------------------------------------------------------------------------
------------------
 tpcds      | customer_details_view_v1 | opengauss | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk > 5400);
 tpcds      | part_view                | opengauss | SELECT  * FROM tpcds.customer_details_view_v1 WHERE (customer_details_view_v1.c_cus
tomer_sk = 9527);
 public     | view_student             | opengauss | SELECT  * FROM student;
 public     | part_view                | opengauss | SELECT  * FROM student WHERE (student.id = 10004);
(4 rows)
openGauss=# --GS_MATVIEW系统表提供了关于数据库中每一个物化视图的信息。
openGauss=# select * from GS_MATVIEW;
 matviewid | mapid | ivm | needrefresh |        refreshtime
-----------+-------+-----+-------------+----------------------------
     19506 |     0 | f   |             | 2022-12-11 19:11:40.079133
(1 row)

删除视图

openGauss=# \dv
                 List of relations
 Schema |     Name     | Type |   Owner   | Storage
--------+--------------+------+-----------+---------
 public | part_view    | view | opengauss |
 public | view_student | view | opengauss |
(2 rows)

openGauss=# drop view part_view;
DROP VIEW
openGauss=# \dv
                 List of relations
 Schema |     Name     | Type |   Owner   | Storage
--------+--------------+------+-----------+---------
 public | view_student | view | opengauss |
(1 row)