该用户从未签到
|
复习:
, R5 B( A5 r6 ]6 z. ^8 i# z: |1.查询语句的基本格式。
R3 b! k8 r: U. @/ z$ Yselect ...
. r0 u/ |) z: D, Y$ b* V4 mfrom 表名
; m" E) u) Z3 T! z1 x& ]where 条件
$ q" a0 s, B- R0 T9 i& @ }1 Ogroup by 列名9 J) ?2 k" T: I5 o* p
having 带组函数的条件
9 ^1 C" _& C# D$ V! D' ~* Yorder by 列名
! F4 ?) q* j( k: e+ U! }% y4 b j* n$ A, W
2.函数8 s* X$ z! B6 u7 G
1)组函数:count / avg / sum / max / min1 L8 q3 W p+ j$ p7 S$ j4 @% E& C
3 `5 l j6 t+ C) F. o; C7 ]
2)单行函数:; p( _8 l4 Q$ _* ^! ~* O
a.字符函数:upper / lower / initcap; {6 ^0 j; L: x, i! F) D/ P
length / lpad / rpad: n x; D* f3 ]. r
3 n5 O* N- u8 s9 d X7 c+ \ l : left ; r: right
1 `% {$ R. D/ q( d: D: b. B( P/ U4 u# I- e
select rpad(ename, 10, '*') from emp_ning;
+ n8 @, Q. s0 D# M
; o0 x. a6 d7 C. \b.数字函数: round / trunc / mod
; @2 U4 a6 u5 h9 Oselect mod(salary, 1000) from emp_ning;& @/ W8 p! v' J8 b# G4 C
. S2 r6 L. m9 Cc.日期函数: months_between / add_months /
( y& Q5 i1 c' w6 v last_day(sysdate)5 g! h# n& }' J ?1 Y$ a- X6 ^4 ^
& V, F& Z- F2 X l% r" l& t+ U/ ~d.转换函数: to_char / to_date / to_number/ r5 o) `8 W- E# e
/ ]% M4 t9 b* C8 F/ p to_char to_number! n2 h: e7 n6 p. z
日期 ---------> 字符 ---------->数字1 C: g* \& v- j3 X! ]
<--------- <---------+ V' u' B" ^4 g+ l: d4 j
to_date to_char
8 X# D/ U+ H8 f- R4 |$ u) c% p/ W8 O. S
( n) Y, o6 m/ K9 p2 ^1 |, i5 Y& T. W where password = '1234' and id = '1001'% S# e- {3 `8 D/ t+ q0 ~
) z8 m( u c+ x select to_number('$7,912,345.67','$9,999,999.99')
+ Z) S- J0 W9 ]2 I: U% _: S K6 q from dual;
8 F4 _$ X6 c+ d
" v2 ?6 r8 i+ @( y: X6 R7912345.671 e1 A- l _8 I4 R2 @
" j$ i+ R3 ^5 J, T
select to_char(7912345.67, '$9,999,999.99')
. T1 e% L$ N3 M" F0 L, mfrom dual; h. s; q9 X' n; |& B: V
& p5 |$ B: K3 H7 k# G1 k! A$ l$7,912,345.67, T, |) v4 o. t$ e; P/ F- T6 v1 X
! i8 H9 j4 C& Z1 S/ H2 ]
e.通用函数:nvl / coalesce / decode
- z+ f( p5 R' Z9 K5 ~3 D4 M- t9 A! ^7 h- s0 c( E) I
使用频率比较高的单行函数:
, [; S' P) A' t ?7 V4 Qupper / round / to_char / to_date / nvl
7 Y! `1 C& n: O+ Z$ x: o
" `! }2 z t+ L1 `- P4 v
$ Z4 ?6 @% i' j一、子查询
9 I8 m: f& i* T1.谁的薪水比张无忌高?) F+ M2 c( |, b( y
子查询
, f6 c4 g+ p! K* N9 J1 Z7 J1 cselect salary from emp_ning5 [5 p: m! \; ^% |' F. @; w
where ename = '张无忌';1 q1 e+ p L: @! D r5 `
9 T6 Q7 m; d* K1 P. f( D
10000
* O# x7 X) |1 D: U& |7 \9 }8 Q/ z& |1 z
主查询
; T( T% ?& K' ?( \! H/ wselect ename from emp_ning
. J, s1 b# W, J: k6 B5 q+ W, `where salary > 10000;% o5 b/ |+ p' i2 n
$ h2 P4 d, t! p, \( \# R) h: t
张三丰
t+ u8 |' ?! W+ l9 w! L7 ^% s# o+ a; n* C! U
合并到一条SQL语句:
" u* w9 W) a% ^. t7 E$ `" Kselect ename from emp_ning
2 K. j* ]5 B, n5 Wwhere salary > (select salary from emp_ning; X* j2 w/ l, M8 R/ u' I3 D
where ename = '张无忌');
]1 }* a+ Q& B0 {0 W6 c" p" ^/ P# q% c9 M! ]
> < >= <= = <>: 单行比较运算符,
! ~- g* U: T8 W. a( \2 Y( L只能和一个数字比较。
, o0 X# @) |% @3 }- y: E6 `: |$ b7 j5 W" a
insert into emp_ning(empno, ename, salary)3 Z' H; C0 `! H$ O; b; L6 y. @% V
values(1014, '张无忌', 8000);
2 Y' b+ F- V$ [1 F8 \* F' Z) H+ W- v; ~( @+ X
谁的薪水比所有叫张无忌的人的薪水都高?大于最大
3 T+ b- |4 ?: n1 P) |! Q3 K5 {10000
8 E! h1 o5 e! X0 X( k1 z5 t: h8000) ^- ] y* }" [; g
+ o3 P' Y4 F% k9 a! i3 A, B
select ename from emp_ning
; ?* x5 |1 ~5 R$ v9 H7 fwhere salary > ALL(select salary from emp_ning# ?# @1 |6 D+ x3 t
where ename = '张无忌');
o! m8 ]; W0 E5 `" Q$ V, Q4 m& e0 t9 e
哪些人的薪水比任何一个叫张无忌的人高都行?大于最小" W8 e: H, v1 o7 t
10000: ~. J5 Z! t( D2 ?2 n% N
8000 G. [- @% t9 O2 j( J
6 P ]6 T- L- W
select ename from emp_ning4 C- z5 X% Y0 n/ c8 n) O2 L
where salary > ANY(select salary from emp_ning7 U# L1 V$ O. X; x
where ename = '张无忌');7 m) S6 f# Z5 M/ ?
# |: t! c$ a% e3 C) X' y
2.谁和刘苍松同部门?列出除了刘苍松之外的员工名字。
/ j5 W4 G: `" v; h6 S8 Jselect deptno from emp_ning
. b: x8 C; [7 Z( s, x+ [. [ Lwhere ename = '刘苍松';$ C! x+ t7 W; x& A6 c+ j! \
10
) Y" x. U5 Q u. ?0 a, S8 z2 [4 r) E& d3 B z. u6 H% j; I
select ename from emp_ning' C( Y9 Z0 C4 ?) ]' D, e
where deptno = 10: ]4 Z2 R% r, ]$ r" W: L/ H& Y
and ename <> '刘苍松';
0 D- J- V2 }; [" U! H& O% u/ y X5 A+ P2 P# u5 X9 p' _
select ename,salary,job
& `" x2 @" A0 v3 z5 h' ~, |from emp_ning8 E$ B8 J9 N2 Q {7 P- C6 u
where deptno = (select deptno from emp_ning
& g$ }1 O) d) [ where ename = '刘苍松')( }6 d/ f& ^2 F
and ename <> '刘苍松';
! `& u: j# i, K% x' ^% q
- m/ X. r. `4 f" M--如果子查询得到的结果是多个,不能使用单行比较运算符
1 W1 l& T8 Q3 q3 G3 w& [$ m等号。改为:in
. |, R5 R7 ?( @, Ninsert into emp_ning(empno, ename, deptno)
/ `' M; R: g& x: J8 |& gvalues(1015, '刘苍松', 20);8 M. j! N" s% r
* c: f6 Z$ A Y& a% r3 C) P
select ename,salary,job,deptno
$ M1 O, n5 |/ C$ M5 u* h! w! [from emp_ning# e1 d( R; P" t& ]
where deptno in (select deptno from emp_ning* ?- M6 j8 K5 ?% T5 S- c( {' U
where ename = '刘苍松')- g' R( \2 S+ j9 u. V
and ename <> '刘苍松';1 _' q6 J- O2 K/ W& [
7 Z7 v3 j/ A. c9 F3 e+ B根据子查询返回的结果的行数:
5 U$ l- j1 C+ Q! a/ h5 K* l返回一行:> < >= <= = <>1 E5 n' s1 j$ D, y
返回多行:>ALL >ANY <ALL <ANY in
2 E( o6 R4 s+ b) _5 X" b! ^! s7 }* P
3.每个部门拿最高薪水的是谁?
) y4 l1 M8 Z6 E5 ^! R! O--返回多行
9 a+ ~. o% G9 q% t3 x" b8 Cselect deptno, max(salary)
( {/ p5 \5 R! y% h# O9 gfrom emp_ning/ |7 e& ~, q( S; v% S
where deptno is not null* ?6 f# R8 L- p; Z0 R1 A
group by deptno;
! V( i$ @( i5 p
6 L3 Z5 r8 U- b2 t! \6 V6 S 30 5000
6 e* b% r- r, T$ g4 Y 20 15000
9 x$ ]5 G0 o# Y3 V 10 10000
9 z( w# S. G: r* a% A% O5 f& l* ]+ Y0 W$ D5 |8 M. V6 q: r
select ename, salary, job, deptno" S+ h) J% q1 N$ h$ \
from emp_ning
- Z3 P8 O; O5 r3 l O% K, D! Uwhere (deptno, salary)
0 F, P$ ]+ }/ N1 F& ^/ r in (select deptno, max(salary)- P3 @, f) M! M( i8 m i9 b
from emp_ning; C. ~: h1 v: G! g- k
where deptno is not null' ~* N& q l0 [3 ^ U
group by deptno);
1 m5 M( s2 C0 r: v
& B% x8 p2 b/ P! Y% W- ]) J+ Z7 c& M4.哪个部门的人数比部门30的人数多?! \7 ~& U9 |3 G5 q
select count(*) from emp_ning
: m' p% ^) X- N' P+ C: swhere deptno = 30;; \. E- H# { i
30 C. a8 t( o; M; Z$ B4 J7 S' j. {5 M
! g; v/ a. ~1 d/ ~: Nselect deptno, count(*)
5 R5 d" S2 S$ Y* J, @/ d# G' [from emp_ning
* U, K% I8 ]3 x% n# C; ?: @! Kgroup by deptno0 r, b/ D, u- ~* N& S
having count(*) > (select count(*) from emp_ning
. v) m9 Z! H$ h ^ where deptno = 30);/ u2 c0 W) v `9 b+ X' x L
9 V# p- p! j: h
5.哪个部门的平均薪水比部门20的平均薪水高?
. q# A+ R* H% y" G9 T, U1 uselect deptno, avg(nvl(salary,0)) avg_s7 P% a/ ^6 f* S9 q
from emp_ning
$ i( {+ X0 Q1 z! _7 f0 \$ Fgroup by deptno4 ]( I/ K* }$ ^3 O) c( s5 l4 l
having avg(nvl(salary,0)) >* S/ t3 o O, y
(select avg(nvl(salary,0))
) ]( N! f+ G2 `% y! c: m2 ]1 ^) I7 L ? from emp_ning
4 P$ O0 i& {5 y; w3 {0 U- s( r where deptno = 20);
9 i% \8 D# K/ u: |) d- i) x; |- d3 [% h! w" _
6.列出员工名字和职位,这些员工所在的部门平均薪水 Y/ ?+ k3 O, s1 X% c9 c$ `- R; }7 O9 Q
大于5000元。, i6 H! l' L8 b
select deptno, avg(nvl(salary,0)): J" A4 x3 u1 a/ b# O
from emp_ning' T* `- B* |. x
group by deptno
- u. y; \; D- B: @having avg(nvl(salary,0)) > 5000;
: I$ N: A2 [* B0 D/ d5 T10 5750
[# @7 T: a3 Z9 K20 8000
9 o: |) \2 o; E, V) V- Q6 o1 n
; v3 o4 w. e9 {9 p" e: K) I+ Bselect ename, job J. o8 b6 U; _
from emp_ning- o# g+ P: N* P7 e* R6 R& K, |
where deptno in (select deptno
3 ]+ y# s# t- j from emp_ning/ Y9 N! y) _. o/ S
group by deptno7 h, {+ I$ K! k
having avg(nvl(salary,0)) > 5000);
4 @: H+ k# a5 J! x' C) Y6 v
; h U8 ]( h' f c8 `, b; |7.谁是张无忌的下属?
. w p$ R7 A. Z: J- [select empno from emp_ning. M! S( c& g, A4 L8 k2 O
where ename = '张无忌';% z2 b) E2 a* N
1001' ^ a9 V4 ?8 f- |, ~* ^7 }
1014- Z5 _+ V% Y- }( v+ l N2 r
- ]- C, Y `8 W" a9 C# E& z
select ename from emp_ning
8 F6 I& E" p$ Swhere mgr in (1001, 1014);
4 j! E& J( h% l/ |* q7 j3 S" I& X4 y0 F# W2 L9 K0 k* o
select ename from emp_ning
- d, t& ^- e( U3 F! twhere mgr in (select empno from emp_ning9 \, a2 J* {1 a& H" `% l
where ename = '张无忌');( a6 q6 y: ]' R9 o
: K2 G2 V1 C" ]* {5 w9 n: u: J8 t
8.研发部有哪些职位?' O' Z: A3 h, @% O8 j+ n
select distinct job from emp_ning# B/ n! W! O7 @1 |7 _# R# R- q
where deptno = (select deptno/ V/ G: w4 d# f
from dept_ning/ K! t8 x' F# m4 @8 c
where dname = '研发部');
. }+ B8 \0 B; Y9 q+ p+ G& m' {1 d* \2 S5 B7 M# ?* i, B
- U( ] \* K/ C6 d
|
|