每日一练：openGauss数据库在线实训课程 ## 第十一天作业

0.分区表索引

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

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

CREATE VIEW db_view AS SELECT * FROM pg_database;
select * from pg_views where viewname = 'db_view';

create user jim password 'abcd@123';
alter view db_view owner to jim;
select * from pg_views where viewname = 'db_view';

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

CREATE schema tpcds;
CREATE TABLE tpcds.student
(  
    c_student_sk             integer,   
    c_student_id             char(5),    
    c_first_name             char(6),    
    c_last_name              char(8) 
) ;
INSERT INTO tpcds.student VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');

CREATE VIEW tpcds.student_details_view_v1 AS SELECT * FROM tpcds.student WHERE c_student_sk < 6000;
select * from pg_views where viewname = 'student_details_view_v1';

create schema tpcds_new;
ALTER VIEW tpcds.student_details_view_v1 SET schema tpcds_new;
select * from pg_views where viewname = 'student_details_view_v1';

3.使用pg_views查看视图信息

select * from pg_views where viewname = 'student_details_view_v1';

4.删除视图、表、用户

DROP VIEW jim.db_view; -- 报错
DROP VIEW db_view; 
DROP VIEW tpcds_new.student_details_view_v1;

• 学习openGauss视图