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

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

--创建表空间Mytest、数据库enmdb、用户test1，并授予用户test1 SYSADMIN权限：
su - omm
gsql  -r
CREATE TABLESPACE Mytest RELATIVE LOCATION 'tablespace/Mytest1';
CREATE DATABASE enmdb WITH TABLESPACE = Mytest;
CREATE USER test1 IDENTIFIED BY 'Mygstest@1234';
ALTER USER test1  SYSADMIN;
--创建一个测试表mytest1，并插入数据：
drop table if exists mytest1;
create table mytest1(product_id INTEGER,product_name Char(20),category Char(30));
insert into mytest1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
select * from mytest1;
\dt mytest1

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 Mytest RELATIVE LOCATION 'tablespace/Mytest1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = Mytest;
CREATE DATABASE
omm=# CREATE USER test1 IDENTIFIED BY 'Mygstest@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER test1  SYSADMIN;
ALTER ROLE
omm=# drop table if exists mytest1;
NOTICE:  table "mytest1" does not exist, skipping
DROP TABLE
omm=# create table mytest1(product_id INTEGER,product_name Char(20),category Char(30));
CREATE TABLE
omm=# insert into mytest1 values(1502,'olympus camera','electrncs'),(1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 4
omm=# select * from mytest1;
 product_id |     product_name     |            category            
------------+----------------------+--------------------------------
       1502 | olympus camera       | electrncs                     
       1601 | lamaze               | toys                          
       1700 | wait interface       | Books                         
       1666 | harry potter         | toys                          
(4 rows)

omm=# \dt mytest1 
                          List of relations
 Schema |  Name   | Type  | Owner |             Storage              
--------+---------+-------+-------+----------------------------------
 public | mytest1 | table | omm   | {orientation=row,compression=no}
(1 row)

omm=# 

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

--以数据库用户test1的身份,连接到刚刚创建的数据库enmdb：
gsql -d enmdb -U test1 -W Mygstest@1234 -r
或者
\c enmdb test1
--创建testchm1、testchm2模式
create schema testchm1;
create schema testchm2;
--创建普通表
create table mytest1(col1  char(100));
insert into mytest1 values('Hello from mytest1 IN SCHEMA public!');
select * from mytest1;
create table testchm1.mytest1(col1  char(100));
insert into testchm1.mytest1 values('Hello from mytest1 IN SCHEMA testchm1!');
select * from testchm1.mytest1;

omm@modb:~$ gsql -d enmdb -U test1 -W Mygstest@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 testchm1;
CREATE SCHEMA
enmdb=> create schema testchm2;
CREATE SCHEMA
enmdb=> create table mytest1(col1  char(100));
CREATE TABLE
enmdb=> insert into mytest1 values('Hello from mytest1 IN SCHEMA public!');
INSERT 0 1
enmdb=> select * from mytest1;
                                                 col1                                                 
------------------------------------------------------------------------------------------------------
 Hello from mytest1 IN SCHEMA public!                                                                
(1 row)

enmdb=> create table testchm1.mytest1(col1  char(100));
CREATE TABLE
enmdb=> insert into testchm1.mytest1 values('Hello from mytest1 IN SCHEMA testchm1!');
INSERT 0 1
enmdb=> select * from testchm1.mytest1;
                                                 col1                                                 
------------------------------------------------------------------------------------------------------
 Hello from mytest1 IN SCHEMA testchm1!                                                              
(1 row)

enmdb=> 

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

create schema enmschm1; --创建enmschm1模式
--查看当前的模式搜索顺序：
show SEARCH_PATH;
--在会话级重新设置模式搜索路径为模式enmschm1：
SET SEARCH_PATH TO enmschm1;
--再次查看当前的模式搜索顺序：
show SEARCH_PATH;
--在数据库enmdb的模式testchm2中创建表mytest1：
create table testchm2.mytest1(col1  char(100));
--并向模式testchm2中新创建的表mytest1，插入一行数据：
insert into testchm2.mytest1 values('Hello from mytest1 IN SCHEMA testchm2!');
--查看不同模式下的表
select * from public.mytest1;
select * from testchm1.mytest1;
select * from testchm2.mytest1;

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


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

enmdb=> create table testchm2.mytest1(col1  char(100));
CREATE TABLE
enmdb=> insert into testchm2.mytest1 values('Hello from mytest1 IN SCHEMA testchm2!');
INSERT 0 1
enmdb=> select * from public.mytest1;
                                                 col1                                                 
------------------------------------------------------------------------------------------------------
 Hello from mytest1 IN SCHEMA public!                                                                
(1 row)

enmdb=> select * from testchm1.mytest1;
                                                 col1                                                 
------------------------------------------------------------------------------------------------------
 Hello from mytest1 IN SCHEMA testchm1!                                                              
(1 row)

enmdb=> select * from testchm2.mytest1;
                                                 col1                                                 
------------------------------------------------------------------------------------------------------
 Hello from mytest1 IN SCHEMA testchm2!                                                              
(1 row)

enmdb=>