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

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

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

--连接openGuass数据库
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.

--创建测试用表空间enmtbs、数据库enmdb、用户user1，并授予用户user1 SYSADMIN权限
omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE
omm=# CREATE USER user1 with sysadmin IDENTIFIED BY 'test@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
omm=# CREATE ROLE

--数据库enmdb中，创建enmschm1、enmschm2模式
omm=# \c enmdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmdb" as user "user1".
enmdb=> create schema enmschm1;
CREATE SCHEMA
enmdb=> create schema enmschm2;
CREATE SCHEMA

--表默认被创建在SEARCH_PATH指定的第一个模式下，默认情况下，在某个数据库上创建的数据库对象，都位于该数据库中的与用户同名的模式中，但如果当前数据库中不存在这个模式，则将表存放在public模式中。
--查看默认搜索路径：
enmdb=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)
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)
--创建一个测试表testtable，并插入一条数据：
enmdb=> drop table if exists testtable;
NOTICE:  table "testtable" does not exist, skipping
DROP TABLE
enmdb=> create table testtable(col varchar(100));
CREATE TABLE
enmdb=> insert into testtable values('Hello from public.testtable!');
INSERT 0 1
enmdb=> select * from testtable;
             col              
------------------------------
 Hello from public.testtable!
(1 row)

enmdb=> \dt
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 public | testtable | table | user1 | {orientation=row,compression=no}
(1 row)
--在不同的模式（public和enmschm1），可以创建同名的表testtable
-- 修改SEARCH_PATH后，表默认被创建在SEARCH_PATH指定的第一个模式下。
enmdb=>  show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)
--执行下面的命令，在会话级重新设置模式搜索路径为模式enmschm1：
 enmdb=> SET SEARCH_PATH TO enmschm1;
SET
enmdb=>  show SEARCH_PATH;
 search_path 
-------------
 enmschm1
(1 row)

--在不同的模式（public和enmschm1），可以创建同名的表testtable：
enmdb=> create table testtable(col1  varchar(100));
CREATE TABLE
enmdb=> insert into testtable values('Hello from enmschm1.testtable!');
INSERT 0 1
enmdb=> select * from testtable; 
             col1                                                 
--------------------------------
 Hello from enmschm1.testtable!                                                                      
(1 row)

enmdb=> \dt 

                            List of relations
  Schema  |   Name    | Type  | Owner |             Storage              
----------+-----------+-------+-------+----------------------------------
 enmschm1 | testtable | table | user1 | {orientation=row,compression=no}
(1 row)

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

enmdb=> \c enmdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmdb" as user "user1".
enmdb=> select * from public.testtable; 
             col              
------------------------------
 Hello from public.testtable!
(1 row)

enmdb=> select * from enmschm1.testtable; 
            col1                                                 
---------------------------------
 Hello from enmschm1.testtable!                                                                      
(1 row)

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

enmdb=> \c enmdb user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "enmdb" as user "user1".
enmdb=> SET SEARCH_PATH TO enmschm1;
SET
show SEARCH_PATH;
enmdb=>  search_path 
-------------
 enmschm1
(1 row)

enmdb=> create table enmschm2.testtable(col2  varchar(100));
CREATE TABLE
enmdb=> insert into enmschm2.testtable values('Hello from enmschm1.testtable!');
INSERT 0 1
enmdb=> select * from enmschm2.testtable;
             col2                                                 
----------------------------------
 Hello from enmschm1.testtable!                                                                      
(1 row)