openGauss每日一练第11天 加油

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,

mm=# create view select_pg_database_omm as select * from pg_database where datname=‘omm’;
CREATE VIEW
omm=# alter view select_pg_database_omm rename to select_pg_database_omm_v2;
ALTER VIEW
omm=# create user jim password ‘jim-demo-view1’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view select_pg_database_omm_v2 owner to jim;
ALTER VIEW
omm=# select * from pg_views where viewname=‘select_pg_database_omm_v2’;
schemaname | viewname | viewowner | definition
------------±--------------------------±----------±----------------------------------------------------------------------omm=#
public | select_pg_database_omm_v2 | jim | SELECT * FROM pg_database WHERE (pg_database.datname = ‘omm’::name);
(1 row)

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

omm=# create schema schema_student_v1;
CREATE SCHEMA
omm=# create table schema_student_v1.student
omm-# (
omm(# student_sk integer not null,
omm(# student_id integer not null
omm(# );
CREATE TABLE
omm=# insert into schema_student_v1.student values
omm-# (17236,1),
omm-# (4689,2),
omm-# (9815,3);
INSERT 0 3
omm=# select * from schema_student_v1.student;
student_sk | student_id
------------±-----------
17236 | 1
4689 | 2
9815 | 3
(3 rows)

omm-# create view schema_student_v1.select_student_id as select student_id from schema_student_v1.student;
omm=# CREATE VIEW
omm=# select * from schema_student_v1.select_student_id;
student_id

      1
      2
      3

(3 rows)

omm=# alter view schema_student_v1.select_student_id set schema public;
ALTER VIEW

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewname = ‘select_student_id’ or viewname=‘select_pg_database_omm_v2’;
schemaname | viewname | viewowner | definition
------------±--------------------------±----------±----------------------------------------------------------------------
public | select_pg_database_omm_v2 | jim | SELECT * FROM pg_database WHERE (pg_database.datname = ‘omm’::name);
public | select_student_id | omm | SELECT student.student_id FROM schema_student_v1.student;
(2 rows)

omm=#

4.删除视图、表、用户

omm=# drop view select_student_id,select_pg_database_omm_v2;
DROP VIEW
omm=# drop user jim;
DROP ROLE
omm=# drop schema schema_student_v1 cascade;
NOTICE: drop cascades to table schema_student_v1.student
DROP SCHEMA