taos 数据库建表插入数据案列笔记

--超级表
--1实名制：先保存人员信息（用户信息）
--人脸照片
--设备id
--身份证号码（+id）

CREATE STABLE st_realname  (
  ts timestamp,
  face_img NCHAR(1000),
  auth_type INT,
  auth_result INT,
  user_id bigint(20)
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${tableName} USING  st_realname TAGS(#{deviceId},#{sectionId}) VALUES (
 #{ts},#{faceImg},#{authType},#{authResult},#{userId});
 

--2气体监测

CREATE STABLE st_gasmonitor  (
  ts timestamp,
  gas_type NCHAR(20),
  unit NCHAR(20),
  value FLOAT
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${tableName} USING  st_gasmonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{ts},#{gasType},#{value},#{unit});
 
 
 INSERT INTO t_1397360254667018242 USING  st_gasmonitor TAGS('1397360254667018242') VALUES ('2021-05-27 12:11:11.523','CH4','%VOL',0.0);
 insert into t_1397360254667018242 using  st_gasmonitor tags('1397360254667018242') values (now,'CH4','%VOL',0.0);
		

--3应急调度

CREATE STABLE emergencycall  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  emergencycall TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
--4安全步距

CREATE STABLE stepdist  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  stepdist TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
			
--5人员定位

CREATE STABLE st_location  (
  ts timestamp,
  card_num NCHAR(20),
  card_device_id NCHAR(20),
  battery NCHAR(20),
  nx DOUBLE,
  ny DOUBLE,
  alarm_state NCHAR(20),
  missing_state NCHAR(20)
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${tableName} USING  st_location TAGS(#{deviceId},#{sectionId}) VALUES (
 #{ts},#{cardNum},#{cardDeviceId},#{battery},#{nX},#{nY},#{alarmState},#{missingState});
					
--6特种设备
--6.1塔吊监测

CREATE STABLE cranemonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  cranemonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
 
 --6.2升降机监测
CREATE STABLE liftmonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  liftmonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
	
 --7桥梁监测
CREATE STABLE bridgemonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  bridgemonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
			
	
 --8偏坡监测
CREATE STABLE slopemonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  slopemonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
				
	
 --9监控量测
CREATE STABLE measurement  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  measurement TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
		
 --10扬尘监测
CREATE STABLE envmonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  envmonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
			
 --11行为监测
CREATE STABLE behaviormonitor  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  behaviormonitor TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});
		
					
 --12可穿戴
CREATE STABLE wearable  (
  update_time timestamp,
  name NCHAR(10),
  excavationface FLOAT,
  invertedarchAlarm bool
)
TAGS (device_id bigint(20),section_id bigint(20));
--插入数据案列
 INSERT INTO ${deviceSn} USING  wearable TAGS(#{deviceId},#{sectionId}) VALUES (
 #{updateDate},#{name});