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

1.gsql命令连到数据库omm

$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr  )
NOTICE : 7 days left before password expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

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

--数据库版本
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)

--PG基础版本
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.

3. 常见元命令使用

--显示当前有哪些数据库模式
openGauss=# \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)

--显示openGauss数据库集簇中有哪些数据库
openGauss=# \l
                           List of databases
+-----------+-------+-----------+---------+-------+-------------------+
|   Name    | Owner | Encoding  | Collate | Ctype | Access privileges |
+-----------+-------+-----------+---------+-------+-------------------+
| mydb      | omm   | GBK       | C       | C     |                   |
| postgres  | omm   | SQL_ASCII | C       | C     |                   |
| template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +|
|           |       |           |         |       | omm=CTc/omm       |
| template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +|
|           |       |           |         |       | omm=CTc/omm       |
+-----------+-------+-----------+---------+-------+-------------------+

--显示会话的连接信息
openGauss=# \conninfo
You are connected to database "postgres" as user "omm" via socket in "/opt/opengauss/tmp" at port "15400".

--连接到postgres数据库
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=# 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

--查看customer_t表的信息
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) |

--以表格形式显示表信息
openGauss=# \pset border 2
Border style is 2.
openGauss=#
openGauss=# select * from customer_t ;
+---------------+---------------+--------------+-------------+
| c_customer_sk | c_customer_id | c_first_name | c_last_name |
+---------------+---------------+--------------+-------------+
|          3769 | 5             | Grace        | White       |
+---------------+---------------+--------------+-------------+
(1 row)

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

4.1本地登录
[omm@localhost dn]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

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

4.2远程登录到数据库
--修改postgresql.conf中password_encryption_type参数为1
--增加pg_hba.conf一条记录
host    all             all             0.0.0.0/0               md5

password_encryption_type
**参数说明：**该字段决定采用何种加密方式对用户密码进行加密存储。修改此参数的配置不会自动触发已有用户密码加密方式的修改，只会影响新创建用户或修改用户密码操作。

该参数属于SIGHUP类型参数，请参考表1中对应设置方法进行设置。

**取值范围：**0、1、2、3

0表示采用md5方式对密码加密。
1表示采用sha256和md5两种方式分别对密码加密。
2表示采用sha256方式对密码加密。
3表示采用sm3方式对密码加密。

--重启数据库
gs_om-t stop & gs_om -t start

--登录验证
$ alter user mydb identified by "openGuass123";
$ \q
$ gsql -d "host=192.168.50.21 port=15400 dbname=postgres user=mydb password=openGuass123"
$ gsql postgres://mydb:openGuass123@192.168.50.21:15400/postgres -r

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@localhost ~]$ gsql -d postgres -p 15400 -r
gsql ((openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:19 commit 0 last mr  )
NOTICE : 7 days left before password 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)

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

--关闭自动提交功能，设置ATUOCOMMIT必须用大写
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
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)

7. gsql帮助

[omm@localhost ~]$ 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)

..............................

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "/opt/opengauss/tmp")
                           allow multi host IP address with comma separator in centralized cluster
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "omm")
  -W, --password=PASSWORD  the password of specified database user

For more information, type "\?" (for internal commands) or "\help" (for SQL
commands) from within gsql, or consult the gsql section in the openGauss documentation.

--\h获取和SQL语法有关的帮助信息
openGauss=# \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
......

--\? 获取和元命令有关的帮助信息
openGauss=# \?
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
......