openGauss每日一练第6天|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;
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:~$ 

复制