前言
hello,大家好，我是小慕呀！今天我们继续来学习吧！
自古深情留不住，总是套路得人心；舔狗舔到最后一无所有！！！thanks
MySQL字段使用函数导致索引失效
MySQL中对字段使用函数导致索引失效的问题其实很常见，大家一般都知道，但是工作中还是有很多人会犯错，比如下面这个例子，查询今天提交的订单。很多同学可能会写上面这种，实际上这种写法导致索引失效，最终全表扫描
-- 索引失效explain select * from oms_order o where date_format( o.SUBMIT_TIME, '%Y-%m-%d' ) = date_format( NOW( ), '%Y-%m-%d' ) -- 索引有效explain select * from oms_order o where o.SUBMIT_TIME BETWEEN curdate() AND DATE_SUB(curdate(),INTERVAL -1 DAY)
MySQL字符串分割一列变成多列
一个朋友提了一个问题，怎么把一列根据某个字符分割成多列(可能是0-5列，即最多5列)。如 a/b/c/d/e ==> a b c d e x/y/z ==> x y z并且我跟他确认了，每列的数据值不会相等，即 a b c d e 几个数不会相等我这里造了一下数据，如下图
最终效果图
SQL 代码如下
select w.name1,case when w.name1 != w.name2 then w.name2 else null end as name2,case when w.name2 != w.name3 then w.name3 else null end as name3,case when w.name3 != w.name4 then w.name4 else null end as name4,case when w.name4 != w.name5 then w.name5 else null end as name5from (SELECT SUBSTRING_INDEX(, '/', 1) AS name1, SUBSTRING_INDEX( SUBSTRING_INDEX(, '/', 2) ,'/', -1) AS name2, SUBSTRING_INDEX( SUBSTRING_INDEX(, '/', 3) ,'/', -1) AS name3, SUBSTRING_INDEX( SUBSTRING_INDEX(, '/', 4) ,'/', -1) AS name4, SUBSTRING_INDEX( SUBSTRING_INDEX(, '/', 5) ,'/', -1) AS name5FROM test t) w
MySQL的group_contact用法
功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果，我们可以手动指定分隔符，如果不指定的话，默认就是用逗号进行分割。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。
示例
假设如下数据库表 testgroup ，数据如下
假设现有需求就是每个id为一行 在前台每行显示该id所有分数，那我们的SQL应该这样写
SELECT id,GROUP_CONCAT(score) FROM testgroup GROUP BY id
我们可以看到 根据id 分成了三行 并且分数默认用 逗号 分割 但是有每个id有重复数据 接下来如果要对分数去重，我们可以改写SQL为如下所示 （在group_contact()里加上distinct即可）
SELECT id,GROUP_CONCAT(DISTINCT score) FROM testgroup GROUP BY id
我们还可以对分数进行排序，改成降序排序，改写SQL成如下
SELECT id,GROUP_CONCAT(score ORDER BY score DESC) FROM testgroup GROUP BY id
如果不喜欢用逗号做分隔符，我们还可以手动指定自己想要的分隔符，比如我们指定分号做分隔符，SQL如下
SELECT id,GROUP_CONCAT(score SEPARATOR ';') FROM testgroup GROUP BY id
这样就OK了，是不是很简单呀！
MySQL的时间查询用法
一、SQL语句查询今天、昨天、前天的记录
查询今天的记录(create_time是datetime类型字段，对应Java类型Date即可)SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 0
查询昨天的SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 1
查询前天的SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 2
二、mysql 查询当天、本周，本月，上一个月的数据
//今天select * from 表名 where to_days(时间字段名) = to_days(now());
//昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
//近7天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
//近30天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
//本月SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
//上个月SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
//查询本季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
//查询上季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
//查询本年数据select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
//查询上年数据select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
//查询当前这周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
//查询上周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
//查询距离当前现在6个月的数据select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
//查询指定时间段的数据select fullName,addedTime FROM t_user where addedTime between '2020-1-1 00:00:00' and '2021-1-1 00:00:00';select fullName,addedTime FROM t_user where addedTime >='2020-1-1 00:00:00' and addedTime < '2021-1-1 00:00:00';
三、mysql日期时间转换函数
1、日期时间转换字符串格式
Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)
select date_format('2021-08-31 22:23:01', '%Y%m%d%H%i%s');//打印结果 20210831222301MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。
2、字符串转换为日期时间
MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
select str_to_date('08/31/2021', '%m/%d/%Y'); -- 2021-08-31select str_to_date('08/31/2021' , '%m/%d/%y'); -- 2021-08-31select str_to_date('08.31.2021', '%m.%d.%Y'); -- 2021-08-31select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30select str_to_date('08.09.2021 08:31:30', '%m.%d.%Y %h:%i:%s'); -- 2021-08-3108:09:30可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。
3、（日期、天数）转换函数
MySQL （日期、天数）转换函数：to_days(date), from_days(days)
//TO_DAYS函数 返回一个天数，什么天数? 从年份0开始的天数 select to_days('0000-00-00'); -- 0
//查从0年开始 到2021年10月01号之间的天数select to_days('2021-10-01'); -- 738398
//查昨天的数据select * from table where to_days(now())-to_days(create_time)<1//FROM_DAYS(N)给出一个天数 N，返回一个 DATE 值：SELECT FROM_DAYS(729669); -> '1997-10-0'
4、（时间、秒）转换函数
MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)
//time_to_sec(time) 函数返回将参数 time 转换为秒数的时间值，转换公式为“小时 ×3600+ 分钟 ×60+ 秒”。select time_to_sec('01:00:05'); -- 3605
//sec_to_time(sec)函数返回将秒参数，转化为小时，分钟和秒的值select sec_to_time(3605); -- '01:00:05'5、拼凑日期、时间函数
MySQL 拼凑日期、时间函数：makedate(year,dayofyear), maketime(hour,minute,second)
//makedate(2021,31) 前面是年份，后面是天数，默认重1月份开始，如果后面天数是多少天，就进一个月数，select makedate(2021,31); -- '2021-01-31'select makedate(2021,32); -- '2021-02-01'
// maketime(12,15,30)返回从小时，分钟和秒参数计算出的时间值。select maketime(12,15,30); -- '12:15:30'
四、mysql 日期时间计算函数
1、为日期增加一个时间间隔：date_add()
set @dt = now(); -- 定义变量
select date_add(@dt, interval 1 day); -- 增加1天select date_add(@dt, interval 1 hour); -- 增加1小时select date_add(@dt, interval 1 minute); -- 增加1分钟select date_add(@dt, interval 1 second); select date_add(@dt, interval 1 microsecond);select date_add(@dt, interval 1 week);select date_add(@dt, interval 1 month);select date_add(@dt, interval 1 quarter);select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); -- sub 1 day 加-1天也就是减1天
2、为日期减去一个时间间隔：date_sub()
select date_sub('2021-08-31 00:00:00', interval '1 1:1:1' day_second); //-- 2021-08-2922:58:593、日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
-- 两个日期相减 date1 - date2，返回天数。select datediff('2021-08-31', '2021-08-30'); -- 1select datediff('2021-08-31', '2021-08-20'); -- -11-- 两个日期相减 time1 - time2，返回 time 差值。select timediff('2021-08-31 08:08:08', '2021-08-30 00:00:00'); -- 32:08:08select timediff('08:08:08', '00:00:00'); -- -08:08:08行百里者半九十，你知道的越多，你不知道的越多！拜拜！，下期再见！
