排序查询

引入
select * from employees;

排序语法：
     select 查询列表
     from  表
     where  筛选条件
     order by 排序列表 [asc | desc]
     
特点：
    1. asc代表的是升序，desc代表的是降序
       如果不写，默认是升序
    2. order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
    3. order by 子句一般放在查询语句的最后，limit子句除外

案例1： 查询员工信息，要求工资从高到低排序

SELECT 
     *
FROM
     employees
ORDER BY
     salary
DESC;

从低到高

SELECT 
     *
FROM
     employees
ORDER BY
     salary
ASC;

不写，默认ASC

SELECT 
     *
FROM
     employees
ORDER BY
     salary;

案例2： 查询部门标号>= 90的员工信息，按照入职时间先后进行排序

select
      *
from
     employees
where
     `department_id` >= 90
order by
        `hiredate`
asc;

案例3， 按年薪的高低显示员工的信息和年薪【按表达式排序】

select
     *,
     salary*12*(1+ifnull(`commission_pct`,0)) as 年薪
from 
    employees
order by
    salary*12*(1+IFNULL(`commission_pct`,0))
desc;

案例4， 按年薪的高低显示员工的信息和年薪【按别名排序】

SELECT
     *,
     salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM 
    employees
ORDER BY
    年薪
DESC;

案例5，按姓名的长度显示员工的姓名和工资【按函数排序】

select
     LENGTH(last_name),
     last_name,
     salary
from
     employees
order by
     length(last_name) DEsc;

案例6， 查询员工信息，要求先按照工资排序，再按照员工编号排序【按多个字段排序】

select
     *
from
    employees
order by
    salary Asc, `employee_id` desc;

作业

1. 查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序

select 
      last_name,
      `department_id`,
      salary*12*(1+ifnull(`commission_pct`,0)) as 年薪
FROM
     employees
order by
     年薪 desc, last_name asc;

2. 选择工资不在8000 到17000 的员工的姓名和工资，按工资降序

select 
     last_name,
     salary
from 
     employees
where
     salary not between 8000 and 17000
order by
     salary desc;

3. 查询邮箱中包含e员工信息，并先按邮箱的字数降序，再按部门号升序

select 
    *,
    LENGTH(`email`)
from
   employees
where
   `email` like '%e%'
order by
    length(`email`) desc, `department_id` asc;