openGauss每日一练第 2 天 | 学习心得体会

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

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

[root@gaussdb ~\]# su - omm  
Last login: Fri Nov 25 10:18:43 CST 2022 on pts/0  
Welcome to 4.19.90-2003.4.0.0036.oe1.x86\_64  
System information as of time: Fri Nov 25 10:41:04 CST 2022  
System load: 0.06  
Processes: 93  
Memory used: 13.9%  
Swap used: 0.0%  
Usage On: 5%  
IP address: 192.168.101.124  
Users online: 1 

实训环境比较卡，我使用的是之前训练营的环境，这里连接的库是postgres端口为26000，不是默认5432端口

[omm@gaussdb ~\]$ gsql -d postgres -p 26000  
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr )  
NOTICE : The password has been expired, please change the password.  
Non-SSL connection (SSL connection is recommended when requiring high-security)  
Type "help" for help.  

openGauss=#  

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

openGauss=# select version();  
version  
\------------------------------------------------------------------------------------------------------------------------------------------------------  
(openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr on x86\_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit  
(1 row)  
  
openGauss=# show server\_version;  
server\_version  
\----------------  
9.2.4  
(1 row)  
  
openGauss=# \\copyright  
GaussDB Kernel Database Management System  
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.  
  
openGauss=#

3.常见元命令的使用

openGauss=# \\l  --显示openGauss数据库集簇中，目前有哪些数据库。  

List of databases  
Name | Owner | Encoding | Collate | Ctype | Access privileges  
\-----------+-------+----------+---------+-------+-------------------  
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  
test | omm | UTF8 | C | C |  
(4 rows)  
  
openGauss=# \\conninfo  --显示openGauss数据库集簇中，目前有哪些数据库。  
You are connected to database "postgres" as user "omm" via socket in "/gaussdb/om/omm\_mppdb" at port "26000".

  
openGauss=# \\c postgres  --在gsql中，切换连接的数据库postgres。  
Non-SSL connection (SSL connection is recommended when requiring high-security)  
You are now connected to database "postgres" as user "omm".

openGauss=# \\du  --\\du命令和\\dg命令，元命令\\dg命令与元命令\\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。  
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 | {}  
  

–创建表customer_t

openGauss=# CREATE TABLE customer\_t  
( c\_customer\_sk integer,  
c\_customer\_id char(5),  
c\_first\_name char(6),  
c\_last\_name char(8)  
) ;openGauss-# openGauss(# openGauss(# openGauss(# openGauss(#  
CREATE TABLE 

–在customer_t中插入数据

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  

\dt     --\dt显示数据库中所有的表，可查到刚刚创建的customer_t

openGauss=# \\dt 
List of relations  
Schema | Name | Type | Owner | Storage  
\--------+------------+-------+-------+----------------------------------  
public | customer\_t | table | omm | {orientation=row,compression=no}  
(1 row)  

openGauss=# \d customer_t   --元命令\d TableName的作用是查看某个表的信息。

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=# \di   --元命令\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=# \\di idx\_customer\_id  
List of relations  
Schema | Name | Type | Owner | Table | Storage  
\--------+-----------------+-------+-------+------------+---------  
public | idx\_customer\_id | index | omm | customer\_t |  
(1 row) 

openGauss=# \pset border 2  --可以用\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=# \x  --打开扩展表格式模式

openGauss=# \\x 
Expanded display is on.

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

openGauss=# show autocommit;   --查看gsql中事务是否默认为自动提交  
\-\[ RECORD 1 \]--  
autocommit | on  

–测试gsql中事务默认为自动提交功能

openGauss=# create table customer\_new as select \* from customer\_t;  
INSERT 0 1  
openGauss=# \\q

–重新登录后看到之前创建的表customer_new：

\[omm@gaussdb ~\]$ gsql -d postgres -p 26000  
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr )  
NOTICE : The password has been expired, please change the password.  
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)  

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

–-测试gsql手动提交，关闭自动提交功能

openGauss=# \\set AUTOCOMMIT off  --关闭自动提交  
  
openGauss=# \\echo :AUTOCOMMIT  --查看自动提交功能已关闭  
off

–测试是否能回滚

openGauss=# 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');openGauss-# openGauss-# openGauss-# openGauss-#  
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  
6885 | 1 | Joes | Hunter  
4321 | 2 | Lily | Carter  
9527 | 3 | James | Cook  
9500 | 4 | Lucy | Baker  
(9 rows)  
 
openGauss=# rollback  --执行回滚  
  
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) 

6.gsql相关的帮助

gsql --help

\h获取和SQL语法有关的帮助信息

–\? 获取和元命令有关的帮助信息