目前还是基础操作阶段，主要是学习模式，用户的管理操作，具体如下：
1.登录环境
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~#
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
omm=# omm=#
2.创建模式
omm=# create schema ds;
CREATE SCHEMA
3.查看模式相关信息
omm=# \dn+ ds
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
ds | omm | |
(1 row)
4.创建模式的表，并插入记录
omm=# create table ds.t1(id int, name char(30));
insert into ds.t1 values(1 ,'xxxx');
CREATE TABLE
omm=# INSERT 0 1
查看记录
omm=# select * from ds.t1;
id | name
----+--------------------------------
1 | xxxx
(1 row)
查看表的相关信息
omm=# \d+ ds.t1;
Table "ds.t1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character(30) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
5.重命名模式ds为ds_new
omm=# ALTER SCHEMA ds RENAME TO ds_new;
ALTER SCHEMA
查看新模式名下的表的记录
omm=# select * from ds_new.t1;
id | name
----+--------------------------------
1 | xxxx
(1 row)
6.创建用户
omm=# CREATE USER jack PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
7.修改ds_new的owner为jack
omm=# ALTER SCHEMA ds_new OWNER TO jack;
ALTER SCHEMA
omm=# \dn+ ds_new;
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
ds_new | jack | |
(1 row)
8.删除schema
omm=# DROP schema ds_new;
ERROR: cannot drop schema ds_new because other objects depend on it
DETAIL: table ds_new.t1 depends on schema ds_new
HINT: Use DROP ... CASCADE to drop the dependent objects too.
==>模式下有对象无法直接删除，需要带上cascade关键字
omm=# drop schema ds_new cascade;
NOTICE: drop cascades to table ds_new.t1
omm=# DROP SCHEMA
删除用户jack
omm=# drop user jack;
DROP ROLE
omm=#
作业
omm=# create schema tpcds;
CREATE SCHEMA
omm=#
omm=# create user tim password 'abcd@123';
ERROR: role "tim" already exists
omm=# drop user tim;
omm=# DROP ROLE
omm=# create user tim password 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
omm=# -------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | omm | |
(7 rows)
omm=# alter schema tpcds owner to tim;
ALTER SCHEMA
omm=# \dn+
List of schemas
Name | Owner | Access privileges | Description
-------------+-------+-------------------+----------------------------------
cstore | omm | | reserved schema for DELTA tables
dbe_perf | omm | | dbe_perf schema
pkg_service | omm | | pkg_service schema
public | omm | omm=UC/omm +| standard public schema
| | =U/omm |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | tim | |
(7 rows)
omm=#
omm=# ALTER SCHEMA tpcds RENAME TO tpcds1;
ALTER SCHEMA
omm=# create table tpcds1.customer(id integer,name varchar(20));
CREATE TABLE
omm=# insert into tpcds1.customer values(1,'aaa');
INSERT 0 1
omm=# select * from tpcds1.customer;
id | name
----+------
1 | aaa
(1 row)
omm=# drop schema tpcds1 cascade;
NOTICE: drop cascades to table tpcds1.customer
DROP SCHEMA
omm=#
今天 的练习到此，结束 ！大家一起加油吧！
