金仓数据库KingbaseES ksql工具用户指南及参考--4. 使用 Ksql

本部分包含以下内容：

• Ksql基础

• Ksql执行脚本

• SQL中插入变量

• 格式化Ksql输出

• 生成HTML

• Ksql调优

• Ksql安全

4.1. Ksql基础

4.1.1. 输入和执行命令

通常，您可以使用空格或Tab分隔命令中的单词，可以在单词之间使用额外的空格或Tab提高命令的可读性。

您可以输入三种命令：

• SQL命令，用于处理数据库中的信息；

• PL/SQL blocks,也是用于处理数据库中的信息；

• Ksql命令，用于格式化查询结果、设置选项以及编辑SQL命令和PL/SQL blocks；

4.1.1.1. 执行命令

在Ksql命令行中，您可以输入一个命令，然后通过Enter键引导Ksql执行。Ksql将处理该命令，并在准备执行另一个命令时重新显示命令提示符。

4.1.2. 列出表的定义

要查看给定表每个列的定义，可以使用Ksql的 \d[S+] [ pattern ] 命令（详见 \d 命令描述）。例如：

test=# \d parts
                        Table "public.parts"
Column |            Type            | Collation | Nullable | Default
-------+----------------------------+-----------+----------+--------
pnum   | integer                    |           |          |
pname  | character varying(15 char) |           |          |

4.1.3. 列出 PL/SQL 定义

要查看函数的定义，可以使用Ksql的 \df[anptwS+] [ pattern ] 命令（详见 \df 命令描述）。例如：

创建名为 TESTFUNC 的函数

CREATE or REPLACE FUNCTION TESTFUNC() RETURNS integer AS $test$
   BEGIN
      RETURN 1;
$test$ LANGUAGE plsql;

查看函数的定义

test=# \df TESTFUNC
                        List of functions
Schema |   Name   | Result data type | Argument data types | Type
-------+----------+------------------+---------------------+------
public | testfunc | integer          |                     | func
(1 row)

4.1.4. 列出模式

4.1.4.1. 概述

很多 \d 命令都可以用一个 pattern 参数来指定要被显示的对象名称。在最简单的情况下，模式正好就是该对象的准确名称。在模式中的字符通常会被变成小写形式（就像在SQL名称中那样），例如 \dt FOO 将会显示名为 foo 的表。就像在SQL名称中那样，把模式放在双引号中可以阻止它被转换成小写形式。如果需要在一个模式中包括一个真正的双引号字符，则需要把它写成两个相邻的双引号，这同样是符合SQL引用标识符的规则。例如， \dt "FOO""BAR" 将显示名为 FOO"BAR （不是 foo"bar ）的表。和普通的SQL名称规则不同，你不能只在模式的一部分周围放上双引号，例如 \dt FOO"FOO"BAR 将会显示名为 fooFOObar 的表。

只要 pattern 参数被完全省略， \d 命令会显示在当前schema 搜索路径中可见的全部对象 —这等价于用 * 作为模式（如果一个对象所在的 schema位于搜索路径中并且没有同类且同名的对象出现在搜索路径中该schema 之前的 schema 中，则说该对象是 可见 的。这表示可以直接用名称引用该对象，而不需要用schema来进行限定）。要查看数据库中所有的对象而不管它们的可见性，可以把 *.* 用作模式。

如果放在一个模式中， * 将匹配任意字符序列（包括空序列），而 ? 会匹配任意的单个字符（这种记号方法就像Unix shell的文件名模式一样）。例如， \dt int* 会显示名称以 int 开始的表。但是如果被放在双引号内， * 和 ? 就会失去这些特殊含义而变成普通的字符。

包含一个点号（ . ）的模式被解释为一个 schema名称模式后面跟上一个对象名称模式。例如， \dt foo*.*bar* 会显示名称以 foo 开始的schema中所有名称包括 bar 的表。如果没有出现点号，那么模式将只匹配当前schema搜索路径中可见的对象。同样，双引号内的点号会失去其特殊含义并且变成普通的字符。

高级用户可以使用字符类等正则表达式记法，如 [0-9] 可以匹配任意数字。所有的正则表达式特殊字符都按照 POSIX正则表达式 所说的工作，以下字符除外： . 会按照上面所说的作为一种分隔符， * 会被翻译成正则表达式记号 .* ， ? 会被翻译成 . ，而 $ 则按字面意思匹配。根据需要，可以通过书写 ? 、 (R +|)、 (R |)和 R ?来分别模拟模式字符 . 、 R *和 R ?。 $ 不需要作为一个正则表达式字符，因为模式必须匹配整个名称，而不是像正则表达式的常规用法那样解释（换句话说， $ 会被自动地追加到模式上）。如果不希望该模式的匹配位置被固定，可以在开头或者结尾写上 * 。注意在双引号内，所有的正则表达式特殊字符会失去其特殊含义并且按照其字面意思进行匹配。还有，在操作符名称模式中（即作为 \do 的参数），正则表达式特殊字符也按照字面意思进行匹配。

4.1.4.2. 列出模式

在Ksql中您可以使用 \dn[S+] [ pattern ]命令列出模式（详见 \dn 命令描述）。例如：

test=# \dn public
List of schemas
Name  |  Owner
------+----------
public| user1
(1 row)

4.1.5. 运行 SQL 命令

SQL命令语言使您能够操作数据库中的数据。有关具体SQL命令的信息，请参考KingbaseES数据库SQL语言。

在命令提示符下，输入该命令第一行

SELECT a, b

如果输入错误，使用 Backspace 删除并重新输入。完成后按 Enter 键移动到下一行。

在第二行的命令提示符下继续输入命令

FROM parts;

分号(;)表示该命令的结束符，按 Enter 后Ksql将处理该命令，并显示以下结果：

 pnum | pname
------+-------
(5 rows)

4.1.5.1. 理解 SQL 命令语法

如口语的语法规则来控制我们将单词组合成句子的方式一样，Ksql 也有语法规则控制如何将单词组合成命令。如果希望 Ksql 接受并执行输入的命令，则必须遵守这些规则。

4.1.5.2. 将 SQL 命令换分为单独的行

您可以在任何地方使用SQL命令将其划分为单独的行，只要不拆分单个单词。因此，可以按三行输入一个简单的查询SQL。例如：

SELECT *
FROM parts
WHERE pnum > 10;

创建一个表：

testdb=> CREATE TABLE my_table (
testdb(> first integer not null default 0,
testdb(> second text)
testdb-> ;
CREATE TABLE

注意

命令提示符的改变。

在本指南中，会发现大多数 SQL 命令都分为子句，每行一个句子。例如，上面的查询例子中，SELECT 和 FROM 子句被放置在不同的行上。许多人发现这种清晰可见的结构很有帮助，但您可以选择任何划分使命令更加的易读。

4.1.5.3. 结束 SQL 命令

可以通如下方式结束 SQL 命令：

• 使用分号(;)

• 使用 \g 结束

• 使用 set SQLTERM 命令定义 SQL 命令结束符

一个分号(;) 告诉 Ksql 您要运行该命令。在命令的最后一行的末尾输入分号，然后按 ENTER 键，Ksql 处理该命令，将其发送到服务器并返回结果。如果在输入分号之前错误的按了 ENTER 键，Ksql 将提示输入命令的下一行的行号。键入分号再次运行命令。例如：

-- 分号结束符
SELECT * FROM parts
 pnum | pname
------+-------
(5 rows)
-- \set SQLTERM 定义 / 结束符
\set SQLTERM /
 select * from parts
 pnum | pname
------+-------
(5 rows)
\set SQLTERM ;

4.1.6. 运行 PL/SQL

您可以使用 PL/SQL 子程序来操作数据库的数据。有关 PL/SQL 语句的信息，请参考 KingbaseES 数据库 PL/SQL 语言参考资料。Ksql 以与 SQL 命令相同的方式处理 PL/SQL 子程序，只是分号(;) 不会终止并执行 PL/SQL。

在以下情况下，可以进入输入 PL/SQL 语句模式：

• 可以键入一个用来创建存储过程的 SQL 命令(例如，创建函数)。以这种方式进入 PL/SQL 模式后，键入要创建的存储过程。

• 可以通过set SQLTERM 指定一个结束符，在创建的存储过程结束后新的一行中键入指定的结束符来结束存储过程的创建。

例如：创建并执行 test 函数

-- 创建名为 test 的函数
CREATE OR REPLACE FUNCTION test( ) RETURNS SETOF test1 AS
$BODY$
BEGIN
   FOR i IN 1 .. 5 LOOP
      INSERT INTO test1 VALUES(i,1);
   END LOOP;
   RETURN QUERY SELECT *
               FROM test1;
   RETURN;
$BODY$
LANGUAGE plpgsql;
-- 通过 \set SQLTERM 指定 / 结束符
   \set SQLTERM /
CREATE OR REPLACE FUNCTION test( ) RETURNS SETOF test1 AS
BEGIN
   FOR i IN 1 .. 5 LOOP
      INSERT INTO test1 VALUES(i,1);
   END LOOP;
   RETURN QUERY SELECT *
               FROM test1;
   RETURN;
\set SQLTERM ;
-- 执行 test 函数
SELECT * FROM test();

4.1.6.1. 创建存储过程

存储过程是 PL/SQL 函数、软件包或过程。若要创建存储过程，请使用以下的 SQL 创建命令：

• CREATE FUNCTION

• CREATE LIBRARY

• CREATE PACKAGE

• CREATE PACKAGE BODY

• CREATE PROCEDURE

• CREATE TRIGGER

• CREATE TYPE

• CREATE TYPE BODY

输入这些命令中的任何一个都可以使其处于 PL/SQL 模式，可以在其中输入 PL/SQL 子程序。有关更多的信息参考 KingbaseES 数据库相应的章节。

要运行 SQL 命令创建的存储过程，可以使用下面的方式：

• CALL FUNCTION NAME;

• SELECT FUNCTION NAME;

例如：创建名为 test 的函数并执行

-- create table tab1
CREATE TABLE tb1(
   a int,
   b int
-- insert values
INSERT INTO tb1 VALUES(1,1);
INSERT INTO tb1 VALUES(2,2);
-- create test() function
\set SQLTERM /
CREATE OR REPLACE FUNCTION test( ) RETURNS SETOF test1 AS
BEGIN
   RETURN QUERY
               SELECT *
               FROM tb1;
   RETURN;
\set SQLTERM ;
-- call test()
SELECT test();
-------
(1,1)
(2,2)
(2 rows)

注意

不是每种存储过程都能通过 SQL 命令执行，比如 CREATE TRIGGER 创建的触发器是通过创建时指定的触发条件自动被触发执行的，并不能通过 SQL 命令执行。

4.1.7. 运行 Ksql 命令

您可以使用 Ksql 命令操作 SQL 命令和 PL/SQL 块，并格式化和打印查询结果。Ksql 对 Ksql 命令的处理方式不同于 SQL 命令或 PL/SQL。例如

输入 ksql 命令：

-- 将结果格式为 html
\pset format html

如果输入错误的内容，可以使用 Backspace 删除它们并重新输入。

输入以下查询，然后按 ENTER 键来运行它：

-- 查询
SELECT * FROM tb1;
select * from tb1;
<table border="1">
   <th align="center">a</th>
   <th align="center">b</th>
<tr valign="top">
   <td align="right">1</td>
   <td align="right">1</td>
<tr valign="top">
   <td align="right">2</td>
   <td align="right">2</td>
</table>
<p>(2 rows)<br />

4.1.7.1. 理解 Ksql 命令语法

4.1.7.1.1. 元命令概述

你输入到Ksql中的任何以未加引用的反斜线开始的东西都是一个Ksql元命令，它们由Ksql自行处理。这些命令让Ksql对管理和编写脚本更有用。元命令常常被称作斜线或者反斜线命令。

4.1.7.1.2. 命令格式

Ksql命令的格式是用反斜线后面直接跟上一个命令动词，然后是一些参数。参数与命令动词和其他参数之间用任意多个空白字符分隔开。

要在一个参数中包括空白，可以将它加上单引号。要在一个参数中包括一个单引号，则需要在文本中写上两个单引号。任何包含在单引号中的内容都服从与 C语言中\n（新行）、\t（制表符）、\b（退格）、\r（回车）、\f（换页）、\digits（10 进制）以及\xdigits（16 进制）类似的替换规则。单引号内文本中的其他任何字符（不管它是什么）前面的反斜线都没有实际意义（会被忽略）。

如果在一个参数中出现一个未加引号的冒号（ : ）后面跟着一个Ksql变量名，它会被该变量的值替换，如 SQL中插入变量 中所述。在其中描述的形式 :'variable_name' 和 :"variable_name" 也有同样的效果。 :{?variable_name }语法允许测试一个变量是否被定义。它会被TRUE或FALSE替换。用一个反斜线转义该冒号可以防止它被替换。

在一个参数中，封闭在反引号（ ` ）中的文本会被当做一个传递给shell的命令行。该命令的输出（移除任何拖尾的新行）会替换反引号文本。在封闭在反引号的文本中，不会有特别的引号或者其他处理发生， :variable_name 的出现除外，其中 variable_name 是一个会被其值替换的Ksql变量名。此外，Also,appearances of :'variable_name' 的出现会被替换为该变量的值，而值会被适当地加以引用以变成一个单一shell命令参数（后一种形式几乎总是优先，除非你非常确定变量中有什么）。因为回车和换行字符在所有的平台上都不能被安全地引用，:'variable_name' 形式会打印一个错误消息并且在这类字符出现在值中时不替换该变量值。

有些命令把SQL标识符（例如一个表名）当作参数。这些参数遵循SQL的语法规则：无引号的字母被强制变为小写，而双引号（ " ）可以保护字母避免大小写转换并且允许在标识符中包含空白。

在双引号内，成对的双引号会被缩减为结果名称中的单个双引号。例如， FOO"BAR"BAZ 会被解释成 fooBARbaz ，而 "A weird"" name" 会变成 A weird" name 。

对参数的解析会在行尾或者碰到另一个未加引号的反斜线时停止。一个未加引号的反斜线被当做新元命令的开始。特殊的序列 \\ （两个反斜线）表示参数结束并且应继续解析SQL命令（如果还有）。使用这种方法，SQL命令和Ksql命令可以被自由地混合在一行中。但是无论在何种情况中，元命令的参数都无法跨越一行。

很多元命令作用在当前查询缓冲区上。这就是一个缓冲区而已，它保存任何已经被键入但是还没有发送到服务器执行的SQL命令文本。这将包括之前输入的行以及在该元命令同一行上出现在前面的任何文本。

4.1.7.1.3. 命令行编辑

为了方便的进行编辑和检索，Ksql支持Readline库。Ksql退出时命令历史会被自动保存，而当Ksql启动时命令历史会被重新载入。Ksql也支持tab 补全，不过补全逻辑绝不是一个SQL解析器。tab补全产生的查询也可能会受其他 SQL命令干扰，例如 SET TRANSACTION ISOLATION LEVEL 。如果出于某种原因不想用 tab 键补全，可以把下面的代码放在主目录下的名为 .inputrc 文件中关闭该特性：

$if ksql
set disable-completion on
$endif

（这不是Ksql特性而是Readline的特性。进一步的细节请阅读它的文档。）

4.1.7.1.4. 命令提示符

Ksql发出的提示符可以根据用户的喜好自定义。 PROMPT1 、 PROMPT2 和 PROMPT3 这三个变量包含了描述提示符外观的字符串和特殊转义序列。Prompt1 是当Ksql等待新命令时发出的常规提示符。Prompt 2是在命令输入时需要更多输入时发出的提示符，例如当命令没有被分号终止或者引用没有被关闭时就会发出这个提示符。在运行一个SQL``COPY FROM STDIN``命令并且需要在终端上输入一个行值时，会发出Prompt 3。

被选中的提示符变量会被原样打印，除非碰到一个百分号（ % ）。百分号的下一个字符会被特定的其他文本替换。预定义好的替换有：

%M

数据库服务器的完整主机名（带有域名），或者当该连接是建立在一个Unix 域套接字上时则是 [local] ，或者当 Unix域套接字不在编译系统内的默认位置上时则是 [local:/dir/name ]。

%m

数据库服务器的主机名称（在第一个点处截断），或者当连接建立在一个Unix 域套接字上时是 [local] 。

%>

数据库服务器正在监听的端口号。

%n

数据库会话的用户名（在数据库会话期间，这个值可能会因为命令 SET SESSION AUTHORIZATION 的结果而改变）。

%/

当前数据库的名称。

%~

和 %/ 类似，但是如果数据库是默认数据库时输出是 ~ （波浪线）。

%#

如果会话用户时一个数据库超级用户，则是 # ，否则是一个 > （在数据库会话期间，这个值可能会因为命令 SET SESSION AUTHORIZATION 的结果而改变）。

%p

当前连接到的后端的进程 ID。

%R

在提示符1下通常是 = ，但如果会话位于一个条件块的一个非活动分支中则是 @ ，如果会话处于单行模式中则是 ^ ，如果会话从数据库断开连接（ \connect 失败时会发生这种情况）则是 ! 。在提示符 2中，根据为什么Ksql期待更多的输入， %R 会被一个相应的字符替换：如果命令还没有被终止是 - ，如果有一个未完的 /* ... */ 注释则是 * ，如果有一个未完的被引用字符串则是一个单引号，如果有一个未完的被引用标识符则是一个双引号，如果有一个未完的美元引用字符串则是一个美元符号，如果有一个还没有被配对的左圆括号则是 ( 。 在提示符 3 中 %R 不会产生任何东西。

%x

事务状态：当不在事务块中时是一个空字符串，在一个事务块中时是 * ，在一个失败的事务块中时是 ! ，当事务状态是未判定时（例如因为没有连接）为 ? 。

%l

当前语句中的行号，从 1 开始。

% digits

带有指定的八进制码的字符会被替换。

%: name :

Ksql变量 name 的值。详见 变量 。

:literal:`%\ command `

command 的输出，类似于平常的“反引号”替换。

%[ ... %]

提示符可以包含终端控制字符，例如改变提示符文本的颜色、背景或者风格以及更改终端窗口标题的控制字符。为了让Readline的行编辑特性正确工作，这些不可打印的控制字符必须被包裹在 %[ 和 %] 之间以指定它们是不可见的。在提示附中可以出现多个这样的标识对。例如：

testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

会导致一个在兼容 VT100的彩色终端上的粗体（ 1; ）的、黑底黄字（ 33;40 ）的提示符。

要在你的提示符中插入一个百分号，可以写成 %% 。提示符 1 和 2 的默认提示是 '%/%R%# ' ，提示符 3 的提示是 '>> ' 。

例如，现在我们把提示符改一改：

testdb=> \set PROMPT1 '%n@%m %~%R%# '
peter@localhost testdb=>

4.1.8. 变量

Ksql提供了和普通 Unix 命令 shell 相似的变量替换特性。变量简单来说就是一对名称/值，其中值可以是任意长度的任意字符串。名称必须由字母（包括非拉丁字母）、数字和下划线构成。

要设置一个变量，可以使用Ksql的元命令 \set 。例如：

testdb=> \set foo bar

会设置 foo 为值 bar 。要检索该变量的内容，可以在名称前放一个冒号，例如：

testdb=> \echo :foo

这在常规 SQL 命令和元命令中均有效，下文的 SQL中插入变量 中有更多细节。

如果调用 \set 时没有第二个参数，该变量会被设置为一个空字符串值。要重置（即删除）一个变量，可以使用命令 \unset 。要显示所有变量的值，在调用 \set 时不带任何参数即可。

注意

\set 的参数服从与其他命令相同的替换规则。因此可以构造有趣的引用，例如 \set :foo 'something' 以及分别得到Perl或者PHP的“软链接”或者“可变变量”。这些构造出来的东西并没有什么用处。在另一方面， \set bar :foo 是一种很好的拷贝变量的方法。

有一些变量会被Ksql特殊对待。它们表示特定的选项设置，运行时这类选项设置可以通过修改该变量的值来改变，或者在某些情况下它们表示Ksql的可更改的状态。按照惯例，所有被特殊对待的变量的名称由全部大写形式的 ASCII 字母（还有可能是数字和下划线）组成。为了确保未来最大的兼容性，最好避免使用这类变量名。

控制Ksql行为的变量通常不能被重置或者设置为无效值。允许 \unset 命令，但它会被解释为将变量设置为它的默认值。没有第二参数的 \set 命令会被解释为将变量设置为 on （对于接受该值的控制变量），对不接受该值的变量则会拒绝这个命令。此外，接受值 on 和 off 的控制变量也能接受其他常见的布尔值拼写方式，例如 true 和 false 。

被特殊对待的变量是：

AUTOCOMMIT

在被设置为 on （默认）时，每一个 SQL 命令在成功完成时会被自动提交。在这种模式中要推迟提交，必须输入一个 BEGIN 或者 START TRANSACTION SQL 命令。当被设置为 off 或者被重置时，在显式发出 COMMIT 或者 END 之前，SQL 命令不会被提交。自动提交打开模式会为你发出一个隐式的 BEGIN ，在任何尚未在事务块中且本身即不是 BEGIN 或其他事务控制命令，也不是无法在事务块中执行的命令（例如 VACUUM ）之前。

注意

在自动提交关闭模式中，必须通过 ABORT 或者 ROLLBACK 显式地放弃任何失败的事务。还要记住，如果退出会话时没有提交，则所有的工作都会丢失。

注意

自动提交打开模式是KingbaseES的传统行为，但是自动提交关闭模式更接近于 SQL 的规范。如果更喜欢自动提交关闭模式，可以在系统级的 ksqlrc 文件或者个人的 ~/.ksqlrc 文件中设置它。

COMP_KEYWORD_CASE

确定在补全一个 SQL关键词时要使用的大小写形式。如果被设置为 lower 或者 upper ，补全后的词将分别是小写或者大写形式。如果被设置为 preserve-lower 或者 preserve-upper （默认），补全后的词将会保持该词已输入部分的大小写形式，但是如果被补全的词还没有被输入，则它会被分别补全成小写或者大写形式。

DBNAME

当前已连接的数据库名称。每次连接到一个数据库时都会设置该变量（包括程序启动时），但是可以被更改或者重置。

ECHO

如果被设置为 all ，所有非空输入行会被按照读入它们的样子打印到标准输出（不适用于交互式读取的行）。要在程序开始时选择这种行为，可以使用开关 -a 。如果被设置为 queries ，Ksql会在发送每个查询给服务器时将它们打印到标准输出。选择这种行为的开关是 -e 。如果被设置为 errors ，那么只有失败的查询会被显示在标准错误输出上。这种行为的开关是 -b 。如果被重置或者设置为 none （默认值）则不会显示任何查询。

ECHO_HIDDEN

当这个变量被设置为 on 且一个反斜线命令查询数据库时，相应的查询会被先显示。这种特性可以帮助我们学习KingbaseES的内部并且在自己的程序中提供类似的功能（要在程序开始时选择这种行为，可以使用开关 -E ）。如果把这个变量设置为值 noexec ，则对应的查询只会被显示而并不真正被发送给服务器执行。默认值是 off 。

ENCODING

当前的客户端字符集编码。每一次你连接到一个数据库（包括程序启动）时以及当你用 \encoding 更改编码时，这个变量都会被设置，但它可以被更改或者重置。

ERROR

如果上一个SQL查询失败则为 true ，如果成功则是 false 。另见 SQLSTATE 。

FETCH_COUNT

如果这个变量被设置为一个大于零的整数值， SELECT 查询的结果会以一组一组的方式取出并且显示（而不是像默认的那样把整个结果集拿到以后再显示），每一组就会包括这么多个行。因此，这种方式只会使用有限的内存量，而不管整个结果集的大小。在启用这个特性时，通常会使用 100 到 1000的设置。记住在使用这种特性时，一个查询可能会在已经显示了一些行之后失败。

提示

尽管可以把这种特性用于任何的输出格式，但是默认的 aligned 格式看起来会比较糟糕，因为每一组的 FETCH_COUNT 个行将被单独格式化，这就会导致不同的行组的列宽不同。其他的输出格式会更好。

HIDE_TABLEAM

如果将该变量设置为 true ，则不会显示表的访问方法详细信息。这主要用于回归测试。

HISTCONTROL

如果这个变量被设置为 ignorespace ，则以一个空格开始的行不会被放入到历史列表中。如果被设置为值 ignoredups ，则匹配之前的历史行的行不会被放入。值 ignoreboth 组合了上述两种值。如果被重置或者被设置为 none （默认值），所有在交互模式中被读入的行都会保存在历史列表中。

HISTFILE

该文件名将被用于存储历史列表。如果被重设，文件名将从 KSQL_HISTORY 环境变量中取得。如果该环境变量也没有被设置，则默认值是 ~/.ksql_history ，在Windows上是 %APPDATA%\kingbase\ksql_history 。例如，

\set HISTFILE ~/.ksql_history- :DBNAME

放在 ~/.ksqlrc 中将会导致Ksql为每一个数据库维护一个单独的历史。

HISTSIZE

存储在命令历史中的最大命令数（默认值是500）。如果被设置为一个负值，则不会应用限制。

HOST

当前连接到的数据库服务器端口。每次连接到一个数据库时都会设置该变量（包括程序启动时），但是可以被更改或者重置。

IGNOREEOF

如果被设置为1或者更小，向一个Ksql的交互式会话发送一个EOF字符（通常是Control+D）将会终止应用。如果设置为一个较大的数值，则必须键入多个连续的EOF字符才能让交互式会话终止。如果该变量被设置为一个非数值，则它会被解释为10。默认值为0。

LASTOID

最后被影响的 OID的值，这可能会由 INSERT 或者 \lo_import 命令返回。这个变量只保证在下一个SQL命令被显示完之前有效。 由于KingbaseES V8.6 版本服务器不再支持OID系统列，所以LASTOID在针对这类服务器时在 INSERT 之后将始终是0。

LAST_ERROR_MESSAGE LAST_ERROR_SQLSTATE

当前Ksql会话中最近一个失败查询的主错误消息和相关的SQLSTATE代码，如果在当前会话中没有发生错误，则是一个空字符串和 00000 。

ON_ERROR_ROLLBACK

当被设置为 on 时，如果事务块中的一个语句产生一个错误，该错误会被忽略并且该事务会继续。当被设置为 interactive 时，只在交互式会话中忽略这类错误，而读取脚本文件时则不会忽略错误。当被重置或者设置为 off （默认值）时，事务块中产生错误的一个语句会中止整个事务。错误回滚模式的工作原理是在事务块的每个命令之前都为你发出一个隐式的 SAVEPOINT ，然后在该命令失败时回滚到该保存点。

ON_ERROR_STOP

默认情况下，出现一个错误后命令处理会继续下去。当这个变量被设置为 on 后，出现错误后命令处理会立即停止。在交互模式下，ksql将会返回到命令提示符；否则，ksql将会退出并且返回错误代码 3 来把这种情况与致命错误区分开来，致命错误会被报告为错误代码 1。在两种情况下，任何当前正在运行的脚本（顶层脚本以及任何它已经调用的其他脚本）将被立即中止。如果顶层命名字符串包含多个 SQL 命令，将在当前命令处停止处理。

PORT

当前连接到的数据库服务器端口。每次连接到一个数据库时都会设置该变量（包括程序启动时），但是可以被更改或者重置。

PROMPT1 PROMPT2 PROMPT3

这些变量指定Ksql发出的提示符的模样。见下文的 提示符。

QUIET

把这个变量设置为 on 等效于命令行选项 -q 。在交互模式下可能用处不大。

ROW_COUNT

上一个SQL查询返回的行数或者受影响的行数，如果该查询失败或者没有报告行计数则为0。

SERVER_VERSION_NAME SERVER_VERSION_NUM

字符串形式的服务器版本号，例如 |V8R2| 、 10.1 或者 11beta1 ，以及数字形式的服务器版本号，例如 90602 或者 100001 。每次你连接到一个数据库（包括程序启动）时，这些都会被设置，但可以被改变或者重设。

SHOW_CONTEXT

这个变量可以被设置为值 never 、 errors 或者 always 来控制是否在来自服务器的消息中显示``CONTEXT``域。默认是 errors （表示在错误消息中显示上下文，但在通知和警告消息中不显示）。当 VERBOSITY 被设置为 terse 或者 sqlstate 时，这个设置无效（另见 \errverbose ，它可以用来得到刚遇到的错误的详细信息）。

SINGLELINE

设置这个变量为 on 等效于命令行选项 -S 。

SINGLESTEP

设置这个变量为 on 等效于命令选项 -s 。

SQLSTATE

与上一个SQL查询的失败相关的错误代码（见 KingbaseES错误代码 )，如果上一个查询成功则为 00000 。

USER

当前连接的数据库用户。每次连接到一个数据库时都会设置该变量（包括程序启动时），但是可以被更改或者重置。

VERBOSITY

这个变量可以被设置为值 default 、 verbose 、 terse 或者 sqlstate 来控制错误报告的详细程度（另见 \errverbose ，在想得到之前的错误的详细版本时使用）。

VERSION VERSION_NAME VERSION_NUM

这些变量在程序启动时被设置以反映Ksql的版本，分别是一个详细的字符串、一个短字符串（例如 |V8R2| 、 10.1 或者 11beta1 ）以及一个数字（例如 90602 或者 100001 ）。它们可以被更改或重设。

WHENEVER_SQLERROR

此变量为兼容Oracle SQL*Plus语法的Whenever sqlerror语法增加的变量，可以被设置为 continue （默认）或者 exit 。默认情况下，出现一个错误后命令处理会继续下去。当这个变量被设置为 exit 后，出现错误后命令处理会立即停止，Ksql将会退出并且返回错误代码 3 来把这种情况与致命错误区分开来，致命错误会被报告为错误代码 1。任何当前正在运行的脚本（顶层脚本以及任何它已经调用的其他脚本）将被立即中止。如果顶层命名字符串包含多个 SQL 命令，将在当前命令处停止处理。

4.1.9. 停止正在运行的任务

假设您执行了一个结果集较大的查询任务，查询结果已经显示了一部分，但并不需要查看其余部分内容，这时可以使用 Ctrl + C 命令，Ksql 将停止显示。

4.1.10. 运行操作系统命令

可以从 Ksql 命令提示符中执行操作系统命令，当您想要执行诸如列出现有操作系统文件等任务时，这一点非常的有用。

要运行操作系统命令，请输入 Ksql 命令 \! , 然后输入操作系统命令。例如：

-- 查看当前所在位置的系统文件
test=# \! ls
code  data2  logfile  ...

4.1.11. 自动保存对数据库的更改

可以使用 SQL 数据库操作语言(DML)命令更新、插入和删除来指定您希望对存储在数据库中的数据信息所做的更改，这些更改可以单独的使用，也可以在PL/SQL块中使用。在输入 SQL 提交命令或 SQL 数据库控制语言(DCL)或数据库定义语言(DDL)命令(如创建表)或使用自动提交功能之前，这些更改不会永久的保存。Ksql 自动提交特性会导致 SQL DML 事务成功之后的更改被永久的保存到数据库。

您可以使用 Ksql 自动提交系统变量 AUTOCOMMIT 来控制自动提交功能。默认情况下，kingbase 数据库的自动提交是开启的，也就是说任何一个 SQL 语句执行完成后，所做的数据修改都会被立刻提交，这种情况下每个语句都是一个独立事务。

注意

自动提交功能提交开启时，无法回滚对数据的修改。

查看自动提交功能是否开启

test=# \echo :AUTOCOMMIT

设置自动提交功能开启或关闭

test=# \set AUTOCOMMIT on/off

4.1.12. 显示详细错误信息

如果 Ksql 在命令中检测到错误，则会显示错误信息。您通常将能够单独从消息中找到如何纠正这个问题的方式。例如：

test=# SELECT * FROM t2;
ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;

可以使用 \errverbose 命令显示最近的服务器错误信息。例如：

test=# \errverbose
ERROR:  42P01: relation "t2" does not exist
LINE 1: SELECT * FROM t2;
LOCATION:  parserOpenTable, parse_relation.c:1244

4.1.12.1. Oracle兼容

为兼容Oracle SQL*Plus工具的Whenever sqlerror语法功能，Ksql也支持此语法。语法定义为：

WHENEVER SQLERROR { EXIT | CONTINUE  }
EXIT选项：表示当遇到SQL错误、PLPGSQL块错误、PLSQL块错误或者错误的Ksql反斜线命令，将会退出Ksql。
CONTINUE选项（默认值）：表示当遇到SQL错误、PLPGSQL块错误、PLSQL块错误或者错误的Ksql反斜线命令，不会退出Ksql。

4.2. Ksql执行脚本

\i or \include filename 命令从文件 filename 读取输入并且把它当作从键盘输入的命令来执行。例如：

在操作系统当前目录中创建一个脚本文件 test.sql, 输入以下语句：

CREATE TABLE t1
   a int,
   b int
INSERT INTO t1 VALUES(1,1);
INSERT INTO t1 VALUES(2,2);
SELECT * FROM t1;

ksql 中输入以下命令执行 test.sql 文件：

test=# \i test.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
a | b
---+---
1 | 1
2 | 2
(2 rows)

4.2.1. 启动 Ksql 时运行一个脚本

若要在启动 Ksql 时运行脚本，使用以下选项：

• -f filename 从文件 filename 而不是标准输入中读取命令。（详见 option选 中 -f）

例如：按照 Ksql 命令，使用用户名，目标数据库以及将要执行的文件名字。例如：

[test@kes_35_14 ~]$ ksql -p54320 -d test -f test.sql
INSERT 0 1
INSERT 0 1
a | b
---+---
1 | 1
2 | 2
1 | 1
2 | 2
1 | 1
2 | 2
(6 rows)

4.3. SQL中插入变量

Ksql变量的一个关键特性是可以把它们替换（“插入”）到常规SQL语句中，也可以把它们作为元命令的参数。此外，Ksql还提供了功能来确保被用作 SQL 文字和标识符的变量值会被正确地引用。插入一个值而不需要加引用的语法是在变量名前面加上一个冒号（ : ）。例如:

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :foo;

将查询表 my_table 。注意这可能会不安全：该变量的值会被按字面拷贝，因此它可能包含不平衡的引号甚至反斜线命令。必须确保把它放在那里是有意义的。

当一个值被用作 SQL文本或者标识符时，最安全的是把它加上引用。要引用一个变量的值作为SQL文本，可以把变量名称放在单引号中并且在引号前面写一个冒号。要引用作为SQL标识符，则可以把变量名称放在双引号中并且在引号前面写一个冒号。这种结构可以正确地处理变量值中嵌入的引号和其他特殊字符。之前的例子用这种方法写会更安全：

testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";

在被引用的SQL文本和标识符中将不会执行变量插入。因此，一个诸如 ':foo' 的结构不会从一个变量的值产生一个被引用的文本（即便能够也会不安全，因为无法正确地处理嵌入在值中的引号）。

使用这种机制的一个例子是把一个文件的内容拷贝到一个表列中。首先把该文件载入到一个变量，然后把该变量的值作为一个被引用的字符串插入：

testdb=> \set content `cat my_file.txt`
testdb=> INSERT INTO my_table VALUES (:'content');

（注意如果 my_file.txt 包含 NULL字节，这样也不行。Ksql不支持在变量值中嵌入 NULL 字节）。

因为冒号可以合法地出现在 SQL命令中，一次明显的插入尝试（即 :name 、 :'name' 或者 :"name" ）不会被替换，除非所提及的变量就是当前被设置的。在任何情况下，可以用一个反斜线对冒号进行转义以避免它被替换。

:{?name }特殊语法根据该变量存在与否返回TRUE或者FALSE，并且因此总是会被替换，除非分号被反斜线转义。

变量的冒号语法对嵌入式查询语言（例如ESQL）来说是标准的SQL。用于数组切片和类型造型的冒号语法是KingbaseES扩展，它有时可能会与标准用法冲突。把一个变量值转义成SQL 文本或者标识符的冒号引用语法是一种Ksql扩展。

4.4. 格式化Ksql输出

4.4.1. 设置输出格式

Ksql 支持aligned、csv、html、asciidoc 等不同的输出格式，可以使用 \pset format 命令设置结果的输出格式。例如：

peter@localhost testdb=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
peter@localhost testdb=> SELECT * FROM my_table;
-[ RECORD 1 ]-
first | 1
second | one
-[ RECORD 2 ]-
first | 2
second | two
-[ RECORD 3 ]-
first | 3
second | three
-[ RECORD 4 ]-
first | 4
second | four

您可以用 \pset 命令以不同的方式显示表,例如：

peter@localhost testdb=> \pset border 2
Border style is 2.
peter@localhost testdb=> SELECT * FROM my_table;
+-------+--------+
| first | second |
+-------+--------+
|     1 |    one |
|     2 |    two |
|     3 |  three |
|     4 |   four |
+-------+--------+
(4 rows)
peter@localhost testdb=> \pset border 0
Border style is 0.
peter@localhost testdb=> SELECT * FROM my_table;
first second
----- ------ 1 one
    2 two
    3 three
    4 four
(4 rows)
peter@localhost testdb=> \pset border 1
Border style is 1.
peter@localhost testdb=> \pset format csv
Output format is csv.
peter@localhost testdb=> \pset tuples_only
Tuples only is on.
peter@localhost testdb=> SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4
peter@localhost testdb=> \pset format unaligned
Output format is unaligned.
peter@localhost testdb=> \pset fieldsep '\t'
Field separator is " ".
peter@localhost testdb=> SELECT second, first FROM my_table;
one 1
two 2
three 3
four 4

4.4.2. 定义打印的标题

设置用于任何后续被打印表的表标题，可以使用 title 命令。这可以用来给输出加上描述性的标签。如果没有给出标题，这个标题会被复原。例如：

test=# \pset title 'This is test function'
Title is "This is test function".
test=# select * from test();
This is test function
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)

4.4.3. 存储和打印查询结果

要将查询结果存储到文件，可以在 Ksql 中输入 `\o or \out [ filename ] 命令 （详见 \o ）。例如：

test=# \o a.txt
test=# SELECT * FROM t1;
test=# \o

提示

更多有关输出格式的设置参考 \pset 命令 。

4.4.4. \r显示风格

当Ksql设置输出格式为 aligned 和 wrapped 时，如果已经连接到ORACLE模式的KingbaseES服务端，那么对于\r(0x13字节)的显示将会兼容Oracle的显示风格，即遇到\r会将光标插入符移到行首，后面输入的字符可能会覆盖已经打印输出的字符。 比如下面的用例：

testdb=# create table tb(vc varchar(20));
CREATE TABLE
testdb=# insert into tb values ('abc' || chr(13) || '12');
INSERT 0 1
testdb=# select * from tb;
-----
(1 row)
testdb=#

而Ksql连接到PG模式的KingbaseES服务端时，将显示为\r字符。依旧使用上面的用例输出结果如下为：

testdb=# select * from tb;
---------
   abc\r12
(1 row)
testdb=#

注意

在兼容ORACLE显示格式时的使用限制或已知问题：

1. 可能在某些字符集情况下会出现显示乱码的情况。

2. 某些包含\r的列显示宽度可能会和其他列不一致。

4.5. 生成HTML

要想将查询结果生成HTML格式，可以使用以下的方式：

• 使用 \pset format html

• 使用 \H or \html 命令开启HTML查询输出格式，如果HTML格式已经开启，这会把它切换回默认的对齐文本格式。

test=# \H
Output format is html.
<table border="1">
   <th align="center">a</th>
   <th align="center">b</th>
<tr valign="top">
   <td align="right">1</td>
   <td align="right">1</td>
<tr valign="top">
   <td align="right">2</td>
   <td align="right">2</td>
</table>
<p>(6 rows)<br />

4.6. Ksql调优

4.6.1. 统计 SQL 执行时间

使用 Ksql 的 \timing 命令显示运行一个或多个命令块执行的时间。例如：

test=# \timing on
Timing is on.
test=# SELECT * FROM t1;
a | b
---+---
1 | 1
2 | 2
1 | 1
2 | 2
1 | 1
2 | 2
(6 rows)
Time: 1.767 ms

4.6.2. 输出执行计划

使用 EXPLAIN 命令生成执行计划的输出。例如：

test=# EXPLAIN SELECT * FROM t1 ORDER BY a;
                      QUERY PLAN
------------------------------------------------------------
Sort  (cost=158.51..164.16 rows=2260 width=8)
   Sort Key: a
   ->  Seq Scan on t1  (cost=0.00..32.60 rows=2260 width=8)
(3 rows)
Time: 1.115 ms

执行计划显示基于成本的行数估计(基数)。一般来说，每个节点上的代价、基数和字节表示累积的结果。

4.7. Ksql安全

4.7.1. 创建和控制角色

您可以使用 SQL 命令来创建和控制对角色的访问，从而为数据库表提供安全性。通过创建一个角色，然后设定访问的权限，可以确保只有特定的用户可以访问特定数据库的权限。

角色将与SQL的 CREATE、GRANT和SET 命令一起使用：

• 若要创建角色，请使用 CREATE 命令。您可以使用或不使用密码创建角色

• 若要授予对角色的访问权限，请使用 GRANT 命令。通过这种方式，您可以控制与该角色相关联的权限

• 若要访问角色，请使用 SET ROLE 命令。如果您使用密码创建了该角色，则用户必须知道密码才能访问该角色

例如：创建名字为 USER1 的用户并设置对应的访问权限

-- CREATE user
CERATE ROLE USER1 SUPERUSER PASSWORD '123456' login;
-- GRANT PRIVILEGES
GRANT ALL PRIVILEGES ON DATABASE test TO USER1;
-- SET ROLE
session_user | current_user
--------------+--------------
peter        | peter
SET ROLE 'paul';
SELECT SESSION_USER, CURRENT_USER;