常用语句
所有应用列表
SELECT demo_all_apps.T_APP_ID,demo_all_apps.T_APP_NAME_CN,demo_all_apps.T_APP_CREATETIME,
`demo@apps_category`.T_APP_CATEGORY_NAME_CN
FROM demo_all_apps
INNER JOIN `demo@apps_category`
ON `demo@apps_category`.T_APP_CATEGORY_ID=demo_all_apps.T_APP_CATEGORY_ID
ORDER BY demo_all_apps.T_APP_ID;
所有编辑过应用特征的应用列表
SELECT demo_all_apps.T_APP_ID,demo_all_apps.T_APP_NAME_CN,demo_all_apps.T_APP_CREATETIME,
`demo@apps_category`.T_APP_CATEGORY_NAME_CN,
demo_all_apps_sigs.T_SIG_APP_ID
FROM demo_all_apps
INNER JOIN `demo@apps_category`
ON `demo@apps_category`.T_APP_CATEGORY_ID=demo_all_apps.T_APP_CATEGORY_ID
INNER JOIN demo_all_apps_sigs
ON demo_all_apps.T_APP_ID=demo_all_apps_sigs.T_SIG_APP_ID
WHERE demo_all_apps.T_APP_AUTHOR!='demoLING' AND demo_all_apps.T_APP_STATUS='1'
AND demo_all_apps_sigs.T_SIG_STATUS='1'
GROUP BY demo_all_apps.T_APP_ID;
所有编辑过审计特征的应用列表
SELECT demo_all_apps.T_APP_ID,demo_all_apps.T_APP_NAME_CN,demo_all_apps.T_APP_CREATETIME,
`demo@apps_category`.T_APP_CATEGORY_NAME_CN,
demo_all_apps_sigs.T_SIG_APP_ID,
demo_all_apps_sigs_adts.T_ADT_SIG_ID
FROM demo_all_apps
INNER JOIN `demo@apps_category`
ON `demo@apps_category`.T_APP_CATEGORY_ID=demo_all_apps.T_APP_CATEGORY_ID
INNER JOIN demo_all_apps_sigs
ON demo_all_apps.T_APP_ID=demo_all_apps_sigs.T_SIG_APP_ID
INNER JOIN demo_all_apps_sigs_adts
ON demo_all_apps_sigs.T_SIG_ID=demo_all_apps_sigs_adts.T_ADT_SIG_ID
WHERE demo_all_apps.T_APP_AUTHOR!='demoLING' AND demo_all_apps.T_APP_STATUS='1'
AND demo_all_apps_sigs.T_SIG_STATUS='1' AND demo_all_apps_sigs_adts.T_ADT_STATUS='1'
GROUP BY demo_all_apps.T_APP_ID;
SELECT DISTINCT A.T_APP_ID,A.T_APP_NAME_CN,C.T_APP_CATEGORY_NAME_CN,B.T_SIG_STATUS
FROM `demo_ALL_APPS` AS A,`demo_ALL_APPS_SIGS` AS B, `demo@APPS_CATEGORY` AS C
WHERE
A.T_APP_ID = B.T_SIG_APP_ID AND A.T_APP_CATEGORY_ID = C.T_APP_CATEGORY_ID
AND A.T_APP_STATUS = 1 AND B.T_SIG_STATUS = 1
AND B.T_SIG_AUTHOR != "demoLING"
ORDER BY T_APP_ID
UPDATE
demo_all_apps AS a,demo_all_apps_sigs AS b,demo_all_apps_sigs_adts AS c
SET c.T_ADT_MATCH_MAIN='tcp_payload[0,6]~%00%00' , c.T_ADT_OFFSET=6 , c.T_ADT_LENGTH=4
WHERE a.T_APP_ID='385' AND a.T_APP_ID=b.T_SIG_APP_ID AND c.T_ADT_SIG_ID=b.T_SIG_ID AND b.T_SIG_PROTOCOL='4';
UPDATE demo_all_apps_sigs,demo_all_apps
SET demo_all_apps_sigs.T_SIG_PRIORITY=10
WHERE demo_all_apps.T_APP_ID=demo_all_apps_sigs.T_SIG_APP_ID
AND demo_all_apps.T_APP_STATUS = 1 AND demo_all_apps_sigs.T_SIG_STATUS = 1
AND demo_all_apps.T_APP_GROUP_TAG LIKE "%byod%"
AND (demo_all_apps.T_APP_CATEGORY_ID='0x00100000' OR demo_all_apps.T_APP_CATEGORY_ID='0x00700000'
OR demo_all_apps.T_APP_CATEGORY_ID='0x00800000' )
AND demo_all_apps.T_APP_AUDIT_SUPPORT=0
SELECT demo_all_apps.T_APP_ID,demo_all_apps.T_APP_NAME_CN,demo_all_apps.T_APP_MODIFYTIME,
demo_all_apps.T_APP_CATEGORY_ID,demo_all_apps_sigs_adts.T_ADT_NAME_CN,
demo_all_apps_sigs.T_SIG_APP_ID
FROM demo_all_apps
INNER JOIN demo_all_apps_sigs
ON demo_all_apps.T_APP_ID=demo_all_apps_sigs.T_SIG_APP_ID
INNER JOIN demo_all_apps_sigs_adts
ON demo_all_apps_sigs.T_SIG_ID=demo_all_apps_sigs_adts.T_ADT_SIG_ID
WHERE T_APP_POST_ID='0'
GROUP BY demo_all_apps.T_APP_ID;
修改某列的值
UPDATE demo_all_apps SET T_APP_POST_ID=3;
UPDATE demo_dev_info INNER JOIN tbldevices
SET demo_dev_info.T_UPDATE_TIME=tbldevices.update_time,demo_dev_info.T_REG_TIME=tbldevices.reg_time
WHERE demo_dev_info.T_DEVICE_SN=tbldevices.device_sn
AND demo_dev_info.T_DEVICE_IP=tbldevices.device_ip;
把一个表的数据插入到另一个表中
只导入目标表中不存在的记录
INSERT INTO 目标表
(字段1, 字段2, ...)
SELECT 字段1, 字段2, ...
FROM 来源表
WHERE not exists (select * from 目标表
where 目标表.比较字段 = 来源表.比较字段);
INSERT INTO demo_dev_info (T_REQ_PACK_ID,T_DEVICE_IP,T_DEVICE_SN,T_REG_TIME,T_UPDATE_TIME)
SELECT device_id,device_ip,device_sn,reg_time,update_time FROM tbldevices
WHERE NOT EXISTS (select * from demo_dev_info
where demo_dev_info.T_DEVICE_IP=tbldevices.device_ip);
1.如果2张表的字段一致，并且希望插入全部数据，可以用这种方法：
INSERT INTO 目标表 SELECT * FROM 来源表;
insert into insertTest select * from insertTest2;
2.如果只希望导入指定字段，可以用这种方法：
INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;(这里的话字段必须保持一致)
insert into insertTest2(id) select id from insertTest2;
INSERT INTO demo_upgrade_app (T_SYS_ID,T_LANGUAGE_ID,T_FILE_NAME,T_FILE_SIZE,
T_START_VERSION,T_END_VERSION,T_OPERATOR,T_CREATE_TIME,T_MODIFY_TIME)
SELECT SystemID,LanguageID,PackFileName,PackFileSize,
BeginVersion,EndVersion,Operator,OperateTime,OperateTime FROM tblupdatepacks
WHERE tblupdatepacks.SystemID=4096;
删除表中的所以记录，但结构不变
Truncate table tablename;
SELECT A.T_APP_ID,A.T_APP_NAME_CN,A.T_APP_CATEGORY_ID,
C.T_APP_CATEGORY_NAME_CN,
B.T_SIG_PRIORITY,B.T_SIG_ID
FROM `demo_ALL_APPS` AS A,`demo_ALL_APPS_SIGS` AS B, `demo@APPS_CATEGORY` AS C
WHERE
A.T_APP_ID = B.T_SIG_APP_ID AND A.T_APP_CATEGORY_ID = C.T_APP_CATEGORY_ID
AND A.T_APP_STATUS = 1 AND B.T_SIG_STATUS = 1
AND A.T_APP_GROUP_TAG LIKE "%byod%"
AND (A.T_APP_CATEGORY_ID='0x00100000' OR A.T_APP_CATEGORY_ID='0x00700000' OR A.T_APP_CATEGORY_ID='0x00800000' )
SELECT A.T_APP_NAME_CN,A.T_APP_PLATFORM,B.T_SIG_ID,B.T_SIG_MATCH_MAIN,T_SIG_MODIFYTIME,B.T_SIG_AUTHOR
FROM demo_all_apps AS A,demo_all_apps_sigs AS B
WHERE A.T_APP_ID = B.T_SIG_APP_ID
AND A.T_APP_PLATFORM != 'Common'
AND B.T_SIG_MATCH_MAIN NOT LIKE '%http_user_agent%';
DROP PROCEDURE IF EXISTS p_app_test;
CREATE PROCEDURE p_app_test(in n int)
BEGIN
SELECT * FROM demo_all_apps where T_APP_ID=n;
END
---------------------------------------------------------
--定义变量
SET @n=149;
--调用存储过程
CALL p_app_test(@n);
'INSERT','1','漏洞','2014-09-25 16:50:29','2014-09-26 16:50:29','design error','cve-20120-1234',1,0,'QQ','QQ 2.9','null','sart cont','currently we are not aware of','QQ 2.9 is prone','NULL'
CALL P_ATA_SECURITYFOCUS(
'INSERT','1','漏洞',
'2014-09-25 16:50:29','2014-09-26 16:50:29',
'design error','cve-20120-1234',
1,0,'QQ','QQ 2.9','null',
'sart cont','currently we are not aware of',
'QQ 2.9 is prone','NULL'
);
DROP PROCEDURE IF EXISTS `P_ATA_CNVD`;
CREATE PROCEDURE `P_ATA_CNVD` (
IN vi_sh_submit_method VARCHAR(253),
IN vi_sh_id INT(11),
IN vi_sh_cnvd_id VARCHAR(253),
IN vi_sh_name_en VARCHAR(253),
IN vi_sh_level VARCHAR(50),
IN vi_sh_publish_time DATETIME,
IN vi_sh_update_time DATETIME,
IN vi_sh_impact_product VARCHAR(253),
IN vi_sh_bugtraq_id TEXT,
IN vi_sh_cve_id VARCHAR(253),
IN vi_sh_des TEXT,
IN vi_sh_discoverer VARCHAR(253),
IN vi_sh_solution TEXT,
IN vi_sh_patch TEXT,
IN vi_sh_reference TEXT )
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT "安全漏洞."
BEGIN
DECLARE vi_sh_publish_time TIMESTAMP;
DECLARE vi_sh_update_time TIMESTAMP;
SET vi_sh_publish_time = CURRENT_TIME;
SET vi_sh_update_time = CURRENT_TIME;
IF( ! STRCMP( vi_sh_submit_method,"INSERT" ))THEN
INSERT INTO `demo@ata_cnvd`
(T_SH_ID,T_SH_CNVD_ID,T_SH_NAME_EN,
T_SH_LEVEL,T_SH_PUBLISH_TIME,T_SH_UPDATE_TIME,
T_SH_IMPACT_PRODUCT,T_SH_BUGTRAQ_ID,T_SH_CVE_ID,
T_SH_DES,T_SH_DISCOVERER,T_SH_SOLUTION,
T_SH_PATCH,T_SH_REFERENCE)
VALUES(vi_sh_id, vi_sh_cnvd_id, vi_sh_name_en,
vi_sh_level, vi_sh_publish_time, vi_sh_update_time,
vi_sh_impact_product, vi_sh_bugtraq_id, vi_sh_cve_id,
vi_sh_des, vi_sh_discoverer, vi_sh_solution,
vi_sh_patch, vi_sh_reference);
ELSEIF(!STRCMP(vi_sh_submit_method,"UPDATE"))THEN
UPDATE `demo@ata_cnvd`
SET T_SH_CNVD_ID = vi_sh_cnvd_id,T_SH_NAME_EN = vi_sh_name_en,
T_SH_LEVEL = vi_sh_level,
T_SH_UPDATE_TIME = vi_sh_update_time,
T_SH_IMPACT_PRODUCT = vi_sh_impact_product,T_SH_BUGTRAQ_ID = vi_sh_bugtraq_id,
T_SH_CVE_ID = vi_sh_cve_id,
T_SH_DES = vi_sh_des,T_SH_DISCOVERER = vi_sh_discoverer,T_SH_SOLUTION = vi_sh_solution,
T_SH_PATCH = vi_sh_patch,T_SH_REFERENCE = vi_sh_reference
WHERE T_SH_ID = vi_sh_id;
END IF;
END;
SELECT A.T_APP_ID,A.T_APP_NAME_CN,
B.T_SIG_NAME_CN,B.T_SIG_ID,B.T_SIG_AUTHOR
FROM `demo_ALL_APPS` AS A,`demo_ALL_APPS_SIGS` AS B
WHERE
A.T_APP_ID = B.T_SIG_APP_ID
AND A.T_APP_STATUS = 1 AND B.T_SIG_STATUS = 1
AND B.T_SIG_MODIFYTIME > '2015-06-14 00:00:00'
ORDER BY B.T_SIG_AUTHOR;
�����ǰFC�8
