第十一课

学习openGauss视图

👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html

学习目标

学习openGauss视图

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

数据库环境

是我个人自己安装部署的OpenGauss单机版

OpenGauss数据库版本：2.1.0

课程学习

连接openGauss

su - omm
source /srv/BigData/OpenGauss/db1_env
gsql -d postgres -p 26000 -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,

create view tpcds.v_dat as select * from pg_database where datname = 'postgres';
alter view tpcds.v_dat rename to v_dtbs;
create user jim password 'abcd@123';
alter view tpcds.v_dtbs owner to jim;
select * from tpcds.v_dtbs;

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

create table tpcds.student
(
student_sk integer,   
student_id char(5),    
s_first_name char(6),    
s_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.v_student as
select * from tpcds.student where student_sk < 6000;
alter view tpcds.v_student set schema public;

3.使用pg_views查看视图信息

select * from pg_views where viewname = 'v_dtbs' or viewname = 'v_student';

4.删除视图、表、用户

drop view tpcds.v_dtbs;drop view v_student;drop table tpcds.student;drop user jim;

写在最后

今天的作业打卡结束！🎉

最后，宣传一下自己创建的社区的打卡活动：零基础 21 天速通 openGuass 打卡活动报名贴！

🏅 是同一个活动哦，但是可以额外获得本社区的福利奖品！还不来参与？