openGauss

day6 学习openGauss创建模式、修改模式属性和删除模式

学习内容 schema

1、创建模式(关键词：create)

create schema schema_name;

2、查看模式信息（关键词：\dn）

\dn+ schema_name;
\dn 	/* 查看所有模式信息 */

拓展
\du	/*查看用户信息，u -> user*/

3、在对应的模式中表格操作(关键字: .)

create table schema_name.table_name (id int, name char(20));	/*对应schema下建表*/
create table table_name( id int, name char(20)); 		/*一般情况下建表*/

insert into schema_name.table_name (id, name) values (234, '2dfhsa');	/*对应schema下插入数据*/
insert into table_name (id, name) values (234, '2dfhsa');		/*一般情况下插入数据*/

select * from schema_name.table_name;	/*对应schema下查看数据*/
select * from table_name;		/*一般情况下查看数据*/

4、修改模式的owner(关键字：owner to)

alter schema schema_name owner to user_name;

5、重命名模式(关键字：rename to)

alter schema schema_name1 rename to schema_name2;

6、删除模式(关键字：drop和cascade)

drop schema schema_name;

–当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字
drop schema schema_name CASCADE;
drop schema username;

cascade翻译: n. 大量; 小瀑布(尤指一连串瀑布中的一支); 倾泻; 流注; 大簇的下垂物;vi. 倾泻; 流注; 大量落下; 大量垂悬;

作业内容

1.创建一个名为tpcds的模式

create schema tpcds;

omm=# create schema tpcds;
CREATE SCHEMA
omm=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
 tpcds       | omm
(6 rows)

omm=# 

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

create user tim identified by 'sadfHsaUB23461NG348';
alter schema tpcds owner to tim

omm=# create user tim identified by 'asg&*354';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \dn+ tpcds;
-------+-------+-------------------+-------------
 tpcds | omm   |                   | 
(1 row)

omm=#                  List of schemas
 Name  | Owner | Access privileges | Description 

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

3.重命名tpcds为tpcds1

alter schema tpcds rename to tpcds1;

omm=# alter schema tpcds rename to tpcds1;
ALTER SCHEMA
omm=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
 tim         | tim
 tpcds1      | tim
(7 rows)

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

create table tpcds1.customer(
customer_id int,
customer_name char(30)
);
insert into tpcds1.customer (customer_id, customer_name) values (33621,'zhasian442'),(3965,'lxan');
select * from tpcds1.customer;

omm=# create table tpcds1.customer(
omm(# customer_id int,
omm(# customer_name char(30)
omm(# );
CREATE TABLE
omm=# insert into tpcds1.customer values (34,'zhangsan');
INSERT 0 1
omm=# insert into tpcds1.customer (customer_id, customer_name) values (3564,'zhangsan442');
INSERT 0 1
omm=# insert into tpcds1.customer (customer_id, customer_name) values (33621,'zhasian442'),(3965,'lxan');
INSERT 0 2
omm=# select * from tpcds1.customer;
 customer_id |         customer_name          
-------------+--------------------------------
          34 | zhangsan                      
        3564 | zhangsan442                   
       33621 | zhasian442                    
        3965 | lxan                          
(4 rows)

5.删除模式tpcds1

drop schema tpcds1;
drop schema tpcds1 cascade;

omm=# drop schema tpcds1;
ERROR:  cannot drop schema tpcds1 because other objects depend on it
DETAIL:  table tpcds1.customer depends on schema tpcds1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop schema tpcds1 cascade;
NOTICE:  drop cascades to table tpcds1.customer
DROP SCHEMA
omm=# drop user tim; 
DROP ROLE

omm=# \dn
   List of schemas
    Name     | Owner 
-------------+-------
 cstore      | omm
 dbe_perf    | omm
 pkg_service | omm
 public      | omm
 snapshot    | omm
(5 rows)

omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}