mysql常用sql

按照月份统计入职人数

select
    date_format( ss_staff_job.begindate, '%m' ) month,
    count(*)
from
    ss_staff_job
where
    date_format( ss_staff_job.begindate, '%Y' )= 2017
group by
    date_format( ss_staff_job.begindate, '%m' )

按照年龄区间统计人数

explain select
    sum( case when age >= 0 and age <= 25 then 1 else 0 end ) as '25以下',
    sum( case when age >= 26 and age <= 30 then 1 else 0 end ) as '26-30',
    sum( case when age >= 31 and age <= 45 then 1 else 0 end ) as '31-45',
    sum( case when age >= 46 and age <= 55 then 1 else 0 end ) as '46-55',
    sum( case when age >= 56 then 1 else 0 end ) as '56以上'
from
    (
        select
            date_format( from_days( to_days( now())- to_days( birthdate )), '%Y' )+ 0 as age
        from
            ss_staff inner join ss_staff_orgrel on
            ss_staff.id = ss_staff_orgrel.staff_id inner join ss_staff_job on
            ss_staff_orgrel.id = ss_staff_job.orgrel_id
        where
            ss_staff.birthdate is not null
    ) tmp

date_format函数
select date_format('2018-01-31','%Y-%m-%d-%M-%y-%D') from dual
结果：2018-01-31-January-18-31st
根据出生日期计算年龄:

select date_format( from_days( to_days( now())- to_days( birthdate )), '%Y' )+ 0 as age from dual

from_days(to_days(now())-to_days('2014-01-21'))得到的是两个日期相差的日期 0004-02-05

查看索引
show index from ss_staff_edu
为表添加索引
create index idx_cate_browse_buy on course(category_id,browse_times,buy_times);
下面是操作索引的一些语法：
http://www.jb51.net/article/73372.htm

对null排序

image.png 现在null在前面 select * from testnull order by coalesce(id,999999999);