1.統(tǒng)計查詢（查詢數(shù)量和）
select sum(slow_count) as count from dd_charging
select sum(slow_count) from dd_charging
//按月統(tǒng)計 posttime為時間戳
SELECT FROM_UNIXTIME(posttime ,'%Y-%m') months,count(id) FROM v2_sms_report GROUP BY months;
2.查詢不在數(shù)據(jù)內(nèi)的數(shù)據(jù)
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.數(shù)據(jù)庫字段去重
select (distinct name) as n_name from table where ....