mysql mybatis 总结

1. count(1) and count()

（1）. count(1) and count()       

 从含义上讲，count(1) 与 count() 都表示对全部数据行的查询。count(*) 包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL ；count(1) 用1代表代码行，在统计结果的时候，不会忽略列值为NULL 。（count（列名）会忽略列值为空）

        表的数据量大些时，对表作分析之后，count(1) 要比使用 count() 用时多。
        在表做过分析之后，count(1)会比count()的用时少些（1w以内数据量），不过差不了多少。 如果count(1)是聚索引, id, count(1) 会更快些。 因为count(), 自动会优化指定到那一个字段。所以没必要去count(1)，用count()，sql会帮你完成优化的。因此：count(1)和count(*)基本没有差别！

        亲测结果： 在字段较多、数据量较大的情况下，使用count(1) 要明显比 count(*) 更加高效。

（2）. count(1) 与 count(字段)

        count(1) 统计表中所有记录数，NULL值记录并不会被忽略；而 count(字段) 会统计该字段在表中出现的次数，忽略字段为NULL值的记录数。

（3）. count() 和 count(1) 和 count(列名) 区别

    执行效率上：
    列名为主键，count(列名) 会比 count(1) 快 ；
    列名不为主键，count(1) 会比 count(列名) 快 ；
    如果表多个列并且没有主键，则 count(1) 的执行效率优于 count() ；
    如果有主键，则 select count（主键）执行效率最优；
    如果表只有一个字段，则 select count(*)最优。

2.mysql 查询

1.单表

2.多表

分类：子查询（嵌套）、内连接、外连接（左右）、全外连接（mysql不支持）、自连接

内连接：查询的交集部分

左外连接工作原理：

        从左表读出一条，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条。 主表的结果集不会减少。

自连接：查询的数据在同一张表中。

自连接案例：

select
case
WHEN km.name is null or km.name ='' then '无'
else km.name
end parentName,
ku.*
from menu ku
left join menu km on ku.parent_id=km.id

#将菜单的父级名称查出来  ，这里用了 case when 函数 parentId为0时，显示无

3.mysql 语句执行顺序

        MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

下面我们来具体分析一下查询处理的每一个阶段

1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。
8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
10. ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11. LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

4.函数

1.GROUP_CONCAT（）可将查询出来的多行数据，连接到一起。可以将此语句做嵌套其他语句里面，用于一对多的字符连接显示。

select 
GROUP_CONCAT(pb.name) as userName 
from person_train_user ptu 
left join person_baseinfo pb on ptu.user_id=pb.id 
where ptu.is_deleted=0 and ptu.train_id=#{trainId}

2. 求年龄，工龄等

TIMESTAMPDIFF(year,work_time,now()) as workingAge

 拓展 datediff()函数 与 timestampdiff()

 datediff（） //日期差异 
 timestampdiff() //时间戳差异，这两个函数比较精确度不一样

datediff()测试

 SELECT DATEDIFF('2018-05-09 08:00:00','2018-05-09') AS DiffDate;
 //结果 0 ； 表示 2018-05-09 与 2018-05-09之间没有日期差。这里是不比较时分秒的。下面验证带上时分秒有没有差别。
 SELECT DATEDIFF('2018-05-09 00:00:00','2018-05-09 23:59:59') AS DiffDate;
 //结果 0 ；
 SELECT DATEDIFF('2018-05-08 23:59:59','2018-05-09 00:00:00') AS DiffDate;
 //结果 -1；
 SELECT DATEDIFF('2018-05-09 00:00:00','2018-05-08 23:59:59') AS DiffDate;
//结果 1；

timestampdiff()测试

select timestampdiff(YEAR,"2018-01-01 15:15:16","2019-08-23 15:15:16") as timestamodiff;
//结果1。相差一年。
select timestampdiff(YEAR,"2019-08-22 15:15:19","2018-12-23 15:15:16") as timestamodiff;
//结果 -1，也是相差一年。
select timestampdiff(YEAR,"2019-08-22 15:15:19","2018-08-22 15:15:16") as timestamodiff; 
//结果0，相差不到一年

select timestampdiff(MONTH,"2018-08-22 15:15:16","2018-07-23 15:15:16") as timestamodiff;
// 结果 0 相差不到一个月
select timestampdiff(MONTH,"2018-08-22 15:15:16","2018-07-01 15:15:16") as timestamodiff;
// 结果 -1 相差一个月
select timestampdiff(MONTH,"2018-08-22 15:15:19","2018-09-23 15:15:16") as timestamodiff; 
// 结果 1 想差一个月

select timestampdiff(DAY,"2018-08-22 15:15:16","2018-08-23 15:15:16") as timestamodiff;
// 结果 1 想差一天
select timestampdiff(DAY,"2018-08-22 15:15:19","2018-08-23 15:15:16") as timestamodiff;
// 结果 0 想差不到一天
select timestampdiff(DAY,"2018-08-23 15:15:19","2018-08-22 15:15:16") as timestamodiff;
// 结果 -1 想差一天

 3.查询24小时内数据(后端可以将日期作为参数)

where time >=(NOW() - interval 24 hour)

 4.根据日期，分组（年，月，日）

//每年

select year（trade_time），SUM(total) AS total FROM `order` GROUP BY YEAR(trade_time)

/每月

select month(trade_time),SUM(total) AS total FROM `order` GROUP BY MONTH(trade_time)

//每周

select week(trade_time),SUM(total) AS total FROM `order` GROUP BY WEEK(trade_time)

//每日

select day(trade_time),SUM(total) AS total FROM `order` GROUP BY DAY(trade_time)

5.近七日，每日分组

//方法1
SELECT 
    DATE_FORMAT( ta.time, '%Y-%m-%d' ) time,
    sum(ta.weight) weight
from 
    table ta
where
    and ta.time >= date(now()) - interval 7 day group by day(ta.time)
ORDER BY time


//方法2

SELECT
	DATE_FORMAT( da.time, '%Y-%m-%d' ) days,
	sum( da.weight ) weight ,
FROM
	(
	SELECT
		table.time,
	FROM
		table
	WHERE
		 DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( table.time ) 
	) AS da 
GROUP BY
	days
order by days