Mysql一些变态的sql写法

随机生成mac
select CONCAT(hex(FLOOR(17+(RAND()*238))),':',hex(FLOOR(17+(RAND()*238))),':',hex(FLOOR(17+(RAND()*238))),':',hex(FLOOR(17+(RAND()*238))),':',hex(FLOOR(17+(RAND()*238))),':',hex(FLOOR(17+(RAND()*238))));
随机生成gwid
select CONCAT(hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))),hex(FLOOR(0+(RAND()*16))))
时间戳
SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' )
select UNIX_TIMESTAMP()

SELECT gwid FROM `ik_heart_router_20141115` where `timestamp`>UNIX_TIMESTAMP()-600 and `timestamp`<UNIX_TIMESTAMP() group by gwid

取url中from=(.*)& 聚组、排序
SELECT left(mid(http_url, LOCATE('from=', http_url),300),LOCATE('&', mid(http_url, LOCATE('from=', http_url),300))) f,count(DISTINCT `host`) c ,http_url
FROM `collect_app_20150427` 
where http_host='m.baidu.com'
group by f 
order by c desc 
limit 10