openGauss每日一练第6天 | schema创建管理

root@modb:~# su - omm
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
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=# CREATE SCHEMA ds;
CREATE SCHEMA
omm=# \dn+ ds;
omm=# List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
ds | omm | |
(1 row)


omm=# create table ds.t1(id int, name char(30));
CREATE TABLE
omm=# insert into ds.t1 values(1 ,'xxxx');
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

omm=# ALTER SCHEMA ds RENAME TO ds_new;
omm=# select * from ds_new.t1;

id | name
----+--------------------------------
1 | xxxx
(1 row)

omm=# CREATE USER jack PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
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)

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.
级联删除
omm=# drop schema ds_new cascade;
NOTICE: drop cascades to table ds_new.t1
DROP SCHEMA

omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA
omm=# create user tim identified by 'gauss_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn+ tpcds
omm=# List of schemas
Name | Owner | Access privileges | Description
-------+-------+-------------------+-------------
tpcds | omm | |
(1 row)


omm=# alter schema tpcds OWNER to tim;
ALTER SCHEMA
omm=# \dn+ tpcds
List of schemas
Name | Owner | Access privileges | Description
-------+-------+-------------------+-------------
tpcds | tim | |
(1 row)
没有分号不生效
omm=# alter schema tpcds rename to tpcds1
没有varchar2类型
omm-# create table customer(c1 varchar2(10));
ERROR: syntax error at or near "create"
LINE 2: create table customer(c1 varchar2(10));
^
omm=# create table customer(c1 char(10));
CREATE TABLE
^
omm=# insert into customer(c1) values ('a');
INSERT 0 1
omm=# select *from tpcds1.customer;
ERROR: schema "tpcds1" does not exist
LINE 1: select *from tpcds1.customer;
这是因为建立的是public表，不是schema tpcds下的表 ^
omm=# \dn+ tpcds1
omm=# List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
(0 rows)


omm=# \dn+ tpcds
List of schemas
Name | Owner | Access privileges | Description
-------+-------+-------------------+-------------
tpcds | tim | |
(1 row)

omm=# alter schema tpcds rename to tpcds1;
ALTER SCHEMA
omm=# \dn+ tpcds1
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
tpcds1 | tim | |
(1 row)

omm=# drop table tpcds1.customer;
omm=# ERROR: table "customer" does not exist

omm=# select *from tpcds1.customer;
ERROR: relation "tpcds1.customer" does not exist on gaussdb
LINE 1: select *from tpcds1.customer;
^
omm=# \d+ tpcds1.customer;
Did not find any relation named "tpcds1.customer".
omm=# \d+ tpcds.customer;
Did not find any relation named "tpcds.customer".
omm=# \d+ customer;
Table "public.customer"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------------+-----------+----------+--------------+-------------
c1 | character(10) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no

omm=# drop table customer;
DROP TABLE
omm=# create table tpcds1.customer(c1 char(10));
CREATE TABLE
omm=# insert into tpcds1.customer(c1) values ('a');
INSERT 0 1
omm=# select *from tpcds1.customer;
c1
------------
a
(1 row)

omm=# drop table tpcds1.customer;
DROP TABLE
omm=# drop schema tpcds1;
DROP SCHEMA
omm=#