sql替换
update `wp_posts` set `post_content`=replace(`post_content`,’http://www.shanmao.me/’,’/’);//把wp_posts 表里面的post_content 字段里面的所有网志替换成“/”
update smf_members set signature=replace(signature,’:’,’:’)
update smf_members set signature=replace(signature,’.’,’.’);
update smf_messages set body=replace(body,’:’,’:’);
update smf_messages set body=replace(body,’.’,’.’);
sql获取近三天的数据
SELECT * FROM uchome_blog WHERE dateline>UNIX_TIMESTAMP()-86400*3 ORDER BY replynum DESC
sql多表匹配查询
SELECT p.author_id, author_ip, p.message_subject, p.message_text, t.user_id, u.username AS fa
FROM `phpbb_privmsgs` AS p, phpbb_privmsgs_to AS t, phpbb_users AS u
WHERE p.msg_id = t.msg_id
AND p.author_id = u.user_id
AND p.author_id != t.user_id
AND u.username = ‘shanmao’
ORDER BY p.message_time ASC
LIMIT 3000
sql转换unixt时间戳，获取条件内的数据，
下面是获取3月20号到3月23号的数据
SELECT *
FROM `phpbb_posts` AS p
LEFT JOIN phpbb_topics AS t ON t.topic_id = p.topic_id
WHERE FROM_UNIXTIME( p.post_time, ‘%m%d’ )
BETWEEN 320
AND 323
order by p.post_id asc
LIMIT 0 , 100
sql获取某一时段的平均值
下面是获取条件内第18小时的时候的数据
SELECT AVG( l.OnlineCountCharacter ) AS onlineag
FROM `userloginlog` AS l, mj_user AS u
WHERE l.uid = u.cityos_user_id
AND l.operation
IN ( 1, 2, 3 )
AND FROM_UNIXTIME( l.time, ‘%H’ ) =18
当数据库中的时间是数字而不是时间戳的时候获取每天登陆
SQLDIV语句（整除）
SELECT `create_datetime`
DIV 1000000 AS `day` , COUNT( * ) AS `sum`
FROM `mj_user_log`
WHERE log_type = ‘logon’
GROUP BY `day`
LIMIT 3000