kingbaseV7 SQL参考

1. 概述
系统初始化完成后，一共有以下七个数据库：
GLOBAL、TEMPDB、TEMPLATE0、TEMPLATE1、TEMPLATE2、SAMPLES和用户在初始化时指定了名称的一个数据库。
其中前五个是系统数据库，GLOBAL、TEMPDB和TEMPLATE0不能建立用户连接；
TEMPLATE1是用户创建新数据库时使用的模板，对它的任何修改都将体现在以后以其为模板创建的数据库中；
TEMPLATE2供系统管理工具使用。SAMPLES数据库中加载了示例数据，

2. 基本SQL元素
2.1. 基本元素
数据库基本概念，其中包括数据库对象的介绍、数据库的安全性、完整性以及事务处理的策略
2.1.1. 数据库对象
2.1.1.1. 数据类型和值  
数据类型，空值关键字NULL
非空值：用常量表示，可以是字符串、位串、数值等
2.1.1.2. 表
    表（table）为数据库中数据存储的基本单位，其数据按行、列存储。
2.1.1.3. 视图
    视图（view）是CREATE VIEW语句定义的导出表，由一个或多个表（或其他视图）中的数据的一种定制的表示，是用一个查询定义的。
视图与表不同。一个视图不分配任何存储空间，不真正地包含数据
由查询定义的视图对应于视图引用表中的数据。视图只在数据字典中存储其定义。
2.1.1.4. 索引
    索引(index)是与表相关的一种数据结构，是为了提高数据检索的性能而建立的
使用CREATE INDEX语句可以创建索引，使用DROP INDEX语句可以删除索引
KingbaseES提供B-树、HASH索引和GIN索引，每种索引分别适用不同的查询类型。
缺省情况下， CREATE INDEX语句创建B-树索引，CREATE FULLTEXT INDEX语句创建GIN索引。
使用USING method子句可以选择是创建B-树索引，HASH索引或GIN索引。
索引有唯一索引和非唯一索引

2.1.1.5. 存储过程/函数
    存储过程/函数是由过程化语言（PL/SQL）书写，经编译和优化后存储在数据库服务器中的过程/函数
使用CREATE PROCEDURE/FUNCTION 语句创建存储过程/函数，使用CALL语句执行存储过程
使用DROP PROCEDURE/FUNCTION删除存储过程/函数。
2.1.1.6. 触发器
    触发器是一种特殊的存储过程。它在插入、删除或修改特定表和视图中的数据时触发执行
存储过程和触发器最主要的区别在于它们运行的方式不同。存储过程由用户、应用程序或触发器显式调用，
而触发器则是当触发事件发生时由系统触发执行
2.1.1.7. 序列发生器
    通过序列发生器，可以自动产生按一定规律（增加或减小）变化的序列的数值
2.1.1.8. 模式
  一个数据库可以包含一个或多个命名的模式（SCHEMA），一个模式内可以包含多个表。不同的模式中的表名可以相同

模式通常在以下情况下使用： 
多个用户使用同一个数据库而不会相互影响。
对数据库中的对象进行逻辑分组，更便于管理。
各个应用分别使用各自的模式，以避免命名冲突。
使用CREATE SCHEMA语句可以创建模式。在模式中创建数据库对象或访问模式中的对象时，对象名前使用指定的模式名

删除一个空模式（模式中的所有数据库对象已经删除），使用DROP SCHEMA语句，
例如：DROP SCHEMA scott;
如果模式中还有数据库对象，则使用CASCADE子句，删除模式及其中的所有数据库对象，
例如：DROP SCHEMA scott CASCADE;

在创建数据库时，会默认创建两个模式：SYS_CATALOG和PUBLIC。
SYS_CATALOG模式用于存放系统表和所有内置的数据类型、函数和操作符。
用户在创建数据库对象没有指定的模式时，可以使用PUBLIC模式。

显示当前的搜索路径：SHOW search_path;
设置搜索模式：
SET search_path TO myschema,public;

2.1.1.9. 数据库
    数据库是一组SQL对象的集合，每个数据库对象（表、索引、函数等）只能属于一个数据库
使用CREATE DATABASE语句可以创建一个新的数据库，
使用ALTER DATABASE语句可以改变数据库的参数配置，
使用DROP DATABASE语句删除该数据库。

2.1.1.10. 包
 包是由过程化语言(PL/SQL)书写，经编译和优化后存储在数据库服务器中的相关的变量、常量、游标、存储过程和函数的集合，
 
2.1.2. 数据库完整性约束
    完整性是指数据的正确性和相容性
完整性约束是对基表中列值加以限制从而定义数据库的有效状态
2.1.2.1. 实体完整性
  KingbaseES系统通过设定主码（PRIMARY KEY）来实现实体完整性。主码要求所说明的列的值非空且唯一
2.1.2.2. 参照完整性
 参照完整性是指对于"参照表"的某一列或某些列的值至少有一个空值，或者和另一个指明的"被参照表"的某行中指明的一列或若干列的值相同
1.ON DELETE RESTRICT 该子句表示被参照表中的数据行如果有参照表中的数据行参照，则禁止删除被参考表的相应数据行。
2.ON DELETE NO ACTION 与ON DELETE RESTRICT基本相同，只是允许推迟约束的检查时间，当检查该约束时，
如果参考被参考表中数据行的参照表中的数据行还存在，则抛出错误。
3.ON DELETE CASCADE 该子句表示被参照表中的数据行如果有参照表中的数据行参照，
则删除被参照表中相应的数据行时，同时删除参照表中的相应数据行。
4.ON DELETE SET NULL 该子句表示被参照表中的数据行如果有参照表中的数据行参照，
则删除被参照表中相应的数据行时，同时把参照表中的相应数据行的数据置为NULL。
5.ON DELETE SET DEFAULT 该子句表示被参照表中的数据行如果有参照表中的数据行参照，
则删除被参照表中相应的数据行时，同时把参照表中的相应数据行的数据置为默认值。

2.1.2.3. 用户自定义完整性
    KingbaseES支持以下类型的用户自定义完整性： 
UNIQUE，要求所说明的列的值唯一。
NOT NULL，要求所说明的列中没有值是空值。
CHECK（<Expression>），要求由Expression指定的完整性约束条件对该表的任意一行都不为假值

2.1.3. 数据库的用户和权限
2.1.3.1. 用户
KingbaseES 系统初始化时需要指定数据库管理员、安全管理员和审计管理员的用户名和密码。登录后，可以使用 CREATE USER 语句创建新的用户。
使用 ALTER USER 语句可以修改数据库认证用户的口令
2.1.3.2. 权限
  权限是执行一种特殊类型的 SQL 语句或存取某一用户的对象的权力，包括系统权限、对象权限和列级权限
系统权限：是执行特定操作的权限
对象权限：是对给定的用户授予在给定对象（例如表）上执行的操作集
列级权限：是对给定的用户授予在给定表或视图上某些列执行操作集

2.2. SQL语法基础
2.2.1. 字符集
KingbaseES系统中的字符可以是数字、字母、专用字符和汉字，在系统中用于定义语言的终结符号与字符串元素。
数字是指从0到9的阿拉伯数字。
字母是指26个英文字母，包括大写字母(如 A，Z)和小写字母(如a，z)。
专用字符可以是实现者定义的除数字和字母字符外的任意字符。

2.2.2. 标识符与关键字
标识符是标识SQL语句中所使用的表、列或其它数据库对象的名称。
关键字和标识符具有相同的词法结构。关键字是系统使用的标识符，大部分是系统保留使用的
标识符必须以当前字符集所支持的多字节字符、字母或下划线开头，
随后的字符可以是当前字符集所支持的多字节字符、字母、数字（0-9）、$或者下划线
 
在系统初始化时，可以指定参数case_insensitive，
该参数的值决定了标识符在系统存储时大小写的方式和系统进行字符串比较时是否是大小写敏感的。
当case_insensitive为true时，非界定标识符在数据库中都是以大写方式存储，
SQL语句中所有非界定标识符都解析为大写方式，系统在进行字符串比较时，是大小写敏感的。
当case_insensitive为false时，非界定标识符在数据库中都是以用户输入的大小写方式存储，
但是系统在进行字符串比较时，是大小写不敏感的。

2.2.3. 常量
    KingbaseES的SQL中有三种类型的常量：字符串、位串和数值。
SQL中的字符串常量是使用单引号（'）引起来的一串字符
 
2.2.4. 操作符
    KingbaseES的SQL中的操作符： 
    + - * / <> = ~ ! @ # % ^ & | ` ? $ 

2.2.5. 注释
    KingbaseES的SQL可以使用下面两种注释： 
单行注释，使用两个短线-，例如，-- This is a standard SQL comment。
多行注释，使用/* */ 。

2.3. 数据类型
2.3.1. 字符串类型
字符串数据类型用于处理文字和自由格式的文本
CHARACTER[(n [CHAR | BYTE])]
别名：CHAR[(n [CHAR | BYTE])]
说明：定长字符串，如果显式指定了 CHAR，则以字符为单位；如果显式指定了 BYTE，则以字节为单位；
如果没有显式指定，则以系统参数 char_default_type 为准，
CHARACTER VARYING[(n [CHAR | BYTE])]
别名：VARCHAR[(n [CHAR | BYTE])]，VARCHAR2[(n [CHAR | BYTE])]
说明：变长字符串，如果显式指定了 CHAR，则以字符为单位；如果显式指定了 BYTE，则以字节为单位；
如果没有显式指定，则以系统参数 char_default_type 为准
CLOB
说明：字符大对象(CHARACTER LARGE OBJECT)，一般通过ODBC或JDBC进行数据的插入和访问。列存储表目前还不支持此数据类型
TEXT
说明：变长字符串的字符型大文本。
值域：变长，无长度限制，但最大长度最好不要超过64K。

2.3.2. 数值数据类型
数值数据类型分精确数值数据类型和近似数值数据类型两大类。
其中，精确数值数据类型用于存储整数（TINYINT、SMALLINT、INT、BIGINT）和定点小数(NUMERIC、DECIMAL)；
近似数值数据类型用于存储浮点数(FLOAT、REAL、DOUBLE)。
NUMERIC[(p[, s])]
说明：p为数值允许的精度（数值所包含的总的数字位数），s为数值允许的标度（指小数点右边的数字位数）。
列存储表目前还不支持此数据类型
NUMBER[(p[, s])]
说明：NUMBER 等价于 NUMERIC。列存储表目前还不支持此数据类型
BIGINT
说明：有符号整数，占用8个字节。
值域：对于通用行存储版
INTEGER
别名：INT
说明：有符号整数，占用4个字节。
SMALLINT
说明：有符号整数，占用2个字节。
TINYINT
说明：有符号整数，占用1个字节。
FLOAT[(n)]
说明：浮点数字数据，规格定义方法采用SQL92标准。n为用于存储科学记数法FLOAT数尾数的二进制位数，
同时指示其精度和存储大小。n必须为从1到53之间的值
REAL
说明：单精度浮点数字数据，占用4个字节

2.3.3. 日期时间类型
日期时间数据类型用于存储日期时间型数据，有这样几种类型：
DATE：日历日期，默认为（年，月，日），共10位。
DATETIME：日历日期和一天内的时间，默认为（年，月，日，时，分，秒），等同于下面的TIMESTAMP(0) WITHOUT TIME ZONE。
TIME[(n)][WITH TIME ZONE | WITHOUT TIME ZONE]：一天内的时间，n为秒的小数部分精度。
如果指WITH TIME ZONE，则时间中会保留相应的时区。
TIMESTAMP[(n)][WITH TIME ZONE | WITHOUT TIME ZONE]：包括日历日期和时间。秒的精度和时区指定与TIME相同。
DATE
说明：日历日期，默认形式为（年，月，日）。
DATETIME
说明：DATETIME 类型数据包括日期和时间。等同于TIMESTAMP(0) WITH TIME ZONE，占用8个字节。 
TIME[(n)][WITH TIME ZONE | WITHOUT TIME ZONE]
别名：TIME相当于TIME WITHOUT TIME ZONE，TIMETZ相当于TIME WITH TIME ZONE。
说明：一天内的时间，n为秒的小数部分精度
TIMESTAMP[(n)][WITH TIME ZONE | WITHOUT TIME ZONE]
别名：TIMESTAMP 相当于 TIMESTAMP WITHOUT TIME ZONE，TIMESTAMPTZ 相当于 TIMESTAMP WITH TIME ZONE。
说明：TIMESTAMP 类型数据包括日期和时间

2.3.4. 时间间隔类型
    通用的时间数据类型用于存储时间间隔型数据，格式为:
    INTERVAL {INTERVAL限定符}
INTERVAL
说明：时间间隔类型主要分为两类：YEAR-MONTH型和DAY-TIME型。
YEAR-MONTH型只能含有YEAR和MONTH域，DAY-TIME型则可以含有DAY、HOUR、MINUTE、SECOND域
输入格式： 
INTERVAL YEAR/MONTH/DAY/HOUR/MINUTE/SECOND形式的INTERVAL类型支持两种输入格式：包含数值的字符串格式和包括数值和单位的表达式
INTERVAL YEAR TO MONTH/DAY TO SECOND形式的INTERVAL类型仅支持字符串格式的输入，
格式为： 
INTERVAL YEAR TO MONTH形式：'yy-mm'
INTERVAL DAY TO SECOND形式：'dd hh:mm:ss'

2.3.5. 位串
  位串就是一串 1 和 0 的字串，我们有两种类型的 SQL位串类型： BIT(x) 和 BIT VARYING(x)；这里的 x 是一个正整数。
BIT[(n)]
说明：定长位串，即：0、1串，字串全部由0/1组成
BIT VARYING[(n)]
说明：除长度可变外，其他规格与BIT[(n)]基本相同，缺省长度为1。
如果指定长度为n而输入长度不足n时，则保持输入的数据。列存储表目前还不支持此数据类型
2.3.6. 二进制串类型
BLOB
说明：一般通过ODBC或JDBC进行数据的插入和访问。列存储表目前还不支持此数据类型
BYTEA
说明：变长，最大长度为最好不要超过64K字节。BYTEA处理的就是实际上的字节，而与区域设置无关
2.3.7. 布尔型
    布尔型数据类型指BOOLEAN。BOOLEAN只能有两个状态之一： 真(True)或假(False)。
BOOLEAN可用于任何布尔表达式，并且布尔表达式的结果也是布尔类型兼容的。

BOOLEAN
别名：BOOL
说明：逻辑布尔量。占用一位。

3. 操作符
    操作符与函数具有相似的功能。它们都用来对单独的数据项进行操作，接收零个或多个参数，返回一个或多个结果。
操作符与函数不同的是，操作符一般是由特殊的字符或关键字来表示的，而函数则一般以如下形式出现：
      函数名(参数1,参数2, ...)
     大部分的操作符至多接收两个参数，而函数是不受此限制的。
    主要有两类操作符：一元操作符和二元操作符。一元操作符只有一个操作数，典型的一元操作符一般出现在操作数的左边。
二元操作符有两个操作数，一般以如下形式使用：
      操作数1 操作符 操作数2

3.1. 算术操作符
    算术操作符主要用于表达式计算中，对数值进行加、减、乘、除和求反的计算，计算结果仍是数值。
其中一些操作符还可以用于日期型数据间和位串的操作，例如可以进行日期间的加减运算和位串的与、或、异或等操作
3.2. 字符串操作符
    字符串操作符用来对字符串进行连接操作，在这个环境中的字符串包括所有类型为 CHARACTER， CHARACTER VARYING 和 TEXT 的值。
    KingbaseES 中提供了一种字符串操作符 ||，它的主要功能是连接字符串。
 KingbaseES 中还提供了另外一种字符串操作符 +，它的主要功能是连接字符串,但是需要开启系统级参数enable_plus_string_concat。
    系统参数enable_plus_string_concat默认为off,可以在data目录下配置文件中修改系统参数，
或者在数据库启动的时候，通过命令 -c "enable_plus_string_concat=on" 来开启这个参数。

3.3. 二进制型操作符
    KingbaseES 中提供了一种二进制型操作符 ||，它的主要功能是连接二进制型数据。
这里的二进制型数据主要指从 BYTEA 数据类型的列中查询出的数据。

3.4. 日期时间操作符
    一些基本算术操作符还可以用于日期型数据间的计算，例如可以进行日期间的加减运算。
表 3-2 日期时间操作符
名字 例子 结果 
* cast('10' as interval hour) * int '3' '+30' 
/ cast('9' as interval hour) / int '3' '+03' 
+ timestamp '2001-09-28 01:00' + cast('23' as interval hour) '2001-09-29 00:00:00' 
+ date '2001-09-28' + cast('1' as interval hour) '2001-09-28 01:00:00' 
+ time '01:00' + cast('3' as interval hour) '04:00:00' 
- timestamp '2001-09-28 23:00' - cast('23' as interval hour) '2001-09-28 00:00:00' 
- date '2001-09-28' - cast('1' as interval hour) '2001-09-27 23:00:00' 
- time '05:00' - cast('2' as interval hour) '03:00:00' 

3.5. 位串操作符
    算术运算符中的“二进制”操作符还可以用于位串类型 BIT 和 BIT VARYING。&，|，和 # 的位串参数必须等长
3.6. 比较操作符
    比较操作符用来判断查询条件是否满足，通常是对两表达式进行比较，比较的结果要么为真(TRUE)，要么为假(FALSE)。
相等=，不相等!=,<> 大于>, 小于<, 大于等于>=, 小于等于<=
等价于"=any" in , not in, all, between x and y,
exists, like, is not null,
3.7. 逻辑操作符
    逻辑操作符用来构造具有多条件的查询语句。用AND操作符连接的各个条件都为TRUE时，整个查询条件才为TRUE。
而用OR连接的条件中只需要其中一个条件为TRUE，整个查询条件即为TRUE。
当求值顺序没有通过括号指明时，应 NOT 优先于 AND，AND 优先于 OR，具有相同优先级的二目运算符（AND或OR）自左到右运算，一目操作符（NOT）自右至左运算。

3.8. 集合操作符
    集合操作符用来合并多个查询块的查询结果，其操作对象为集合。
三个集合操作符中，INTERSECT 的优先级别最高，UNION 和 EXCEPT 有相同的优先级别
操作符 功能 
UNION [ALL] 求各查询块的查询结果的并集，消除了重复行。若使用了 ALL 选项，则合并结果中不消除重复的行  
INTERSECT 求各查询的查询结果的交集，求交结果中取消重复行 
EXCEPT 求两查询的查询结果的差集，求差结果中没有重复行

3.9. 全文检索操作符
tsquery的操作符:
操作符 功能 
&& 与操作符，结果是两个 tsquery 的与操作
||  或操作符，结果是两个tsquery的或操作
!! 非操作符，结果是非tsquery中条件的情况
@> 包含操作符。即左边的tsquery是否包含右边的tsquery
<@ 被包含操作符。即左边的tsquery是否被右边的tsquery包含 

3.10. 层次查询操作符
层次查询的操作符:
    层次查询的操作符只能用在层次查询中（指定CONNECT BY子句），
层次查询操作符
操作符 功能 实例 
PRIOR 在当前元组的父元组上求值，如果当前元组是根（Root）元组，则求值为NULL。
CONNECT_BY_ROOT 在当前层次查询的根（Root）元组上求值。 
注意: 
KingbaseES中，CONNECT_BY_ROOT不能用在CONNECT BY子句和START WITH子句中。
层次查询运算符不能嵌套使用，并且其操作数不能是层次查询伪列。