常用SQL语句2（Oracle）
时间：2021-07-01 10:21:17
帮助过：3人阅读
/********************************SCOTT Database*******************************/
2 select * from EMP;
3 select * from DEPT;
4 select * from SALGRADE;
5
6 select ename, sal
*12 from emp;
7
8 /*system empty table*/
9 select * from dual;
10 select 2*3 from dual;
11
12 select sysdate
from dual;
13 /*alias*/
14 select ename, sal
*12 annual_sal
from emp;
15 /*alias with space*/
16 select ename, sal
*12 "annual sal"
from emp;
17
18 /*0 not equal null value*/
19 select ename, comm
from emp;
20
21 /*year salary + comm, any contains empty‘s math operation result is empty*/
22 select ename, sal
*12 + comm
from emp;
23
24 select ename
||sal
from emp;
25
26 /*concat string*/
27 select ename
|| ‘abcdefg‘ from emp;
28
29 /*2 ‘ instead of 1 ‘*/
30 select ename
|| ‘abcdefg‘‘abcd‘ from emp;
31
32 select deptno
from emp;
33
34 /*remove duplicate*/
35 select distinct deptno
from emp;
36
37 select distinct deptno, job
from emp;
38
39 select * from emp
where deptno
= 10;
40
41 select * from EMP
where ename
= ‘CLARK‘;
42
43 select ename, sal
from emp
where SAL
> 1500;
44
45 select ename, sal
from emp
where deptno
<> 10;
46
47 select ename, sal
from emp
where ename
> ‘CBA‘;
48
49 select ename, sal
from emp
where sal
between 800 and 1500;
50
51 select ename, sal
from emp
where sal
>= 800 and sal
<= 1500;
52
53 /*empty value*/
54 select ename, sal, comm
from emp
where comm
= null;
55
56 select ename, sal, comm
from emp
where comm
is null;
57
58 select ename, sal, comm
from emp
where comm
is not null;
59
60 select ename, sal, comm
from emp
where sal
in (
800,
1500,
2000);
61
62 select ename, sal, comm
from emp
where ename
in (
‘SMITH‘,
‘KING‘,
‘ABC‘);
63
64 select ename, sal
from emp
where DEPTNO
= 10 and sal
> 1000;
65
66 select ename, sal
from emp
where DEPTNO
= 10 or sal
> 1000;
67
68 select ename, sal
from emp
where DEPTNO
= 10 and sal
not in (
800,
1500);
69
70 /*% means 0 or more*/
71 select ename
from emp
where ENAME
like ‘%ALL%‘;
72
73 select ename
from emp
where ename
like ‘_A%‘;
74
75 /*contains % in the selected rows need escape sequence*/
76
77 select ename
from emp
where ENAME
like ‘%\%%‘;
78
79 /*default escapse sequence is \ but we can defintion by use <escape> ‘$‘ */
80 select ename
from emp
where ename
like ‘%$%%‘ escape ‘$‘;
81
82 select * from DEPT;
83
84 /*order by asc desc defalut is asc*/
85 select * from DEPT
order by DEPTNO
desc;
86
87 select empno, ename
from emp;
88
89 select empno, ename
from emp
order by EMPNO
asc;
90
91 select empno, ename
from emp
order by EMPNO
desc;
92
93 select empno, ename
from emp
where deptno
<> 10 order by empno
asc;
94
95 select ename, sal, deptno
from emp
order by deptno
asc;
96
97 select ename, sal, deptno
from emp
order by deptno
asc, ename
desc;
98
99 select ename, sal
*12 annual_sal
from emp
where ename
not like ‘_A%‘ and sal
> 800 order by sal
desc;
100
101 select lower(ename)
from emp
102
103 select ename
from emp
where lower(ename)
like ‘_a%‘;
104
105 select ename
from emp
where ename
like ‘_a%‘ or ename
like ‘_A%‘;
106
107 select substr(ename,
1,
3)
from emp;
108
109 select substr(ename,
2,
3)
from emp;
110
111 select chr(
65)
from dual;
112
113 select ascii(
‘A‘)
from dual;
114
115 select round(
23.652)
from dual;
116
117 select round(
23.652,
2)
from dual;
118
119 select round(
23.652,
-1)
from dual;
120
121 /* 9 means number, is this position has no number, it will not be displayed
122 ‘$99,999.9999‘ --> $800.0000
123 ‘$99,999.9999‘ --> $1,500.0000
124 ‘L99,999.9999‘ --> Y1,500.0000
125 ‘$00000.0000‘ --> $00800.0000
126 ‘$00000.0000‘ --> $01500.0000
127
128 to_char
129 */
130
131 select to_char(sal,
‘$99,999.9999‘)
from emp;
132
133 select to_char(sal,
‘$00000.0000‘)
from emp;
134
135 select hiredate
from emp;
136
137 /* Date format
138 YYYY -- year
139 MM -- month
140 DD -- day
141 HH -- 12 hour
142 HH24 -- 24 hour
143 MI -- minute
144 SS -- second
145 */
146 select to_char(hiredate,
‘YYYY-MM-DD HH:MI:SS‘) joindate
from emp;
147
148 /*to_date*/
149 select ename, hiredate
from emp
where HIREDATE
> to_date(
‘1981-2-20 12:34:56‘,
‘YYYY-MM-DD HH24:MI:SS‘);
150
151 /*to_number*/
152 select sal
from EMP
where sal
> to_number(
‘$1,250.00‘,
‘$9,999.99‘);
153
154 select ename, sal
*12 + comm
from emp;
155
156
157 /*
158 function nvl to deal with null value to math operation
159 if comm is null then o instead of null or it will be comm
160 */
161 select ename, sal
*12 + nvl(comm,
0)
from emp;
162
163
164 /*
165 group function : min, max, avg, count, sum
166 */
167 select max(sal) maxsal
from emp;
168
169 select min(sal) minsal
from emp;
170
171 select avg(sal) avgsal
from emp;
172
173 select to_char(
avg(sal),
‘999999999.99‘) avgsal
from emp;
174
175 select round(
avg(sal),
2) avgsal
from emp;
176
177 select sum(sal)
from emp;
178
179 select count(
*) records
from emp;
180
181 select count(
*)
from emp
where deptno
= 10;
182
183 select count(ename)
from emp;
184
185 /*count the field which is not null*/
186 select count(comm)
from emp;
187
188 select count(deptno)
from emp;
189
190 select count(
distinct deptno)
from emp;
191
192 /*
193 function : group by
194 */
195
196 /* which department‘s avg salary is highest*/
197 select sal, deptno
from emp;
198
199
200 /*department avg salary*/
201 select deptno, to_char(
avg(sal),
‘99999999.99‘) depavgsal
from emp
group by deptno;
202
203 select deptno, job,
max(sal) maxsal
from emp
group by deptno, job;
204
205 /*the highest salary in the company*/
206 select max(sal)
from emp;
207
208 /*who is the highest salary in the company*/
209 select ename
from emp
where sal
= (
select max(sal)
from emp);
210
211 /*the highest salary in the department*/
212 select deptno,
max(sal)
from emp
group by deptno;
213
214 select to_char(
avg(sal),
‘99999999.99‘) avgsal, deptno
from emp
group by deptno;
215
216 /* having is limited with grouping*/
217 select to_char(
avg(sal),
‘99999999.99‘) avgsal, deptno
from emp
group by deptno
having avg(sal)
> 2000;
218
219 select deptno,
max(sal)
from emp
where SAL
> 1000 group by deptno
having avg(sal)
> 2000 order by deptno
desc;
220
221 select deptno, to_char(
avg(sal),
‘99999999.99‘)
from emp
where sal
> 1200 group by deptno
having avg(sal)
> 1500 order by avg(sal)
desc;
222
223
224 select ename, sal
from emp
where SAL
= (
select max(sal)
from emp);
225
226 select ename, sal
from emp
where sal
> (
select avg(sal)
from emp);
227
228 select ename, sal
from emp
join (
select max(sal) maxsal, deptno
from emp
group by deptno) t
on (emp.sal
= t.maxsal
and emp.deptno
= t.deptno);
229
230 select * from SALGRADE;
231
232 select deptno, to_char(
avg(sal),
‘999999999.99‘) s
from emp
group by deptno;
233
234 SELECT dname,grade,to_char(S,
‘999999.999‘)
FROM dept,(
SELECT deptno,grade,s
FROM (
SELECT deptno,grade,s
FROM (
SELECT deptno,
avg(sal) s
FROM emp
GROUP BY deptno),salgrade
WHERE s
BETWEEN losal
AND hisal
ORDER BY grade
DESC) t
WHERE ROWNUM
=1) t
WHERE dept.deptno
=t.deptno;
235
236 SELECT dname,grade,to_char(S,
‘999999.999‘)
FROM dept,(
SELECT deptno,grade,s
FROM (
SELECT deptno,grade,s
FROM (
SELECT deptno,
avg(sal) s
FROM emp
GROUP BY deptno),salgrade
WHERE s
BETWEEN losal
AND hisal
ORDER BY grade
DESC) t
WHERE ROWNUM
=1) t
WHERE dept.deptno
=t.deptno;
237
238
239 /*self table connection*/
240
241 select empno, ename, mgr
from emp;
242
243 select e1.ename, e2.ename
from emp e1, emp e2
where e1.MGR
= e2.EMPNO;
244
245 select ename, dname
from EMP, DEPT
where emp.DEPTNO
= dept.DEPTNO;
246
247 select ename, dname
from EMP
join DEPT
on (emp.DEPTNO
= dept.DEPTNO);
248
249 select ename, dname
from EMP
join DEPT using (deptno);
250
251 select ename, grade
from emp e
join salgrade s
on (e.SAL
between s.LOSAL
and s.HISAL);
252
253 select ename, dname, grade
from emp e
join dept d
on (e.DEPTNO
= d.DEPTNO)
join salgrade s
on (e.SAL
between s.LOSAL
and s.HISAL)
where ename
not like ‘_A%‘;
254
255 /*left join == left outer join*/
256 select e1.ename, e2.ename
from emp e1
left join emp e2
on (e1.mgr
= e2.empno);
257
258 select e1.ename, e2.ename
from emp e1
left outer join emp e2
on (e1.mgr
= e2.empno);
259
260 select * from dept;
261
262 select ename, dname
from emp e
right join DEPT d
on (e.DEPTNO
= d.DEPTNO);
263
264 select ename, dname
from emp e
right outer join DEPT d
on (e.DEPTNO
= d.DEPTNO);
265
266 /* full join */
267 select ename, dname
from emp e
full join DEPT d
on (e.DEPTNO
= d.DEPTNO);
268
269 /*department avg salary*/
270 select deptno, to_char(
avg(sal),
‘9999999.99‘)
from emp
group by deptno;
271
272 /*department avg salary‘s grade*/
273 select deptno, avg_sal, grade
274 from (
select deptno, to_char(
avg(sal),
‘999999.99‘) avg_sal
from emp
group by deptno) t
275 join SALGRADE s
276 on (t.avg_sal
between s.LOSAL
and s.HISAL);
277
278 /*salary grade for every employee*/
279 select ename, deptno, grade
from emp e
join SALGRADE s
on (e.SAL
between s.LOSAL
and s.HISAL);
280
281 select deptno, to_char(
avg(grade),
‘9.9‘) avg_grade
282 from (
select ename, deptno, grade
from emp e
join SALGRADE s
on (e.SAL
between s.LOSAL
and s.HISAL)) t
283 group by t.deptno;
284
285 /* who is manager*/
286 select ename
from emp
where EMPNO
in (
select mgr
from emp);
287
288 select ename
from emp
where EMPNO
in (
select distinct mgr
from emp);
289
290 select * from emp;
291
292 /*select highest salary*/
293 select max(sal)
from emp;
294
295 select distinct e1.sal
from emp e1
join emp e2
on (e1.SAL
< e2.SAL);
296
297 /* please don‘t use group function to find the max salary*/
298 select distinct sal
from emp
where SAL
not in (
select distinct e1.sal
from emp e1
join emp e2
on (e1.SAL
< e2.SAL));
299
300 select deptno, avg_sal
from (
select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno)
301 where avg_sal
= (
select max(avg_sal)
from (
select to_char(
avg(sal),
‘999999.99‘) avg_sal, deptno
from emp
group by deptno));
302
303
304 select max(avg_sal)
from (
select to_char(
avg(sal),
‘999999.99‘) avg_sal, deptno
from emp
group by deptno);
305 select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno;
306
307
308 /*avg salary is highest*/
309 select deptno, avg_sal
from (
select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno)
where avg_sal
= (
select max(avg_sal)
from (
select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno));
310
311 /*the depatname of the highest avg salary*/
312 select dname
from dept
where deptno
=
313 (
select deptno
from (
select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno)
where avg_sal
= (
select max(avg_sal)
from (
select to_char(
avg(sal),
‘99999.99‘) avg_sal, deptno
from emp
group by deptno)));
314
315
316 select avg(sal) sal_avg
from emp
group by deptno;
317
318 select deptno,
avg(sal) sal_avg
from emp
group by deptno;
319
320 select deptno, to_char(sal_avg,
‘999999.99‘) sal_avg, grade
321 from (
select deptno,
avg(sal) sal_avg
from emp
group by deptno) t
322 join salgrade s
323 on (t.sal_avg
between s.LOSAL
and s.HISAL);
324
