学习目标
学习openGauss创建模式、修改模式属性和删除模式
课程学习
模式是一组数据库对象的集合，主要用于控制对数据库对象的访问
连接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.创建模式
–创建名为ds的模式
omm=# CREATE SCHEMA ds;
CREATE SCHEMA
–查看ds模式信息, owner为omm
omm=# \dn+ ds;
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
ds | omm | |
(1 row)
2.在ds中建表
omm=# create table ds.t1(id int, name char(30));
CREATE TABLE
omm=# insert into ds.t1 values(1 ,'xxxx');
omm=# select * from ds.t1;INSERT 0 1
id | name
----+--------------------------------
1 | xxxx
(1 row)
–查看表信息
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
3.修改模式信息
–重命名模式为ds_new
omm=# ALTER SCHEMA ds RENAME TO ds_new;
ALTER SCHEMA
omm=# select * from ds_new.t1;
id | name
----+--------------------------------
1 | xxxx
(1 row)
–创建用户jack
omm=# CREATE USER jack PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
–将ds_new的所有者修改为jack
omm=# ALTER SCHEMA ds_new OWNER TO jack;
ALTER SCHEMA
–查看ds_new模式信息
omm=# \dn+ ds_new;
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
ds_new | jack | |
(1 row)
omm=#
4.删除模式
omm=# DROP SCHEMA ds_new;
omm=# 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.
–当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字
omm=# DROP SCHEMA ds_new CASCADE;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA
omm=# DROP USER jack;
omm=# DROP ROLE
omm=# DROP SCHEMA ds_new CASCADE;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA
omm=# DROP USER jack;
omm=# DROP ROLE
课后作业
1.创建一个名为tpcds的模式
omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA
2.创建一个用户tim, 并将tpcds的owner修改为tim，且修改owner前后分别使用\dn+查看模式信息
omm=# CREATE USER tim PASSWORD 'timd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER SCHEMA tpcds OWNER TO tim;
ALTER SCHEMA
omm=# \dn+ tpcds
List of schemas
Name | Owner | Access privileges | Description
-------+-------+-------------------+-------------
tpcds | tim | |
(1 row)
3.重命名tpcds为tpcds1
omm=# ALTER SCHEMA tpcds RENAME TO tpcds1;
ALTER SCHEMA
4.在模式tpcds1中建表customer、插入记录和查询记录
omm=# create table tpcds1.customer(id int, name char(30));
CREATE TABLE
omm=# insert into tpcds1.customer values(1 ,'cxb');
INSERT 0 1
omm=# select * from tpcds1.customer;
(1 row)
omm=# id | name
----+--------------------------------
1 | cxb
5.删除模式tpcds1
omm=# DROP SCHEMA tpcds1 CASCADE;
NOTICE: drop cascades to table tpcds1.customer
DROP SCHEMA
