记录一次SQL查询任务的使用总结

常用的时间函数

DATE_ADD(date,INTERVAL expr type)【date:合法的日期表达式；expr：时间间隔；type：时间单位】向日期添加指定的时间间隔
NOW（）：yyyy-MM-dd hh:MM:ss
curdate（）：yyyy-MM-dd
CURTIME()：hh:MM:ss
DATE_SUB(date,INTERVAL expr type)【date:合法的日期表达式；expr：时间间隔；type：时间单位】向日期减少指定的时间间隔
date_format(date,format)【date:合法的日期表达式；format:规定日期/时间的输出格式】

分组查询

使用顺序：
select 字段1，字段2 from 表名where [条件表达式] group by字段3 order by 字段4 Having[条件表达式]（聚集函数）limit

（1）Group by满足于我们针对某些字段进行分组，然后在组内对多行数据进行处理（计算行数，计算max，min等等）这样的需求。group by返回的数据是有序的。如果我们对数据进行分组后，要输出（select）其所有（多）行的数据，此时是无法实现的。根据版本的不同（5.6版本及之前是取每组的第一条），可能会输出第一行数据，可能会保错；

（2）只能先取得分组结果再排序，不能先对数据进行排序在进行分组，可先过滤数据，若要先排序只能重新select获取结果排成一张表再分组；即：
select field_a , field_b from（select * from tableA order by field_b）group by field_a;

（3）where和having关键字的区别：having后面可以跟聚合函数，where不行；

（4）想要查询表中某个字段的各种值，用 MAX(expr)、MIN(expr)、SUM(expr)、AVG(expr)，
例子：取某个字段的最大值的相关记录：
①select field_a , field_b from（select * from tableA order by field_b）group by field_a;
（该方法在mysql 5.6是没问题的，在5.7版本中会把order by语句优化掉）；

 ②`select substring_index(group_contact(field_b ORDER BY sort DESC), ’ , ’ , 1) , field_a  from tableA group by field_a;`

③SELECT a.* from tableA a left JOIN sort_group b on a.gp = b.gp and a.sort < b.sort where b.sort is null
（因为使用表连接，效率极低）

（5）查询优化：limit 1；如果查询条件的字段没有建立索引，则会进行全表扫描，再确定返回结果或者需要的结果只有一条的话使用limit 1可以提升效率，但如果建立了索引就没有必要了，因为两者效率没什么区别了；

（6）使用了分组查询，但是查询的字段没有进行分组是错误的，在Oracle中会报错，因为group by后的分组存在多个数据，数据库没法判定取哪一条记录，这是mysql的设计，当group by 字段和select 多字段是唯一的一个分组时，这样能提高效率。但是如果不能唯一确定的话，mysql会随机选一个值。这是要注意的。
想用查询分组后的非分组字段，可以采用left join的方式，相当于两个表匹配的方式。

Select field_b from tableA A left join (select field_a  from  tableA  group by field_a )  B on A.field_a = B.field_a

（7）count（distinc 字段A）和（group by字段A）浅析：
①distinct就是将所有内容加载到内存中，大致可以理解为一个hash结构，key自然就是字段A的所有值，因为是hash结构，运算速度自然比较快，最后计算hash中有多少key就是最终的结果，但是在海量数据中，内存消耗是比较大的，可能会out of memory；
②group by是将字段A先排序，与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作；
因此，count(distinct)吃内存，查询快；group by空间复杂度小，在时间复杂度允许的情况下，可以发挥他的空间复杂度优势。

（8）按日期对表进行分组查询：
1）按天统计：
select DATE_FORMAT(add_time,’%Y%m%d’) days,count(field_a) counta from tableA group by days;
2）按周统计：
select DATE_FORMAT(add_time,’%Y%u’) weeks,count(field_a) counta from tableA group by weeks;
3）按月统计:
select DATE_FORMAT(add_time,’%Y%m’) months,count(field_a) counta from tableA group by months;

（9）mysql对于查询记录中字段为null的值进行默认处理：IFNULL（field_a,default）;

（10）Sql获取随机数：SELECT FLOOR(RAND()*100)

（11）求两表之间数据的差集
①…not in…;(易理解，效率低)
②left…join …where … is null；
③select * from A where (select count(1) as num from B where A.id = B.id) = 0;（逻辑复杂，效率最高）

交集：内连接
（12）order by 与limit混用会出现排序不稳定，分页数据错误，当排序条件的值一致时，会随机返回排序的数据，可以增加多一个排序条件；

（13）使用mysql内置关键字报错：
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:

（14）mysql嵌套子查询效率确实比较低，可以将其优化成连接查询：MySQL有时优化子查询很差，特别是在WHERE从句中的IN()子查询，因为会将sql优化成了exists子句 ；

（15）in和exists语句的差别：
in把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。（只执行一次）
exists对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；（执行外表的长度的次数）
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。
例子：A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

（16）not in 和 not exists的区别
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

（17）表连接的笛卡尔积问题：
内连接查询：on条件是A表或者B表的唯一字段，则结果集是两表的交集，不是笛卡尔积。否则则结果集是两表匹配到的结果集的笛卡尔积(局部笛卡尔积) 。
左连接查询：on条件是唯一字段，则结果集是左表记录的数量。否则局部笛卡尔积；
（如果A表有m条记录，m1条符合on条件，B表有n条记录，有n1条符合on条件，则结果集是 (m-m1) + m1*n1）

（18）mysql中的列属性尽量用NOT NULL：
1、NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果集。
2、使用 concat 函数拼接时，首先要对各个字段进行非 NULL 判断，否则只要任何一个字段为空都会造成拼接的结果为 NULL。
3、当用 count 函数进行统计时，NULL 列不会计入统计。
4、查询空行数据，用 is NULL。
5、NULL 列需要更多的存储空间，一般需要一个额外的字节作为判断是否为 NULL 的标志位。