• 基础查询

    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
上一篇：
下一篇：