基础查询
set names utf8; 设置编码 select * from biao 基本查询 select distinct id from biao 结果字段不重复 select * from biao order by id desc ,time asc; 升序降序 select * from biao group by sex having; 分组筛选 select * from biao limit 4 返回4条 select * from biao 4,3 返回3条,从第5条记录开始 select a,b,c from A inner join B on A.id = B.id; 内连接 select a,b,c from A,B where A.id=B.id;内连接 select * from A left join B on A.id=B.id; 左连接 select * from A right join B on A.id=B.id;右连接 select id from Table where id2 in(select id3 from Table2) 子查询 select id from Table where find_in_set(type,"ssq,sd,pls") 查询分类 select id as ID from A as a 别名 select * from A union all select * from B 合并结果集 select * from A union select * from B 去重复 SELECT id,title FROM article WHERE id<$id ORDER BY id desc LIMIT 1 上一篇 SELECT id,title FROM article WHERE id>$id ORDER BY id ASC LIMIT 1 下一篇 select count(distinct openid) as total from TB 统计记录去重复 select A.* from caipiao A where 1>(select count(*) from caipiao where types=A.types and qishu>A.qishu ) order by A.types desc,A.qishu desc 分类第一条 select names,headimgurl,id from ims_yoby_xy_fans id where id in($ids) order by field(id,3,1) 查询结果不变
高级查询
查询今天select * from T where to_days(FROM_UNIXTIME(createtime))=to_days(now())
select * from T where to_days(now())-to_days(FROM_UNIXTIME(createtime))<1
昨天select * from T where to_days(now())-to_days(FROM_UNIXTIME(createtime))=1
近七天含今天select * from T where DATE_SUB(CURDATE(), INTERVAL 7 DAY) < date(FROM_UNIXTIME(createtime))
本周select * from T where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now())
上周select * from T select * from T where YEARWEEK(date_format(FROM_UNIXTIME(createtime),'%Y-%m-%d')) = YEARWEEK(now())-1
经30天含今天select * from T where DATE_SUB(CURDATE(), INTERVAL 30 DAY) < date(FROM_UNIXTIME(createtime))
本月select * from T where DATE_FORMAT(FROM_UNIXTIME(createtime), '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
上月select * from T where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y%m')) =1
本季度select * from T where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(NOW())
上季度select * from T where QUARTER(FROM_UNIXTIME(createtime))=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER))
今年select * from T where YEAR(FROM_UNIXTIME(createtime))=YEAR(NOW())
去年select * from T where YEAR(FROM_UNIXTIME(createtime))=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
排名,相同分数id小的在前面,不重复排名SELECT @rank := @rank + 1 AS rank,t.* FROM (SELECT @rank := 0) r, user AS t ORDER BY t.score DESC;
排名同时更新表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
排名重复,相同排名后后一名去掉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,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;
随机数据高效率SELECT * FROM user WHERE id >= ((SELECT MAX(id) FROM user)-(SELECT MIN(id) FROM user)) * RAND() + (SELECT MIN(id) FROM user) limit 2;
上一条,下一条select * from user where id<7 order by id desc limit 1;
select * from user where id>7 limit 1;
更新字段首字母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');
PHP时间判断
今天$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
作者:Yoby 创建时间:2020-07-22 23:15
更新时间:2024-12-05 13:26
更新时间:2024-12-05 13:26