课后作业

连接openGauss

su - omm
gsql -r

1.创建视图

1.1为系统表PG_DATABASE创建视图

CREATE VIEW view_PG AS
SELECT * FROM PG_DATABASE ;

1.2在用户表上创建视图

#创建表
CREATE TABLE student
(
    s_student_age   integer,
    s_student_name  char(30)
);
#插入数据
INSERT INTO student VALUES
(20, 'Joes'),
(30, 'Lucy'),
(25, 'Jame');
#创建视图
CREATE VIEW view_st AS
SELECT * FROM student WHERE s_student_age<30;

1.3查看视图

select * from view_PG;

select * from view_st;

1.4使用pg_views查看视图信息

select * from pg_views where viewname = 'view_st';

select * from pg_views where viewname = 'view_PG';

2.修改视图属性

2.1重命名视图

ALTER VIEW view_PG RENAME TO view_pg_;

2.2修改视图owner

create user jim password 'abcd@123';
alter view view_pg_ owner to jim;

2.3修改视图schema

CREATE schema ma;
ALTER VIEW view_st SET schema ma;

注：view_st变为ma.view_st！！！

2.4使用pg_views查看视图信息

select * from pg_views where viewname = 'view_st';

select * from pg_views where viewname = 'view_pg_';

3.删除视图

3.1删除视图

DROP VIEW view_pg_;
DROP VIEW ma.view_st;

3.2删除用户

DROP user jim;

3.3删除表

DROP TABLE student;

注：先删除视图后才可删除用户和表！！！