gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。
# 1.使用gsql命令连接数据库
--使用omm用户连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。
su - omm
gsql -r
或
gsql -d omm -p 5432 -r
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
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=# \q
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=#
# 2.在gsql中查看数据库的版本、pg基础版本和版权信息
select version();
show server_version;
\copyright
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-linux-gnu, compiled b
y g++ (GCC) 7.3.0, 64-bit
(1 row)
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.
# 3.常见元命令的使用
--\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
\l
--\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
\conninfo
--\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
\c postgres
--\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
\du
\dg
--\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
\db
--\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
\du
--创建表
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
--插入数据
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
--\dt命令，命令\dt的作用是显示数据库中所有的表。
\dt
--\d TableName命令，元命令\d TableName的作用是查看某个表的信息。
\d customer_t
--\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
create index idx_customer_id on customer_t(c_customer_id);
\di
--可以用\pset命令以不同的方法显示表：
\pset border 2
SELECT * FROM customer_t;
--打开扩展表格式模式。
\x
omm=# omm=# \l
--\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
omm=#
List of databases
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=#
omm=# --\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
omm=#
\conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
omm=# omm=#
omm=# \c postgres] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=#
openGauss=#
openGauss=# openGauss=#
集簇中，目前有哪些用户和角色。 令与元命令\du命令的作用类似，都是显示openGauss数据库�
\du
openGauss=#
openGauss=# List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------------------------------
-+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}
openGauss=#
openGauss=# \dg
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------------------------------
-+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}
openGauss=#
openGauss=# --\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=#
openGauss=# \db
(2 rows)
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
openGauss=# openGauss=# --\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
openGauss=#
openGauss=# \du
List of roles
Role name | Attributes
| Member of
-----------+-----------------------------------------------------------------------------------------------------------------
-+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT
| {}
openGauss=#
openGauss=# openGauss=# --创建表
CREATE TABLE customer_t
openGauss-# ( c_customer_sk integer,
openGauss(# c_customer_id char(5),
openGauss(# c_first_name char(6),
openGauss(# c_last_name char(8)
openGauss(# ) ;
CREATE TABLE
openGauss=# openGauss=# --插入数据
;NSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
openGauss=# openGauss=#
--\dt命令，命令\dt的作用是显示数据库中所有的表。
openGauss=# \dt
List of relations
Schema | Name | Type | Owner | Storage
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
--------+------------+-------+-------+----------------------------------
openGauss=#
openGauss=# --\d TableName命令，元命令\d TableName的作用是查看某个表的信息。
openGauss=# \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) |
openGauss=# openGauss=#
--\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
openGauss=# create index idx_customer_id on customer_t(c_customer_id);
CREATE INDEX
openGauss=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
(1 row)
openGauss=#
openGauss=# openGauss=#
--可以用\pset命令以不同的方法显示表：
openGauss=# \pset border 2
Border style is 2.
openGauss=# SELECT * FROM customer_t;
+---------------+---------------+--------------+-------------+
| c_customer_sk | c_customer_id | c_first_name | c_last_name |
+---------------+---------------+--------------+-------------+
| 3769 | 5 | Grace | White |
+---------------+---------------+--------------+-------------+
(1 row)
openGauss=# openGauss=# openGauss=#
--打开扩展表格式模式。
openGauss=# \x
Expanded display is on.
openGauss=# # 4.gsql中的事务：测试gsql中的默认事务自动提交功能
--查看gsql中事务是否默认为自动提交
show AUTOCOMMIT;
--测试gsql中事务默认为自动提交功能
create table customer_new as select * from customer_t;
\q
--重新登录后看到之前创建的表customer_new：
gsql -d postgres -p 5432 -r
\dt
openGauss=# --查看gsql中事务是否默认为自动提交
openGauss=# show AUTOCOMMIT;
+-[ RECORD 1 ]----+
| autocommit | on |
+------------+----+
openGauss=# --测试gsql中事务默认为自动提交功能
openGauss=# create table customer_new as select * from customer_t;
INSERT 0 1
openGauss=# \q
omm@modb:~$
omm@modb:~$ --重新登录后看到之前创建的表customer_new：
-bash: $'--\351\207\215\346\226\260\347\231\273\345\275\225\345\220\216\347\234\213\345\210\260\344\271\213\345\211\215\345\210\233\345\273\272\347\232\204\350\241\250customer_new\357\274\232': command not found
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=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------+-------+-------+----------------------------------
public | customer_new | table | omm | {orientation=row,compression=no}
openGauss=# public | customer_t | table | omm | {orientation=row,compression=no}
(2 rows)
openGauss=## 5.gsql中的事务：测试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;
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
openGauss-# #注意：此处设置ATUOCOMMIT必须用大写！
openGauss-# \set AUTOCOMMIT off
openGauss-# --插入一些数据
openGauss-# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
openGauss-# (6885, 1, 'Joes', 'Hunter'),
openGauss-# (4321, 2, 'Lily','Carter'),
openGauss-# (9527, 3, 'James', 'Cook'),
openGauss-# (9500, 4, 'Lucy', 'Baker');
ERROR: invalid byte sequence for encoding "UTF8": 0xe2 0x67 0x73
openGauss=# --查看表中数据
openGauss=# select * from customer_t;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
openGauss=# ROLLBACK;
--执行回滚
openGauss=# ROLLBACK
openGauss=# --检查是否回滚成功
openGauss=# SELECT * FROM customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)# 6.gsql相关的帮助
--连接数据库时，可以使用如下命令获取帮助信息。
gsql --help
--\h获取和SQL语法有关的帮助信息
\h
--\? 获取和元命令有关的帮助信息
\？
--连接数据库时，可以使用如下命令获取帮助信息。
openGauss-# --\h获取和SQL语法有关的帮助信息
openGauss-#
\h
openGauss-# 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 DIRECTORY CREATE INDEX DROP RESOURCE LABEL
ALTER EXTENSION CREATE LANGUAGE DROP RESOURCE POOL
ALTER FOREIGN TABLE CREATE MASKING POLICY DROP ROLE
ALTER FOREIGN TABLE FOR HDFS CREATE MATERIALIZED VIEW DROP ROW LEVEL SECURITY POLICY
ALTER FUNCTION CREATE MODEL DROP SCHEMA
ALTER GLOBAL CONFIGURATION CREATE NODE DROP SEQUENCE
ALTER GROUP CREATE NODE GROUP DROP SERVER
ALTER INDEX CREATE OPERATOR DROP SUBSCRIPTION
ALTER LARGE OBJECT CREATE PACKAGE DROP SYNONYM
ALTER MASKING POLICY CREATE PACKAGE BODY DROP TABLE
ALTER MATERIALIZED VIEW CREATE PROCEDURE DROP TABLESPACE
ALTER NODE GROUP CREATE RESOURCE LABEL DROP TEXT SEARCH DICTIONARY
ALTER OPERATOR CREATE RESOURCE POOL DROP TRIGGER
ALTER NODE CREATE PUBLICATION DROP TEXT SEARCH CONFIGURATION
ALTER PUBLICATION CREATE ROW LEVEL SECURITY POLICY DROP USER
--More-- ALTER PACKAGE CREATE ROLE DROP TYPE
--More--
Most commands optionally preceded by integer argument k. Defaults in brackets.
Star (*) indicates argument becomes new default.
-------------------------------------------------------------------------------
<space> Display next k lines of text [current screen size]
z Display next k lines of text [current screen size]*
<return> Display next k lines of text [1]*
d or ctrl-D Scroll k lines [current scroll size, initially 11]*
q or Q or <interrupt> Exit from more
s Skip forward k lines of text [1]
f Skip forward k screenfuls of text [1]
b or ctrl-B Skip backwards k screenfuls of text [1]
' Go to place where previous search started
= Display current line number
/<regular expression> Search for kth occurrence of regular expression [1]
n Search for kth occurrence of last r.e [1]
!<cmd> or :!<cmd> Execute <cmd> in a subshell
v Start up /usr/bin/vi at current line
ctrl-L Redraw screen
:n Go to kth next file [1]
:p Go to kth previous file [1]
:f Display current file name and line number
. Repeat previous command
-------------------------------------------------------------------------------
ALTER RESOURCE LABEL CREATE SCHEMA DROP VIEW
ALTER RESOURCE POOL CREATE SEQUENCE DROP WEAK PASSWORD DICTIONARY
ALTER ROLE CREATE SERVER DROP WORKLOAD GROUP
ALTER ROW LEVEL SECURITY POLICY CREATE SNAPSHOT AS END
ALTER SCHEMA CREATE SNAPSHOT FROM EXECUTE
ALTER SEQUENCE CREATE SUBSCRIPTION EXECUTE DIRECT
ALTER SERVER CREATE SYNONYM EXPLAIN
ALTER SESSION CREATE TABLE FETCH
ALTER SUBSCRIPTION CREATE TABLE AS GRANT
ALTER SYNONYM CREATE TABLE PARTITION INSERT
ALTER TABLE CREATE TEXT SEARCH CONFIGURATION MOVE
ALTER TABLE PARTITION CREATE TEXT SEARCH DICTIONARY PREDICT BY
ALTER SYSTEM KILL SESSION CREATE TABLE SUBPARTITION LOCK
ALTER SYSTEM SET CREATE TABLESPACE MERGE
ALTER TABLE SUBPARTITION CREATE TRIGGER PREPARE
ALTER TABLESPACE CREATE TYPE PREPARE TRANSACTION
ALTER TEXT SEARCH CONFIGURATION CREATE USER PUBLISH SNAPSHOT
ALTER TEXT SEARCH DICTIONARY CREATE VIEW PURGE
ALTER TRIGGER CREATE WEAK PASSWORD DICTIONARY PURGE SNAPSHOT
ALTER TYPE CREATE WORKLOAD GROUP REASSIGN OWNED
ALTER USER CURSOR REFRESH MATERIALIZED VIEW
ALTER VIEW DEALLOCATE REINDEX
ALTER WORKLOAD GROUP DECLARE RESET
ANALYSE DELETE REVOKE
ANALYZE DO ROLLBACK
ANONYMOUS BLOCK DROP APP WORKLOAD GROUP ROLLBACK PREPARED
ARCHIVE SNAPSHOT DROP APP WORKLOAD GROUP MAPPING SAMPLE SNAPSHOT
BEGIN DROP AUDIT POLICY SAVEPOINT
CALL DROP CLIENT MASTER KEY SELECT
CHECKPOINT DROP COLUMN ENCRYPTION KEY SELECT INTO
CLEAN CONNECTION DROP DATA SOURCE SET
CLOSE DROP DATABASE SET CONSTRAINTS
CLUSTER DROP DIRECTORY SET ROLE
COMMENT DROP EXTENSION SET SESSION AUTHORIZATION
COMMIT DROP FOREIGN TABLE SET TRANSACTION
COMMIT PREPARED DROP FUNCTION SHOW
COPY DROP GLOBAL CONFIGURATION START TRANSACTION
CREATE APP WORKLOAD GROUP DROP GROUP TIMECAPSULE TABLE
CREATE APP WORKLOAD GROUP MAPPING DROP INDEX TRUNCATE
CREATE AUDIT POLICY DROP MASKING POLICY UPDATE
CREATE BARRIER DROP MATERIALIZED VIEW VACUUM
CREATE CLIENT MASTER KEY DROP MODEL VALUES
CREATE COLUMN ENCRYPTION KEY DROP NODE
openGauss-# \？
Invalid command \？. Try \? for help.
openGauss-#
openGauss-# \?
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
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\i+ FILE KEY execute commands from encrypted file
\ir FILE as \i, but relative to location of current script
\ir+ FILE KEY as \i+, but relative to location of current script
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\ddp [PATTERN] list default privileges
\dD[S+] [PATTERN] list domains
\ded[+] [PATTERN] list data sources
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions最后修改时间：2022-11-25 10:28:42
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
