1.gsql命令连到数据库omm
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.
omm=# gsql -d 0mm -p 5432 -r
omm-#
2.查看数据库的版本、版权信息
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=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
omm=#
3.常见元命令使用
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=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
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
List of ro
les
Role name | Attribut
es | Member of
-----------+------------------------------------------------------------
------------------------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer a
udit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# \dg
List of ro
les
Role name | Attribut
es | Member of
-----------+------------------------------------------------------------
------------------------------------------------------+-----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer a
udit, 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=# 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
openGauss=# 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=# create index idx_customer_id on customer_t(c_customer_id);
CREATE INDEX
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.
4.使用两种方法，连到postgres数据库中
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.
5.测试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.
openGauss=# create table customer_new as select * from customer_t;
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=#
6.测试gsql中的事务手动提交功能
openGauss=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
(6885, 1, 'Joes', 'Hunter'),
openGauss-# 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)
openGauss=#
7.了解gsql相关帮助
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
-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
-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: "|") -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: "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 -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=# \h
Available help:
ABORT CREATE TABLE SUBPARTITION
ALTER APP WORKLOAD GROUP CREATE TABLESPACE
ALTER APP WORKLOAD GROUP MAPPING CREATE TEXT SEARCH CONFIGURATION
ALTER AUDIT POLICY CREATE TEXT SEARCH DICTIONARY
ALTER DATA SOURCE CREATE TRIGGER
ALTER EXTENSION CREATE WEAK PASSWORD DICTIONARY
ALTER FOREIGN TABLE CREATE WORKLOAD GROUP
ALTER FOREIGN TABLE FOR HDFS CURSOR
ALTER FUNCTION DEALLOCATE
ALTER GLOBAL CONFIGURATION DECLARE
ALTER GROUP DELETE
ALTER INDEX DO
ALTER LARGE OBJECT DROP APP WORKLOAD GROUP
ALTER MASKING POLICY DROP APP WORKLOAD GROUP MAPPING
ALTER DATABASE CREATE TYPE
ALTER DEFAULT PRIVILEGES CREATE USER
ALTER DIRECTORY CREATE VIEW
ALTER MATERIALIZED VIEW DROP AUDIT POLICY
ALTER NODE DROP CLIENT MASTER KEY
ALTER NODE GROUP DROP COLUMN ENCRYPTION KEY
ALTER OPERATOR DROP DATA SOURCE
ALTER PACKAGE DROP DATABASE
ALTER PUBLICATION DROP DIRECTORY
ALTER RESOURCE LABEL DROP EXTENSION
ALTER RESOURCE POOL DROP FOREIGN TABLE
ALTER ROLE DROP FUNCTION
ALTER ROW LEVEL SECURITY POLICY DROP GLOBAL CONFIGURATION
ALTER SCHEMA DROP GROUP
ALTER SEQUENCE DROP INDEX
ALTER SERVER DROP MASKING POLICY
ALTER SESSION DROP MATERIALIZED VIEW
ALTER SUBSCRIPTION DROP MODEL
ALTER SYNONYM DROP NODE
ALTER SYSTEM KILL SESSION DROP NODE GROUP
ALTER SYSTEM SET DROP OPERATOR
ALTER TABLE DROP OWNED
ALTER TABLE PARTITION DROP PACKAGE
ALTER TABLE SUBPARTITION DROP PACKAGE BODY
ALTER TABLESPACE DROP PROCEDURE
ALTER VIEW DROP SCHEMA
--More-- ALTER TEXT SEARCH CONFIGURATION DROP PUBLICATION
ALTER TEXT SEARCH DICTIONARY DROP RESOURCE LABEL
ALTER TRIGGER DROP RESOURCE POOL
ALTER TYPE DROP ROLE
ALTER USER DROP ROW LEVEL SECURITY POLICY
ALTER WORKLOAD GROUP DROP SEQUENCE
ANALYSE DROP SERVER
ANALYZE DROP SUBSCRIPTION
ANONYMOUS BLOCK DROP SYNONYM
ARCHIVE SNAPSHOT DROP TABLE
BEGIN DROP TABLESPACE
CALL DROP TEXT SEARCH CONFIGURATION
CHECKPOINT DROP TEXT SEARCH DICTIONARY
CLEAN CONNECTION DROP TRIGGER
CLOSE DROP TYPE
CLUSTER DROP USER
COMMENT DROP VIEW
COMMIT DROP WEAK PASSWORD DICTIONARY
COMMIT PREPARED DROP WORKLOAD GROUP
COPY END
CREATE APP WORKLOAD GROUP EXECUTE
CREATE APP WORKLOAD GROUP MAPPING EXECUTE DIRECT
CREATE AUDIT POLICY EXPLAIN
CREATE BARRIER FETCH
--More-- CREATE CLIENT MASTER KEY GRANT
CREATE COLUMN ENCRYPTION KEY INSERT
CREATE DATA SOURCE LOCK
CREATE DATABASE MERGE
CREATE DIRECTORY MOVE
CREATE EXTENSION PREDICT BY
CREATE FOREIGN TABLE PREPARE
--More-- CREATE FUNCTION PREPARE TRANSACTION
CREATE GROUP PUBLISH SNAPSHOT
CREATE INDEX PURGE
CREATE LANGUAGE PURGE SNAPSHOT
CREATE MASKING POLICY REASSIGN OWNED
CREATE MATERIALIZED VIEW REFRESH MATERIALIZED VIEW
CREATE MODEL REINDEX
CREATE NODE RESET
CREATE NODE GROUP REVOKE
CREATE OPERATOR ROLLBACK
CREATE PACKAGE ROLLBACK PREPARED
CREATE PACKAGE BODY SAMPLE SNAPSHOT
CREATE PROCEDURE SAVEPOINT
CREATE PUBLICATION SELECT
CREATE RESOURCE LABEL SELECT INTO
CREATE RESOURCE POOL SET
CREATE ROLE SET CONSTRAINTS
CREATE ROW LEVEL SECURITY POLICY SET ROLE
CREATE SCHEMA SET SESSION AUTHORIZATION
CREATE SEQUENCE SET TRANSACTION
CREATE SERVER SHOW
CREATE SNAPSHOT AS START TRANSACTION
CREATE SNAPSHOT FROM TIMECAPSULE TABLE
CREATE SUBSCRIPTION TRUNCATE
CREATE SYNONYM UPDATE
CREATE TABLE VACUUM
CREATE TABLE AS VALUES
CREATE TABLE PARTITION
omm=# \?
General
\copyright show openGauss usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pip
e)
\h(\help) [NAME] help on syntax of SQL commands, * for al
l 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 e
ditor
\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
\i+ FILE KEY execute commands from encrypted file
--More-- \echo [STRING] write string to standard output
\i FILE execute commands from file
\ir FILE as \i, but relative to location of current scri
pt
\ir+ FILE KEY as \i+, but relative to location of current scr
ipt
\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 elsewher
e
\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
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dp [PATTERN] list table, view, and sequence access privilege
s
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\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 mod
e
\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
nly|title|tableattr|pager|
feedback})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if no
ne
\x [on|off|auto] toggle expanded output (currently off)
(NAME := {format|border|expanded|fieldsep|field
sep_zero|footer|null|
numericlocale|recordsep|recordsep_zero|tuples_o
Connection
\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "omm")
\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 s
hell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parame
ters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
omm=#
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
