openGauss每日一练第 4 天 | 建立多个用户，新建表并插入数据

原创 Jeff 2022-11-28

516

课后作业
1.创建用户user1、user2、user3，授予user1、user2、user3数据库系统的SYSADMIN权限
2.分别使用user1、user2、user3访问数据库musicdb2，创建各自的表，并插入数据。表名和数据如下：
表名分别为： products1、 products2、 products3
字段名	数据类型	含义
product_id	INTEGER	产品编号
product_name	Char(20)	产品名
category	Char(30)	种类
向表中插入数据：
product_id	product_name	category
1502	olympus camera	electrncs
1601	lamaze	toys
1700	wait interface	Books
1666	harry potter	toys
3.使用user1、user2、user3用户中的任何一个，查看当前数据库musicdb2有哪些表

准备数据库及表空间

--进入数据库omm，创建表空间、测试数据库
openGauss-# drop DATABASE  IF EXISTS  musicdb;
drop DATABASE  IF EXISTS  musicdb1;
drop DATABASE  IF EXISTS  musicdb2;
drop DATABASE  IF EXISTS  musicdb3;
create database omm;
WARNING:  Session unused timeout.
FATAL:  terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
openGauss=# NOTICE:  database "musicdb1" does not exist, skipping
DROP DATABASE
openGauss=# NOTICE:  database "musicdb2" does not exist, skipping
DROP DATABASE
openGauss=# NOTICE:  database "musicdb3" does not exist, skipping
DROP DATABASE
openGauss=# CREATE DATABASE
openGauss=#
openGauss=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
 omm       | omm   | SQL_ASCII | C       | C     |
 postgres  | omm   | SQL_ASCII | C       | C     |
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(4 rows)

drop tablespace IF EXISTS music_tbs;
CREATE TABLESPACE music_tbs RELATIVE LOCATION 'tablespace/test_ts1';
CREATE DATABASE musicdb2 WITH TABLESPACE = music_tbs;

创建用户并授权sysadmin

--执行下面的SQL语句，创建用户user1、user2、user3：
 CREATE USER user1 IDENTIFIED BY 'kunpeng@1234';
 CREATE USER user2 IDENTIFIED BY 'kunpeng@1234';
 CREATE USER user3 IDENTIFIED BY 'kunpeng@1234';
 
--授予user1、user2、user3数据库系统的SYSADMIN权限：
ALTER USER user1 SYSADMIN;
ALTER USER user2 SYSADMIN;
ALTER USER user3 SYSADMIN;

--执行下面的命令，查看有哪些用户：
 \du

user1、2、3分别创建表products1、 products2、 products3，并插入数据

\c musicdb2 user1
CREATE TABLE products1
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products1 is 'user1测试表';
comment on COLUMN products1.product_id IS '产品编号';
comment on COLUMN products1.product_name IS '产品名';
comment on COLUMN products1.category IS '种类';

insert into products1 values(1502,'olympus camera','electrncs');
insert into products1 values(1601,'lamaze','toys');
insert into products1 values(1700,'wait interface','Books');
insert into products1 values(1666,'harry potter','toys');

\c musicdb2 user2
CREATE TABLE products2
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products2 is 'user2测试表';
comment on COLUMN products2.product_id IS '产品编号';
comment on COLUMN products2.product_name IS '产品名';
comment on COLUMN products2.category IS '种类';

insert into products2 values(1502,'olympus camera','electrncs');
insert into products2 values(1601,'lamaze','toys');
insert into products2 values(1700,'wait interface','Books');
insert into products2 values(1666,'harry potter','toys');

\c musicdb2 user3
CREATE TABLE products3
(
    product_id  integer,
    product_name char(20),
    category char(30)
);
comment on TABLE products3 is 'user3测试表';
comment on COLUMN products3.product_id IS '产品编号';
comment on COLUMN products3.product_name IS '产品名';
comment on COLUMN products3.category IS '种类';

insert into products3 values(1502,'olympus camera','electrncs');
insert into products3 values(1601,'lamaze','toys');
insert into products3 values(1700,'wait interface','Books');
insert into products3 values(1666,'harry potter','toys');

# omm 切换不需要密码~
musicdb2=> \c omm omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# \c postgres omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=#

openGauss=# \h comment
Command:     COMMENT
Description: define or change the comment of an object
Syntax:
COMMENT ON
{
  AGGREGATE agg_name (agg_type [, ...] ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  COLUMN { table_name.column_name | view_name.column_name } |
  CONSTRAINT constraint_name ON table_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  EXTENSION object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name ( [ {[ argmode ] [ argname ] argtype} [, ...] ] ) |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  ROLE object_name |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SERVER object_name |
  TABLE object_name |
  TABLESPACE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TYPE object_name |
  VIEW object_name
}
   IS 'text';

openGauss=#

插入数据并查询

查看建立的表

musicdb2=> \dt
                           List of relations
 Schema |   Name    | Type  | Owner |             Storage
--------+-----------+-------+-------+----------------------------------
 public | products1 | table | user1 | {orientation=row,compression=no}
 public | products2 | table | user2 | {orientation=row,compression=no}
 public | products3 | table | user3 | {orientation=row,compression=no}
(3 rows)

musicdb2=>