gsql是openGauss提供在命令行下运行的数据库连接工具，类似Oracle的sqlplus工具。Linux 64位版本数据库的gsql程序文件位于$GAUSSHOME/bin目录。
1、使用gsql命令连到数据库omm
先通过 gs_om -t status --all 确认端口号等信息
[omm@ogdb1 ~]$ gs_om -t status --all
-----------------------------------------------------------------------
cluster_state : Normal
redistributing : No
-----------------------------------------------------------------------
node : 1
node_name : ogdb1
instance_id : 6001
node_ip : 10.6.0.104
data_path : /og01/og3.1.0/install/data/dn01
instance_port : 40000
type : Datanode
instance_state : Normal
az_name : AZ1
instance_role : Normal
-----------------------------------------------------------------------
如上 instance_port 端口，即为需要连接的端口，本环境使用40000端口，非默认端口，则需要指定端口，切换至omm用户，使用gsql命令连接到omm数据库。
su - omm
使用默认方式连入，提示错误。
[omm@ogdb1 ~]$ gsql -r
failed to connect /og01/og3.1.0/tmp:5432.
使用指定参数方式连入，则可正常连入。
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 15:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=#
2、在gsql中查看数据库的版本、版权信息
登录数据库后，使用select version(); 可查看数据库的版本。
omm=# 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
(1 row)
使用 \copyright 可查看数据库的版本。
omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
3、常见元命令的使用
3.1、\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库
omm=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
omm | omm | SQL_ASCII | C | C |
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
(4 rows)
omm=#
3.2、\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/og01/og3.1.0/tmp" at port "40000".
3.3、\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
omm=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
3.4、\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
openGauss=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# \dg
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=#
3.5、\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
3.6、\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
openGauss=# \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
(11 rows)
3.7、\dt命令，命令\dt的作用是显示数据库中所有的表。
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
3.8、\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) |
3.9、\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
omm=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
(1 row)
3.10、\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)
3.11、 \x命令，打开扩展表格式模式。
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 |
+---------------+----------+
4、使用两种方法，连到postgres数据库中
通过gsql工具直接连入postgres数据库。
[omm@ogdb1 ~]$ gsql -d postgres -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=#
如当前已连接到其他数据库，用\c命令可连入postgres数据库。
omm=# -- 当前为 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=#
openGauss=#
5、 测试gsql中的默认事务自动提交功能
登录数据库后，可通过 show AUTOCOMMIT 命令可查看gsql中事务是否默认为自动提交，如果显示为on，即默认为自动提交。
omm=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)
查看customer_t表中数据。
omm=# SELECT * FROM customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
插入测试数据：
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');
INSERT 0 2
再次查看customer_t表中数据：
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
4321 | 2 | Lily | Carter
(3 rows)
重新登录后再次查看customer_t表中数据，说明当前为默认提交状态：
omm=# \q
[omm@ogdb1 ~]$ gsql -d omm -p 40000 -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
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
4321 | 2 | Lily | Carter
(3 rows)
omm=#
6、测试gsql中的事务手动提交功能
如变更数据库为非自动提交，需执行\set AUTOCOMMIT off 即可。
omm=# \set AUTOCOMMIT off
omm=# \echo :AUTOCOMMIT;
off; --查看是否显示为off ,显示为off即说明已关闭自动提交。
再次插入测试数据
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 2
再次查看表中数据
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
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(5 rows)
通过ROLLBACK;执行回滚操作
omm=# ROLLBACK;
ROLLBACK
检查确认新插入数据已经不存在，事务回滚成功
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
4321 | 2 | Lily | Carter
(3 rows)
7、了解gsql相关帮助信息
操作系统层可使用gsql --help命令，获取帮助信息。
[omm@ogdb1 ~]$ 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
....
连入数据库后，亦可通过输入help 查看帮助信息
omm=# help
You are using gsql, the command-line interface to gaussdb.
Type: \copyright for distribution terms
\h for help with SQL commands --可获取和SQL语法有关的帮助信息
\? for help with gsql commands --可获取与元命令有关帮助信息
\g or terminate with semicolon to execute query
\q to quit
测试表
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=# 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=#
omm=#
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
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=# create index idx_customer_id on customer_t(c_customer_id);
扫一扫
526
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
