openGauss数据库gisql使用
本次课程主要练习以下内容：
1.gsql命令连到数据库omm
2.查看数据库的版本、版权信息
3.常见元命令使用
4.使用两种方法，连到postgres数据库中
5.测试gsql中的默认事务自动提交功能
6.测试gsql中的事务手动提交功能
7.了解gsql相关帮助
练习操作：
1.gsql命令连到数据库omm
两种连接方式，一种是直接使用gsql-r直接连接，另一种是指定了数据库和端口
参数说明：
-d 指定数据库
-p 数据库端口
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=# \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=# select version();
2.查看数据库的版本、版权信息
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=# \copyright
GaussDB Kernel Database Management System
Copyright (c) Huawei Technologies Co., Ltd. 2018. All rights reserved.
3.常见元命令使用
\l 查看数据库
\d 查看表
\dt 查看表
\du 查看用户
openGauss=# \du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------------------------------------------------------------
+-----------
gaussdb | 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 |
(2 rows)
omm=# \l
template0 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
omm=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------------+-------+-------+----------------------------------
public | customer_t | table | omm | {orientation=row,compression=no}
(1 row)
omm=# \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) |
以下是所有的元命令使用帮助，可以使用\？来显示
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[+] 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
以下是帮助信息，可以使用\h来显示
Available help:
ABORT ALTER SYSTEM KILL SESSION CREATE DATABASE CREATE TEXT SEARCH CONFIGURATION DROP PACKAGE PREPARE
ALTER APP WORKLOAD GROUP ALTER SYSTEM SET CREATE DIRECTORY CREATE TEXT SEARCH DICTIONARY DROP PACKAGE BODY PREPARE TRANSACTION
ALTER APP WORKLOAD GROUP MAPPING ALTER TABLE CREATE EXTENSION CREATE TRIGGER DROP PROCEDURE PUBLISH SNAPSHOT
ALTER AUDIT POLICY ALTER TABLE PARTITION CREATE FOREIGN TABLE CREATE TYPE DROP PUBLICATION PURGE
ALTER DATA SOURCE ALTER TABLE SUBPARTITION CREATE FUNCTION CREATE USER DROP RESOURCE LABEL PURGE SNAPSHOT
ALTER DATABASE ALTER TABLESPACE CREATE GROUP CREATE VIEW DROP RESOURCE POOL REASSIGN OWNED
ALTER DEFAULT PRIVILEGES ALTER TEXT SEARCH CONFIGURATION CREATE INDEX CREATE WEAK PASSWORD DICTIONARY DROP ROLE REFRESH MATERIALIZED VIEW
ALTER DIRECTORY ALTER TEXT SEARCH DICTIONARY CREATE LANGUAGE CREATE WORKLOAD GROUP DROP ROW LEVEL SECURITY POLICY REINDEX
ALTER EXTENSION ALTER TRIGGER CREATE MASKING POLICY CURSOR DROP SCHEMA REPLACE
ALTER FOREIGN TABLE ALTER TYPE CREATE MATERIALIZED VIEW DEALLOCATE DROP SEQUENCE RESET
ALTER FOREIGN TABLE FOR HDFS ALTER USER CREATE MODEL DECLARE DROP SERVER REVOKE
ALTER FUNCTION ALTER VIEW CREATE NODE DELETE DROP SUBSCRIPTION ROLLBACK
ALTER GLOBAL CONFIGURATION ALTER WORKLOAD GROUP CREATE NODE GROUP DO DROP SYNONYM ROLLBACK PREPARED
ALTER GROUP ANALYSE CREATE OPERATOR DROP APP WORKLOAD GROUP DROP TABLE SAMPLE SNAPSHOT
ALTER INDEX ANALYZE CREATE PACKAGE DROP APP WORKLOAD GROUP MAPPING DROP TABLESPACE SAVEPOINT
ALTER LARGE OBJECT ANONYMOUS BLOCK CREATE PACKAGE BODY DROP AUDIT POLICY DROP TEXT SEARCH CONFIGURATION SELECT
ALTER MASKING POLICY ARCHIVE SNAPSHOT CREATE PROCEDURE DROP CLIENT MASTER KEY DROP TEXT SEARCH DICTIONARY SELECT INTO
ALTER MATERIALIZED VIEW BEGIN CREATE PUBLICATION DROP COLUMN ENCRYPTION KEY DROP TRIGGER SET
ALTER NODE CALL CREATE RESOURCE LABEL DROP DATA SOURCE DROP TYPE SET CONSTRAINTS
ALTER NODE GROUP CHECKPOINT CREATE RESOURCE POOL DROP DATABASE DROP USER SET ROLE
ALTER OPERATOR CLEAN CONNECTION CREATE ROLE DROP DIRECTORY DROP VIEW SET SESSION AUTHORIZATION
ALTER PACKAGE CLOSE CREATE ROW LEVEL SECURITY POLICY DROP EXTENSION DROP WEAK PASSWORD DICTIONARY SET TRANSACTION
ALTER PROCEDURE CLUSTER CREATE SCHEMA DROP FOREIGN TABLE DROP WORKLOAD GROUP SHOW
ALTER PUBLICATION COMMENT CREATE SEQUENCE DROP FUNCTION END START TRANSACTION
ALTER RESOURCE LABEL COMMIT CREATE SERVER DROP GLOBAL CONFIGURATION EXECUTE TIMECAPSULE TABLE
ALTER RESOURCE POOL COMMIT PREPARED CREATE SNAPSHOT AS DROP GROUP EXECUTE DIRECT TRUNCATE
ALTER ROLE COPY CREATE SNAPSHOT FROM DROP INDEX EXPLAIN UPDATE
ALTER ROW LEVEL SECURITY POLICY CREATE APP WORKLOAD GROUP CREATE SUBSCRIPTION DROP MASKING POLICY FETCH VACUUM
ALTER SCHEMA CREATE APP WORKLOAD GROUP MAPPING CREATE SYNONYM DROP MATERIALIZED VIEW GRANT VALUES
ALTER SEQUENCE CREATE AUDIT POLICY CREATE TABLE DROP MODEL INSERT
ALTER SERVER CREATE BARRIER CREATE TABLE AS DROP NODE LOCK
ALTER SESSION CREATE CLIENT MASTER KEY CREATE TABLE PARTITION DROP NODE GROUP MERGE
ALTER SUBSCRIPTION CREATE COLUMN ENCRYPTION KEY CREATE TABLE SUBPARTITION DROP OPERATOR MOVE
ALTER SYNONYM CREATE DATA SOURCE CREATE TABLESPACE DROP OWNED PREDICT BY
4.使用两种方法，连到postgres数据库中
第一种是在系统中指定数据库的名称连接，第二种是在连接上数据库后使用\c dbname来连接
penGauss=# \c omm
omm=# Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=#
omm=#
5.测试gsql中的默认事务自动提交功能
openGauss=# show AUTOCOMMIT;
autocommit
------------
on
(1 row)
openGauss=# create table customer_new as select * from customer_t;
INSERT 0 1
openGauss=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 'L' THEN 'large sequence' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view' WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c.reloptions as "Storage"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'db4ai'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND c.relname not like 'matviewmap\_%'
AND c.relname not like 'mlog\_%'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
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)
6.测试gsql中的事务手动提交功能
这个不是很理解，照着实验做的，后面再看下相关文档深入了解下
omm=# \set AUTOCOMMIT off
omm=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# 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)
omm=# ROLLBACK;
omm=# ROLLBACK
omm=#
omm=#
omm=# SELECT * FROM customer_t;
omm=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
3769 | 5 | Grace | White
(1 row)
7.了解gsql相关帮助
在元命令中已经说明了，使用?和\h来查看帮助信息。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
