mysql语句（自用）

1.统计查询（查询数量和）

select  sum(slow_count) as count  from dd_charging
select  sum(slow_count)  from dd_charging
//按月统计  posttime为时间戳
SELECT FROM_UNIXTIME(posttime ,'%Y-%m') months,count(id) FROM v2_sms_report GROUP BY months;

2.查询不在数据内的数据

select * from dd_charging where id not in (1,3,5)

3.组合查询

SELECT `id`,count(*) as cnt,substr( longitude - 1796,0,6) as pjing,substr(latitude - 1796,0,5) as pwei,avg(longitude - 1796) as pjing2,avg(latitude - 1796) as pwei2 FROM pile where type=1  GROUP BY pjing,pwei

4.查询账户信息

select user_id,money,FROM_UNIXTIME(create_time),pay_method FROM dd_uu WHERE user_id in(...) and create_time>1481212800  ORDER BY user_id,create_time desc

5.替换字段中的某个值

update t_uu set headico=REPLACE (headico,'public','Public') where xxx

6.替换手机号中间四位

update `laozhuji` set tel=INSERT(tel,4,4,'****') WHERE id=1

7.mysql 取每个分类下的N条记录

第一种方式
SELECT 
    *
FROM
    tablename AS a
WHERE
    (SELECT 
            COUNT(*)
        FROM
            tablename AS b
        WHERE
            b.分类id = a.分类id AND b.id >= a.id) <= 5
ORDER BY a.分类id ASC , a.id DESC
 
第二种方式
SELECT 
    *
FROM
    tablename AS a
WHERE
    (SELECT 
            COUNT(*)
        FROM
            tablename AS b
        WHERE
            b.type = a.type AND b.id >= a.id) <= 5
ORDER BY a.type ASC , a.id DESC

8.数据库字段去重

select (distinct name) as n_name from table where ....

最后编辑于：2018.12.24 14:08:34

mysql语句（自用）

推荐阅读更多精彩内容