openGauss数据库源码解析系列文章——openGauss开发快速入门（二）

在上一篇openGauss数据库源码解析系列文章——openGauss开发快速入门（上）中，我们介绍了openGauss的安装部署方法，本篇将具体介绍openGauss基本使用。

二、 openGauss基本使用

openGauss安装完成后会自动启动，用户可以使用openGauss自带的gsql（openGauss交互终端）客户端连接数据库进行相关SQL操作。

（一） 连接数据库

本篇为您介绍如何使用gsql客户端连接数据库。gsql是openGauss自带的客户端工具。使用gsql连接数据库，可以交互式地输入、编辑、执行SQL语句。

1. 确认连接信息

gsql客户端工具通过数据库主节点连接数据库。因此连接前需获取数据库主节点所在服务器的IP地址及数据库主节点的端口号信息。

操作步骤：

（1） 以操作系统用户omm登录数据库主节点。

（2） 使用“gs_om -t status --detail”命令查询openGauss各实例情况。

gs_om -t status --detail

查询结果如下。

[ DBnode State ]
node        node_ip         instance                         state
-----------------------------------------------------------------------------1
  plat1 192.168.0.11  5001 /srv/BigData/gaussdb/data1/dbnode Normal

如上所示，部署了数据库主节点实例的服务器IP地址为192.168.10.11。数据库主节点数据路径“/srv/BigData/gaussdb/data1/dbnode”。

（3） 确认数据库主节点的端口号。

在（2）中查到的数据库主节点数据路径下的postgresql.conf文件中查看端口号信息。示例如下。

cat /srv/BigData/gaussdb/data1/dbnode/postgresql.conf | grep port

查询结果如下，8000为数据库主节点的端口号。

port = 8000    # (change requires restart)
#comm_sctp_port = 1024   # Assigned by installation (change requires restart)
#comm_control_port = 10001  # Assigned by installation (change requires restart)
      # supported by the operating system:
      # e.g. 'localhost=10.145.130.2 localport=12211 remotehost=10.145.130.3 remoteport=12212, localhost=10.145.133.2 localport=12213 remotehost=10.145.133.3 remoteport=12214'
      # e.g. 'localhost=10.145.130.2 localport=12311 remotehost=10.145.130.4 remoteport=12312, localhost=10.145.133.2 localport=12313 remotehost=10.145.133.4 remoteport=12314'
      #   %r = remote host and port
alarm_report_interval = 10
support_extended_features=true

2. 使用gsql本地连接

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

1） 注意事项：

缺省情况下，客户端连接数据库后处于空闲状态时会根据参数“session_timeout”的默认值自动断开连接。如果要关闭超时设置，设置参数“session_timeout”为0即可。

2） 前提条件：

已确认连接信息，获取数据库主节点端口号信息。

3） 操作步骤：

（1）以操作系统用户omm登录数据库主节点。

（2）连接数据库。数据库安装完成后默认生成名称为postgres的数据库，第一次连接数据库时可以连接到此数据库。执行如下命令连接数据库。

gsql -d postgres -p 8000

其中postgres为需要连接的数据库名称，8000为数据库主节点的端口号。请根据实际情况替换。

连接成功后，系统显示类似如下信息。

gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131
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数据库时手动输入的密码，此处需将原始密码修改为自定义的密码，例如Mypwd123，命令如下。

postgres=# ALTER ROLE omm IDENTIFIED BY 'Mypwd123' REPLACE 'XuanYuan@2012';

（4） 退出数据库。

postgres=# \q

（二） 使用数据库

本篇描述使用数据库的基本操作。通过此篇用户可以完成创建数据库、创建表及向表中插入数据和查询表中数据等数据管理和基础权限管理操作。

1. 从这里开始

本篇为您完整地介绍一个使用数据库的示例，帮助您更好了解数据库，更多关于SQL语句的使用请参见后续章节。

（1）以操作系统用户omm登录数据库主节点。

（2）执行如下命令连接数据库。

gsql -d postgres -p 8000

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

gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131）
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#

（3） 创建数据库用户。
默认只有openGauss安装时创建的管理员用户可以访问初始数据库，您还可以创建其他数据库用户账号访问数据库。

postgres=# CREATE USER joe WITH PASSWORD "Bigdata@123";

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

CREATE ROLE

如上创建了一个用户名为joe，密码为Bigdata@123的用户。

（4） 创建数据库。

postgres=#  CREATE DATABASE db_tpcc OWNER joe;

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

CREATE DATABASE

db_tpcc数据库创建完成后，就可以按如下方法退出postgres数据库，使用新用户joe连接db_tpcc数据库执行创建表等操作。您也可以选择继续在默认的postgres数据库下进行后续的体验。

openGauss=#  \q
gsql -d db_tpcc -p 8000 -U joe -W Bigdata@123
gsql ((openGauss 1.0.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
db_tpcc=> 

（5） 创建schema。

db_tpcc=> CREATE SCHEMA joe AUTHORIZATION joe;

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

CREATE SCHEMA

（6） 创建表。
① 创建一个名称为mytable，只有一列的表。字段名为firstcol，字段类型为integer。

db_tpcc=>  CREATE TABLE mytable (firstcol int);

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

CREATE TABLE

② 向表中插入数据：

db_tpcc=> INSERT INTO mytable values (100);

当结果显示为如下信息，则表示插入数据成功。

INSERT 0 1

③ 查看表中数据：

db_tpcc=> SELECT * FROM mytable;
 firstcol 
----------
      100
(1 row)

2. 简单数据管理

在openGuass中拥有多种对象进行数据管理，例如表、数据库。用户可以通过创建表存储数据，查询表检索数据。也可以通过创建数据库进行数据隔离。本节为您介绍openGauss中如何创建数据库、表以及查看数据库中的对象。

1） 创建数据库

创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。

（1）注意事项：

① 只有拥有CREATEDB权限的用户才可以创建新数据库，系统管理员默认拥有此权限。

② 不能在事务块中执行创建数据库语句。

③ 在创建数据库过程中，若出现类似“could not initialize database directory”的错误提示，可能是由于文件系统上数据目录的权限不足或磁盘满等原因引起。

（2）语法格式：

CREATE DATABASE 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 ]}[...] ];

（3） 示例：

--创建jim和tom用户。
openGauss=# CREATE USER jim PASSWORD 'Bigdata@123';
openGauss=# CREATE USER tom PASSWORD 'Bigdata@123';
--创建一个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';
--创建兼容ORA格式的数据库。
openGauss=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';
--删除兼容TD、ORA格式的数据库。
openGauss=# DROP DATABASE td_compatible_db;
openGauss=# DROP DATABASE ora_compatible_db;

2） 创建表

在当前数据库中创建一个新的空白表，该表为命令执行者所有。

创建表时，如未指定表的存储方式，默认创建的是行存储表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。

（1） 语法格式：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option [...] ] }
        [, ... ])
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ COMPRESS | NOCOMPRESS ]
    [ TABLESPACE tablespace_name ];

其中列约束column_constraint为：

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

其中列的压缩可选项compress_mode为：

{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }

其中表约束table_constraint为：

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

其中like选项like_option为：

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }

其中索引参数index_parameters为：

[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

3） 查询数据

SELECT用于从表或视图中取出数据。

SELECT语句就像叠加在数据库表上的过滤器，利用SQL关键字从数据表中过滤出用户需要的数据。

（1）注意事项：

① 必须对每个在SELECT命令中使用的字段有SELECT权限。

② 使用FOR UPDATE或FOR SHARE还要求UPDATE权限。

（2）语法格式：

查询数据。

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [/*+ plan_hint */] [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
{ * | {expression [ [ AS ] output_name ]} [, ...] }
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ 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 ]
[ {FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ]} [...] ];

其中子查询with_query为：

with_query_name [ ( column_name [, ...] ) ]  
  AS ( {SELECT | values | INSERT | update | delete} )

其中指定查询源from_item为：

{[ ONLY ] table_name [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
|( 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 } ' )

简化版查询语法，功能相当于SELECT * FROM table_name。

TABLE { ONLY {(table_name)| table_name} | table_name [ * ]};

本篇从连接数据库和使用数据库两方面介绍了openGauss基本使用，你掌握了吗？下篇我们将接着介绍数据库的开发和编译，敬请期待！

Gauss松鼠会是汇集数据库爱好者和关注者的大本营， 大家共同学习、探索、分享数据库前沿知识和技术， 互助解决问题，共建数据库技术交流圈。