学习目标
学习openGauss数据库客户端工具gsql的使用。
课程学习
gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。
课后作业：
1.gsql命令连到数据库omm
gsql -r 或
gsql -d omm -p 5432 -r
2.查看数据库的版本、版权信息
select version();
show server_version;
\copyright
3.常见元命令使用
–\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
–\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
–\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
–\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
–\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
–\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
omm=# \l
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)
omm=# \conninfo
You are connected to database “omm” as user “omm” via socket in “/tmp” at port “5432”.
omm=# \c postgres
You are now connected to database "postgres" as user "omm".
openGauss=# \du
Role name | Attributes
| Member of
\-----------+-----------------------------------------------------------------------------------------------------
\-------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}
openGauss=# \dg
Role name | Attributes
| Member of
\-----------+-----------------------------------------------------------------------------------------------------
\-------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy
admin, UseFT | {}
openGauss=# \db
Name | Owner | Location
\------------+-------+----------
pg\_default | omm |
pg\_global | omm |
(2 rows)
openGauss=# \dn
Name | Owner
\-----------------+---------
blockchain | omm
cstore | omm
db4ai | omm
dbe\_perf | omm
dbe\_pldebugger | omm
dbe\_pldeveloper | omm
gaussdb | gaussdb
pkg\_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)
4.使用两种方法，连到postgres数据库中
omm@modb:~$ gsql -d postgres -p 5432 -r
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# \c postgres
You are now connected to database "postgres" as user "omm".
5.测试gsql中的默认事务自动提交功能
openGauss=# show autocommit;
\------------
on
(1 row)
openGauss=# create table t1(id int);
INSERT 0 1
openGauss=# \\q
omm@modb:~$ gsql -d postgres -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.
openGauss=# select \* from t1;
id
\----
10
(1 row)
6.测试gsql中的事务手动提交功能
openGauss=# \set AUTOCOMMIT OFF 注意：此处的AUTOCOMMIT一定要大写
openGauss=# SELECT * from t1;
\----
10
(1 row)
openGauss=# insert INTO t1 values(20);
INSERT 0 1
openGauss=# select * from t1;
\----
10
20
(2 rows)
openGauss=# rollback;
ROLLBACK
openGauss=# **select * from t1;
**id
----
10
(1 row)
7.了解gsql相关帮助
–连接数据库时，可以使用如下命令获取帮助信息。 gsql --help
–\h获取和SQL语法有关的帮助信息
**–\? 获取和元命令有关的帮助信息
**
omm@modb:~$ gsql --help
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 (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
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")
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 -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.
