day2-gsql 使用

学习笔记

gsql 说明

• gsql 是 openGauss 提供的在命令行下运行的数据库连接工具，可以通过此工具连接服务并对其进行维护。

连接数据库

su - omm
# 如果有 omm 库并使用的默认端口
gsql -r
# -d 指定要连接的数据库，-p 指定端口
gsql -d postgres -p 15400 -r

在 gsql 中查看数据库版本、pg基础版本、版权信息

$ gsql -d postgres -p 15400 -r
openGauss=# 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
 
openGauss=# show server_version;
 server_version
----------------
 9.2.4
 
openGauss=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.

查看元命令使用

• \l：查看所有数据库

openGauss=# \l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
 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

• \conninfo：查看连接的基本信息

openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/opt/huawei/tmp" at port "15400".

• \c：切换数据库

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

• \du \dg：查看用户和角色

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

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

• \db：显示当前数据库有那些表空间

aaa=# \db
      List of tablespaces
    Name    | Owner | Location
------------+-------+----------
 pg_default | omm   |
 pg_global  | omm   |

• \dn：显示当前数据库有哪些数据库模式

aaa=# \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

• \dt：显示所有表

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

• \di：显示所有索引

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

gsql autocommit

• 查看 autocmmit 状态

openGauss=# show AUTOCOMMIT;
 autocommit
------------
 on

• 开启/关闭 autocommit
  • 即使更改了，使用 show AUTOCMMIT 查看隔离级别仍为默认隔离级别

# 开启
openGauss=# \set AUTOCOMMIT on
# 关闭
openGauss=# \set AUTOCOMMIT off

获取帮助信息

• 查看使用 gsql 连接数据库时的帮助信息

gsql --help

• gsql 内获取 SQL 语法有关帮助信息

Available help:
  ABORT                             ALTER SEQUENCE                    COMMIT                            CREATE RESOURCE POOL              DROP APP WORKLOAD GROUP MAPPING   DROP SEQUENCE                     PURGE SNAPSHOT
  ALTER APP WORKLOAD GROUP          ALTER SERVER                      COMMIT PREPARED                   CREATE ROLE                       DROP AUDIT POLICY                 DROP SERVER                       REASSIGN OWNED
  
……

• 获取元命令有关帮助信息

openGauss=# \?
General
  \copyright             show openGauss usage and distribution terms
  \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
  
……

课程作业

gsql命令连到数据库omm

gsql -d postgres -p 15400 -r

查看数据库的版本、版权信息

openGauss=# select version();
openGauss=# \copyright

常见元命令使用

• \dl \du ……

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

gsql -r
gsql -d postgres -p 15400 -r

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

# 关闭
openGauss=# \set AUTOCOMMIT off
openGauss=# CREATE TABLE customer_t ( c_customer_sk integer, c_customer_id char(5), c_first_name char(6), c_last_name char(8) ) ;
openGauss=# \l
openGauss=# rollback;
openGauss=# \l

了解gsql相关帮助

openGauss=# \help
openGauss=# \?