十四、openGauss基本使用

时间:2020-12-28 00:54:40

一、连接数据库

1、确认连接信息

gsql是openGauss自带的客户端工具。使用gsql连接数据库,可以交互式地输入、编辑、执行SQL语句。
gsql客户端工具通过数据库主节点连接数据库。因此连接前需获取数据库主节点所在服务器的IP地址及数据库主节点的端口号信息。
操作步骤:
(1) 以操作系统用户omm登录数据库主节点。
(2) 使用"gs_om -t status --detail"命令查询openGauss各实例情况。
[ Cluster State ]
cluster_state : Normal
redistributing : No
current_az : AZ_ALL
[ Datanode State ]

node node_ip port instance state
-----------------------------------------------------------------------------------------------
1 gsdb01 192.168.10.131 26000 6001 /opt/huawei/install/data/dn01 P Primary Normal

如上所示,部署了数据库主节点实例的服务器IP地址为192.168.10.131。数据库主节点数据路径"/opt/huawei/install/data/dn01"。
(3) 确认数据库主节点的端口号。
[omm@gsdb01 ~]$ cat /opt/huawei/install/data/dn01/postgresql.conf|grep ^port
port = 26000 # (change requires restart)

2、使用gsql本地连接

gsql是openGauss提供的在命令行下运行的数据库连接工具。此工具除了具备操作数据库的基本功能,还提供了若干高级特性,便于用户使用。
本节只介绍如何使用gsql本地连接数据库。

1) 注意事项:
缺省情况下,客户端连接数据库后处于空闲状态时会根据参数“session_timeout”的默认值自动断开连接。如果要关闭超时设置,设置参数“session_timeout”为0即可。
2) 前提条件:
已确认连接信息,获取数据库主节点端口号信息。
3) 操作步骤:
(1)以操作系统用户omm登录数据库主节点。
(2)连接数据库。数据库安装完成后默认生成名称为postgres的数据库,第一次连接数据库时可以连接到此数据库。执行如下命令连接数据库。
gsql -d postgres -p 26000
其中postgres为需要连接的数据库名称,26000为数据库主节点的端口号。请根据实际情况替换。
连接成功后,系统显示类似如下信息。
[omm@gsdb01 ~]$ gsql -d postgres -p 26000
gsql ((openGauss 3.1.1 build 70980198) compiled at 2023-01-06 09:27:09 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#

omm用户是管理员用户,因此系统显示“postgres=#”。若使用普通用户身份登录和连接数据库,系统显示"postgres=>"。
"Non-SSL connection"表示未使用SSL方式连接数据库。如果需要高安全性时,请使用SSL连接。

(3)首次登录更改一下密码。
原始密码为安装openGauss数据库时手动输入的密码,此处需将原始密码修改为自定义的密码,例如Mysql@123,命令如下。
openGauss=# alter role omm identified by 'Mysql@123' replace 'IPTrace@123';
ALTER ROLE

(4) 退出数据库。
openGauss=# \q

二、使用数据库

1、从这里开始

1、登录
以操作系统用户omm登录数据库主节点。
gsql -d postgres -p 26000

2、创建数据库用户
openGauss=# create user joe with password 'Mysql@123';
CREATE ROLE

3、创建数据库
openGauss=# create database db_test owner joe;
CREATE DATABASE
#db_test数据库创建完成后,就可以按如下方法退出postgres数据库,使用新用户joe连接db_test数据库执行创建表等操作。
#您也可以选择继续在默认的postgres数据库下进行后续的体验

openGauss=# \q
[omm@gsdb01 ~]$ gsql -d db_test -p 26000 -U joe -W Mysql@123 -r
gsql ((openGauss 3.1.1 build 70980198) compiled at 2023-01-06 09:27:09 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db_test=>

4、创建schema
db_test=> create schema AUTHORIZATION joe;
CREATE SCHEMA

5、创建表
db_test=> create table mytable (firstcol int);
CREATE TABLE
插入数据
db_test=> insert into mytable values (100);
INSERT 0 1
查看表中数据
db_test=> select * from mytable;
firstcol
----------
100
(1 row)

2、简单数据库管理

1) 创建数据库

参考地址
https://gitee.com/opengauss/docs/blob/master/content/zh/docs/Developerguide/CREATE-DATABASE.md

创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建,且仅支持使用template0来创建。
注意事项
只有拥有CREATEDB权限的用户才可以创建新数据库,系统管理员默认拥有此权限。
不能在事务块中执行创建数据库语句。
在创建数据库过程中,出现类似“Permission denied”的错误提示,可能是由于文件系统上数据目录的权限不足。出现类似“No space left on device”
的错误提示,可能是由于磁盘满引起的。

语法格式
CREATE DATABASE [IF NOT EXISTS] database_name
[ [ WITH ] { [ OWNER [=] user_name ] |
[ TEMPLATE [=] template ] |
[ ENCODING [=] encoding ] |
[ LC_COLLATE [=] lc_collate ] |
[ LC_CTYPE [=] lc_ctype ] |
[ DBCOMPATIBILITY [=] compatibilty_type ] |
[ TABLESPACE [=] tablespace_name ] |
[ CONNECTION LIMIT [=] connlimit ]}[...] ];

示例
--创建jim和tom用户。
openGauss=# CREATE USER jim PASSWORD 'xxxxxxxxx';
openGauss=# CREATE USER tom PASSWORD 'xxxxxxxxx';
--创建一个GBK编码的数据库music(本地环境的编码格式必须也为GBK)。
openGauss=# CREATE DATABASE music ENCODING 'GBK' template = template0;
--创建数据库music2,并指定所有者为jim。
openGauss=# CREATE DATABASE music2 OWNER jim;
--用模板template0创建数据库music3,并指定所有者为jim。
openGauss=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;
--设置music数据库的连接数为10。
openGauss=# ALTER DATABASE music CONNECTION LIMIT= 10;
--将music名称改为music4。
openGauss=# ALTER DATABASE music RENAME TO music4;
--将数据库music2的所属者改为tom。
openGauss=# ALTER DATABASE music2 OWNER TO tom;
--设置music3的表空间为PG_DEFAULT。
openGauss=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;
--关闭在数据库music3上缺省的索引扫描。
openGauss=# ALTER DATABASE music3 SET enable_indexscan TO off;
--重置enable_indexscan参数。
openGauss=# ALTER DATABASE music3 RESET enable_indexscan;
--删除数据库。
openGauss=# DROP DATABASE music2;
openGauss=# DROP DATABASE music3;
openGauss=# DROP DATABASE music4;
--删除jim和tom用户。
openGauss=# DROP USER jim;
openGauss=# DROP USER tom;
--创建兼容TD格式的数据库。
openGauss=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'C';
--创建兼容A格式的数据库。
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';
--删除兼容TD、A格式的数据库。
openGauss=# DROP DATABASE td_compatible_db;
openGauss=# DROP DATABASE ora_compatible_db;

2) 创建表

参考地址:
https://gitee.com/opengauss/docs/blob/master/content/zh/docs/Developerguide/CREATE-TABLE.md

功能描述
在当前数据库中创建一个新的空白表,该表由命令执行者所有。

注意事项
列存表支持的数据类型请参考列存表支持的数据类型。
列存表不支持数组。
列存表不支持生成列。
列存表不支持创建全局临时表。
创建列存表的数量建议不超过1000个。
如果在建表过程中数据库系统发生故障,系统恢复后可能无法自动清除之前已创建的、大小为0的磁盘文件。此种情况出现概率小,不影响数据库系统的正常运行。
列存表的表级约束只支持PARTIAL CLUSTER KEY、UNIQUE、PRIAMRY KEY,不支持外键等表级约束。
列存表的字段约束只支持NULL、NOT NULL、DEFAULT常量值、UNIQUE和PRIMARY KEY。
列存表支持delta表,受参数enable_delta_store控制是否开启,受参数deltarow_threshold控制进入delta表的阀值。
使用JDBC时,支持通过PrepareStatement对DEFAULT值进行参数化设置。
每张表的列数最大为1600,具体取决于列的类型,所有列的大小加起来不能超过8192 byte(由于数据存储形式原因,实际上限略小于8192 byte),text、varchar、char等长度可变的类型除外。
被授予CREATE ANY TABLE权限的用户,可以在public模式和用户模式下创建表。如果想要创建包含serial类型列的表,还需要授予CREATE ANY SEQUENCE创建序列的权限。
不可与同一模式下已存在的synonym产生命名冲突。
仅支持在B兼容性数据库下指定COMMENT和可见性VISIBLE\INVISIBLE。

语法格式
创建表。
CREATE [ [ GLOBAL | LOCAL ] [ TEMPORARY | TEMP ] | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
({ column_name data_type [ CHARACTER SET | CHARSET charset ] [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option [...] ] }
[, ... ])
[ AUTO_INCREMENT [ = ] value ]
[ [DEFAULT] CHARACTER SET | CHARSET [ = ] default_charset ] [ [DEFAULT] COLLATE [ = ] default_collation ]
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
[ COMMENT {=| } 'text' ];

其中列约束column_constraint为:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) |
DEFAULT default_expr |
GENERATED ALWAYS AS ( generation_expr ) [STORED] |
AUTO_INCREMENT |
ON UPDATE update_expr |
UNIQUE [KEY] index_parameters |
ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = column_encryption_key, ENCRYPTION_TYPE = encryption_type_value ) |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ COMMENT {=| } 'text' ]

其中列的压缩可选项compress_mode为:
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }

其中表约束table_constraint为:
[ CONSTRAINT [ constraint_name ] ]
{ CHECK ( expression ) |
UNIQUE [ index_name ][ USING method ] ( { { column_name | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] |
PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters [ VISIBLE | INVISIBLE ] |
FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ (refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] |
PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ COMMENT {=| } 'text' ]

其中like选项like_option为:
{ INCLUDING | EXCLUDING } { DEFAULTS | GENERATED | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }

其中索引参数index_parameters为:
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

示例
--创建普通表空间。
openGauss=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION 'tablespace/tablespace_1';
--创建表时,指定表空间。
openGauss=# CREATE TABLE tpcds.warehouse_t9
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
) TABLESPACE DS_TABLESPACE1;

--向tpcds.warehouse_t19表中增加一个varchar列。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);
--给tpcds.warehouse_t19表增加一个检查约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);
--在一个操作中改变两个现存字段的类型。
openGauss=# ALTER TABLE tpcds.warehouse_t19
ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),
ALTER COLUMN W_STREET_NAME TYPE varchar(100);
--此语句与上面语句等效。
openGauss=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));
--给一个已存在字段添加非空约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;
--移除已存在字段的非空约束。
openGauss=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;
--如果列存表中还未指定局部聚簇,向在一个列存表中添加局部聚簇列。
openGauss=# ALTER TABLE tpcds.warehouse_t17 ADD PARTIAL CLUSTER KEY(W_WAREHOUSE_SK);
--查看约束的名称,并删除一个列存表中的局部聚簇列。
openGauss=# \d+ tpcds.warehouse_t17
Table "tpcds.warehouse_t17"
Column | Type | Modifiers | Storage | Stats target | Description
-------------------+-----------------------+-----------+----------+--------------+-------------
w_warehouse_sk | integer | not null | plain | |
w_warehouse_id | character(16) | not null | extended | |
w_warehouse_name | character varying(20) | | extended | |
w_warehouse_sq_ft | integer | | plain | |
w_street_number | character(10) | | extended | |
w_street_name | character varying(60) | | extended | |
w_street_type | character(15) | | extended | |
w_suite_number | character(10) | | extended | |
w_city | character varying(60) | | extended | |
w_county | character varying(30) | | extended | |
w_state | character(2) | | extended | |
w_zip | character(10) | | extended | |
w_country | character varying(20) | | extended | |
w_gmt_offset | numeric(5,2) | | main | |
Partial Cluster :
"warehouse_t17_cluster" PARTIAL CLUSTER KEY (w_warehouse_sk)
Has OIDs: no
Location Nodes: ALL DATANODES
Options: compression=no, version=0.12
openGauss=# ALTER TABLE tpcds.warehouse_t17 DROP CONSTRAINT warehouse_t17_cluster;
--将表移动到另一个表空间。
openGauss=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;
--创建模式joe。
openGauss=# CREATE SCHEMA joe;
--将表移动到另一个模式中。
openGauss=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;
--重命名已存在的表。
openGauss=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;
--从warehouse_t23表中删除一个字段。
openGauss=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;
--删除表空间、模式joe和模式表warehouse
openGauss=# DROP TABLE tpcds.warehouse_t22;
openGauss=# DROP TABLE joe.warehouse_t23;
openGauss=# DROP TABLESPACE DS_TABLESPACE1;
openGauss=# DROP SCHEMA IF EXISTS joe CASCADE;

3) 查询数据

参考地址
https://gitee.com/opengauss/docs/blob/master/content/zh/docs/Developerguide/SELECT.md

功能描述
SELECT用于从表或视图中取出数据。
SELECT语句就像叠加在数据库表上的过滤器,利用SQL关键字从数据表中过滤出用户需要的数据。

注意事项
必须对每个在SELECT命令中使用的字段有SELECT权限。
使用FOR UPDATE,FOR NO KEY UPDATE,FOR SHARE或FOR KEY SHARE还要求UPDATE权限。

语法格式
查询数据
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ into_option ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition [, ...] ]
[ WINDOW {window_name AS ( window_definition )} [, ...] ]
[ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
[ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT { [offset,] count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ into_option ]
[ {FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | WAIT N]} [...] ]
[ into_option ];

其中子查询with_query为:
with_query_name [ ( column_name [, ...] ) ]
AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )

其中into字句为:
into_option: {
INTO var_name [, var_name] ...
| INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
}
export_options: {
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
}

其中指定查询源from_item为:
{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
[TIMECAPSULE {TIMESTAMP|CSN} expression]
|( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
|with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
|function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
|function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
|from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

其中group子句为:
( )
| expression
| ( expression [, ...] )
| ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
| CUBE ( { expression | ( expression [, ...] ) } [, ...] )
| GROUPING SETS ( grouping_element [, ...] )

其中指定分区partition_clause为:
PARTITION { ( partition_name ) |
FOR ( partition_value [, ...] ) }
说明: 指定分区只适合分区表。

其中设置排序方式nlssort_expression_clause为:
NLSSORT ( column_name, ' NLS_SORT = { SCHINESE_PINYIN_M | generic_m_ci } ' )
其中,第二个参数可选generic_m_ci,仅支持纯英文不区分大小写排序。

简化版查询语法,功能相当于select * from table_name。
TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

示例
--查询tpcds.reason表的所有r_reason_sk记录,且去除重复。
openGauss=# SELECT DISTINCT(r_reason_sk) FROM tpcds.reason;
--LIMIT子句示例:获取表中一条记录。
openGauss=# SELECT * FROM tpcds.reason LIMIT 1;
--查询所有记录,且按字母升序排列。
openGauss=# SELECT r_reason_desc FROM tpcds.reason ORDER BY r_reason_desc;
--通过表别名,从pg_user和pg_user_status这两张表中获取数据。
openGauss=# SELECT a.usename,b.locktime FROM pg_user a,pg_user_status b WHERE a.usesysid=b.roloid;
--FULL JOIN子句示例:将pg_user和pg_user_status这两张表的数据进行全连接显示,即数据的合集。
openGauss=# SELECT a.usename,b.locktime,a.usesuper FROM pg_user a FULL JOIN pg_user_status b on a.usesysid=b.roloid;
--GROUP BY子句示例:根据查询条件过滤,并对结果进行分组。
openGauss=# SELECT r_reason_id, AVG(r_reason_sk) FROM tpcds.reason GROUP BY r_reason_id HAVING AVG(r_reason_sk) > 25;
--GROUP BY CUBE子句示例:根据查询条件过滤,并对结果进行分组汇总。
openGauss=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY CUBE(r_reason_id,r_reason_sk);
--GROUP BY GROUPING SETS子句示例:根据查询条件过滤,并对结果进行分组汇总。
openGauss=# SELECT r_reason_id,AVG(r_reason_sk) FROM tpcds.reason GROUP BY GROUPING SETS((r_reason_id,r_reason_sk),r_reason_sk);
--UNION子句示例:将表tpcds.reason里r_reason_desc字段中的内容以W开头和以N开头的进行合并。
openGauss=# SELECT r_reason_sk, tpcds.reason.r_reason_desc
FROM tpcds.reason
WHERE tpcds.reason.r_reason_desc LIKE 'W%'
UNION
SELECT r_reason_sk, tpcds.reason.r_reason_desc
FROM tpcds.reason
WHERE tpcds.reason.r_reason_desc LIKE 'N%';