前言：
最近参加了由opengauss、墨天轮、鲲鹏社区一起推出的活动《每日一练 opengauss 3.0.0 数据库在线实训课程》，共21天，墨天轮提供实操环境，特此记录学习笔记。
活动详情：https://www.modb.pro/db/551619
主题：
学习openGauss数据库客户端工具gsql的使用。
学习笔记
第二天我们学习opengauss的一个工具：gsql
gsql类似于oracle数据库的sqlplus工具，可以连接数据库，但不仅于此，gsql还有一些其他功能方便用户操作opengauss数据库。
同样，我们先来看看帮助。
root@modb:~# su - omm
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 (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
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.
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
omm@modb:~$
可以看到，帮助里面对gsql的介绍是 opengauss的交互终端。同样的，gsql可以帮助我们做很多事。下面我们看一些具体操作。
1.连接数据库
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=# \q
omm@modb:~$
# -r的意思就是可编辑，比如进去之后上下箭头就可以翻阅历史命令。
# -p指定端口，默认为5432
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=# \q
omm@modb:~$
2.在gsql中查看数据库的版本、pg基础版本和版权信息
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=# select version(); version
---------------------------------------------------------------------------
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr on aarch64-unknown-lin
ux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)
omm=# show server_version;
omm=# server_version
----------------
9.2.4
(1 row)
omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
omm=#
3.常见元命令的使用
–\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
openGauss=# \l
List of databases
+-[ RECORD 1 ]------+-------------+
| Name | omm |
| Owner | omm |
| Encoding | UTF8 |
| Collate | C |
| Ctype | C |
| Access privileges | |
+-[ RECORD 2 ]------+-------------+
| Name | postgres |
| Owner | omm |
| Encoding | UTF8 |
| Collate | C |
| Ctype | C |
| Access privileges | |
| Owner | omm |
| Encoding | UTF8 |
| Collate | C |
| Ctype | C |
| Access privileges | =c/omm |
| | omm=CTc/omm |
--More--+-[ RECORD 3 ]------+-------------+
| Name | template0 |
+-[ RECORD 4 ]------+-------------+
| Name | template1 |
| Owner | omm |
| Encoding | UTF8 |
| Collate | C |
| Ctype | C |
| Access privileges | =c/omm |
| | omm=CTc/omm |
+-------------------+-------------+
openGauss=#
–\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".
openGauss=#
–\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
openGauss=# \c postgres
openGauss=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=#
–\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
openGauss=# \du
List of roles
+-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------+
| Role name | gaussdb |
| Attributes | Sysadmin |
| Member of | {} |
+-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------+
| Role name | omm |
| Attributes | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT |
| Member of | {} |
+------------+------------------------------------------------------------------------------------------------------------------+
openGauss=# \dg
List of roles
+-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------+
| Role name | gaussdb |
| Attributes | Sysadmin |
openGauss=# | Member of | {} |
+-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------+
| Role name | omm |
| Attributes | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT |
| Member of | {} |
+------------+------------------------------------------------------------------------------------------------------------------+
openGauss=#
–\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=# \db
List of tablespaces
+-[ RECORD 1 ]----------+
| Name | pg_default |
| Owner | omm |
| Location | |
+-[ RECORD 2 ]----------+
| Name | pg_global |
| Owner | omm |
| Location | |
+----------+------------+
openGauss=#
–\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
openGauss=# \dn
List of schemas
+-[ RECORD 1 ]------------+
| Name | blockchain |
| Owner | omm |
+-[ RECORD 2 ]------------+
| Name | cstore |
--More--| Owner | omm |
+-[ RECORD 3 ]------------+
| Name | db4ai |
| Owner | omm |
+-[ RECORD 4 ]------------+
| Name | dbe_perf |
| Owner | omm |
+-[ RECORD 5 ]------------+
| Name | dbe_pldebugger |
| Owner | omm |
+-[ RECORD 6 ]------------+
| Name | dbe_pldeveloper |
| Owner | omm |
+-[ RECORD 7 ]------------+
| Name | gaussdb |
| Owner | gaussdb |
+-[ RECORD 8 ]------------+
| Name | pkg_service |
| Owner | omm |
+-[ RECORD 9 ]------------+
| Name | public |
| Owner | omm |
+-[ RECORD 10 ]-----------+
| Name | snapshot |
| Owner | omm |
+-[ RECORD 11 ]-----------+
| Name | sqladvisor |
| Owner | omm |
+-------+-----------------+
openGauss=#
在omm数据库中创建一个表
root@modb:~# su - 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=# CREATE TABLE customer_t
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# ) ;
CREATE TABLE
omm=#
omm=# --插入数据
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
omm=#
–\dt命令，命令\dt的作用是显示数据库中所有的表。
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
omm=#
–\d TableName命令，元命令\d TableName的作用是查看某个表的信息。
omm=# \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) |
omm=#
–\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
omm=# create index idx_customer_id on customer_t(c_customer_id);
CREATE INDEX
omm=#
omm=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
(1 row)
omm=#
–可以用\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=# \x
Expanded display is on.
omm=# SELECT * FROM customer_t;
+-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769 |
| c_customer_id | 5 |
| c_first_name | Grace |
| c_last_name | White |
+---------------+----------+
omm=#
4.测试gsql中的默认事务自动提交功能
查看gsql中事务是否默认为自动提交
omm=# show AUTOCOMMIT;
+-[ RECORD 1 ]----+
| autocommit | on |
+------------+----+
omm=#
测试gsql中事务默认为自动提交功能
omm=# create table customer_new as select * from customer_t;
INSERT 0 1
omm=# \q
omm@modb:~$
重新登录后看到之前创建的表customer_new：
omm=# \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)
omm=#
5.gsql中的事务：测试gsql中的事务手动提交功能
测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
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'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
--查看表中数据
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
6885 | 1 | Joes | Hunter
omm=# 4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(5 rows)
--执行回滚
omm=# ROLLBACK;
ROLLBACK
--检查是否回滚成功
omm=# SELECT * FROM customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
omm=#
omm=#
6.gsql相关的帮助
–连接数据库时，可以使用如下命令获取帮助信息。
gsql --help
–\h获取和SQL语法有关的帮助信息
omm-# \h
Available help:
ABORT CREATE DATA SOURCE DROP NODE GROUP
ALTER APP WORKLOAD GROUP CREATE DATABASE DROP OPERATOR
ALTER APP WORKLOAD GROUP MAPPING CREATE DIRECTORY DROP OWNED
ALTER AUDIT POLICY CREATE EXTENSION DROP PACKAGE
ALTER DATA SOURCE CREATE FOREIGN TABLE DROP PACKAGE BODY
ALTER DATABASE CREATE FUNCTION DROP PROCEDURE
ALTER DEFAULT PRIVILEGES CREATE GROUP DROP PUBLICATION
ALTER EXTENSION CREATE LANGUAGE DROP RESOURCE POOL
ALTER FOREIGN TABLE CREATE MASKING POLICY DROP ROLE
–? 获取和元命令有关的帮助信息
omm-# \?
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
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
\r reset (clear) the query buffer
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\w FILE write query buffer to file
总结
第二天我们学习了gsql如何连接数据库以及一些gsql的高级特性，同时还学会了如何使用元命令查看数据库、表空间、表信息、索引信息等。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
