打卡第2天，学会体会
今天学习gsql相关操作；
gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。
https://docs.opengauss.org/zh/docs/3.1.0/docs/Toolreference/gsql.html
使用gsql命令连接数据库
使用gsql连接到openGauss服务器。
gsql工具使用-d参数指定目标数据库名、-U参数指定数据库用户名、-h参数指定主机名、-p参数指定端口号信息。
说明：
- 若未指定数据库名称，则使用初始化时默认生成的数据库名称；
- 若未指定数据库用户名，则默认使用当前操作系统用户作为数据库用户名；
- 当某个值没有前面的参数（-d、-U等）时，若连接的命令中没有指定数据库名（-d）则该参数会被解释成数据库名；
- 如果已经指定数据库名（-d）而没有指定数据库用户名（-U）时，该参数则会被解释成数据库用户名。
[omm@bogon ~]$ gsql postgres -r
gsql ((GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.2.在gsql中查看数据库的版本、pg基础版本和版权信息
openGauss=# select version();
version
------------------------------------------------------------------------------------------------------------------------------
openGauss 2.1.0 (GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release
(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.3.常见元命令的使用
openGauss=# \l --显示openGauss数据库集簇中，目前有哪些数据库
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
ipmp2 | omm | GBK | zh_CN.gbk | zh_CN.gbk |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
openGauss=# \conninfo --显示连接信息
You are connected to database "postgres" as user "omm" via socket in "/tmp" at port "25108".
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 --显示openGauss数据库集簇中，目前有哪些用户和角色
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
xxxxxxxx | | {}
xxxxxxxxxx| Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# \dg --显示openGauss数据库集簇中，目前有哪些用户和角色
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
xxxxxxxx | | {}
xxxxxxxxxx| Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# \db --显示openGauss数据库集簇中，目前有哪些表空间
List of tablespaces
Name | Owner | Location
------------------+----------+-----------------------------
pg_default | omm |
pg_global | omm |
(5 rows)
openGauss=# \dn --显示当前数据库有哪些数据库模式
List of schemas
Name | Owner
----------------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_application_info | omm
dbe_file | omm
dbe_lob | omm
dbe_match | omm
dbe_output | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_random | omm
dbe_raw | omm
dbe_scheduler | omm
dbe_session | omm
dbe_sql | omm
dbe_sql_util | omm
dbe_task | omm
dbe_utility | omm
pkg_service | omm
pkg_util | omm
public | omm
snapshot | omm
sqladvisor | omm
sys | omm
(25 rows)
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
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 --查看某个表的信息
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=# \d+ customer_t --查看某个表的信息
Table "public.customer_t"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+--------------+-----------+----------+--------------+-------------
c_customer_sk | integer | | plain | |
c_customer_id | character(5) | | extended | |
c_first_name | character(6) | | extended | |
c_last_name | character(8) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
openGauss=# create index idx_customer_id on customer_t(c_customer_id);
CREATE INDEX
openGauss=# \di --查看索引信息，元命令\di IndexName的作用是查看某个索引的信息
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
(1 row)
openGauss=# \pset border 2 --\pset命令以不同的方法显示表：
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 ----打开扩展表格式模式。
Expanded display is on.
openGauss=# SELECT * FROM customer_t;
-[ RECORD 1 ]-+---------
c_customer_sk | 3769
c_customer_id | 5
c_first_name | Grace
c_last_name | White 4.gsql中的事务：测试gsql中的默认事务自动提交功能
openGauss=# show autocommit;
autocommit
------------
on
(1 row)
openGauss=# create table customer_new as select * from customer_t;
INSERT 0 1
openGauss=# \q
[omm@bogon ~]$ gsql postgres -r
gsql ((GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release)
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中的事务手动提交功能
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
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;
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
[omm@bogon ~]$ gsql --help
gsql is the GaussDB Kernel 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)
-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进入gsql
\? --获取元数据相关信息
openGauss=# \?
General
\copyright show GaussDB Kernel 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\h --获取和SQL语法有关的帮助信息
openGauss=# \h
Available help:
ABORT CREATE DATA SOURCE DROP OPERATOR
ALTER APP WORKLOAD GROUP CREATE DATABASE DROP OWNED
ALTER APP WORKLOAD GROUP MAPPING CREATE DIRECTORY DROP PACKAGE
ALTER AUDIT POLICY CREATE EXTENSION DROP PACKAGE BODY
ALTER DATA SOURCE CREATE FOREIGN TABLE DROP PROCEDURE
ALTER DATABASE CREATE FUNCTION DROP PUBLICATION
ALTER DEFAULT PRIVILEGES CREATE GROUP DROP RESOURCE LABEL
ALTER DIRECTORY CREATE INDEX DROP RESOURCE POOL
ALTER EXTENSION CREATE MASKING POLICY DROP ROLE
ALTER FOREIGN TABLE CREATE MATERIALIZED VIEW DROP ROW LEVEL SECURITY POLICY
ALTER FOREIGN TABLE FOR HDFS CREATE MODEL DROP SCHEMA
ALTER FUNCTION CREATE NODE DROP SEQUENCE
ALTER GLOBAL CONFIGURATION CREATE NODE GROUP DROP SERVER
ALTER GROUP CREATE OPERATOR DROP SUBSCRIPTION
ALTER INDEX CREATE PACKAGE DROP SYNONYM课程作业
1.gsql命令连到数据库omm
[omm@bogon ~]$ gsql postgres -r
gsql ((GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.2.查看数据库的版本、版权信息
openGauss=# select version();
version
------------------------------------------------------------------------------------------------------------------
------------
openGauss 2.1.0 (GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8
336 release
(1 row)
openGauss=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.3.常见元命令使用
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(3 rows)
openGauss=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+---------------+-------+-------+----------------------------------
public | customer_new | table | omm | {orientation=row,compression=no}
public | customer_new2 | table | omm | {orientation=row,compression=no}
public | customer_t | table | omm | {orientation=row,compression=no}
(3 rows)
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------------+----------+-----------------------------
pg_default | omm |
pg_global | omm |
(2 rows)
4.使用两种方法，连到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=#
[omm@bogon ~]$ gsql postgres -r
gsql ((GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.5.测试gsql中的默认事务自动提交功能
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=# 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=# \q
[omm@bogon ~]$ gsql postgres -r
gsql ((GaussDB Kernel V500R002C10 build 5c627b28) compiled at 2022-08-23 00:04:55 commit 3841 last mr 8336 release)
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
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)
6.测试gsql中的事务手动提交功能
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=# \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=# rollback;
ROLLBACK
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)
7.了解gsql相关帮助
openGauss=# \?
General
\copyright show GaussDB Kernel 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
openGauss=# \h
Available help:
ABORT CREATE DATA SOURCE DROP OPERATOR
ALTER APP WORKLOAD GROUP CREATE DATABASE DROP OWNED
ALTER APP WORKLOAD GROUP MAPPING CREATE DIRECTORY DROP PACKAGE
ALTER AUDIT POLICY CREATE EXTENSION DROP PACKAGE BODY
ALTER DATA SOURCE CREATE FOREIGN TABLE DROP PROCEDURE
ALTER DATABASE CREATE FUNCTION DROP PUBLICATION
ALTER DEFAULT PRIVILEGES CREATE GROUP DROP RESOURCE LABEL
ALTER DIRECTORY CREATE INDEX DROP RESOURCE POOL
ALTER EXTENSION CREATE MASKING POLICY DROP ROLE
ALTER FOREIGN TABLE CREATE MATERIALIZED VIEW DROP ROW LEVEL SECURITY POLICY
ALTER FOREIGN TABLE FOR HDFS CREATE MODEL DROP SCHEMA
ALTER FUNCTION CREATE NODE DROP SEQUENCE
ALTER GLOBAL CONFIGURATION CREATE NODE GROUP DROP SERVER
ALTER GROUP CREATE OPERATOR DROP SUBSCRIPTION
ALTER INDEX CREATE PACKAGE DROP SYNONYM
ALTER LARGE OBJECT CREATE PACKAGE BODY DROP TABLE
ALTER MASKING POLICY CREATE PROCEDURE DROP TABLESPACE
ALTER MATERIALIZED VIEW CREATE PUBLICATION DROP TEXT SEARCH CONFIGURATION
ALTER NODE CREATE RESOURCE LABEL DROP TEXT SEARCH DICTIONARY
ALTER NODE GROUP CREATE RESOURCE POOL DROP TRIGGER
ALTER OPERATOR CREATE ROLE DROP TYPE
ALTER PACKAGE CREATE ROW LEVEL SECURITY POLICY DROP USER
ALTER PUBLICATION CREATE SCHEMA DROP VIEW
ALTER RESOURCE LABEL CREATE SEQUENCE DROP WEAK PASSWORD DICTIONARY
ALTER RESOURCE POOL CREATE SERVER DROP WORKLOAD GROUP
ALTER ROLE CREATE SNAPSHOT AS END
ALTER ROW LEVEL SECURITY POLICY CREATE SNAPSHOT FROM EXECUTE
ALTER SCHEMA CREATE SUBSCRIPTION EXECUTE DIRECT
ALTER SEQUENCE CREATE SYNONYM EXPLAIN
ALTER SERVER CREATE TABLE FETCH
openGauss=#
openGauss=# \q
[omm@bogon ~]$ gsql --help
gsql is the GaussDB Kernel 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)
-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: "local socket")
allow multi host IP address with comma separator in centralized cluster
-p, --port=PORT database server port (default: "25108")
-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 GaussDB Kernel
documentation.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
