openGauss每日一练第11天 课程笔记和作业

数据库环境

openGauss:2.0.0 - 数据库实训平台

学习目标

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

学习笔记

• 系统视图

omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
 schemaname |         viewname         | viewowner |                                    definition                                    
------------+--------------------------+-----------+----------------------------------------------------------------------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

课后作业

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

学习资源

• openGauss SQL学习参考资料
• 每日一练：openGauss数据库在线实训课程
• openGauss每日一练 | 21期养成好习惯，提升技术能力！
• 墨天轮Markdown编辑器使用介绍
• 墨天轮数据库在线实训平台V1.0操作手册
• 墨天轮数据社区

欢迎各位同学一起来交流学习心得！