第六课 学习openGauss创建模式、修改模式属性和删除模式
学习目标
学习openGauss创建模式、修改模式属性和删除模式
课程学习
模式是一组数据库对象的集合，主要用于控制对数据库对象的访问
连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r
学习内容如下
1.创建模式
–创建名为ds的模式
CREATE SCHEMA ds;
–查看ds模式信息, owner为omm
\dn+ ds;
2.在ds中建表
create table ds.t1(id int, name char(30));
insert into ds.t1 values(1 ,‘xxxx’);
select * from ds.t1;
–查看表信息
\d+ ds.t1;
3.修改模式信息
–重命名模式为ds_new
ALTER SCHEMA ds RENAME TO ds_new;
select * from ds_new.t1;
–创建用户jack
CREATE USER jack PASSWORD ‘abcd@123’;
–将ds_new的所有者修改为jack
ALTER SCHEMA ds_new OWNER TO jack;
–查看ds_new模式信息
\dn+ ds_new;
4.删除模式
DROP SCHEMA ds_new;
–当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字
DROP SCHEMA ds_new CASCADE;
DROP USER jack;
课后作业
1.创建一个名为tpcds的模式
2.创建一个用户tim, 并将tpcds的owner修改为tim，且修改owner前后分别使用\dn+查看模式信息
3.重命名tpcds为tpcds1
4.在模式tpcds1中建表customer、插入记录和查询记录
5.删除模式tpcds1
CREATE SCHEMA tpcds;
CREATE USER tim PASSWORD 'abcd@123';
ALTER SCHEMA tpcds OWNER TO tim;
\dn+
ALTER SCHEMA tpcds RENAME TO tpcds1;
create table tpcds1.customer(id int, name char(30));
insert into tpcds1.customer values(1 ,'xxxx');
select * from tpcds1.customer;
\dn+ tpcds1;
DROP SCHEMA tpcds1;
DROP SCHEMA tpcds1 CASCADE;
DROP USER tim;
课后作业记录
omm=#
omm=#
omm=# CREATE SCHEMA ds;
CREATE SCHEMA
omm=#
omm=# \dn+ ds;
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
ds | omm | |
(1 row)
omm=#
omm=# create table ds.t1(id int, name char(30));
CREATE TABLE
omm=# insert into ds.t1 values(1 ,'xxxx');
INSERT 0 1
omm=# select * from ds.t1;
id | name
----+--------------------------------
1 | xxxx
(1 row)
omm=#
omm=# \d+ ds.t1;
Table "ds.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
omm=#
omm=# ALTER SCHEMA ds RENAME TO ds_new;
ALTER SCHEMA
select * from ds_new.t1;omm=#
id | name
----+--------------------------------
1 | xxxx
(1 row)
omm=#
omm=# CREATE USER jack PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#
omm=# ALTER SCHEMA ds_new OWNER TO jack;
ALTER SCHEMA
omm=#
omm=# \dn+ ds_new;
(1 row)
omm=# List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
ds_new | jack | |
omm=#
omm=# DROP SCHEMA ds_new;
ERROR: cannot drop schema ds_new because other objects depend on it
DETAIL: table ds_new.t1 depends on schema ds_new
HINT: Use DROP ... CASCADE to drop the dependent objects too.
omm=#
omm=#
omm=# DROP SCHEMA ds_new CASCADE;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA
omm=# DROP USER jack;
DROP ROLE
omm=#
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=#
omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA
omm=#
omm=# CREATE USER tim PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER SCHEMA tpcdsOWNER TO tim;
ERROR: syntax error at or near "TO"
LINE 1: ALTER SCHEMA tpcdsOWNER TO tim;
^
omm=#
omm=# ALTER SCHEMA tpcds OWNER TO tim;
ALTER SCHEMA
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
tim | tim | |
tpcds | tim | |
(7 rows)
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
tim |
| {}
omm=#
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 | |
omm=# (7 rows)
omm=#
omm=# create table tpcds1.customer(id int, name char(30));
CREATE TABLE
omm=# insert into tpcds1.customer values(1 ,'xxxx');
INSERT 0 1
omm=# select * from tpcds1.customer;
id | name
----+--------------------------------
1 | xxxx
(1 row)
omm=#
omm=# \dn+ tpcds1;
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
tpcds1 | tim | |
(1 row)
omm=#
omm=# DROP SCHEMA tpcds1;
ERROR: cannot drop schema tpcds1 because other objects depend on it
DETAIL: table tpcds1.customer depends on schema tpcds1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
omm=#
omm=#
omm=# DROP SCHEMA tpcds1 CASCADE;
NOTICE: drop cascades to table tpcds1.customer
DROP SCHEMA
omm=# DROP USER tim;
DROP ROLE
omm=#
omm=# \dn+ tpcds1;
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
(0 rows)
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------
-------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatora
dmin, Policyadmin, UseFT | {}
omm=#
最后修改时间：2021-12-30 21:44:34
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
