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

原创 Alex 2021-12-15

252

今天来练习一下视图的操作了

1.登录数据库环境

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.

2.创建视图
omm=# CREATE VIEW tps_view AS
omm-# SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
CREATE VIEW

3.查看视图

omm-# Create schema tpcds;
ERROR: syntax error at or near "2"
LINE 1: 2~
^
omm=# CREATE TABLE tpcds.customer
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# ) ;
CREATE TABLE

插入数据

omm=# INSERT INTO tpcds.customer VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4

创建视图

omm-# CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
omm-# WHERE c_customer_sk < 6000;
CREATE VIEW

创建schema

omm=# create schema tpcds;
ERROR: schema "tpcds" already exists
omm=# select * from tpcds.customer_details_view_v1;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
4321 | 2 | Lily | Carter
(1 row)

查看视图数据字典
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)

5.修改视图名称
omm=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW

6.修改视图schema

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

7.修改视图owner
omm=# create user jack password 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view tps_view owner to jack;
ALTER VIEW

omm=# select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';
schemaname | viewname | viewowner | definition

------------+--------------------------+-----------+--------------------------------------------------------------------------------
--
public | tps_view | jack | SELECT * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name)
;
public | customer_details_view_v2 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

8.删除视图

opengauss

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

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

评论