作业：

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=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=#  CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1 SYSADMIN;
ALTER ROLE

2.查看当前数据库下有哪些模式

omm=# \c musicdb  user1 
Password for user user1: 
musicdb=> Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".

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
snapshot        | omm
sqladvisor      | omm
(10 rows)

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

musicdb=> create schema yangkai1 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema yangkai2 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema yangkai3 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema yangkai4 AUTHORIZATION user1;
CREATE SCHEMA

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

musicdb=> create table yangkai1.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table yangkai2.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table yangkai3.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table yangkai4.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> insert into yangkai1.tb1 values (1,'yangkai');
musicdb=> INSERT 0 1
musicdb=> insert into yangkai2.tb1 values (1,'yangkai');
INSERT 0 1
musicdb=> insert into yangkai3.tb1 values (1,'yangkai');
musicdb=> INSERT 0 1
musicdb=> insert into yangkai4.tb1 values (1,'yangkai');
musicdb=> INSERT 0 1

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

--查看当前模式的搜索路径
musicdb=> show SEARCH_PATH;
search_path   
----------------
"$user",public
(1 row)
--创建视图查看所有模式
musicdb=> create or replace view my_tables as
musicdb->  select table_catalog, table_schema, table_name, table_type
musicdb-> from information_schema.tables
musicdb-> where table_schema not in ('pg_catalog', 'information_schema','dbe_perf');
musicdb=> CREATE VIEW

musicdb=>  select * from my_tables;
musicdb       | yangkai3        | tb1        | BASE TABLE
musicdb       | yangkai2        | tb1        | BASE TABLE
musicdb       | yangkai1        | tb1        | BASE TABLE
(8 rows)

musicdb=>  table_catalog |  table_schema   | table_name | table_type 
---------------+-----------------+------------+------------
musicdb       | db4ai           | snapshot   | BASE TABLE
musicdb       | dbe_pldeveloper | gs_errors  | BASE TABLE
musicdb       | dbe_pldeveloper | gs_source  | BASE TABLE
musicdb       | public          | my_tables  | VIEW
musicdb       | yangkai4        | tb1        | BASE TABLE
--访问musicdb数据库下不同模式的同名表
musicdb=> select * from yangkai1.tb1;
id |  name   
----+---------
1 | yangkai
(1 row)

musicdb=> select * from yangkai2.tb1;
 id |  name   
----+---------
 1 | yangkai
(1 row)

musicdb=> select * from yangkai3.tb1;
id |  name   
----+---------
1 | yangkai
(1 row)

musicdb=> select * from yangkai4.tb1;
id |  name   
----+---------
 1 | yangkai
(1 row)

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

musicdb=> \du
                                                          List of roles
Role name |                                           Attributes                                                    
| Member of 

gaussdb   | Sysadmin                                                                                                         
| {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, 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
snapshot        | omm
sqladvisor      | omm
yangkai1        | user1
yangkai2        | user1
yangkai3        | user1
yangkai4        | user1
(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, Policyadmin, UseFT 
| {}
user1     | Sysadmin                                                                                                         
| {}

omm=# dn
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
user1           | user1
(11 rows)

7.总结

通过实验可以得出结论：模式是在数据库层面，用户是在实例层面