openGauss每日一练第11天

今天学习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;
  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;

–查看视图

 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)

–系统视图

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


 schemaname |         viewname         | viewowner |                                
    definition                                    
------------+--------------------------+-----------+--------------------------------
--------------------------------------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WH
ERE (pg_tablespace.spcname = 'pg_default'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer W
HERE (customer.c_customer_sk < 6000);
(2 rows)

2.修改视图定义
–修改视图名称

ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW

–修改视图所属schema

ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW
omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
 schemaname |         viewname         | viewowner |                                
    definition                                    
------------+--------------------------+-----------+--------------------------------
--------------------------------------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WH
ERE (pg_tablespace.spcname = 'pg_default'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer W
HERE (customer.c_customer_sk < 6000);
(2 rows)

–修改视图owner

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

–查看系统视图tps_views

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 WH
ERE (pg_tablespace.spcname = 'pg_default'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer W
HERE (customer.c_customer_sk < 6000);
(2 rows)

3.删除视图

DROP VIEW customer_details_view_v2;
Drop view tps_view;

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

create view view1 as select * from pg_database;
CREATE VIEW
omm=# select * from view1;
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallo
wconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibili
ty |        datacl        | datfrozenxid64 
-----------+--------+----------+-------------+-------------+---------------+--------
------+--------------+---------------+--------------+---------------+---------------
---+----------------------+----------------
 template1 |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | t             | t      
      |           -1 |         14176 | 0            |          1663 | PG            
   | {=c/omm,omm=CTc/omm} |           8176
 omm       |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t      
      |           -1 |         14176 | 7891         |          1663 | PG            
   |                      |           7891
 template0 |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | t             | f      
 postgres  |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t      
      |           -1 |         14176 | 0            |          1663 | PG            
   |                      |           8461
(4 rows)

      |           -1 |         14176 | 0            |          1663 | PG            
   | {=c/omm,omm=CTc/omm} |           7891
omm=# create user jim identified by 'abcd@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view view1 owner to jim;
ALTER VIEW

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

create schema test1;
CREATE SCHEMA
omm=# create table student (id int,name varchar(20));
CREATE TABLE
omm=# insert into student values(1,'aaa');
INSERT 0 1
omm=# insert into student values(2,'bbb'),(3,'cccc'),(4,'ddd');
INSERT 0 3
omm=# create view view2 as select * from student where id<3;
CREATE VIEW
omm=# select * from view2;
id | name 
----+------
 1 | aaa
 2 | bbb
(2 rows)
omm=# alter view view2 set schema test1;
ALTER VIEW

3.使用pg_views查看视图信息

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

4.删除视图、表、用户

omm=# drop view view1;
DROP VIEW
omm=# drop view test1.view2;
DROP VIEW
omm=# drop table student;
omm=# DROP TABLE

omm=# drop user jim;
DROP ROLE