1.gsql命令连到数据库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=# gsql 命令参数参考
gsql -d <数据库名称> -h <集群地址> -U <数据库用户> -p <数据库端口> -r
2.查看数据库的版本、版权信息
查看openGauss数据库版本。根据下列输出可以看到，当前使用的是openGauss版本为3.0.0 build 02c14696。
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, compile
d by g++ (GCC) 7.3.0, 64-bit
(1 row)
查看PG版本。说明openGauss是由PG9.2.4版本演化而来。
omm=# show server_version;
server_version
----------------
9.2.4
(1 row)查看版权信息。因为openGauss内核和华为GaussDB Kernel共基线开发，所以可以看到GaussDB Kernel字样。
omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.3.常见元命令使用
- --\l命令，作用是显示openGauss数据库集簇中，目前有哪些数据库。
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)- --\conninfo命令，作用是在gsql中，显示会话的连接信息。
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".- --\c[onnect] [DBNAME]命令，作用是在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=# - --\du命令和\dg命令，两者作用类似，都是显示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 | {}- --\db命令，作用是显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)- --\dn命令，作用是显示当前数据库有哪些数据库模式。
openGauss=# \dn
List of schemas
Name | Owner
-----------------+---------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
gaussdb | gaussdb
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)- --\dt命令，作用是显示数据库中所有的表。
-- 创建表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=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White'); INSERT 0 1openGauss=# \dt List of relations Schema | Name | Type | Owner | Storage --------+------------+-------+-------+---------------------------------- public | customer_t | table | omm | {orientation=row,compression=no} (1 row)
- --\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) | - --\di IndexName命令，查看索引信息。
-- 创建索引openGauss=# create index idx_customer_id on customer_t(c_customer_id); CREATE INDEXopenGauss=# \di idx_customer_id List of relations Schema | Name | Type | Owner | Table | Storage --------+-----------------+-------+-------+------------+--------- public | idx_customer_id | index | omm | customer_t | (1 row)
- --可以用\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)
- --\x 打开扩展表格式模式。
openGauss=# \x Expanded display is on.openGauss=# 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@modb:~$ gsql -d postgres -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=#- 使用gsql连接默认库，再使用\c跳转
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
openGauss=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# 5.测试gsql中的默认事务自动提交功能
--查看gsql中事务是否默认为自动提交
openGauss=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)--测试gsql中事务默认为自动提交功能
openGauss=# create table customer_new as select * from customer_t;
INSERT 0 1
openGauss=# \q
omm@modb:~$ --重新登录后看到之前创建的表customer_new
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} public | customer_t | table | omm | {orientation=row,compression=no} (2 rows)
6.测试gsql中的事务手动提交功能
–测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
openGauss=# \set AUTOCOMMIT offopenGauss=# \echo :AUTOCOMMIT off
--向表中插入一些数据
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');
INSERT 0 4--查看表中数据
openGauss=# 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)--执行回滚
openGauss=# rollback ;
ROLLBACK--检查是否回滚成功
openGauss=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)7.了解gsql相关帮助
--连接数据库时，可以使用如下命令获取帮助信息。
--\h获取和SQL语法有关的帮助信息
--\? 获取和元命令有关的帮助信息
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
