打卡第11天| 学习openGauss视图

打卡第11天| 学习openGauss视图

👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html

课程学习

连接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';omm=# CREATE VIEW tps_view AS
omm-# SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
CREATE VIEW
omm=# 

–查看视图

SELECT * FROM tps_view ;
omm=# select * from tps_view;
omm=#   spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
------------+----------+--------+------------+------------+----------
 pg_default |       10 |        |            |            | f
(1 row)

–创建一个由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;

omm# Create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.customer
omm-# (  c_customer_sk             integer,   
omm(#   c_customer_id             char(5),    
omm(# omm(#   c_first_name              char(6),    
  c_last_name               char(8) 
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO tpcds.customer 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=# CREATE VIEW tpcds.customer_details_view_v1 AS
omm-# SELECT * FROM tpcds.customer
omm-# WHERE c_customer_sk < 6000;
CREATE VIEW
omm=# 

–查看视图

select * from tpcds.customer_details_view_v1;
omm=# select * from tpcds.customer_details_view_v1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          4321 | 2             | Lily         | Carter  
(1 row)

omm=# 

–系统视图

select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';omm=# 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_d
efault'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 600
0);
(2 rows)

omm=# 

2.修改视图定义

–修改视图名称

ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
omm=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW
omm=# 

–修改视图所属schema

ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
omm=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW
omm=# 

–修改视图owner

create user jack password 'abcd@123';
alter view tps_view owner to jack;
omm=# create user jack password 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view tps_view owner to jack;
ALTER VIEW
omm=# 

–查看系统视图tps_views

select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';omm=# select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';
 schemaname |         viewname         | viewowner |                                    definition                    
                
------------+--------------------------+-----------+------------------------------------------------------------------
----------------
 public     | tps_view                 | jack      | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_d
efault'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 600
0);
(2 rows)

3.删除视图

DROP VIEW customer_details_view_v2;
Drop view tps_view;
omm=# DROP VIEW customer_details_view_v2;
DROP VIEW
omm=# Drop view tps_view;
DROP VIEW
omm=# 

课程作业

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

omm=# create view v_pg_database as select * from pg_database;
CREATE VIEW
omm=# alter view v_pg_database rename to v_pg_database_new;
ALTER VIEW
omm=# create user jim password '@163.com';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view v_pg_database_new owner to jim;
ALTER VIEW
omm=# 

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

omm=# create table student
omm-# ( id int,
omm(#   name varchar(30),
omm(#   address varchar(100),
omm(#   birthday date);
CREATE TABLE
omm=# create view v_student as select id, name from student;
CREATE VIEW
omm=# create schema sch1;
CREATE SCHEMA
omm=# alter view v_student set schema sch1;
ALTER VIEW
omm=# 

3.使用pg_views查看视图信息

omm=# select * from pg_views where schemaname = 'sch1' or schemaname='public';
 schemaname |     viewname      | viewowner |                  definition                   
------------+-------------------+-----------+-----------------------------------------------
 public     | v_pg_database_new | jim       | SELECT  * FROM pg_database;
 sch1       | v_student         | omm       | SELECT student.id, student.name FROM student;
(2 rows)

omm=# 

4.删除视图、表、用户

omm=# drop view sch1.v_student,public.v_pg_database_new;
DROP VIEW
omm=# 
omm=# drop table student;
DROP TABLE
omm=# drop schema sch1 cascade;
DROP SCHEMA
omm=# drop user jim;
DROP ROLE
omm=#