本次课的内容是学习openGauss创建模式、修改模式属性和删除模式。
模式是一组数据库对象的集合，主要用于控制对数据库对象的访问
课程学习前还是先进入实训环境，输入su - omm口令和密码连接openGauss。
root@modb:~#
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、创建一个名为tpcds的模式
可以使用\dn+命令查看omm中的模式信息。
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
(5 rows)
omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
omm=# snapshot | omm | | snapshot schema
tpcds | omm | |
(6 rows)
2、创建一个用户tim, 并将tpcds的owner修改为tim，且修改owner前后分别使用\dn+查看模式信息
首先使用\du查看当前用户信息
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
创建用户，用\du查看当前用户信息，用\dn+查看当前模式信息
omm=# CREATE USER tim IDENTIFIED BY 'user1_tim';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
tim | | {}
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | omm | |
(7 rows)
将tpcds的owner修改为tim
omm=# ALTER SCHEMA tpcds OWNER TO tim;
omm=# ALTER SCHEMA
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | tim | |
(7 rows)
3、重命名tpcds为tpcds1
omm=# ALTER SCHEMA tpcds RENAME TO tpcds1;
ALTER SCHEMA
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds1 | tim | |
(7 rows)
4、在模式tpcds1中建表customer、插入记录和查询记录
omm=# CREATE TABLE tpcds1.customer(
omm(# id int,
omm(# name char(20),
omm(# food char(50)
omm(# );
CREATE TABLE
omm=# insert into tpcds1.customer values(1,'william','noodles');
INSERT 0 1
omm=# select * from tpcds1.customer;
id | name | food
----+----------------------+----------------------------------------------------
1 | william | noodles
(1 row)
omm=# \d+ tpcds1.customer;
Table "tpcds1.customer"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(20) | | extended | |
food | character(50) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
5、删除模式tpcds1和tim
omm=# DROP SCHEMA tpcds1 CASCADE;
ERROR: role "jack" does not exist
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table tpcds1.table1
drop cascades to table tpcds1.customer
omm=# DROP SCHEMA
omm=# \dn
(6 rows)
omm=# List of schemas
Name | Owner
-------------+-------
cstore | omm
dbe_perf | omm
pkg_service | omm
public | omm
snapshot | omm
tim | tim
omm=# DROP SCHEMA tim CASCADE;
DROP SCHEMA
omm=# \dn
List of schemas
Name | Owner
-------------+-------
cstore | omm
dbe_perf | omm
pkg_service | omm
public | omm
snapshot | omm
(5 rows)
6、退出gsql程序
omm=# \q
omm@modb:~$
最后修改时间：2021-12-07 09:22:34
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
