1987-04-19 -》1987
select right('1987-04-19',4)
###（1）获取员工的入职年份
select ename,hiredate,left(hiredate,4) from emp;

###（2）获取员工的入职工龄

select ename,hiredate,2021 - left(hiredate,4) from emp;

select hiredate,(2021 - left(hiredate,4)) as y from emp;
select
hiredate,(2021 - left(hiredate,4)) as y,
case
when(2021 - left(hiredate,4)) >40 then "***"
when(2021 - left(hiredate,4)) >39 then "**"
else "*"
end as star
from emp;
##（1）统计各个部门的人数
部门的id 人数 平均工资
10 6 600
20 3 300
30 4 400
select
deptno,count(*),avg(sal)
from
emp
group by
deptno;
##（2）统计各个部门的平均工资
部门的id 平均工资
10 600
20 300
30 400
部门的id 岗位 人数
1 java开发 10
1 前端开发 6
2 出纳 2
2 会计 4

select
deptno,job,count(*)
from
emp
group by
deptno,job;

##having
需求：统计平均工资超过2000的部门。
（1）算出各个部门的平均工资。
（2）过滤，只留下超过2000的。

select
deptno,avg(sal) as sal_avg
from
emp
group by
deptno
having
sal_avg > 2000;

需求：统计30部门，平均工资超过1000的岗位。
select
job,avg(sal) as sal_avg
from
emp
where
deptno = 30
group by
job
having
sal_avg > 1000;

##limit
##语法 limit, start len;从start开始，查len条
##(1)查询前5条数据
select * from emp limit 0 5;
select * from emp limit 5;

##(2)查询第2条开始，查询5条
select * from emp limit 2 5;
select
deptno,avg(sal)as sal_avg
from
emp
where
mgr is not null
group by
deptno
order by
sal_avg desc
limit 2;