openGauss每日一练第 2 天|客户端工具gsql的使用

1.gsql命令连到数据库omm

root@modb:~# su - omm
omm@modb:~$ gsql -d postgres -U omm -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.
openGauss=# 

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

openGauss=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
openGauss=# select version();
openGauss=#                                                                         version                                                                        
--------------------------------------------------------------------------------------------------------------------------------
 (openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr   on aarch64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

3.常见元命令使用

--\？命令 查看元命令帮助信息
openGauss=# \?
--\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
openGauss=# \l
--\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
openGauss=#\conninfo
--\c[onnect] [DBNAME]命令，元命令\c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
openGauss=#\c postgres

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

\du

\dg

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

\db

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

\dn


  --查看表（系统表）信息
  openGauss=#\d+(\dS+)
  --查看表空间
  openGauss=#\db+
  --查看所有数据库
 openGauss=# \l+ 
 --查看连接信息 
 omm=# \conninfo 或 \c

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

omm@modb:~$ gsql -d postgres -U omm -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.
或
openGauss=# \q
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=# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "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=# 

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

--查看事务提交状态
openGauss=# show autocommit;
 autocommit 
------------
 on
(1 row)
--创建表test01
CREATE TABLE test01
(  c_customer_sk             integer,   
 c_customer_id             char(5),    
 c_first_name              char(6),    
 c_last_name               char(8) 
) ;
--退出
openGauss=#\q
--连接
omm@modb:~$ gsql -d postgres -U omm -r
--查看test01是否创建
openGauss=# \dt
                            List of relations
 Schema |     Name     | Type  | Owner |             Storage              
--------+--------------+-------+-------+----------------------------------
 public | customer_new | table | omm   | {orientation=row,compression=no}
 public | customer_t   | table | omm   | {orientation=row,compression=no}
 public | test01       | table | omm   | {orientation=row,compression=no}
(3 rows)

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

--连接
omm@modb:~$ gsql -d postgres -U omm -r
--查看事务提交状态
 openGauss=# \echo :AUTOCOMMIT
 autocommit 
------------
 on
(1 row)
--关闭事务自动提交状态
openGauss=# \set AUTOCOMMIT OFF
--查看事务提交状态
openGauss=# \echo :AUTOCOMMIT
OFF
--创建表test02
CREATE TABLE test02
(  c_customer_sk             integer,   
 c_customer_id             char(5),    
 c_first_name              char(6),    
 c_last_name               char(8) 
) ;
--查看表
openGauss-# \dt
                            List of relations
 Schema |     Name     | Type  | Owner |             Storage              
--------+--------------+-------+-------+----------------------------------
 public | customer_new | table | omm   | {orientation=row,compression=no}
 public | customer_t   | table | omm   | {orientation=row,compression=no}
 public | test02       | table | omm   | {orientation=row,compression=no}
(3 rows)
--执行回滚提交rollback commit命令 再查看test02已经被回滚 说明关闭事务自动提交成功
openGauss=# rollback 
openGauss=# commit;
ROLLBACK
openGauss=# \dt
                            List of relations
 Schema |     Name     | Type  | Owner |             Storage              
--------+--------------+-------+-------+----------------------------------
 public | customer_new | table | omm   | {orientation=row,compression=no}
 public | customer_t   | table | omm   | {orientation=row,compression=no}
(2 rows)

7.了解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
                           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)

                           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)Output format options:
  -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)
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -r                       if this parameter is set,use libedit 
                           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
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.

                           must use with -c or -f