openGauss每日一练第 7 天 | 学习心得体会

原创 Lily_tj 2022-11-30

297

学习目标

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

课程学习

一个用户连接到数据库后，可以在这个数据库中创建多个模式。

课后作业和操作结果

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

\dn

gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:46 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
 dbe_sql_util    | omm
 hc              | hc
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(12 rows)

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

create tablespace musicdb_tbs relative location 'tablespace/musicdb_tbs';

create database musicdb with tablespace musicdb_tbs;

\c musicdb

create schema schema1 authorization hc;
create schema schema2 authorization hc;
create schema schema3 authorization hc;
create schema schema4 authorization hc;

omm=# \c musicdb
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb" as user "omm".
musicdb=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(11 rows)

musicdb=# create user hc identified by 'qaz_hc123';
ERROR:  role "hc" already exists
musicdb=# create schema schema1 authorization hc;
CREATE SCHEMA
musicdb=# create schema schema2 authorization hc;
CREATE SCHEMA
musicdb=# create schema schema3 authorization hc;
CREATE SCHEMA
musicdb=# create schema schema4 authorization hc;
CREATE SCHEMA
musicdb=# \conninfo
You are connected to database "musicdb" as user "omm" via socket in "/tmp" at port "5432".

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

create table schema1.tab1(id int,name varchar(20));

create table schema2.tab2(id int,name varchar(20));

create table schema3.tab3(id int,name varchar(20));

create table schema4.tab4(id int,name varchar(20));

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');
CREATE VIEW
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       | schema4         | tab4       | BASE TABLE
 musicdb       | schema3         | tab3       | BASE TABLE
 musicdb       | schema2         | tab2       | BASE TABLE
 musicdb       | schema1         | tab1       | BASE TABLE
(8 rows)

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



select * from schema1.tab1;

select * from schema2.tab2;

select * from schema3.tab3;

select * from schema4.tab4;

musicdb=> insert into schema1.tab1 values(1,'user1');
INSERT 0 1
musicdb=> insert into schema2.tab2 values(2,'user2');
INSERT 0 1
musicdb=> insert into schema3.tab3 values(3,'user3');
INSERT 0 1
musicdb=> insert into schema4.tab4 values(4,'user4');
INSERT 0 1
musicdb=> select * from schema1.tab1;
 id | name  
----+-------
  1 | user1
(1 row)

musicdb=> select * from schema2.tab2;
 id | name  
----+-------
  2 | user2
(1 row)

musicdb=> select * from shcema3.tab3;
ERROR:  schema "shcema3" does not exist
LINE 1: select * from shcema3.tab3;
                      ^
musicdb=> select * from schema3.tab3;
 id | name  
----+-------
  3 | user3
(1 row)

musicdb=> select * from schema4.tab4;
 id | name  
----+-------
  4 | user4
(1 row)

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