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

学习目标

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

学习内容

通过本章学习，进一步熟悉opengauss基本的建库、建表操作。了解了数据库可以建多个模式，并且模式之间表可以重复不互相影响。

1.创建数据库。

2.创建一个用户，并赋权sysadmin权限。

3.通过用户连接到数据库，建立多个模式。

4.给不同模式创建同名的表并插入数据。

5.查询不同模式的表，查询是否正常。

课后作业

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

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
 pkg_service     | omm
 public          | omm
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
(14 rows)

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

musicdb=# create schema test1;
CREATE SCHEMA
musicdb=# create schema test2;
CREATE SCHEMA
musicdb=# create schema test3;
CREATE SCHEMA
musicdb=# create schema test4;
CREATE SCHEMA

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

musicdb=# create table test1.products1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
musicdb=# insert into test1.products1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
musicdb=# create table test2.products1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
musicdb=# insert into test2.products1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
musicdb=# create table test3.products1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
musicdb=# insert into test3.products1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
musicdb=# create table test4.products1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
musicdb=# insert into test4.products1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
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
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
 test1           | omm
 test2           | omm
 test3           | omm
 test4           | omm
(18 rows)

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

创建视图，确认表的模式

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_name  ='products1';
CREATE VIEW
musicdb=# select * from my_tables;
 table_catalog | table_schema | table_name | table_type 
---------------+--------------+------------+------------
 musicdb       | test1        | products1  | BASE TABLE
 musicdb       | test2        | products1  | BASE TABLE
 musicdb       | test3        | products1  | BASE TABLE
 musicdb       | test4        | products1  | BASE TABLE
(4 rows)

查询不同模式的表

musicdb=# select * from test1.products1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
       1666 | harry potter         | toys                          
(4 rows)

musicdb=# select * from test2.products1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
       1666 | harry potter         | toys                          
(4 rows)

musicdb=# select * from test3.products1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
       1666 | harry potter         | toys                          
(4 rows)

musicdb=# select * from test4.products1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
       1666 | harry potter         | toys                          
(4 rows)

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

musicdb=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Membe
r 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
 schm1           | user1
 schm2           | user1
 schm3           | user1
 schm4           | user1
 snapshot        | omm
 sqladvisor      | omm
 test4           | omm
 test1           | omm
 test2           | omm
 test3           | omm
(18 rows)