# SQL参考
# 1.SQL规范一致性
Ignite直接支持ANSI-99标准的主要特性,下面的表格会显示Ignite与SQL:1999 (核心)的兼容性。
特性ID,特姓名 | 支持度 |
---|---|
E011 数值数据类型 | Ignite完全支持下面的子特性: E011–01:INTEGER和SMALLINT数据类型(包括各种拼写) E011–02:REAL,DOUBLE PRECISON以及FLOAT数据类型 E011–05:数值比较 E011–06:数值类型间的隐性转换 Ignite部分支持下面的子特性: E011–03:DECIMAL和NUMERIC数据类型,目前不支持固定精度 E011–04:算术运算符 |
E021 字符串类型 | Ignite完全支持下面的子特性: E021–03:字符字面量 E021–04:CHARACTER_LENGTH函数 E021–05:OCTET_LENGTH函数 E021–06:SUBSTRING函数 E021–07:字符串拼接 E021–08:UPPER和LOWER函数 E021–09:TRIM函数 E021–10:不可变长度和可变长度字符串之间的隐式转换 E021–11:POSITION函数 E021–12:字符串比较 Ignite部分支持下面的子特性: E021–01:CHARACTER数据类型(包括各种拼写) E021–02:CHARACTER VARYING数据类型(包括各种拼写) |
E031 标识符 | Ignite完全支持下面的子特性: E031–01:分割标识符 E031–02:小写标识符 E031–03:下划线结尾 |
E051 基本查询规范 | Ignite完全支持下面的子特性: E051–01:SELECT DISTINCT E051–04:GROUP BY可以包含SELECT字段列表中没有的列 E051–05:SELECT列表项可以重命名 E051–06:HAVING子句 E051–07:SELECT中的限定符* E051–08:FROM子句中的别名 Ignite不支持下面的子特性: E051–02:GROUP BY子句,不支持ROLLUP、CUBE、GROUPING SETS E051–09:FROM子句列重命名 |
E061 基本谓词和查询条件 | Ignite完全支持下面的子特性: E061–01:比较谓词 E061–02:BETWEEN谓词 E061–03:包含值列表的IN谓词 E061–06:NULL谓词 E061–08:EXISTS谓词 E061–09:比较谓词中的子查询 E061–11:IN谓词中的子查询 E061–13:子查询别名 E061–14:检索条件 Ignite部分支持下面的子特性: E061–04:LIKE谓词 E061–05:LIKE谓词,ESCAPE子句 E061–07:限定比较谓词 Ignite不支持下面的子特性: E061–12:限定比较谓词中的子查询 |
E071 基本查询表达式 | Ignite部分支持下面的子特性: E071–01:UNION DISTINCT表运算符 E071–02:UNION ALL表运算符 E071–03:EXCEPT DISTINCT表运算符 E071–05:通过表运算符组合的列不必具有完全相同的数据类型 E071–06:子查询中的表运算符 |
E081 基本权限 | Ignite不支持下面的子特性: E081–01:表级SELECT权限 E081–02:DELETE权限 E081–03:表级INSERT权限 E081–04:表级UPDATE权限 E081–05:列级UPDATE权限 E081–06:表级REFERENCES权限 E081–07:列级REFERENCES权限 E081–08:WITH GRANT OPTION E081–09:USAGE权限 E081–10:EXECUTE权限 |
E091 聚合函数 | Ignite部分支持下面的子特性: E091–01:AVG E091–02:COUNT E091–03:MAX E091–04:MIN E091–05:SUM E091–06:ALL限定符 E091–07:DISTINCT限定符 |
E101 基本数据维护 | Ignite完全支持下面的子特性: E101–03:已检索的UPDATE语句 E101–04:已检索的DELETE语句 Ignite部分支持下面的子特性: E101–01:INSERT语句不支持DEFAULT默认值 |
E111 单行SELECT语句 | 目前不支持 |
E121 基本游标支持 | Ignite不支持下面的子特性: E121–01:DECLARE CURSOR E121–02:ORDER BY中的列可以不在SELECT字段列表中 E121–03:ORDER BY子句中的值表达式 E121–04:OPEN语句 E121–06:UPDATE语句中的定位 E121–07:DELETE语句中的定位 E121–08:CLOSE语句 E121–10:FETCH语句,隐式NEXT E121–17:WITH HOLD游标 |
E131 NULL值支持 | Ignite完全支持此特性 |
E141 基本完整性约束 | Ignite完全支持下面的子特性: E141–01:NOT NULL约束 Ignite部分支持下面的子特性: E141–03:PRIMARY KEY约束 E141–08:PRIMARY KEY推断NOT NULL Ignite不支持下面的子特性: E141–02:NOT NULL列的UNIQUE约束 E141–04:对引用的删除和更新操作,都具有默认NO ACTION的基本FOREIGN KEY约束 E141–06:CHECK约束 E141–07:列默认值 E141–10:外键中的列名没有顺序约束 |
E151 事务支持 | Ignite不支持下面的子特性: E151–01:COMMIT语句 E151–02:ROLLBACK语句 |
E152 基本SET TRANSACTION语句 | Ignite不支持下面的子特性: E152–01:SET TRANSACTION语句: ISOLATION LEVEL SERIALIZABLE子句 E152–02:SET TRANSACTION语句: READ ONLY和READ WRITE子句 |
E153 可更新的带子查询的查询 | Ignite完全支持此特性 |
E161 双减号开头的SQL注释 | Ignite完全支持此特性 |
E171 SQLSTATE支持 | Ignite部分支持本特性,实现了标准错误码的子集,并且引入了部分自定义的错误码 |
E182 主机语言绑定(以前的“模块语言”) | 目前不支持 |
F021 基本信息模式 | Ignite不支持下面的子特性: F021–01:COLUMNS视图 F021–02:TABLES视图 F021–03:VIEWS视图 F021–04:TABLE_CONSTRAINTS视图 F021–05:REFERENTIAL_CONSTRAINTS视图 F021–06:CHECK_CONSTRAINTS视图 |
F031 基本模式维护 | Ignite完全支持下面的子特性: F031–04:ALTER TABLE语句,ADD COLUMN子句 Ignite部分支持下面的子特性: F031–01:持久化的表的CREATE TABLE语句,支持基本语法,但是不支持AS,不支持权限(INSERT、SELECT、UPDATE、DELETE) Ignite不支持下面的子特性: F031–02:CREATE VIEW语句 F031–03:GRANT语句 F031–13:DROP TABLE语句,RESTRICT子句 F031–16:DROP VIEW语句,RESTRICT子句 F031–19:REVOKE语句,RESTRICT子句 |
F041 基本表联接 | Ignite完全支持下面的子特性: F041–01:内联接(INNER关键字不是必须的) F041–02:INNER关键字 F041–03:LEFT OUTER JOIN F041–04:RIGHT OUTER JOIN F041–05:外联接可以嵌套 F041–07:左或右外联接中的内部表也可以用于内联接 F041–08:所有的比较运算符都支持,不仅仅是= |
F051 基本时期和时间 | Ignite完全支持下面的子特性: F051–04:DATE、TIME和TIMESTAMP数据类型上的比较谓词 F051–05:日期时间类型和字符串类型之间的显式类型转换 F051–06:CURRENT_DATE F051–07:LOCALTIME F051–08:LOCALTIMESTAMP Ignite部分支持下面的子特性: F051–01:DATE数据类型(包括DATE字面量) F051–02:TIME数据类型(包括TIME字面量) F051–03:TIMESTAMP数据类型(包括TIMESTAMP字面量) |
F081 视图中的UNION和EXCEPT | 暂不支持 |
F131 分组运算符 | Ignite不支持下面的子特性: F131–01:带有分组视图的查询中支持WHERE、GROUP BY和HAVING子句 F131–02:带有分组视图的查询中支持多个表 F131–03:带有分组视图的查询中支持聚合函数 F131–04:带有GROUP BY和HAVING子句和分组视图的子查询 F131–05:带有GROUP BY和HAVING子句和分组视图的单行SELECT |
F181 多模块支持 | 暂不支持 |
F201 CAST函数 | Ignite完全支持此特性 |
F221 显式默认值 | Ignite完全支持此特性 |
F261 CASE表达式 | Ignite完全支持下面的子特性: F261–01:简单CASE F261–02:已检索CASE F261–03:NULLIF F261–04:COALESCE |
F311 模式定义语句 | Ignite不支持下面的子特性: F311–01:CREATE SCHEMA F311–02:持久化的表的CREATE TABLE F311–03:CREATE VIEW F311–04:CREATE VIEW: WITH CHECK OPTION F311–05:GRANT语句 |
F471 标量子查询值 | Ignite完全支持此特性 |
F481 扩展NULL谓词 | Ignite完全支持此特性 |
F501 特性和一致性视图 | Ignite不支持下面的子特性: F501–01:SQL_FEATURES视图 F501–02:SQL_SIZING视图 F501–03:SQL_LANGUAGES视图 |
F812 基本标志 | 暂不支持 |
S011 明确的数据类型 | Ignite不支持下面的子特性: S011–01:USER_DEFINED_TYPES视图 |
T321 基本SQL调用的存储过程 | Ignite不支持下面的子特性: T321–01:无过载的用户定义函数 T321–02:无过载的用户定义存储过程 T321–03:函数调用 T321–04:CALL语句 T321–05:RETURN语句 T321–06:ROUTINES视图 T321–07:PARAMETERS视图 |
# 2.数据定义语言(DDL)
# 2.1.CREATE TABLE
该命令会创建一个新的Ignite缓存,并在其之上定义一个SQL表,底层的缓存以键值对的形式存储数据,而表则可以使用SQL对数据进行查询。
该表会位于连接参数指定的模式中,如果未指定模式,则默认值为PUBLIC
。
CREATE TABLE
命令是同步的,此外,它还会阻止在该命令完成之前发起的其他DDL命令的执行,DML命令的执行不受影响,可以并行。
CREATE TABLE [IF NOT EXISTS] tableName (tableColumn [, tableColumn]...
[, PRIMARY KEY (columnName [,columnName]...)])
[WITH "paramName=paramValue [,paramName=paramValue]..."]
tableColumn := columnName columnType [DEFAULT defaultValue] [PRIMARY KEY]
参数
tableName
:表名;tableColumn
:新表中一个列的名字和类型;columnName
:之前定义的一个列名;DEFAULT
:指定列的默认值,只能接受常量值;IF NOT EXISTS
:只有同名表不存在时才会创建新表;PRIMARY KEY
:定义表的主键,可以由一个或者多个列组成;WITH
:标准ANSI SQL中未定义的参数:TEMPLATE=<缓存模板名>
:大小写敏感的缓存模板名。一个模板是在集群中通过Ignite.addCacheConfiguration
注册的CacheConfiguration
类的实例。使用预定义的TEMPLATE=PARTITIONED
或者TEMPLATE=REPLICATED
模板,可以直接创建对应复制类型的缓存,其它的参数由CacheConfiguration
对象决定,在没有显式指定的情况下,默认会使用TEMPLATE=PARTITIONED
模板。BACKUPS=<备份数>
:设置数据的备份数量,如果未指定这个参数,或者未指定任意的TEMPLATE
参数,那么创建的缓存备份数量为0;ATOMICITY=<ATOMIC | TRANSACTIONAL>
:为底层缓存设置原子化模式模式,如果该参数和TEMPLATE
参数都未指定,那么创建的缓存为ATOMIC
模式;WRITE_SYNCHRONIZATION_MODE=<PRIMARY_SYNC | FULL_SYNC | FULL_ASYNC>
:设置底层缓存的写同步模式,如果未指定这个参数,或者未指定任意的TEMPLATE
参数,那么创建的缓存为FULL_SYNC
模式;CACHE_GROUP=<缓存组名>
:设置底层缓存所属的缓存组名;AFFINITY_KEY=<关联键列名>
:设置关联键名字,它应该是PRIMARY KEY
约束中的一个列;CACHE_NAME=<新缓存的自定义名字>
:该命令创建的底层缓存的名字,如果该参数不指定,则会使用SQL_{SCHEMA_NAME}_{TABLE}
的形式;DATA_REGION=<已有的数据区名>
:表数据存储的数据区的名字,Ignite默认会将所有的数据存储于默认的数据区中;PARALLELISM=<SQL执行线程数>
:SQL查询在每个节点默认是以单线程模式执行的,但是某些场景以多线程方式执行性能会更好,具体请参见查询并行度相关章节的内容;KEY_TYPE=<自定义键类型名>
:设置自定义键类型的名字,用于Ignite的键值API中,名字需要与Java、.NET和C++的类相对应,或者如果使用了二进制对象而不是自定义类时,也可以是随机的。在自定义键中,字段的数量和类型需要与PRIMARY KEY
相对应,具体参见使用非SQL API章节的内容;VALUE_TYPE=<自定义值类型名>
:设置自定义值类型的名字,用于Ignite的键值API以及其它的非SQL API中。名字需要与Java、.NET和C++的类相对应,或者如果使用了二进制对象而不是自定义类时,也可以是随机的。值类型需要包含在CREATE TABLE命令中定义的所有列,但是PRIMARY KEY
约束中列出的不算,具体参见使用非SQL API章节的内容。另外,对于使用CDC进行数据复制的场景,如果要使用SQL查询,两端也要指定相同的VALUE_TYPE
;WRAP_KEY=<true | false>
:这个标志控制单列主键是否会被包装成二进制对象形式,这个标志默认值为false
,这个标志对多列的PRIMARY KEY
不会产生影响,不管这个参数值是什么,它总是会被包装;WRAP_VALUE=<true | false>
:这个标志控制单列基本类型的值是否会被包装成二进制对象形式,这个标志默认值为true
,这个标志对多列的值不会产生影响,不管这个参数值是什么,它总是会被包装。如果表中只有一个列,并且没有计划添加额外的列时,可以将其配置为false
。注意如果该参数配置为false
,就无法在该表上执行ALTER TABLE ADD COLUMN
命令;
关于数据库模式方面的更多信息,请参见这里的介绍。
# 2.1.1.定义主键
下面的示例显示如何使用列定义中指定的PRIMARY KEY
创建表,并覆盖缓存相关参数。其将创建一个新的分布式缓存SQL_PUBLIC_PERSON
(命名规则是SQL_{SCHEMA_NAME}_{TABLE}
格式),该缓存会存储与特定的Java、.NET、C++类或二进制对象相对应的Person
类型的对象。
与分布式缓存相关的参数在语句的WITH
子句中传递。如果省略WITH
子句,则将使用在CacheConfiguration
对象中设置的默认参数创建缓存。
CREATE TABLE Person (
id int PRIMARY KEY,
city_id int,
name varchar,
age int,
company varchar
) WITH "atomicity=transactional,cachegroup=somegroup";
# 2.1.2.使用非SQL API
如果希望使用键-值或者其他的非SQL API访问该表,那么需要指定CACHE_NAME
,以及和业务模型对象相对应的KEY_TYPE
和VALUE_TYPE
参数,以便于非SQL API的使用。
- 使用
CACHE_NAME
参数来覆盖SQL_{SCHEMA_NAME}_{TABLE}
格式的默认值; - 该命令默认会创建两个新的二进制类型,分别对应键和值,Ignite会随机生成UUID格式字符串形式的类型名,这会使非SQL API使用这些类型变得困难。
下面的示例显示如何创建PERSON
表和同名的底层缓存。缓存将存储Person
类型的对象,其显式定义了键类型PersonKey
和值类型PersonValue
。PRIMARY KEY
中的列将用作对象的键,其余列将属于值。
CREATE TABLE IF NOT EXISTS Person (
id int,
city_id int,
name varchar,
age int,
company varchar,
PRIMARY KEY (id, city_id)
) WITH "template=partitioned,backups=1,affinity_key=city_id,CACHE_NAME=Person,KEY_TYPE=PersonKey,VALUE_TYPE=PersonValue";
# 2.1.3.使用非大写列名
Ignite会解析所有不带引号的标识符和列名,并在CREATE TABLE
命令执行期间将其转为大写,这给显示定义键类型和值类型的场景带来了麻烦。
这时有如下的选择来处理这个问题:
- 使用QuerySqlField注解:执行
CREATE TABLE
命令时会创建列别名,这会阻止每次对非大写字段的检查; - 注意,列名默认每次都转换为大写,必须确保DDL字段和缓存类型字段始终与字母大小写匹配。
在下面的示例中,可以看到CREATE TABLE
命令中的驼峰样式affKey
字段加上了引号,这与PersonKey
键类型中的相同属性相匹配。
CREATE TABLE IF NOT EXISTS Person (
id INT,
"affKey" INT,
val VARCHAR,
PRIMARY KEY (id, "affKey")
) WITH "template=partitioned,backups=1,affinity_key=affKey,CACHE_NAME=Person,KEY_TYPE=PersonKey,VALUE_TYPE=PersonValue";
class PersonKey {
private int id;
/*
* This is a camel case field 'affKey' must match the DDL table schema, so you must be sure:
* - Using the quoted "affKey" field name in the DDL table definition;
* - Convert the 'affKey' field to the upper case 'AFFKEY' to match the DDL table definition;
*/
@AffinityKeyMapped
private int affKey;
public PersonKey(int id, int affKey) {
this.id = id;
this.affKey = affKey;
}
}
注意一些和Ignite集成的技术,比如Spring Data的CrudRepository
,并不支持加引号的字段来访问数据。
# 2.2.ALTER TABLE
修改已有表的结构:
ALTER TABLE [IF EXISTS] tableName {alter_specification}
alter_specification:
ADD [COLUMN] {[IF NOT EXISTS] tableColumn | (tableColumn [,...])}
| DROP [COLUMN] {[IF EXISTS] columnName | (columnName [,...])}
| {LOGGING | NOLOGGING}
tableColumn := columnName columnType
ALTER TABLE的范围
目前,Ignite只支持列的增加和删除,在即将发布的版本中,命令的语法和功能将进一步扩展。
参数
tableName
:表名tableColumn
:表中要增加的列名和类型columnName
:要增加或者删除的列名IF EXISTS
:如果对应表,如果指定表名的表不存在,则不会抛出异常;如果对应列,如果指定列名的列不存在,则不会抛出异常IF NOT EXISTS
:如果同名的列已经存在不会抛出错误LOGGING
:开启预写日志,预写日志默认开启,只有开启持久化,该指令才有意义NOLOGGING
:禁用预写日志,只有开启持久化,该指令才有意义
描述
ALTER TABLE ADD
为之前创建的表增加一个或者多个列,列添加了之后,就可以在DML语句中进行访问,并且在CREATE INDEX语句中增加索引。
ALTER TABLE DROP
从一个表中删除一个或者多个列,删除之后,查询就会无法访问,并且要考虑如下的特殊性和局限性:
- 命令无法从集群中删除实际的数据,这意味着如果删除了列
name
,列name
对应的值还在集群中保存着,这个限制会在下个版本中解决; - 如果该列被索引,则索引需要使用
DROP INDEX
命令手工删除; - 无法删除主键或者主键的一部分;
- 如果该列表示整个值则也无法删除,这个限制与原始值有关。
Ignite以键-值对的形式存储数据,所有新增的列都属于值,键(PRIMARY KEY
)所属的列是无法修改的。
ALTER TABLE
语句在执行过程中,相同表的DDL和DML语句都会被阻塞一小段时间。
如果开启了Ignite的持久化,这个命令对应的模式改变也会被持久化到磁盘,因此即使整个集群重启也不会受到影响。
示例
给表增加一列:
ALTER TABLE Person ADD COLUMN city varchar;
只有没有同名列时才会为表增加新列:
ALTER TABLE City ADD COLUMN IF NOT EXISTS population int;
只有表存在时才会增加新列:
ALTER TABLE IF EXISTS Missing ADD number long;
一次为表增加多个列:
ALTER TABLE Region ADD COLUMN (code varchar, gdp double);
从表中删除一个列:
ALTER TABLE Person DROP COLUMN city;
只有指定的列存在时才从表删除列:
ALTER TABLE Person DROP COLUMN IF EXISTS population;
只有表存在时才删除列:
ALTER TABLE IF EXISTS Person DROP COLUMN number;
一次删除多个列:
ALTER TABLE Person DROP COLUMN (code, gdp);
禁用预写日志:
ALTER TABLE Person NOLOGGING
# 2.3.DROP TABLE
DROP TABLE
命令会删除现有的表,底层的缓存及其数据也会被销毁。
DROP TABLE [IF EXISTS] tableName
参数
tableName
:要删除的表名;IF EXISTS
:如果指定名字的表不存在,不会抛出错误。
描述
在DROP TABLE
命令执行过程中,相同表的其它DDL和DML命令都会被阻塞,,在表删除后,所有挂起的命令都会报错。
如果开启了Ignite的持久化,这个命令对应的模式改变也会被持久化到磁盘,因此即使整个集群重启也不会受到影响。
示例
如果Person表存在,将其删除:
DROP TABLE IF EXISTS "Person";
# 2.4.CREATE INDEX
为表创建索引。
CREATE [SPATIAL] INDEX [[IF NOT EXISTS] indexName] ON tableName
(columnName [ASC|DESC] [,...]) [(index_option [...])]
index_option := {INLINE_SIZE size | PARALLEL parallelism_level}
参数
indexName
:要创建的索引名,索引名在模式层级必须是唯一的;ASC
:升序排列(默认);DESC
:降序排列;SPATIAL
:创建空间索引,目前只支持几何类型;IF NOT EXISTS
:如果同名索引已经存在则不会抛出错误,数据库只检查索引名,不考虑列的类型和数量,如果在模式中存在同名的索引名,则索引的创建会被忽略;index_option
:创建索引的其它选项:INLINE_SIZE
:指定索引内联大小(字节),根据这个大小,Ignite会将整个索引值或者其一部分直接放入索引页中,这样会避免对数据页的额外调用以及提高查询性能。索引内联是默认开启的,然后其大小是根据表结构预先自动计算好的。将其值配置为0可以禁用内联功能,但是通常不推荐,具体可以看增加索引内联章节的介绍;PARALLEL
:指定用于索引并行创建的线程数。线程数越多,索引创建的就越快,如果该值超过了CPU数量,那么它会被减少到内核的数量。如果参数未指定,那么线程数的默认值为可用CPU核数的25%。
描述
CREATE INDEX
为指定的表创建一个新的索引,常规的索引存储于内部的B+树数据结构中,该B+树和实际的数据一样,在集群内进行分布,一个集群节点会存储其所属的数据对应的索引的一部分。
如果CREATE INDEX
在运行时执行,那么数据库会在指定的索引列上同步地迭代,CREATE INDEX
执行完之前同一个表的其它DDL命令会被阻塞,但是DML语句的执行不受影响,还是并行地执行。
如果开启了Ignite的持久化,这个命令对应的模式改变也会被持久化到磁盘,因此即使整个集群重启也不会受到影响。
# 2.4.1.索引的权衡
为应用选择索引时,需要考虑很多事情。
- 索引是有代价的:它们消耗内存,每个索引需要单独更新,因此当配置了很多索引时,缓存的写入性能会下降。最重要的是,执行查询时如果选择了错误的索引优化器会犯更多的错误。
注意
索引每个字段是错误的!
- 索引只是有序数据结构(B+树),如果在字段(a,b,c)上定义了索引,那么记录的排序会是首先是a,然后b,再然后c。
有序索引示例
| A | B | C |
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 4 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 5 |
任意条件,比如a = 1 and b > 3
,都会被视为有界范围,在log(N)
时间内两个边界在索引中可以被快速检索到,然后结果就是两者之间的任何数据。
下面的条件会使用索引:
a = ?
a = ? and b = ?
a = ? and b = ? and c = ?
从索引的角度,条件a = ?
和c = ?
不会好于a = ?
明显地,半界范围a > ?
可以工作得很好。
- 单字段索引不如以同一个字段开始的多字段组合索引(索引(a)不如索引(a,b,c)),因此要优先使用组合索引;
- 如果指定了
INLINE_SIZE
属性,索引在B+树页面中会保存字段数据的前缀。这会通过更少的行数据检索提高搜索性能,但是树的大小大大增加(树高度的适度增加),并且由于过多的页面分割和合并从而减少了数据插入和删除的性能。在选择树的内联大小时考虑页面大小比较好:每个B-树条目需要页面中16+内联大小的字节(加上头和页面的额外链接)。
示例
创建常规索引:
CREATE INDEX title_idx ON books (title);
只有在不存在时创建倒序索引:
CREATE INDEX IF NOT EXISTS name_idx ON persons (firstName DESC);
创建组合索引:
CREATE INDEX city_idx ON sales (country, city);
创建指定内联大小的索引:
CREATE INDEX fast_city_idx ON sales (country, city) INLINE_SIZE 60;
创建空间索引:
CREATE SPATIAL INDEX idx_person_address ON Person (address);
# 2.5.DROP INDEX
删除表的一个索引。
DROP INDEX [IF EXISTS] indexName
参数
indexName
:要删除的索引名;IF EXISTS
:如果指定名字的索引不存在,不会抛出错误,数据库只会校验索引的名字,不考虑列的类型和数量。
描述
DROP INDEX
命令会删除之前创建的一个索引。
相同表的其它DDL命令会被阻塞,直到DROP INDEX
执行完成,DML命令的执行不受影响,仍然会以并行的方式执行。
如果开启了Ignite的持久化,这个命令对应的模式改变也会被持久化到磁盘,因此即使整个集群重启也不会受到影响。
示例
删除一个索引:
DROP INDEX idx_person_name;
# 2.6.CREATE USER
用指定的用户名和密码创建用户。
只有超级用户才能创建新用户。集群第一次启动时,Ignite创建了超级用户账户,名为ignite
,密码为ignite
,目前,无法修改超级用户的用户名,也不能将超级用户的权限赋予其它账户。
CREATE USER userName WITH PASSWORD 'password';
参数
userName
:新用户的名字,用户名长度不能超过UTF8编码的60个字节;password
:新用户的密码,密码不能为空。
描述
该命令用指定的用户名和密码创建用户。
要创建一个大小写敏感的用户名,需要使用双引号(")SQL标识符。
什么时候关注大小写敏感的用户名?
JDBC/ODBC接口只支持大小写不敏感的用户名,如果打算使用Java、.NET或者其它编程语言的API访问Ignite,那么要么使用大写字母,要么使用双引号传递用户名。
比如,如果用户名为Test
:
1.在JDBC/ODBC中可以使用Test
、TEst
、TEST
等各种组合;
2.在Ignite为Java、.NET或者其它编程语言设计的原生SQL API中,可以使用TEST
或者"Test"
作为用户名。
另外,尽量使用大小写敏感的用户名以保证所有SQL接口的名字一致性。
示例
使用test
作为用户名和密码创建用户:
CREATE USER test WITH PASSWORD 'test';
创建大小写敏感的用户名:
CREATE USER "TeSt" WITH PASSWORD 'test'
# 2.7.ALTER USER
修改已有用户的密码,该密码可以被超级用户ignite
或者用户自己更新。
ALTER USER userName WITH PASSWORD 'newPassword';
参数
userName
:已有的用户名;newPassword
:用户的新密码。
示例
更新用户的密码:
ALTER USER test WITH PASSWORD 'test123';
# 2.8.DROP USER
该命令会删除一个已有用户,只有超级用户(ignite
)才能删除用户。
DROP USER userName;
参数
userName
:要删除的用户名。
示例
删除用户:
DROP USER test;
# 2.9.ANALYZE
该命令用于收集统计信息。
ANALYZE 'schemaName'.'tableName'(column1, column2);
参数
schemaName
:要收集统计信息的模式名;tableName
:要收集统计信息的表名;(column1, column2)
:要收集统计信息的列名。
当ANALYZE命令与WITH
参数语句一起使用时,指定的参数将应用于每个目标。例如:
ANALYZE public.statistics_test, statistics_test2, statistics_test3(col3) WITH 'MAX_CHANGED_PARTITION_ROWS_PERCENT=15,NULLS=0'
可用的参数如下:
MAX_CHANGED_PARTITION_ROWS_PERCENT
:表中过时行的最大百分比(默认值为15%);NULLS
:列中空值的数量;TOTAL
:列值的总数;SIZE
:列值的平均大小(字节);DISTINCT
:列中不同的非空值的数量。
# 2.10.REFRESH STATISTICS
该命令用于刷新统计数据。
REFRESH STATISTICS 'schemaName'.'tableName'(column1, column2);
参数
schemaName
:要收集统计信息的模式名;tableName
:要收集统计信息的表名;(column1, column2)
:要收集统计信息的列名。
示例
REFRESH STATISTICS PRODUCTS, SALE(productId, discount)
# 2.11.DROP STATISTICS
该命令用于删除统计数据。
DROP STATISTICS 'schemaName'.'tableName'(column1, column2);
参数
schemaName
:要收集统计信息的模式名;tableName
:要收集统计信息的表名;(column1, column2)
:要收集统计信息的列名。
示例
DROP STATISTICS USERS, ORDERS(customerId, productId)
# 3.数据操作语言(DML)
# 3.1.SELECT
从一张表或者多张表中获得数据。
SELECT
[TOP term] [DISTINCT | ALL] selectExpression [,...]
FROM tableExpression [,...] [WHERE expression]
[GROUP BY expression [,...]] [HAVING expression]
[{UNION [ALL] | MINUS | EXCEPT | INTERSECT} select]
[ORDER BY order [,...]]
[{ LIMIT expression [OFFSET expression]
[SAMPLE_SIZE rowCountInt]} | {[OFFSET expression {ROW | ROWS}]
[{FETCH {FIRST | NEXT} expression {ROW | ROWS} ONLY}]}]
参数
DISTINCT
:配置从结果集中删除重复数据;GROUP BY
:通过给定的表达式对结果集进行分组;HAVING
:分组后过滤;ORDER BY
:使用给定的列或者表达式对结果集进行排序;LIMIT
和FETCH FIRST/NEXT ROW(S) ONLY
:对查询返回的结果集的数量进行限制(如果为null或者小于0则无限制);OFFSET
:配置忽略的行数;UNION
、INTERSECT
、MINUS
、EXPECT
:将多个查询的结果集进行组合;tableExpression
:表联接。联接表达式目前不支持交叉联接和自然联接,自然联接是一个内联接,其条件会自动加在同名的列上;
tableExpression = [[LEFT | RIGHT]{OUTER}] | INNER | CROSS | NATURAL]
JOIN tableExpression
[ON expression]
LEFT
:左联接执行从第一个(最左边)表开始的联接,然后匹配任何第二个(最右边)表的记录;RIGHT
:右联接执行从第二个(最右边)表开始的联接,然后匹配任何第一个(最左边)表的记录;OUTER
:外联接进一步细分为左外联接、右外联接和完全外联接,这取决于哪些表的行被保留(左、右或两者);INNER
:内联接要求两个合并表中的每一行具有匹配的列值;CROSS
:交叉连接返回相关表数据的笛卡尔积;NATURAL
:自然联接是等值联接的一个特殊情况;ON
:要联接的条件或者值。
描述
SELECT
查询可以在分区
和复制
模式的数据上执行。
当在全复制的数据上执行的时候,Ignite会将查询发送到某一个节点上,然后在其本地数据上执行。
如果查询在分区数据上执行,那么执行流程如下:
- 查询会被解析,然后拆分为多个映射查询和一个汇总查询;
- 所有的映射查询会在请求数据所在的所有节点上执行;
- 所有节点将本地执行的结果集返回给查询发起方(汇总),然后在将数据正确地合并之后完成汇总阶段。
关联
Ignite支持并置以及非并置的分布式SQL关联。
在分区和复制模式的数据集上进行关联也没有任何的限制。
但是,如果关联了分区模式的数据,那么要注意,要么将关联的键并置在一起,要么为查询开启非并置关联参数。
分组和排序优化
带有ORDER BY
子句的SQL查询不需要将整个结果集加载到查询发起节点来进行完整的排序,而是查询映射的每个节点先对各自的结果集进行排序,然后汇总过程会以流式进行合并处理。
对于有序的GROUP BY
查询也实现了部分优化,不需要将整个结果集加载到发起节点来进行分组,在Ignite中,每个节点的部分结果集可以流化、合并、聚合,然后逐步返回给应用。
示例
获取Person
表的所有数据:
SELECT * FROM Person;
获取所有数据,按照字典顺序排序:
SELECT * FROM Person ORDER BY name;
计算某个城市的人口数量:
SELECT city_id, COUNT(*) FROM Person GROUP BY city_id;
关联Person
和City
表的数据:
SELECT p.name, c.name
FROM Person p, City c
WHERE p.city_id = c.id;
# 3.2.INSERT
往表中插入数据。
INSERT INTO tableName
{[( columnName [,...])]
{VALUES {({DEFAULT | expression} [,...])} [,...] | [DIRECT] [SORTED] select}}
| {SET {columnName = {DEFAULT | expression}} [,...]}
参数
tableName
:要更新的表名;columnName
:VALUES
子句中的值对应的列名。
描述
INSERT
命令在表中新增了一个条目。
因为Ignite是以键-值对的形式存储数据的,因此所有的INSERT
语句最后都会被转换成键-值操作的集合。
如果单个键-值对正在加入缓存,那么最后INSERT
语句会被转换成cache.putIfAbsent(...)
操作,还有,如果插入多个键-值对,那么DML引擎会为每组数据创建一个EntryProcessor
,然后使用cache.invokeAll(...)
将数据注入缓存。
关于SQL引擎如何处理并发问题,可以看这里。
示例
在表中插入一个新的Person
:
INSERT INTO Person (id, name, city_id) VALUES (1, 'John Doe', 3);
将Account
表的数据注入Person
表:
INSERT INTO Person(id, name, city_id)
(SELECT a.id + 1000, concat(a.firstName, a.secondName), a.city_id
FROM Account a WHERE a.id > 100 AND a.id < 1000);
# 3.3.UPDATE
修改表中的数据。
UPDATE tableName [[AS] newTableAlias]
SET {{columnName = {DEFAULT | expression}} [,...]} |
{(columnName [,...]) = (select)}
[WHERE expression][LIMIT expression]
参数
table
:表名;columnName
:要修改的列名;
描述
UPDATE
命令会修改表中的已有数据。
因为Ignite以键-值对的形式存储数据,因此所有的UPDATE
语句最后都会被转换成一组键-值操作。
开始时,SQL引擎会根据UPDATE
语句的WHERE
子句生成并且执行一个SELECT
查询,然后只会修改该结果集范围内的数据。
修改是通过cache.invokeAll(...)
执行的,这基本上意味着SELECT
查询完成后,SQL引擎就会准备一定量的EntryProcessors
,然后使用cache.invokeAll(...)
进行执行。EntryProcessors
修改完数据后,会进行额外的检查,确保在查询和执行实际的更新之间没有受到干扰。
主键更新
Ignite不允许更新主键,因为主键和分区的映射关系是静态的。虽然分区及其数据可以修改所有者,但是键会一直属于一个固定的分区,该分区是根据主键使用一个哈希函数计算的。
因此,如果主键需要更新,那么这条数据应该先删除,更新后再插入。
示例
更新数据的name
列:
UPDATE Person SET name = 'John Black' WHERE id = 2;
根据Account
表的数据更新Person
表:
UPDATE Person p SET name = (SELECT a.first_name FROM Account a WHERE a.id = p.id)
# 3.4.WITH
用于命名子查询,然后在SQL语句的其他部分引用。
WITH { name [( columnName [,...] )] AS ( select ) [,...] }
{ select | insert | update | merge | delete | createTable }
参数
query_name
:创建的子查询名,分配给子查询的名字,会被视为内联视图或表。
描述
WITH
会创建一个子查询,一个或多个公共表条目可以通过名字引用。列名声明是可选的,因为从已命名的SELECT查询中推断出列名。在WITH语句中的最后的动作,可以是SELECT、INSERT、UPDATE、MERGE、DELETE或CREATE TABLE。
示例
WITH cte1 AS (
SELECT 1 AS FIRST_COLUMN
), cte2 AS (
SELECT FIRST_COLUMN+1 AS FIRST_COLUMN FROM cte1
)
SELECT sum(FIRST_COLUMN) FROM cte2;
# 3.5.MERGE
将数据合并后入表。
MERGE INTO tableName [(columnName [,...])]
[KEY (columnName [,...])]
{VALUES {({ DEFAULT | expression } [,...])} [,...] | select}
参数
tableName
:要更新的表名;columnName
:VALUES
子句中的值对应的列名。
MERGE
命令会更新已有的条目,如果不存在会插入一个新的条目。
因为Ignite是以键-值对的形式存储数据的,因此所有的MERGE
语句最后都会被转换成键-值操作的集合。
MERGE
是非常明确的操作,它会根据要被插入和更新的数据行数翻译成对应的cache.put(...)
和cache.putAll(...)
操作。
示例
合并一组数据进Person
表:
MERGE INTO Person(id, name, city_id) VALUES
(1, 'John Smith', 5),
(2, 'Mary Jones', 5);
从Account
表获得数据,然后注入Person
表:
MERGE INTO Person(id, name, city_id)
(SELECT a.id + 1000, concat(a.firstName, a.secondName), a.city_id
FROM Account a WHERE a.id > 100 AND a.id < 1000);
# 3.6.DELETE
从表中删除数据。
DELETE
[TOP term] FROM tableName
[WHERE expression]
[LIMIT term]
参数
tableName
:表名;TOP
、LIMIT
:指定要删除的数据的数量(如果为null或者小于0则无限制)。
描述
DETELE
命令会从一个表中删除数据。
因为Ignite以键-值对的形式存储数据,因此所有的DELETE
语句最后都会被转换成一组键-值操作。
DELETE
语句的执行会被拆分为两个阶段,和UPDATE
语句的执行过程类似。
首先,使用一个SQL查询,SQL引擎会收集符合DELTE
语句中WHERE
子句的键,下一步,持有这些键之后,会创建一组EntryProcessors
,调用cache.invokeAll(...)
执行,在数据被删除之后,会进行额外的检查,确保在查询和执行实际的删除之间没有受到干扰。
示例
从Persons
表中删除指定name的数据:
DELETE FROM Person WHERE name = 'John Doe';
# 4.操作型命令
# 4.1.COPY
将CSV文件的数据复制进一个SQL表。
COPY FROM '/path/to/local/file.csv'
INTO tableName (columnName, columnName, ...) FORMAT CSV [CHARSET '<charset-name>']
参数
‘/path/to/local/file.csv’
:CSV文件的实际路径;tableName
:要注入数据的表名;columnName
:与CSV文件中的列对应的列名;charset-name
:字符集名,默认值为UTF-8。
描述
COPY
命令可以将本地文件系统中文件的内容复制到服务端然后将数据注入SQL表。从内部来说,它会将文件内容读取为二进制形式数据包,然后将数据包发送到服务端,最后内容会以流的形式解析和处理。如果要将数据转存为文件,也可以使用这个模式。
只支持JDBC
目前COPY
命令只支持通过JDBC驱动以及CSV格式文件。
示例
COPY
命令可以以如下方式执行:
COPY FROM '/path/to/local/file.csv' INTO city (
ID, Name, CountryCode, District, Population) FORMAT CSV
在上面的命令中,需要将/path/to/local/file.csv
替换为CSV文件的实际路径,比如,可以使用最新的Ignite二进制包中自带的city.csv
,该文件位于[IGNITE_HOME]/examples/src/main/resources/sql/
目录。
# 4.2.SET STREAMING
将文件内容流化,批量地导入SQL表。
SET STREAMING [OFF|ON];
描述
使用SET
命令,如果开启流化处理,可以在集群上将流化数据以批量的形式导入SQL表。JDBC驱动会分批打包命令,然后将其发送给服务端。在服务端,该批量会被转化为缓存更新命令流,这些更新命令在节点间是异步分布的,异步会增加峰值吞吐量,因为在任意时间节点,所有的节点都会忙于数据加载。
使用
要在集群中流化数据,需要准备一个SET STREAMING ON
开头的文件,然后是要进行数据加载的INSERT
命令。
提示
配置STREAMING ON
后就会使用DataStreamer,除非成功结束,否则无法保证默认的数据一致性。
SET STREAMING ON;
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (3,'Herat','AFG','Herat',186800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (5,'Amsterdam','NLD','Noord-Holland',731200);
-- More INSERT commands --
注意,在执行上面的语句之前,需要在集群中先创建表,执行CREATE TABLE
命令,或者将该命令作为插入数据的文件的一部分,放在SET STREAMING ON
命令之前,比如:
CREATE TABLE City (
ID INT(11),
Name CHAR(35),
CountryCode CHAR(3),
District CHAR(20),
Population INT(11),
PRIMARY KEY (ID, CountryCode)
) WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=City, KEY_TYPE=demo.model.CityKey, VALUE_TYPE=demo.model.City";
SET STREAMING ON;
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (3,'Herat','AFG','Herat',186800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (4,'Mazar-e-Sharif','AFG','Balkh',127800);
INSERT INTO City(ID, Name, CountryCode, District, Population) VALUES (5,'Amsterdam','NLD','Noord-Holland',731200);
-- More INSERT commands --
将数据刷入集群
如果数据加载完成,一定要关闭JDBC连接,确保数据刷入集群。
已知的限制
虽然流化数据可以使数据加载快于其它的数据加载方式,但是它有很多限制需要注意:
- 只能使用
INSERT
命令,其它比如SELECT
或者其它任意的DML或者DDL命令的执行都会抛出异常; - 由于流化数据的异步属性,无法知道每个语句执行时的更新总数,因此所有与更新总数有关的JDBC命令都会返回0。
示例
作为示例,可以使用最新的Ignite二进制包自带的world.sql
文件,该文件位于[IGNITE_HOME]/examples/sql/
目录。可以执行SQLLine的run
命令,如下:
!run /apache_ignite_version/examples/sql/world.sql
执行上述命令并且关闭连接后,所有数据会加载到集群并且可以查询到:
# 4.3.KILL QUERY
KILL QUERY
命令用于取消正在运行的查询。当查询使用KILL
命令取消时,所有节点上执行的本地查询都会被终止。
参数
query_id
:可以通过SQL_QUERIES视图获得;ASYNC
:是否立即返回不等待取消完成的可选参数。
# 4.4.KILL TRANSACTION
KILL TRANSACTION
命令用于取消正在运行的事务。
参数
xid
:事务ID,可以通过TRANSACTIONS视图获得。
# 4.5.KILL SCAN
KILL SCAN
命令用于取消正在运行的扫描查询。
参数
origin_node_id
、cache_name
、query_id
:可以通过SCAN_QUERIES视图获得。
示例
KILL SCAN '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' 'cache-name' 1
# 4.6.KILL COMPUTE
KILL COMPUTE
命令用于取消正在运行的计算。
参数
# 4.7.KILL CONTINUOUS
KILL CONTINUOUS
命令用于取消正在运行的持续查询。
参数
origin_node_id
、routine_id
:可以通过CONTINUOUS_QUERIES视图获得。
示例
KILL CONTINUOUS '6fa749ee-7cf8-4635-be10-36a1c75267a7_54321' '6fa749ee-7cf8-4635-be10-36a1c75267a7_12345'
# 4.8.KILL SERVICE
KILL SERVICE
命令用于取消正在运行的服务。
参数
name
:部署时指定的服务名,可以通过SERVICES视图获得。
# 4.9.KILL CONSISTENCY
KILL CONSISTENCY
命令用于取消所有正在运行的一致性修复/检查操作。
# 4.10.KILL CLIENT
KILL CLIENT
命令可以取消本地的客户端(瘦客户端/JDBC/ODBC)连接。
参数
connection_id
:相应的客户端连接ID,指定ALL
可以删除所有的连接。注意,connection_id
指的是本地值,同一个客户端在不同节点该值是不同的,该值可通过CLIENT_CONNECTIONS视图获得;node_id
:要删除的连接对应的节点ID。
# 5.聚合函数
# 5.1.AVG
AVG ([DISTINCT] expression)
参数
DISTINCT
:可选关键字,加上会去除重复数据。
描述
取平均值,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效,返回值类型与参数值类型相同。
示例
计算玩家的平均年龄:
SELECT AVG(age) "AverageAge" FROM Players;
# 5.2.BIT_AND
BIT_AND (expression)
描述
所有非空值的按位与操作,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效。
对长度相等的两个二进制表达式的每一对对应的位执行逻辑与操作。
对于每个对,如果第一个为1且第二个也为1,则返回值为1,否则返回0。
# 5.3.BIT_OR
BIT_OR (expression)
描述
所有非空值的按位或操作,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效。
对长度相等的两个二进制表达式的每一对对应的位执行逻辑或操作。
对于每个对,如果第一个为1或者第二个为1或者两者都为1,则返回值为1,否则返回0。
# 5.4.COUNT
COUNT (* | [DISTINCT] expression)
描述
所有条目或者非空值的数量,该方法返回long型值,如果没有命中记录,返回值为0,该函数只对SELECT语句有效。
示例
计算每个城市玩家的数量:
SELECT city_id, COUNT(*) FROM Players GROUP BY city_id;
# 5.5.GROUP_CONCAT
GROUP_CONCAT([DISTINCT] expression || [expression || [expression ...]]
[ORDER BY expression [ASC|DESC], [[ORDER BY expression [ASC|DESC]]]
[SEPARATOR expression])
参数
DISTINCT
:对结果集进行筛选,去除重复记录;expression
:指定一个表达式,该表达式可以是列名、另一个函数的结果或数学运算;ORDER BY
:通过表达式对数据行进行排序;SEPARATOR
:覆写字符串分隔符。默认的分隔符是逗号“,”。
注意
只有按主键或关联键对结果分组(即使用GROUP BY
)时,才支持GROUP_CONCAT
函数内的DISTINCT
和ORDER BY
表达式。此外,如果使用了Java API,必须在连接串中配置collocated=true
或者调用SqlFieldsQuery.setCollocated(true)
,以通知Ignite数据是并置的。
描述
用分隔符连接字符串。默认分隔符是','(没有空格)。此方法返回一个字符串。如果未命中数据则结果为空。只能在SELECT语句中使用聚合。
这里的expression
可以是列和字符串的串联(使用||
操作符),比如,column1 || "=" || column2
。
示例
将所有玩家的名字聚合成一行:
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ', ') FROM Players;
# 5.6.MAX
MAX (expression)
参数
expression
:可以是一个列名,也可以是另一个函数或者数学操作的结果集。
描述
返回最大值,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效,返回值类型与参数值类型相同。
示例
获得最高的玩家:
SELECT MAX(height) FROM Players;
# 5.7.MIN
MIN (expression)
参数
expression
:可以是一个列名,也可以是另一个函数或者数学操作的结果集。
描述
返回最小值,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效,返回值类型与参数值类型相同。
示例
获得最年轻的玩家:
SELECT MIN(age) FROM Players;
# 5.8.SUM
SUM ([DISTINCT] expression)
参数
DISTINCT
:去除重复的数据;expression
:可以是一个列名,也可以是另一个函数或者数学操作的结果集。
描述
返回所有值的总和,如果没有命中记录,返回值为NULL,该函数只对SELECT语句有效,返回值类型与参数值类型相同。
示例
获得所有玩家的总得分:
SELECT SUM(goal) FROM Players;
# 6.数值函数
# 6.1.ABS
ABS (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
返回表达式的绝对值。
示例
计算绝对值:
SELECT transfer_id, ABS (price) from Transfers;
# 6.2.ACOS
ACOS (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算反余弦函数值,该函数返回double型值。
示例
获取反余弦值:
SELECT acos(angle) FROM Triangles;
# 6.3.ASIN
ASIN (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算反正弦函数值,该函数返回double型值。
示例
获取反余弦值:
SELECT asin(angle) FROM Triangles;
# 6.4.ATAN
ATAN (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算反正切函数值,该函数返回double型值。
示例
获取反余切值:
SELECT atan(angle) FROM Triangles;
# 6.5.COS
COS (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算余弦函数值,该函数返回double型值。
示例
获取余弦值:
SELECT COS(angle) FROM Triangles;
# 6.6.COSH
COSH (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算双曲余弦函数值,该函数返回double型值。
示例
获取双曲余弦值:
SELECT COSH(angle) FROM Triangles;
# 6.7.COT
COT (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算余切函数值,该函数返回double型值。
示例
获取余切值:
SELECT COT(angle) FROM Triangles;
# 6.8.SIN
SIN (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算正弦函数值,该函数返回double型值。
示例
获取正弦值:
SELECT SIN(angle) FROM Triangles;
# 6.9.SINH
SINH (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算双曲正弦函数值,该函数返回double型值。
示例
获取双曲正弦值:
SELECT SINH(angle) FROM Triangles;
# 6.10.TAN
TAN (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算正切函数值,该函数返回double型值。
示例
获取正切值:
SELECT TAN(angle) FROM Triangles;
# 6.11.TANH
TANH (expression)
参数
expression
:可以是列名,也可以是另一个函数或者数学操作的结果集。
描述
计算双曲正切函数值,该函数返回double型值。
示例
获取双曲正切值:
SELECT TANH(angle) FROM Triangles;
# 6.12.ATAN2
ATAN2 (y, x)
参数
x
和y
:参数。
描述
将直角坐标转换成极坐标时的角度计算,该函数返回double型值。
示例
获取方位角:
SELECT ATAN2(X, Y) FROM Triangles;
# 6.13.BITAND
BITAND (y, x)
参数
x
和y
:参数。
描述
按位与操作,该函数返回double型值。
示例
SELECT BITAND(X, Y) FROM Triangles;
# 6.14.BITGET
BITGET (y, x)
参数
x
和y
:参数。
描述
当且仅当第一个参数在第二个参数指定的位置上存在二进制位组时返回true,该方法返回布尔值,第二个参数从0开始,最小有效位位置为0。
示例
检查第3位是否为1:
SELECT BITGET(X, 3) from Triangles;
# 6.15.BITOR
BITOR (y, x)
参数
x
和y
:参数。
描述
按位或操作,该方法返回long型值。
示例
计算两个字段的或操作:
SELECT BITOR(X, Y) from Triangles;
# 6.16.BITXOR
BITXOR (y, x)
参数
x
和y
:参数。
描述
按位异或操作,该方法返回long型值。
示例
计算两个字段的异或操作:
SELECT BITXOR(X, Y) from Triangles;
# 6.17.MOD
MOD (y, x)
参数
x
和y
:参数。
描述
取模操作,该方法返回long型值。
示例
计算两个字段的模:
SELECT MOD(X, Y) from Triangles;
# 6.18.CEILING
CEIL (expression)
CEILING (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.ceil,该方法返回double型值。
示例
计算品类的向上取整价格:
SELECT item_id, CEILING(price) FROM Items;
# 6.19.DEGREES
DEGREES (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.toDegrees,该方法返回double型值。
示例
计算参数的角度值:
SELECT DEGREES(X) FROM Triangles;
# 6.20.EXP
EXP (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.exp,该方法返回double型值。
示例
计算exp:
SELECT EXP(X) FROM Triangles;
# 6.21.FLOOR
FLOOR (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.floor,该方法返回double型值。
示例
计算向下取整价格:
SELECT FLOOR(X) FROM Items;
# 6.22.LOG
LOG (expression)
LN (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.log,该方法返回double型值。
示例
计算自然对数:
SELECT LOG(X) from Items;
# 6.23.LOG10
LOG10 (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.log10(Java5),该方法返回double型值。
示例
计算底数为10的对数:
SELECT LOG10(X) from Items;
# 6.24.RADIANS
RADIANS (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.toRadians,该方法返回double型值。
示例
计算参数的弧度值:
SELECT RADIANS(X) FROM Triangles;
# 6.25.SQRT
SQRT (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.sqrt,该方法返回double型值。
示例
计算参数的平方根:
SELECT SQRT(X) FROM Items;
# 6.26.PI
PI (expression)
参数
expression
:任意有效的数值表达式。
描述
可以参照Java的Math.PI,该方法返回double型值。
示例
计算参数的圆周率:
SELECT PI(X) FROM Items;
# 6.27.POWER
POWER (X, Y)
参数
x
和y
:参数。
描述
可以参照Java的Math.pow,该方法返回double型值。
示例
计算2的乘方:
SELECT pow(2, n) FROM Rows;
# 6.28.RAND
{RAND | RANDOM} ([expression])
参数
expression
:任意有效的数值表达式。
描述
调用函数时没有参数会生成下一个伪随机数,带参数时会使用会话的随机数生成器,,该方法返回从0(包含)到1(不包含)的一个double型值。
示例
为每个玩家生成一个随机数:
SELECT random() FROM Play;
# 6.29.RANDOM_UUID
{RANDOM_UUID | UUID} ()
描述
使用122伪随机位,返回一个新的UUID。
示例
为每个玩家生成一个随机数:
SELECT UUID(),name FROM Player;
# 6.30.ROUND
ROUND ( expression [, precision] )
参数
expression
:任意有效的数值表达式;precision
:小数点之后的位数,如果位数未指定,会四舍五入到最近的long型值。
描述
四舍五入到指定的位数,如果未指定位数,则四舍五入到最近的long型值,该方法会返回一个数值(类型与输入相同)。
示例
将每个玩家的年龄转成整型值:
SELECT name, ROUND(age) FROM Player;
# 6.31.ROUNDMAGIC
ROUNDMAGIC (expression)
参数
expression
:任意有效的数值表达式;
描述
该方法会很好地对数值进行四舍五入,但是速度较慢。它对0附近的数值有特殊的处理,它只支持小于等于+/-1000000000000的数值。
在内部,首先将值转成字符串,然后检查最后的4位,000x
会变成0000
,999x
会变成999999
,这些都是自动的,该方法返回double型值。
示例
对每个玩家的年龄进行四舍五入:
SELECT name, ROUNDMAGIC(AGE/3*3) FROM Player;
# 6.32.SECURE_RAND
SECURE_RAND (int)
参数
int
:指定位数。
描述
生成安全加密的随机数,该方法返回字节。
示例
返回真正的随机数:
SELECT name, SECURE_RAND(10) FROM Player;
# 6.33.SIGN
SIGN (expression)
参数
expression
:任意有效的数值表达式。
描述
如果表达式值小于0,则返回-1,等于0,则返回0,否则返回1。
示例
返回每个值的符号:
SELECT name, SIGN(VALUE) FROM Player;
# 6.34.ENCRYPT
ENCRYPT (algorithmString , keyBytes , dataBytes)
参数
algorithmString
:指定支持的AES算法;keyBytes
:指定密钥;dataBytes
:数据。
描述
使用密钥对数据进行加密,支持的算法为AES,块大小为16个字节,该方法返回字节。
示例
对玩家的名字进行加密:
SELECT ENCRYPT('AES', '00', STRINGTOUTF8(Name)) FROM Player;
# 6.35.DECRYPT
DECRYPT (algorithmString , keyBytes , dataBytes)
参数
algorithmString
:指定支持的AES算法;keyBytes
:指定密钥;dataBytes
:数据。
描述
使用密钥对数据进行解密,支持的算法为AES,块大小为16个字节,该方法返回字节。
示例
对玩家的名字进行解密:
SELECT DECRYPT('AES', '00', '3fabb4de8f1ee2e97d7793bab2db1116'))) FROM Player;
# 6.36.TRUNCATE
{TRUNC | TRUNCATE} ({{numeric, digitsInt} | timestamp | date | timestampString})
描述
截断到一定的位数,该方法返回double型值。如果处理一个时间戳,会将截断到日期类型,如果处理一个日期,会将其截断到时间较少的一个日期,如果处理时间戳字符串,会将时间戳截断到一个日期类型。
示例
TRUNCATE(VALUE, 2);
# 6.37.COMPRESS
COMPRESS(dataBytes [, algorithmString])
参数
dataBytes
:要压缩的数据;algorithmString
:压缩的算法。
描述
使用指定的压缩算法压缩数据,支持的算法包括:LZF(快,但是压缩率较低,默认),DEFLATE(高压缩率)。压缩并不一定会减少大小,很小的对象以及冗余较少的对象会变得更大,该方法返回字节。
示例
COMPRESS(STRINGTOUTF8('Test'))
# 6.38.EXPAND
EXPAND(dataBytes)
参数
dataBytes
:要解开的数据。
描述
解压缩通过COMPRESS函数压缩的数据,该方法返回字节。
示例
UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Test'))))
# 6.39.ZERO
ZERO()
描述
返回0,该函数用于无法使用数值字面量的场景。
示例
ZERO()
# 7.字符串函数
# 7.1.ASCII
ASCII(string)
参数
string
:参数。
描述
返回字符串中第一个字符的ASCII码值,该方法返回int型值。
示例
select ASCII(name) FROM Players;
# 7.2.BIT_LENGTH
BIT_LENGTH(string)
参数
string
:参数。
描述
返回字符串的位数,该方法返回long型值,对于BLOB、CLOB、BYTES以及JAVA_OBJECT,需要使用精度,每个字符需要16位。
示例
select BIT_LENGTH(name) FROM Players;
# 7.3.LENGTH
{LENGTH | CHAR_LENGTH | CHARACTER_LENGTH} (string)
参数
string
:参数。
描述
返回字符串的字符数,该方法返回long型值,对于BLOB、CLOB、BYTES以及JAVA_OBJECT,需要使用精度,每个字符需要16位。
示例
SELECT LENGTH(name) FROM Players;
# 7.4.OCTET_LENGTH
OCTET_LENGTH(string)
参数
string
:参数。
描述
返回字符串的字节数,该方法返回long型值,对于BLOB、CLOB、BYTES以及JAVA_OBJECT,需要使用精度,每个字符需要2个字节。
示例
SELECT OCTET_LENGTH(name) FROM Players;
# 7.5.CHAR
{CHAR | CHR} (int)
参数
int
:参数。
描述
返回ASCII码值对应的字符,该方法返回字符串。
示例
SELECT CHAR(65)||name FROM Players;
# 7.6.CONCAT
CONCAT(string, string [,...])
参数
string
:参数。
描述
字符串连接,与操作符||
不同,NULL参数会被忽略,不会导致结果变为NULL,该方法返回字符串。
示例
SELECT CONCAT(NAME, '!') FROM Players;
# 7.7.CONCAT_WS
CONCAT_WS(separatorString, string, string [,...])
参数
separatorString
:分隔符;string
:参数。
描述
通过一个分隔符对字符串连接,与操作符||
不同,NULL参数会被忽略,不会导致结果变为NULL,该方法返回字符串。
示例
SELECT CONCAT_WS(',', NAME, '!') FROM Players;
# 7.8.DIFFERENCE
DIFFERENCE(X, Y)
参数
X
,Y
:要比较的字符串。
描述
返回两个字符串之间的差异,该方法返回整型值。
示例
计算玩家姓名的一致程度:
select DIFFERENCE(T1.NAME, T2.NAME) FROM players T1, players T2
WHERE T1.ID = 10 AND T2.ID = 11;
# 7.9.HEXTORAW
HEXTORAW(string)
参数
string
:要转换的十六进制字符串。
描述
将十六进制字符串转换为普通字符串,4个十六进制字符转成一个普通字符。
示例
SELECT HEXTORAW(DATA) FROM Players;
# 7.10.RAWTOHEX
RAWTOHEX(string)
参数
string
:要转换成十六进制的字符串。
描述
将字符串转换为十六进制形式,4个十六进制字符对应一个普通字符,该方法返回字符串类型。
示例
SELECT RAWTOHEX(DATA) FROM Players;
# 7.11.INSTR
INSTR(string, searchString, [, startInt])
参数
string
:任意字符串searchString
:要搜索的字符串startInt
:搜索的起始位置
描述
返回要搜索的字符串在源字符串中的位置,如果指定了起始位置,之前的字符会被忽略,如果是负数,会返回最右侧的位置,如果未找到则返回0,注意该函数是区分大小写的。
示例
检查一个字符串是否包含@
字符:
SELECT INSTR(EMAIL,'@') FROM Players;
# 7.12.INSERT
INSERT(originalString, startInt, lengthInt, addString)
参数
originalString
:原来的字符串;startInt
:起始位置;lengthInt
:长度;addString
:要添加的字符串。
描述
在原字符串的指定位置插入额外的字符串,长度指的是在源字符串从指定开始位置开始删除的字符的长度,该方法返回字符串。
示例
SELECT INSERT(NAME, 1, 1, ' ') FROM Players;
# 7.13.LOWER
{LOWER | LCASE} (string)
参数
string
:参数
描述
将字符串转为小写。
示例
SELECT LOWER(NAME) FROM Players;
# 7.14.UPPER
{UPPER | UCASE} (string)
参数
string
:参数
描述
将字符串转为大写。
示例 下面的示例会将所有玩家的名字转为大写形式:
SELECT UPPER(last_name) "LastNameUpperCase" FROM Players;
# 7.15.LEFT
LEFT(string, int)
参数
string
:参数;int
:字符数。
描述
返回最左边的若干个字符。
示例
获取玩家名字的前三个字符:
SELECT LEFT(NAME, 3) FROM Players;
# 7.16.RIGHT
RIGHT(string, int)
参数
string
:参数;int
:字符数。
描述
返回最右边的若干个字符。
示例
获取玩家名字的后三个字符:
SELECT RIGHT(NAME, 3) FROM Players;
# 7.17.LOCATE
LOCATE(searchString, string [, startInt])
参数
string
:任意字符串searchString
:要搜索的字符串startInt
:搜索的起始位置
描述
返回要搜索的字符串在源字符串中的位置,如果指定了起始位置,之前的字符会被忽略,如果是负数,会返回最右侧的位置,如果未找到则返回0。
示例
检查一个字符串是否包含@
字符:
SELECT LOCATE('.', NAME) FROM Players;
# 7.18.POSITION
POSITION(searchString, string)
描述
返回要搜索的字符串在源字符串中的位置,可以参照LOCATE
。
示例
SELECT POSITION('.', NAME) FROM Players;
# 7.19.LPAD
LPAD(string, int[, paddingString])
描述
将字符串左补到指定的长度,如果长度比字符串长度小,后面会被截断,如果未指定补齐字符串,默认使用空格。
示例
SELECT LPAD(AMOUNT, 10, '*') FROM Players;
# 7.20.RPAD
RPAD(string, int[, paddingString])
描述
将字符串右补到指定的长度,如果长度比字符串长度小,后面会被截断,如果未指定补齐字符串,默认使用空格。
示例
SELECT RPAD(TEXT, 10, '-') FROM Players;
# 7.21.LTRIM
LTRIM(string)
描述
删除字符串开头的空格。
示例
SELECT LTRIM(NAME) FROM Players;
# 7.22.RTRIM
RTRIM(string)
描述
删除字符串末尾的空格。
示例
SELECT RTRIM(NAME) FROM Players;
# 7.23.TRIM
TRIM ([{LEADING | TRAILING | BOTH} [string] FROM] string)
描述
删除首尾的所有空格,通过一个字符串,也可以删除其它的字符。
示例
SELECT TRIM(BOTH '_' FROM NAME) FROM Players;
# 7.24.REGEXP_REPLACE
REGEXP_REPLACE(inputString, regexString, replacementString [, flagsString])
描述
替换每个匹配正则表达式的子串,具体可以参照Java的String.replaceAll()
方法,如果参数都为空(除了可选的flagsString参数),返回结果也为空。
flagsString参数可以为i
、c
、n
、m
,其它的字符会出现异常,该参数可以同时使用多个标志位(比如im
),后面的标志会覆盖前面的,比如ic
等价于c
。
i
:启用区分大小写匹配(Pattern.CASE_INSENSITIVE);c
:禁用区分大小写匹配(Pattern.CASE_INSENSITIVE);n
:允许句号.
匹配换行符(Pattern.DOTALL);m
:启用多行模式(Pattern.MULTILINE)。
示例
SELECT REGEXP_REPLACE(name, 'w+', 'W', 'i') FROM Players;
# 7.25.REGEXP_LIKE
REGEXP_LIKE(inputString, regexString [, flagsString])
描述
用正则表达式进行字符串匹配,具体可以参照Java的Matcher.find()
方法,如果参数都为空(除了可选的flagsString参数),返回结果也为空。
flagsString参数可以为i
、c
、n
、m
,其它的字符会出现异常,该参数可以同时使用多个标志位(比如im
),后面的标志会覆盖前面的,比如ic
等价于c
。
i
:启用区分大小写匹配(Pattern.CASE_INSENSITIVE);c
:禁用区分大小写匹配(Pattern.CASE_INSENSITIVE);n
:允许句号.
匹配换行符(Pattern.DOTALL);m
:启用多行模式(Pattern.MULTILINE)。
示例
SELECT REGEXP_LIKE(name, '[A-Z ]*', 'i') FROM Players;
# 7.26.REPEAT
REPEAT(string, int)
描述
返回重复若干次数的字符串。
示例
SELECT REPEAT(NAME || ' ', 10) FROM Players;
# 7.27.REPLACE
REPLACE(string, searchString [, replacementString])
描述
将文本中出现的所有搜索字符串替换为另一个字符串,如果未指定替换字符串,索索字符串会从原字符串中删除,如果参数都为空,则返回值为空。
示例
SELECT REPLACE(NAME, ' ') FROM Players;
# 7.28.SOUNDEX
SOUNDEX(string)
描述
返回表示字符串声音的四个字符码,具体可以看这里,该方法返回一个字符串。
示例
SELECT SOUNDEX(NAME) FROM Players;
# 7.29.SPACE
SPACE(int)
描述
返回由若干个空格组成的字符串。
示例
SELECT name, SPACE(80) FROM Players;
# 7.30.STRINGDECODE
STRINGDECODE(string)
描述
使用Java的字符串编码格式对已编码字符串进行转换,特殊字符为\b
、\t
、\n
、\f
、\r
、\"
、\
、\<octal>
, \u<unicode>
,该方法返回一个字符串。
示例
STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'));
# 7.31.STRINGENCODE
STRINGENCODE(string)
描述
使用Java的字符串编码格式对字符串进行编码,特殊字符为\b
、\t
、\n
、\f
、\r
、\"
、\
、\<octal>
, \u<unicode>
,该方法返回一个字符串。
示例
STRINGENCODE(STRINGDECODE('Lines 1\nLine 2'))
# 7.32.STRINGTOUTF8
STRINGTOUTF8(string)
描述
将字符串转换成UTF-8格式的字符数组,该方法返回字节数组。
示例
SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;
# 7.33.SUBSTRING
{SUBSTRING | SUBSTR} (string, startInt [, lengthInt])
描述
返回一个字符串从指定位置开始的一个子串,如果开始位置为负数,那么开始位置会相对于字符串的末尾。长度是可选的。SUBSTRING(string [FROM start] [FOR length])
也是支持的。
示例
SELECT SUBSTR(name, 2, 5) FROM Players;
# 7.34.UTF8TOSTRING
UTF8TOSTRING(bytes)
描述
将UTF-8编码的字符串解码成对应的字符串。
示例
SELECT UTF8TOSTRING(STRINGTOUTF8(name)) FROM Players;
# 7.35.XMLATTR
XMLATTR(nameString, valueString)
描述
以name=value的形式创建一个XML属性元素,value是以XML文本的形式进行编码,该方法返回一个字符串。
示例
XMLNODE('a', XMLATTR('href', 'http://h2database.com'))
# 7.36.XMLNODE
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
描述
创建一个XML节点元素,如果属性字符串为空或者null,那么意味着这个节点没有属性,如果内容字符串为空或者null,那么这个节点是没有内容的,如果内容中包含换行,那么默认会被缩进,该方法返回一个字符串。
示例
XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2')
# 7.37.XMLCOMMENT
XMLCOMMENT(commentString)
描述
创建一个XML注释,两个减号(--
)会被转换为- -
。该方法返回一个字符串。
示例
XMLCOMMENT('Test')
# 7.38.XMLCDATA
XMLCDATA(valueString)
描述
创建一个XML CDATA元素,如果值中包含]]>
,会创建一个XML文本元素作为替代,该方法返回一个字符串。
示例
XMLCDATA('data')
# 7.39.XMLSTARTDOC
XMLSTARTDOC()
描述
返回一个XML声明,结果为<?xml version=1.0?>
。
示例
XMLSTARTDOC()
# 7.40.XMLTEXT
XMLTEXT(valueString [, escapeNewlineBoolean])
描述
创建一个XML文本元素,如果启用,换行符会被转换为一个XML实体(&#
),该方法返回一个字符串。
示例
XMLSTARTDOC()
# 7.41.TO_CHAR
TO_CHAR(value [, formatString[, nlsParamString]])
描述
该函数会格式化一个时间戳、数值或者文本。
示例
TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY')
# 7.42.TRANSLATE
TRANSLATE(value , searchString, replacementString]])
描述
该函数会用另一组字符替换字符串中一组字符。
示例
TRANSLATE('Hello world', 'eo', 'EO')
# 8.日期和时间函数
# 8.1.CURRENT_DATE
{CURRENT_DATE [()] | CURDATE() | SYSDATE | TODAY}
描述
返回当前日期,该方法在一个事务中会返回同一个值。
示例
CURRENT_DATE()
# 8.2.CURRENT_TIME
{CURRENT_TIME [ () ] | CURTIME()}
描述
返回当前时间,该方法在一个事务中会返回同一个值。
示例
CURRENT_TIME()
# 8.3.CURRENT_TIMESTAMP
{CURRENT_TIMESTAMP [([int])] | NOW([int])}
描述
返回当前时间戳,注意纳秒的精度参数是可选的,该方法在一个事务中会返回同一个值。
示例
CURRENT_TIMESTAMP()
# 8.4.DATEADD
{DATEADD| TIMESTAMPADD} (unitString, addIntLong, timestamp)
描述
为时间戳增加若干个单位。第一个字符串表示单位,使用负数可以做减法,如果操作毫秒addIntLong可以是一个long型值,否则限定为整型值。支持的单位与EXTRACT
函数一致。DATEADD
方法返回一个时间戳,TIMESTAMPADD
方法返回一个long型值。
示例
DATEADD('MONTH', 1, DATE '2001-01-31')
# 8.5.DATEDIFF
{DATEDIFF | TIMESTAMPDIFF} (unitString, aTimestamp, bTimestamp)
描述
返回两个时间戳根据特定单位计算的差值,该方法返回long型值,第一个字符串表示单位,支持的单位与EXTRACT
函数一致。
示例
DATEDIFF('YEAR', T1.CREATED, T2.CREATED)
# 8.6.DAYNAME
DAYNAME(date)
描述
返回日期的名字(英语)。
示例
DAYNAME(CREATED)
# 8.7.DAY_OF_MONTH
DAY_OF_MONTH(date)
描述
返回月中的日期数(1-31)。
示例
DAY_OF_MONTH(CREATED)
# 8.8.DAY_OF_WEEK
DAY_OF_WEEK(date)
描述
返回周中的天数(1表示周日)。
示例
DAY_OF_WEEK(CREATED)
# 8.9.DAY_OF_YEAR
DAY_OF_YEAR(date)
描述
返回年中的天数(1-366)。
示例
DAY_OF_YEAR(CREATED)
# 8.10.EXTRACT
EXTRACT ({EPOCH | YEAR | YY | QUARTER | MONTH | MM | WEEK | ISO_WEEK
| DAY | DD | DAY_OF_YEAR | DOY | DAY_OF_WEEK | DOW | ISO_DAY_OF_WEEK
| HOUR | HH | MINUTE | MI | SECOND | SS | MILLISECOND | MS
| MICROSECOND | MCS | NANOSECOND | NS}
FROM timestamp)
描述
返回时间戳中的特定值,该方法返回整型值。
示例
EXTRACT(SECOND FROM CURRENT_TIMESTAMP)
# 8.11.FORMATDATETIME
FORMATDATETIME (timestamp, formatString [,localeString [,timeZoneString]])
描述
将日期、时间或者时间戳格式化成字符串,最常用的格式符为:y:年,M:月,d:日期,H:小时,m:分钟,s:秒,要了解详细信息,可以看Java的java.text.SimpleDateFormat,该方法返回字符串。
示例
FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
# 8.12.HOUR
HOUR(timestamp)
描述
返回时间戳中的小时数(0-23)。
示例
HOUR(CREATED)
# 8.13.MINUTE
MINUTE(timestamp)
描述
返回时间戳中的分钟数(0-59)。
示例
MINUTE(CREATED)
# 8.14.MONTH
MONTH(timestamp)
描述
返回时间戳中的月数(1-12)。
示例
MONTH(CREATED)
# 8.15.MONTHNAME
MONTHNAME(date)
描述
返回月的名字(英语)。
示例
MONTHNAME(CREATED)
# 8.16.PARSEDATETIME
PARSEDATETIME(string, formatString [, localeString [, timeZoneString]])
描述
将字符串解析成时间戳类型,最常用的格式符为:y:年,M:月,d:日期,H:小时,m:分钟,s:秒,要了解详细信息,可以看Java的java.text.SimpleDateFormat
。
示例
PARSEDATETIME('Sat, 3 Feb 2001 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
# 8.17.QUARTER
QUARTER(timestamp)
描述
返回时间戳中的季度数(1-4)。
示例
QUARTER(CREATED)
# 8.18.SECOND
SECOND(timestamp)
描述
返回时间戳中的秒数(0-59)。
示例
SECOND(CREATED)
# 8.19.WEEK
WEEK(timestamp)
描述
返回时间戳中的周数(1-53),该方法使用系统当前的区域设置。
示例
WEEK(CREATED)
# 8.20.YEAR
YEAR(timestamp)
描述
返回时间戳中的年数。
示例
YEAR(CREATED)
# 9.系统函数
# 9.1.COALESCE
{COALESCE | NVL } (aValue, bValue [,...])
描述
返回第一个非空值。
示例
COALESCE(A, B, C)
# 9.2.DECODE
DECODE(value, whenValue, thenValue [,...])
描述
返回第一个匹配的值,NULL会匹配NULL,如果没有匹配的,那么会返回NULL或者最后一个参数(参数个数为偶数)。
示例
DECODE(RAND()>0.5, 0, 'Red', 1, 'Black')
# 9.3.GREATEST
GREATEST(aValue, bValue [,...])
描述
返回非空的最大值,如果所有值都为空则返回空。
示例
GREATEST(1, 2, 3)
# 9.4.IFNULL
IFNULL(aValue, bValue)
描述
如果aValue
非空则返回aValue
,否则返回bValue
。
示例
IFNULL(NULL, '')
# 9.5.LEAST
LEAST(aValue, bValue [,...])
描述
返回非空的最小值,如果所有值都为空则返回空。
示例
LEAST(1, 2, 3)
# 9.6.NULLIF
NULLIF(aValue, bValue)
描述
如果aValue
等于bValue
,则返回NULL,否则返回aValue
。
示例
NULLIF(A, B)
# 9.7.NVL2
NVL2(testValue, aValue, bValue)
描述
如果testValue
为空,则返回bValue
,否则返回aValue
。
示例
NVL2(X, 'not null', 'null')
# 9.8.CASEWHEN
CASEWHEN (boolean , aValue , bValue)
描述
如果布尔表达式为true,则返回aValue
,否则返回bValue
。
示例
CASEWHEN(ID=1, 'A', 'B')
# 9.9.CAST
CAST (value AS dataType)
描述
将值变更为另一个类型,规则如下:
- 如果将数值转为布尔值,0被认为false,其它值为true;
- 如果将布尔值转为数值,false为0,true为1;
- 当将数值转为另一个类型的数值时,会检查是否溢出;
- 如果将数值转为二进制,字节数会与精度匹配;
- 如果将字符串转为二进制,会用十六进制编码;
- 一个十六进制字符串可以转为二进制形式,然后转成数值。如果无法直接转换,则首先将其转为字符串。
示例
CAST(NAME AS INT);
CAST(65535 AS BINARY);
CAST(CAST('FFFF' AS BINARY) AS INT);
# 9.10.CONVERT
CONVERT (value , dataType)
描述
将值转为另一个类型。
示例
CONVERT(NAME, INT)
# 9.11.TABLE
TABLE | TABLE_DISTINCT (name dataType = expression)
描述
返回一个结果集,TABLE_DISTINCT会删除重复行。
示例
SELECT * FROM TABLE(ID INT=(1, 2), NAME VARCHAR=('Hello', 'World'))
# 10.数据类型
本章节中列出了Ignite中支持的SQL数据类型列表,比如string、numeric以及date/time类型。
对于每个SQL类型来说,都会被映射到Ignite原生支持的编程语言或者驱动指定的类型上。
# 10.1.BOOLEAN
可选值:TRUE
和FALSE
。
映射:
- Java/JDBC:
java.lang.Boolean
- .NET/C#:
bool
- C/C++:
bool
- ODBC:
SQL_BIT
# 10.2.INT
可选值:[-2147483648
, 2147483647
]。
映射:
- Java/JDBC:
java.lang.Integer
- .NET/C#:
int
- C/C++:
int32_t
- ODBC:
SQL_INTEGER
# 10.3.TINYINT
可选值:[-128
, 127
]。
映射:
- Java/JDBC:
java.lang.Byte
- .NET/C#:
sbyte
- C/C++:
int8_t
- ODBC:
SQL_TINYINT
# 10.4.SMALLINT
可选值:[-32768
, 32767
]。
映射:
- Java/JDBC:
java.lang.Short
- .NET/C#:
short
- C/C++:
int16_t
- ODBC:
SQL_SMALLINT
# 10.5.BIGINT
可选值:[-9223372036854775808
, 9223372036854775807
]。
映射:
- Java/JDBC:
java.lang.Long
- .NET/C#:
long
- C/C++:
int64_t
- ODBC:
SQL_BIGINT
# 10.6.DECIMAL
可选值:带有固定精度的数值类型。
映射:
- Java/JDBC:
java.math.BigDecimal
- .NET/C#:
decimal
- C/C++:
ignite::Decimal
- ODBC:
SQL_DECIMAL
# 10.7.DOUBLE
可选值:浮点数。
映射:
- Java/JDBC:
java.lang.Double
- .NET/C#:
double
- C/C++:
double
- ODBC:
SQL_DOUBLE
# 10.8.REAL
可选值:单精度浮点数。
映射:
- Java/JDBC:
java.lang.Float
- .NET/C#:
float
- C/C++:
float
- ODBC:
SQL_FLOAT
# 10.9.TIME
可选值:时间数据类型,格式为hh:mm:ss
。
映射:
- Java/JDBC:
java.sql.Time
- .NET/C#:
N/A
- C/C++:
ignite::Time
- ODBC:
SQL_TYPE_TIME
# 10.10.DATE
可选值:日期数据类型,格式为yyyy-MM-dd
。
映射:
- Java/JDBC:
java.sql.Date
- .NET/C#:
N/A
- C/C++:
ignite::Date
- ODBC:
SQL_TYPE_DATE
注意
尽可能地使用TIMESTAMP
而不是DATE
,因为DATE
类型的序列化/反序列化效率非常低,导致性能下降。
# 10.11.TIMESTAMP
可选值:时间戳数据类型,格式为yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]
。
映射:
- Java/JDBC:
java.sql.Timestamp
- .NET/C#:
System.DateTime
- C/C++:
ignite::Timestamp
- ODBC:
SQL_TYPE_TIMESTAMP
# 10.12.VARCHAR
可选值:Unicode字符串。
映射:
- Java/JDBC:
java.lang.String
- .NET/C#:
string
- C/C++:
std::string
- ODBC:
SQL_VARCHAR
# 10.13.CHAR
可选值:Unicode字符串。支持这个类型是为了与旧的应用或者其它数据库进行兼容。
映射:
- Java/JDBC:
java.lang.String
- .NET/C#:
string
- C/C++:
std::string
- ODBC:
SQL_CHAR
# 10.14.UUID
可选值:通用唯一标识符,长度128位。
映射:
- Java/JDBC:
java.util.UUID
- .NET/C#:
System.Guid
- C/C++:
ignite::Guid
- ODBC:
SQL_GUID
# 10.15.BINARY
可选值:表示一个字节数组。
映射:
- Java/JDBC:
byte[]
- .NET/C#:
byte[]
- C/C++:
int8_t[]
- ODBC:
SQL_BINARY
# 10.16.GEOMETRY
可选值:空间几何类型,基于com.vividsolutions.jts
库,通常以文本格式表示。
映射:
- Java/JDBC: 来自
com.vividsolutions.jts
包的类型 - .NET/C#:
N/A
- C/C++:
N/A
- ODBC:
N/A
# 11.优化器提示
当前,优化器提示只支持基于Calcite的SQL引擎。
18624049226