openGauss每日一练 第11天 视图

学习目标

学习openGauss视图

视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

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

CREATE VIEW dbs_view AS
SELECT * FROM pg_database  ;

ALTER VIEW dbs_view RENAME TO dbs_view_v2;
create user jim password 'abcd@123';
alter view dbs_view_v2 owner to jim;

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

Create schema tpcds;
CREATE TABLE tpcds.student
( c_student_sk             integer,   
  c_student_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
INSERT INTO tpcds.student VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

CREATE VIEW tpcds.student_v1 AS
SELECT * FROM tpcds.student
WHERE c_student_sk < 6000;

select * from tpcds.student_v1 ;
ALTER VIEW tpcds.student_v1  SET schema public;

3.使用pg_views查看视图信息

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

4.删除视图、表、用户

DROP VIEW student_v1;
Drop view dbs_view_v2;

DROP SCHEMA tpcds CASCADE;
DROP USER jim;
\dn

作业执行结果

omm=# CREATE VIEW dbs_view AS
omm-# SELECT * FROM pg_database  ;
CREATE VIEW
omm=# 
omm=# ALTER VIEW dbs_view RENAME TO dbs_view_v2;
ALTER VIEW
omm=# create user jim password 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view dbs_view_v2 owner to jim;
ALTER VIEW
omm=# 
omm=# Create schema tpcds;
CREATE TABLE tpcds.student
omm-# CREATE SCHEMA
omm=# ( c_student_sk             integer,   
omm(#   c_student_id             char(5),    
omm(#   c_first_name              char(6),    
omm(#   c_last_name               char(8) 
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO tpcds.student VALUES    
omm-# (6885, 1, 'Joes', 'Hunter'),    
omm-# (4321, 2, 'Lily','Carter'),    
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# 
omm=# CREATE VIEW tpcds.student_v1 AS
omm-# SELECT * FROM tpcds.student
omm-# WHERE c_student_sk < 6000;
CREATE VIEW
omm=# 
omm=# select * from tpcds.student_v1 ;
 c_student_sk | c_student_id | c_first_name | c_last_name 
--------------+--------------+--------------+-------------
         4321 | 2            | Lily         | Carter  
(1 row)

omm=# ALTER VIEW tpcds.student_v1  SET schema public;
ALTER VIEW
omm=# 
omm=# select * from student_v1 ;
 c_student_sk | c_student_id | c_first_name | c_last_name 
--------------+--------------+--------------+-------------
         4321 | 2            | Lily         | Carter  
(1 row)

omm=# select * from pg_views where viewname = 'student_v1' or viewname = 'tps_view';
 schemaname |  viewname  | viewowner |                            definition                     
        
------------+------------+-----------+-----------------------------------------------------------
--------
 public     | student_v1 | omm       | SELECT  * FROM tpcds.student WHERE (student.c_student_sk <
 6000);
(1 row)

omm=# 
omm=# 
omm=# DROP VIEW student_v1;
DROP VIEW
omm=# Drop view dbs_view_v2;
DROP VIEW
omm=# 
omm=# DROP SCHEMA tpcds CASCADE;
NOTICE:  drop cascades to table tpcds.student
DROP SCHEMA
omm=# DROP USER jim;
DROP ROLE