MySQL常用知识点汇总

mysql中的join

可查出左表所有数据

select * from tb_zdxm p  left join tb_zdxm_plan pl on p.id=pl.pro_id  and pl.plan_year='2017' ; 

只能查出部分数据

select * from tb_zdxm p  left join tb_zdxm_plan pl on p.id=pl.pro_id  where pl.plan_year='2017' ;

重复数据取最新数据的问题

错误做法

重复数据单纯排序后分组取出的数据仍然不是最新数据，order by失效

select *
       FROM (select me2.matter_id m_id, me2.satisfaction_degree
             FROM matter_evaluate me2
                      LEFT JOIN matter_order mo3
                                ON me2.matter_id = mo3.id
             order by me2.found_time desc) me3
       group by me3.m_id

正确做法
排序后增加一个“Limit${填写一个很大的值}”，此时order by生效

select *
           FROM (select *
                  FROM (select me2.matter_id m_id, me2.satisfaction_degree
                        FROM matter_evaluate me2
                                 LEFT JOIN matter_order mo3
                                           ON me2.matter_id = mo3.id
                        order by me2.found_time desc
                        LIMIT 1000000000) me3
                  group by me3.m_id) me4

效率更改的写法

select matter_id, max(found_time) found_time
from matter_evaluate
group by matter_id;

mysql中的find_in_set函数

FIND_IN_SET(str,strList)

• str 要查询的字符串
• strList 字段名，参数以“,”分隔，如(1,2,6,8)
• 查询字段(strList)中包含的结果，返回结果null或记录。

假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

select find_in_set('1','1,2,3,4,5,6');

mysql locate函数

判断字符串（string）中是否包含另一个字符串（subStr）

locate(subStr,string) ：函数返回subStr在string中出现的位置

// 如果字符串 string 包含 subStr
locate(subStr,string) > 0
 
// 如果字符串 string 不包含 subStr
locate(subStr,string) = 0

SELECT * 
FROM party_course_study
WHERE LOCATE(findCode, '00001') >0
 
// 注：Mybatis使用场景，需要加 <![CDATA[ ]]>
SELECT * 
FROM party_course_study
WHERE <![CDATA[ LOCATE(findCode, '00001') > 0 ]]>

mysql datetime类型求差

mysql中两个datetime类型的数据相减得到两个日期的时间差

start='2018-12-06 05:00:00'
end='2018-12-06 10:00:00'

求差

TIMESTAMPDIFF(unit,start,end)

• unit分类
  • MINUTE：以分钟为单位
  • HOUR:小时为单位
  • DAY：天为单位
  • MONTH:月为单位

mysql datediff(获取两个日期的时间间隔)

DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。

计算时只用到这些值的日期部分。

mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
    -> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
|    1 |  -15 |
+------+------+
1 row in set (0.00 sec)

mysql统计一年12月份数据

效果

select 
sum(case month(createTime) when '1' then 1 else 0 end) as 一月份,
sum(case month(createTime) when '2' then 1 else 0 end) as 二月份,
sum(case month(createTime) when '3' then 1 else 0 end) as 三月份,
sum(case month(createTime) when '4' then 1 else 0 end) as 四月份,
sum(case month(createTime) when '5' then 1 else 0 end) as 五月份,
sum(case month(createTime) when '6' then 1 else 0 end) as 六月份,
sum(case month(createTime) when '7' then 1 else 0 end) as 七月份,
sum(case month(createTime) when '8' then 1 else 0 end) as 八月份,
sum(case month(createTime) when '9' then 1 else 0 end) as 九月份,
sum(case month(createTime) when '10' then 1 else 0 end) as 十月份,
sum(case month(createTime) when '11' then 1 else 0 end) as 十一月份,
sum(case month(createTime) when '12' then 1 else 0 end) as 十二月份,
year(createTime) as year
from fire_disaster
where year(createTime) in('2015','2016','2017','2018') group by  year;

逗号分隔字符求交集函数

CREATE FUNCTION str_intersection(str1 TEXT, str2 TEXT) RETURNS TINYINT
BEGIN
    DECLARE idx INT DEFAULT 0; -- B 集合单元索引
    DECLARE len INT DEFAULT 0;-- B 集合表达式长度
    DECLARE llen INT DEFAULT 0;-- 最后检查位置
    DECLARE clen INT DEFAULT 0;-- 当前检查位置
    DECLARE tmpStr varchar(255);-- 临时检查数据集
    DECLARE curt varchar(255);-- B 当前检查的单元
    SET len = LENGTH(str1);
    WHILE idx < len
        DO
            SET idx = idx + 1;
            SET tmpStr = SUBSTRING_INDEX(str2, ',', idx);
            SET clen = LENGTH(tmpStr);
-- 获取当前 setB 中的单元
            IF idx = 1 THEN
                SET curt = tmpStr;
            ELSE
                SET curt = SUBSTRING(str2, llen + 2, clen - llen - 1);
            END IF;
-- 检查是否存在于 setA 中
            IF curt != '' and FIND_IN_SET(curt, str1) > 0 THEN
                RETURN 1;
            END IF;
-- 当前检查终点与上次检查终点相同则跳出
            IF clen <= llen THEN
                RETURN 0;
            END IF;

            SET llen = clen;
        END WHILE;
    RETURN 0;
END;

正则表达式匹配字符串交集

create function is_mixed(str1 text, str2 text) returns tinyint
BEGIN
    DECLARE ismixed TINYINT DEFAULT 0;
    set ismixed:=(select concat(str1, ',') regexp concat(replace(str2,',',',|'), ','));
    RETURN ismixed;
    END;