openGauss每日一练第12天 | 学习心得体会

openGauss的模式是对数据库做一个逻辑分割。所有的数据库对象都建立在模式下面。openGauss的模式和用户是弱绑定的，所谓的弱绑定是指虽然创建用户的同时会自动创建一个同名模式，但用户也可以单独创建模式，并且为用户指定其他的模式。
在一个数据库中，可以有多个模式。模式可以把一组对象组织在一起。这样组织机构有多少个应用，我们可以将数据库对象组织成几个模式；组织机构有几个部门，也可以为该部门创建单独的模式。默认情况下，用户将访问数据库的public模式。

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

--创建testsm数据库和testsm表空间
CREATE TABLESPACE testsm RELATIVE LOCATION 'tablespace/testsm';
CREATE DATABASE testsm WITH TABLESPACE = testsm;
--创建模式testsm，模式testsm1，属主是用户omm：
create schema testsm;
create schema testsm1;

omm=# \dn 
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm          | omm
 testsm1         | omm
(12 rows)

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

CREATE USER john IDENTIFIED BY 'kunpeng@1234';
ALTER USER john  SYSADMIN;

omm=# \dn+
                                         List of schemas
      Name       | Owner | Access privileges |           Description            | WithBlockChain 
-----------------+-------+-------------------+----------------------------------+----------------
 blockchain      | omm   |                   | blockchain schema                | f
 public          | omm   | omm=UC/omm       +| standard public schema           | f
                 |       | =U/omm            |                                  | 
 snapshot        | omm   |                   | snapshot 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
 sqladvisor      | omm   | omm=UC/omm       +| sqladvisor schema                | f
                 |       | =U/omm            |                                  | 
 testsm          | omm   |                   |                                  | f
 testsm1         | omm   |                   |                                  | f
(13 rows)

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
                 |       | =U/omm            |                                  | 
 snapshot        | omm   |                   | snapshot schema                  | 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            |                                  | 
 testsm          | john  |                   |                                  | f
 testsm1         | omm   |                   |                                  | f
(13 rows)

3.重命名testsm为testsm2

omm=# alter schema testsm rename to testsm2;
ALTER SCHEMA
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 john            | john
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm1         | omm
 testsm2         | john
(13 rows)

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

openGauss在创建一个新的数据库时，会自动创建一个public模式。当用户登录到该数据库时，如果没有特殊的指定，都是操作在public模式中的数据库对象

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=# create table t1(product_id INTEGER,product_name char(20),category char(30));
omm=# CREATE TABLE
insert into t1 values(1502,'olympus camera','electrncs');
INSERT 0 1
omm=# select * from t1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
(1 row)
--查看当前连接的数据库中，testsm1模式下有哪些表：
omm=# select table_catalog,table_schema,table_name,table_type
omm-#  from information_schema.tables  
omm-#  where table_schema = 'testsm1';
 table_catalog | table_schema | table_name | table_type 
---------------+--------------+------------+------------
 omm           | testsm1      | t1         | BASE TABLE
(1 row)

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

在gsql客户端会话中，执行命令SET SEARCH_PATH TO schm1可以修改模式搜索路径，但只在gsql客户端会话的持续过程中起作用，一旦退出gsql客户端会话，这个设置就丢失了。重新登录gsql会话将模式搜索路径恢复为默认值"$user",public。

omm=# SET SEARCH_PATH TO testsm1;
SET
omm=# show SEARCH_PATH;
 search_path 
-------------
 testsm1
(1 row)

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

修改数据库级别的搜索顺序后，数据库用户john再次登录到数据库testsm，其模式搜索路径已经变更为数据库默认的模式搜索路径testsm1

ALTER DATABASE testsm SET SEARCH_PATH TO testsm1;
omm=# \c testsm john
Password for user john: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testsm" as user "john".
testsm=> 
testsm=> show search_path ;
 search_path 
-------------
 testsm1
(1 row)

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

--设置用户模式
testsm=> ALTER USER john SET SEARCH_PATH TO testsm2;
ALTER ROLE
testsm=> \q
--登录查看
omm@modb:~$ gsql -d testsm   -U john   -W kunpeng@1234 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

testsm=> show SEARCH_PATH;
 search_path 
-------------
 testsm2
(1 row)

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