第三章 MySql-DQL

第三章 MySql-DQL

---

排序查询(升序)

select 
      hiredate 
from 
      employees 
order by hiredate asc ;

排序查询(降序)

select 
        hiredate 
from    employees 
order by hiredate desc ;

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

select 
         *
from 
        employees  salary order by salary desc;

查询部门编号 >= 90的员工信息，按入职时间的先后进行排序

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

查询 按年薪的高低显示员工信息 和 年薪 （带表达式的操作）

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

查询 按年薪的高低显示员工信息 和 年薪 （按别名排序）

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

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

select 
        last_name,salary,length(last_name) as "姓名长度"
from  
        employees
order by length(last_name) desc;

查询员工信息，要求先按工资升序，再按员工编号降序序

注意：如果你排序的这两列其中一列中有相同的数据的话
会导致另外一列排序的大小有大的在前面，如下图：
select  
        salary,employee_id 
from 
        employees 
order by salary asc ,employee_id desc;

下面是练习题

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

select
        last_name as "姓名",
        department_id "部门号",
        salary*12*(1+ifnull(commission_pct,0)) as "年薪"
 from
        employees
 order by 年薪 desc,last_name asc;

• 2.查询工资不在8000到17000的员工的姓名和工资，按工资降序排列；

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

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

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