课程作业

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

--创建表
omm=# create table zy(id int,name varchar(20) not null,age int default 20,primary key(id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zy_pkey" for table "zy"
CREATE TABLE
-- 插入数据
omm=# insert into zy values(1,'zouyang');
INSERT 0 1
omm=# insert into zy values(2,'zhangsan');
INSERT 0 1
omm=# insert into zy values(3,'lisi');
INSERT 0 1
--创建视图
omm=# create view view_zouyang as
omm-# select * from zy where name='zouyang';
CREATE VIEW
--查看视图
omm=# select * from view_zouyang;
id | name | age
----+---------+-----
1 | zouyang | 20
(1 row)

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

omm=# create view view_zy as select * from view_zouyang;
omm=# CREATE VIEW
omm=# select * from view_zy;
id | name | age
----+---------+-----
1 | zouyang | 20
(1 row)
--更新基表
omm=# update zy set age=33 where id=1;
UPDATE 1
--更新基表后视图也会实时变化的
omm=# select * from view_zy;
id | name | age
----+---------+-----
1 | zouyang | 33
(1 row)

3.创建物化视图

--创建物化视图
omm=# create materialized view mv_zouyang as select * from zy;
omm=# CREATE MATERIALIZED VIEW
--查看物化视图
omm=# select * from mv_zouyang;
id | name | age
----+----------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyang | 33
(4 rows)

4.手动更新物化视图

-未更新基表，发现数据是一直的
omm=# select * from zy;
id | name | age
----+----------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyang | 33
(4 rows)

omm=# select * from mv_zouyang;
id | name | age
----+----------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyang | 33
(4 rows)
--更新基表后，发现物化视图是不会变
omm=# update zy set name='zouyangbak' where id=1;
UPDATE 1
omm=# select * from zy;
id | name | age
----+------------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyangbak | 33
(4 rows)

omm=# select * from mv_zouyang;
id | name | age
----+----------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyang | 33
(4 rows)
--尝试手工刷新
omm=# refresh materialized view mv_zouyang;
REFRESH MATERIALIZED VIEW
omm=# select * from zy;
id | name | age
----+------------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyangbak | 33
(4 rows)
omm=# select * from mv_zouyang;
id | name | age
----+------------+-----
2 | zhangsan | 20
3 | lisi | 20
4 | wangwu | 20
1 | zouyangbak | 33
(4 rows)

5.删除创建的视图

omm=# drop view zouyang ;
DROP VIEW
omm=# drop view view_zy;
DROP VIEW
omm=# drop MATERIALIZED VIEW mv_zouyang;
DROP MATERIALIZED VIEW