mysql（三） order by 排序练习+实例

select 
    ename,sal
from
    emp
order by
    sal; // 默认是升序！！！

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JonES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

指定降序：
select 
    ename,sal
from
    emp
order by
    sal desc;

±-------±--------+
| ename | sal |
±-------±--------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JonES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
±-------±--------+

select 
    ename,sal
from
    emp
order by
    sal asc;

±-------±--------+
| ename | sal |
±-------±--------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JonES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
±-------±--------+

查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，
再按照名字升序排列。
select 
    ename,sal
from
    emp
order by
    sal asc, ename asc; // sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JonES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

select ename,sal from emp order by 2; // 2表示第二列。第二列是sal
按照查询结果的第2列sal排序。

了解一下，不建议在开发中这样写，因为不健壮。
因为列的顺序很容易发生改变，列顺序修改之后，2就废了。

找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。
select 
    ename,sal
from
    emp
where
    sal between 1250 and 3000
order by
    sal desc;

±-------±--------+
| ename | sal |
±-------±--------+
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JonES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
±-------±--------+

关键字顺序不能变：
    select
        ...
    from
        ...
    where
        ...
    order by
        ...

        第一步：from
        第二步：where
        第三步：select
        第四步：order by（排序总是在最后执行！）