1. 默认排序
SQL> select *
2 from emp
3 order by sal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
由此可见,默认排序是升序
2. 升序排序
SQL> select *
2 from emp
3 order by sal asc; //升序
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
3.降序排序
SQL> select *
2 from emp
3 order by sal desc; //降序
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7369 SMITH CLERK 7902 17-12月-80 800 20
4.order by 表达式
SQL> select empno,ename,job,mgr,hiredate, sal*12 年薪,comm,deptno
2 from emp
3 order by sal*12;
EMPNO ENAME JOB MGR HIREDATE 年薪 COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 9600 20
7900 JAMES CLERK 7698 03-12月-81 11400 30
7876 ADAMS CLERK 7788 23-5月 -87 13200 20
7521 WARD SALESMAN 7698 22-2月 -81 15000 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 15000 1400 30
7934 MILLER CLERK 7782 23-1月 -82 15600 10
7844 TURNER SALESMAN 7698 08-9月 -81 18000 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 19200 300 30
7782 CLARK MANAGER 7839 09-6月 -81 29400 10
7698 BLAKE MANAGER 7839 01-5月 -81 34200 30
7566 JONES MANAGER 7839 02-4月 -81 35700 20
7902 FORD ANALYST 7566 03-12月-81 36000 20
7788 SCOTT ANALYST 7566 19-4月 -87 36000 20
7839 KING PRESIDENT 17-11月-81 60000 10
1111 tom_abcd prgrammer 2222 03-12月-81 93600 1000 20
5. order by 别名
SQL> select empno,ename,job,mgr,hiredate, sal*12 年薪,comm,deptno
2 from emp
3 order by 年薪;
EMPNO ENAME JOB MGR HIREDATE 年薪 COMM DEPTNO
--------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 9600 20
7900 JAMES CLERK 7698 03-12月-81 11400 30
7876 ADAMS CLERK 7788 23-5月 -87 13200 20
7521 WARD SALESMAN 7698 22-2月 -81 15000 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 15000 1400 30
7934 MILLER CLERK 7782 23-1月 -82 15600 10
7844 TURNER SALESMAN 7698 08-9月 -81 18000 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 19200 300 30
7782 CLARK MANAGER 7839 09-6月 -81 29400 10
7698 BLAKE MANAGER 7839 01-5月 -81 34200 30
7566 JONES MANAGER 7839 02-4月 -81 35700 20
7902 FORD ANALYST 7566 03-12月-81 36000 20
7788 SCOTT ANALYST 7566 19-4月 -87 36000 20
7839 KING PRESIDENT 17-11月-81 60000 10
1111 tom_abcd prgrammer 2222 03-12月-81 93600 1000 20
6. order by 序号
SQL> select empno,ename,job,mgr,hiredate, sal*12 年薪,comm,deptno
2 from emp
3 order by 1; //按第1列升序排列
EMPNO ENAME JOB MGR HIREDATE 年薪 COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
1111 tom_abcd prgrammer 2222 03-12月-81 93600 1000 20
7369 SMITH CLERK 7902 17-12月-80 9600 20
7499 ALLEN SALESMAN 7698 20-2月 -81 19200 300 30
7521 WARD SALESMAN 7698 22-2月 -81 15000 500 30
7566 JONES MANAGER 7839 02-4月 -81 35700 20
7654 MARTIN SALESMAN 7698 28-9月 -81 15000 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 34200 30
7782 CLARK MANAGER 7839 09-6月 -81 29400 10
7788 SCOTT ANALYST 7566 19-4月 -87 36000 20
7839 KING PRESIDENT 17-11月-81 60000 10
7844 TURNER SALESMAN 7698 08-9月 -81 18000 0 30
7876 ADAMS CLERK 7788 23-5月 -87 13200 20
7900 JAMES CLERK 7698 03-12月-81 11400 30
7902 FORD ANALYST 7566 03-12月-81 36000 20
7934 MILLER CLERK 7782 23-1月 -82 15600 10
7.多重排序
7.1 例子1
SQL> select *
2 from emp
3 order by deptno asc,sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7369 SMITH CLERK 7902 17-12月-80 800 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7.2 例子2
SQL> select *
2 from emp
3 order by deptno asc,sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7900 JAMES CLERK 7698 03-12月-81 950 30
8.带有空值的排序
8.1 例子1
SQL> select *
2 from emp
3 order by comm desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7839 KING PRESIDENT 17-11月-81 5000 10
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
上述例子的问题是空字段排在了前面,如果表格数据量大的情况下,你还得翻页才能看到你想要看的数据,所以需要改善
8.2 例子2(解决了例子1中的缺陷)
SQL> select *
2 from emp
3 order by comm desc
4 nulls last;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7369 SMITH CLERK 7902 17-12月-80 800 20
8.3 例子3
SQL> select *
2 from emp
3 order by comm asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
1111 tom_abcd prgrammer 2222 03-12月-81 7800 1000 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7369 SMITH CLERK 7902 17-12月-80 800 20
说明;升序时,空值默认排在最后