gsql是openGauss提供在命令行下运行的数据库连接工具，类似Oracle的sqlplus工具。Linux 64位版本数据库的gsql程序文件位于$GAUSSHOME/bin目录。

1、使用gsql命令连到数据库omm

先通过 gs_om -t status --all 确认端口号等信息

[omm@ogdb1 ~]$ gs_om -t status --all
-----------------------------------------------------------------------
cluster_state             : Normal
redistributing            : No
-----------------------------------------------------------------------
node                      : 1
node_name                 : ogdb1
instance_id               : 6001
node_ip                   : 10.6.0.104
data_path                 : /og01/og3.1.0/install/data/dn01
instance_port             : 40000
type                      : Datanode
instance_state            : Normal
az_name                   : AZ1
instance_role             : Normal
-----------------------------------------------------------------------

如上 instance_port 端口，即为需要连接的端口，本环境使用40000端口，非默认端口，则需要指定端口，切换至omm用户，使用gsql命令连接到omm数据库。

su - omm

使用默认方式连入，提示错误。
[omm@ogdb1 ~]$ gsql -r
failed to connect /og01/og3.1.0/tmp:5432.

使用指定参数方式连入，则可正常连入。
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 15:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# 

2、在gsql中查看数据库的版本、版权信息

登录数据库后，使用select version(); 可查看数据库的版本。

omm=# select version();
 version                                                                        
-----------------------------------------------------------
 (openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

使用 \copyright 可查看数据库的版本。

omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.

3、常见元命令的使用

3.1、\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库

omm=# \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)

omm=# 

3.2、\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。

omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/og01/og3.1.0/tmp" at port "40000".

3.3、\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。

omm=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".

3.4、\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。

openGauss=# \du
           List of roles
 Role name |                                Attributes                                                    | Member of 
-----------+-------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=# \dg
           List of roles
 Role name |                                Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=# 

3.5、\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。

openGauss=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

3.6、\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。

openGauss=# \dn
     List of schemas
      Name       | Owner 
-----------------+-------
 blockchain      | omm
 cstore          | omm
 db4ai           | omm
 dbe_perf        | omm
 dbe_pldebugger  | omm
 dbe_pldeveloper | omm
 dbe_sql_util    | omm
 pkg_service     | omm
 public          | omm
 snapshot        | omm
 sqladvisor      | omm
(11 rows)

3.7、\dt命令，命令\dt的作用是显示数据库中所有的表。

omm=# \dt
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage              
--------+------------+-------+-------+----------------------------------
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)

3.8、\d TableName命令，元命令\d TableName的作用是查看某个表的信息。

omm=# \d customer_t
        Table "public.customer_t"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 c_customer_sk | integer      | 
 c_customer_id | character(5) | 
 c_first_name  | character(6) | 
 c_last_name   | character(8) | 

3.9、\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。

omm=# \di
                        List of relations
 Schema |      Name       | Type  | Owner |   Table    | Storage 
--------+-----------------+-------+-------+------------+---------
 public | idx_customer_id | index | omm   | customer_t | 
(1 row)

3.10、\pset命令，可以以不同的方法显示表：

omm=# \pset border 2
Border style is 2.
omm=# SELECT * FROM customer_t;
+---------------+---------------+--------------+-------------+
| c_customer_sk | c_customer_id | c_first_name | c_last_name |
+---------------+---------------+--------------+-------------+
|          3769 | 5             | Grace        | White       |
+---------------+---------------+--------------+-------------+
(1 row)

3.11、 \x命令，打开扩展表格式模式。

omm=# \x
Expanded display is on.
omm=# SELECT * FROM customer_t;
+-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769     |
| c_customer_id | 5        |
| c_first_name  | Grace    |
| c_last_name   | White    |
+---------------+----------+

4、使用两种方法，连到postgres数据库中

通过gsql工具直接连入postgres数据库。

[omm@ogdb1 ~]$ gsql -d postgres -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# 

如当前已连接到其他数据库，用\c命令可连入postgres数据库。

omm=#      -- 当前为 omm 数据库
omm=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# 
openGauss=# 

5、 测试gsql中的默认事务自动提交功能

登录数据库后，可通过 show AUTOCOMMIT 命令可查看gsql中事务是否默认为自动提交，如果显示为on，即默认为自动提交。

omm=# show AUTOCOMMIT;
 autocommit 
------------
 on
(1 row)

查看customer_t表中数据。
omm=# SELECT * FROM customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White 
          
插入测试数据：
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES 
omm-# (6885, 1, 'Joes', 'Hunter'),    
omm-# (4321, 2, 'Lily','Carter');
INSERT 0 2

再次查看customer_t表中数据：
omm=# SELECT * FROM customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
(3 rows)


重新登录后再次查看customer_t表中数据，说明当前为默认提交状态：
omm=# \q
[omm@ogdb1 ~]$ gsql -d omm  -p 40000  -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# SELECT * FROM customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
(3 rows)

omm=# 

6、测试gsql中的事务手动提交功能

如变更数据库为非自动提交，需执行\set AUTOCOMMIT off 即可。

omm=# \set AUTOCOMMIT off
omm=# \echo :AUTOCOMMIT;
off;            --查看是否显示为off ,显示为off即说明已关闭自动提交。

再次插入测试数据
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES 
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 2

再次查看表中数据
omm=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
          9527 | 3             | James        | Cook    
          9500 | 4             | Lucy         | Baker   
(5 rows)

通过ROLLBACK;执行回滚操作
omm=# ROLLBACK;
ROLLBACK

检查确认新插入数据已经不存在，事务回滚成功
omm=#  select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White   
          6885 | 1             | Joes         | Hunter  
          4321 | 2             | Lily         | Carter  
(3 rows)

7、了解gsql相关帮助信息

操作系统层可使用gsql --help命令，获取帮助信息。

[omm@ogdb1 ~]$ gsql --help
gsql is the openGauss interactive terminal.

Usage:
  gsql [OPTION]... [DBNAME [USERNAME]]

General options:
  -c, --command=COMMAND    run only single command (SQL or internal) and exit
  -d, --dbname=DBNAME      database name to connect to (default: "omm")
  -f, --file=FILENAME      execute commands from file, then exit
  -l, --list               list available databases, then exit
  -v, --set=, --variable=NAME=VALUE
                           set gsql variable NAME to VALUE
  -V, --version            output version information, then exit
  -X, --no-gsqlrc          do not read startup file (~/.gsqlrc)
  -1 ("one"), --single-transaction
                           execute command file as a single transaction
  -?, --help               show this help, then exit

....

连入数据库后，亦可通过输入help 查看帮助信息

omm=# help
You are using gsql, the command-line interface to gaussdb.
Type:  \copyright for distribution terms
       \h for help with SQL commands      --可获取和SQL语法有关的帮助信息
       \? for help with gsql commands     --可获取与元命令有关帮助信息
       \g or terminate with semicolon to execute query
       \q to quit

测试表

omm=# CREATE TABLE customer_t
omm-# (  c_customer_sk             integer,   
omm(#  c_customer_id             char(5),    
omm(#  c_first_name              char(6),    
omm(#  c_last_name               char(8) 
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
omm=# 
omm=# 
omm=# \dt
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage              
--------+------------+-------+-------+----------------------------------
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)

omm=# \d customer_t
        Table "public.customer_t"
    Column     |     Type     | Modifiers 
---------------+--------------+-----------
 c_customer_sk | integer      | 
 c_customer_id | character(5) | 
 c_first_name  | character(6) | 
 c_last_name   | character(8) | 

omm=# create index idx_customer_id on customer_t(c_customer_id);