openGauss每日一练第 11 天 | 视图管理及课后作业

openGauss 每日一练第 11 天学习打卡，巩固 openGauss 数据库视图管理基本操作！

学习目标

学习 openGauss 视图管理

前面每日一练链接：

openGauss每日一练第 1 天 | 数据库和表的基本操作（一）
openGauss每日一练第 2 天 | 数据库和表的基本操作（二）
openGauss每日一练第 3 天 | 前三课作业实操练习
openGauss每日一练第 4 天 | 角色管理及课后作业
openGauss每日一练第 5 天 | 用户管理及课后作业
openGauss每日一练第 6 天 | 模式管理及课后作业
openGauss每日一练第 7 天 | 表空间管理及课后作业
openGauss每日一练第 8 天 | 分区表管理及课后作业
openGauss每日一练第 9 天 | 普通表索引管理及课后作业
openGauss每日一练第 10 天 | 分区表索引管理及课后作业

课程学习

图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。

注意：在启动 gsql 的命令行中加上“-E”参数，就可以把 gsql 中各种以“\”开头的命令执行的实际 SQL 语句打印出来.如果在已运行的 gsql 中显示了某个命令实际执行的 SQL 语句后又想关闭此功能，该怎么办？这时可以使用“\set ECHO_HIDDEN on|off”命令。

连接 openGauss

su - omm
gsql -r

gsql -r -h localhost -U user1 -W JiekeXu12 -d postgres

1.创建视图

–为系统表 pg_tablespace 创建字段 spcname 为 pg_default 组成的视图

CREATE VIEW tps_view AS
SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

–查看视图

SELECT * FROM tps_view ;

–创建一个由 c_customer_sk 小于 6000 的内容组成的视图

Create schema tpcds;
CREATE TABLE tpcds.customer
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;

INSERT INTO tpcds.customer VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk < 6000;

–查看视图

select * from tpcds.customer_details_view_v1;

–系统视图

select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';

jiekexu=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
 schemaname |         viewname         | viewowner |                                    definition                                    
------------+--------------------------+-----------+----------------------------------------------------------------------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

2.修改视图定义

ALTER VIEW 更改视图的各种辅助属性。（如果用户是更改视图的查询定义，要使用CREATE OR REPLACE VIEW。）

只有视图的所有者或者被授予了视图 ALTER 权限的用户才可以执行 ALTER VIEW 命令，系统管理员默认拥有该权限。针对所要修改属性的不同，对其还有以下权限约束：

• 修改视图的模式，当前用户必须是视图的所有者或者系统管理员，且要有新模式的 CREATE 权限。
• 修改视图的所有者，当前用户必须是视图的所有者或者系统管理员，且该用户必须是新所有者角色的成员，并且此角色必须有视图所在模式的CREATE 权限。

–修改视图名称

ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

–修改视图所属 schema

ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

–修改视图 owner

create user jack password 'abcd@123';
alter view tps_view owner to jack;

–查看系统视图 tps_views

select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';

3.删除视图

DROP VIEW customer_details_view_v2;
Drop view tps_view;

课程作业

1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,

create view db_view as select * from pg_database;
alter view db_view rename to pgdb_view;
select * from pg_views where viewname = 'pgdb_view' or viewname = 'db_view';
create user jiekexu password 'JiekeXu1';
alter view pgdb_view owner to jiekexu;

select * from pg_views where viewname = 'pgdb_view' or viewname = 'db_view';
=================================

jiekexu=# create view db_view as select * from pg_database;
CREATE VIEW
jiekexu=# \d+ db_view
                       View "public.db_view"
      Column      |   Type    | Modifiers | Storage  | Description 
------------------+-----------+-----------+----------+-------------
 datname          | name      |           | plain    | 
 datdba           | oid       |           | plain    | 
 encoding         | integer   |           | plain    | 
 datcollate       | name      |           | plain    | 
 datctype         | name      |           | plain    | 
 datistemplate    | boolean   |           | plain    | 
 datallowconn     | boolean   |           | plain    | 
 datconnlimit     | integer   |           | plain    | 
 datlastsysoid    | oid       |           | plain    | 
 datfrozenxid     | xid32     |           | plain    | 
 dattablespace    | oid       |           | plain    | 
 datcompatibility | name      |           | plain    | 
 datacl           | aclitem[] |           | extended | 
 datfrozenxid64   | xid       |           | plain    | 
View definition:
 SELECT  *
   FROM pg_database;

jiekexu=# select * from pg_views where viewname = 'pgdb_view' or viewname = 'db_view';
jiekexu=#  schemaname | viewname | viewowner |         definition          
------------+----------+-----------+-----------------------------
 public     | db_view  | omm       | SELECT  * FROM pg_database;
(1 row)


jiekexu=# alter view db_view rename to pgdb_view;
ALTER VIEW
jiekexu=# select * from pg_views where viewname = 'pgdb_view' or viewname = 'db_view';
 schemaname | viewname  | viewowner |         definition          
------------+-----------+-----------+-----------------------------
 public     | pgdb_view | omm       | SELECT  * FROM pg_database;
(1 row)

jiekexu=# create user jiekexu password 'JiekeXu1';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
jiekexu=# alter view pgdb_view owner to jiekexu;
ALTER VIEW
jiekexu=# select * from pg_views where viewname = 'pgdb_view' or viewname = 'db_view';
 schemaname | viewname  | viewowner |         definition          
------------+-----------+-----------+-----------------------------
 public     | pgdb_view | jiekexu   | SELECT  * FROM pg_database;
(1 row)

2.创建一个用户表 student，并在用户表上创建视图，修改视图 schema;

create schema std;

CREATE TABLE std.student
(  student_sk             integer,   
  student_id             char(5),    
  student_first_name     char(6),    
  student_last_name      char(8) 
) ;

INSERT INTO std.student VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

create view std.student_view as select * from  std.student where student_id > 1;

--查看视图
select * from std.student_view;
select * from pg_views where viewname= 'student_view';

--注意：表和视图名不能重复，这里和 Oracle 是有区别的。
jiekexu=# create view std.student as select * from  std.student where student_id > 1;
ERROR:  relation "student" already exists

--修改视图 schema
alter view std.student set schema public;

3.使用 pg_views 查看视图信息

jiekexu=# select * from pg_views where viewname= 'student_view';
 schemaname |   viewname   | viewowner |                              definition                              
------------+--------------+-----------+----------------------------------------------------------------------
 public     | student_view | omm       | SELECT  * FROM std.student WHERE ((student.student_id)::bigint > 1);
(1 row)

4.删除视图、表、用户

drop view student_view;
drop table std.student;
--因用户 jiekexu 下存在视图 pgdb_view ,故需要加 cascade 删除用户。
drop user jiekexu cascade;

欧耶，第十一课视图管理实操及课后作业练习题完成啦！