openGauss每日一练第 13 天 | 表管理1

学习目标
学习openGauss表的创建、搜索路径和访问方法等

课程作业
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=# create table t1(id int);
CREATE TABLE
omm=# insert into t1 values(10);
INSERT 0 1
omm=# select * from t1;
 id 
----
 10
(1 row)

omm=# show search_path;
  search_path   
----------------
 "$user",public
(1 row)

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

omm=# create user dhr identified by 'dhr@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE

omm=# create tablespace enmtbs relative location 'tablespace/enmtbs';
CREATE TABLESPACE
omm=# create database enmdb with tablespace=enmtbs;
CREATE DATABASE
omm=# alter user dhr sysadmin;
ALTER ROLE
omm=# \q

omm@modb:~$ gsql -d enmdb -U dhr -W dhr@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=> create schema enschm1;
CREATE SCHEMA
enmdb=> create schema enschm2;
CREATE SCHEMA
enmdb=> create table enschm1.t1(id int);
CREATE TABLE
enmdb=> create table enschm2.t1(id int);
CREATE TABLE
enmdb=> insert into enschm1.t1 values(11);
INSERT 0 1
enmdb=> insert into enschm2.t1 values(22);
INSERT 0 1
enmdb=> select * from enschm1.t1;
 id 
----
 11
(1 row)

enmdb=> select * from enschm2.t1;
 id 
----
 22
(1 row)

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

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

enmdb=> set search_path to enschm1;
SET
enmdb=> create table enschm1.tt(id int);
CREATE TABLE
enmdb=> insert into enschm1.tt values(20);
INSERT 0 1
enmdb=> select * from enschm1.tt;
 id 
----
 20
(1 row)