Ai分享|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//查询所有员工薪资，排序？ // 默认是升序！！！ mysql> 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 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//怎么降序？ //使用关键字 desc mysql> 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 | | WARD | 1250.00 | | MARTIN | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//指定升序？ mysql> 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 | | SCOTT | 3000.00 | | FORD | 3000.00 | | KING | 5000.00 | +--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//可以两个字段排序吗？或者说按照多个字段排序？ //查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话， //再按照名字升序排列。 mysql> select ename,sal from emp order by sal asc, ename asc; +--------+---------+ | 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 | +--------+---------+ // sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。 |
|
1 2 3 4 5 6 |
//了解：根据字段的位置也可以排序 select ename,sal from emp order by 2; // 2表示第二列。第二列是sal //按照查询结果的第2列sal排序 //了解一下，不建议在开发中这样写，因为不健壮。 //因为列的顺序很容易发生改变，列顺序修改之后，2就废了。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
//综合一点的案例： //找出工资在1250到3000之间的员工信息，要求按照薪资降序排列。 mysql> select -> ename,sal -> from -> emp -> where -> sal between 1250 and 3000 -> order by -> sal desc; +--------+---------+ | ename | sal | +--------+---------+ | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | +--------+---------+ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
总结: 关键字顺序不能变： select ... from ... where ... order by ... 以上语句的执行顺序必须掌握： 第一步：from 第二步：where 第三步：select 第四步：order by（排序总是在最后执行！） |
