GaussDB数据库gsql工具的元命令演示
【摘要】 列出数据库集簇中所有数据库的名称、所有者、字符集编码以及使用权限等
1.GaussDB的元命令
(1)列出数据库集簇中所有数据库的名称、所有者、字符集编码以及使用权限等
| gsql的元命令，可用于简化数据库操作： 元命令 作用 元命令 作用 \l 列出数据库集簇中所有数据库的名称、所有者、字符集编码以及使用权限等 \d 列出当前search_path中模式下所有的表、视图和序列 \db 列出所有可用的表空间 \dn 列出所有的模式（名称空间） \du 列出所有数据库角色 \dt 列出数据库中的表 \di 列出所有的索引 \dv 列出所有的视图 \ds 列出所有的序列 \dp 列出权限信息 \d Tablename 列出表的详细信息 \d Indexname 列出索引的详细信息 \df 列出所有的函数 \sf 列出函数的定义 \timing 显示每条SQL语句的执行时间(以毫秒为单位) \echo [string] 把字符串写到标准输出 \i file.sql 从文件FILE中读取内容，并将其当作输入，执行查询 \! os_command 执行操作系统命令 \? 查看gsql 的帮助命令 \h 查看SQL语法帮助 \conninfo 查询当前连接的数据库的信息 \c 更换连接的数据库和用户 \o file_name 把所有的查询结果发送到文件里 \q 退出gsql |
2.元命令演示
(1)列出数据库集簇中所有数据库的名称、所有者、字符集编码以及使用权限等.
sjzt=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+-------------+-------------+-----------------------
mydb2 | | SQL_ASCII | C | C |
mydb3 | | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | | SQL_ASCII | C | C | =Tc/rdsAdmin +
| | | | | rdsAdmin=CTc/rdsAdmin+
| | | | | rdsBackup=c/rdsAdmin +
| | | | | rdsRepl=c/rdsAdmin +
| | | | | root=c/rdsAdmin +
| | | | | rdsMetric=c/rdsAdmin
sjzt | sjzt | SQL_ASCII | C | C | =Tc/sjzt +
| | | | | sjzt=CTc/sjzt +
| | | | | sjzt=APm/sjzt
template0 | | SQL_ASCII | C | C | =c/rdsAdmin +
| | | | | rdsAdmin=CTc/rdsAdmin
template1 | | SQL_ASCII | C | C | =c/rdsAdmin +
| | | | | rdsAdmin=CTc/rdsAdmin
(6 rows)(2)列出当前search_path中模式下所有的表、视图和序列
--(2)列出当前search_path中模式下所有的表、视图和序列
sjzt=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------+-------+-------+------------------------------------------------
sjzt | dept | table | sjzt | {orientation=row,compression=no}
sjzt | emp | table | sjzt | {orientation=row,compression=no}
sjzt | emp2 | table | sjzt | {orientation=row,compression=no}
sjzt | part_tbl1 | table | sjzt | {orientation=row,compression=no}
sjzt | part_tbl2 | table | sjzt | {orientation=row,compression=no}
sjzt | pt1 | table | sjzt | {orientation=row,compression=no}
sjzt | t1 | table | sjzt | {orientation=row,compression=no}
sjzt | warehouse_t1 | table | sjzt | {orientation=row,compression=no}
sjzt | warehouse_t2 | table | sjzt | {orientation=row,fillfactor=70,compression=no}
sjzt | warehouse_t3 | table | sjzt | {orientation=column,compression=high}
(10 rows)(3)列出可用表空间。
sjzt=> \db
List of tablespaces
Name | Owner | Location
------------+-------+----------
pg_default | |
pg_global | |
tbs2 | |
tbs3 | |
(4 rows)(4)列出可用的模式空间。
sjzt=> \dn
List of schemas
Name | Owner
----------------------+-------
blockchain |
cstore |
db4ai |
dbe_application_info |
dbe_file |
dbe_lob |
dbe_match |
dbe_output |
dbe_perf |
dbe_pldebugger |
dbe_pldeveloper |
dbe_random |
dbe_raw |
dbe_scheduler |
dbe_session |
dbe_sql |
dbe_sql_util |
dbe_task |
dbe_utility |
dbe_xml |
dbe_xmldom |
dbe_xmlparser |
mydb1 | sjzt
pkg_service |
pkg_util |
public |
sjzt | sjzt
snapshot |
sqladvisor |
sys |
(30 rows)(5)列出当前数据库的角色。
sjzt=> \du
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
sjzt | | {}
\dt = \d --结果一样都是列出数据库下的表。(6)列出数据库下是索引。
sjzt=> \di
List of relations
Schema | Name | Type | Owner | Table | Storage
--------+-------------------------------------+------------------------+-------+--------------+---------
sjzt | warehouse_t1_w_warehouse_name_key | index | sjzt | warehouse_t1 |
sjzt | warehouse_t2_pkey | index | sjzt | warehouse_t2 |
sjzt | warehouse_t2_w_warehouse_name_key_g | global secondary index | sjzt | warehouse_t2 |
(3 rows)(7)列出数据库下的视图。
sjzt=> \dv
List of relations
Schema | Name | Type | Owner | Storage
--------+------+------+-------+---------
sjzt | v_1 | view | sjzt |
(1 row)(8)列出当前数据库下序列。
sjzt=> \ds
List of relations
Schema | Name | Type | Owner | Storage
--------+---------+----------+-------+---------
sjzt | s_seq01 | sequence | sjzt |
(1 row)(9)列出表的访问权限。
sjzt=> \dp
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+--------------+----------+-------------------+--------------------------
sjzt | dept | table | |
sjzt | emp | table | |
sjzt | emp2 | table | |
sjzt | part_tbl1 | table | |
sjzt | part_tbl2 | table | |
sjzt | pt1 | table | |
sjzt | s_seq01 | sequence | |
sjzt | t1 | table | |
sjzt | v_1 | view | |
sjzt | warehouse_t1 | table | |
sjzt | warehouse_t2 | table | |
sjzt | warehouse_t3 | table | |
(12 rows)(10)查看表字段和类型。
sjzt=> \d emp
Table "sjzt.emp"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
ename | character varying(20) |
sal | numeric |
empno | integer |
deptno | integer | (11)查看索引的详细信息。
sjzt=> \d warehouse_t2_pkey
Unlogged index "sjzt.warehouse_t2_pkey"
Column | Type | Definition
----------------+---------+----------------
w_warehouse_sk | integer | w_warehouse_sk
primary key, btree, for table "sjzt.warehouse_t2"(12)列出当前数据库下的函数。
sjzt=> \df
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
--------+-----------------------+------------------+---------------------+--------+------------+------------+---------
sjzt | package_func_overload | integer | col integer | normal | f | t | f
(1 row)(13)列出函数的定义。
CREATE OR REPLACE FUNCTION sjzt.package_func_overload(col int)
RETURN integer NOT FENCED NOT SHIPPABLE PACKAGE
AS
declare
col_type text;
begin
col := 122;
dbe_output.print_line('one int parameters ' || col);
return 0;
end;
/(14)打开SQL的执行时间统计。
sjzt=> \timing
Timing is on.
sjzt=> select * from emp;
id | ename | sal | empno | deptno
----+-------+------+-------+--------
1 | xsq1 | 2000 | 6379 | 10
(1 row)
Time: 4.821 ms(15)将内容输出到终端。
sjzt=> \echo "你是坏蛋"
"你是坏蛋"(16)执行指定文件的内容。
vi 1.sql
select *from emp;
sjzt=> \i 1.sql
id | ename | sal | empno | deptno
----+-------+------+-------+--------
1 | xsq1 | 2000 | 6379 | 10
(1 row)(17)命令行执行操作系统命令。
sjzt=> \! ls -lsa
total 352
4 drwxr-x--- 12 omm omm 4096 Sep 4 22:48 .
0 drwxr-xr-x 3 root root 17 Aug 28 14:43 ..
4 -rw------- 1 omm omm 19 Sep 4 22:48 1.sql
4 -rw------- 1 omm omm 19 Aug 29 16:36 .applet
16 -rw------- 1 omm omm 12473 Sep 1 23:29 .bash_history
4 -rwxr-xr-x 1 omm omm 75 Jan 10 2020 .bash_logout
4 -rwxr-xr-x 1 omm omm 1205 Aug 31 13:52 .bash_profile
4 -rwxr-xr-x 1 omm omm 506 Aug 30 18:47 .bashrc
0 drwx------ 5 omm omm 50 Aug 29 16:36 .cache
0 drwx------ 12 omm omm 220 Aug 29 16:36 .config
0 drwx------ 3 omm omm 22 Aug 30 18:34 dbs
0 drwx------ 3 omm omm 25 Aug 29 16:36 .dbus
4 -rw------- 1 omm omm 16 Aug 29 16:36 .esd_auth
16 -rw-r--r-- 1 omm omm 15482 Aug 29 16:36 .face
4 -rw------- 1 omm omm 1079 Aug 30 18:42 gauss_env_file
64 -rw------- 1 omm omm 64000 Aug 30 18:47 install_cluster.log
8 -rw------- 1 omm omm 7281 Sep 4 22:34 lextab.py
0 drwx------ 3 omm omm 19 Aug 29 16:36 .local
0 drwx------ 5 omm omm 82 Aug 30 18:35 log
0 drwx------ 4 omm omm 39 Aug 28 12:48 .mozilla
0 drwx------ 2 omm omm 40 Aug 29 17:02 .oracle_jre_usage
4 -rw-r--r-- 1 omm omm 18 Aug 29 16:37 .recentAppLog
0 drwx------ 2 omm omm 25 Aug 28 15:30 .ssh
0 drwx------ 2 omm omm 56 Aug 30 18:34 sslcrt
4 -rw------- 1 omm omm 2096 Aug 28 23:40 .viminfo
36 -rw------- 1 omm omm 36220 Aug 29 16:42 .xsession-errors
168 -rw------- 1 omm omm 172002 Sep 4 22:34 yacctab.py
4 -rw-r--r-- 1 omm omm 204 Mar 6 2021 .zshrc(18)查看GSQL的帮助语法。
sjzt=> \?
General
\copyright show GaussDB Kernel 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(19)查看SQL语法帮助。
sjzt=> \h
Available help:
ABORT ALTER TEXT SEARCH CONFIGURATION CREATE NODE GROUP DROP DATABASE EXECUTE DIRECT
ALTER APP WORKLOAD GROUP ALTER TEXT SEARCH DICTIONARY CREATE OPERATOR DROP DATABASE LINK EXPLAIN
ALTER APP WORKLOAD GROUP MAPPING ALTER TRIGGER CREATE PACKAGE DROP DIRECTORY FETCH
ALTER AUDIT POLICY ALTER TYPE CREATE PACKAGE BODY DROP EVENT GRANT
ALTER COLUMN ENCRYPTION KEY ALTER USER CREATE PROCEDURE DROP EXTENSION INSERT
ALTER DATA SOURCE ALTER VIEW CREATE PUBLICATION DROP FOREIGN TABLE LOCK
ALTER DATABASE ALTER WORKLOAD GROUP CREATE RESOURCE LABEL DROP FUNCTION MERGE
ALTER DATABASE LINK ANALYSE CREATE RESOURCE POOL DROP GLOBAL CONFIGURATION MOVE
ALTER DEFAULT PRIVILEGES ANALYZE CREATE ROLE DROP GROUP PREDICT BY
ALTER DIRECTORY ANONYMOUS BLOCK CREATE ROW LEVEL SECURITY POLICY DROP INDEX PREPARE
ALTER EVENT ARCHIVE SNAPSHOT CREATE SCHEMA DROP MASKING POLICY PREPARE TRANSACTION
ALTER EXTENSION BEGIN CREATE SEQUENCE DROP MATERIALIZED VIEW PUBLISH SNAPSHOT
ALTER FOREIGN TABLE CALL CREATE SERVER DROP MODEL PURGE
ALTER FOREIGN TABLE FOR HDFS CHECKPOINT CREATE SNAPSHOT AS DROP NODE PURGE SNAPSHOT
ALTER FUNCTION CLEAN CONNECTION CREATE SNAPSHOT FROM DROP NODE GROUP REASSIGN OWNED
ALTER GLOBAL CONFIGURATION CLOSE CREATE SUBSCRIPTION DROP OPERATOR REFRESH MATERIALIZED VIEW
ALTER GROUP CLUSTER CREATE SYNONYM DROP OWNED REINDEX
ALTER INDEX COMMENT CREATE TABLE DROP PACKAGE RESET
ALTER LARGE OBJECT COMMIT CREATE TABLE AS DROP PACKAGE BODY REVOKE
ALTER MASKING POLICY COMMIT PREPARED CREATE TABLE PARTITION DROP PROCEDURE ROLLBACK
ALTER MATERIALIZED VIEW COPY CREATE TABLE SUBPARTITION DROP PUBLICATION ROLLBACK PREPARED
ALTER NODE CREATE APP WORKLOAD GROUP CREATE TABLESPACE DROP RESOURCE LABEL SAMPLE SNAPSHOT
ALTER NODE GROUP CREATE APP WORKLOAD GROUP MAPPING CREATE TEXT SEARCH CONFIGURATION DROP RESOURCE POOL SAVEPOINT
ALTER OPERATOR CREATE AUDIT POLICY CREATE TEXT SEARCH DICTIONARY DROP ROLE SELECT
ALTER PACKAGE CREATE BARRIER CREATE TRIGGER DROP ROW LEVEL SECURITY POLICY SELECT INTO
ALTER PUBLICATION CREATE CLIENT MASTER KEY CREATE TYPE DROP SCHEMA SELECT UNPIVOT
ALTER RESOURCE LABEL CREATE COLUMN ENCRYPTION KEY CREATE USER DROP SEQUENCE SELECT PIVOT
ALTER RESOURCE POOL CREATE DATA SOURCE CREATE VIEW DROP SERVER SET
ALTER ROLE CREATE DATABASE CREATE WEAK PASSWORD DICTIONARY DROP SUBSCRIPTION SET CONSTRAINTS
ALTER ROW LEVEL SECURITY POLICY CREATE DATABASE LINK CREATE WORKLOAD GROUP DROP SYNONYM SET ROLE
ALTER SCHEMA CREATE DIRECTORY CURSOR DROP TABLE SET SESSION AUTHORIZATION
ALTER SEQUENCE CREATE EVENT DEALLOCATE DROP TABLESPACE SET TRANSACTION
ALTER SERVER CREATE EXTENSION DECLARE DROP TEXT SEARCH CONFIGURATION SHOW
ALTER SESSION CREATE FOREIGN TABLE DELETE DROP TEXT SEARCH DICTIONARY SHOW EVENTS
ALTER SUBSCRIPTION CREATE FUNCTION DELIMITER DROP TRIGGER START TRANSACTION
ALTER SYNONYM CREATE GLOBAL INDEX DO DROP TYPE TIMECAPSULE TABLE
ALTER SYSTEM KILL SESSION CREATE GROUP DROP APP WORKLOAD GROUP DROP USER TRUNCATE
ALTER SYSTEM SET CREATE INDEX DROP APP WORKLOAD GROUP MAPPING DROP VIEW UPDATE
ALTER TABLE CREATE MASKING POLICY DROP AUDIT POLICY DROP WEAK PASSWORD DICTIONARY VACUUM
ALTER TABLE PARTITION CREATE MATERIALIZED VIEW DROP CLIENT MASTER KEY DROP WORKLOAD GROUP VALUES
ALTER TABLE SUBPARTITION CREATE MODEL DROP COLUMN ENCRYPTION KEY END
ALTER TABLESPACE CREATE NODE DROP DATA SOURCE EXECUTE
sjzt=> (20)查看当前的连接信息。
sjzt=> \conninfo
You are connected to database "sjzt" as user "sjzt" via socket in "/data/cluster/temp" at port "8000".
sjzt=> (21)切换到postgres数据库。
gaussdb=> \c - sjzt
Password for user sjzt:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "sjzt".
gaussdb=> (22)切换到sjzt用户。
gaussdb=> \c - sjzt
Password for user sjzt:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "sjzt".
gaussdb=> (23)将输出的内容重定向到2.txt文件中。
sjzt=> \o 2.txt
sjzt=> select * from emp;
sjzt=> \q
[omm@gaussdb01 ~]$ more 2.txt
id | ename | sal | empno | deptno
----+-------+------+-------+--------
1 | xsq1 | 2000 | 6379 | 10
(1 row)
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）