opengauss每日一练第11天
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.
1.创建视图
–为系统表pg_tablespace创建字段spcname为pg_default组成的视图
omm=# CREATE VIEW tps_view AS
omm-# SELECT * FROM pg_tablespace WHERE spcname = ‘pg_default’;
CREATE VIEW
–查看视图
omm=# SELECT * FROM tps_view ;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------±---------±-------±-----------±-----------±---------
pg_default | 10 | | | | f
(1 row)
–创建一个由c_customer_sk小于6000的内容组成的视图
omm=# Create schema tpcds;
CREATE SCHEMA
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=# omm-# INSERT INTO tpcds.customer VALUES
(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
omm-# SELECT * FROM tpcds.customer
omm-# WHERE c_customer_sk < 6000;
CREATE VIEW
–查看视图
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)
2.修改视图定义
–修改视图名称
omm=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW
–修改视图所属schema
omm=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW
–修改视图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
–查看系统视图tps_views
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)
3.删除视图
omm=# DROP VIEW customer_details_view_v2;
DROP VIEW
omm=# Drop view tps_view;
opengauss每日一练第11天|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.
1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,
omm=# CREATE VIEW test1_view AS SELECT * FROM pg_database;
CREATE VIEW
omm=# alter view test1_view rename to test1_view_new;
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 test1_view_new owner to jim;
ALTER VIEW
2.创建一个用户表student，并在用户表上创建视图，修改视图schema;
omm=# create schema st1;
CREATE SCHEMA
omm=# omm(# id int not null,
create table st1.student (
omm(# name char(20),
omm(# age int,
sex tinyint
omm(# omm(# );
CREATE TABLE
在用户表上创建视图，修改视图schema;
omm=# create view st1.stu_view as select * from st1.student;
CREATE VIEW
omm=# create schema st2;
CREATE SCHEMA
omm=# alter view st1.stu_view set schema st2;
ALTER VIEW
3.使用pg_views查看视图信息
omm=# select * from pg_views where viewname = ‘test1_view’ or viewname = ‘test1_view_new’;
schemaname | viewname | viewowner | defini
tion
------------±---------------±----------±--------------
public | test1_view_new | jim | SELECT * FROM
pg_database;
(1 row)
4.删除视图、表、用户
omm=# drop view test1_view_new;
DROP VIEW
omm=# drop view st2.stu_view;
DROP VIEW
omm=# drop table st1.student;
DROP TABLE
omm=# drop user jim;
DROP ROLE
omm=#
opengauss每日一练第11天|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.
1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,
omm=# CREATE VIEW test1_view AS SELECT * FROM pg_database;
CREATE VIEW
omm=# alter view test1_view rename to test1_view_new;
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 test1_view_new owner to jim;
ALTER VIEW
2.创建一个用户表student，并在用户表上创建视图，修改视图schema;
omm=# create schema st1;
CREATE SCHEMA
omm=# omm(# id int not null,
create table st1.student (
omm(# name char(20),
omm(# age int,
sex tinyint
omm(# omm(# );
CREATE TABLE
在用户表上创建视图，修改视图schema;
omm=# create view st1.stu_view as select * from st1.student;
CREATE VIEW
omm=# create schema st2;
CREATE SCHEMA
omm=# alter view st1.stu_view set schema st2;
ALTER VIEW
3.使用pg_views查看视图信息
omm=# select * from pg_views where viewname = ‘test1_view’ or viewname = ‘test1_view_new’;
schemaname | viewname | viewowner | defini
tion
------------±---------------±----------±--------------
public | test1_view_new | jim | SELECT * FROM
pg_database;
(1 row)
4.删除视图、表、用户
omm=# drop view test1_view_new;
DROP VIEW
omm=# drop view st2.stu_view;
DROP VIEW
omm=# drop table st1.student;
DROP TABLE
omm=# drop user jim;
DROP ROLE
omm=#
omm=#
DROP VIEW
omm=#
