openGauss 每日一练第4天

基础环境准备

• 创建表空间

CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';

• 创建测试数据库

CREATE DATABASE musicdb WITH TABLESPACE = music_tbs;

1.创建用户user1、user2、user3，授予user1、user2、user3数据库系统的SYSADMIN权限

• 创建用户user1、user2、user3

#进入gsql
su - omm
gsql -r
# 创建用户
CREATE USER user1 IDENTIFIED BY 'abcd@1234';
CREATE USER user2 IDENTIFIED BY 'abcd@1234';
CREATE USER user3 IDENTIFIED BY 'abcd@1234';

• 赋予SYSADMIN权限

ALTER USER user1 SYSADMIN;
ALTER USER user2 SYSADMIN;
ALTER USER user3 SYSADMIN;

• 查看当前数据库中的用户

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 | {}
 user1     | Sysadmin                                                                                                         | {}
 user2     | Sysadmin                                                                                                         | {}
 user3     | Sysadmin                                                                                                         | {}

2.分别使用user1、user2、user3访问数据库musicdb2，创建各自的表，并插入数据。表名和数据如下：

表名分别为： products1、 products2、 products3

向表中插入数据：

• 在music_tbs 表空间上，创建musicdb2 数据库

  CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs ;
  
  

• 切换到 user1 用户下，创建各自的表，并插入数据

  # 切换到user1 用户，输入密码，连接
  musicdb2=# \c musicdb2 user1
  Password for user user1: 
  Non-SSL connection (SSL connection is recommended when requiring high-security)
  You are now connected to database "musicdb2" as user "user1".
  
  # 查看当前连接
  musicdb2=> \conninfo
  You are connected to database "musicdb2" as user "user1" via socket in "/tmp" at port "5432".
  

  创建表，并插入数据

  CREATE TABLE products1 (
      product_id INTEGER ,
      product_name  Char(20),
      category Char(30)
      );
  -- 备注信息需要使用comment on 进行添加
  comment on column products1.product_id is '产品编号';
  comment on column products1.product_name is '产品名';
  comment on column products1.category is '种类';
  -- 使用user1 用户插入数据
  INSERT into products1 values(1502,'olympus camera','electrncs');
  select * from products1;
  

• 按照上诉方法，切换到 user2、user3 用户，并插入数据

3.使用user1、user2、user3用户中的任何一个，查看当前数据库musicdb2有哪些表

使用user 1 查看数据库 musicdb2 中的表。

musicdb2=> \c musicdb2 user1
Password for user user1: 
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "musicdb2" as user "user1".

# 查看当前表
musicdb2=> \dt
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage              
--------+-----------+-------+-------+----------------------------------
 user1  | products1 | table | user1 | {orientation=row,compression=no}
(1 row)

#查看products1 表结构详情
musicdb2=> \d+ products1
                              Table "user1.products1"
    Column    |     Type      | Modifiers | Storage  | Stats target | Description  
--------------+---------------+-----------+----------+--------------+--------------
 product_id   | integer       |           | plain    |              | 产品编号
 product_name | character(20) |           | extended |              | 产品名
 category     | character(30) |           | extended |              | 种类
Has OIDs: no
Options: orientation=row, compression=no