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

👉openGauss SQL学习参考

学习目标

学习openGauss视图

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

课程学习

连接openGauss

root@modb:~# su - omm
omm@modb:~$ gsql -r   

1.创建视图

––为系统表 pg_tablespace 创建字段 spcname 为 pg_default 组成的视图
CREATE VIEW tps_view AS
SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

--查看视图
mydb=# SELECT * FROM tps_view ;
  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
------------+----------+--------+------------+------------+----------
 pg_default |       10 |        |            |            | f
(1 row)

mydb=# 

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

mydb=# select * from tpcds.customer ;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

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

--查看视图
mydb=# 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)

--系统视图
mydb=# 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)

mydb=# 

2.修改视图定义

--修改视图名称
mydb=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW

--–修改视图所属 schema
mydb=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW

--修改视图 owner
create user jack password 'abcd@123';
alter view tps_view owner to jack;

--查看系统视图 tps_views
mydb=# 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_default'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

mydb=# 

3.删除视图

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

课后作业

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

mydb=# CREATE VIEW pgdb_view AS
mydb-# SELECT datname,encoding,datconnlimit FROM PG_DATABASE  ;
CREATE VIEW
mydb=#  ALTER VIEW pgdb_view RENAME TO pgdb_view_v2;
ALTER VIEW
mydb=# \dv+
                           List of relations
 Schema |     Name     | Type | Owner |  Size   | Storage | Description 
--------+--------------+------+-------+---------+---------+-------------
 public | pgdb_view_v2 | view | omm   | 0 bytes |         | 
(1 row)

mydb=# alter view pgdb_view_v2 owner to jack;
ALTER VIEW
mydb=# \dv+
                           List of relations
 Schema |     Name     | Type | Owner |  Size   | Storage | Description 
--------+--------------+------+-------+---------+---------+-------------
 public | pgdb_view_v2 | view | jack  | 0 bytes |         | 
(1 row)

mydb=# 

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

CREATE TABLE tpcds.student(id int,name char(10));
insert into tpcds.student values
(1,'aaa'),
(2,'bbb');

mydb=# CREATE VIEW tpcds.stu_view AS  select name from tpcds.student;
CREATE VIEW
mydb=# select * from tpcds.stu_view;
    name    
------------
 aaa       
 bbb       
(2 rows)

mydb=# ALTER VIEW tpcds.stu_view SET schema public;
ALTER VIEW
mydb=# \dv+
                           List of relations
 Schema |     Name     | Type | Owner |  Size   | Storage | Description 
--------+--------------+------+-------+---------+---------+-------------
 public | pgdb_view_v2 | view | jack  | 0 bytes |         | 
 public | stu_view     | view | omm   | 0 bytes |         | 
(2 rows)

mydb=# 

3.使用 pg_views 查看视图信息

mydb=#  select * from pg_views where viewname = 'stu_view';
 schemaname | viewname | viewowner |               definition                
------------+----------+-----------+-----------------------------------------
 public     | stu_view | omm       | SELECT student.name FROM tpcds.student;
(1 row)

4.删除视图、表、用户

mydb=# drop view stu_view;
DROP VIEW
mydb=# drop view pgdb_view_v2;
DROP VIEW
mydb=# drop table tpcds.student;
DROP TABLE
mydb=# drop table tpcds.customer;
DROP TABLE
mydb=# drop user jack;
DROP ROLE