概述
本文档记录openGauss 3.0.0数据库每日一练第7天课程作业，学习openGauss数据库、用户和模式的关系和访问方式，理解模式是在数据库层面，用户是在实例层面。
课程练习
查看当前数据库下有哪些模式
[omm@ogauss1 ~]$ gsql -d music_db -p 15400 -r -U user1 -W zs@123456
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
user1@local:/opt/huawei/tmp [music_db]=>\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)
为数据库music_db创建4个模式
user1@local:/opt/huawei/tmp [music_db]=>create schema schm1 AUTHORIZATION user1;
CREATE SCHEMA
user1@local:/opt/huawei/tmp [music_db]=>create schema schm2 AUTHORIZATION user1;
CREATE SCHEMA
user1@local:/opt/huawei/tmp [music_db]=>create schema schm3 AUTHORIZATION user1;
CREATE SCHEMA
user1@local:/opt/huawei/tmp [music_db]=>create schema schm4 AUTHORIZATION user1;
CREATE SCHEMA
user1@local:/opt/huawei/tmp [music_db]=>\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)
在数据库music_db的不同的模式下创建同名的表
user1@local:/opt/huawei/tmp [music_db]=>create table schm1.day7(id int,note varchar(20));
CREATE TABLE
user1@local:/opt/huawei/tmp [music_db]=>create table schm2.day7(id int,note varchar(20));
CREATE TABLE
user1@local:/opt/huawei/tmp [music_db]=>create table schm3.day7(id int,note varchar(20));
CREATE TABLE
user1@local:/opt/huawei/tmp [music_db]=>create table schm4.day7(id int,note varchar(20));
CREATE TABLE
user1@local:/opt/huawei/tmp [music_db]=>
user1@local:/opt/huawei/tmp [music_db]=>insert into schm1.day7 values(1,'table in schm1');
INSERT 0 1
user1@local:/opt/huawei/tmp [music_db]=>insert into schm2.day7 values(1,'table in schm2');
INSERT 0 1
user1@local:/opt/huawei/tmp [music_db]=>insert into schm3.day7 values(1,'table in schm3');
INSERT 0 1
user1@local:/opt/huawei/tmp [music_db]=>insert into schm4.day7 values(1,'table in schm4');
INSERT 0 1
user1@local:/opt/huawei/tmp [music_db]=>
user1@local:/opt/huawei/tmp [music_db]=>\d
No relations found.
访问musicdb数据库下不同模式的同名表
user1@local:/opt/huawei/tmp [music_db]=>show search_path;
search_path
----------------
"$user",public
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from schm1.day7;
id | note
----+----------------
1 | table in schm1
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from schm2.day7;
id | note
----+----------------
1 | table in schm2
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from schm3.day7;
id | note
----+----------------
1 | table in schm3
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from schm4.day7;
id | note
----+----------------
1 | table in schm4
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>
user1@local:/opt/huawei/tmp [music_db]=>set search_path='schm1';
SET
user1@local:/opt/huawei/tmp [music_db]=>show search_path;
search_path
-------------
schm1
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from day7;
id | note
----+----------------
1 | table in schm1
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>set search_path='schm2';
SET
user1@local:/opt/huawei/tmp [music_db]=>show search_path;
search_path
-------------
schm2
(1 row)
user1@local:/opt/huawei/tmp [music_db]=>select * from day7;
id | note
----+----------------
1 | table in schm2
(1 row)
在同一个数据库内，默认会按照search_path中设置的顺序访问对应schema下的表。
模式是在数据库层面，用户是在实例层面
[omm@ogauss1 ~]$ gsql -d music_db1 -p 15400 -r -U user1 -W zs@123456
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
user1@local:/opt/huawei/tmp [music_db1]=>\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)
user1@local:/opt/huawei/tmp [music_db1]=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
test1 | Sysadmin | {}
user1 | Sysadmin <================= | {}
user10 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}
user6 | Sysadmin | {}
[omm@ogauss1 ~]$ gsql -d music_db -p 15400 -r -U user1 -W zs@123456
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:34 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
user1@local:/opt/huawei/tmp [music_db]=>\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)
user1@local:/opt/huawei/tmp [music_db]=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
test1 | Sysadmin | {}
user1 | Sysadmin <====================== | {}
user10 | Sysadmin | {}
user2 | Sysadmin | {}
user3 | Sysadmin | {}
user6 | Sysadmin
不同的数据库music_db和music_db1内均有user1用户，但是在数据库music_db和music_db1有不同的schema。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
