openGauss每日一练第11天 | 视图

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

作业打卡:

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

CREATE VIEW pgdatabase_v1 AS SELECT * FROM PG_DATABASE ;

ALTER VIEW pgdatabase_v1 RENAME TO pgdatabase_v111;

create user jim password 'PassWord123';

alter view pgdatabase_v111 owner to jim;

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

create schema pysql;

CREATE TABLE pysql.student

(  c_student_sk             integer,   

  c_student_id             char(5),    

  c_first_name              char(6),    

  c_last_name               char(8)

) ;

INSERT INTO pysql.student VALUES    

(6885, 1, 'Joes', 'Hunter'),    

(4321, 2, 'Lily','Carter'),    

(9527, 3, 'James', 'Cook'),

(9500, 4, 'Lucy', 'Baker');

CREATE VIEW pysql.student_view_v1 AS SELECT * FROM pysql.student;

ALTER VIEW pysql.student_view_v1 SET schema public;

select * from public.student_view_v1;

3.使用pg_views查看视图信息

select * from pg_views where schemaname = 'public';

4.删除视图、表、用户

drop view pgdatabase_v1;

drop view student_view_v1;

drop table pysql.student;

drop schema pysql;

drop schema jim;