MySQL排序查询

#排序查询

/*
   语法
    select 查询列表                       第三步
    from   表名                           第一步
    （where  筛选条件）                   第二步
    order by 排序列表  asc/desc；         第四步

  
*/
USE myemployees;
SELECT * FROM employees;

#查询员工信息，要求工资从高到底排序
SELECT *
FROM   employees
ORDER BY salary DESC;
SELECT *
FROM   employees
ORDER BY salary; #默认是asc

#案例二：查询部门编号大于等于90的员工信息，按照入职时间的先后进行排序
SELECT * 
FROM employees
WHERE department_id >=90
ORDER BY hiredate ASC;

#案例三：按表达式排序 
#按年薪的高低显示员工的信息和年薪
SELECT *,salary*(1+IFNULL(commission_pct,0))*12 AS 年薪
FROM employees
ORDER BY salary*(1+IFNULL(commission_pct,0))*12 DESC;

#案例四：按别名排序
SELECT *,salary*(1+IFNULL(commission_pct,0))*12 AS 年薪
FROM employees
ORDER BY 年薪 DESC;

#案例五：按函数排序
#按姓名的长度显示员工的姓名和工资 
#length()计算字符串长度
SELECT CONCAT(last_name,first_name) AS 姓名,
        salary,
        LENGTH(last_name)+LENGTH(first_name) AS 长度
FROM    employees
ORDER BY 长度 ASC;

#案例六：按多的字段排序
#查询员工信息先按员工工资排序，再按员工编号排序
SELECT *
FROM   employees
ORDER BY salary ASC ,employee_id DESC;#order by 子句放在查询语句的最后 但limit子句除外

#测试：查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
SELECT CONCAT(last_name,first_name) AS 姓名,
       department_id,
       salary*(1+IFNULL(commission_pct,0))*12 AS 年薪
FROM   employees
ORDER BY 年薪 DESC,last_name  ASC;

#选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT CONCAT(last_name,first_name) AS 姓名,
       salary
FROM   employees
WHERE  salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT  *
FROM    employees
WHERE   email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;