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

原创晨辉 2022-11-26

1961

1.gsql命令连到数据库omm

gsql #所有默认值连接
gsql -r #指定
gsql -d postgres -p 5432 #指定连接数据库及服务端口

omm@modb:~$ gsql -d postgres -p 5432
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=# \q
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.
复制

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

通过gsql连接到数据库后使用select version();命令或者show server_version;查看数据库版本信息；
select version();为openGauss数据库当前版本信息，show server_version为openGauss基础版本信息（如下可以看到基础版本为9.2.4，说明openGauss是基于PostgreSQL 9.2.4研发而来）.
使用\copyright 查看版本信息，可以看到openGauss版权为华为公司。

omm=# select version();
 (openGauss 3.0.0 build 02c14696) compiled at 2022-04-01 18:12:00 commit 0 last mr   on aarch64-unknown-lin
ux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

                                                                        version                            
                                            
---------------------------------------------------
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 显示数据库中数据库信息
\c 连接指定数据库，如\c postgres
\du 同\dg 显示数据库中所有用户和角色
\db 显示数据库中所有表空间信息
\dn 显示数据库中所有schema信息
\d 显示当前数据库下相关数据库对象信息(包含表、视图、物化视图、序列、外部表、stream\ contview)
\d tablename 查看某个表的详细信息
\dt 显示当前数据库中所有的表
\dt+ 以扩展方式显示当前数据库所有表信息，比起\dt 多了最后一列描述信息
\di 查看当前数据库中索引信息
\di indexname 查看当前数据库某个索引的信息
\dv 查看当前数据库视图信息
\ds 查看当前数据库序列信息
\df 查看当前数据库函数信息
\dx 查看已安装的扩展程序信息

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

gsql 指定连接postgres数据库
gsql -d postgres -p 5432
gsql默认连接后使用\c postgres 连接postgres数据库

omm@modb:~$ gsql -d postgres -p 5432
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=# \q
omm@modb:~$ gsql
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=# 
复制

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

如下创建测试表test，插入一行数据，进行rollback时提示进程中无事务，再次查询test发现刚插入的数据已经可以查询，说明刚才事务已自动提交；
使用show AUTOCOMMIT;查看autocommit参数发现参数为on;

xhy=# create table test(id int,dt date);
CREATE TABLE
xhy=# insert into test select 1,sysdate;
INSERT 0 1
xhy=# rollback;
NOTICE:  there is no transaction in progress
ROLLBACK
xhy=# select * from test;
 id |         dt          
----+---------------------
  1 | 2022-11-26 20:50:31
(1 row)

xhy=# show AUTOCOMMIT;
 autocommit 
------------
 on
(1 row)
复制

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

使用\set AUTOCOMMIT off 关闭自动提交功能；
再次进行插入测试，插入后进行rollback 再次查询数据；
如下可以看到rollback后再次查询刚刚插入的数据不见了，说明回滚成功。

xhy=# \set AUTOCOMMIT off
xhy=# insert into test select 2,sysdate;
INSERT 0 1
xhy=# select * from test;
 id |         dt          
----+---------------------
  1 | 2022-11-26 20:50:31
  2 | 2022-11-26 21:03:57
(2 rows)

xhy=# rollback;
ROLLBACK
xhy=# select * from test;
 id |         dt          
----+---------------------
  1 | 2022-11-26 20:50:31
(1 row)

复制

7.了解gsql相关帮助

在操作系统层面使用 gsql --help可以获取gsql 命令使用参数的相关帮助
使用gsql 连接数据库后使用\h 可以获取SQL语法相关帮助，如想知道创建数据库语法则
\h CREATE DATABASE
? 为获取和元命令有关的帮助信息

[omm1@og1 ~]$ 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: "postgres")
  -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)

Output format options:
  -A, --no-align           unaligned table output mode
  -F, --field-separator=STRING
                           set field separator (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see \pset command)
  -R, --record-separator=STRING
                           set record separator (default: newline)
  -r                       if this parameter is set,use libedit 
  -t, --tuples-only        print rows only
  -T, --table-attr=TEXT    set HTML table tag attributes (e.g., width, border)
  -x, --expanded           turn on expanded table output
  -z, --field-separator-zero
                           set field separator to zero byte
  -0, --record-separator-zero
                           set record separator to zero byte
  -2, --pipeline           use pipeline to pass the password, forbidden to use in terminal
                           must use with -c or -f

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "/opt/mogdb/tmp")
                           allow multi host IP address with comma separator in centralized cluster
  -p, --port=PORT          database server port (default: "26000")
  -U, --username=USERNAME  database user name (default: "omm1")
  -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.

[omm1@og1 ~]$ gsql -d xhy
gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:41 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.


xhy=# \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 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 FUNCTION                    CREATE MODEL                      DROP SCHEMA
  ALTER GLOBAL CONFIGURATION        CREATE NODE                       DROP SEQUENCE
  ALTER GROUP                       CREATE NODE GROUP                 DROP SERVER
  ALTER INDEX                       CREATE OPERATOR                   DROP SUBSCRIPTION
  ALTER LARGE OBJECT                CREATE PACKAGE                    DROP SYNONYM
  ALTER MASKING POLICY              CREATE PACKAGE BODY               DROP TABLE
  ALTER MATERIALIZED VIEW           CREATE PROCEDURE                  DROP TABLESPACE
  ALTER NODE                        CREATE PUBLICATION                DROP TEXT SEARCH CONFIGURATION
  ALTER NODE GROUP                  CREATE RESOURCE LABEL             DROP TEXT SEARCH DICTIONARY
  ALTER OPERATOR                    CREATE RESOURCE POOL              DROP TRIGGER
  ALTER PACKAGE                     CREATE ROLE                       DROP TYPE
  ALTER PUBLICATION                 CREATE ROW LEVEL SECURITY POLICY  DROP USER
  ALTER RESOURCE LABEL              CREATE SCHEMA                     DROP VIEW
xhy=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE database_name
    [ [ WITH ] {[ OWNER [=] user_name ]|
           [ TEMPLATE [=] template ]|
           [ ENCODING [=] encoding ]|
           [ LC_COLLATE [=] lc_collate ]|
           [ LC_CTYPE [=] lc_ctype ]|
           [ DBCOMPATIBILITY [=] compatibility_type ]|
           [ TABLESPACE [=] tablespace_name ]|
           [ CONNECTION LIMIT [=] connlimit ]}[...] ];

xhy=# \?
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
  \r                     reset (clear) the query buffer
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \i+ FILE KEY           execute commands from encrypted file
  \ir FILE               as \i, but relative to location of current script
  \ir+ FILE KEY          as \i+, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \ddp    [PATTERN]      list default privileges
  \dD[S+] [PATTERN]      list domains
  \ded[+] [PATTERN]      list data sources
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[+]  [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[+]  [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dE[S+] [PATTERN]      list foreign tables
  \dx[+]  [PATTERN]      list extensions
  \l[+]   [PATTERN]      list all databases
  \sf[+] FUNCNAME        show a function's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset NAME [VALUE]     set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager|
                         feedback})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

Connection
  \c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
                         connect to new database (currently "xhy")
  \encoding [ENCODING]   show or set client encoding
  \conninfo              display information about current connection

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations
xhy=#  
复制