第2天 | 学习openGauss客户端工具gsql的使用
学习目标
学习openGauss数据库客户端工具gsql的使用。
课程学习
gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。
1.使用gsql命令连接数据库
--使用omm用户连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。 su - omm gsql -r 或 gsql -d omm -p 5432 -r复制
2.在gsql中查看数据库的版本、pg基础版本和版权信息
select version();
show server_version;
\copyright
复制3.常见元命令的使用
--\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
\l
--\conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
\conninfo
--\c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
\c postgres
--\du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
\du
\dg
--\db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
\db
--\dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
\dn
--创建表
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
--插入数据
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
--\dt命令，命令\dt的作用是显示数据库中所有的表。
\dt
--\d TableName命令，元命令\d TableName的作用是查看某个表的信息。
\d customer_t
--\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
create index idx_customer_id on customer_t(c_customer_id);
\di
--可以用\pset命令以不同的方法显示表：
\pset border 2
SELECT * FROM customer_t;
--打开扩展表格式模式。
\x
复制4.gsql中的事务：测试gsql中的默认事务自动提交功能
--查看gsql中事务是否默认为自动提交
show AUTOCOMMIT;
--测试gsql中事务默认为自动提交功能
create table customer_new as select * from customer_t;
\q
--重新登录后看到之前创建的表customer_new：
gsql -d postgres -p 5432 -r
\dt
复制5.gsql中的事务：测试gsql中的事务手动提交功能
–测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
\set AUTOCOMMIT off
--插入一些数据
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');
--查看表中数据
select * from customer_t;
--执行回滚
ROLLBACK;
--检查是否回滚成功
SELECT * FROM customer_t;
复制6.gsql相关的帮助
--连接数据库时，可以使用如下命令获取帮助信息。
gsql --help
--\h获取和SQL语法有关的帮助信息
\h
--\? 获取和元命令有关的帮助信息
\？
复制课程作业
1.gsql命令连到数据库omm
2.查看数据库的版本、版权信息
3.常见元命令使用
4.使用两种方法，连到postgres数据库中
5.测试gsql中的默认事务自动提交功能
6.测试gsql中的事务手动提交功能
7.了解gsql相关帮助
Welcome to 墨天轮.
This is Web Terminal of modb.pro; Good Good Study, Day Day Up.
root@modb:~#
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=# 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)
omm=# show server_version;
server_version
----------------
9.2.4
(1 row)
omm=# \copyrigth
Invalid command \copyrigth. Try \? for help.
omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
omm=#
omm=# \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)
omm=#
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
omm=#
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=# \du
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
gaussdb | Sysadmin | {}
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=# openGauss=#
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
openGauss=#
openGauss=# \dn
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
gaussdb | gaussdb
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)
List of schemas
Name | Owner
-----------------+---------
blockchain | omm
cstore | omm
db4ai | omm
openGauss=#
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(# ) ;
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=#
openGauss=# \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=# \c postgres
openGauss=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
openGauss=# select * from customer_t;
(1 row)
openGauss=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
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)
openGauss=# \d customer_t
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
openGauss=# Table "public.customer_t"
Column | Type | Modifiers
---------------+--------------+-----------
c_customer_sk | integer | openGauss=#
openGauss=# create index idx_customer_id on
openGauss-# create index idx_customer_id on customer_t(c_customer_id);
'openGauss=# 'ERROR: syntax error at or near "cols"
LINE 1: create index idx_customer_id on stty cols 253
^
openGauss'#
openGauss'#
openGauss'# create index idx_customer_id on customer_t(c_customer_id);
openGauss'# \di
openGauss'# commit;
openGauss'# \c postgres
openGauss'# exit
openGauss'# \q
openGauss'# \q
openGauss'# \q
openGauss'# ^C
openGauss=# create index idx_customer_id on customer_t(c_customer_id);
ERROR: relation "idx_customer_id" already exists
openGauss=# \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-----------------+-------+-------+------------+---------
public | idx_customer_id | index | omm | customer_t |
(1 row)
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)
openGauss=# \x
Expanded display is on.
openGauss=# SELECT * FROM customer_t;
+---------------+----------+
openGauss=# +-[ RECORD 1 ]--+----------+
| c_customer_sk | 3769 |
| c_customer_id | 5 |
| c_first_name | Grace |
| c_last_name | White |
openGauss=# --查看gsql中事务是否默认为自动提交
openGauss=# show AUTOCOMMIT;
+-[ RECORD 1 ]----+
| autocommit | on |
+------------+----+
openGauss=# openGauss=# --测试gsql中事务默认为自动提交功能
create table customer_new as select * from customer_t;
ERROR: relation "customer_new" already exists in schema "public"
DETAIL: creating new table with existing name in the same schema
openGauss=# \q
omm@modb:~$
omm@modb:~$ --重新登录后看到之前创建的表customer_new：
-bash: $'--\351\207\215\346\226\260\347\231\273\345\275\225\345\220\216\347\234\213\345\210\260\344\271\213\345\211\215\345\210\233\345\273\272\347\232\204\350\241\250customer_new\357\274\232': command not found
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=# \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)
openGauss=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)
openGauss=# create table customer_new as select * from customer_t;
ERROR: relation "customer_new" already exists in schema "public"
DETAIL: creating new table with existing name in the same schema
openGauss=# drop table customer_new
openGauss-# ;
DROP TABLE
openGauss=# create table customer_new as select * from customer_t;
openGauss=# INSERT 0 1
openGauss=# \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=# \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)
openGauss=# gsql手动提交
openGauss-# #Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
openGauss-# #注意：此处设置ATUOCOMMIT必须用大写！
openGauss-#
openGauss-# \set AUTOCOMMIT off
openGauss-# --插入一些数据
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');
ERROR: syntax error at or near "gsql手动提交"
LINE 1: gsql手动提交
^
openGauss=# openGauss=# --查看表中数据
select * from customer_t;
ERROR: current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
openGauss=# openGauss=# --执行回滚
ROLLBACK;
ROLLBACK
openGauss=# --检查是否回滚成功
openGauss=# SELECT * FROM customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
openGauss=#
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;
openGauss=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
openGauss=# --连接数据库时，可以使用如下命令获取帮助信息。
openGauss=#
openGauss=# gsql --help
openGauss-# openGauss-# --\h获取和SQL语法有关的帮助信息
openGauss-#
openGauss-# \h
Available help:
ABORT ALTER SYSTEM KILL SESSION CREATE DATA SOURCE CREATE TABLE SUBPARTITION DROP NODE GROUP LOCK
ALTER APP WORKLOAD GROUP ALTER SYSTEM SET CREATE DATABASE CREATE TABLESPACE DROP OPERATOR MERGE
ALTER APP WORKLOAD GROUP MAPPING ALTER TABLE CREATE DIRECTORY CREATE TEXT SEARCH CONFIGURATION DROP OWNED MOVE
ALTER AUDIT POLICY ALTER TABLE PARTITION CREATE EXTENSION CREATE TEXT SEARCH DICTIONARY DROP PACKAGE PREDICT BY
ALTER DATA SOURCE ALTER TABLE SUBPARTITION CREATE FOREIGN TABLE CREATE TRIGGER DROP PACKAGE BODY PREPARE
ALTER DATABASE ALTER TABLESPACE CREATE FUNCTION CREATE TYPE DROP PROCEDURE PREPARE TRANSACTION
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH CONFIGURATION CREATE GROUP CREATE USER DROP PUBLICATION PUBLISH SNAPSHOT
ALTER DIRECTORY ALTER TEXT SEARCH DICTIONARY CREATE INDEX CREATE VIEW DROP RESOURCE LABEL PURGE
ALTER EXTENSION ALTER TRIGGER CREATE LANGUAGE CREATE WEAK PASSWORD DICTIONARY DROP RESOURCE POOL PURGE SNAPSHOT
ALTER FOREIGN TABLE ALTER TYPE CREATE MASKING POLICY CREATE WORKLOAD GROUP DROP ROLE REASSIGN OWNED
ALTER FOREIGN TABLE FOR HDFS ALTER USER CREATE MATERIALIZED VIEW CURSOR DROP ROW LEVEL SECURITY POLICY REFRESH MATERIALIZED VIEW
ALTER LARGE OBJECT ANONYMOUS BLOCK CREATE PACKAGE DROP APP WORKLOAD GROUP DROP SYNONYM ROLLBACK PREPARED
ALTER MASKING POLICY ARCHIVE SNAPSHOT CREATE PACKAGE BODY DROP APP WORKLOAD GROUP MAPPING DROP TABLE SAMPLE SNAPSHOT
ALTER MATERIALIZED VIEW BEGIN CREATE PROCEDURE DROP AUDIT POLICY DROP TABLESPACE SAVEPOINT
ALTER NODE CALL CREATE PUBLICATION DROP CLIENT MASTER KEY DROP TEXT SEARCH CONFIGURATION SELECT
ALTER FUNCTION ALTER VIEW CREATE MODEL DEALLOCATE DROP SCHEMA REINDEX
ALTER GLOBAL CONFIGURATION ALTER WORKLOAD GROUP CREATE NODE DECLARE DROP SEQUENCE RESET
ALTER GROUP ANALYSE CREATE NODE GROUP DELETE DROP SERVER REVOKE
ALTER INDEX ANALYZE CREATE OPERATOR DO DROP SUBSCRIPTION ROLLBACK
ALTER NODE GROUP CHECKPOINT CREATE RESOURCE LABEL DROP COLUMN ENCRYPTION KEY DROP TEXT SEARCH DICTIONARY SELECT INTO
ALTER OPERATOR CLEAN CONNECTION CREATE RESOURCE POOL DROP DATA SOURCE DROP TRIGGER SET
ALTER PACKAGE CLOSE CREATE ROLE DROP DATABASE DROP TYPE SET CONSTRAINTS
ALTER PUBLICATION CLUSTER CREATE ROW LEVEL SECURITY POLICY DROP DIRECTORY DROP USER SET ROLE
--More--
Most commands optionally preceded by integer argument k. Defaults in brackets.
Star (*) indicates argument becomes new default.
-------------------------------------------------------------------------------
<space> Display next k lines of text [current screen size]
z Display next k lines of text [current screen size]*
<return> Display next k lines of text [1]*
d or ctrl-D Scroll k lines [current scroll size, initially 11]*
q or Q or <interrupt> Exit from more
s Skip forward k lines of text [1]
f Skip forward k screenfuls of text [1]
b or ctrl-B Skip backwards k screenfuls of text [1]
' Go to place where previous search started
= Display current line number
/<regular expression> Search for kth occurrence of regular expression [1]
n Search for kth occurrence of last r.e [1]
!<cmd> or :!<cmd> Execute <cmd> in a subshell
v Start up /usr/bin/vi at current line
ctrl-L Redraw screen
:n Go to kth next file [1]
:p Go to kth previous file [1]
:f Display current file name and line number
. Repeat previous command
-------------------------------------------------------------------------------
ALTER RESOURCE LABEL COMMENT CREATE SCHEMA DROP EXTENSION DROP VIEW SET SESSION AUTHORIZATION
ALTER RESOURCE POOL COMMIT CREATE SEQUENCE DROP FOREIGN TABLE DROP WEAK PASSWORD DICTIONARY SET TRANSACTION
ALTER ROLE COMMIT PREPARED CREATE SERVER DROP FUNCTION DROP WORKLOAD GROUP SHOW
ALTER ROW LEVEL SECURITY POLICY COPY CREATE SNAPSHOT AS DROP GLOBAL CONFIGURATION END START TRANSACTION
ALTER SCHEMA CREATE APP WORKLOAD GROUP CREATE SNAPSHOT FROM DROP GROUP EXECUTE TIMECAPSULE TABLE
ALTER SEQUENCE CREATE APP WORKLOAD GROUP MAPPING CREATE SUBSCRIPTION DROP INDEX EXECUTE DIRECT TRUNCATE
ALTER SERVER CREATE AUDIT POLICY CREATE SYNONYM DROP MASKING POLICY EXPLAIN UPDATE
ALTER SESSION CREATE BARRIER CREATE TABLE DROP MATERIALIZED VIEW FETCH VACUUM
ALTER SUBSCRIPTION CREATE CLIENT MASTER KEY CREATE TABLE AS DROP MODEL GRANT VALUES
ALTER SYNONYM CREATE COLUMN ENCRYPTION KEY CREATE TABLE PARTITION DROP NODE INSERT
openGauss-#
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
(arg: 1) )信息
openGauss-#
openGauss-# \？
Invalid command \？. Try \? for help.
openGauss-#
openGauss-# \q
omm@modb:~$ 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)
-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,
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-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
--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: -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)
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: "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.
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=# \h
Available help:
ABORT ALTER SYSTEM KILL SESSION CREATE DATA SOURCE CREATE TABLE SUBPARTITION DROP NODE GROUP LOCK
ALTER APP WORKLOAD GROUP ALTER SYSTEM SET CREATE DATABASE CREATE TABLESPACE DROP OPERATOR MERGE
ALTER APP WORKLOAD GROUP MAPPING ALTER TABLE CREATE DIRECTORY CREATE TEXT SEARCH CONFIGURATION DROP OWNED MOVE
ALTER AUDIT POLICY ALTER TABLE PARTITION CREATE EXTENSION CREATE TEXT SEARCH DICTIONARY DROP PACKAGE PREDICT BY
ALTER DATA SOURCE ALTER TABLE SUBPARTITION CREATE FOREIGN TABLE CREATE TRIGGER DROP PACKAGE BODY PREPARE
ALTER EXTENSION ALTER TRIGGER CREATE LANGUAGE CREATE WEAK PASSWORD DICTIONARY DROP RESOURCE POOL PURGE SNAPSHOT
ALTER DIRECTORY ALTER TEXT SEARCH DICTIONARY CREATE INDEX CREATE VIEW DROP RESOURCE LABEL PURGE
ALTER DATABASE ALTER TABLESPACE CREATE FUNCTION CREATE TYPE DROP PROCEDURE PREPARE TRANSACTION
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH CONFIGURATION CREATE GROUP CREATE USER DROP PUBLICATION PUBLISH SNAPSHOT
ALTER FOREIGN TABLE ALTER TYPE CREATE MASKING POLICY CREATE WORKLOAD GROUP DROP ROLE REASSIGN OWNED
ALTER FOREIGN TABLE FOR HDFS ALTER USER CREATE MATERIALIZED VIEW CURSOR DROP ROW LEVEL SECURITY POLICY REFRESH MATERIALIZED VIEW
ALTER FUNCTION ALTER VIEW CREATE MODEL DEALLOCATE DROP SCHEMA REINDEX
ALTER GLOBAL CONFIGURATION ALTER WORKLOAD GROUP CREATE NODE DECLARE DROP SEQUENCE RESET
ALTER GROUP ANALYSE CREATE NODE GROUP DELETE DROP SERVER REVOKE
ALTER INDEX ANALYZE CREATE OPERATOR DO DROP SUBSCRIPTION ROLLBACK
DROP DATA SOURCE DROP TRIGGER SET
ALTER LARGE OBJECT ANONYMOUS BLOCK CREATE PACKAGE DROP APP WORKLOAD GROUP DROP SYNONYM ROLLBACK PREPARED
ALTER MASKING POLICY ARCHIVE SNAPSHOT CREATE PACKAGE BODY DROP APP WORKLOAD GROUP MAPPING DROP TABLE SAMPLE SNAPSHOT
ALTER MATERIALIZED VIEW BEGIN CREATE PROCEDURE DROP AUDIT POLICY DROP TABLESPACE SAVEPOINT
ALTER NODE CALL CREATE PUBLICATION DROP CLIENT MASTER KEY DROP TEXT SEARCH CONFIGURATION SELECT
ALTER NODE GROUP CHECKPOINT CREATE RESOURCE LABEL DROP COLUMN ENCRYPTION KEY DROP TEXT SEARCH DICTIONARY SELECT INTO
ALTER OPERATOR CLEAN CONNECTION CREATE RESOURCE POOL ALTER PACKAGE CLOSE CREATE ROLE DROP DATABASE DROP TYPE SET CONSTRAINTS
--More--
Most commands optionally preceded by integer argument k. Defaults in brackets.
Star (*) indicates argument becomes new default.
-------------------------------------------------------------------------------
<space> Display next k lines of text [current screen size]
z Display next k lines of text [current screen size]*
<return> Display next k lines of text [1]*
d or ctrl-D Scroll k lines [current scroll size, initially 11]*
q or Q or <interrupt> Exit from more
s Skip forward k lines of text [1]
f Skip forward k screenfuls of text [1]
b or ctrl-B Skip backwards k screenfuls of text [1]
' Go to place where previous search started
= Display current line number
/<regular expression> Search for kth occurrence of regular expression [1]
n Search for kth occurrence of last r.e [1]
!<cmd> or :!<cmd> Execute <cmd> in a subshell
v Start up /usr/bin/vi at current line
ctrl-L Redraw screen
:n Go to kth next file [1]
:p Go to kth previous file [1]
:f Display current file name and line number
. Repeat previous command
-------------------------------------------------------------------------------
ALTER PUBLICATION CLUSTER CREATE ROW LEVEL SECURITY POLICY DROP DIRECTORY DROP USER SET ROLE
ALTER RESOURCE LABEL COMMENT CREATE SCHEMA DROP EXTENSION DROP VIEW SET SESSION AUTHORIZATION
ALTER RESOURCE POOL COMMIT CREATE SEQUENCE DROP FOREIGN TABLE DROP WEAK PASSWORD DICTIONARY SET TRANSACTION
ALTER ROLE COMMIT PREPARED CREATE SERVER DROP FUNCTION DROP WORKLOAD GROUP SHOW
ALTER ROW LEVEL SECURITY POLICY COPY CREATE SNAPSHOT AS DROP GLOBAL CONFIGURATION END START TRANSACTION
ALTER SCHEMA CREATE APP WORKLOAD GROUP CREATE SNAPSHOT FROM DROP GROUP EXECUTE TIMECAPSULE TABLE
ALTER SEQUENCE CREATE APP WORKLOAD GROUP MAPPING CREATE SUBSCRIPTION DROP INDEX EXECUTE DIRECT TRUNCATE
ALTER SERVER CREATE AUDIT POLICY CREATE SYNONYM DROP MASKING POLICY EXPLAIN UPDATE
ALTER SESSION CREATE BARRIER CREATE TABLE DROP MATERIALIZED VIEW FETCH VACUUM
ALTER SUBSCRIPTION CREATE CLIENT MASTER KEY CREATE TABLE AS DROP MODEL GRANT VALUES
ALTER SYNONYM CREATE COLUMN ENCRYPTION KEY CREATE TABLE PARTITION DROP NODE INSERT
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
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
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\ir FILE as \i, but relative to location of current script
\ir+ FILE KEY as \i+, but relative to location of current script
\i FILE execute commands from file
\i+ FILE KEY execute commands from encrypted file
--More-- \r reset (clear) the query buffer
\w FILE write query buffer to file
Input/Output
\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
\dg[+] [PATTERN] list roles
--More-- \dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
--More-- \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[+] 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 "postgres")
\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
openGauss=#
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
