常用SQL语句总结

前段时间做网站后台,用到了一些SQL,貌似还算常用,贴出来吧,以后省的自己重新写。

1.日期相加及格式化

SELECT num from cihionlinenum where timehour = DATE_FORMAT(DATE_ADD("2012-11-05 14:00:00",INTERVAL 3600 SECOND),'%Y-%m-%d %H')
2.数量统计(去重复),日期转换为秒及取绝对值
SELECT COUNT(DISTINCT hino) as onlinenum from cihipresencerecord where type = 1 and ABS(TIME_TO_SEC(time)-TIME_TO_SEC("2012-10-25 10:00:00"))<600
3.分组查询(支持排序)
SELECT * from (SELECT * FROM cihipresencerecord where type = 0 and time BETWEEN "2012-10-31 17:00:00" AND "2012-10-31 17:20:00" ORDER BY time desc) as result GROUP BY hino
4.日期相加及比较,between and包括两端
SELECT COUNT(DISTINCT hino) as onlinenum from cihipresencerecord where type = 1 and time BETWEEN "2012-10-23 11:11:11" AND DATE_ADD("2012-10-23 11:11:11",INTERVAL 600000000 SECOND)
5.日期相减,精确到.000000
select ABS(TIMEDIFF(time,"2012-10-25 15:45:01")) as subnum from cihipresencerecord where id = 38
6.日期时间戳转时间
select FROM_UNIXTIME('001353578394','%Y-%m-%d %H:%m:%s');
7.日期格式化及分组连接,CAST使用(int->char)
SELECT DATE_FORMAT(tjdate,'%Y-%m-%d') as tjdate,GROUP_CONCAT(CAST(type AS CHAR)) as typeStr,GROUP_CONCAT(CAST(xxl AS CHAR)) as xxlStr from cihirjxxl where tjdate BETWEEN DATE_FORMAT('2012-12-01','%Y%m%d') AND DATE_FORMAT('2012-12-04','%Y%m%d') GROUP BY tjdate
8.日期格式(天)及分组
select DATE(time) as tjdate,ip,COUNT(DISTINCT hino) as onlinenum from cihipresencerecord where ip is not null and DATE(time) BETWEEN '2012-10-23' and '2012-11-23' and type=1 GROUP BY ip
9.按年月日统计
SELECT DATE(time) as tjdate,COUNT(DISTINCT hino) as onlinenum,COUNT(DISTINCT ip) as ipnum from cihipresencerecord where ip is not NULL and type=1 and time BETWEEN '2012-10-23' and '2012-11-23' GROUP BY tjdate
10.按年月统计
select EXTRACT(YEAR_MONTH FROM jfrq)as tjyf,SUM(dtsjlj) as zxsc,COUNT(DISTINCT username) as zxyhs from ofzxjfb where EXTRACT(YEAR_MONTH FROM jfrq) BETWEEN '201205' AND '201211' GROUP BY tjyf
11.SQL计算
select DATE_FORMAT(tjdate,'%Y-%m-%d') as tjdate,SUM(djl) as djltotal,COUNT(hinum) as usertotal,SUM(djl)/count(hinum) as average from cihirjdjl where type=1 and tjdate BETWEEN DATE_FORMAT('2012-12-03','%Y%m%d') and DATE_FORMAT('2012-12-04','%Y%m%d') GROUP BY tjdate

12.FIND_IN_SET(此处还是换成IN吧)

"select t2.stateid,t2.code,t2.statedesc,t2.isdefine,t1.yhfzid from ofuserstateconfig t1 right join ofuserstate t2 on t1.stateid=t2.stateid where FIND_IN_SET(t1.yhfzid,'"+param+"')";

13.update MAX+1

update cihimedal set version = ((SELECT max_value from (select MAX(version) as max_value from cihimedal) as temp)+1)

14.存在则更新,不存在则插入

INSERT table (auto_id, auto_name) values (1, ‘yourname’) ON DUPLICATE KEY UPDATE auto_name=’yourname’

下面是杂七杂八的总结,哎,没条理性,呵呵。


1.springMVC报错信息被spring吞噬掉,解决方法:调整log4j日志输出级别为log4j.logger.org.springframework.web=debug


2.MySQL求两日期相差秒数:
   a.  select ABS(TIMEDIFF(time,"2012-10-25 15:45:00")) as subnum from cihipresencerecord where id = 38 
   b.  select abs(TIME_TO_SEC(time)- TIME_TO_SEC("2012-10-25 15:45:00"))  as subnum from cihipresencerecord  where id = 38


3.JQuery获取select值
  jQuery("#select1  option:selected").text();


4. System.nanoTime()获取纳秒级别的时间;

    System.currentTimeMillis()获取毫秒级别的时间

5.MySQL 自4.1版以后开始支持INSERT … ON DUPLICATE KEY UPDATE语法,使得原本需要执行3条SQL语句(SELECT,INSERT,UPDATE),缩减为1条语句即可完成。

INSERT ... ON DUPLICATE KEY UPDATE,当插入的记录会引发主键冲突或者违反唯一约束时,则使用UPDATE更新旧的记录,否则插入新记录。

6.post请求回显乱码问题,貌似应该设置request的编码,当然乱码还有很多,以后再总结。


7.根据起始日期获取该阶段所有日期 
/**
* 根据起始日期获取该阶段所有日期 
*/
public List<String> getDateByDay(String startDate,String endDate){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
List<String> dates = new ArrayList<String>();
try {
long start = sdf.parse(startDate).getTime();
long end = sdf.parse(endDate).getTime();
for(long i=start; i<end+(24*3600*1000); i=i+(24*3600*1000)){
Date date = new Date();
date.setTime(i);
dates.add(sdf.format(date));
}
} catch (ParseException e) {
System.out.println("日期解析出错---"+e.getMessage());
}
return dates;
}

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值