openGauss每日一练第11天 |学习openGauss视图

学习目标

学习openGauss视图

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

课程学习

连接openGauss

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

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';

2.修改视图定义

–修改视图名称

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,

omm=# create view test as select * from pg_database;
CREATE VIEW
omm=# alter view test rename to test1;
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 test1 owner to jim;
ALTER VIEW

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

omm=# Create schema st;
ERROR:  schema "st" already exists
omm=# CREATE TABLE st.student
omm-# (
omm(# student_id    char(5),    
omm(# student_name    char(6)   
omm(# );
CREATE TABLE

omm=# INSERT INTO st.student VALUES    
omm-# (1, 'Alice'),    
omm-# (2, 'Bob'),    
omm-# (3, 'Cindy'),
omm-# (4, 'Dog');
INSERT 0 4

omm=# CREATE VIEW st.student_v1 AS SELECT * FROM st.student;
CREATE VIEW
omm=# ALTER VIEW st.student_v1 SET schema public;
ALTER VIEW
omm=# select * from public.student_v1;
 student_id | student_name 
------------+--------------
 1          | Alice 
 2          | Bob   
 3          | Cindy 
 4          | Dog   
(4 rows)

3.使用pg_views查看视图信息

omm=# select * from pg_views where schemaname = 'public';
 schemaname |  viewname  | viewowner |         definition          
------------+------------+-----------+-----------------------------
 public     | test1      | jim       | SELECT  * FROM pg_database;
 public     | student_v1 | omm       | SELECT  * FROM st.student;
(2 rows)

4.删除视图、表、用户

omm=# drop view test1;
DROP VIEW
omm=# drop view student_v1 ;
DROP VIEW
omm=# drop table st.student ;
DROP TABLE
omm=# drop schema st ;
DROP SCHEMA
omm=# drop schema  jim ;
DROP SCHEMA