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

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 TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE
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
omm=# \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=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)

enmdb=> create table t1(col varchar(20));
CREATE TABLE
enmdb=> insert into t1 values ('hello');
INSERT 0 1
enmdb=> select * from t1;
  col  
-------
 hello
(1 row)

enmdb=> select table_catalog,table_schema,table_name,table_type from information_schema.tables where table_schema = 'public';
 table_catalog | table_schema | table_name | table_type 
---------------+--------------+------------+------------
 enmdb         | public       | t1         | BASE TABLE
(1 row)

enmdb=> 

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

CREATE SCHEMA
enmdb=> create schema enmschm2;
CREATE SCHEMA
enmdb=> show SEARCH_PATH;
enmdb=>   search_path   
----------------
 "$user",public
(1 row)


enmdb=> create table t1(col varchar(20));
ERROR:  relation "t1" already exists in schema "public"
DETAIL:  creating new table with existing name in the same schema
enmdb=> insert into t1 values ('hello');
INSERT 0 1
enmdb=> select * from t1;
  col  
-------
 hello
 hello
(2 rows)

enmdb=> 

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

  search_path   
----------------
 "$user",public
(1 row)

enmdb=> create table enmschm2.t3(col1  char(20));
CREATE TABLE
enmdb=>  insert into enmschm2.t3 values ('hello   333');
INSERT 0 1
enmdb=> select * from enmschm2.t3 ;
enmdb=>          col1         
----------------------
 hello   333         
(1 row)


enmdb=>