简介
本实验指导书主要目的是为了让读者熟悉并掌握openGauss数据库的基本操作，并通过案例强化学习。
内容描述
本实验指导书先介绍创建管理用户、表及其它数据库对象等openGauss数据库的基本操作，再从学校数据模型的案例入手，进行模拟演练。
前置条件
openGauss数据库开发需要掌握openGauss数据库的基本操作和SQL语法，openGauss数据库支持SQL2003标准语法，数据库基本操作参见附录一。
实验环境说明
●组网说明
本实验环境为华为云ECS服务器和openGauss数据库。
●设备介绍
为了满足本实验需要，建议实验环境采用以下配置：
设备名称、型号与版本的对应关系如下：
设备明细表
设备名称设备型号软件版本数据库openGaussopenGauss 1.1.0操作系统openEuleropenEuler 20.3LTS
实验概览
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');
用户也可以对独立的字段或者整个行明确缺省值：
postgres=# INSERT INTO customer_t1 (c_customer_sk, c_customer_id, c_first_name) VALUES (3769, 'hello', DEFAULT);
或
postgres=# INSERT INTO customer_t1 DEFAULT VALUES;
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_sk = c_customer_sk + 100;
用户可以在一个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;
执行如下命令查询表customer_t1的所有数据。
postgres=# SELECT * 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;
1.3 创建和管理其他数据库对象操作
1.3.1 创建和管理schema
1.3.1.1 创建schema
执行如下命令来创建一个schema。
postgres=# CREATE SCHEMA myschema;
当结果显示为如下信息，则表示成功创建一个名为myschema的schema。
CREATE SCHEMA
如果需要在模式中创建或者访问对象，其完整的对象名称由模式名称和具体的对象名称组成。中间由符号“.”隔开。例如：myschema.table。
执行如下命令在创建schema时指定owner。
postgres=# CREATE SCHEMA myschema AUTHORIZATION omm;
当结果显示为如下信息，则表示成功创建一个属于omm用户，名为myschema的schema。
CREATE SCHEMA
1.3.1.2 管理schema
1.3.1.2.1 使用schema
在特定schema下创建对象或者访问特定schema下的对象，需要使用有schema修饰的对象名。该名称包含schema名以及对象名，他们之间用“.”号分开。
执行如下命令在myschema下创建mytable表。
postgres=# CREATE TABLE myschema.mytable(id int, name varchar(20));
CREATE TABLE
如果在数据库中指定对象的位置，就需要使用有schema修饰的对象名称。
执行如下命令查询myschema下mytable表的所有数据。
postgres=# SELECT * FROM myschema.mytable; id| name
----+------
(0 rows)
1.3.1.2.2 schema的搜索路径
可以设置search_path配置参数指定寻找对象可用schema的顺序。在搜索路径列出的第一个schema会变成默认的schema。如果在创建对象时不指定schema，则会创建在默认的schema中。
执行如下命令查看搜索路径。
postgres=# SHOW SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
执行如下命令将搜索路径设置为myschema、public，首先搜索myschema。
postgres=# SET SEARCH_PATH TO myschema,public;
SET
1.3.1.2.3 schema的权限控制
默认情况下，用户只能访问属于自己的schema中的数据库对象。如果需要访问其他schema的对象，则该schema的所有者应该赋予他对该schema的usage权限。
通过将模式的CREATE权限授予某用户，被授权用户就可以在此模式中创建对象。注意默认情况下，所有角色都拥有在public模式上的USAGE权限，但是普通用户没有在public模式上的CREATE权限。普通用户能够连接到一个指定数据库并在它的public模式中创建对象是不安全的，如果普通用户具有在public模式上的CREATE权限，则建议通过如下语句撤销该权限。
撤销PUBLIC在public模式下创建对象的权限，下面语句中第一个“public”是模式，第二个“PUBLIC”指的是所有角色。
postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
使用以下命令查看现有的schema：
postgres=# SELECT current_schema();
current_schema
----------------
myschema
(1 row)
执行如下命令创建用户jack，并将myschema的usage权限赋给用户jack。
postgres=# CREATE USER jack IDENTIFIED BY 'Bigdata@123';
CREATE ROLE
postgres=# GRANT USAGE ON schema myschema TO jack;
GRANT
将用户jack对于myschema的usage权限收回。
postgres=# REVOKE USAGE ON schema myschema FROM jack;
REVOKE
1.3.1.2.4 删除schema
当schema为空时，即该schema下没有数据库对象，使用DROP SCHEMA命令进行删除。例如删除名为nullschema的空schema。
postgres=# DROP SCHEMA IF EXISTS nullschema;
NOTICE: schema "nullschema" does not exist, skipping
DROP SCHEMA
当schema非空时，如果要删除一个schema及其包含的所有对象，需要使用CASCADE关键字。例如删除myschema及该schema下的所有对象。
postgres=# DROP SCHEMA myschema CASCADE;
DROP SCHEMA
执行如下命令删除用户jack。
postgres=# DROP USER jack;
DROP ROLE
1.3.2 创建和管理分区表
1.3.2.1 创建分区表
步骤 1 创建schema。
CREATE SCHEMA tpcds;
步骤 2 创建表空间。
postgres=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1'; postgres=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2'; postgres=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3'; postgres=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
当结果显示为如下信息，则表示创建成功。
CREATE TABLESPACE
步骤 3 创建分区表并插入数据。
postgres=# CREATE TABLE tpcds.web_returns_p2 (
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20))
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
显示如下，表示创建成功。
CREATE TABLE
插入数据。
postgres=# insert into tpcds.web_returns_p2 values(1, 'a', 1, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 1.0, 'a'), (2, 'b', 2, 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 1.1, 'b'), (5050, 'c', 300, 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 1.2, 'c'), (14888, 'd', 400, 'd', 'd', 'd', 'd', 'd', 'd', 'd', 'd', 1.5, 'd');
插入数据返回如下：
INSERT 04
1.3.2.2 管理分区表
1.3.2.2.1 修改分区表行迁移属性
命令如下：
postgres=# ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
ALTER TABLE
1.3.2.2.2 删除分区
删除分区P8。
postgres=# ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
ALTER TABLE
1.3.2.2.3 增加分区
增加分区P8，范围为 40000<= P8<=MAXVALUE。
postgres=# ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
ALTER TABLE
1.3.2.2.4 重命名分区
重命名分区P8为P_9。
postgres=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
ALTER TABLE
重命名分区P_9为P8。
postgres=# ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
ALTER TABLE
1.3.2.2.5 修改分区的表空间
修改分区P6的表空间为example3。
postgres=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P6 TABLESPACE example3;
ALTER TABLE
修改分区P4的表空间为example4。
postgres=# ALTER TABLE tpcds.web_returns_p2 MOVE PARTITION P4 TABLESPACE example4;
ALTER TABLE
1.3.2.2.6 查询分区
查询分区P1。
postgres=# SELECT * FROM tpcds.web_returns_p2 PARTITION (P1);
1.3.2.2.7 删除分区表和表空间
命令如下
postgres=# DROP TABLE tpcds.web_returns_p2;
DROP TABLE
postgres=# DROP TABLESPACE example1; postgres=# DROP TABLESPACE example2; postgres=# DROP TABLESPACE example3;postgres=# DROP TABLESPACE example4;
DROP TABLESPACE
1.3.3 创建和管理索引
1.3.3.1 准备工作
步骤 1 创建表空间
postgres=# CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1'; postgres=# CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2'; postgres=# CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3'; postgres=# CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
当结果显示为如下信息，则表示创建成功。
CREATE TABLESPACE
步骤 2 创建表并在表中插入数据。
postgres=# CREATE TABLE tpcds.web_returns_p2 (
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20))
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
显示如下，表示创建成功。
CREATE TABLE
插入数据。
postgres=# insert into tpcds.web_returns_p2 values(1, 'a', 1, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 1.0, 'a'), (2, 'b', 2, 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 1.1, 'b'), (5050, 'c', 300, 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 1.2, 'c'), (14888, 'd', 400, 'd', 'd', 'd', 'd', 'd', 'd', 'd', 'd', 1.5, 'd');
1.3.3.2 创建索引
创建分区表索引tpcds_web_returns_p2_index1，不指定索引分区的名称。
postgres=# CREATE INDEX tpcds_web_returns_p2_index1 ON tpcds.web_returns_p2 (ca_address_id) LOCAL;
当结果显示为如下信息，则表示创建成功。
CREATE INDEX
创建分区索引tpcds_web_returns_p2_index2，并指定索引分区的名称。
postgres=# CREATE INDEX tpcds_web_returns_p2_index2 ON tpcds.web_returns_p2 (ca_address_sk) LOCAL (
PARTITION web_returns_p2_P1_index,
PARTITION web_returns_p2_P2_index TABLESPACE example3,
PARTITION web_returns_p2_P3_index TABLESPACE example4,
PARTITION web_returns_p2_P4_index,
PARTITION web_returns_p2_P5_index,
PARTITION web_returns_p2_P6_index,
PARTITION web_returns_p2_P7_index,
PARTITION web_returns_p2_P8_index
) TABLESPACE example2;
当结果显示为如下信息，则表示创建成功。
CREATE INDEX
1.3.3.3 管理索引
1.3.3.3.1 修改索引分区的表空间
修改索引分区web_returns_p2_P2_index的表空间为example1。
postgres=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P2_index TABLESPACE example1;
当结果显示为如下信息，则表示修改成功。
ALTER INDEX
修改索引分区web_returns_p2_P3_index的表空间为example2。
postgres=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 MOVE PARTITION web_returns_p2_P3_index TABLESPACE example2;
当结果显示为如下信息，则表示修改成功。
ALTER INDEX
1.3.3.3.2 重命名索引分区
执行如下命令对索引分区web_returns_p2_P8_index重命名web_returns_p2_P8_index_new。
postgres=# ALTER INDEX tpcds.tpcds_web_returns_p2_index2 RENAME PARTITION web_returns_p2_P8_index TO web_returns_p2_P8_index_new;
当结果显示为如下信息，则表示重命名成功。
ALTER INDEX
1.3.3.3.3 查询索引
执行如下命令查询系统和用户定义的所有索引。
postgres=# SELECT RELNAME FROM PG_CLASS WHERE RELKIND='i';
执行如下命令查询指定索引的信息。
postgres=# \di+
tpcds.tpcds_web_returns_p2_index1
1.3.3.3.4 删除索引
postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index1; postgres=# DROP INDEX tpcds.tpcds_web_returns_p2_index2;
当结果显示为如下信息，则表示删除成功。
DROP INDEX
1.3.3.4 索引创建举例
openGauss支持4种创建索引的方式：唯一索引、多字段索引、部分索引、表达式索引。
步骤 1 创建一个普通表。
postgres=# CREATE TABLE tpcds.customer_address_bak AS TABLE tpcds.web_returns_p2;
步骤 2 创建普通索引。
如果对于tpcds.customer_address_bak表，需要经常进行以下查询。
postgres=# SELECT ca_address_sk FROM tpcds.customer_address_bak WHERE ca_address_sk=14888;
使用以下命令创建索引。
postgres=# CREATE INDEX index_wr_returned_date_sk ON tpcds.customer_address_bak (ca_address_sk);
CREATE INDEX
步骤 3 创建多字段索引
假如用户需要经常查询表tpcds.customer_address_bak中ca_address_sk是5050，且ca_street_number小于1000的记录，使用以下命令进行查询。
postgres=# SELECT ca_address_sk,ca_address_id FROM tpcds.customer_address_bak WHERE ca_address_sk = 5050 AND ca_street_number < 1000;
使用以下命令在字段ca_address_sk和ca_street_number上定义一个多字段索引。
postgres=# CREATE INDEX more_column_index ON tpcds.customer_address_bak(ca_address_sk ,ca_street_number);
CREATE INDEX
步骤 4 创建部分索引
如果只需要查询ca_address_sk为5050的记录，可以创建部分索引来提升查询效率。
postgres=# CREATE INDEX part_index ON tpcds.customer_address_bak(ca_address_sk) WHERE ca_address_sk = 5050;
CREATE INDEX
步骤 5 创建表达式索引
假如经常需要查询ca_street_number小于1000的信息，执行如下命令进行查询。
postgres=# SELECT * FROM tpcds.customer_address_bak WHERE trunc(ca_street_number) < 1000;
可以为上面的查询创建表达式索引：
postgres=# CREATE INDEX para_index ON tpcds.customer_address_bak (trunc(ca_street_number));
CREATE INDEX
步骤 6 删除tpcds.customer_address_bak表。
postgres=# DROP TABLE tpcds.customer_address_bak;
DROP TABLE
1.3.4 创建和管理视图
1.3.4.1 创建视图
执行如下命令创建普通视图MyView。
postgres=# CREATE OR REPLACE VIEW MyView AS SELECT * FROM tpcds.web_returns_p2 WHERE trunc(ca_address_sk) > 10000;
CREATE VIEW
执行如下命令创建物化视图MV_MyView。
postgres=# CREATE MATERIALIZED VIEW MV_MyView AS SELECT * FROM tpcds.web_returns_p2 WHERE trunc(ca_address_sk) > 5000;
SELECT 2
物化视图使用场景：报表统计、大表统计等，定期固化数据快照， 避免对多表重复跑相同的查询。
物化视图使用注意事项：
不可以在临时表或全局临时表上创建。
当基表数据发生变化时，需要使用刷新命令保持物化视图与基表同步。
1.3.4.2 管理视图
1.3.4.2.1 查询普通视图
执行如下命令查询MyView视图。
postgres=# SELECT * FROM MyView;
1.3.4.2.2 查看普通图的具体信息
执行如下命令查询MyView视图的详细信息。
postgres=# \d+ Myview
View "public.myview"
Column | Type | Modifiers | Storage | Description
------------------+-----------------------+-----------+----------+-------------
ca_address_sk | integer || plain |
ca_address_id | character(16)|| extended |
ca_street_number | character(10)|| extended |
ca_street_name | character varying(60)|| extended |
ca_street_type | character(15)|| extended |
ca_suite_number | character(10)|| extended |
ca_city | character varying(60)|| extended |
ca_county | character varying(30)|| extended |
ca_state | character(2)|| extended |
ca_zip | character(10)|| extended |
ca_country | character varying(20)|| extended |
ca_gmt_offset | numeric(5,2)|| main |
ca_location_type | character(20)|| extended |
View definition:
SELECT *
FROM tpcds.web_returns_p2
WHERE trunc(web_returns_p2.ca_address_sk::double precision)>10000::double prec
ision;
1.3.4.2.3 查询物化视图
执行如下命令查询MV_MyView视图。
postgres=# SELECT * FROM MV_MyView;
ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | ca_state | ca_zip | ca_country | ca_gm
t_offset | ca_location_type
---------------+------------------+------------------+----------------+-----------------+-----------------+---------+-----------+----------+------------+------------+------
---------+----------------------
5050| c |300| c | c | c | c | c | c | c | c |1.20| c
14888| d |400| d | d | d | d | d | d | d | d |1.50| d
(2 rows)
1.3.4.2.4 查看物化图的具体信息
执行如下命令查询MyView视图的详细信息。
postgres=# \d+ MV_MyView
Materialized view "public.mv_myview"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------------------+-----------+----------+--------------+-------------
ca_address_sk | integer || plain ||
ca_address_id | character(16)|| extended ||
ca_street_number | character(10)|| extended ||
ca_street_name | character varying(60)|| extended ||
ca_street_type | character(15)|| extended ||
ca_suite_number | character(10)|| extended ||
ca_city | character varying(60)|| extended ||
ca_county | character varying(30)|| extended ||
ca_state | character(2)|| extended ||
ca_zip | character(10)|| extended ||
ca_country | character varying(20)|| extended ||
ca_gmt_offset | numeric(5,2)|| main ||
ca_location_type | character(20)|| extended ||
View definition:
SELECT *
FROM tpcds.web_returns_p2
WHERE trunc(web_returns_p2.ca_address_sk::double precision)>5000::double precision;
Replica Identity: NOTHING
Has OIDs: no
给基表新增二条记录，然后刷新物化视图。
postgres=# insert into tpcds.web_returns_p2 values (7050, 'c', 300, 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 1.2, 'c'), (8888, 'd', 400, 'd', 'd', 'd', 'd', 'd', 'd', 'd', 'd', 1.5, 'd');
INSERT 02
1.3.4.2.5 刷新物化图
由于基表数据变更过，可以执行如下命令刷新物化视图MV_MyView。
postgres=# REFRESH MATERIALIZED VIEW MV_MyView;
REFRESH MATERIALIZED VIEW
再查看物化视图MV_MyView，发现多了二条记录。
postgres=# SELECT * FROM MV_MyView;
ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | ca_state | ca_zip | ca_country | ca_gm
t_offset | ca_location_type
---------------+------------------+------------------+----------------+-----------------+-----------------+---------+-----------+----------+------------+------------+------
---------+----------------------
5050| c |300| c | c | c | c | c | c | c | c |1.20| c
7050| c |300| c | c | c | c | c | c | c | c |1.20| c
8888| d |400| d | d | d | d | d | d | d | d |1.50| d
14888| d |400| d | d | d | d | d | d | d | d |1.50| d
(4 rows)
1.3.4.2.6 删除视图
执行如下命令删除视图。
postgres=# DROP VIEW MyView;
DROP VIEW
postgres=# DROP MATERIALIZED VIEW MV_MyView;
DROP MATERIALIZED VIEW
1.3.5 创建和管理序列
1.3.5.1 方法一：声明字段类型为序列整型(serial)来定义标识符字段。
例如：
postgres=# CREATE TABLE T1 (id serial,
name text
);
当结果显示为如下信息，则表示创建成功。
CREATE TABLE
1.3.5.2 方法二：创建序列，并通过nextval(‘sequence_name’)函数指定为某一字段的默认值。
步骤 1 创建序列。
postgres=# CREATE SEQUENCE seq1 cache 100;
结果显示为如下信息，则表示创建成功。
CREATE SEQUENCE
步骤 2 指定为某一字段的默认值，使该字段具有唯一标识属性。
postgres=# CREATE TABLE T2 (id int not null default nextval('seq1'),
name text
);
当结果显示为如下信息，则表示默认值指定成功。
CREATE TABLE
步骤 3 指定序列与列的归属关系。
将序列和一个表的指定字段进行关联。删除此字段或其所在表的时候会自动删除已关联的序列。
postgres=# ALTER SEQUENCE seq1 OWNED BY T2.id;
当结果显示为如下信息，则表示指定成功。
ALTER SEQUENCE
1.3.5.3 删除序列
DROP SEQUENCE seq1 CASCADE;
1.3.6 创建和管理存储过程
1.3.6.1 创建存储过程
步骤 1 创建表t_test。
postgres=# create table t_test(c1 int, c2 int);
步骤 2 创建存储过程insert_data。
postgres=# create or replace procedure insert_data
is
a int;
b int;
begin
a=1;b=2;
insert into t_test values(a,b);
insert into t_test values(b,a);
end;
/
步骤 3 调用存储过程。
call insert_data();
步骤 4 查询表内容。
postgres=# select * from t_test;
c1 | c2
----+----
1|22|1(2 rows)
1.3.6.2 管理存储过程
管理存储过程，命令如下：
postgres=# \sf insert_data
结果如下：
CREATE OR REPLACE FUNCTION public.insert_data()
RETURNS void
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$ DECLARE
a int;
b int;
begin
a=1;b=2;
insert into t_test values(a,b);
insert into t_test values(b,a);
end$function$
删除存储过程，命令如下：
drop procedure insert_data;
1.3.7 创建和管理全局临时表
1.3.7.1 会话级全局临时表
数据会话级可见，其他会话看不到数据，但表结构可见。
步骤 1 创建临时表t_test2。
建表语句，使用ON COMMIT PRESERVE ROWS
postgres=# CREATE GLOBAL TEMPORARY TABLE t_test2(id integer,
lbl text
) ON COMMIT PRESERVE ROWS;
成功返回如下：
CREATE TABLE
步骤 2 在当前会话插入数据并查询。
postgres=# insert into t_test2 values(1,'data1');
INSERT 01postgres=# insert into t_test2 values(2,'data2');
INSERT 01postgres=# select * from t_test2;id| lbl
----+-------
1| data1
2| data2
(2 rows)
步骤 3 退出会话再查看。
postgres=# \q[omm@ecs-32de ~]$ gsql -d postgres -p 26000 -r
gsql ((openGauss 1.0.1 build 13b34b53) compiled at 2020-10-12 02:01:33 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help"for help.
postgres=# \dt t_test2
List of relations
Schema | Name | Type | Owner | Storage
--------+---------+-------+-------+--------------------------------------------------------------
public | t_test2 | table | omm |{orientation=row,compression=no,on_commit_delete_rows=false}(1 row)
步骤 4 查询表内容。
postgres=# select * from t_test2;id| lbl
----+-----
(0 rows)
此时可以发现，在其它会话中表结构可以看到，但是表数据看不到。
步骤 5 删除临时表。
postgres=# drop table t_test2;
DROP TABLE
1.3.7.2 事务级全局临时表
数据事务级可见，事务提交后数据删除。
步骤 1 创建临时表t_test3。
建表语句，使用ON COMMIT DELETE ROWS
postgres=# CREATE GLOBAL TEMPORARY TABLE t_test3(id integer,
lbl text
) ON COMMIT DELETE ROWS;
CREATE TABLE
步骤 2 插入数据并查询。
先用begin开始一个事务，接着给表插入数据，此时再对表进行查询，可以查出相应数据。
postgres=# begin;
BEGIN
postgres=# insert into t_test3 values(1,'data1');
INSERT 01postgres=# select * from t_test3;id| lbl
----+-------
1| data1
(1 row)
步骤 3 结束事务再查询。
先用commit提交来结束事务，此时再对表进行查询，可以发现已经查询不出数据了。
postgres=# commit;
COMMIT
postgres=# select * from t_test3;id| lbl
----+-----
(0 rows)(1 row)
步骤 4 删除临时表。
postgres=# drop table t_test3;
DROP TABLE
数据库开发实验中创建和管理用户、表空间和数据库、创建表、创建和管理schema就为大家介绍到这里了，
下一篇将介绍学校数据模型以及学校数据模型表操作。
版权归原作者 Gauss松鼠会 所有， 如有侵权，请联系我们删除。