作业：

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 zouyang1 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema zouyang2 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema zouyang3 AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema zouyang4 AUTHORIZATION user1;
CREATE SCHEMA

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

musicdb=> create table zouyang1.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table zouyang2.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table zouyang3.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> create table zouyang4.tb1(id int,name varchar(10));
CREATE TABLE
musicdb=> insert into zouyang1.tb1 values (1,'zouyang');
musicdb=> INSERT 0 1
musicdb=> insert into zouyang2.tb1 values (1,'zouyang');
INSERT 0 1
musicdb=> insert into zouyang3.tb1 values (1,'zouyang');
musicdb=> INSERT 0 1
musicdb=> insert into zouyang4.tb1 values (1,'zouyang');
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 | zouyang3 | tb1 | BASE TABLE
musicdb | zouyang2 | tb1 | BASE TABLE
musicdb | zouyang1 | 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 | zouyang4 | tb1 | BASE TABLE
--访问musicdb数据库下不同模式的同名表
musicdb=> select * from zouyang1.tb1;
id | name
----+---------
1 | zouyang
(1 row)

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

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

musicdb=> select * from zouyang4.tb1;
id | name
----+---------
1 | zouyang
(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
zouyang1 | user1
zouyang2 | user1
zouyang3 | user1
zouyang4 | 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)