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

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

root@modb:~# su - omm
omm@modb:~$ gsql -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.

omm=# create schema testsm;
CREATE SCHEMA
omm=# create schema testsmmmm             
omm=# create schema testsm1;       
CREATE SCHEMA
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+查看模式信息

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=#  ALTER USER john SYSADMIN;
ALTER ROLE
omm=# \du
                                                              List of roles
 Role name |                                                    Attributes         
                                           | Member of 
-----------+-----------------------------------------------------------------------
-------------------------------------------+-----------
 gaussdb   | Sysadmin                                                              
                                           | {}
 john      | Sysadmin                                                              
                                           | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monit
oradmin, Operatoradmin, Policyadmin, UseFT | {}

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

omm=# 

3.重命名testsm为testsm2

ALTER SCHEMA
omm=# 

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

CREATE TABLE
omm=# insert into testsm1.t1 values (1,'OpenGauss'),(2,'PostgreSQL');
INSERT 0 2
omm=# select * from testsm1.t1 ;
 id |    name    
----+------------
  1 | OpenGauss
  2 | PostgreSQL
(2 rows)

omm=# 

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

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

omm=# \q
omm@modb:~$ gsql -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.

omm=# show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)

omm=# 

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

ALTER DATABASE
omm=# \q
omm@modb:~$ gsql -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.

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

omm=# 

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

ALTER ROLE
omm=# \q

omm@modb:~$  gsql -d omm -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.

omm=> show SEARCH_PATH;
 search_path 
-------------
 testsm1
(1 row)

omm=>