openGauss每日一练第6天

最新推荐文章于 2024-11-02 12:29:24 发布

hello openGauss

最新推荐文章于 2024-11-02 12:29:24 发布

阅读量211

点赞数 5

文章标签： oracle 数据库 openGauss

本文链接：https://blog.csdn.net/2401_83143557/article/details/137775681

版权

openGauss 每日一练第 6 天

本文出处：openGauss每日一练第六天 - 墨天轮

学习地址

每日一练：openGauss数据库在线实训课程 - 墨天轮课程

学习目标

学习 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=#