GS | 交互命令说明

[GS 3.1]

 1 执行上一条SQL

\\

Tank> select * from t1;

ID NAME
-- ----
 1 Tank

1 row selected.

Tank> \\

ID NAME
-- ----
 1 Tank

1 row selected.

2 编辑最近执行的SQL

\ed

\edit

Tank> select * from t1;

ID NAME
-- ----
 1 Tank

1 row selected.

Tank> \ed

select * from t1

3 在客户端中执行OS命令

\host

\ho

\!

Tank> \ho  
[gs@test01 ~]$ exit
exit

Tank> \!   
[gs@test01 ~]$ exit
exit

Tank> \! pwd
/home/gs

Tank> 

4 显示执行过的SQL

\hi

\history

gSQL> select * from t1;

ID NAME
-- ----
 1 Tank

1 row selected.

gSQL> select * from dual;

DUMMY
-----
X    

1 row selected.

gSQL> \hi    

ID SQL               
-- ------------------
 1 select * from t1  
 2 select * from dual

gSQL> \1

ID NAME
-- ----
 1 Tank

1 row selected.

5 调用脚本

Tank> \i '1.sql'
select * from t1;

ID NAME
-- ----
 1 Tank

1 row selected.

Tank> !cat 1.sql
select * from t1;

6 设置自动提交

\set autocommit [on | off]

Tank> \set autocommit on
Tank> \set autocommit off

7 输出执行计划

\set autotrace on

输出结果+执行计划
\set autotrace traceonly

只输出执行计划

\set autotrace off

不输出执行计划（默认）

Tank> \set autotrace on
Tank> select * from t1;

ID NAME
-- ----
 1 Tank

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                         |
|    1  |    TABLE ACCESS ("T1")                                       |                       1 |
==================================================================================================

     1  -  READ COLUMNS : ID, NAME

<<<  end print plan


Tank> \set autotrace traceonly
Tank> select * from t1;


>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                         |
|    1  |    TABLE ACCESS ("T1")                                       |                       0 |
==================================================================================================

     1  -  READ COLUMNS : ID, NAME

<<<  end print plan


Tank> \set autotrace off

作用与\explain plan相同：

\explain plan on
输出结果+执行计划

\explain plan only
只输出执行计划
 \explain plan
未指定on或only时，默认为on

8 设置颜色用于区分输出行

\set color on
每行的输出颜色不同
\set color off
每行的输出颜色相同（默认值）

9 设置输出结果字段长度

\set colsize number

10 设置输出结果的行大小

\set linesize number

11 设置每页显示的行数

\set pagesize number

12 设置显示执行时间、

\set time [on | off]

Tank> set timing on
Tank> select * from t1;

ID NAME 
-- -----
 1 Tank 
 2 Bruce
 3 Roy  
 4 Ken  

4 rows selected.

Elapsed time: 0.15800 ms
Tank> set timing off

13 保存输出结果到文件

\spool 'filename' [CREATE | REPLACE | APPEND]

执行SQL

\spool off

Tank> \spool 'output.csv' create 
Tank> select * from t1;

ID NAME 
-- -----
 1 Tank 
 2 Bruce
 3 Roy  
 4 Ken  

4 rows selected.

Tank> \spool off

Tank> !
[gs@test01 ~]$ cat output.csv 
Tank> select * from t1;

ID NAME 
-- -----
 1 Tank 
 2 Bruce
 3 Roy  
 4 Ken  

4 rows selected.

Tank> \spool off

14 查看表的定义信息

\ddl_table table_name

Tank> \ddl_table t1


SET SESSION AUTHORIZATION "TESTUSER"; 
CREATE TABLE "TESTUSER"."T1" 
    ( 
        "ID" NUMBER( 10, 0 )
    ) 
    PCTFREE  10 
    PCTUSED  60 
    INITRANS 4 
    MAXTRANS 8 
    STORAGE 
    ( 
        INITIAL 524288 
        NEXT    262144 
        MINSIZE 524288 
        MAXSIZE 562949953159168 
    ) 
    TABLESPACE "DATA_TBS" 
;
COMMIT;

Tank
2019.7.24

https://blog.csdn.net/daiyejava

https://www.cnblogs.com/okey