openGauss开源数据库实战十四

任务十四 openGauss 逻辑结构:视图管理

任务目标

在三级模式二级映射体系中,可以将概念模式映射为外模式。视图是将概念模式映射为外模式的手段。本任务的目的是掌握openGauss视图的管理:创建视图、删除视图、查询视图的信息、修改视图的信息。

实施步骤

一、准备工作

执行下列的命令和语句,创建一个名叫pupil的数据库用户,并授予用户pupil访问数据库pupildb的权限:

gsql -d postgres -p 26000 -r

CREATE USER pupil IDENTIFIED BY 'pupil@ustb2020';
GRANT CONNECT on DATABASE studentdb TO pupil;
\q

二、为什么需要视图

1.通过视图对用户隐藏信息

使用Linux用户omm,打开一个Linux终端窗口,执行如下命令,使用用户student连接到openGauss的数据库studentdb,并查询表instructor的内容:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

select * from instructor;

我们发现,只要有访问表instructor的权限,用户就能看到该表的所有信息。要想限制用户权限使其不能看到教师的salary信息,方法是创建一个视图,并授予用户pupil读取视图faculty的权限:

create or replace view faculty as
    select ID, name, dept_name
    from instructor;
GRANT SELECT ON faculty TO pupil;
\q

打开另外一个Linux终端窗口,使用数据库用户pupil连接到数据库studentdb:

gsql -d studentdb -h 192.168.100.91 -U pupil -p 26000 -W pupil@ustb2020 -r

执行下面的gsql元命令,看看数据库用户pupil能查看到哪些表名和视图名:

\dtv

执行下面的SQL语句,查看视图faculty的内容:

select * from faculty;

执行下面的SOL语句,查看表instructor的内容:

select * from instructor;
\q

由于数据库用户pupil没有被授权访问表instructor,因此它无法看到表instructor的内容,但是数据库用户pupil被授权可以访问视图faculty,而视图faculty屏蔽了表instructor中的敏感信息salary。

2.创建一个比逻辑模型更符合用户直觉的表

使用Linux用户omm,打开一个Linux终端窗口,执行如下命令,使用用户student连接到openGauss的数据库studentdb:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

假如希望有一个关于物理系在2009年秋季学期所开设的所有课程段的表,可以创建以下的视图:

create or replace view physics_fall_2009 as
     select course.course_id, sec_id, building, room_number
     from course, section
     where course.course_id = section.course_id and 
           course.dept_name = 'Physics' and 
           section.semester = 'Fall' and 
           section.year = '2009';
select * from  physics_fall_2009;

然后我们可以把视图当成一个表来使用:

select * 
from  physics_fall_2009
where building= 'Watson';

在查询中,视图名可以出现在关系名可以出现的任何地方。例如,查找生物系的所有教师的信息(除了工资以外):

select * 
from faculty
where dept_name ='Biology';

三、创建视图时指定视图的属性名

执行下面的SOL语句,创建一个视图departments_total_salary,显示每个系所有教师的工资总和:

create or replace view departments_total_salary(dept_name, total_salary) as
      select dept_name, sum(salary)
      from instructor
      group by dept_name;

执行下面的gsql元命令,查看数据库有哪些视图:

\dv

执行下面的gsql元命令,查看视图departments_total_salary的信息:

\dv departments_total_salary;

执行下面的gsql元命令,查看视图departments_total_salary的详细信息:

\d departments_total_salary;

可以看出,该视图的属性名是在创建视图时由用户指定的。
可以把视图当成一个表来使用:

Select * From departments_total_salary;

四、基于视图建立新的视图

我们使用前面创建的视图physics_fall_2009来创建视图physics_fall_2009_watson,该视图是关于物理系在2009年秋季学期所开设的所有在Watson大楼上课的课程段的。

create or replace view physics_fall_2009_watson as
     select course_id, room_number
     from physics_fall_2009
     where building= 'Watson';

这条语句相当于:

create or replace view physics_fall_2009_watson_1 as
     select course_id, room_number
     from (
            select course.course_id, sec_id, building, room_number
            from course, section
            where course.course_id = section.course_id and 
                  course.dept_name = 'Physics' and 
                  section.semester = 'Fall' and 
                  section.year = '2009'
            ) tbl
     where building= 'Watson';

\q

五、物化视图

普通视图在查询中是实时进行计算的。如果建立视图的基表数据很多,使用视图的时候进行实时计算视图表示的结果集,将消耗很大的计算机资源,并且费时很长。
物化视图可提前计算出视图的结果集,并将该结果集保存在数据库里。
如果更新了基表,物化视图将过期。也就是说,基表更新后,物化视图不能反映最新的数据情况。因此,在基表发生变化的时候,需要对物化视图进行更新。
更新物化视图的方法有两种:实时刷新、延迟刷新(可周期性自动刷新或手动刷新)。
创建一个测试表test:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

drop materialized view if exists mv_test;
drop table if exists test;
create table test(id serial primary key,testnum serial);
insert into test(testnum) values(generate_series(1,100000));

创建物化视图:

create materialized view mv_test as 
select * 
from test 
where testnum%2=0;

查看物化视图目前有多少行记录:

select count(*) from mv_test;

为物化视图的基表test添加100000行,然后再查看物化视图当前有多少行记录:

insert into test(testnum) values(generate_series(1,100000));
select count(*) from mv_test;

我们发现,虽然物化视图mv_test的基表test添加了更多的行,但是物化视图并没有更新。
我们可以手动更新物化视图,并查看物化视图更新后有多少行记录:

refresh materialized view mv_test;
select count(*) from mv_test;
\q

六、视图失效(openGauss暂不支持)

七、通过视图更新基表(openGauss暂不支持)

八、清理工作

在继续后面的任务之前,关闭所有的Linux终端窗口(或者退出所有的openGaussgsql会话),打开一个Linux终端窗口,执行下面的命令和SQL语句,进行清理:

gsql -d studentdb -h 192.168.100.91 -U student -p 26000 -W student@ustb2020 -r

revoke all on faculty from pupil;
revoke CONNECT on DATABASE studentdb FROM pupil;
drop view physics_fall_2009_watson_1;
drop view physics_fall_2009_watson;
drop view physics_fall_2009;
drop view faculty;
drop materialized view if exists mv_test;
drop table if exists test;
\q