openGauss每日一练第11天 | 学习心得体会

第十一课

11.1 打卡第11天| 学习openGauss视图

👉openGauss SQL学习参考资料

https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html

学习目标

学习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,

Create schema tpcds;

CREATE VIEW tpcds.tps_view AS

SELECT * FROM PG_DATABASE WHERE datname = 'postgres';

ALTER VIEW tpcds.tps_view RENAME TO tps_view2;

create user jim password 'abcd@123';

alter view tpcds.tps_view2 owner to jim;

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

create table tpcds.student(sno int);

create view tpcds.v_student as select sno from tpcds.student where sno > 100;

ALTER VIEW tpcds.v_student SET schema public;

3.使用pg_views查看视图信息

select * from pg_views where viewname = 'v_student';

4.删除视图、表、用户

drop view v_student;

drop view tps_view;

drop table tpcds.student;

drop user jim;

omm=# Create schema tpcds;

CREATE SCHEMA

omm=#

omm=# CREATE VIEW tpcds.tps_view AS

omm-# SELECT * FROM PG_DATABASE WHERE datname = 'postgres';

CREATE VIEW

omm=# ALTER VIEW tpcds.tps_view RENAME TO tps_view2;

ALTER VIEW

omm=# create user jim password 'abcd@123';

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.

CREATE ROLE

omm=# alter view tpcds.tps_view2 owner to jim;

ALTER VIEW

omm=# create table tpcds.student(sno int);

CREATE TABLE

omm=# create view tpcds.v_student as select sno from tpcds.student where sno > 100;

CREATE VIEW

omm=# ALTER VIEW tpcds.v_student SET schema public;

ALTER VIEW

omm=# select * from pg_views where viewname = 'v_student';

 schemaname | viewname  | viewowner |                            definition                            

------------+-----------+-----------+------------------------------------------------------------------

 public     | v_student | omm       | SELECT student.sno FROM tpcds.student WHERE (student.sno > 100);

(1 row)

omm=# drop view tpcds.v_student;

ERROR:  view "v_student" does not exist

omm=# drop view tpcds.tps_view;

ERROR:  view "tps_view" does not exist

omm=# drop view v_student;

DROP VIEW

omm=# drop view tps_view;

DROP VIEW

omm=# drop table tpcds.student;

DROP TABLE

omm=# drop user jim;

ERROR:  role "jim" cannot be dropped because some objects depend on it

DETAIL:  owner of view tpcds.tps_view2

omm=# drop user jim cascade;

DROP ROLE

omm=#