openGauss每日一练第 2 天

原创陶笑 2022-11-28

110

#学习目标

学习从操作系统层面和使用openGauss工具查看数据库的状态、版本和数据文件目录。

课程作业

1.gsql命令连到数据库omm

样例
gsql -d postgres -h 172.16.0.176 -U jack -p 26000 -W Test@123
gsql连接数据库的几个参数 -d 数据库名 -h 主机名 -U 用户名 -p 端口 -W 密码

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-# \q

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=# 

复制

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

--查看数据库版本信息
openGauss=# 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)

-- 查看服务器版本号，该参数为固定参数，用户无法修改此参数，只能查看。该参数继承自PostgreSQL内核，表示当前数据库内核兼容PostgreSQL对应的server_version版本，无实际含义。
openGauss=# show server_version;
 server_version 
----------------
 9.2.4
(1 row)

--copyright可查看版权信息。
openGauss=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
复制

3.常见元命令使用

常用元命令很多主要有：

\du

显示用户和角色

\dg

\du和\dg相同

\dp 或 \z

显示表的权限分配

\timing

显示执行SQL的时间

\encoding

显示客户端字符集、并切换客户端字符集

\pset

格式化输出

\pset border 2

\pset format unaligned

使用tab分隔的文本

\pset fieldsep ‘\t’

默认分隔符是"|",可以用fieldsep 来设置其他分隔符；
例如 tab 分隔符

\o

\o代表将查询的结果输出到文件上

\X

\x 可以将行展示的数据按列展示，类似于MySQL 的/G

\i

\i 可以执行外部的命令，和非交互模式下 -f 效果相同

\e

\e 命令用于编辑文件，也可以用于编辑函数或试图定义，类似Linux中的vi

\e 后面不加文件名
表示生成一个临时文件，并进入vi编辑器，编译完成后会直接执行vi里的内容
\e+
表示编辑一个存在的文件
类似的还有 \ef \ev来编辑存储过程或者视图，所以\ef \ev既可以用来创建也可以用来查看当前的视图和存储过程的定义；

\reset

清除psql的缓冲区

\echo

用于输出一行信息，通常用于sql脚本中输出提示信息

练习了一些：

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

openGauss=# \cconninfo
Invalid command \cconninfo. Try \? for help.
openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "5432".
openGauss=# \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
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=# \dg
                                                              List of roles
 Role name |                                                    Attributes                                                    | Member of 
-----------+------------------------------------------------------------------------------------------------------------------+-----------
 gaussdb   | Sysadmin                                                                                                         | {}
 omm       | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}

openGauss=# \db
      List of tablespaces
    Name    | Owner | Location 
------------+-------+----------
 pg_default | omm   | 
 pg_global  | omm   | 
(2 rows)

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)

openGauss=# \dt
No relations found.
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=# CREATE TABLE customer_t
(  c_customer_sk             integer,   
 c_customer_id             char(5),    
 c_first_name              char(6),    
openGauss=# select version();^C
openGauss=# 
openGauss=# \dt
 public | customer_t | table | omm   | {orientation=row,compression=no}
(1 row)

openGauss=#                            List of relations
 Schema |    Name    | Type  | Owner |             Storage              
--------+------------+-------+-------+----------------------------------

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_new | table | omm   | {orientation=row,compression=no}
 public | customer_t   | table | omm   | {orientation=row,compression=no}
(2 rows)
复制

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

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".


omm@modb:~$ gsql -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中的默认事务自动提交功能

在AUTOCOMMIT打开的情况下，创建一张表，或任何DML操作不需要再进行commit提交

openGauss=# show AUTOCOMMIT;
 autocommit 
------------
 on
(1 row)

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

openGauss=# create  table customer_new as select * from customer_t;
INSERT 0 1
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中的事务手动提交功能

--当前会话中关闭AUTOCOMMIT 
openGauss=# \set 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
--当前会话中查看该表数据为5条记录
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)


--另开一个会话查看该表仍只有1条记录
[omm@og3 ~]$ gsql -p 26000 -d postgres -r
gsql ((openGauss 3.1.0 build 4e931f9a) compiled at 2022-09-29 14:19:24 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# select * from customer_t;
 c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
          3769 | 5             | Grace        | White
(1 row)


--原来的会话进行rollback后查询数据并未提交
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相关帮助

--连接数据库时，可以使用如下命令获取帮助信息。

gsql --help

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

\h

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

 \？

复制

个人更喜欢与在gsql中使用?来查看帮助

opengauss opengauss每日一练

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

关注作者

墨天轮-雪宝君

2年前

作业审核合格，一起参与21天openGauss学习打卡活动！活动详情：https://www.modb.pro/db/551619

2年前