第12天 | openGauss逻辑结构：模式管理

接着昨天继续学习openGauss,今天是第12天了。今天学习内容是openGauss逻辑结构：模式管理。
老规矩，先登陆墨天轮为我准备的实训实验室

root@modb:~# su - omm
omm@modb:~$ gsql -r

–执行如下的命令和SQL语句，创建表空间enmtbs和数据库enmdb：

omm=# CREATE TABLESPACE enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE

–创建数据库用户user1的同时，会在系统的omm数据库中创建一个与这个用户名同名的模式user1。

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

作业要求

1.创建一个名为testsm、testsm1的模式

omm=# create schema testsm;
CREATE SCHEMA
omm=# create schema testsm1;
omm=# CREATE SCHEMA

2.创建一个用户john, 并将testsm的owner修改为john，且修改owner前后分别使用\dn+查看模式信息

omm=# CREATE USER john IDENTIFIED BY 'john@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1  SYSADMIN;
ALTER ROLE

修改前记录

omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 john            | john
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm          | omm
 testsm1         | omm
 user1           | user1
(14 rows)

将testsm的owner修改为john,并查看变化后的模式

omm=# ALTER SCHEMA testsm OWNER TO john;
ALTER SCHEMA
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 john            | john
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm          | john
 testsm1         | omm
 user1           | user1
(14 rows)

3.重命名testsm为testsm2

omm=# alter schema testsm rename to testsm2;
omm=# ALTER SCHEMA
omm=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 john            | john
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
 testsm1         | omm
 testsm2         | john
 user1           | user1
(14 rows)

4.在模式testsm1中建表t1、插入记录和查询记录

omm=# create table testsm1.t1(product_id INTEGER,product_name Char(20),category Char(10));
CREATE TABLE
omm-# insert into testsm1.t1(product_id,product_name,category) values
(1700,'wait interface','Books'),
omm-# (1666,'harry potter','toys');
INSERT 0 3
select * from testsm1.t1;
omm=# select * from testsm1.t1;
 product_id |     product_name     |  category  
------------+----------------------+------------
       1601 | lamaze               | toys      
       1700 | wait interface       | Books     
       1666 | harry potter         | toys      
       1601 | lamaze               | toys      
       1700 | wait interface       | Books     
omm=#        1666 | harry potter         | toys   

5.在会话级设置模式搜索顺序

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

omm=# SET SEARCH_PATH TO testsm1;
SET
omm=# show SEARCH_PATH;
 search_path 
-------------
 testsm1

6.在数据库级设置模式搜索顺序

omm=# ALTER DATABASE enmdb SET SEARCH_PATH TO testsm1;
ALTER DATABASE
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=> show SEARCH_PATH;
 search_path 
-------------
 testsm1

7.在用户级设置模式搜索顺序

enmdb=> ALTER USER user1 SET SEARCH_PATH TO schm2;
ALTER ROLE
enmdb=> \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 
-------------
 schm2
(1 row)

总结

今天练习比较深了点，课后还需要多多练习。