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' ;



相关推荐