opengauss每日一练第11天-课后作业

学习目标
学习openGauss视图

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

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

CREATE VIEW pg_db_view AS SELECT * FROM PG_DATABASE ;

ALTER VIEW pg_db_view RENAME TO pg_db_view1;

create schema jim;

ALTER VIEW pg_db_view1  SET schema jim;

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

Create schema student;
CREATE TABLE student.customer
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;

INSERT INTO student.customer VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

CREATE VIEW student.customer_details_view_v1 AS SELECT * FROM student.customer WHERE c_customer_sk < 6000;

ALTER VIEW student.customer_details_view_v1 SET schema public;

3.使用pg_views查看视图信息

select * from pg_views where viewname = 'customer_details_view_v1' or viewname = 'pg_db_view1';

4.删除视图、表、用户
drop view public.customer_details_view_v1;
drop table student.customer  cascade;
drop schema student cascade;

drop view jim.pg_db_view1;
drop schema jim cascade;