TDengine基本操作整理

TDengine

1.建表操作

1.0.数据类型

#	类型	Bytes	说明
1	TIMESTAMP	8	时间戳。缺省精度毫秒，可支持微秒和纳秒，详细说明见上节。
2	INT	4	整型，范围 [-2^31, 2^31-1]
3	INT UNSIGNED	4	无符号整数，[0, 2^32-1]
4	BIGINT	8	长整型，范围 [-2^63, 2^63-1]
5	BIGINT UNSIGNED	8	长整型，范围 [0, 2^64-1]
6	FLOAT	4	浮点型，有效位数 6-7，范围 [-3.4E38, 3.4E38]
7	DOUBLE	8	双精度浮点型，有效位数 15-16，范围 [-1.7E308, 1.7E308]
8	BINARY	自定义	记录单字节字符串，建议只用于处理 ASCII 可见字符，中文等多字节字符需使用 NCHAR
9	SMALLINT	2	短整型， 范围 [-32768, 32767]
10	SMALLINT UNSIGNED	2	无符号短整型，范围 [0, 65535]
11	TINYINT	1	单字节整型，范围 [-128, 127]
12	TINYINT UNSIGNED	1	无符号单字节整型，范围 [0, 255]
13	BOOL	1	布尔型，{true, false}
14	NCHAR	自定义	记录包含多字节字符在内的字符串，如中文字符。每个 NCHAR 字符占用 4 字节的存储空间。字符串两端使用单引号引用，字符串内的单引号需用转义字符 \'。NCHAR 使用时须指定字符串大小，类型为 NCHAR(10) 的列表示此列的字符串最多存储 10 个 NCHAR 字符。如果用户字符串长度超出声明长度，将会报错。
15	JSON		JSON 数据类型， 只有 Tag 可以是 JSON 格式
16	VARCHAR	自定义	BINARY 类型的别名
17	GEOMETRY	自定义	几何类型
18	VARBINARY	自定义	可变长的二进制数据

涛斯数据类型对应java数据类型

#	TDengine DataType	JDBCType （driver 版本 < 2.0.24）	JDBCType （driver 版本 >= 2.0.24）
1	TIMESTAMP	java.lang.Long	java.sql.Timestamp
2	INT	java.lang.Integer	java.lang.Integer
3	BIGINT	java.lang.Long	java.lang.Long
4	FLOAT	java.lang.Float	java.lang.Float
5	DOUBLE	java.lang.Double	java.lang.Double
6	SMALLINT	java.lang.Short	java.lang.Short
7	TINYINT	java.lang.Byte	java.lang.Byte
8	BOOL	java.lang.Boolean	java.lang.Boolean
9	BINARY	java.lang.String	byte array
10	NCHAR	java.lang.String	java.lang.String
11	JSON	-	java.lang.String

1.1.创建数据库

Crete Database power Keep 365 duration 10 buffer 16 WAL_LEVEL 1；

上述语句将创建一个名为 power 的库，这个库的数据将保留 365 天（超过 365 天将被自动删除），每 10 天一个数据文件，每个 VNode 的写入内存池的大小为 16 MB，对该数据库入会写 WAL 但不执行 FSYNC。

1.2.创建超级表

CREATE STABLE [IF NOT EXISTS] stb_name (create_definition [, create_definition] ...) TAGS (create_definition [, create_definition] ...) [table_options]
 
create_definition:
    col_name column_definition
 
column_definition:
    type_name

使用说明

• 超级表中列的最大个数为 4096，需要注意，这里的 4096 是包含 TAG 列在内的，最小个数为 3，包含一个时间戳主键、一个 TAG 列和一个数据列。
• TAGS语法指定超级表的标签列，标签列需要遵循以下约定：
  • TAGS 中的 TIMESTAMP 列写入数据时需要提供给定值，而暂不支持四则运算，例如 NOW + 10s 这类表达式。
  • TAGS 列名不能与其他列名相同。
  • TAGS 列名不能为预留关键字。
  • TAGS 最多允许 128 个，至少 1 个，总长度不超过 16 KB。

**例：**一个物联网系统，往往存在多种类型的设备，比如对于电网，存在智能电表、变压器、母线、开关等等。为便于多表之间的聚合，使用 TDengine, 需要对每个类型的数据采集点创建一个超级表。可以使用如下的 SQL 命令创建超级表：

CREATE STABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupId int);

与创建普通表一样，创建超级表时，需要提供表名，表结构 Schema，即数据列的定义。第一列必须为时间戳（示例中为 ts），其他列为采集的物理量（示例中为 current, voltage, phase），数据类型可以为整型、浮点型、字符串等。除此之外，还需要提供标签的 Schema （示例中为 location, groupId），标签的数据类型可以为整型、浮点型、字符串等。采集点的静态属性往往可以作为标签，比如采集点的地理位置、设备型号、设备组 ID、管理员 ID 等等。标签的 Schema 可以事后增加、删除、修改。

1.3.创建子表

CREATE TABLE [IF NOT EXISTS] tb_name USING stb_name TAGS (tag_value1, ...);

**例：**TDengine 对每个数据采集点需要独立建表。与标准的关系型数据库一样，一张表有表名，Schema，但除此之外，还可以带有一到多个标签。创建时，需要使用超级表做模板，同时指定标签的具体值。

CREATE TABLE d1001 USING meters TAGS ("California.SanFrancisco", 2);

其中 d1001 是表名，meters 是超级表的表名，后面紧跟标签 Location 的具体标签值为 “California.SanFrancisco”，标签 groupId 的具体标签值为 2。虽然在创建表时，需要指定标签值，但可以事后修改。

1.4.批量创建子表

CREATE TABLE [IF NOT EXISTS] tb_name1 USING stb_name TAGS (tag_value1, ...) [IF NOT EXISTS] tb_name2 USING stb_name TAGS (tag_value2, ...) ...;

tb_name1：需创建的表名

stb_name：超级表名

**例：**创建两张子表语句示例

create table d1005 using meters tags("beijing",3) d1006 using meters tags("tianjin",4);

2.建表后操作

1.0.修改主表

1.0.1.增加列

ALTER TABLE tb_name ADD COLUMN field_name data_type;

1.0.2.删除列

ALTER TABLE tb_name DROP COLUMN field_name;

1.0.3.修改列宽

ALTER TABLE tb_name MODIFY COLUMN field_name data_type(length);

1.0.4.修改列名

ALTER TABLE tb_name RENAME COLUMN old_col_name new_col_name

2.1.修改子表

使用说明

1. 对子表的列和标签的修改，除了更改标签值以外，都要通过超级表才能进行。

2.1.0修改子表标签值

ALTER TABLE tb_name SET TAG tag_name=new_tag_value;

2.1.1删除表

DROP TABLE [IF EXISTS] [db_name.]tb_name [, [IF EXISTS] [db_name.]tb_name] ...

3.2.查看表信息

3.2.0显示表创建语句

SHOW CREATE TABLE tb_name;

3.2.1获取表结构信息

DESCRIBE [db_name.]tb_name;

3.数据库写入

1.0.正常语法

1.0.1.插入一条数据

INSERT INTO d1001 VALUES (NOW, 10.2, 219, 0.32);

1.0.2.插入多条记录

INSERT INTO d1001 VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32) (1626164208000, 10.15, 217, 0.33);

1.0.3.指定列插入

INSERT INTO d1001 (ts, current, phase) VALUES ('2021-07-13 14:06:33.196', 10.27, 0.31);

1.0.4.向多个表插入记录

INSERT INTO d1001 VALUES ('2021-07-13 14:06:34.630', 10.2, 219, 0.32) ('2021-07-13 14:06:35.779', 10.15, 217, 0.33)
            d1002 (ts, current, phase) VALUES ('2021-07-13 14:06:34.255', 10.27, 0.31);

1.0.5.插入记录时自动建表

如果用户在写数据时并不确定某个表是否存在，此时可以在写入数据时使用自动建表语法来创建不存在的表，若该表已存在则不会建立新表。自动建表时，要求必须以超级表为模板，并写明数据表的 TAGS 取值。例如：

INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) VALUES ('2021-07-13 14:06:32.272', 10.2, 219, 0.32);

也可以在自动建表时，只是指定部分 TAGS 列的取值，未被指定的 TAGS 列将置为 NULL。例如：

INSERT INTO d21001 USING meters (groupId) TAGS (2) VALUES ('2021-07-13 14:06:33.196', 10.15, 217, 0.33);

自动建表语法也支持在一条语句中向多个表插入记录。例如：

INSERT INTO d21001 USING meters TAGS ('California.SanFrancisco', 2) VALUES ('2021-07-13 14:06:34.630', 10.2, 219, 0.32) ('2021-07-13 14:06:35.779', 10.15, 217, 0.33)
            d21002 USING meters (groupId) TAGS (2) VALUES ('2021-07-13 14:06:34.255', 10.15, 217, 0.33)
            d21003 USING meters (groupId) TAGS (2) (ts, current, phase) VALUES ('2021-07-13 14:06:34.255', 10.27, 0.31);

1.1.超级表语法

自动建表, 表名通过tbname列指定

INSERT INTO meters(tbname, location, groupId, ts, current, phase) 
                values('d31001', 'California.SanFrancisco', 2, '2021-07-13 14:06:34.630', 10.2, 219, 0.32) 
                ('d31001', 'California.SanFrancisco', 2, '2021-07-13 14:06:35.779', 10.15, 217, 0.33)
                ('d31002', NULL, 2, '2021-07-13 14:06:34.255', 10.15, 217, 0.33)        

4.数据查询

1.Hints

Hints 是用户控制单个语句查询优化的一种手段，当 Hint 不适用于当前的查询语句时会被自动忽略，具体说明如下：

• Hints 语法以/*+开始，终于*/，前后可有空格。
• Hints 语法只能跟随在 SELECT 关键字后。
• 每个 Hints 可以包含多个 Hint，Hint 间以空格分开，当多个 Hint 冲突或相同时以先出现的为准。
• 当 Hints 中某个 Hint 出现错误时，错误出现之前的有效 Hint 仍然有效，当前及之后的 Hint 被忽略。
• hint_param_list 是每个 Hint 的参数，根据每个 Hint 的不同而不同。

目前支持的 Hints 列表如下：

Hint	参数	说明	适用范围
BATCH_SCAN	无	采用批量读表的方式	超级表 JOIN 语句
NO_BATCH_SCAN	无	采用顺序读表的方式	超级表 JOIN 语句
SORT_FOR_GROUP	无	采用sort方式进行分组	partition by 列表有普通列时

例：

SELECT /*+ BATCH_SCAN() */ a.ts FROM stable1 a, stable2 b where a.tag0 = b.tag0 and a.ts = b.ts;
SELECT /*+ SORT_FOR_GROUP() */ count(*), c1 FROM stable1 PARTITION BY c1;

2.列表

查询语句可以指定部分或全部列作为返回结果。数据列和标签列都可以出现在列表中。

2.1.通配符

通配符 * 可以用于代指全部列。对于普通表和子表，结果中只有普通列。对于超级表，还包含了 TAG 列。

SELECT * FROM d1001;

通配符支持表名前缀，以下两个 SQL 语句均为返回全部的列：

SELECT * FROM d1001;
SELECT d1001.* FROM d1001;

在 JOIN 查询中，带表名前缀的和不带前缀返回的结果有差别， *返回全部表的所有列数据（不包含标签），而带表名前缀的通配符，则只返回该表的列数据。

SELECT * FROM d1001, d1003 WHERE d1001.ts=d1003.ts;
SELECT d1001.* FROM d1001,d1003 WHERE d1001.ts = d1003.ts;

上面的查询语句中，前者返回 d1001 和 d1003 的全部列，而后者仅返回 d1001 的全部列。

2.2.标签列

在超级表和子表的查询中可以指定 标签列，且标签列的值会与普通列的数据一起返回。

SELECT location, groupid, current FROM d1001 LIMIT 2;

2.3.标签查询

当查询的列只有标签列时，TAGS 关键字可以指定返回所有子表的标签列。每个子表只返回一行标签列。

返回所有子表的标签列：

SELECT TAGS tag_name [, tag_name ...] FROM stb_name

3.case表达式

语法

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END
CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

说明

TDengine 通过 CASE 表达式让用户可以在 SQL 语句中使用 IF … THEN … ELSE 逻辑。

第一种 CASE 语法返回第一个 value 等于 compare_value 的 result，如果没有 compare_value 符合，则返回 ELSE 之后的 result，如果没有 ELSE 部分，则返回 NULL。

第二种语法返回第一个 condition 为真的 result。 如果没有 condition 符合，则返回 ELSE 之后的 result，如果没有 ELSE 部分，则返回 NULL。

CASE 表达式的返回类型为第一个 WHEN THEN 部分的 result 类型，其余 WHEN THEN 部分和 ELSE 部分，result 类型都需要可以向其转换，否则 TDengine 会报错。

示例

某设备有三个状态码，显示其状态，语句如下：

SELECT CASE dev_status WHEN 1 THEN 'Running' WHEN 2 THEN 'Warning' WHEN 3 THEN 'Downtime' ELSE 'Unknown' END FROM dev_table;

统计智能电表的电压平均值，当电压小于 200 或大于 250 时认为是统计有误，修正其值为 220，语句如下：

SELECT AVG(CASE WHEN voltage < 200 or voltage > 250 THEN 220 ELSE voltage END) FROM meters;

4.join子句

TDengine 支持基于时间戳主键的内连接，即 JOIN 条件必须包含时间戳主键。只要满足基于时间戳主键这个要求，普通表、子表、超级表和子查询之间可以随意的进行内连接，且对表个数没有限制，其它连接条件与主键间必须是 AND 操作。

普通表与普通表之间的 JOIN 操作：

SELECT *
FROM temp_tb_1 t1, pressure_tb_1 t2
WHERE t1.ts = t2.ts

超级表与超级表之间的 JOIN 操作：

SELECT *
FROM temp_stable t1, temp_stable t2
WHERE t1.ts = t2.ts AND t1.deviceid = t2.deviceid AND t1.status=0;

子表与超级表之间的 JOIN 操作：

SELECT *
FROM temp_ctable t1, temp_stable t2
WHERE t1.ts = t2.ts AND t1.deviceid = t2.deviceid AND t1.status=0;

5.嵌套查询

SELECT ... FROM (SELECT ... FROM ...) ...;