1.gsql命令连到数据库omm
su - omm gsql -r 或 gsql -d omm -p 5432 -r (-d 数据库名 -p 端口号 -r选项提供了对gsql命令的历史版本支持。)
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.查看数据库的版本、版权信息
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 by g++ (GCC) 7.3.0, 64-bit
(1 row)
omm=# show server_version;
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 --\conninfo命令，显示会话的连接信息。 \conninfo --\c[onnect] [DBNAME]命令，连接的数据库postgres。 \c postgres --\du命令和\dg命令，查看用户和角色。 \du \dg --\db命令，查询表空间。 \db --\dn命令，查询schema \dn
omm=# \l
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=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
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=# \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=# \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=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
openGauss=# \dn
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)
List of schemas
Name | Owner
-----------------+---------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
gaussdb | gaussdb
openGauss=#
4.使用两种方法，连到postgres数据库中
su - omm
gsql -r
\c postgres --切换到postgres
或
gsql -d postgres -p 5432 -r --直接登录postgres
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=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
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=#
5.测试gsql中的默认事务自动提交功能
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=#
openGauss=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)
openGauss=# CREATE TABLE customer_gs
c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
;
CREATE TABLE
openGauss=# INSERT INTO customer_gs (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
openGauss=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-------------+-------+-------+----------------------------------
public | customer_gs | table | omm | {orientation=row,compression=no}
(1 row)
openGauss=# SELECT * FROM customer_gs;
openGauss=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
6.测试gsql中的事务手动提交功能
openGauss=# \set AUTOCOMMIT off
openGauss=# INSERT INTO customer_gs (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');
INSERT 0 4
openGauss=# select * from customer_gs;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
openGauss=# 3769 | 5 | Grace | White
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(5 rows)
openGauss=# ROLLBACK;
ROLLBACK
openGauss=# select * from customer_gs;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
openGauss=#
7.了解gsql相关帮助
gsql --help --查看帮助命令 \h --查看SQL语法帮助信息 \？ --查看元命令帮助信息
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
.....
omm=# \h
Available help:
ABORT BEGIN CREATE TABLESPACE DROP TEXT SEARCH CONFIGURATION
ALTER APP WORKLOAD GROUP CALL CREATE TEXT SEARCH CONFIGURATION DROP TEXT SEARCH DICTIONARY
ALTER APP WORKLOAD GROUP MAPPING CHECKPOINT CREATE TEXT SEARCH DICTIONARY DROP TRIGGER
ALTER AUDIT POLICY CLEAN CONNECTION CREATE TRIGGER DROP TYPE
......
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)
\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
\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
--More-- \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)
......
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
