SQL Cookbook读书笔记一

这些都是平时不常用，但效率很高的sql，在此将其记下，以备查询。

1. SQL> select * from (select sal as salary,comm as commission from emp) where salary<5000; 
2.  
3.     SALARY COMMISSION 
4. ---------- ---------- 
5.        800 
6.       1600        300 
7.       1250        500 
8.       2975 
9.       1250       1400 
10.       2850 
11.       2450 
12.       3000 
13.       1500          0 
14.       1100 
15.        950 
16.  
17.     SALARY COMMISSION 
18. ---------- ---------- 
19.       3000 
20.       1300 
21.  
22. 已选择13行。 
23.  
24. SQL> select ename||' WORKS AS A' ||job as msg from emp where deptno=10; 
25.  
26. MSG 
27. ------------------------------ 
28. CLARK WORKS AS AMANAGER 
29. KING WORKS AS APRESIDENT 
30. MILLER WORKS AS ACLERK 
31.  
32. SQL> select ename,sal,case when sal <= 2000 then 'UNDERPAID' when sal >= 4000 then 'OVERPAID' else 'OK' end as status from emp; 
33.  
34. ENAME             SAL STATUS 
35. ---------- ---------- --------- 
36. SMITH             800 UNDERPAID 
37. ALLEN            1600 UNDERPAID 
38. WARD             1250 UNDERPAID 
39. JONES            2975 OK 
40. MARTIN           1250 UNDERPAID 
41. BLAKE            2850 OK 
42. CLARK            2450 OK 
43. SCOTT            3000 OK 
44. KING             5000 OVERPAID 
45. TURNER           1500 UNDERPAID 
46. ADAMS            1100 UNDERPAID 
47.  
48. ENAME             SAL STATUS 
49. ---------- ---------- --------- 
50. JAMES             950 UNDERPAID 
51. FORD             3000 OK 
52. MILLER           1300 UNDERPAID 
53.  
54. 已选择14行。 

从表中随机返回n条记录

1. SQL> select * from (select ename,job from emp order by dbms_random.value()) where rownum<=5; 
2.  
3. ENAME      JOB 
4. ---------- --------- 
5. WARD       SALESMAN 
6. CLARK      MANAGER 
7. TURNER     SALESMAN 
8. SMITH      CLERK 
9. JONES      MANAGER 

1. select * from (select ename,job from emp order by dbms_random.value()) where rownum=5; 

这样的写法是错误的，因为oracle不会直接去找rownum为5的数据，它是一条一条往后查询的，有了1，才能有2，一直到5为止。

查找某列值为空的所有行

1. SQL> select * from emp where comm is null; 

使用coalesce函数用实际的值来替换空值

1. SQL> select coalesce(comm,0) from emp; 

以下两名效果是一样的

1. SQL> select ename,job,sal from emp where deptno = 10 order by sal desc; 
2.  
3. ENAME      JOB              SAL 
4. ---------- --------- ---------- 
5. KING       PRESIDENT       5000 
6. CLARK      MANAGER         2450 
7. MILLER     CLERK           1300 
8.  
9. SQL> select ename,job,sal from emp where deptno = 10 order by 3 desc; 
10.  
11. ENAME      JOB              SAL 
12. ---------- --------- ---------- 
13. KING       PRESIDENT       5000 
14. CLARK      MANAGER         2450 
15. MILLER     CLERK           1300 

substr的用法

1. substr( string, start_position, [ length ] ) 

1. SQL> select ename,job from emp order by substr(job,length(job)-1,2); 
2.  
3. ENAME      JOB 
4. ---------- --------- 
5. ALLEN      SALESMAN 
6. WARD       SALESMAN 
7. MARTIN     SALESMAN 
8. TURNER     SALESMAN 
9. JONES      MANAGER 
10. CLARK      MANAGER 
11. BLAKE      MANAGER 
12. KING       PRESIDENT 
13. SMITH      CLERK 
14. JAMES      CLERK 
15. ADAMS      CLERK 
16.  
17. ENAME      JOB 
18. ---------- --------- 
19. MILLER     CLERK 
20. SCOTT      ANALYST 
21. FORD       ANALYST 
22.  
23. 已选择14行。 

将数字转为#，再将#用空替换

1. SQL> select ename||' '||deptno as data from emp order by replace(translate(data,'0123456789','##########'),'#',''); 
2.  
3. DATA 
4. --------------------------------------------------- 
5. ADAMS 20 
6. ALLEN 30 
7. BLAKE 30 
8. CLARK 10 
9. FORD 20 
10. JAMES 30 
11. JONES 20 
12. KING 10 
13. MARTIN 30 
14. MILLER 10 
15. SCOTT 20 
16.  
17. DATA 
18. --------------------------------------------------- 
19. SMITH 20 
20. TURNER 30 
21. WARD 30 
22.  
23. 已选择14行。 

将空值进行排序

1. SQL> select ename,sal,comm from emp order by comm desc nulls first; 
2.  
3. ENAME             SAL       COMM 
4. ---------- ---------- ---------- 
5. SMITH             800 
6. JONES            2975 
7. CLARK            2450 
8. BLAKE            2850 
9. SCOTT            3000 
10. KING             5000 
11. JAMES             950 
12. MILLER           1300 
13. FORD             3000 
14. ADAMS            1100 
15. MARTIN           1250       1400 
16.  
17. ENAME             SAL       COMM 
18. ---------- ---------- ---------- 
19. WARD             1250        500 
20. ALLEN            1600        300 
21. TURNER           1500          0 
22.  
23. 已选择14行。 
24.  
25. SQL> select ename,sal,comm from emp order by comm desc nulls last; 
26.  
27. ENAME             SAL       COMM 
28. ---------- ---------- ---------- 
29. MARTIN           1250       1400 
30. WARD             1250        500 
31. ALLEN            1600        300 
32. TURNER           1500          0 
33. SMITH             800 
34. JONES            2975 
35. JAMES             950 
36. MILLER           1300 
37. FORD             3000 
38. ADAMS            1100 
39. BLAKE            2850 
40.  
41. ENAME             SAL       COMM 
42. ---------- ---------- ---------- 
43. CLARK            2450 
44. SCOTT            3000 
45. KING             5000 
46.  
47. 已选择14行。 
48.  
49. SQL> select ename,sal,comm from emp order by comm nulls last; 
50.  
51. ENAME             SAL       COMM 
52. ---------- ---------- ---------- 
53. TURNER           1500          0 
54. ALLEN            1600        300 
55. WARD             1250        500 
56. MARTIN           1250       1400 
57. SMITH             800 
58. JONES            2975 
59. JAMES             950 
60. MILLER           1300 
61. FORD             3000 
62. ADAMS            1100 
63. BLAKE            2850 
64.  
65. ENAME             SAL       COMM 
66. ---------- ---------- ---------- 
67. CLARK            2450 
68. SCOTT            3000 
69. KING             5000 
70.  
71. 已选择14行。 
72.  
73. SQL> select ename,sal,comm from emp order by comm nulls first; 
74.  
75. ENAME             SAL       COMM 
76. ---------- ---------- ---------- 
77. SMITH             800 
78. JONES            2975 
79. CLARK            2450 
80. BLAKE            2850 
81. SCOTT            3000 
82. KING             5000 
83. JAMES             950 
84. MILLER           1300 
85. FORD             3000 
86. ADAMS            1100 
87. TURNER           1500          0 
88.  
89. ENAME             SAL       COMM 
90. ---------- ---------- ---------- 
91. ALLEN            1600        300 
92. WARD             1250        500 
93. MARTIN           1250       1400 
94.  
95. 已选择14行。 

将用case函数得到的值，做为查询和排序条件

1. SQL> select ename,sal,job,comm from emp order by case when job = 'SALESMAN' then comm else sal end; 
2.  
3. ENAME             SAL JOB             COMM 
4. ---------- ---------- --------- ---------- 
5. TURNER           1500 SALESMAN           0 
6. ALLEN            1600 SALESMAN         300 
7. WARD             1250 SALESMAN         500 
8. SMITH             800 CLERK 
9. JAMES             950 CLERK 
10. ADAMS            1100 CLERK 
11. MILLER           1300 CLERK 
12. MARTIN           1250 SALESMAN        1400 
13. CLARK            2450 MANAGER 
14. BLAKE            2850 MANAGER 
15. JONES            2975 MANAGER 
16.  
17. ENAME             SAL JOB             COMM 
18. ---------- ---------- --------- ---------- 
19. SCOTT            3000 ANALYST 
20. FORD             3000 ANALYST 
21. KING             5000 PRESIDENT 
22.  
23. 已选择14行。 
24.  
25. SQL> select ename,sal,job,comm,case when job = 'SALESMAN' then comm else sal end as ordered from emp order by 5; 
26.  
27. ENAME             SAL JOB             COMM    ORDERED 
28. ---------- ---------- --------- ---------- ---------- 
29. TURNER           1500 SALESMAN           0          0 
30. ALLEN            1600 SALESMAN         300        300 
31. WARD             1250 SALESMAN         500        500 
32. SMITH             800 CLERK                       800 
33. JAMES             950 CLERK                       950 
34. ADAMS            1100 CLERK                      1100 
35. MILLER           1300 CLERK                      1300 
36. MARTIN           1250 SALESMAN        1400       1400 
37. CLARK            2450 MANAGER                    2450 
38. BLAKE            2850 MANAGER                    2850 
39. JONES            2975 MANAGER                    2975 
40.  
41. ENAME             SAL JOB             COMM    ORDERED 
42. ---------- ---------- --------- ---------- ---------- 
43. SCOTT            3000 ANALYST                    3000 
44. FORD             3000 ANALYST                    3000 
45. KING             5000 PRESIDENT                  5000 
46.  
47. 已选择14行。 

本文转自sucre03 51CTO博客，原文链接：http://blog.51cto.com/sucre/414090，如需转载请自行联系原作者