作业

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 'Yangbaji999';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter user john sysadmin;
ALTER ROLE	
--查看修改前模式信息

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

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,'yangkai');
INSERT 0 1
omm=# select * from t1;
id |         name         
----+----------------------
1 | yangkai             
(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.在会话级设置模式搜索顺序

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

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

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