一、学习目标

学习openGauss数据库客户端工具gsql的使用。感觉gsql跟psql类似，用法也类似，所以不难理解。

二、课程学习

1、使用gsql连接数据库
–使用omm用户连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。
切到omm用户，然后使用gsql -r即可登录数据库
或者gsql -d omm -p 5432 -r

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# 
omm@modb:~$ gsql -d omm  -p 5432  -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# 

2、在gsql中查看数据库的版本、pg基础版本和版权信息
登录到数据库中然后就可以查询数据库版本等信息，可以看出目前环境是高斯3.0,基于pg 9.2.4版本。

omm=# select version();
-------------------------------------------
 (openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr   on aarch64-unknown-linu
x-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

                                                                        version                             
                                           
------------------------------------------------------------------------------------------------------------
omm=# show server_version;
 server_version 
----------------
 9.2.4
(1 row)

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

3、数据库通用元命令
\l 可以查看当前环境中所有数据库。

omm=# \l
                         List of databases
   Name    | Owner | Encoding | Collate | Ctype | Access privileges 
-----------+-------+----------+---------+-------+-------------------
 omm       | omm   | UTF8     | C       | C     | 
 postgres  | omm   | UTF8     | C       | C     | 
 template0 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
 template1 | omm   | UTF8     | C       | C     | =c/omm           +
           |       |          |         |       | omm=CTc/omm
(4 rows)

\conninfo的作用是在gsql中，显示会话的连接信息。

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

–\c[onnect] [DBNAME]命令切换连接的数据库

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

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

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

openGauss-# \dg
                                                              List of roles
 Role name |                                                    Attributes                                  
                  | Member of 


-----------+------------------------------------------------------------------------------------------------
------------------+-----------
 gaussdb   | Sysadmin                                                                                       
                  | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, P
olicyadmin, UseFT | {}openGauss-# 

CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
–插入数据
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, ‘Grace’,‘White’);

openGauss=# CREATE TABLE customer_t
openGauss-# openGauss(# (  c_customer_sk             integer,   
openGauss(#  c_customer_id             char(5),    
 c_first_name              char(6),    
openGauss(#  c_last_name               char(8) 
openGauss(# ) ;
CREATE TABLE
openGauss=# openGauss=# 

 'Grace','White'); INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 
INSERT 0 1
openGauss=# 

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

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

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

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

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

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

可以用\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)

打开扩展表格式模式

omm=# \xomm=# 
Expanded display is on.

4、gsql中的事务：测试gsql中的默认事务自动提交功能
–测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！

\set AUTOCOMMIT off
–插入一些数据
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, ‘Joes’, ‘Hunter’),
(4321, 2, ‘Lily’,‘Carter’),
(9527, 3, ‘James’, ‘Cook’),
(9500, 4, ‘Lucy’, ‘Baker’);
–查看表中数据
select * from customer_t;
–执行回滚
ROLLBACK;
–检查是否回滚成功
SELECT * FROM customer_t;

omm=# \set AUTOCOMMIT off
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
omm-# (6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
omm-# omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# select * from customer_t;
+-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769     |
| c_customer_id | 5        |
| c_first_name  | Grace    |
| c_last_name   | White    |
+-[ RECORD 2 ]--+----------+
| c_customer_sk | 6885     |
| c_customer_id | 1        |
| c_first_name  | Joes     |
| c_last_name   | Hunter   |
+-[ RECORD 3 ]--+----------+
| c_customer_sk | 4321     |
| c_customer_id | 2        |
| c_first_name  | Lily     |
| c_last_name   | Carter   |
+-[ RECORD 4 ]--+----------+
| c_customer_sk | 9527     |
| c_customer_id | 3        |
| c_first_name  | James    |
| c_last_name   | Cook     |
+-[ RECORD 5 ]--+----------+
| c_customer_sk | 9500     |
| c_customer_id | 4        |
| c_first_name  | Lucy     |
| c_last_name   | Baker    |
+---------------+----------+

omm=#  ROLLBACK;
ROLLBACK
omm=# SELECT * FROM customer_t;
+-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769     |
| c_customer_id | 5        |
| c_first_name  | Grace    |
| c_last_name   | White    |
+---------------+----------+

6、gsql相关的帮助

omm@modb:~$ 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
                           execute command file as a single transaction
  -?, --help               show this help, then exit

Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -k, --with-key=KEY       the key for decrypting the encrypted file
  -L, --log-file=FILENAME  send session log to file
  -m, --maintenance        can connect to cluster during 2-pc transaction recovery
  -n, --no-libedit        disable enhanced command line editing (libedit)
                           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
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -C, --enable-client-encryption              enable client encryption feature
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)

Output format options:  -R, --record-separator=STRING
                           set record separator (default: newline)
  -r                       if this parameter is set,use libedit 
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)

  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator to zero byte
  -0, --record-separator-zero
                           set record separator to zero byte
  -2, --pipeline           use pipeline to pass the password, forbidden to use in terminal
                           must use with -c or -f

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
                           allow multi host IP address with comma separator in centralized cluster
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "omm")
  -W, --password=PASSWORD  the password of specified database user

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within gsql, or consult the gsql section in the openGauss documentation.

omm=# \h
Available help:
  ABORT                             BEGIN                             CREATE TABLESPACE                 DROP TEXT SEARCH CONFIGURATION
  ALTER APP WORKLOAD GROUP          CALL                              CREATE TEXT SEARCH CONFIGURATION  DROP TEXT SEARCH DICTIONARY
  ALTER APP WORKLOAD GROUP MAPPING  CHECKPOINT                        CREATE TEXT SEARCH DICTIONARY     DROP TRIGGER
  ALTER AUDIT POLICY                CLEAN CONNECTION                  CREATE TRIGGER                    DROP TYPE
  ALTER DATA SOURCE                 CLOSE                             CREATE TYPE                       DROP USER
  ALTER DATABASE                    CLUSTER                           CREATE USER                       DROP VIEW
  ALTER DEFAULT PRIVILEGES          COMMENT                           CREATE VIEW                       DROP WEAK PASSWORD DICTIONARY
  ALTER FOREIGN TABLE FOR HDFS      CREATE APP WORKLOAD GROUP         DEALLOCATE                        EXECUTE DIRECT
  ALTER EXTENSION                   COMMIT PREPARED                   CREATE WORKLOAD GROUP             END
  ALTER FOREIGN TABLE               COPY                              CURSOR                            EXECUTE
  ALTER DIRECTORY                   COMMIT                            CREATE WEAK PASSWORD DICTIONARY   DROP WORKLOAD GROUP
  ALTER FUNCTION                    CREATE APP WORKLOAD GROUP MAPPING DECLARE                           EXPLAIN
  ALTER INDEX                       CREATE CLIENT MASTER KEY          DROP APP WORKLOAD GROUP           INSERT
  ALTER LARGE OBJECT                CREATE COLUMN ENCRYPTION KEY      DROP APP WORKLOAD GROUP MAPPING   LOCK
  ALTER MASKING POLICY              CREATE DATA SOURCE                DROP AUDIT POLICY                 MERGE
  ALTER MATERIALIZED VIEW           CREATE DATABASE                   DROP CLIENT MASTER KEY            MOVE
  ALTER NODE                        CREATE DIRECTORY                  DROP COLUMN ENCRYPTION KEY        PREDICT BY
  ALTER NODE GROUP                  CREATE EXTENSION                  DROP DATA SOURCE                  PREPARE
  ALTER OPERATOR                    CREATE FOREIGN TABLE              DROP DATABASE                     PREPARE TRANSACTION
  ALTER PACKAGE                     CREATE FUNCTION                   DROP DIRECTORY                    PUBLISH SNAPSHOT
--More--  ALTER GLOBAL CONFIGURATION        CREATE AUDIT POLICY               DELETE                            FETCH
  ALTER GROUP                       CREATE BARRIER                    DO                                GRANT

omm=# \?
General
  \copyright             show openGauss usage and distribution terms
  \i FILE                execute commands from file
  \i+ FILE KEY           execute commands from encrypted file
  \r                     reset (clear) the query buffer
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \ir FILE               as \i, but relative to location of current script
  \ir+ FILE KEY          as \i+, but relative to location of current script
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \h(\help) [NAME]              help on syntax of SQL commands, * for all commands
  \parallel [on [num]|off] toggle status of execute (currently off)
  \q                     quit gsql

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \p                     show the contents of the query buffer
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

三、学习小结

第二课主要是介绍连接openGauss数据库中一些通用操作，建表、建索引、查看数据库、查看连接信息、查看数据库用户、角色、切换数据库、帮忙命令等常用操作，操作难度还是属于容易程度，比较好入门。