概述

本文档记录openGauss 3.0.0数据库每日一练第13天课程作业，学习openGauss表的创建、搜索路径和访问方法。

课程练习

创建一个表（默认，不指定模式），查看该表在那个模式下

omm@local:/opt/huawei/tmp [postgres]=#CREATE TABLESPACE day13 RELATIVE LOCATION 'tablespace/day13';
CREATE TABLESPACE
omm@local:/opt/huawei/tmp [postgres]=#CREATE DATABASE musicdb13 WITH TABLESPACE = day13;
CREATE DATABASE
omm@local:/opt/huawei/tmp [postgres]=#CREATE USER user13 IDENTIFIED BY 'zs@123456';
CREATE ROLE
omm@local:/opt/huawei/tmp [postgres]=#ALTER USER user13  SYSADMIN;
ALTER ROLE
omm@local:/opt/huawei/tmp [postgres]=#\c musicdb13 user13
Password for user user13: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb13" as user "user13".
user13@local:/opt/huawei/tmp [musicdb13]=>create table tab_day13_1(id int,name varchar(20));
CREATE TABLE
user13@local:/opt/huawei/tmp [musicdb13]=>\d
                            List of relations
 Schema |    Name     | Type  | Owner  |             Storage              
--------+-------------+-------+--------+----------------------------------
 public | tab_day13_1 | table | user13 | {orientation=row,compression=no}
(1 row)

默认位于public下

使用一个用户连接到musicdb13数据库，访问不同模式表

user13@local:/opt/huawei/tmp [musicdb13]=>create schema schem1;
CREATE SCHEMA
user13@local:/opt/huawei/tmp [musicdb13]=>create schema schem2;
CREATE SCHEMA
user13@local:/opt/huawei/tmp [musicdb13]=>create schema schem3;
CREATE SCHEMA
user13@local:/opt/huawei/tmp [musicdb13]=>create table schem1.tab_day13_1(id int,name varchar(20));
CREATE TABLE
user13@local:/opt/huawei/tmp [musicdb13]=>create table schem2.tab_day13_1(id int,name varchar(20));
CREATE TABLE
user13@local:/opt/huawei/tmp [musicdb13]=>create table schem3.tab_day13_1(id int,name varchar(20));
CREATE TABLE
user13@local:/opt/huawei/tmp [musicdb13]=>insert into schem1.tab_day13_1 values(1,'table owner schem1');
INSERT 0 1
user13@local:/opt/huawei/tmp [musicdb13]=>insert into schem2.tab_day13_1 values(1,'table owner schem2');
INSERT 0 1
user13@local:/opt/huawei/tmp [musicdb13]=>insert into schem3.tab_day13_1 values(1,'table owner schem3');
INSERT 0 1
user13@local:/opt/huawei/tmp [musicdb13]=>select * from schem1.tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem1
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>select * from schem2.tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem2
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>select * from schem3.tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem3
(1 row)

在会话级设置模式搜索路径

user13@local:/opt/huawei/tmp [musicdb13]=>\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
 schem1          | user13  <===============
 schem2          | user13  <===============
 schem3          | user13  <===============
 snapshot        | omm
 sqladvisor      | omm
(13 rows)

user13@local:/opt/huawei/tmp [musicdb13]=>show search_path;
  search_path   
----------------
 "$user",public
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>set search_path to schem2;
SET
user13@local:/opt/huawei/tmp [musicdb13]=>show search_path;
 search_path 
-------------
 schem2
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>insert into tab_day13_1 values(2,'table owner schem2');
INSERT 0 1
user13@local:/opt/huawei/tmp [musicdb13]=>insert into schem2.tab_day13_1 values(3,'table owner schem2');
INSERT 0 1
user13@local:/opt/huawei/tmp [musicdb13]=>select * from schem2.tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem2
  2 | table owner schem2
  3 | table owner schem2
(3 rows)

user13@local:/opt/huawei/tmp [musicdb13]=>select * from schem1.tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem1
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>show search_path;
 search_path 
-------------
 schem2
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>set search_path to schem1;
SET
user13@local:/opt/huawei/tmp [musicdb13]=>select * from tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem1
(1 row)

user13@local:/opt/huawei/tmp [musicdb13]=>set search_path to schem2;
SET
user13@local:/opt/huawei/tmp [musicdb13]=>select * from tab_day13_1;
 id |        name        
----+--------------------
  1 | table owner schem2
  2 | table owner schem2
  3 | table owner schem2
(3 rows)