1.gsql命令连到数据库omm
# 切换到 omm 用户
su - omm
# 连接数据库
gsql -r
复制
2.查看数据库的版本、版权信息
# 查看数据库版本
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)
# 查看pg基础版本信息
show server_version;
+----------------+
| server_version |
+----------------+
| 9.2.4 |
+----------------+
(1 row)
# 查看版权信息
\copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
复制
3.常见元命令使用
# 查看有哪些数据库
\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 |
+-----------+-------+----------+---------+-------+-------------------+
# 查看当前连接信息
\conninfo
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".
# 切换到指定的数据库 \c [数据库名称]
\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
\du
List of roles
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
| Role name | Attributes | Member of |
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
| gaussdb | Sysadmin | {} |
| omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
\dg
List of roles
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
| Role name | Attributes | Member of |
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
| gaussdb | Sysadmin | {} |
| omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} |
+-----------+------------------------------------------------------------------------------------------------------------------+-----------+
# 查看表空间
\db
List of tablespaces
+------------+-------+----------+
| Name | Owner | Location |
+------------+-------+----------+
| pg_default | omm | |
| pg_global | omm | |
+------------+-------+----------+
(2 rows)
# 查看所有的数据库模式 schemas
\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
List of relations
+--------+------------+-------+-------+----------------------------------+
| Schema | Name | Type | Owner | Storage |
+--------+------------+-------+-------+----------------------------------+
| public | customer_t | table | omm | {orientation=row,compression=no} |
+--------+------------+-------+-------+----------------------------------+
(1 row)
# 查看表的详情信息 \d [表名]
\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) | |
+---------------+--------------+-----------+
Indexes:
"idx_customer_id" btree (c_customer_id) TABLESPACE pg_default
# 查看某索引的详情信息
\di idx_customer_id
List of relations
+--------+-----------------+-------+-------+------------+---------+
| Schema | Name | Type | Owner | Table | Storage |
+--------+-----------------+-------+-------+------------+---------+
| public | idx_customer_id | index | omm | customer_t | |
+--------+-----------------+-------+-------+------------+---------+
(1 row)
# 对查询结果表格样式进行修改 \pset ，具体样式可参考http://www.postgres.cn/docs/10/app-psql.html 中\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
\x
select * from customer_t ;
"count as file"
+-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769 |
| c_customer_id | 5 |
| c_first_name | Grace |
| c_last_name | White |
+---------------+----------+
复制
4.使用两种方法，连到postgres数据库中
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=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
复制
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
openGauss=# You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".
复制
5.测试gsql中的默认事务自动提交功能
# 查看当前的事务提交模式,显示为自动提交
openGauss=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)
# 创建一张表，并退出当前会话，然后重新登录，查看表是否创建成功
openGauss=# create table customer_new as select * from customer_t;
INSERT 0 1
#退出当前会话，重新连接
openGauss=# \q
omm@modb:~$ gsql -r -d postgres
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.
#查看表是否创建成功，可以看到customer_new 表创建成功
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中的事务手动提交功能
# 将事务提交模式修改为手动
openGauss=# \set 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');
INSERT 0 4
# 在未提交的状态下，查询表中数据，可以看到，能查询出数据
openGauss=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(4 rows)
# 对刚才的插入进行事务回滚
openGauss=# ROLLBACK;
#再次查询，发现无法查询出刚才插入的数据
openGauss=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
(0 rows)
复制
0
作业审核合格，一起参与21天openGauss学习打卡活动！
活动详情：https://www.modb.pro/db/551619
