openGauss每日一练（6）|openGauss创建模式、修改模式属性和删除模式

openGauss每日一练（6）|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.创建模式
–创建名为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’);
INSERT 0 1
omm=# select * from ds.t1;
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

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)
4.删除模式

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.
–当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字
omm=# DROP SCHEMA ds_new CASCADE;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA
omm=# DROP USER jack;
DROP ROLE
omm=#

每日一练（6）课后作业
(openGauss创建模式、修改模式属性和删除模式)
root@modb:~# 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的模式
omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA
2.创建一个用户tim, 并将tpcds的owner修改为tim，且修改owner前后分别使用\dn+查看模式信息
omm=# CREATE USER tim PASSWORD ‘abcd@123’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn+ tpcds;
List of schemas
Name | Owner | Access privileges | Description
-------±------±------------------±------------
tpcds | omm | |
(1 row)

omm=# ALTER SCHEMA tpcds OWNER TO tim;
ALTER SCHEMA
omm=# \dn+ tpcds;
(1 row)

omm=# List of schemas
Name | Owner | Access privileges | Description
-------±------±------------------±------------
tpcds | tim | |
3.重命名tpcds为tpcds1
omm=# ALTER SCHEMA tpcds RENAME TO tpcds1;
ALTER SCHEMA
4.在模式tpcds1中建表customer、插入记录和查询记录
omm=# create table tpcds1.t1(id int, name char(30));
CREATE TABLE
omm=# insert into tpcds1.t1 values(1 ,‘xxxx’);
omm=# INSERT 0 1
select * from tpcds1.t1;
id | name
----±-------------------------------
1 | xxxx
(1 row)
5.删除模式tpcds1

omm=# DROP SCHEMA tpcds1 CASCADE;
NOTICE: drop cascades to table tpcds1.t1
DROP SCHEMA
omm=# DROP USER tim;
DROP ROLE
omm=# ^C
omm=#