openGauss每日一练第13天 | openGauss逻辑结构：表管理1

在数据库下面创建表对象，如果不指定模式，默认是创建在public模式下面。openGauss创建表的同时，可以指定表是行表还是列表，表的模式，表的表空间、表的注释等等属性。

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

su - omm
gsql -r

enmdb=> \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 enmschm1        | user1
 enmschm2        | user1
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(12 rows)

--创建t1表，不指定表模式，默认表的模式是public
enmdb=> create table t1(id int);
CREATE TABLE
enmdb=> \dti t1
                            List of relations
 Schema | Name | Type  | Owner | Table |             Storage              
--------+------+-------+-------+-------+----------------------------------
 public | t1   | table | user1 |       | {orientation=row,compression=no}
(1 row)

enmdb=> 

2、使用一个用户连接到enmdb数据库，测试该用户可以访问不同模式中的表

--用户只有有权限就可以访问不同模式下的表，可以通过模式名.表名方式来访问
enmdb=> set search_path=enmschm1;
enmdb=> SET

enmdb=> create table k1(id int);
CREATE TABLE
enmdb=> insert into k1 values(1);
INSERT 0 1
enmdb=> select * from k1;
 id 
----
  1
(1 row)

enmdb=> \dti k1
                             List of relations
  Schema  | Name | Type  | Owner | Table |             Storage              
----------+------+-------+-------+-------+----------------------------------
 enmschm1 | k1   | table | user1 |       | {orientation=row,compression=no}
(1 row)

enmdb=> 


enmdb=> set search_path = enmschm2;
SET
enmdb=> create table k2(id int);
CREATE TABLE
enmdb=> insert into k2 values(2);
INSERT 0 1
enmdb=> select * from k2;
 id 
----
  2
(1 row)

enmdb=> \dti k2 
                             List of relations
  Schema  | Name | Type  | Owner | Table |             Storage              
----------+------+-------+-------+-------+----------------------------------
 enmschm2 | k2   | table | user1 |       | {orientation=row,compression=no}
(1 row)

enmdb=> 


enmdb=> \q
omm@modb:~$ gsql -d enmdb -U user1 -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.

enmdb=> select current_database(),current_user,current_schema;
current_database | current_user | current_schema 
------------------+--------------+----------------
 enmdb            | user1        | public
(1 row)

--user1访问enmschm1.k1表
enmdb=> select * from enmschm1.k1;
 id 
----
  1
(1 row)

--user1访问enmschm2.k2表
enmdb=> select * from enmschm2.k2;
 id 
----
  2
(1 row)

3、在会话级设置模式搜索路径为:模式enmschm1,使用SchemaName.TableName的表标识方法访问表(创建表、插入数据和查询表中数据)

enmdb=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

enmdb=> set search_path = enmschm1;
SET

enmdb=> show search_path;
 search_path 
-------------
 enmschm1
(1 row)

enmdb=> select * from enmschm1.k1;
enmdb=>  id 
----
  1
(1 row)

enmdb=> 


enmdb=> create table t2(id int);
CREATE TABLE
enmdb=> insert into t2 values(1),(2);
INSERT 0 2
enmdb=> select * from enmschm1.t2;
 id 
----
  1
  2
(2 rows)

enmdb=> \dti t2
                             List of relations
  Schema  | Name | Type  | Owner | Table |             Storage              
----------+------+-------+-------+-------+----------------------------------
 enmschm1 | t2   | table | user1 |       | {orientation=row,compression=no}
(1 row)