暂无图片
暂无图片
暂无图片
暂无图片
1
暂无图片

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

原创 yao9164 2022-12-08
799

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=# \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=# CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE TABLESPACE
omm=# CREATE DATABASE musicdb  WITH TABLESPACE = music_tbs;
CREATE DATABASE
复制

image.png

2 创建用户并授权

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
复制

image.png

3 使用创建用户连接数据库

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=> \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 musicdb   | omm   | UTF8     | C       | C     | 
 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
(5 rows)
musicdb=>            List of tablespaces
    Name    | Owner |      Location       
------------+-------+---------------------
 music_tbs  | omm   | tablespace/test_ts1
 pg_default | omm   | 
复制

image.png

4 在musicdb库下创建四个schema

musicdb=> create schema  schema2  AUTHORIZATION user1;
musicdb=> CREATE SCHEMA
musicdb=> create schema  schema3  AUTHORIZATION user1;
CREATE SCHEMA
musicdb=> create schema  schema4  AUTHORIZATION user1;
CREATE SCHEMA
复制

image.png

5 查看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
 schema1         | user1
 schema2         | user1
 schema3         | user1
 schema4         | user1
 schm1           | user1
 snapshot        | omm
 sqladvisor      | omm
(15 rows)

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      | schm1              | user1
 musicdb      | schema1            | user1
 musicdb      | schema2            | user1
 musicdb      | schema3            | user1
 musicdb      | schema4            | user1
(18 rows)

-- 通过以上两种方式查看数据库下的所有模式,可以发现查询到信息有些差异,\dn查询到的要少一些,相比语句查询少了pg_toast、snapshot、information_schema这三个模式。        
复制

image.png

6 分别在musicdb库下不同模式创建同名表并插入数据

musicdb=> create table schema1.tb01(content varchar(100));
CREATE TABLE
musicdb=> create table schema2.tb01(content varchar(100));
CREATE TABLE
musicdb=> create table schema3.tb01(content varchar(100));
CREATE TABLE
musicdb=> create table schema4.tb01(content varchar(100));
CREATE TABLE
musicdb=> 
musicdb=> insert into schema1.tb01 values('This is schema1');
INSERT 0 1
musicdb=> insert into schema2.tb01 values('This is schema2');
INSERT 0 1
musicdb=> insert into schema3.tb01 values('This is schema3');
INSERT 0 1
musicdb=> insert into schema4.tb01 values('This is schema4');
INSERT 0 1
复制

image.png

7 创建并查看视图

musicdb=> create or replace view show_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 show_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          | show_tables | VIEW
 musicdb       | schema4         | tb01        | BASE TABLE
 musicdb       | schema3         | tb01        | BASE TABLE
 musicdb       | schema2         | tb01        | BASE TABLE
 musicdb       | schema1         | tb01        | BASE TABLE
(8 rows)
复制

image.png

8 查看搜索模式顺序

musicdb=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)
-- 可以看到搜索模式的顺序是先搜索user,再搜索public
复制

9 使用user1访问musicdb库下不同模式的同名表

musicdb=> select * from schema1.tb01;  
musicdb=>      content     
-----------------
 This is schema1
(1 row)

musicdb=> select * from schema2.tb01; 
     content     
-----------------
 This is schema2
(1 row)

musicdb=> select * from schema3.tb01;
     content     
-----------------
 This is schema3
(1 row)

musicdb=> select * from schema4.tb01;
     content     
-----------------
 This is schema4
(1 row)


复制

image.png

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

墨天轮-雪宝君
暂无图片
2年前
评论
暂无图片 0
作业审核合格,一起参与21天openGauss学习打卡活动! 活动详情:https://www.modb.pro/db/551619
2年前
暂无图片 点赞
评论