学习目标
学习openGauss数据库客户端工具gsql的使用。
课程学习
gsql是openGauss提供在命令行下运行的数据库连接工具，可以通过此工具连接服务器并对其进行操作和维护，除了具备操作数据库的基本功能，gsql还提供了若干高级特性，便于用户使用。
课后作业
1.gsql命令连到数据库omm
使用omm用户连接到本机omm数据库的5432端口，命令中的-r选项提供了对gsql命令的历史版本支持。
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.在gsql中查看数据库的版本、pg基础版本和版权信息
omm=# select 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) version ----------------------------------------------------------------------------------------------------------------- -------------------------------------- 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.常见元命令的使用
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=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命令与元命
openGauss=# \du List of roles Role name | Attributes | Member of -----------+----------------------------------------------------------------------------------------------------- -------------+----------- gaussdb | Sysadmin | {} omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy admin, UseFT | {} openGauss=# \dg omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policy admin, UseFT | {} List of roles Role name | Attributes | Member of -----------+----------------------------------------------------------------------------------------------------- -------------+----------- gaussdb | Sysadmin | {}复制
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 sqladvisor | omm (11 rows) 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复制
3.7 --创建表
CREATE TABLE customer_t
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
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
3.8 --插入数据
INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White'); ce','White');NSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grac INSERT 0 1 openGauss=#复制
3.9 --\dt命令，命令\dt的作用是显示数据库中所有的表。
openGauss=# \dt List of relations Schema | Name | Type | Owner | Storage --------+------------+-------+-------+---------------------------------- public | customer_t | table | omm | {orientation=row,compression=no} (1 row)复制
3.10 --\di IndexName命令，查看索引信息，元命令\di IndexName的作用是查看某个索引的信息。
openGauss=# create index idx_customer_id on customer_t(c_customer_id); CREATE INDEX openGauss=# \di (1 row) openGauss=# List of relations Schema | Name | Type | Owner | Table | Storage --------+-----------------+-------+-------+------------+--------- public | idx_customer_id | index | omm | customer_t | openGauss=#复制
3.11 --可以用\pset命令以不同的方法显示表：
openGauss=# 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 | | 3769 | 5 | Grace | White | +---------------+---------------+--------------+-------------+ (3 rows) openGauss=#复制
3.12 --打开扩展表格式模式。
openGauss=# SELECT * FROM customer_t; +-[ RECORD 1 ]--+----------+ | c_customer_sk | 3769 | | c_customer_id | 5 | | c_first_name | Grace | | c_last_name | White | +-[ RECORD 2 ]--+----------+ | c_customer_sk | 3769 | | c_customer_id | 5 | | c_first_name | Grace | | c_last_name | White | +-[ RECORD 3 ]--+----------+ | c_customer_sk | 3769 | | c_customer_id | 5 | | c_first_name | Grace | | c_last_name | White | +---------------+----------+复制
4.使用两种方法，连到postgres数据库中
4.1 第一种
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=#
4.2 第二种
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.复制
5.gsql中的事务：测试gsql中的默认事务自动提交功能
5.1 测试gsql手动提交
openGauss=# show AUTOCOMMIT; +-[ RECORD 1 ]----+ | autocommit | on | +------------+----+复制
5.2 --测试gsql中事务默认为自动提交功能
openGauss=# create table customer_new as select * from customer_t; INSERT 0 3 openGauss=#复制
5.3 --重新登录后看到之前创建的表customer_new
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)复制
6.gsql中的事务：测试gsql中的事务手动提交功能
6.1 –测试gsql手动提交
#Opengauss默认执行完一条语句后，立即提交。可以关闭自动提交功能：
#注意：此处设置ATUOCOMMIT必须用大写！
openGauss=# \set AUTOCOMMIT off复制
6.2 --插入一些数据
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
6.3 --查看表中数据
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 3769 | 5 | Grace | White 6885 | 1 | Joes | Hunter 4321 | 2 | Lily | Carter 9527 | 3 | James | Cook 9500 | 4 | Lucy | Baker (7 rows)复制
6.4 执行回滚
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 3769 | 5 | Grace | White 3769 | 5 | Grace | White (3 rows)复制
7.gsql相关的帮助
7.1 --连接数据库时，可以使用如下命令获取帮助信息。
openGauss=# gsql --help复制
7.2 --\h获取和SQL语法有关的帮助信息
openGauss-# \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 FOREIGN TABLE CREATE MASKING POLICY DROP ROLE ALTER FOREIGN TABLE FOR HDFS CREATE MATERIALIZED VIEW DROP ROW LEVEL SECURITY POLICY ALTER GROUP CREATE NODE GROUP DROP SERVER ALTER FUNCTION CREATE MODEL DROP SCHEMA ALTER GLOBAL CONFIGURATION CREATE NODE DROP SEQUENCE ALTER INDEX CREATE OPERATOR DROP SUBSCRIPTION ALTER LARGE OBJECT CREATE PACKAGE DROP SYNONYM ALTER NODE CREATE PUBLICATION DROP TEXT SEARCH CONFIGURATION ALTER PACKAGE CREATE ROLE DROP TYPE ALTER NODE GROUP CREATE RESOURCE LABEL DROP TEXT SEARCH DICTIONARY ALTER OPERATOR CREATE RESOURCE POOL DROP TRIGGER --More-- ALTER MASKING POLICY CREATE PACKAGE BODY DROP TABLE ALTER MATERIALIZED VIEW CREATE PROCEDURE DROP TABLESPACE 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 PURGE ALTER TRIGGER CREATE WEAK PASSWORD DICTIONARY PURGE SNAPSHOT ALTER TYPE CREATE WORKLOAD GROUP REASSIGN OWNED ALTER USER CURSOR REFRESH MATERIALIZED VIEW ALTER VIEW DEALLOCATE REINDEX --More-- 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 SEARCH DICTIONARY PREDICT BY ALTER TABLE SUBPARTITION CREATE TRIGGER PREPARE ALTER TABLESPACE CREATE TYPE PREPARE TRANSACTION ALTER TEXT SEARCH CONFIGURATION CREATE USER PUBLISH SNAPSHOT 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复制
7.3 --? 获取和元命令有关的帮助信息
openGauss-# \？ Invalid command \？. Try \? for help. 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 \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 \ir FILE as \i, but relative to location of current script \echo [STRING] write string to standard output \i FILE execute commands from file \o [FILE] send all query results to file or |pipe \qecho [STRING] write string to query output stream (see \o) --More-- \ir+ FILE KEY as \i+, but relative to location of current script 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--More-- \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复制
最后修改时间：2022-11-26 00:05:36
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
墨天轮福利君
2年前
评论
0
作业审核合格，一起参与21天openGauss学习打卡活动！
活动详情：https://www.modb.pro/db/5516192年前
点赞 评论
相关阅读
openGauss荣获中国软件行业协会多奖项，技术升级再创行业新高度
openGauss
554次阅读
2025-04-30 14:30:58
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
MogDB
307次阅读
2025-04-17 10:41:41
MogDB 发布更新，解决 openGauss 数据库在长事务情况下Ustore表膨胀问题
云和恩墨
201次阅读
2025-04-16 09:52:02
GitCode 成 openGauss 新归宿，国产开源数据库里程碑事件
严少安
172次阅读
2025-04-27 11:37:53
荣誉时刻！openGauss认证证书快递已发，快来看看谁榜上有名！
墨天轮小教习
162次阅读
2025-04-23 17:39:13
单个执行机并行执行MySQL到openGauss数据迁移子任务
Clipnosis
151次阅读
2025-04-30 16:39:58
openGauss6.0.0适配操作系统自带的软件，不依赖三方库
来杯拿铁
96次阅读
2025-04-18 10:49:53
Postgresql数据库单个Page最多存储多少行数据
maozicb
92次阅读
2025-04-23 16:02:19
openGauss新特性 | openGauss-DataVec向量数据库特性介绍
openGauss
65次阅读
2025-04-17 10:41:47
RISC-V 首迎 openGauss 7.0.0-RC1 全量版适配！数据库核心功能完整落地开源架构
openGauss
49次阅读
2025-04-16 10:33:59
