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

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

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

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

## 创建用户omm=# create user john identified by 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE## 检查模式信息omm=# \dn+ testsm
                          List of schemas
  Name  | Owner | Access privileges | Description | WithBlockChain 
--------+-------+-------------------+-------------+----------------
 testsm | omm   |                   |             | f
(1 row)
## 修改模式的owneromm=# alter schema testsm owner to john;
ALTER SCHEMA
## 修改模式owner后，检查模式信息omm=# \dn+ testsm
                          List of schemas
  Name  | Owner | Access privileges | Description | WithBlockChain 
--------+-------+-------------------+-------------+----------------
 testsm | john  |                   |             | f
(1 row)

3.重命名testsm为testsm2

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

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

## 建立新表t1omm=# create table testsm1.t1 (id integer);
CREATE TABLE## 插入记录omm=# insert into testsm1.t1 values (1);
INSERT 0 1## 查询记录
omm=# select * from testsm1.t1 ;
 id 
----
  1
(1 row)

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

omm=# set search_path to testsm1;
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)

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

##创建数据库omm=# create database testdb;
CREATE DATABASE## 修改搜索顺序omm=# alter database testdb set search_path to testsm1;
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=# show search_path ;
  search_path   
----------------
 "$user",public
(1 row)
omm=# \c testdb john
Password for user john: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "testdb" as user "john".
testdb=> show search_path ;
 search_path 
-------------
 testsm1
(1 row)

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

omm=# alter user john set search_path to testsm2;
ALTER ROLE
omm=# \qomm@modb:~$ gsql -d testdb -U john -W abcd@123 -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.

testdb=> show search_path ;
 search_path 
-------------
 testsm2
(1 row)
testdb=> \c omm john
Password for user john: 
omm=> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "john".

omm=> show search_path ;
 search_path 
-------------
 testsm2
(1 row)

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