学习目标
学习openGauss视图
视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
课程学习
连接openGauss
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
1.创建视图
–为系统表pg_tablespace创建字段spcname为pg_default组成的视图
CREATE VIEW tps_view AS
SELECT * FROM pg_tablespace WHERE spcname = ‘pg_default’;
–查看视图
SELECT * FROM tps_view ;
–创建一个由c_customer_sk小于6000的内容组成的视图
Create schema tpcds;
CREATE TABLE tpcds.customer
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO tpcds.customer VALUES
(6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk < 6000;
–查看视图
select * from tpcds.customer_details_view_v1;
–系统视图
select * from pg_views where schemaname = ‘tpcds’ or schemaname = ‘public’;
2.修改视图定义–修改视图名称
ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
–修改视图所属schema
ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
–修改视图owner
create user jack password ‘abcd@123’;
alter view tps_view owner to jack;
–查看系统视图tps_views
select * from pg_views where viewname = ‘customer_details_view_v2’ or viewname = ‘tps_view’;
3.删除视图
DROP VIEW customer_details_view_v2;
Drop view tps_view;
课程作业
1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,
-- 1.
create view v1 as
select * from pg_database;
alter view v1 rename to v1_1;
create user jim password 'abc@12345';
alter view v1_1 owner to jim;
2.创建一个用户表student，并在用户表上创建视图，修改视图schema;
-- 2.
create table student (id int);
create view v2 as select * from student;
alter view v2 set schema jim;
3.使用pg_views查看视图信息
-- 3.
select * from pg_views where viewname = 'v1_1' or viewname = 'v2';
4.删除视图、表、用户
-- 4.
drop view v1_1;
drop view jim.v2;
drop table student;
drop user jim;
学习心得
视图是表的延伸，实际使用时需要注意视图owner的设定及其权限。
参考文档
openGauss产品文档:
PG_VIEWS
PG_DATABASE
松鼠镇楼，按时打卡
