ts/SQLite.sql

-- Active: 1745229607546@@127.0.0.1@3306



#获取所有的模版
SELECT *
FROM tExecPlanTemplate;

#获取模板列表
SELECT EPT.TemplateName, EPT.ChannelID 
FROM tExecPlanTemplate AS EPT
WHERE ChannelID = %1;

#获取所有的模板列表，无需关注频率
SELECT EPT.TemplateName, EPT.ChannelID 
FROM tExecPlanTemplate AS EPT
GROUP BY EPT.TemplateName;

UPDATE tExecPlanTemplate 
SET TemplateName = '模版2'
WHERE TemplateName = '模版1';


#================================================================================================
# 表格操作
#================================================================================================

#检查表格是否存在
SELECT name 
FROM sqlite_master 
WHERE type='table' AND name='%1';

SELECT *
FROM sqlite_master;

#创建表格
CREATE TABLE IF NOT EXISTS "tExecPlanTemplate"
(
    "TemplateName" VARCHAR(255) NOT NULL,
    "ChannelID" INT NOT NULL,
    "ChannelName" VARCHAR(255),
    "OnWeekDay" INT NOT NULL,
    "OnDate" VARCHAR(255) NOT NULL,
    "OnTime" VARCHAR(255) NOT NULL,
    "OffWeekDay" INT NOT NULL,
    "OffDate" VARCHAR(255) NOT NULL,
    "OffTime" VARCHAR(255) NOT NULL,

    "DeviceName" VARCHAR(255),
    "ActionName" VARCHAR(255),
    "ActionID" INT
)

#删除表格
DROP TABLE IF EXISTS "tExecPlanTemplate";