openGauss每日一练第6天

学习地址
https://www.modb.pro/course/133

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

模式是一组数据库对象的集合，主要用于控制对数据库对象的访问

课后作业
1.创建一个名为 tpcds 的模式
SQL文本：
create schema tpcds;
\dn tpcds

omm=# create schema tpcds;
CREATE SCHEMA
omm=# \dn tpcds
List of schemas
Name | Owner
-------+-------
tpcds | omm
(1 row)

omm-#
2.创建一个用户 tim, 并将 tpcds 的 owner 修改为 tim，且修改 owner 前后分别使用\dn+查看模式信息
SQL文本：
create user tim password '_*';
\dn+
alter schema tpcds owner to tim;
\dn+

omm=# create user tim password '_*';
omm=# 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 |
schema2 | omm | |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | omm | |
(8 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 |
schema2 | omm | |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds | tim | |
(8 rows)

omm=#

3.重命名 tpcds 为 tpcds1
SQL文本：
alter schema tpcds rename to tpcds1;
\dn+

omm=# alter schema tpcds rename to tpcds1;
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 |
schema2 | omm | |
snapshot | omm | | snapshot schema
tim | tim | |
tpcds1 | tim | |
(8 rows)

omm=#
4.在模式 tpcds1 中建表 customer、插入记录和查询记录
SQL文本：
建表
create table tpcds1.customer
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
);

插入记录
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');

查询记录
select * from tpcds1.customer;

omm=# create table tpcds1.customer
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# );
CREATE TABLE
omm=# INSERT INTO tpcds1.customer (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
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)

omm=# \dt customer
List of relations
Schema | Name | Type | Owner | Storage
--------+----------+-------+-------+----------------------------------
tpcds1 | customer | table | omm | {orientation=row,compression=no}
(1 row)

omm=#
5.删除模式 tpcds1
SQL文本： drop schema tpcds1; drop schema tpcds1 cascade; \dn+
omm=# drop schema tpcds1;
ERROR: cannot drop schema tpcds1 because other objects depend on it
DETAIL: table 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 customer
DROP 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 |
schema2 | omm | |
snapshot | omm | | snapshot schema
tim | tim | |
(7 rows)

omm=#