TDEngine的SQL语法
阅读数:224 评论数:0
跳转到新版页面分类
数据库
正文
TDEngine终端可以通过source命令来运行SQL命令脚本:
taos>source <filename>
一、支持的数据类型
1、时间戳
(1)时间格式为YYYY-MM-DD HH:mm:ss.MS,默认时间分辨率为毫秒,比如:2017-08-12 18:25:58.128
(2)内部函数now是客户端的当前时间
(3)插入记录时,如果时间戳为now,插入数据时使用的提交这条记录的客户端的当前时间。
(4)Epoch Time:时间戳也可以是一个长整数,表示从1970-01-01 08:00:00.00开始的毫秒数。
(5)时间可以加减,比如now-2h,表示查询时刻向前推2个小时。数字后面的时间单位可以是u(微秒)、a(毫秒)、s(秒)、m(分)、h(小时)、d(天)、w(周)、n(自然月)、y(年)。
TDEngine缺省的时间戳是毫秒精度,但通过在create database时传递precision参数可以支持微秒。
2、支持的10种数据类型
# | 类型 | Bytes | 说明 |
---|---|---|---|
1 | TIMESTAMP | 8 | 时间戳。缺省精度毫秒,可支持微秒。从格林威治时间 1970-01-01 00:00:00.000 (UTC/GMT) 开始,计时不能早于该时间。(从 2.0.18.0 版本开始,已经去除了这一时间范围限制) |
2 | INT | 4 | 整型,范围 [-2^31+1, 2^31-1], -2^31 用作 NULL |
3 | BIGINT | 8 | 长整型,范围 [-2^63+1, 2^63-1], -2^63 用于 NULL |
4 | FLOAT | 4 | 浮点型,有效位数 6-7,范围 [-3.4E38, 3.4E38] |
5 | DOUBLE | 8 | 双精度浮点型,有效位数 15-16,范围 [-1.7E308, 1.7E308] |
6 | BINARY | 自定义 | 记录单字节字符串,建议只用于处理 ASCII 可见字符,中文等多字节字符需使用 nchar。理论上,最长可以有 16374 字节,但由于每行数据最多 16K 字节,实际上限一般小于理论值。binary 仅支持字符串输入,字符串两端需使用单引号引用。使用时须指定大小,如 binary(20) 定义了最长为 20 个单字节字符的字符串,每个字符占 1 byte 的存储空间,此时如果用户字符串超出 20 字节将会报错。对于字符串内的单引号,可以用转义字符反斜线加单引号来表示,即 \’ 。 |
7 | SMALLINT | 2 | 短整型, 范围 [-32767, 32767], -32768 用于 NULL |
8 | TINYINT | 1 | 单字节整型,范围 [-127, 127], -128 用于 NULL |
9 | BOOL | 1 | 布尔型,{true, false} |
10 | NCHAR | 自定义 | 记录包含多字节字符在内的字符串,如中文字符。每个 nchar 字符占用 4 bytes 的存储空间。字符串两端使用单引号引用,字符串内的单引号需用转义字符 \’ 。nchar 使用时须指定字符串大小,类型为 nchar(10) 的列表示此列的字符串最多存储 10 个 nchar 字符,会固定占用 40 bytes 的空间。如果用户字符串长度超出声明长度,将会报错。 |
(1)TDEngine对SQL语句中的英文字符不区分大小写,自动转化为小写执行。可以使用单引号保持大小写。
(2)虽然 Binary 类型在底层存储上支持字节型的二进制字符,但不同编程语言对二进制数据的处理方式并不保证一致,因此建议在 Binary 类型中只存储 ASCII 可见字符,而避免存储不可见字符。多字节的数据,例如中文字符,则需要使用 nchar 类型进行保存。如果强行使用 Binary 类型保存中文字符,虽然有时也能正常读写,但并不带有字符集信息,很容易出现数据乱码甚至数据损坏等情况。
二、数据库管理
1、创建库
(1)语法
CREATE DATABASE [IF NOT EXISTS] db_name [KEEP keep] [DAYS days] [UPDATE 1];
keep是该数据库的数据保留多长天数,缺省是3650(10年),数据库会自动删除超过时限的数据。
update标志数据库支持更新相同时间戳数据。
(2)示例
create database maifodor keep 365 days 10 blocks 4 update 1;
上述语句将创建一个名为maifodor的库,这个库的数据将保留365天(超过365天将被自动删除),每10天一个数据文件,内存块数为4,允许更新数据。
创建库之后使用use maifodro切换到此库中。
2、删除数据库
drop database [if exists] db_name;
3、显示系统当前参数
show variables;
4、修改数据库参数
(1)comp
alter database db_name comp 2;
comp参数是指修改数据文件压缩标志位,缺省值为2 ,取值范围为[0,2],0表示不压缩,1表示一阶段压缩,2表示两阶段压缩。
(2)replica
alter database db_name replica 2;
replica参数是指修改数据库副本数,取值范围[1,3]。在集群中使用,副本数必须小于等于DNODE的数目。
(3)keep
取值范围[days,365000],
(4)quorum
QUORUM 参数是指数据写入成功所需要的确认数,取值范围 [1, 2]。对于异步复制,quorum 设为 1,具有 master 角色的虚拟节点自己确认即可。对于同步复制,需要至少大于等于 2。原则上,Quorum >= 1 并且 Quorum <= replica(副本数),这个参数在启动一个同步模块实例时需要提供。
(5)blocks
BLOCKS 参数是每个 VNODE (TSDB) 中有多少 cache 大小的内存块,因此一个 VNODE 的用的内存大小粗略为(cache * blocks)。取值范围 [3, 1000]。
(6)cachelast
CACHELAST 参数控制是否在内存中缓存子表的最近数据。缺省值为 0,取值范围 [0, 1, 2, 3]。其中 0 表示不缓存,1 表示缓存子表最近一行数据,2 表示缓存子表每一列的最近的非 NULL 值,3 表示同时打开缓存最近行和列功能。
(7)wal
WAL 参数控制 WAL 日志的落盘方式。缺省值为 1,取值范围为 [1, 2]。1 表示写 WAL,但不执行 fsync;2 表示写 WAL,而且执行 fsync。
(8)fsync
FSYNC 参数控制执行 fsync 操作的周期。缺省值为 3000,单位是毫秒,取值范围为 [0, 180000]。如果设置为 0,表示每次写入,立即执行 fsync。该设置项主要用于调节 WAL 参数设为 2 时的系统行为。
(9)update
UPDATE 参数控制是否允许更新数据。缺省值为 0,取值范围为 [0, 1]。0 表示会直接丢弃后写入的相同时间戳的数据;1 表示会使用后写入的数据覆盖已有的相同时间戳的数据。
5、显示系统所有数据库
show databases;
6、显示一个数据库的创建语句
show create database db_name;
常用于数据库迁移。对一个已经存在的数据库,返回其创建语句;在另一个集群中执行该语句,就能得到一个设置完全相同的 Database。
三、表管理
1、创建表
CREATE TABLE [IF NOT EXISTS] tb_name (timestamp_field_name TIMESTAMP, field1_name data_type1 [, field2_name data_type2 ...]);
(1)表的第一个字段必须是timestamp,并且系统自动将其设为主键。
(2)每行长度不能超过16k个字符。
2、以超级表为模板创建数据表
CREATE TABLE [IF NOT EXISTS] tb_name USING stb_name TAGS (tag_value1, ...);
3、以超级表为模板创建数据表,并指定具体的tags列
CREATE TABLE [IF NOT EXISTS] tb_name USING stb_name (tag_name1, ...) TAGS (tag_value1, ...);
指定一部分tags列的值来创建数据库表(没被指定的tags列会设为空值)
4、批量创建数据库
CREATE TABLE [IF NOT EXISTS] tb_name1 USING stb_name TAGS (tag_value1, ...) tb_name2 USING stb_name TAGS (tag_value2, ...) ...;
(1)批量建表方式要求数据表必须以超级表为模板
(2)在不超过SQL语句长度限制(65480个字符)的前提下,单条语句中的建表数量建议控制在1000~3000之间,将会获得比较理想的建表速度。
5、删除数据表
drop table [if exists] tb_name;
6、显示当前数据库下的所有数据表信息
show tables [like tab_name_wildcar]
(1)like后的语句不要超过24个字节
(2)%匹配0到任意个字符,_匹配一个字符。
7、显示一个数据表的创建语句
show create table tb_name;
常用于数据库迁移。对一个已经存在的数据表,返回其创建语句;在另一个集群中执行该语句,就能得到一个结构完全相同的数据表。
8、在线修改显示字符宽度
set max_binary_display_width <nn>;
如显示的内容后面以...结尾时,表示该内容已被截断,可通过本命令修改显示字符宽度以显示完整的内容。
9、获取表的结构信息
describe tb_name;
10、表增加列
alter table tb_name add column field_name data_type;
(1)列的最大个数为1024,最小个数为2
(2)列名最大长度为64
11、表删除列
alter table tb_name drop column field_name;
如果表是通过超级表创建,更改表结构的操作只能对超级表进行。同时针对超级表的结构更改对所有通过该结构创建的表生效。对于不是通过超级表创建的表,可以直接修改表结构。
四、超级表STable管理
1、创建超级表
CREATE STABLE [IF NOT EXISTS] stb_name (timestamp_field_name TIMESTAMP, field1_name data_type1 [, field2_name data_type2 ...]) TAGS (tag1_name tag_type1, tag2_name tag_type2 [, tag3_name tag_type3]);
(1)tags列的数据类型不能是timestamp
(2)tags列名不能与其他列名相同
(3)tagq最多允许128,至少1个,总长度不超过16KB
2、删除超级表
drop stable [if exists] stb_name;
删除 STable 会自动删除通过 STable 创建的子表。
3、显示当前数据库下的所有超级表信息
show stables [like tb_name_wildcard];
查看数据库内全部 STable,及其相关信息,包括 STable 的名称、创建时间、列数量、标签(TAG)数量、通过该 STable 建表的数量。
4、显示一个超级表的创建语句
show create stable stb_name;
5、超级表增删列
alter stable stb_name add column field_name data_type;
alter stable stb_name drop column field_name;
五、超级表STable 中tag管理
1、添加标签
alter stable stb_name add tags new_tag_name tag_type;
2、删除标签
alter stable stb_name drop tag_name;
3、修改标签名
alter stable stb_name change tag old_tag_name new_tag_name;
4、修改子表标签值
alter table tb_name set tag tag_name=new_tag_name;
六、数据写入
1、插入一条记录
INSERT INTO tb_name VALUES (field_value, ...);
2、指定列插入
INSERT INTO tb_name (field1_name, ...) VALUES (field1_value1, ...);
3、插入多条记录
INSERT INTO tb_name VALUES (field1_value1, ...) (field1_value2, ...) ...;
在使用“插入多条记录”方式写入数据时,不能把第一列的时间戳取值都设为now,否则会导致语句中的多条记录使用相同的时间戳,于是就可能出现相互覆盖以致这些数据行无法全部被正确保存。
4、向多个表插入多条记录
INSERT INTO tb1_name VALUES (field1_value1, ...) (field1_value2, ...) ...
tb2_name VALUES (field1_value1, ...) (field1_value2, ...) ...;
5、插入记录时自动建表
INSERT INTO tb_name USING stb_name TAGS (tag_value1, ...) VALUES (field_value1, ...);
如果用户在写数据时并不确定某个表是否存在,此时可以在写入数据时使用自动建表语法来创建不存在的表,若该表已存在则不会建立新表。自动建表时,要求必须以超级表为模板,并写明数据表的 TAGS 取值。
七、数据查询
SELECT select_expr [, select_expr ...]
FROM {tb_name_list}
[WHERE where_condition]
[INTERVAL (interval_val [, interval_offset])]
[SLIDING sliding_val]
[FILL fill_val]
[GROUP BY col_list]
[ORDER BY col_list { DESC | ASC }]
[SLIMIT limit_val [SOFFSET offset_val]]
[LIMIT limit_val [OFFSET offset_val]]
[>> export_file];
1、通配符
(1)*对于普通表,结果中只有普通列。
(2)*对超级表,包含标签列。
(3通配符支持表名前缀
SELECT * FROM d1001;
SELECT d1001.* FROM d1001;
2、标签列
支持在普通表的查询中指定标签列,且标签列的值会与普通列的数据一起返回。
3、as可以声明列的别名
4、特殊用法
(1)特殊关键字
TBNAME:在超级表查询中可视为一个特殊的标签,代表查询涉及的子表名。
_c0:表示超级表的第一列。
5、LIMIT参数控制的是每个分组中至多允许输出的条数,SLIMIT控制由groupby指令划分分组中,至多允许输出几个分组的数据。
6、支持的条件过滤操作
Operation | Note | Applicable Data Types |
---|---|---|
> | larger than | timestamp and all numeric types |
< | smaller than | timestamp and all numeric types |
>= | larger than or equal to | timestamp and all numeric types |
<= | smaller than or equal to | timestamp and all numeric types |
= | equal to | all types |
<> | not equal to | all types |
between and | within a certain range | timestamp and all numeric types |
% | match with any char sequences | binary nchar |
_ | match with a single char | binary nchar |
(1)<>也可以写为!=,不能用于第一列timestamp字段
(2)支持AND OR
(3)针对单一字段的过滤,如果是时间过滤条件,则一条语句中只支持设定一个,但针对其他列或标签列,则可以使用OR关键字进行组合条件的查询过滤,例如:((value > 20 AND value < 30) OR (value < 12))
(4)新版本支持between and语法。
7、union all
SELECT ...
UNION ALL SELECT ...
[UNION ALL SELECT ...]
TDengine 支持 UNION ALL 操作符。也就是说,如果多个 SELECT 子句返回结果集的结构完全相同(列名、列类型、列数、顺序),那么可以通过 UNION ALL 把这些结果集合并到一起。目前只支持 UNION ALL 模式,也即在结果集的合并过程中是不去重的。
八、SQL函数
1、聚合函数
(1)count
统计表、超级表中记录行数或某列的非空值个数。
结果类型:长整型int64
(2)avg
统计表、超级表中某列的平均值。
结果类型:Double
不能应用于timestamp、binary、nchar、bool字段
(3)twa
统计表中某列在一段时间内的时间加权平均。
结果类型:Double
不能应用在timestamp、binary、nchar、bool类型字段
(4)sum
结果类型: Double和int64
不能应用在timestamp、binary、nchar、bool类型字段
(5)stddev
某列的均方差
结果类型:Double
不能应用在timestamp、binary、nchar、bool类型字段
(6)leastquares
统计表中某列的值是主键(时间戳)的拟合直线方程。start_val是自变量初始值,step_val是自变量的步长值。
结果类型:字符串表达式(斜率、截距)
SELECT LEASTSQUARES(field_name, start_val, step_val) FROM tb_name [WHERE clause];
九、选择函数
1、min
不能应用于timestamp、binary、nchar、bool类型
2、max
不能应用于timestamp、binary、nchar、bool类型
3、first
统计某列的值最先写入的非NULL值。
4、last
统计某列的值最后写入的非null值
5、top
SELECT TOP(field_name, K) FROM { tb_name | stb_name } [WHERE clause];
统计表中某列的值最大K个非null值。如果多条数据取值一样,全部取用又会超出 k 条限制时,系统会从相同值中随机选取符合要求的数量返回。
(1)k值取值范围1≤k≤100;
(2)不支持FILL子句
6、bottom
统计表中某列最小k个非null的值。
7、percentile
SELECT PERCENTILE(field_name, P) FROM { tb_name } [WHERE clause];
统计表中某列的值百分比分位数。
不能应用于timestamp、binary、nchar、bool类型字段。
P值取值范围0≤P≤100,为0的时候等同于MIN,为100的时候等同于MAX。
结果类型:Double。
8、apercentile
与PERCENTILE函数相似,但是返回近似结果。推荐使用APERCENTILE
函数,该函数性能远胜于PERCENTILE
函数。
9、last_row
获取表的最后一条记录
说明:与LAST函数不同,LAST_ROW不支持时间范围限制,强制返回最后一条记录。
限制:LAST_ROW()不能与INTERVAL一起使用。
十、计算函数
1、diff
统计表中某列的值与前一行对应值的差。
不能应用在timestamp、binary、nchar、bool类型字段。
说明:输出结果行数是范围内总行数减一,第一行没有结果输出。从 2.1.3.0 版本开始,DIFF 函数可以在由 GROUP BY 划分出单独时间线的情况下用于超级表(也即 GROUP BY tbname)。
2、spread
统计表中某列的最大值和最小值之差
应用字段:不能应用在binary、nchar、bool类型字段。
说明:可用于TIMESTAMP字段,此时表示记录的时间覆盖范围。
3、四则运算
SELECT field_name [+|-|*|/|%][Value|field_name] FROM { tb_name | stb_name } [WHERE clause];
应用字段:不能应用在timestamp、binary、nchar、bool类型字段。
说明:
1)支持两列或多列之间进行计算,可使用括号控制计算优先级;
2)NULL字段不参与计算,如果参与计算的某行中包含NULL,该行的计算结果为NULL。
十一、时间维度聚合
SELECT function_list FROM tb_name
[WHERE where_condition]
INTERVAL (interval [, offset])
[SLIDING sliding]
[FILL ({NONE | VALUE | PREV | NULL | LINEAR | NEXT})]
SELECT function_list FROM stb_name
[WHERE where_condition]
INTERVAL (interval [, offset])
[SLIDING sliding]
[FILL ({ VALUE | PREV | NULL | LINEAR | NEXT})]
[GROUP BY tags]
1、聚合时间段的长度由关键词INTERVAL指定,最短时间间隔10毫秒(10a),并且支持偏移(偏移必须小于间隔)。聚合查询中,能够同时执行的聚合和选择函数仅限于单个输出的函数:count、avg、sum 、stddev、leastsquares、percentile、min、max、first、last,不能使用具有多行输出结果的函数(例如:top、bottom、diff以及四则运算)。
2、WHERE语句可以指定查询的起止时间和其他过滤条件。
3、SLIDING语句用于指定聚合时间段的前向增量。
4、FILL语句指定某一时间区间数据缺失的情况下的填充模式。填充模式包括以下几种:
(1)不进行填充:NONE
(2)固定值填充:Fill(value,1.23)
(3)null填充
(4)prev填充,使用前一个非Null值填充数据。
(5)next填充,使用下一个非null值填充数据。
5、注意
(1)使用FILL语句的时候可能生成大量的填充输出,务必指定查询的时间区间。针对每次查询,系统可返回不超过1千万条具有插值的结果。
(2)在时间维度聚合中,返回的结果中时间序列严格单调递增。
(3)如果查询对象是超级表,则聚合函数会作用于该超级表下满足值过滤条件的所有表的数据。如果查询中没有使用GROUP BY语句,则返回的结果按照时间序列严格单调递增;如果查询中使用了GROUP BY语句分组,则返回结果中每个GROUP内不按照时间序列严格单调递增。
十二、SQL边界限制
(1)数据库名最大长度为 32
(2)表名最大长度为 192,每行数据最大长度 16k 个字符(注意:数据行内每个 BINARY/NCHAR 类型的列还会额外占用 2 个字节的存储位置)
(3)列名最大长度为 64,最多允许 1024 列,最少需要 2 列,第一列必须是时间戳
(4)标签名最大长度为 64,最多允许 128 个,可以 1 个,一个表中标签值的总长度不超过 16k 个字符
(5)SQL 语句最大长度 65480 个字符,但可通过系统配置参数 maxSQLLength 修改,最长可配置为 1M
(6)SELECT 语句的查询结果,最多允许返回 1024 列(语句中的函数调用可能也会占用一些列空间),超限时需要显式指定较少的返回数据列,以避免语句执行报错。
(7)库的数目,超级表的数目、表的数目,系统不做限制,仅受系统资源限制
十三、其他约定
1、TAOS SQL支持对标签、TBNAME进行GROUP BY操作,也支持普通列进行GROUP BY,前提是:仅限一列且该列的唯一值小于10万个。
2、TAOS SQL支持表之间按主键时间戳来join两张表的列,暂不支持两个表之间聚合后的四则运算。
3、IS NOT NULL支持所有类型的列。不为空的表达式为 <>"",仅对非数值类型的列适用。
十四、删除数据
删除数据是 TDengine 提供的根据指定时间段删除指定表或超级表中数据记录的功能,方便用户清理由于设备故障等原因产生的异常数据。
1、语法
DELETE FROM [ db_name. ] tb_name [WHERE condition];
(1)db_name
:可选参数,指定要删除表所在的数据库名,不填写则在当前数据库中
(2)tb_name
:必填参数,指定要删除数据的表名,可以是普通表、子表,也可以是超级表。
(3)condition:可选参数,指定删除数据的过滤条件,不指定过滤条件则为表中所有数据,请慎重使用。特别说明,这里的 where 条件中只支持对第一列时间列的过滤。
注意:
数据删除后不可恢复,请慎重使用。为了确保删除的数据确实是自己要删除的,建议可以先使用 select
语句加 where
后的删除条件查看要删除的数据内容,确认无误后再执行 delete
命令。
2、示例
delete from meters where ts < '2021-10-01 10:40:00.100' ;