Mysql 常用sql语句汇总

引用

http://blog.csdn.net/tbkken/article/details/8173020

按年汇总，统计：
select sum(mymoney) as totalmoney, count(*) as sheets from table group by date_format(col, '%Y');
按月汇总，统计： 
select sum(mymoney) as totalmoney, count(*) as sheets from table group by date_format(col, '%Y-%m');
按季度汇总，统计： 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3)); 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
按小时： 
select sum(mymoney) as totalmoney,count(*) as sheets from table group by date_format(col, '%Y-%m-%d %H ');
查询 本年度的数据:
SELECT * FROM table WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())
查询数据附带季度数:
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM table;
查询 本季度的数据:
SELECT * FROM table WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());
本月统计:
select * from table where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())
本周统计:
select * from table where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())
N天内记录:
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N 


--给分期表创建唯一约束
alter table A add unique index unique_sltId_loanNo (SLT_ACCOUNT_ID, LOAN_PERIOD_NO);

--创建普通索引
alter table A add index  FK_FK_INSTALLMENT_INFO_SLT_ACCOUNT_ID (SLT_ACCOUNT_ID); 

alter table A add index  IDX (SLT_ACCOUNT_ID,REPAY_STATUS); 


<!-- 查询30以内的下一期记录-->
<select id="sqlId" resultMap="BaseResultMap" parameterType="java.lang.Long">
select * from tableName
where sid = 88888888
and status=1 
and DATE_FORMAT(date_add(now(),interval 1 month),'%Y-%m-%d') >= DATE_FORMAT(duDate,'%Y-%m-%d')  
and now() < duDate
</select>