高级查询 · 万能Web开发文档笔记

# 高级查询 - 更新字段首字母 ``` <pre class="calibre14">``` UPDATE city SET pinyin=ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(`name` USING gbk),1)),16,10), 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7, 0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6, 0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1), 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P', 'Q','R','S','T','W','X','Y','Z'); ``` ``` - 常用查询 ``` <pre class="calibre14">``` select * from list where to_days(FROM_UNIXTIME(createtime))=to_days(now()) 今天 where to_days(now())-to_days(FROM_UNIXTIME(createtime))<1 今天 where to_days(now())-to_days(FROM_UNIXTIME(createtime))=1 昨天 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(FROM_UNIXTIME(createtime))近七天含今天 where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now()) 本周 where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now())-1 上周 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) < date(FROM_UNIXTIME(createtime))近30天含今天 where DATE_FORMAT(FROM_UNIXTIME(createtime), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') 查询本月 where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y%m')) =1 上月 where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(NOW()) 本季度 where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) 上季度 where YEAR(FROM_UNIXTIME(createtime))=YEAR(NOW()) 今年 where YEAR(FROM_UNIXTIME(createtime))=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) 去年 SELECT @rank := @rank + 1 AS rank,t.* FROM (SELECT @rank := 0) r, user AS t ORDER BY t.score DESC; 相同分数按照id小的在前,排名不重复 SELECT rank,score,id FROM ( SELECT USER .*, @c := IF ( @p = score, @c, @r ) AS rank, @p := score, @r := @r + 1 FROM USER, ( SELECT @p := NULL, @r := 1, @c := 0 ) r ORDER BY score DESC ) c; 相同排名重复后去掉后面 UPDATE user INNER JOIN (SELECT @rank := @rank + 1 AS rank,t.id FROM (SELECT @rank := 0) r, user AS t ORDER BY t.score DESC) t2 ON t2.id=user.id SET user.rank=t2.rank 更新表自身排名,更新前rank都是0,没有重复 UPDATE user INNER JOIN (SELECT rank,id FROM ( SELECT user.*, @c := IF ( @p = score, @c, @r ) AS rank, @p := score, @r := @r + 1 FROM user, ( SELECT @p := NULL, @r := 1, @c := 0 ) r ORDER BY score DESC ) c) t2 ON t2.id=user.id SET user.ranking=t2.rank 有重复同上 UPDATE userSET rank= rank+1 WHERE id=5 自动加一 SELECT * FROM user ORDER BY RAND() LIMIT 5; 随机数据性能低下1000以内 SELECT * FROM user AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM user)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 2;连续的id高效率 SELECT * FROM user WHERE id >= ((SELECT MAX(id) FROM user)-(SELECT MIN(id) FROM user)) * RAND() + (SELECT MIN(id) FROM user) limit 2; 随机id不连续 select * from user where id<7 order by id desc limit 1; 上一条6 select * from user where id>7 limit 1; 下一条 ``` ``` - php 判断时间 ``` <pre class="calibre16">``` 今天 $start=mktime(0,0,0,date('m'),date('d'),date('Y')); $end=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1; 昨天 $start=mktime(0,0,0,date('m'),date('d')-1,date('Y')); $end=mktime(0,0,0,date('m'),date('d'),date('Y'))-1; 近七天 $start=strtotime(date("Y-m-d",strtotime("-6 day"))); $end=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1; 近30天 $start=strtotime(date("Y-m-d",strtotime("-29 day"))); $end=mktime(0,0,0,date('m'),date('d')+1,date('Y'))-1; 本周 $start=mktime(0,0,0,date('m'),date('d')-date('w')+1,date('Y')); $end=mktime(23,59,59,date('m'),date('d')-date('w')+7,date('Y')); 上周 $start=mktime(0,0,0,date('m'),date('d')-date('w')+1-7,date('Y')); $end=mktime(23,59,59,date('m'),date('d')-date('w')+7-7,date('Y')); 本月 $start=mktime(0,0,0,date('m'),1,date('Y')); $end=mktime(23,59,59,date('m'),date('t'),date('Y')); 上月 $start=mktime(0, 0 , 0,date("m")-1,1,date("Y")); $end=mktime(23,59,59,date("m") ,0,date("Y")); 本季度 $quarter = empty($param) ? ceil((date('n'))/3) : $param;//获取当前季度 $start= mktime(0, 0, 0,$quarter*3-2,1,date('Y')); $end=mktime(23, 59, 59,$quarter*3,date('t',mktime(0, 0 , 0,$quarter*3,1,date("Y"))),date('Y')); 上季度 ceil((date('n'))/3)-1 某季度 $param=1 今年 $start=strtotime(date('Y-01-01 00:00:00')); $end=strtotime(date('Y-12-31 23:59:59')); 去年 $start=strtotime(date('Y-01-01',strtotime('-1 year'))); $end=strtotime(date('Y-12-31 23:59:59',strtotime('-1 year'))); 一年后 strtotime(date("Y-m-d",strtotime("+1 year"))); 2019-7-10 00:00:00 ``` ```