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

本次课程目的是学习openGauss表的创建、搜索路径和访问方法等
1.创建一个表（默认，不指定模式），查看该表在那个模式下
本地环境创建了单独的test数据库和用户，默认下创建的表在public模式下，由于权限问题，尝试了很多方法，最后还是改了模式的所有者才可以创建表。

mm=# create tablespace testtbs relative location 'tablespace/testtbs1';
CREATE TABLESPACE
omm=# 
omm=# create database test with tablespace = testtbs;
CREATE DATABASE
omm=# 
omm=# create user user1 identified by 'kunpeng@1234';
CREATE ROLE
omm=# alter user user1 sysadmin;
ALTER ROLE

omm=# GRANT ALL PRIVILEGES ON DATABASE test to user1;
GRANT
mm=# ALTER ROLE user1 CREATEDB;
ALTER ROLE
omm=# 
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user1;
test=# alter schema public owner to user1;
ALTER SCHEMA

omm=# \q
[omm@ogca1 ~]$ gsql -r -p15400 -d test -U user1 -W 'kunpeng@1234'
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

test=> create table testtable(col varchar(100));
CREATE TABLE
test=> 
test=> 
test=> 
test=> insert into testtable values('Hello from testtable!');
INSERT 0 1
test=> select * from testtable;
          col          
-----------------------
 Hello from testtable!
(1 row)
test=> select table_catalog,table_schema,table_name,table_type
test-> from information_schema.tables 
test-> where table_schema = 'public';
 table_catalog | table_schema | table_name | table_type 
---------------+--------------+------------+------------
 test          | public       | testtable  | BASE TABLE
(1 row)

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

omm=# create user user2 identified by 'kunpeng@1234';
CREATE ROLE
omm=# alter user user2 sysadmin;
ALTER ROLE
omm=# 
[omm@ogca1 ~]$ gsql -p15400 -r -U user2 -W kunpeng@1234
gsql: FATAL:  database "user2" does not exist
[omm@ogca1 ~]$ gsql -p15400 -r -U user2 -W kunpeng@1234 -d omm
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=> 
test=> select * from public.testtable;
          col          
-----------------------
 Hello from testtable!
(1 row)

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

omm=> SET SEARCH_PATH TO user1;
SET
omm=> show search_path;
 search_path 
-------------
 user1
(1 row)
omm=> create table user1.test1(id number,name varchar(10));
CREATE TABLE
omm=> insert into user1.test1 values (1,'aaa'),(2,'bbb');
INSERT 0 2
omm=> select * from user1.test1;
 id | name 
----+------
  1 | aaa
  2 | bbb
(2 rows)