一、学习目标

这节课是本次实训第二节课程，本次课的重点是熟练掌握gsql工具的使用。gsql是openGauss数据库提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。

二、课程学习

2.1 gsql命令

如果想充分的了解gsql命令及其参数具体含义，可通过gsql --help来查看。

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
                           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)
  -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:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING                           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
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -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)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero

commands) from within gsql, or consult the gsql section in the openGauss documentation.

\h

 \？

每个命令详细解释还可以通过openGauss官网 https://docs.opengauss.org/zh/docs/3.1.0-lite/docs/Toolreference/gsql.html去查询gsql更详细的使用。

2.2 gsql查看数据库的版本

select version();
show server_version;
 \copyright

2.3 gsql命令及其含义

以下gsql常用命令及其含义，如下表:

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

--查看gsql中事务是否默认为自动提交
show AUTOCOMMIT;
--测试gsql中事务默认为自动提交功能
create  table customer_new as select * from customer_t;
\q
--重新登录后看到之前创建的表customer_new：
gsql -d postgres  -p 5432  -r
\dt

2.5 测试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;

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

gsql -r
或
gsql -d omm  -p 5432  -r