openGauss每日一练第 2 天 | 学习openGauss客户端工具gsql的使用

原创 SongF 2022-11-25

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 1

openGauss=# \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 INDEX
openGauss=# \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)复制