openGauss每日一练第11天｜openGauss视图的操作

坚持学习openGauss数据库，坚持每天打卡。第十一天学习openGauss视图的操作。

连接opengauss

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((opengauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
non-ssl connection (ssl connection is recommended when requiring high-security)
type "help" for help.
omm=#

1.为系统表pg_database创建视图，重命名视图并修改owner为jim,

omm=# create view pd_view as select * from pg_database;
CREATE VIEW
omm=# alter view pd_view rename to pd_view1;
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 pd_view1 owner to jim;
ALTER VIEW

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

omm=# create schema schema1;
CREATE SCHEMA
omm=# create table schema1.student
omm-# (
omm(# id integer,
omm(# name char(5) 
omm(# ) ;
CREATE TABLE
omm=# insert into schema1.student values (1,'joes'),(2,'lily'),(3,'james'),(4,'lucy');
INSERT 0 4
omm=# create view schema1.student_view as select * from schema1.student;
CREATE VIEW
omm=# alter view schema1.student_view set schema public;
ALTER VIEW

3.使用pg_views查看视图信息

omm=#  schemaname |   viewname   | viewowner |           definition            
------------+--------------+-----------+---------------------------------
 public     | pd_view1     | jim       | SELECT  * FROM pg_database;
 public     | student_view | omm       | SELECT  * FROM schema1.student;
(2 rows)

4.删除视图、表、用户

omm=# drop view pd_view1;
DROP VIEW
omm=# drop view student_view;
DROP VIEW
omm=# drop table schema1.student;
DROP TABLE
omm=# drop user jim;
DROP ROLE
omm=# drop schema schema1;
DROP SCHEMA

通过学习和作业，更加深入的了解了opengauss视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。