openGauss每日一练第11天 | 数据库视图的操作

打卡第11天，学习opengauss视图;

视图是一张虚表，不存储数据；

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/CREATE-VIEW.html

语法格式

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query;

课程作业

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

openGauss=# create or replace view view_db as select * from pg_database; 
CREATE VIEW

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

openGauss=# create user jim password 'abcd@123';
CREATE ROLE

openGauss=# alter view view_db owner to jim;
ALTER VIEW

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

openGauss=# create table student(id int,name char(50));
CREATE TABLE
openGauss=# insert into student(id,name) values(1,'aa'),(2,'bb'),(3,'cc');
INSERT 0 3

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

openGauss=# alter view view_st set schema  jim;
ALTER VIEW

3.使用pg_views查看视图信息

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

4.删除视图、表、用户

openGauss=# drop view view_db;
DROP VIEW

openGauss=# drop view jim.view_st;
DROP VIEW
openGauss=# drop table student;
DROP TABLE

openGauss=# drop user jim;
DROP ROLE
openGauss=#