MySQL排序查找

按照工资升序，找出员工名和薪资 默认升序排列 [bjpowernode]>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 | +--------+---------+ 14 rows in set (0.00 sec) 指定升序或者降序 1. asc:表示升序 2. desc:表示降序 [bjpowernode]>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 | +--------+---------+ 14 rows in set (0.00 sec) [bjpowernode]>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 | +--------+---------+ 14 rows in set (0.01 sec) 按照工资降序排列，工资一样的按照名字的升序排列 [bjpowernode]>select ename,sal from EMP order by sal desc,ename asc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | 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 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+ 14 rows in set (0.00 sec) # 多个字段同时排序，越靠前的位置起的作用越大 找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列 [bjpowernode]>select #最后查 -> ename,job,sal -> from #先执行 -> EMP -> where #接下来 -> job='SALESMAN' -> order by #最后排序输出 -> sal desc; +--------+----------+---------+ | ename | job | sal | +--------+----------+---------+ | ALLEN | SALESMAN | 1600.00 | | TURNER | SALESMAN | 1500.00 | | WARD | SALESMAN | 1250.00 | | MARTIN | SALESMAN | 1250.00 | +--------+----------+---------+ 4 rows in set (0.00 sec)