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

学习目标

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

学习内容

通过本章学习，了解了模式和表之间管理。不同模式可以创建同名的表。并且一个用户可以创建多个模式。可以通过模式.表访问表。

课程作业

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

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

omm=# create table t1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
omm=# \dt
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | t1   | table | omm   | {orientation=row,compression=no}
(1 row)

omm=#

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

omm=# create database enmdb;
ERROR:  database "enmdb" already exists
omm=# \l+
                                                          List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace |              
  Description                 
-----------+-------+----------+---------+-------+-------------------+-------+------------+--------------
------------------------------
 enmdb     | omm   | UTF8     | C       | C     |                   | 12 MB | enmtbs     | 
 omm       | omm   | UTF8     | C       | C     |                   | 12 MB | pg_default | 
 postgres  | omm   | UTF8     | C       | C     |                   | 14 MB | pg_default | default admin
istrative connection database
 template0 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | default templ
ate for new databases
 template1 | omm   | UTF8     | C       | C     | =c/omm           +| 12 MB | pg_default | unmodifiable 
(5 rows)
omm=# create user user5 with password 'test'@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user user5 with sysadmin;
ALTER ROLE
omm=# \du user5;
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 user5     | Sysadmin   | {}

omm=# 

enmdb=> show SEARCH_PATH;
  search_path   
----------------
 "$user",public
(1 row)
enmdb=> create schema test1;
CREATE SCHEMA
enmdb=> create schema test2;
CREATE SCHEMA
enmdb=> set search_path='test1';
SET
enmdb=> create table t1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
enmdb=> \dt t1; 
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 test1  | t1   | table | user5 | {orientation=row,compression=no}
(1 row)
enmdb=> set search_path='test2';
SET
enmdb=> show SEARCH_PATH;
enmdb=>  search_path 
-------------
 test2
(1 row)
enmdb=> create table t1(product_id integer,product_name char(20),category char(30));
CREATE TABLE
enmdb=> \dt t1;  
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 test2  | t1   | table | user5 | {orientation=row,compression=no}
(1 row)

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

omm=# set search_path='enmschm1';
SET
omm=# \d+
No relations found.
omm=# create table enmschm1.test1(a int);
ERROR:  schema "enmschm1" does not exist
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 user1           | user1
 user5           | user5
(12 rows)

omm=# create schema enmschm1;
CREATE SCHEMA
omm=# set search_path='enmschm1';
SET
omm=# \dt 
No relations found.
omm=# create table enmschm1.test1(a int);
CREATE TABLE
omm=# \dt
                          List of relations
  Schema  | Name  | Type  | Owner |             Storage              
----------+-------+-------+-------+----------------------------------
 enmschm1 | test1 | table | omm   | {orientation=row,compression=no}
(1 row)

omm=# insert into test1 values (1);
INSERT 0 1
omm=# insert into test1 values (1);
INSERT 0 1
omm=# insert into enmschm1.test1 values (1);
INSERT 0 1
omm=# select * from enmschm1.test1;
 a 
---
 1
 1
 1
(3 rows)