openGauss每日一练第12天

学习目标：opengauss数据库模式管理

1.登录opengauss数据库，利用gsql -r命令

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.

2.创建两个schema（testsm,testsm1），并创建用户john。并将testsm的属主修改为john;

omm=# create schema testsm;
CREATE SCHEMA
omm=# create schema testsm1;
CREATE SCHEMA
omm=# 
omm=# create user john identified by 'Dongyunyun1!';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
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
 testsm          | omm
 testsm1         | omm
(13 rows)                          
omm=# alter schema testsm owner to john;
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
 testsm          | john
 testsm1         | omm
(13 rows)

3.删除模式testsm1，重命名testsm为testsm1；

omm=# drop schema testsm1;
DROP SCHEMA
omm=# alter schema testsm rename to testsm1;
ALTER SCHEMA

4.设置会话搜索顺序，并在testsm1中创建表t1.并查询数据。

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

omm=# create table t1(id int);
CREATE TABLE
omm=# insert into t1 values(1);
INSERT 0 1
omm=# select * from t1;
omm=#  id 
----
  1
(1 row)

5.设置数据库及用户的search_path。

omm=# alter database omm set search_path to testsm1;
ALTER DATABASE
omm=# alter user john set search_path to testsm1;
ALTER ROLE