openGauss每日一练第 7 天 | 一个数据库中创建多个模式

学习目标
学习openGauss数据库、用户和模式的关系和访问方式，理解模式是在数据库层面，用户是在实例层面

课程学习
一个用户连接到数据库后，可以在这个数据库中创建多个模式。要访问这些模式，可以使用DatabaseName.SchemaName.TableName或者SchemaName.TableName，来访问某个模式下的一个表。
默认情况下访问public模式下的表，可以不用添加模式名前缀。

课程作业
1.查看当前数据库下有哪些模式

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=# \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
(10 rows)

复制

2.然后为数据库musicdb创建4个模式，名称自定义

omm=# create tablespace music_tbs relative location 'tablespace/music_tbs';
CREATE TABLESPACE
omm=# create database musicdb with tablespace=music_tbs;

CREATE DATABASE
omm=# 
omm=# create user user1 identified by 'test@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user user1 sysadmin;
ALTER ROLE
omm=# \c musicdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=> create schema schm1 authorization user1;
CREATE SCHEMA
musicdb=> create schema schm2 authorization user1;
musicdb=> CREATE SCHEMA

musicdb=> create schema schm3 authorization user1;
CREATE SCHEMA
musicdb=> create schema schm4 authorization user1;
CREATE SCHEMA

musicdb=> \dn
 pkg_service     | omm
 public          | omm
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)

     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm

复制

3.在数据库musicdb的不同的模式下创建同名的表

musicdb=> create table schm1.t1(id int);
CREATE TABLE
musicdb=> create table schm2.t1(id int);
CREATE TABLE
musicdb=> create table schm3.t1(id int);
CREATE TABLE
musicdb=> create table schm4.t1(id int);
CREATE TABLE
musicdb=> insert into schm1.t1 values(10);
INSERT 0 1
musicdb=> insert into schm2.t1 values(20);
INSERT 0 1
musicdb=> insert into schm3.t1 values(30);
INSERT 0 1
musicdb=> insert into schm4.t1 values(40);
INSERT 0 1

复制

4.访问musicdb数据库下不同模式的同名表

musicdb=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

musicdb=> select * from schm1.t1;
musicdb=>  id 
----
 10
(1 row)


musicdb=> select  * from schm2.t1;
 id 
----
 20
(1 row)

musicdb=> select   * from schm3.t1;
 id 
----
 30
(1 row)

musicdb=> select * from schm4.t1;
musicdb=>  id 
----
 40
(1 row)


复制

5.实验理解：模式是在数据库层面，用户是在实例层面

musicdb=> \du
                                                              List of roles
 Role name |                                                    Attributes                                       
             | Member of 
-----------+-----------------------------------------------------------------------------------------------------
-------------+-----------
 gaussdb   | Sysadmin                                                                                            
             | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}
 user1     | Sysadmin                                                                                            
             | {}

musicdb=> \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
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)

musicdb=> \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=# \du
                                                              List of roles
 Role name |                                                    Attributes                                       
             | Member of 
-----------+-----------------------------------------------------------------------------------------------------
-------------+-----------
 gaussdb   | Sysadmin                                                                                            
             | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}
 user1     | Sysadmin                                                                                            
             | {}

omm=# \dn
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user1           | user1
(11 rows)

     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm

复制