作业

1.环境准备

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 enmtbs RELATIVE LOCATION 'tablespace/enmtbs1';
CREATE TABLESPACE
omm=# CREATE DATABASE enmdb WITH TABLESPACE = enmtbs;
CREATE DATABASE

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

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

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

omm=# create user john identified by 'kunpeng@1234';

NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user john sysadmin;
ALTER ROLE
--查看修改前模式信息

\dn+

--修改模式对应的用户
omm=# alter schema testsm owner to john;
ALTER SCHEMA
--查看修改前后模式信息
\dn+

4.重命名testsm为testsm2

omm=# alter schema testsm rename to testsm2;
ALTER SCHEMA

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

omm=# SET SEARCH_PATH TO testsm1;
SET
omm=# create table t1(id int,name char(20));
CREATE TABLE
omm=# insert into t1 values(1,'zouyang');
INSERT 0 1
omm=# select * from t1;
id | name
----+----------------------
1 | zouyang
(1 row)

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

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

SET SEARCH_PATH TO schm1; 

 show SEARCH_PATH;

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

alter database enmdb set search_path to testsm1;

show SEARCH_PATH;

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

omm=# ALTER USER john SET SEARCH_PATH TO testsm1;
omm=# ALTER ROLE
omm=# \q