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 ....