openGauss每日一练第12天| openGauss逻辑结构：模式管理

1.创建一个名为testsm、testsm1的模式

omm=# create schema testsm;
omm=# create schema testsm1;

2.创建一个用户john, 并将testsm的owner修改为john，且修改owner前后分别使用\dn+查看模式信息

omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description | WithBlockChain
-----------------+-------+-------------------+----------------------------------+----------------
blockchain | omm | | blockchain schema | f
cstore | omm | | reserved schema for DELTA tables | f
db4ai | omm | omm=UC/omm +| db4ai schema | f
| | =U/omm | |
dbe_perf | omm | | dbe_perf schema | f
dbe_pldebugger | omm | omm=UC/omm +| dbe_pldebugger schema | f
| | =U/omm | |
dbe_pldeveloper | omm | omm=UC/omm +| dbe_pldeveloper schema | f
| | =U/omm | |
pkg_service | omm | | pkg_service schema | f
public | omm | omm=UC/omm +| standard public schema | f
snapshot | omm | | snapshot schema | f
sqladvisor | omm | omm=UC/omm +| sqladvisor schema | f
| | =U/omm | |
--More-- | | =U/omm | |
schm1 | user1 | | | f
testsm | omm | | | f
testsm1 | omm | | | f
user1 | user1 | | | f
(14 rows)

omm=# alter schema testsm owner to john;
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description | WithBlockChain
-----------------+-------+-------------------+----------------------------------+----------------
blockchain | omm | | blockchain schema | f
cstore | omm | | reserved schema for DELTA tables | f
db4ai | omm | omm=UC/omm +| db4ai schema | f
| | =U/omm | |
dbe_perf | omm | | dbe_perf schema | f
dbe_pldebugger | omm | omm=UC/omm +| dbe_pldebugger schema | f
| | =U/omm | |
dbe_pldeveloper | omm | omm=UC/omm +| dbe_pldeveloper schema | f
| | =U/omm | |
john | john | | | f
pkg_service | omm | | pkg_service schema | f
public | omm | omm=UC/omm +| standard public schema | f
| | =U/omm | |
schm1 | user1 | | | f
snapshot | omm | | snapshot schema | f
sqladvisor | omm | omm=UC/omm +| sqladvisor schema | f
| | =U/omm | |
testsm | john | | | f
testsm1 | omm | | | f
user1 | user1 | | | f
(15 rows)

3.重命名testsm为testsm2

omm=# alter schema testsm rename to testsm2;
ALTER SCHEMA

omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description | WithBlockChain
-----------------+-------+-------------------+----------------------------------+----------------
| | =U/omm | |
blockchain | omm | | blockchain schema | f
cstore | omm | | reserved schema for DELTA tables | f
db4ai | omm | omm=UC/omm +| db4ai schema | f
| | =U/omm | |
dbe_perf | omm | | dbe_perf schema | f
dbe_pldebugger | omm | omm=UC/omm +| dbe_pldebugger schema | f
| | =U/omm | |
dbe_pldeveloper | omm | omm=UC/omm +| dbe_pldeveloper schema | f
| | =U/omm | |
john | john | | | f
pkg_service | omm | | pkg_service schema | f
public | omm | omm=UC/omm +| standard public schema | f
sqladvisor | omm | omm=UC/omm +| sqladvisor schema | f
| | =U/omm | |
testsm1 | omm | | | f
--More-- schm1 | user1 | | | f
snapshot | omm | | snapshot schema | f
testsm2 | john | | | f
user1 | user1 | | | f
(15 rows)

4.在模式testsm1中建表t1、插入记录和查询记录

omm=# create table testsm1.t1(col1 char(10));
omm=# insert into testsm1.t1 values('20221205');
omm=# select * from testsm1.t1;
col1
------------
20221205
(1 row)

5.在会话级设置模式搜索顺序

omm=# show search_path;
search_path
----------------
"$user",public
(1 row)
omm=# set search_path to testsm1;
SET
omm=# show search_path;
search_path
-------------
testsm1
(1 row)
omm=# \q
omm@modb:~$ gsql -r
omm=# show search_path;
search_path
----------------
"$user",public
(1 row)

6.在数据库级设置模式搜索顺序

omm=# alter database enmdb set search_path to testsm1;
omm=# \q
omm@modb:~$ gsql -r
omm=# \c enmdb john
enmdb=> show search_path;
search_path
-------------
testsm1
(1 row

7.在用户级设置模式搜索顺序

enmdb=> alter user john set search_path to testsm2;
enmdb=> \q
enmdb=> show search_path;
search_path
-------------
testsm2
(1 row)

知识点：

1.搜索顺序的优先级

--会话级模式搜索顺序的优先级最高，用户级模式搜索顺序的优先级第2，数据库级模式搜索顺序的优先级最低。

enmdb=> set search_path to testsm1;
SET
enmdb=> show search_path;
search_path
-------------
testsm1
(1 row)

2.查看模式有哪些表

--查看当前连接的数据库中，public模式下有哪些表：

gsql -r

omm=# select table_catalog,table_schema,table_name,table_type from information_schema.tables where table_schema='public';

 table_catalog | table_schema | table_name | table_type 

---------------+--------------+------------+------------

 omm           | public       | test       | BASE TABLE

(1 row)

 --查看指定数据库中，public模式下有哪些表：

 omm=# select table_catalog,table_schema,table_name,table_type from information_schema.tables where table_catalog='omm' and table_schema='public';

 table_catalog | table_schema | table_name | table_type 

---------------+--------------+------------+------------

 omm           | public       | test       | BASE TABLE

(1 row)