openGauss每日一练第11天，视图

openGauss每日一练第11天课后作业

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

create view db_view as
select * from pg_database;

alter view
db_view rename to db_view2;

create user jim
password 'gauss_4U';

alter view
db_view2 owner to jim;

select * from pg_views where viewname='db_view2';

\dv

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

create table
student (

id int,

first_name
char(20),

last_name
char(20)

);

insert into
student values

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

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

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

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

create view
student_view as select * from student where id>2;

create schema
school;

select * from student_view;

alter view
student set schema school;

\dv

\dv school.*

3.使用pg_views查看视图信息

select * from
pg_views where viewname='db_view2' or viewname='student_view';

4.删除视图、表、用户

drop view
db_view2;

drop view
school.student_view;

drop table
student;

drop user jim
cascade;

drop schema
school;