MYSQL常用高级查询

使用mysql查询附近的人

-- 其中$lng是你的经度，#$lat是你的纬度
-- 表user_location必须有记录lng,lat
SELECT lng,lat,
(POWER(MOD(ABS(lng - $lng),360),2) + POWER(ABS(lat - $lat),2)) AS distance
FROM `user_location` ORDER BY distance LIMIT 100

使用时间进行数据统计及求和

-- 统计某天用户注册量
select date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
to_days(create_time) = to_days(str_to_date('2017/12/23','%Y/%m/%d'))

-- 统计某月用户注册量
select  date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
date_format(create_time,'%Y-%m') = date_format(str_to_date('2017-12','%Y-%m'))

-- 统计昨天的用户注册量
select date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
date_format(create_time,'%Y-%m-%d') = 
date_format(date_add(now(),interval -1 day),'%Y-%m-%d')

-- 统计7天内的注册用户数据
select * from ui_user where datediff(NOW(),create_time)<7;

-- 统计近3天每天用户注册数量
select date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
date_format(create_time,'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')
union all
select date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
date_format(create_time,'%Y-%m-%d') = 
date_format(date_add(now(),interval -1 day),'%Y-%m-%d')
union all
select date_format(create_time,'%Y-%m-%d') as create_time,
ifnull(count(*),0) as num from ui_user where 
date_format(create_time,'%Y-%m-%d') = 
date_format(date_add(now(),interval -2 day),'%Y-%m-%d')

其他函数

-- 查询username长度小于3的所有用户
select * from ui_user where length(username) < 3