现在的位置: 首页 > 软件开发 > 正文

MySQL SQL语句随笔

2010年11月10日 软件开发 ⁄ 共 1206字 暂无评论 ⁄ 被围观 1,190+

MySQL SQL语句随笔, 主要是以前用的比较少但是又很有用的一些,例如: UNIX_TIMESTAMP,SUM,DISTINCT,SUBSTRING,DATE_ADD,ON DUPLICATE KEY,CONVERT_TZ等。

"SELECT DISTINCT SUBSTRING(timestamp,1,10) start, DATE_ADD(SUBSTRING(timestamp,1,10), INTERVAL 1 DAY) end FROM `$hits` WHERE timestamp < FROM_UNIXTIME(\'$older_than\') ORDER BY `id`";

"INSERT INTO `$archive_sites` (`range_id`,`site_id`,`views`,visits) VALUES $values ON DUPLICATE KEY UPDATE views=views+VALUES(views), visits=visits+VALUES(visits)";

"SELECT url_id,url_site_id AS site_id,COUNT(*) views,COUNT(DISTINCT ip_int1,ip_int2) visits  FROM $hits h WHERE timestamp >= \'$start\' AND timestamp < \'$end\' GROUP BY `url_id`"

"REPLACE INTO `$sites` (`id`,`type`,`name`) VALUES ($new_sid,$type,$name)";

$ts = $db_support_tz && $timezone != \'system\' ? "CONVERT_TZ(`timestamp`,\'system\',\'$timezone\')" : "timestamp";

 "SELECT COUNT(*) FROM $hits WHERE $for_site AND $not_filtered AND UNIX_TIMESTAMP($ts) > $date1 ";

"SELECT hits.id, url_site_id site_id, HEX(ip_int1) ip_int1, HEX(ip_int2) ip_int2 FROM  `$hits` AS hits ORDER BY hits.id DESC LIMIT 1, 20";

"SELECT UNIX_TIMESTAMP(refs.add_time) add_time,refs.url ,SUM(`c`) `refcount` FROM ($select1 UNION ALL $select2) `u`, `$refs` refs WHERE refs.id = u.referer_id AND refs.url != \'\' $and_exclude_internal AND refs.search_engine_id IS NULL GROUP BY `referer_id` 

给我留言

您必须 [ 登录 ] 才能发表留言!

×
#