暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / openGauss每日一练第7天 | openGauss中一个数据库中可以创建多个模式

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

原创 xiaocx 2022-11-30
238

学习目标

    学习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=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

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 | {}

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(4 rows)

omm=#


2.创建表空间、测试数据库、用户等

    进入数据库omm，创建表空间、测试数据库、用户等，过程如下：
omm=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
CREATE DATABASE
omm=# 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
omm=# 
omm=# \q

实验步骤

1.查看模式

    使用用户user1连接到数据库musicdb，首先查看当前数据库下有哪些模式；

omm@modb:~$ gsql -d musicdb  -U user1 -p 5432 -W kunpeng@1234 -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.

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)
musicdb=>


2.创建模式

    用户user1在数据库musicdb中，创建4个模式： schm_xiaocx1、schm_xiaocx2、schm_xiaocx3、schm_xiaocx4。过程如下：

create schema schm_xiaocx1 AUTHORIZATION user1;
create schema schm_xiaocx2 AUTHORIZATION user1;
create schema schm_xiaocx3 AUTHORIZATION user1;
create schema schm_xiaocx4 AUTHORIZATION user1;

musicdb=> create schema  schm_xiaocx1  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm_xiaocx2  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm_xiaocx3  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schm_xiaocx4  AUTHORIZATION user1;
CREATE SCHEMA

    使用\dn查看musicdb数据库下有哪些模式：

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
 schm_xiaocx1    | user1
 schm_xiaocx2    | user1
 schm_xiaocx3    | user1
 schm_xiaocx4    | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)

musicdb=>

    除了可以用gsql的元命令\dn来查看数据库有哪些模式，还可以执行下面的SQL语句，查看某个数据库下有哪些模式：

musicdb=> SELECT catalog_name, schema_name, schema_owner FROM information_schema.schemata;
 catalog_name |    schema_name     | schema_owner 
--------------+--------------------+--------------
 musicdb      | pg_toast           | omm
 musicdb      | cstore             | omm
 musicdb      | pkg_service        | omm
 musicdb      | dbe_perf           | omm
 musicdb      | snapshot           | omm
 musicdb      | blockchain         | omm
 musicdb      | pg_catalog         | omm
 musicdb      | public             | omm
 musicdb      | sqladvisor         | omm
 musicdb      | dbe_pldebugger     | omm
 musicdb      | dbe_pldeveloper    | omm
 musicdb      | information_schema | omm
 musicdb      | db4ai              | omm
 musicdb      | schm_xiaocx1       | user1
 musicdb      | schm_xiaocx2       | user1
 musicdb      | schm_xiaocx3       | user1
 musicdb      | schm_xiaocx4       | user1
(17 rows)

musicdb=>


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

    执行下面的SQL语句，分别在上面创建的4个模式下，创建相同的表。

create table schm_xiaocx1.txiaocx(col varchar(100));
create table schm_xiaocx2.txiaocx(col varchar(100));
create table schm_xiaocx3.txiaocx(col varchar(100));
create table schm_xiaocx4.txiaocx(col varchar(100));

musicdb=> create table schm_xiaocx1.txiaocx(col varchar(100));
CREATE TABLE
musicdb=> create table schm_xiaocx2.txiaocx(col varchar(100));
CREATE TABLE
musicdb=> create table schm_xiaocx3.txiaocx(col varchar(100));
CREATE TABLE
musicdb=> create table schm_xiaocx4.txiaocx(col varchar(100));
CREATE TABLE
musicdb=>

    执行下面的SQL语句，往4个模式中的表txiaocx分别插入一条数据：
    在同一个数据库下，可以直接使用SchemaName.TableName来指定一个表，可以省略数据库名。

insert into schm_xiaocx1.txiaocx values('Hello! from schema schm_xiaocx1 11111');
insert into schm_xiaocx2.txiaocx values('Hello! from schema schm_xiaocx2 22222');
insert into schm_xiaocx3.txiaocx values('Hello! from schema schm_xiaocx3 33333');
insert into schm_xiaocx4.txiaocx values('Hello! from schema schm_xiaocx4 44444');

musicdb=> insert into schm_xiaocx1.txiaocx values('Hello! from schema schm_xiaocx1   11111');
INSERT 0 1
musicdb=> insert into schm_xiaocx2.txiaocx values('Hello! from schema schm_xiaocx2   22222');
INSERT 0 1
musicdb=> insert into schm_xiaocx3.txiaocx values('Hello! from schema schm_xiaocx3   33333');
INSERT 0 1
musicdb=> insert into schm_xiaocx4.txiaocx values('Hello! from schema schm_xiaocx4   44444');
INSERT 0 1
musicdb=>

    执行下面的SQL语句，查看musicdb数据库目前有哪些表：
        创建视图：

CREATE OR replace VIEW my_tables AS
SELECT table_catalog,
	table_schema,
	table_name,
	table_type
FROM information_schema.tables
WHERE table_schema NOT IN (
		'pg_catalog',
		'information_schema',
		'dbe_perf'
		);

        查看视图：

musicdb=>  select * from my_tables;
 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       | schm_xiaocx4    | txiaocx    | BASE TABLE
 musicdb       | schm_xiaocx3    | txiaocx    | BASE TABLE
 musicdb       | schm_xiaocx2    | txiaocx    | BASE TABLE
 musicdb       | schm_xiaocx1    | txiaocx    | BASE TABLE
(8 rows)

musicdb=>


4.查看用户在数据库中搜索模式的顺序：

    使用show SEARCH_PATH，查看默认的搜索模式的顺序。
musicdb=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)

musicdb=>



    使用用户user1访问musicdb数据库下其他模式的表，需要指定模式名前缀：

musicdb=>  select * from schm_xiaocx1.txiaocx;                    
               col                
----------------------------------
 Hello! from schema schm1   11111
(1 row)

musicdb=>  select * from schm_xiaocx2.txiaocx;               
               col                
----------------------------------
 Hello! from schema schm2   22222
(1 row)

musicdb=>  select * from schm_xiaocx3.txiaocx;         
               col                
----------------------------------
 Hello! from schema schm3   33333
(1 row)

musicdb=>  select * from schm_xiaocx4.txiaocx;
               col                
----------------------------------
 Hello! from schema schm4   44444
(1 row)

musicdb=> \q


5.在不同的数据库中，查看其用户和模式

    登录musicdb数据库，查看用户和模式
omm@modb:~$ gsql -d musicdb  -U user1 -p 5432 -W kunpeng@1234 -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.

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
 schm_xiaocx1    | user1
 schm_xiaocx2    | user1
 schm_xiaocx3    | user1
 schm_xiaocx4    | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)

musicdb=> \q


    登录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=# \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
     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)

omm=# \q


结论

    模式是在数据库层面，用户是在实例层面。
opengaussopengauss每日一练墨力计划opengauss训练营我和opengauss的故事
最后修改时间：2022-12-03 11:55:13
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

openGauss每日一练（共22篇）
openGauss每日一练

评论