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

文档:SCHEMA

SCHEMA

SCHEMA又称作模式。通过管理SCHEMA，允许多个用户使用同一数据库而不相互干扰，可以将数据库对象组织成易于管理的逻辑组，同时便于将第三方应用添加到相应的SCHEMA下而不引起冲突。
每个数据库包含一个或多个SCHEMA。数据库中的每个SCHEMA包含表和其他类型的对象。数据库创建初始，默认具有一个名为PUBLIC的SCHEMA，且所有用户都拥有此SCHEMA的USAGE权限，只有系统管理员和初始化用户可以在public Schema下创建普通函数、聚合函数、存储过程和同义词对象，只有初始化用户可以在public Schema下创建操作符，其他用户即使赋予create权限后也不可以创建上述五种对象。可以通过SCHEMA分组数据库对象。SCHEMA类似于操作系统目录，但SCHEMA不能嵌套。
相同的数据库对象名称可以应用在同一数据库的不同SCHEMA中，而没有冲突。例如，a_schema和b_schema都可以包含名为mytable的表。具有所需权限的用户可以访问数据库的多个SCHEMA中的对象。
通过CREATE USER创建用户的同时，系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA。

课程学习目标

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

当前没有实验过一主一备环境：如果说用户是在实例层面，那么在主节点创建的用户，是不在备节点存在的？待实验

课程作业

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

omm=# 

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

a) 环境准备

omm=# drop database if exists musicdb;
NOTICE:  database "musicdb" does not exist, skipping
omm=# DROP DATABASE
omm=# drop tablespace if exists music_tbs;
NOTICE:  Tablespace "music_tbs" does not exist, skipping.
DROP TABLESPACE
omm=# create tablespace music_tbs relative location 'tablespace/music_tbs1';
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;
omm=# ALTER ROLE

b) user1登录musicdb,创建schema

omm=# \c musicdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "user1".
musicdb=>
musicdb=> create schema tv_schm1 authorization user1;
CREATE SCHEMA
musicdb=> create schema tv_schm2 authorization user1;
CREATE SCHEMA
musicdb=> create schema tv_schm3 authorization user1;
CREATE SCHEMA
musicdb=> create schema tv_schm4 authorization user1;
CREATE SCHEMA

c) 查看连接信息,显示当前user1连接到数据库musicdb

musicdb=> \conninfo
You are connected to database "musicdb" as user "user1" via socket in "/tmp" at port "5432".

d) 查看\dn schme信息

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
 tv_schm1        | user1
 tv_schm2        | user1
 tv_schm3        | user1
 tv_schm4        | user1
(14 rows)

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

a) 在数据库musicdb中创建tv_schm1的表tbtv

musicdb=> create table tv_schm1.tbtv(col1 varchar(100));
CREATE TABLE

b) tv_schm1.tbtv插入一行数据

musicdb=> insert into tv_schm1.tbtv values('hello schm1');
INSERT 0 1
musicdb=>

c) 通过schema_name.table_name查询表,可以查询得到col1,返回0行

musicdb=> select * from tv_schm1.tbtv;
musicdb=>     col1     
-------------
 hello schm1
(1 row)

d) 直接通过table_name查询表,返回ERROR

musicdb=> select * from tbtv;
ERROR:  relation "tbtv" does not exist on gaussdb
LINE 1: select * from tbtv;
                      ^
musicdb=>

e) \dt 查询表信息,没有查询结果

musicdb=> \dt********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  c.reloptions as "Storage"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'db4ai'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      AND c.relname not like 'matviewmap\_%'
      AND c.relname not like 'mlog\_%'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************


No relations found.
musicdb=>

f) 以此类推,创建tv_schm2,tv_schm3,tv_schm4,分别创建同名表tvtb,插入一行数据’hello schmn’

musicdb=> create table tv_schm2.tbtv(col varchar(100));
CREATE TABLE
musicdb=> insert into tv_schm2.tbtv values('hello schm2');
INSERT 0 1
musicdb=> create table tv_schm3.tbtv(col varchar(100));
CREATE TABLE
musicdb=> insert into tv_schm3.tbtv values(h'ello schm3');
INSERT 0 1
musicdb=> create table tv_schm4.tbtv(col1 varchar(100));
CREATE TABLE
musicdb=> insert into tv_schm4.tbtv values('hello schm4');
INSERT 0 1
musicdb=> 

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

musicdb=> select * from tv_schm1.tbtv;
    col1     
-------------
 hello schm1
(1 row)

musicdb=> select * from tv_schm2.tbtv;
     col     
-------------
 hello schm2
(1 row)

musicdb=> select * from tv_schm3.tbtv;
musicdb=>      col     
-------------
 hello schm3
(1 row)
              
musicdb=> select * from tv_schm4.tbtv;
    col1     
-------------
 hello schm4
(1 row)

musicdb=> 

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

a)退出登录musicdb库,登录omm数据库

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

b)查询\dn schema信息,没有刚才创建的命名为tv_schmn的schema,可见schema只隶属于其被创建的数据库中

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