MySQL 使用到的功能

1 存储过程，即 procedure

存储过程可以把命令的拼接放到数据库当中，并且也接收传参

参考命令格式：

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_task_statistics`(in prm_project varchar(255), in prm_task_name varchar(255), in prm_task_stage varchar(255), in prm_site_id int)
BEGIN set @@session.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';SELECT Project, TaskStage, TaskName, SiteID, UUID, CaseID, CaseName, Type, SubSys, Param, AVG(DetailTimeElapsed) AS DetailTimeElapsedAvg, MAX(DetailTimeElapsed) AS DetailTimeElapsedMax,  MIN(DetailTimeElapsed) AS DetailTimeElapsedMin, AVG(CaseTimeElapsed) AS CaseTimeElapsedAvg,  MAX(CaseTimeElapsed) AS CaseTimeElapsedMax, MIN(CaseTimeElapsed) AS CaseTimeElapsedMin,  AVG(Value) as ValueAvg, Max(Value) as ValueMax, MIN(Value) AS ValueMin, IFNULL(c1.casepass, 0) AS CaseResultPassCount, IFNULL(c2.casefail, 0) AS CaseResultFailCount, IFNULL(c1.casepass, 0)/0 AS CaseResultPassRatio, IFNULL(d1.detailpass, 0) AS DetailResultPassCount,IFNULL(d2.detailfail, 0) AS DetailResultFailCount,  IFNULL(d1.detailpass, 0)/(IFNULL(d1.detailpass, 0) + IFNULL(d2.detailfail, 0)) AS DetailResultPassRatio  FROM sltdb.vw_task_detail  left join (select CaseID as CID, COUNT(CaseResult) casepass  from sltdb.vw_task_detail where CaseResult='Passed' group by CaseID) as c1     on sltdb.vw_task_detail.CaseID = c1.CID left join (select CaseID as CID, COUNT(CaseResult) casefail from sltdb.vw_task_detail where CaseResult!='Passed' group by CaseID) as c2     on sltdb.vw_task_detail.CaseID = c2.CID left join (select CaseID as CID, COUNT(DetailResult) detailpass from sltdb.vw_task_detail as c where DetailResult='Passed' group by CaseID) as d1     on sltdb.vw_task_detail.CaseID = d1.CID left join (select CaseID as CID, COUNT(DetailResult) detailfail from sltdb.vw_task_detail as c where DetailResult!='Passed' group by CaseID) as d2     on sltdb.vw_task_detail.CaseID = d2.CID where Project=prm_project and TaskName=prm_task_name and TaskStage=prm_task_stage group by Project, TaskStage, TaskName, SiteID, UUID, CaseID, CaseName, Type, SubSys, Param;END

参考调用命令格式：

call proc_task_statistics('{project}', '{task_name}', '{task_stage}', '{site_num}')"

当不想传入某个参数时，用null替代，则会检出该列所有数据

2 view。即视图

视图可视作几个表的拼接，在应用过程中主要使用到了

left/right/inner join 将一张表拼接到另外一张表上，必须通过on关键字指定连接表与被连接表的关联列

AS关键字：解决不同表间列相同的冲突问题

TIMEDIFF：两个datetime数据类型列的时差计算

COUNT/MAX/MIN 对列取统计值，必须以GROUP BY列名的方式指定计数逻辑

参考创建视图的命令

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `sltdb`.`vw_task_detail` AS
    SELECT 
        `sltdb`.`tbtask`.`Project` AS `Project`,
        `sltdb`.`tbtask`.`TaskStage` AS `TaskStage`,
        `sltdb`.`tbtask`.`TaskName` AS `TaskName`,
        `sltdb`.`tbsubtask`.`ID` AS `tbSubTaskID`,
        `sltdb`.`tbsubtask`.`LoopNum` AS `LoopNum`,
        `sltdb`.`tbsubtask`.`UUID` AS `UUID`,
        `sltdb`.`tbsubtask`.`SiteID` AS `SiteID`,
        `sltdb`.`tbcaseresult`.`ID` AS `ID`,
        `sltdb`.`tbcaseresult`.`SubTaskID` AS `SubTaskID`,
        `sltdb`.`tbcaseresult`.`Result` AS `CaseResult`,
        `sltdb`.`tbcaseresult`.`BeginTime` AS `CaseBeginTime`,
        `sltdb`.`tbcaseresult`.`EndTime` AS `CaseEndTime`,
        `sltdb`.`tbcaseresult`.`BinCode` AS `BinCode`,
        `sltdb`.`tbcaseresultdetail`.`Param` AS `Param`,
        `sltdb`.`tbcaseresultdetail`.`Value` AS `Value`,
        `sltdb`.`tbcaseresultdetail`.`BeginTime` AS `DetailBeginTime`,
        `sltdb`.`tbcaseresultdetail`.`EndTime` AS `DetailEndTime`,
        `sltdb`.`tbcaseresultdetail`.`Result` AS `DetailResult`,
        `sltdb`.`tbcase`.`SubSys` AS `SubSys`,
        `sltdb`.`tbcase`.`Type` AS `Type`,
        `sltdb`.`tbcase`.`CaseID` AS `CaseID`,
        `sltdb`.`tbcase`.`CaseStage` AS `CaseStage`,
        `sltdb`.`tbcase`.`CaseName` AS `CaseName`,
        TIMEDIFF(`sltdb`.`tbcaseresultdetail`.`EndTime`,
                `sltdb`.`tbcaseresultdetail`.`BeginTime`) AS `DetailTimeElapsed`,
        TIMEDIFF(`sltdb`.`tbcaseresult`.`EndTime`,
                `sltdb`.`tbcaseresult`.`BeginTime`) AS `CaseTimeElapsed`
    FROM
        ((((`sltdb`.`tbtask`
        LEFT JOIN `sltdb`.`tbsubtask` ON ((`sltdb`.`tbtask`.`ID` = `sltdb`.`tbsubtask`.`TaskID`)))
        LEFT JOIN `sltdb`.`tbcaseresult` ON ((`sltdb`.`tbsubtask`.`ID` = `sltdb`.`tbcaseresult`.`SubTaskID`)))
        LEFT JOIN `sltdb`.`tbcaseresultdetail` ON ((`sltdb`.`tbcaseresult`.`ID` = `sltdb`.`tbcaseresultdetail`.`CaseResultID`)))
        LEFT JOIN `sltdb`.`tbcase` ON ((`sltdb`.`tbcaseresult`.`CaseID` = `sltdb`.`tbcase`.`ID`)))