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

原创落笔花生 2022-11-25

247

学习目标

学习openGauss数据库客户端工具gsql的使用。

课程学习

gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。

1.使用gsql命令连接数据库

--使用omm用户连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。
root@modb:~# su - omm
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.

2.在gsql中查看数据库的版本、pg基础版本和版权信息

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.

3.常见元命令的使用

--\l命令，元命令\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=#            |       |          |         |       | omm=CTc/omm
(4 rows)
--\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
omm-# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
--\c[onnect] [DBNAME]命令，元命令\ 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命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
omm=# \du
                                                              List of roles
 Role name |                                                    Attributes                                         
           | Member of 
-----------+-------------------------------------------------------------------------------------------------------
-----------+-----------
 gaussdb   | Sysadmin                                                                                              
           | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyad
min, UseFT | {}

omm=# \dg
                                                              List of roles
 Role name |                                                    Attributes                                         
           | Member of 
-----------+-------------------------------------------------------------------------------------------------------
-----------+-----------
 gaussdb   | Sysadmin                                                                                              
           | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyad
min, UseFT | {}
--\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
omm=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)
--\dn命令，元命令\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)
--创建表并插入数据
omm=# CREATE TABLE customer_t
omm-# (  c_customer_sk             integer,   
omm(#  c_customer_id             char(5),    
omm(#  c_first_name              char(6),    
omm(#  c_last_name               char(8) 
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
--\dt命令，命令\dt的作用是显示数据库中所有的表。
omm=# \dt
                           List of relations
 Schema |    Name    | Type  | Owner |             Storage              
--------+------------+-------+-------+----------------------------------
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)
--\d TableName命令，元命令\d TableName的作用是查看某个表的信息。
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) |
--\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
omm=# create index idx_customer_id on customer_t(c_customer_id); #创建一个索引
CREATE INDEX
omm=# \di
                        List of relations
 Schema |      Name       | Type  | Owner |   Table    | Storage 
--------+-----------------+-------+-------+------------+---------
 public | idx_customer_id | index | omm   | customer_t | 
(1 row)
--可以用\pset命令以不同的方法显示表：
omm=# \pset border 2 #设置输出格式为里外都有表格
Border style is 2.
omm=# SELECT * FROM customer_t;
+---------------+---------------+--------------+-------------+
| c_customer_sk | c_customer_id | c_first_name | c_last_name |
+---------------+---------------+--------------+-------------+
|          3769 | 5             | Grace        | White       |
+---------------+---------------+--------------+-------------+
(1 row)
--打开扩展表格式模式。
omm=# \x #把表中每一行的每列数据都拆分为单行展示。
Expanded display is on.
omm=# SELECT * FROM customer_t;
+---------------+----------+

omm=# +-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769     |
| c_customer_id | 5        |
| c_first_name  | Grace    |
| c_last_name   | White    |

4.gsql中的事务：测试gsql中的默认事务自动提交功能

--查看gsql中事务是否默认为自动提交
omm=# show AUTOCOMMIT;
+-[ RECORD 1 ]----+
| autocommit | on |
+------------+----+
--测试gsql中事务默认为自动提交功能
omm=# create  table customer_new as select * from customer_t; #在旧表基础上复制一张新表（不含主键等）
INSERT 0 1
omm=# \q
--重新登录后看到之前创建的表customer_new：
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=# \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)

5.gsql中的事务：测试gsql中的事务手动提交功能

–测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
omm=# \echo :AUTOCOMMIT;
on;
omm=# \set AUTOCOMMIT off #关掉自动提交
omm=# \echo :AUTOCOMMIT;
off;
omm=# omm-# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES    
(6885, 1, 'Joes', 'Hunter'),    
omm-# omm-# (4321, 2, 'Lily','Carter'),    
omm-# (9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# 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)
omm=# rollback; 回滚
ROLLBACK
omm=# select * from customer_t;
(1 row)

omm=#  c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White

6.gsql相关的帮助

--连接数据库时，可以使用如下命令获取帮助信息。(登录数据库之前使用)
omm=# gsql --help
omm-# \q
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

Input and output options:
  -a, --echo-all           echo all input from script
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -k, --with-key=KEY       the key for decrypting the encrypted file
  -L, --log-file=FILENAME  send session log to file
  -m, --maintenance        can connect to cluster during 2-pc transaction recovery
  -n, --no-libedit        disable enhanced command line editing (libedit)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -C, --enable-client-encryption              enable client encryption feature
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)
。。。
--\h获取和SQL语法有关的帮助信息
omm-# \h
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 FUNCTION                    CREATE MODEL                      DROP SCHEMA
  ALTER GLOBAL CONFIGURATION        CREATE NODE                       DROP SEQUENCE
  ALTER GROUP                       CREATE NODE GROUP                 DROP SERVER
  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 INDEX                       CREATE OPERATOR                   DROP SUBSCRIPTION
...                 

--\? 获取和元命令有关的帮助信息
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)
  \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
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \ir FILE               as \i, but relative to location of current script
  \ir+ FILE KEY          as \i+, but relative to location of current script
  \i FILE                execute commands from file
  \i+ FILE KEY           execute commands from encrypted file
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)
--More--  \r                     reset (clear) the query buffer
  \w FILE                write query buffer to file
...

课程作业

1.gsql命令连到数据库omm

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.
root@modb:~# su - omm
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.

2.查看数据库的版本、版权信息

select version(); #数据库版本
show server_version; #pg基础版本
 \copyright #版权信息

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的作用是显示当前数据库有哪些数据库模式。
\dn
--创建表
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 #一行一行显示单列

4.使用两种方法，连到postgres数据库中

root@modb:~# su - omm
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=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".

root@modb:~# su - omm
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=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".

5.测试gsql中的默认事务自动提交功能

--查看gsql中事务是否默认为自动提交
show AUTOCOMMIT;
--测试gsql中事务默认为自动提交功能
create  table customer_new as select * from customer_t;
\q
--重新登录后看到之前创建的表customer_new：
gsql -d postgres  -p 5432  -r
\dt

6.测试gsql中的事务手动提交功能

#测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
\echo : AUTOCOMMIT; #查看自动提交状态
\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;

7.了解gsql相关帮助

#连接数据库时，可以使用如下命令获取帮助信息。（在登陆数据库之前使用）
gsql --help
#\h获取和SQL语法有关的帮助信息
\h
#\? 获取和元命令有关的帮助信息
 \？

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

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

学习目标

课程学习

课程作业

评论