openGauss每日一练第2天
今天的学习目标：openGauss数据库客户端工具gsql的使用
1. gsql 命令连接数据库
gsql -d
-p -U -W <密码> -r \l 查看数据库信息
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=# \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
omm=# (4 rows)
复制2. 在gsql中查看数据库的版本、pg基础版本和版权信息
2.1 gsql中查看数据库的版本
select version();
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++ (G
CC) 7.3.0, 64-bit
(1 row)
复制2.2 gsql中查看pg基础版本信息
show server_version;
omm=# show server_version;
server_version
----------------
9.2.4
(1 row)
复制2.3 gsql中查看数据库的版权信息
\copyright
omm=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
omm=#
复制3. 常见元命令的使用
3.1\l命令，元命令\l的作用是显示openGauss数据库集簇中，目前有哪些数据库。
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=# | | | | | omm=CTc/omm
复制3.2 \conninfo命令，元命令\conninfo的作用是在gsql中，显示会话的连接信息。
omm=# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
复制3.3 \c[onnect] [DBNAME]命令，元命令\ c[onnect] [DBNAME]的作用是在gsql中，切换连接的数据库postgres。
omm=# \c postgres
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
复制3.4 du命令和\dg命令，元命令\dg命令与元命令\du命令的作用类似，都是显示openGauss数据库集簇中，目前有哪些用户和角色。
openGauss=# \du
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------------------------------------------------------------+------
-----
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
openGauss=# \dg
gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
List of roles
Role name | Attributes | Membe
r of
-----------+------------------------------------------------------------------------------------------------------------------+------
-----
openGauss=#
复制3.5 \db命令，元命令\db的作用是显示openGauss数据库集簇中，目前有哪些表空间。
openGauss=# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
复制3.6 \dn命令，元命令\dn的作用是显示当前数据库有哪些数据库模式。
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=#
复制3.7 表操作
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 varchar(10)
openGauss(# );
ERROR: relation "customer_t" already exists in schema "public"
DETAIL: creating new table with existing name in the same schema
openGauss=# insert into customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name)
values(1,'1','zhang','zhangsan');
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=#
openGauss=# \d
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
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 |
| 3769 | 5 | Grace | White |
| 1 | 1 | zhang | zhangsan |
+---------------+---------------+--------------+-------------+
(3 rows)
openGauss=# \x
Expanded display is on.
复制4. gsql中的事务：测试gsql中的默认事务自动提交功能
查看gsql中事务是否为自动提交
//查看事务是否为自动提交
openGauss=# show AUTOCOMMIT;
+-[ RECORD 1 ]----+
| autocommit | on |
+------------+----+
openGauss=# create table customer_new as select * from customer_t ;
INSERT 0 3
openGauss=# \q
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=# \dt
No relations found.
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=# \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=#
复制5.gsql中的事务：测试gsql中的事务手动提交功能
\set AUTOCOMMIT off //设置事务提交为手动
ROLLBACK //回滚事务
openGauss=# \set AUTOCOMMIT off
openGauss=# select * from customer_t ;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
3769 | 5 | Grace | White
1 | 1 | zhang | zhangsan
(3 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=# 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'),
openGauss=# select * from customer_t ;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
3769 | 5 | Grace | White
1 | 1 | zhang | zhangsan
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(7 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
3769 | 5 | Grace | White
1 | 1 | zhang | zhangsan
(3 rows)
openGauss=#
复制6. gsql的相关帮助
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, --no-libedit disable enhanced command line editing (libedit)
-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)
-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 -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)
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.
-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
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=# \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 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 FOREIGN TABLE CREATE MASKING POLICY DROP ROLE
ALTER FOREIGN TABLE FOR HDFS CREATE MATERIALIZED VIEW DROP ROW LEVEL SECURITY POLICY
ALTER NODE GROUP CREATE RESOURCE LABEL DROP TEXT SEARCH DICTIONARY
ALTER OPERATOR CREATE RESOURCE POOL DROP TRIGGER
ALTER PACKAGE CREATE ROLE DROP TYPE
--More-- ALTER MATERIALIZED VIEW CREATE PROCEDURE DROP TABLESPACE
ALTER NODE CREATE PUBLICATION DROP TEXT SEARCH CONFIGURATION
ALTER PUBLICATION CREATE ROW LEVEL SECURITY POLICY DROP USER
ALTER RESOURCE LABEL CREATE SCHEMA DROP VIEW
ALTER RESOURCE POOL CREATE SEQUENCE DROP WEAK PASSWORD DICTIONARY
ALTER ROLE CREATE SERVER DROP WORKLOAD GROUP
ALTER ROW LEVEL SECURITY POLICY CREATE SNAPSHOT AS END
ALTER SCHEMA CREATE SNAPSHOT FROM EXECUTE
ALTER SEQUENCE CREATE SUBSCRIPTION EXECUTE DIRECT
ALTER SERVER CREATE SYNONYM EXPLAIN
ALTER SESSION CREATE TABLE FETCH
ALTER SUBSCRIPTION CREATE TABLE AS GRANT
ALTER SYNONYM CREATE TABLE PARTITION INSERT
ALTER SYSTEM KILL SESSION CREATE TABLE SUBPARTITION LOCK
ALTER SYSTEM SET CREATE TABLESPACE MERGE
ALTER TABLE CREATE TEXT SEARCH CONFIGURATION MOVE
ALTER TABLE PARTITION CREATE TEXT SEAR ALTER TEXT SEARCH CONFIGURATION CREATE USER PUBLISH SNAPSHOT
ALTER TEXT SEARCH DICTIONARY CREATE VIEW PURGE
CH DICTIONARY PREDICT BY
ALTER TABLE SUBPARTITION CREATE TRIGGER PREPARE
ALTER TABLESPACE CREATE TYPE PREPARE TRANSACTION
ALTER USER CURSOR REFRESH MATERIALIZED VIEW
ALTER VIEW DEALLOCATE REINDEX
--More-- ALTER TRIGGER CREATE WEAK PASSWORD DICTIONARY PURGE SNAPSHOT
ALTER TYPE CREATE WORKLOAD GROUP REASSIGN OWNED
ALTER WORKLOAD GROUP DECLARE RESET
ANALYSE DELETE REVOKE
ANALYZE DO ROLLBACK
ANONYMOUS BLOCK DROP APP WORKLOAD GROUP ROLLBACK PREPARED
ARCHIVE SNAPSHOT DROP APP WORKLOAD GROUP MAPPING SAMPLE SNAPSHOT
BEGIN DROP AUDIT POLICY SAVEPOINT
CALL DROP CLIENT MASTER KEY SELECT
CHECKPOINT DROP COLUMN ENCRYPTION KEY SELECT INTO
CLEAN CONNECTION DROP DATA SOURCE SET
CLOSE DROP DATABASE SET CONSTRAINTS
CLUSTER DROP DIRECTORY SET ROLE
COMMENT DROP EXTENSION SET SESSION AUTHORIZATION
COMMIT DROP FOREIGN TABLE SET TRANSACTION
COMMIT PREPARED DROP FUNCTION SHOW
COPY DROP GLOBAL CONFIGURATION START TRANSACTION
CREATE APP WORKLOAD GROUP DROP GROUP TIMECAPSULE TABLE
CREATE APP WORKLOAD GROUP MAPPING DROP INDEX TRUNCATE
CREATE AUDIT POLICY DROP MASKING POLICY UPDATE
CREATE BARRIER DROP MATERIALIZED VIEW VACUUM
CREATE CLIENT MASTER KEY DROP MODEL VALUES
CREATE COLUMN ENCRYPTION KEY DROP NODE
omm=# \?
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
\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
\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
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\qecho [STRING] write string to query output stream (see \o)
--More-- \echo [STRING] write string to standard output
\i FILE execute commands from file
复制「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
