openGauss每日一练第6天|openGauss创建模式、修改模式属性和删除模式

学习

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

课后作业

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

omm=# CREATE SCHEMA tpcds;
CREATE SCHEMA

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

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            
-------------+-------+-------------------+----------------------------------
 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            
(7 rows)

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       | tim   |                   | 

3.重命名tpcds为tpcds1

omm=# ALTER SCHEMA tpcds RENAME TO tpcds1;
ALTER SCHEMA

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

omm=# CREATE TABLE tpcds1.customer
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
CREATE TABLE

omm=# INSERT INTO tpcds1.customer (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');omm-# omm-# omm-# omm-# 
INSERT 0 4
omm=# select * from tpcds1.customer;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(4 rows)

5.删除模式tpcds1

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