简介

本实验指导书主要目的是为了让读者熟悉并掌握openGauss数据库的基本操作，并通过案例强化学习。

内容描述

本实验指导书先介绍创建管理用户、表及其它数据库对象等openGauss数据库的基本操作，再从学校数据模型的案例入手，进行模拟演练。

前置条件

openGauss数据库开发需要掌握openGauss数据库的基本操作和SQL语法，openGauss数据库支持SQL2003标准语法，数据库基本操作参见附录一。

实验环境说明

组网说明
本实验环境为华为云ECS服务器和openGauss数据库。
设备介绍
为了满足本实验需要，建议实验环境采用以下配置：
设备名称、型号与版本的对应关系如下：

设备明细表

实验概览

1 数据库开发实验

1.1 创建和管理用户、表空间和数据库

1.1.1 创建和管理用户

1.1.1.1 创建用户

通过CREATE USER创建的用户，默认具有LOGIN权限；
通过CREATE USER创建用户的同时系统会在执行该命令的数据库中，为该用户创建一个同名的SCHEMA；其他数据库中，则不自动创建同名的SCHEMA；用户可使用CREATE SCHEMA命令，分别在其他数据库中，为该用户创建同名SCHEMA。
系统管理员在普通用户同名schema下创建的对象，所有者为schema的同名用户（非系统管理员）。
创建用户jim，登录密码为Bigdata@123。

postgres=# CREATE USER jim PASSWORD 'Bigdata@123';
CREATE ROLE

同样的下面语句也可以创建用户。

postgres=# CREATE USER kim IDENTIFIED BY 'Bigdata@123';
CREATE ROLE

如果创建有“创建数据库”权限的用户，则需要加CREATEDB关键字。

postgres=# CREATE USER dim CREATEDB PASSWORD 'Bigdata@123';
CREATE ROLE

1.1.1.2 管理用户

将用户jim的登录密码由Bigdata@123修改为Abcd@123。

postgres=# ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'Bigdata@123';
ALTER ROLE

为用户jim追加CREATEROLE权限。

postgres=# ALTER USER jim CREATEROLE;
ALTER ROLE

将enable_seqscan的值设置为on，设置成功后，在下一会话中生效。

postgres=# ALTER USER jim SET enable_seqscan TO on;
ALTER ROLE

锁定jim帐户。

postgres=# ALTER USER jim ACCOUNT LOCK;
ALTER ROLE

删除用户。

postgres=# DROP USER kim CASCADE; 
DROP ROLE
postgres=# DROP USER jim CASCADE; 
DROP ROLE
postgres=# DROP USER dim CASCADE;
DROP ROLE

1.1.2 创建和管理表空间

1.1.2.1 创建表空间

步骤 1 执行如下命令创建用户jack。

postgres=# CREATE USER jack IDENTIFIED BY 'Bigdata@123';

当结果显示为如下信息，则表示创建成功。

CREATE ROLE

步骤 2 执行如下命令创建表空间。

postgres=# CREATE TABLESPACE fastspace RELATIVE LOCATION 'tablespace/tablespace_1';

当结果显示为如下信息，则表示创建成功。

CREATE TABLESPACE

其中“fastspace”为新创建的表空间，“数据库节点数据目录/pg_location/tablespace/tablespace_1”是用户拥有读写权限的空目录，如 /gaussdb/data/db1/pg_location/tablespace/tablespace_1 。

步骤 3 数据库系统管理员执行如下命令将“fastspace”表空间的访问权限赋予数据用户jack。

postgres=# GRANT CREATE ON TABLESPACE fastspace TO jack;

当结果显示为如下信息，则表示赋予成功。

GRANT

1.1.2.2 管理表空间

1.1.2.2.1 查询表空间

方式1：检查pg_tablespace系统表。如下命令可查到系统和用户定义的全部表空间。

postgres=# SELECT spcname FROM pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 fastspace
(3 rows)

方式2：使用gsql程序的元命令查询表空间。

postgres=# \db
             List of tablespaces
    Name    | Owner |        Location         
------------+-------+-------------------------
 fastspace  | omm   | tablespace/tablespace_1
 pg_default | omm   | 
 pg_global  | omm   | 
(3 rows)

1.1.2.2.2 查询表空间使用率

步骤 1 查询表空间的当前使用情况。

postgres=# SELECT PG_TABLESPACE_SIZE('fastspace');

返回如下信息：

pg_tablespace_size  
-------------------- 
            4096 
(1 row)

其中4096表示表空间的大小，单位为字节。
步骤 2 计算表空间使用率。
表空间使用率=PG_TABLESPACE_SIZE/表空间所在目录的磁盘大小。

1.1.2.2.3 修改表空间

执行如下命令对表空间fastspace重命名为fspace。

postgres=# ALTER TABLESPACE fastspace RENAME TO fspace;
ALTER TABLESPACE

1.1.2.2.4 删除表空间

执行如下命令删除用户jack。

postgres=# DROP USER jack CASCADE;
DROP ROLE

执行如下命令删除表空间fspace。

postgres=# DROP TABLESPACE fspace;
DROP TABLESPACE

说明：用户必须是表空间的owner或者系统管理员才能删除表空间。

1.1.3 创建和管理数据库

1.1.3.1 创建数据库

步骤 1 使用如下命令创建一个新的表空间tpcds_local。

postgres=# CREATE TABLESPACE tpcds_local RELATIVE LOCATION 'tablespace/tablespace_2';
CREATE TABLESPACE

步骤 2 使用如下命令创建一个新的数据库db_tpcc。

postgres=# CREATE DATABASE db_tpcc WITH TABLESPACE = tpcds_local;
CREATE DATABASE

1.1.3.2 管理数据库

1.1.3.2.1 查看数据库

使用\l元命令查看数据库系统的数据库列表（l表示list）。

postgres=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 db_tpcc   | 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)

使用如下命令通过系统表pg_database查询数据库列表。

postgres=# SELECT datname FROM pg_database;
  datname  
-----------
 template1
 db_tpcc
 template0
 postgres
(4 rows)

1.1.3.2.2 修改数据库

用户可以使用如下命令修改数据库属性（比如：owner、名称和默认的配置属性）。
使用以下命令为数据库设置默认的模式搜索路径。

postgres=# ALTER DATABASE db_tpcc SET search_path TO pa_catalog,public;
ALTER DATABASE

使用如下命令为数据库重新命名。

postgres=# ALTER DATABASE db_tpcc RENAME TO human_tpcds;
ALTER DATABASE

1.1.3.2.3 删除数据库

用户可以使用DROP DATABASE命令删除数据库。此命令删除了数据库中的系统目录，并且删除了带有数据的磁盘上的数据库目录。用户必须是数据库的owner或者系统管理员才能删除数据库。当有人连接数据库时，删除操作会失败。删除数据库时请先连接到其他的数据库。
使用如下命令删除数据库：

postgres=# DROP DATABASE human_tpcds;
DROP DATABASE

1.2 创建和管理表

1.2.1 创建表

表是建立在数据库中的，在不同的数据库中可以存放相同的表。甚至可以通过使用模式在同一个数据库中创建相同名称的表。

执行如下命令创建表。
postgres=# CREATE TABLE customer_t1 
( 
    c_customer_sk             integer, 
    c_customer_id             char(5), 
    c_first_name              char(6), 
    c_last_name               char(8) 
);

当结果显示为如下信息，则表示创建成功。

CREATE TABLE

其中c_customer_sk 、c_customer_id、c_first_name和c_last_name是表的字段名，integer、char(5)、char(6)和char(8)分别是这四字段名称的类型。

1.2.2 向表中插入数据

1.2.2.1 向表customer_t1中插入一行数据

数据值是按照这些字段在表中出现的顺序列出的，并且用逗号分隔。通常数据值是文本（常量），但也允许使用标量表达式。

postgres=# INSERT INTO customer_t1(c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', 'Grace');

如果用户已经知道表中字段的顺序，也可无需列出表中的字段。例如以下命令与上面的命令效果相同。

postgres=# INSERT INTO customer_t1 VALUES (3769, 'hello', 'Grace');

如果用户不知道所有字段的数值，可以忽略其中的一些。没有数值的字段将被填充为字段的缺省值。例如：

postgres=# INSERT INTO customer_t1 (c_customer_sk, c_first_name) VALUES (3769, 'Grace');

或

postgres=# INSERT INTO customer_t1 VALUES (3769, 'hello');

1.2.2.2 向表中插入多行数据

命令如下：

postgres=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES  
    (6885, 'maps', 'Joes'), 
    (4321, 'tpcds', 'Lily'), 
(9527, 'world', 'James');

如果需要向表中插入多条数据，除此命令外，也可以多次执行插入一行数据命令实现。但是建议使用此命令可以提升效率。

1.2.2.3 从指定表插入数据到当前表

如果从指定表插入数据到当前表，例如在数据库中创建了一个表customer_t1的备份表customer_t2，现在需要将表customer_t1中的数据插入到表customer_t2中，则可以执行如下命令。

postgres=# CREATE TABLE customer_t2 
( 
    c_customer_sk             integer, 
    c_customer_id             char(5), 
    c_first_name              char(6), 
    c_last_name               char(8) 
);

插入数据：

INSERT INTO customer_t2 SELECT * FROM customer_t1;

删除备份表：

postgres=# DROP TABLE customer_t2 CASCADE;
DROP TABLE

1.2.3 更新表中数据

修改已经存储在数据库中数据的行为叫做更新。用户可以更新单独一行，所有行或者指定的部分行。还可以独立更新每个字段，而其他字段则不受影响。
需要将表customer_t1中c_customer_sk为9527的字段重新定义为9876：

postgres=# UPDATE customer_t1 SET c_customer_sk = 9876 WHERE c_customer_sk = 9527;
UPDATE 1

这里的表名称也可以使用模式名修饰，否则会从默认的模式路径找到这个表。SET后面紧跟字段和新的字段值。新的字段值不仅可以是常量，也可以是变量表达式。
比如，把所有c_customer_sk的值增加100：

postgres=# UPDATE customer_t1 SET  c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421;

用户可以在一个UPDATE命令中更新更多的字段，方法是在SET子句中列出更多赋值，比如：

postgres=# UPDATE customer_t1 SET  c_customer_id = 'Admin', c_first_name = 'Local' WHERE c_customer_sk = 4421;

1.2.4 查看数据

使用系统表pg_tables查询数据库所有表的信息。

postgres=# SELECT * FROM pg_tables;

使用gsql的\d+命令查询表的结构。

postgres=# \d+ customer_t1;

执行如下命令查询表customer_t1的数据量。

postgres=# SELECT count(*) FROM customer_t1;

执行如下命令只查询字段c_customer_sk的数据。

postgres=# SELECT c_customer_sk FROM customer_t1;

执行如下命令过滤字段c_customer_sk的重复数据。

postgres=# SELECT DISTINCT( c_customer_sk ) FROM customer_t1;

执行如下命令查询字段c_customer_sk为3869的所有数据。

postgres=# SELECT * FROM customer_t1 WHERE c_customer_sk = 3869;

执行如下命令按照字段c_customer_sk进行排序。

postgres=# SELECT * FROM customer_t1 ORDER BY c_customer_sk;

执行如下命令查询ROWNUM伪列。

postgres=# SELECT rownum,c_customer_sk,c_customer_id FROM customer_t1;

执行如下命令使用别名进行查询(CNB、CSK、CID为列别名，T 为表别名)。

postgres=# SELECT rownum CNB,T.c_customer_sk CSK,T.c_customer_id CID FROM customer_t1 T;

1.2.5 删除表中数据

在使用表的过程中，可能会需要删除已过期的数据，删除数据必须从表中整行的删除。
使用DELETE命令删除行，如果删除表customer_t1中所有c_customer_sk为3869的记录：

postgres=# DELETE FROM customer_t1 WHERE c_customer_sk = 3869;

如果执行如下命令之一，会删除表中所有的行。

postgres=# DELETE FROM customer_t1;

或：

postgres=# TRUNCATE TABLE customer_t1;

全表删除的场景下，建议使用truncate，不建议使用delete。
删除创建的表：

postgres=# DROP TABLE customer_t1;

设备名称	设备型号	软件版本
数据库	openGauss	openGauss 1.1.0
操作系统	openEuler	openEuler 20.3LTS

简介

内容描述

前置条件

实验环境说明

实验概览

1 数据库开发实验

1.1 创建和管理用户、表空间和数据库

1.1.1 创建和管理用户

1.1.2 创建和管理表空间

1.1.3 创建和管理数据库

1.2 创建和管理表

1.2.1 创建表

1.2.2 向表中插入数据

1.2.3 更新表中数据

1.2.4 查看数据

1.2.5 删除表中数据

关于鲲鹏

新闻与活动

交流与资讯

支持与服务

开源社区