gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护,与oracle的SQLplus类似，今天就学习关于gsql的一些基本操作。
使用gsql命令连接数据库
两种方式：1）方式1
root@modb:~# su - omm
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=#
2）方式2
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=#
查看数据库的版本、pg基础版本和版权信息
1）版本查询
omm=# select 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)
2）pg基础版本
omm=# show server_version;
server_version
----------------
9.2.4
(1 row)
3）版权信息
omm=# \copyright
GaussDB Kernel Database Management System
Copyright © Huawei Technologies Co., Ltd. 2018. All rights reserved.
常见元命令的使用
\l命令
显示openGauss数据库集簇中，目前有哪些数据库
mm=# \l
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
\conninfo
在gsql中，显示会话的连接信息
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
\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".
\du和\dg
都是显示openGauss数据库集簇中，目前有哪些用户和角色
openGauss=# \du
-----------+------------------------------------------------------------------------------------------------------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policya
dmin, UseFT | {}
List of roles
Role name | Attributes
| Member of
openGauss=#
openGauss=# \dg
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------
------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policya
dmin, UseFT | {}
\db
显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
\dn
显示当前数据库有哪些数据库模式。
omm=# \dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
查看表结构及索引
创建测试数据：
--创建表
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
--插入数据
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
omm=# INSERT 0 1
--显示数据库中所有的表
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}
--查看某个表的信息
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) |
Indexes:
"idx_customer_id" btree (c_customer_id) TABLESPACE pg_default
--查看某个索引的信息
omm=# \di idx_customer_id
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
\pset命令以不同的方法显示表
omm=# \pset border 2
Border style is 2.
omm=#
omm=# SELECT * FROM customer_t;
+---------------+---------------+--------------+-------------+
| c_customer_sk | c_customer_id | c_first_name | c_last_name |
+---------------+---------------+--------------+-------------+
| 3769 | 5 | Grace | White |
| 3769 | 5 | Grace | White |
+---------------+---------------+--------------+-------------+
--打开扩展表格式模式。
\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
-[ RECORD 2 ]-+---------
c_customer_sk | 3769
c_customer_id | 5
c_first_name | Grace
c_last_name | White
测试gsql中的默认事务自动提交功能
--查看gsql中事务是否默认为自动提交
omm=# show AUTOCOMMIT;
-[ RECORD 1 ]--
autocommit | on
--测试gsql中事务默认为自动提交功能
omm=# create table customer_new as select * from customer_t;
INSERT 0 2
omm=# \q ---创建后退出
omm@modb:~$
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=# \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)
测试gsql中的事务手动提交功能
#注意：此处设置ATUOCOMMIT必须用大写！
omm=# \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');
--查看表中数据
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
3769 | 5 | Grace | White
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(6 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
3769 | 5 | Grace | White
(2 rows)
gsql相关的帮助
连接数据库时，可以使用如下命令获取帮助信息。
gsql --help
--\h获取和SQL语法有关的帮助信息
\h
--\? 获取和元命令有关的帮助信息
\？
