13 排序（升序、降序）

13 排序（升序、降序）

    

    按照工资升序，找出员工名和薪资

        select empno,ename,sal,job,comm from emp order by sal asc; // 默认升序排列  asc可以不写 desc降序

            +-------+--------+---------+-----------+---------+

            | empno | ename  | sal     | job       | comm    |

            +-------+--------+---------+-----------+---------+

            |  7369 | SMITH  |  800.00 | CLERK     |    NULL |

            |  7900 | JAMES  |  950.00 | CLERK     |    NULL |

            |  7876 | ADAMS  | 1100.00 | CLERK     |    NULL |

            |  7521 | WARD   | 1250.00 | SALESMAN  |  500.00 |

            |  7654 | MARTIN | 1250.00 | SALESMAN  | 1400.00 |

            |  7934 | MILLER | 1300.00 | CLERK     |    NULL |

            |  7844 | TURNER | 1500.00 | SALESMAN  |    0.00 |

            |  7499 | ALLEN  | 1600.00 | SALESMAN  |  300.00 |

            |  7782 | CLARK  | 2450.00 | MANAGER   |    NULL |

            |  7698 | BLAKE  | 2850.00 | MANAGER   |    NULL |

            |  7566 | JONES  | 2975.00 | MANAGER   |    NULL |

            |  7902 | FORD   | 3000.00 | ANALYST   |    NULL |

            |  7788 | SCOTT  | 3000.00 | ANALYST   |    NULL |

            |  7839 | KING   | 5000.00 | PRESIDENT |    NULL |

            +-------+--------+---------+-----------+---------+

            

    注意：默认是升序，怎么指定升序或者降序呢？asc表示升序，desc表示降序。

        select empno,ename,sal,job,comm from emp order by sal desc;

            +-------+--------+---------+-----------+---------+

            | empno | ename  | sal     | job       | comm    |

            +-------+--------+---------+-----------+---------+

            |  7839 | KING   | 5000.00 | PRESIDENT |    NULL |

            |  7788 | SCOTT  | 3000.00 | ANALYST   |    NULL |

            |  7902 | FORD   | 3000.00 | ANALYST   |    NULL |

            |  7566 | JONES  | 2975.00 | MANAGER   |    NULL |

            |  7698 | BLAKE  | 2850.00 | MANAGER   |    NULL |

            |  7782 | CLARK  | 2450.00 | MANAGER   |    NULL |

            |  7499 | ALLEN  | 1600.00 | SALESMAN  |  300.00 |

            |  7844 | TURNER | 1500.00 | SALESMAN  |    0.00 |

            |  7934 | MILLER | 1300.00 | CLERK     |    NULL |

            |  7654 | MARTIN | 1250.00 | SALESMAN  | 1400.00 |

            |  7521 | WARD   | 1250.00 | SALESMAN  |  500.00 |

            |  7876 | ADAMS  | 1100.00 | CLERK     |    NULL |

            |  7900 | JAMES  |  950.00 | CLERK     |    NULL |

            |  7369 | SMITH  |  800.00 | CLERK     |    NULL |

            +-------+--------+---------+-----------+---------+

            

    按照工资的降序排列，当工资相同的时候在按照名字的升序排列。

        select empno,ename,sal,job,comm from emp order by sal desc, ename asc;

            +-------+--------+---------+-----------+---------+

            | empno | ename  | sal     | job       | comm    |

            +-------+--------+---------+-----------+---------+

            |  7839 | KING   | 5000.00 | PRESIDENT |    NULL |

            |  7902 | FORD   | 3000.00 | ANALYST   |    NULL |

            |  7788 | SCOTT  | 3000.00 | ANALYST   |    NULL |

            |  7566 | JONES  | 2975.00 | MANAGER   |    NULL |

            |  7698 | BLAKE  | 2850.00 | MANAGER   |    NULL |

            |  7782 | CLARK  | 2450.00 | MANAGER   |    NULL |

            |  7499 | ALLEN  | 1600.00 | SALESMAN  |  300.00 |

            |  7844 | TURNER | 1500.00 | SALESMAN  |    0.00 |

            |  7934 | MILLER | 1300.00 | CLERK     |    NULL |

            |  7654 | MARTIN | 1250.00 | SALESMAN  | 1400.00 |

            |  7521 | WARD   | 1250.00 | SALESMAN  |  500.00 |

            |  7876 | ADAMS  | 1100.00 | CLERK     |    NULL |

            |  7900 | JAMES  |  950.00 | CLERK     |    NULL |

            |  7369 | SMITH  |  800.00 | CLERK     |    NULL |

            +-------+--------+---------+-----------+---------+

        注意：越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

        

    select empno,ename,sal,job,comm from emp order by 2; // 2就表示第二列 不建议使用这种方式，因为不健壮，只要查询的列名更改，那么排序将会发生改变。

        +-------+--------+---------+-----------+---------+

        | empno | ename  | sal     | job       | comm    |

        +-------+--------+---------+-----------+---------+

        |  7876 | ADAMS  | 1100.00 | CLERK     |    NULL |

        |  7499 | ALLEN  | 1600.00 | SALESMAN  |  300.00 |

        |  7698 | BLAKE  | 2850.00 | MANAGER   |    NULL |

        |  7782 | CLARK  | 2450.00 | MANAGER   |    NULL |

        |  7902 | FORD   | 3000.00 | ANALYST   |    NULL |

        |  7900 | JAMES  |  950.00 | CLERK     |    NULL |

        |  7566 | JONES  | 2975.00 | MANAGER   |    NULL |

        |  7839 | KING   | 5000.00 | PRESIDENT |    NULL |

        |  7654 | MARTIN | 1250.00 | SALESMAN  | 1400.00 |

        |  7934 | MILLER | 1300.00 | CLERK     |    NULL |

        |  7788 | SCOTT  | 3000.00 | ANALYST   |    NULL |

        |  7369 | SMITH  |  800.00 | CLERK     |    NULL |

        |  7844 | TURNER | 1500.00 | SALESMAN  |    0.00 |

        |  7521 | WARD   | 1250.00 | SALESMAN  |  500.00 |

        +-------+--------+---------+-----------+---------+

    

    找出工作岗位是salesman的员工，并且要求按照薪资的降序排列？

        select empno,ename,sal,job,comm,deptno from emp where job = 'salesman' order by sal desc;

            +-------+--------+---------+----------+---------+--------+

            | empno | ename  | sal     | job      | comm    | deptno |

            +-------+--------+---------+----------+---------+--------+

            |  7499 | ALLEN  | 1600.00 | SALESMAN |  300.00 |     30 |

            |  7844 | TURNER | 1500.00 | SALESMAN |    0.00 |     30 |

            |  7521 | WARD   | 1250.00 | SALESMAN |  500.00 |     30 |

            |  7654 | MARTIN | 1250.00 | SALESMAN | 1400.00 |     30 |

            +-------+--------+---------+----------+---------+--------+

    

    sql执行顺序    

        select

            *                    3

        from

            tablename    1

        where

            条件            2

        order by

            ...                4

            

        order by 是最后执行的。