课程作业 1.gsql命令连到数据库omm 2.查看数据库的版本、版权信息 3.常见元命令使用 4.使用两种方法，连到postgres数据库中 5.测试gsql中的默认事务自动提交功能 6.测试gsql中的事务手动提交功能 7.了解gsql相关帮助
gsql 是 opengauss 的客户端工具(命令行)
1、连接数据库
# 连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。
su - omm
gsql -r
或
gsql -d omm -p 5432 -r
复制
root@modb:~# su - 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=# \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=#
2、查看DB版本和PG版本及版权
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.
3、常用命令
# 显示openGauss数据库集群中，目前有哪些数据库,\l
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)
# 显示会话的连接信息,\conninfo
omm-# \conninfo
You are connected to database "omm" as user "omm" via socket in "/tmp" at port "5432".
# 切换连接的数据库,\c[onnect] [DBNAME]
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-#
# 显示openGauss数据库集簇中，目前有哪些用户和角色，\dg与\du
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-# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
openGauss-# gaussdb | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
# 显示openGauss数据库集簇中，目前有哪些表空间，\db
openGauss-# \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | omm |
pg_global | omm |
(2 rows)
# 显示当前数据库有哪些数据库模式，\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)
--创建表
CREATE TABLE customer_jeff
(c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
);
CREATE TABLE
--插入数据
omm=# INSERT INTO customer_jeff(c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');
INSERT 0 1
omm=# commit;
WARNING: there is no transaction in progress
COMMIT
omm=#
–\dt,显示数据库中所有的表。
\dt
–\d TableName,查看某个表的信息。
\d customer_jeff
–\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
create index idx_customer_id on customer_jeff(c_customer_id);
\di idx_customer_id
–可以用\pset命令以不同的方法显示表：
\pset border 0 :表示输出内容无边框
\pset border 1 :表示边框只在内部
\pset border 2 :表示内外都有边框,≥2
SELECT * FROM customer_jeff;
–打开扩展表格式模式。
\x
4、测试事务自动提交功能
omm=# create table auto_commit_test as SELECT * FROM customer_jeff;
omm=# INSERT 0 1
omm=# exit
omm-# \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.
omm=# \dt
public | auto_commit_test | table | omm | {orientation=row,compression=no}
public | customer_jeff | table | omm | {orientation=row,compression=no}
(2 rows)
omm=# List of relations
Schema | Name | Type | Owner | Storage
--------+------------------+-------+-------+----------------------------------
5、测试事务手动提交功能
-- 没发现\set autocommit off设置有什么效果，事务还是提交了，rollback不成功，新session还是可以看到数据。。。
6、gsql 帮助
omm=# \h
Available help:
ABORT ALTER TEXT SEARCH DICTIONARY CREATE OPERATOR DROP DATABASE EXECUTE DIRECT
ALTER APP WORKLOAD GROUP ALTER TRIGGER CREATE PACKAGE DROP DIRECTORY EXPLAIN
ALTER APP WORKLOAD GROUP MAPPING ALTER TYPE CREATE PACKAGE BODY DROP EXTENSION FETCH
ALTER AUDIT POLICY ALTER USER CREATE PROCEDURE DROP FOREIGN TABLE GRANT
ALTER DEFAULT PRIVILEGES ANALYSE CREATE RESOURCE POOL DROP GROUP MERGE
ALTER DIRECTORY ANALYZE CREATE ROLE DROP INDEX MOVE
ALTER EXTENSION ANONYMOUS BLOCK CREATE ROW LEVEL SECURITY POLICY DROP MASKING POLICY PREDICT BY
ALTER FOREIGN TABLE ARCHIVE SNAPSHOT CREATE SCHEMA DROP MATERIALIZED VIEW PREPARE
ALTER DATA SOURCE ALTER VIEW CREATE PUBLICATION DROP FUNCTION INSERT
ALTER DATABASE ALTER WORKLOAD GROUP CREATE RESOURCE LABEL DROP GLOBAL CONFIGURATION LOCK
ALTER FOREIGN TABLE FOR HDFS BEGIN CREATE SEQUENCE DROP MODEL PREPARE TRANSACTION
ALTER FUNCTION CALL CREATE SERVER DROP NODE PUBLISH SNAPSHOT
ALTER GLOBAL CONFIGURATION CHECKPOINT CREATE SNAPSHOT AS DROP NODE GROUP PURGE
ALTER GROUP CLEAN CONNECTION CREATE SNAPSHOT FROM DROP OPERATOR PURGE SNAPSHOT
ALTER INDEX CLOSE CREATE SUBSCRIPTION DROP OWNED REASSIGN OWNED
ALTER LARGE OBJECT CLUSTER CREATE SYNONYM DROP PACKAGE REFRESH MATERIALIZED VIEW
ALTER MASKING POLICY COMMENT CREATE TABLE DROP PACKAGE BODY REINDEX
ALTER NODE GROUP COPY CREATE TABLE SUBPARTITION DROP RESOURCE LABEL ROLLBACK
ALTER OPERATOR CREATE APP WORKLOAD GROUP CREATE TABLESPACE DROP RESOURCE POOL ROLLBACK PREPARED
ALTER PACKAGE CREATE APP WORKLOAD GROUP MAPPING CREATE TEXT SEARCH CONFIGURATION DROP ROLE SAMPLE SNAPSHOT
ALTER MATERIALIZED VIEW COMMIT CREATE TABLE AS DROP PROCEDURE RESET
ALTER NODE COMMIT PREPARED CREATE TABLE PARTITION DROP PUBLICATION REVOKE
ALTER PUBLICATION CREATE AUDIT POLICY CREATE TEXT SEARCH DICTIONARY DROP ROW LEVEL SECURITY POLICY SAVEPOINT
ALTER RESOURCE LABEL CREATE BARRIER CREATE TRIGGER DROP SCHEMA SELECT
ALTER RESOURCE POOL CREATE CLIENT MASTER KEY CREATE TYPE DROP SEQUENCE SELECT INTO
ALTER ROLE CREATE COLUMN ENCRYPTION KEY CREATE USER DROP SERVER SET
ALTER ROW LEVEL SECURITY POLICY CREATE DATA SOURCE CREATE VIEW DROP SUBSCRIPTION SET CONSTRAINTS
ALTER SCHEMA CREATE DATABASE CREATE WEAK PASSWORD DICTIONARY DROP SYNONYM SET ROLE
ALTER SEQUENCE CREATE DIRECTORY CREATE WORKLOAD GROUP DROP TABLE SET SESSION AUTHORIZATION
ALTER SERVER CREATE EXTENSION CURSOR DROP TABLESPACE SET TRANSACTION
ALTER SESSION CREATE FOREIGN TABLE DEALLOCATE DROP TEXT SEARCH CONFIGURATION SHOW
ALTER SUBSCRIPTION CREATE FUNCTION DECLARE DROP TEXT SEARCH DICTIONARY START TRANSACTION
ALTER SYNONYM CREATE GROUP DELETE DROP TRIGGER TIMECAPSULE TABLE
ALTER TABLE PARTITION CREATE MATERIALIZED VIEW DROP AUDIT POLICY DROP WEAK PASSWORD DICTIONARY VALUES
ALTER TABLE SUBPARTITION CREATE MODEL DROP CLIENT MASTER KEY DROP WORKLOAD GROUP
ALTER TABLE CREATE MASKING POLICY DROP APP WORKLOAD GROUP MAPPING DROP VIEW VACUUM
ALTER TABLESPACE CREATE NODE DROP COLUMN ENCRYPTION KEY END
ALTER SYSTEM KILL SESSION CREATE INDEX DO DROP TYPE TRUNCATE
ALTER SYSTEM SET CREATE LANGUAGE DROP APP WORKLOAD GROUP DROP USER UPDATE
ALTER TEXT SEARCH CONFIGURATION CREATE NODE GROUP DROP DATA SOURCE EXECUTE
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
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
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
\r reset (clear) the query buffer
\w FILE write query buffer to 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 "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 shell
Variables
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
\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
最后修改时间：2022-11-25 16:25:44
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
