复习: - H9 A$ g# ~9 ]' A' U, P1.查询语句的基本格式。 ! k' z, U: {9 O! {! hselect ... 3 c, n4 v! |. w5 a( Xfrom 表名7 o: P% D+ V- R
where 条件7 I$ f- ^3 T1 [# u' L8 D8 o
group by 列名 u" @7 g0 {9 ^ x; j
having 带组函数的条件/ l) U; r# Y& ]: w
order by 列名! P5 r' y7 [( x( w: [. O. W
$ E6 X4 X& }# p7 y$ T& g
2.函数2 s$ `' [! _: {; [) J- X3 G) G
1)组函数:count / avg / sum / max / min; ?: g) p3 {6 Q f5 O6 B& F
$ J( I8 x" `- A! k# H
2)单行函数: $ _: `0 p) n" ha.字符函数:upper / lower / initcap ) i4 z1 [6 v1 E; _4 I- f* P* d/ i length / lpad / rpad 2 }! r; k" _$ h' T 0 e$ {! b- c2 g9 @( V6 [) } l : left ; r: right4 R( T4 l& `2 @0 ?
# C& u/ T& m% J3 [select rpad(ename, 10, '*') from emp_ning; # q, C- v& D% ]- L# b( r' o$ n
b.数字函数: round / trunc / mod: e2 A1 z# P( ^5 C8 o7 v/ J
select mod(salary, 1000) from emp_ning; 2 ?$ U+ m+ G- s 2 H# ^3 ]) [- m( s* R: H( Dc.日期函数: months_between / add_months / 1 K# n# _& ?4 E, l0 E
last_day(sysdate) " c- J6 ]7 x% D* U % b9 O. e- ]. Kd.转换函数: to_char / to_date / to_number% f; o' F/ X* U5 X$ C
5 l5 q( W& x) }2 T to_char to_number & K' E) Y' c! F) |% |4 a 日期 ---------> 字符 ---------->数字( a N* i8 n/ |8 A3 ]' K% W- B0 i* n
<--------- <---------; l; Y6 a& n8 t: v0 M$ P* C. _4 o g
to_date to_char( K2 x$ w) c X$ T. M9 C
6 l" \% ^' g6 \; l8 p, B- Q1 R/ n
) k2 x, g0 H; w' L where password = '1234' and id = '1001' + H4 v6 F$ X7 s2 N; x H" G & {$ p$ o1 S, K& y( m select to_number('$7,912,345.67','$9,999,999.99') $ \: J1 {8 V5 h from dual; 1 [% d; \% |- p7 {; {2 P V- V3 B/ h 6 s. U# W. f( G# Y( V7912345.675 I. F8 O7 ~) z4 ~$ \- v, v6 C) A
8 A/ F/ n# n' P8 p& P+ fselect to_char(7912345.67, '$9,999,999.99') # L8 Y( {& g5 x* C, q: J6 z- Nfrom dual;5 L5 j: K8 i" B6 f- ~
7 H% C) v8 D( i* [
$7,912,345.67 . J# B% J3 {6 K% D [2 P8 A$ D- we.通用函数:nvl / coalesce / decode * o6 B5 l. k; R9 y4 i5 G0 g & U- ]+ Q0 q G1 ?6 Y" O使用频率比较高的单行函数:# B9 ~! S) w! j& L) e
upper / round / to_char / to_date / nvl$ ]* R) `2 l1 i* q: t8 k
- I$ W. l: m- `9 y, J
2 f, L" R, Q: C6 Z. k* G
一、子查询# f. o! s$ V" s) S
1.谁的薪水比张无忌高?' U0 J* j+ \$ ]$ L3 i
子查询( [0 }1 Y" O4 j" A9 Z0 h$ U
select salary from emp_ning + j/ ]5 X; j* V# r) n& v& ]where ename = '张无忌';& p' |3 S: {! u/ Z. ?2 {& e( T Z
8 R: F# m5 r' h3 Y
10000) U% z9 P% ?% V" g) o% l% g
" a6 {. D4 A) \9 E主查询 # e C" x& R" x. Z7 |2 |* |select ename from emp_ning ( ?8 D8 L2 {+ a4 w9 d bwhere salary > 10000; 7 W0 i2 h0 g! z+ r' e# H) k/ J" J6 y, |) t7 C8 x/ S, D0 w5 e
张三丰 2 W3 f% n; W% G1 h3 L3 J/ G$ L0 n& p' D1 D& h; D2 ~* K' J5 j
合并到一条SQL语句: 3 k- L4 y1 u7 [1 iselect ename from emp_ning+ [) N: x: g. a; Z f% ]- w0 G
where salary > (select salary from emp_ning 0 U" d( |" V% z) r' c where ename = '张无忌');, T; h: x9 q( ~/ y
: S' O) N6 ] f( F" p) i; t
> < >= <= = <>: 单行比较运算符, : e4 c# Z/ k: R! K/ z只能和一个数字比较。$ q$ |1 h5 ?# B; a" ^& C
3 Q# M7 F5 S6 g3 D$ _+ p
insert into emp_ning(empno, ename, salary). J3 [7 v% K, f' X9 Q6 L. S
values(1014, '张无忌', 8000);( ]9 o X: n% _. C3 N8 |
7 _! \0 v9 S/ ], S: j3 j
谁的薪水比所有叫张无忌的人的薪水都高?大于最大 3 g2 W" X3 l. ^10000( d+ I7 c' K+ J& H4 ^ U
8000 7 D2 u( m9 g/ Y$ x6 C+ @9 `! B: F4 [9 v8 i& W' v. L% _) p
select ename from emp_ning' p2 K& A+ b# a. U' s3 b. `
where salary > ALL(select salary from emp_ning : X# x9 g7 g2 B1 v7 z where ename = '张无忌'); ?0 D' ~7 w- I + U3 i q/ P6 q- P L哪些人的薪水比任何一个叫张无忌的人高都行?大于最小5 O& v$ {$ G+ l$ J
10000 3 K( W+ t3 e+ Y8000$ r" W2 m/ K' i
; f9 [) w# h: P& y$ A4 z9 b! u) pselect ename from emp_ning 8 t; f9 k/ _7 l4 P, H1 j* Twhere salary > ANY(select salary from emp_ning & u" P b% y c& B4 x where ename = '张无忌');: E- s, p* h. I; o% \
5 b8 V7 p- m% W# l. P/ F! n2.谁和刘苍松同部门?列出除了刘苍松之外的员工名字。; g9 V) C5 \1 x* ]
select deptno from emp_ning ) j# O+ @3 R/ n7 f" Mwhere ename = '刘苍松';, M' i$ m8 D6 {; v, I I
10* z6 H# P# s; r$ Z" W
; u, @) C1 d+ l6 A
select ename from emp_ning # e4 L+ M3 n. q# R- ~, Rwhere deptno = 10 0 `0 H+ {) Y2 c$ G) iand ename <> '刘苍松'; & A* g, @: t: z y/ o& @. j- K - |( o. ~+ R- c' t" tselect ename,salary,job : W# h( ^, ?/ m- b i
from emp_ning! A2 d" O) [* B) a0 J2 K
where deptno = (select deptno from emp_ning$ e9 N; r4 |1 I1 @/ V
where ename = '刘苍松') ) a6 P9 b% G6 `; O0 C6 Iand ename <> '刘苍松';! i# Y% D# x& m8 Q6 `9 m
0 t! W$ A- {7 {7 q--如果子查询得到的结果是多个,不能使用单行比较运算符' G: c8 U9 o: v5 Q" P
等号。改为:in( l2 g( D9 B+ M# ^4 ?7 }' o$ X# g
insert into emp_ning(empno, ename, deptno)1 g2 @2 ~% R" _) M! ~
values(1015, '刘苍松', 20); # x1 @' b. M. o s2 S8 Z$ d! K$ X2 L* f T- A# s
select ename,salary,job,deptno 9 f- ~- j! t' ]& w9 p9 z/ h# a+ w
from emp_ning5 J2 Z k1 y% _, r, G1 i6 Y5 {$ c
where deptno in (select deptno from emp_ning 6 Y( n# B( D0 ^6 n3 k+ V5 r where ename = '刘苍松'). q8 _* y1 a- y8 T& x
and ename <> '刘苍松'; # V1 n7 S3 I0 ^7 e7 U3 U8 r/ h ! q$ i# i2 J2 H# G0 N% ?9 w5 G8 h根据子查询返回的结果的行数:& N6 _4 }. f* K5 \0 H
返回一行:> < >= <= = <>* w& F9 h6 Q3 j3 T+ [9 _
返回多行:>ALL >ANY <ALL <ANY in * H7 L9 n9 a; }+ \( o" W1 V2 H/ ~0 n( E
3.每个部门拿最高薪水的是谁? 5 _0 x9 l) k% c' N--返回多行8 j2 Z) y) F% t* N# q# x3 P" ]4 K
select deptno, max(salary)7 _& r2 }+ z" |
from emp_ning $ _: X/ s7 ?9 nwhere deptno is not null ; d+ r+ B F! B9 g& e- r* z- Vgroup by deptno;5 V8 Z. c. G( C& N- Y) y/ \# a
. k% H5 s. z/ g, [6 W 30 5000 . f1 E/ r2 L8 n8 `; U( ~* I 20 15000" D2 c" h2 Q% Y3 W& ^& b
10 10000( P l( x* k" `+ t: {! p# j
3 C. ^5 A& g7 s/ p1 \! Q
select ename, salary, job, deptno8 R. V L- J6 a" T
from emp_ning9 {) r1 P* V' c* f E
where (deptno, salary) 3 @9 ]; m- V/ S- a$ `4 Z( o in (select deptno, max(salary), L1 M/ d p1 ]7 j$ m0 a/ Y1 \
from emp_ning1 S! Z1 } c8 \* ~' U
where deptno is not null 6 U. x- e% y' G group by deptno);8 j4 {$ O# n5 o
8 g8 i- }1 N0 I4.哪个部门的人数比部门30的人数多? u R3 c; w! c3 U6 J% X
select count(*) from emp_ning , c- \3 s$ w/ Y4 d$ fwhere deptno = 30; 3 A4 ]5 [) g' t5 t, h( @" w3 & F: I: K2 ]+ A% j L4 [4 B" z+ P+ C' ?' ?2 o) m
select deptno, count(*)' p$ w% j6 J/ p0 A
from emp_ning ; G; Y5 x/ A% r" u! b+ Agroup by deptno- }5 ?2 v" S% d5 Y' M- g: i, R
having count(*) > (select count(*) from emp_ning6 Y& w$ ~7 ]: W" C
where deptno = 30);3 h; G b; H1 [0 h; {) [
* P9 _+ J4 y" v! I5 f
5.哪个部门的平均薪水比部门20的平均薪水高? ! Y9 ~% y$ F2 A+ W3 J# k/ Y" nselect deptno, avg(nvl(salary,0)) avg_s4 c# N- p m, z8 M. J
from emp_ning ' G6 `4 k7 n1 B& jgroup by deptno ' C9 r4 D8 F0 G' D. |% jhaving avg(nvl(salary,0)) >$ n: b, U! s- R& K9 i* o0 i
(select avg(nvl(salary,0)) 0 C1 O# }" K# @. Y8 K6 J% |* Y from emp_ning6 k( o4 y! \4 _3 f, l$ k7 f
where deptno = 20);2 a% O5 n7 w5 B1 E/ o6 J, m
2 k) x- ^& {; H8 Q- L
6.列出员工名字和职位,这些员工所在的部门平均薪水 7 I* Y9 E" U% ?* ] R大于5000元。& V) s0 P0 Y0 D z3 f
select deptno, avg(nvl(salary,0))& G/ e; t& e: v7 b
from emp_ning3 X5 A/ o* \! ?7 P: B
group by deptno ; I: I- Z0 U* L Z, }, m: [having avg(nvl(salary,0)) > 5000; ! k& ~0 w4 R" w" {; w" Z2 w10 5750 5 h' G+ g( v: t0 a20 8000 # e* D4 \1 x1 b! x; J( U* ` 5 ^& p& [7 L( [8 [0 x+ E1 l; xselect ename, job2 O0 O/ Y/ a2 [. |- B
from emp_ning " c4 ~5 D% [6 E& w. d) @where deptno in (select deptno , K; Q4 Z1 x1 e& b* z0 x" U from emp_ning- k2 H0 K0 ]6 H/ G2 w
group by deptno4 K6 h) l' r+ P
having avg(nvl(salary,0)) > 5000); J9 c& {9 Z8 l+ _( W
% ~2 }- U0 v" r- B0 ~% g% r7.谁是张无忌的下属?$ U* b+ m; K y! T
select empno from emp_ning 3 L: E: g# M- w Nwhere ename = '张无忌';: E) u* h6 G8 U: D1 u
1001 ; t9 A. Q' q T( t1014 9 G6 E+ l8 i! o o2 g( U" G, e$ [% K# T& Y' k
select ename from emp_ning. V) V0 D4 g$ F2 d+ g$ E& f7 w
where mgr in (1001, 1014); 6 F5 `) y2 _: R4 w) U; @) K4 ~% ^8 b' o9 X9 F
select ename from emp_ning 1 w5 G) v0 f d% G4 d! K/ owhere mgr in (select empno from emp_ning. _9 t; e$ s7 y }* r3 s8 d& w" b
where ename = '张无忌');2 q+ G( ]" c0 P% X: ] l
0 g1 x9 q( [3 P' X
8.研发部有哪些职位?' o. V; q- \4 t% s
select distinct job from emp_ning, R5 x. L' N) D# h: S+ n
where deptno = (select deptno # `8 e! w' T6 J% x2 b( w8 \ from dept_ning 3 S5 g+ N; L( s+ Y where dname = '研发部'); + d/ g/ m9 L& E& R o5 x1 l/ P : E. T. V* K) }2 {8 V u* m; E: @9 v! ?