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

学习目标

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

前面每日一练链接

openGauss 每日一练第 1 天 | openGauss 数据库状态查看
openGauss 每日一练第 2 天 | 学习 gsql 命令行的使用
openGauss 每日一练第 3 天 | openGauss 数据库状态查看
openGauss 每日一练第 4 天 | openGauss 中一个数据库可以被多个用户访问
openGauss 每日一练第 5 天 | openGauss 中一个用户可以访问多个数据库
openGauss 每日一练第 6 天 | openGauss 中用户一次只能连接到一个数据库

课程学习

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

连接数据库

#第一次进入等待15秒
#数据库启动中...
[omm@openGauss ~]$ su - omm
[omm@openGauss ~]$ gsql -d postgres -p 26000
gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr  )
NOTICE : The password has been expired, please change the password. 
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

1.创建模式、查看模式

--进入数据库omm，创建表空间、测试数据库
drop DATABASE  IF EXISTS  musicdb;
drop DATABASE  IF EXISTS  musicdb1;
drop DATABASE  IF EXISTS  musicdb2;
drop DATABASE  IF EXISTS  musicdb3;
drop tablespace IF EXISTS music_tbs;
\set PROMPT1 '%n@%m %~%R%#'
CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;

--执行下面的 SQL 语句，创建用户 user1：

 CREATE USER user1 IDENTIFIED BY 'JiekeXu_1234';
 
--授予user1数据库系统的SYSADMIN权限：

ALTER USER user1 SYSADMIN;

--使用用户 user1 连接到数据库 musicdb，首先查看当前数据库下有哪些模式；
\q
gsql -d musicdb  -U user1 -p 26000 -W JiekeXu_1234 -r

\dn

--注意：openGauss 3.1 版本中比 1.0 多很多模式。
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)

2.然后为数据库 musicdb 创建 4 个模式： schm1、 schm2、 schm3、 schm4

--用户 user1 在数据库 musicdb 中，创建了 4 个模式：
  
create schema  schm1  AUTHORIZATION user1;
create schema  schm2  AUTHORIZATION user1;
create schema  schm3  AUTHORIZATION user1;
create schema  schm4  AUTHORIZATION user1;

–查看musicdb数据库下有哪些模式：

\dn

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

SELECT catalog_name, schema_name, schema_owner
FROM information_schema.schemata;

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

--在不同模式下，创建相同的表                   
create table schm1.ttt(col varchar(100));
create table schm2.ttt(col varchar(100));
create table schm3.ttt(col varchar(100));
create table schm4.ttt(col varchar(100));

--执行下面的SQL语句，往4个模式中的表ttt分别插入一条数据：
--在同一个数据库下，可以直接使用SchemaName.TableName来指定一个表，可以省略数据库名。
insert into schm1.ttt values('Hello! from schema schm1   11111');
insert into schm2.ttt values('Hello! from schema schm2   22222');
insert into schm3.ttt values('Hello! from schema schm3   33333');
insert into schm4.ttt values('Hello! from schema schm4   44444');

--执行下面的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');

--查看视图：
 select * from my_tables;

--注意 openGauss 1.0.0 版本创建视图时会报错没有权限，3.0.1 版本可正常创建。
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');
ERROR:  permission denied for schema public

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

--查看默认的搜索模式的顺序

show SEARCH_PATH;

--访问 musicdb 数据库下其他模式的表，需要指定模式名前缀：
 select * from schm1.ttt;                    

 select * from schm2.ttt;               

 select * from schm3.ttt;         
    
 select * from schm4.ttt;

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

--登录 musicdb 数据库，查看用户和模式
gsql -d musicdb  -U user1 -p 26000 -W JiekeXu_1234 -r
\du
\dn
\q
--登录 omm 数据库，查看用户和模式
gsql -r -p 26000
\du
\dn
\q

课程作业

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

musicdb=> \dn
 List of schemas
   Name   | Owner 
----------+-------
 cstore   | omm
 dbe_perf | omm
 public   | omm
 schm1    | user1
 schm2    | user1
 schm3    | user1
 schm4    | user1
 snapshot | omm
(8 rows)

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

create schema  sch1;
create schema  sch2;
create schema  sch3;
create schema  sch4;

musicdb=> create schema  sch4;
CREATE SCHEMA
musicdb=> \dn
 List of schemas
   Name   | Owner 
----------+-------
 cstore   | omm
 dbe_perf | omm
 public   | omm
 sch1     | user1
 sch2     | user1
 sch3     | user1
 sch4     | user1
 schm1    | user1
 schm2    | user1
 schm3    | user1
 schm4    | user1
 snapshot | omm
(12 rows)

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

--在不同模式下，创建相同的表                   
create table sch1.t(col varchar(100));
create table sch2.t(col varchar(100));
create table sch3.t(col varchar(100));
create table sch4.t(col varchar(100));

--执行下面的 SQL 语句，往4个模式中的表ttt分别插入一条数据：
--在同一个数据库下，可以直接使用 SchemaName.TableName 来指定一个表，可以省略数据库名。
insert into sch1.t values('Hello! from schema sch1   11111');
insert into sch2.t values('Hello! from schema sch2   22222');
insert into sch3.t values('Hello! from schema sch3   33333');
insert into sch4.t values('Hello! from schema sch4   44444');

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

 select * from sch1.t;                    

 select * from sch2.t;               

 select * from sch3.t;         
    
 select * from sch4.t;

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

如下，登录不同的数据库，看到的模式不一样，故模式是在数据库层面的对象，看到的用户是一样的，故用户是在实例层面的对象。

--登录 musicdb 数据库，查看用户和模式
gsql -d musicdb  -U user1 -p 26000 -W JiekeXu_1234 -r
\du
\dn
\q

--登录 postgres 数据库，查看用户和模式
gsql -d postgres -p 26000
gsql ((openGauss 1.0.0 build 0bd0ce80) compiled at 2020-06-30 18:19:27 commit 0 last mr  )
NOTICE : The password has been expired, please change the password. 
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# \du
                                         List of roles
 Role name |                               Attributes                               | Member of 
-----------+------------------------------------------------------------------------+-----------
 jiekexu   |                                                                        | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, UseFT | {}
 user1     | Sysadmin                                                               | {}

postgres=# \dn
  List of schemas
   Name   |  Owner  
----------+---------
 cstore   | omm
 dbe_perf | omm
 jiekexu  | jiekexu
 pmk      | omm
 public   | omm
 snapshot | omm
(6 rows)