该用户从未签到
|
复习:% @5 ~1 d! v4 E7 s0 X
1.查询语句的基本格式。
' q" @4 `% S+ s$ B" n! J7 Y1 \select ...
4 E2 O) [( Z7 v. Z$ c6 A9 ?2 Cfrom 表名
+ I- g/ e2 J' C& S: k0 { wwhere 条件. v' Z, u a0 B6 C- s q$ u' K& {
group by 列名# X. W9 v2 {0 r4 p. g8 ]
having 带组函数的条件/ {" C6 e. m8 c: y; [' r
order by 列名
& G& u0 _; S: l8 X' h$ `7 n" f2 Y4 z7 w+ ~. @0 G. l, W
2.函数
, @8 f4 W" m- J. O7 ?' M1)组函数:count / avg / sum / max / min
# y! E1 u& E. n9 I/ R; e- i7 |+ A4 u; U9 f! U& v; F8 C, v
2)单行函数:
1 q* k; w- ^5 {- \) L5 |: [a.字符函数:upper / lower / initcap
) T7 i0 G. @. e length / lpad / rpad" W7 t, H7 |6 H. p7 ^
6 I9 J2 B+ S! ?1 V; W7 M
l : left ; r: right2 s, o( @9 P; s2 W
5 S% r" t& e+ W* d& x' l7 Sselect rpad(ename, 10, '*') from emp_ning;
4 t: T4 [: @7 F o8 M5 K% U" a* \' j! n5 k' x0 W' \/ n* v1 m3 G+ r
b.数字函数: round / trunc / mod
- E8 W7 k }3 F) p' M5 eselect mod(salary, 1000) from emp_ning;
: N; C* D/ X( s6 n# {7 y/ s' g
( X' I) [/ c- X& f; j; Yc.日期函数: months_between / add_months /
# G+ S" x0 E+ |7 H7 x last_day(sysdate)
- {2 j0 @7 f$ Z0 o! a8 R* a2 [0 z* y7 s, n# D4 t& W5 z1 @
d.转换函数: to_char / to_date / to_number" C4 R' L X P! j
9 H$ v* X: {: c
to_char to_number* }+ I, c. w+ o2 z# I
日期 ---------> 字符 ---------->数字
0 Z/ ]& i& W( }' A: ^ @4 g5 m <--------- <---------: [/ \! i9 u/ r0 w0 ~# d+ J! t
to_date to_char" V Q# [9 r3 [+ m/ b% C
; k! W5 T5 C, y: |4 M" O) { G
- ^/ }5 S* h6 k! x5 i' I' }3 T where password = '1234' and id = '1001'
N7 j" Q) P3 | l+ G: H0 y# [/ `/ l) D' \7 u/ u* z
select to_number('$7,912,345.67','$9,999,999.99')
" b% L/ M: z1 r/ J3 C from dual;
& X+ p3 q# x+ T. e1 {) D
/ B/ o$ S T( z; Q7912345.67
3 N9 G7 Z4 D1 z* K3 x9 G
# |' @* G0 F+ J/ {! Vselect to_char(7912345.67, '$9,999,999.99')
, |# j2 A$ w* r7 V1 j. E6 L7 sfrom dual;
- e0 @$ e/ p1 {
4 j. {8 y- Q0 i" a/ x$7,912,345.67
/ H1 J& N0 l* e5 t
% m# w$ j4 I. Q7 G3 P S, i. de.通用函数:nvl / coalesce / decode, P5 }# O: Y% A
( e( S1 q' W @1 \使用频率比较高的单行函数:/ _) N2 U8 Y- }" ^, l L
upper / round / to_char / to_date / nvl
# y. r5 A& U7 ~$ J! n1 L5 Q; ?0 i- l
( @) r w" q% ?7 D( ~1 M# K9 f
一、子查询, i: r. B5 b. @$ B
1.谁的薪水比张无忌高?' Q, G) Q) ] M& @6 Q
子查询$ g8 `$ S0 \; K! t0 X/ g
select salary from emp_ning0 h, I" B) v( Q4 Q3 Q' G9 k# g
where ename = '张无忌';% v% n" v G6 H) O1 b7 [
& _" A" z6 j# C; d' G$ @
10000
% Z0 n6 B, }) }/ O
; z. ^* Z* Z/ \: w, P主查询( q, V* x: R$ @" f
select ename from emp_ning o+ S6 ]3 u' U/ {: ^1 Q |
where salary > 10000;& |' c6 o8 R) {8 S: M
@& b# |+ s% j! m' Q/ a5 p张三丰( g7 i3 x- J/ s9 @) ~, H j, w1 A
, s4 ~, x6 R, g0 t合并到一条SQL语句:
& H! i1 ], h7 }4 P. A( o2 _; I0 uselect ename from emp_ning$ Y& L0 g; X+ _. O7 `( x
where salary > (select salary from emp_ning& Z! ]' n) H. c: Y, q( s
where ename = '张无忌');
9 ^2 r/ s2 ]' Q. R8 q5 q" l. p3 K" O: U7 F& T
> < >= <= = <>: 单行比较运算符,' M; P( _+ I. ]! U! f) \8 w
只能和一个数字比较。
( h, A: e) A% U% u5 c4 j" {& w. e8 |9 ]9 W" c9 H4 R
insert into emp_ning(empno, ename, salary)1 R+ l* Z7 C5 |
values(1014, '张无忌', 8000);4 R& Q2 i8 [# B1 `' A" P% Q: r
- m' m( g4 N; H谁的薪水比所有叫张无忌的人的薪水都高?大于最大 N8 k5 w$ L( H
10000$ L4 X2 o, \' D
80001 x/ f0 N; b5 \5 Y; S$ L- p; w3 L. ^+ I
' M7 h7 {! u5 S+ w' v0 ^2 x& ^1 g: ~select ename from emp_ning+ T) Q! {" j6 c% D# P
where salary > ALL(select salary from emp_ning# P% w6 ~. u1 b Y
where ename = '张无忌'); D3 m Y1 j; F& {4 U2 [
5 [$ Q( c, _ d7 l$ J$ [
哪些人的薪水比任何一个叫张无忌的人高都行?大于最小' c" g, b i7 t
10000
4 A7 I* X( U& j* U8000
8 S( z# E5 k$ o# h+ @5 g( `% Y6 H( C: W. I
select ename from emp_ning
- o. Z! t0 W/ zwhere salary > ANY(select salary from emp_ning
5 S7 Q% A3 `8 S! \+ {4 E5 n where ename = '张无忌');( J9 g7 z7 h8 H0 F: E8 ~5 Q9 H# @
1 s( q; D- N$ g
2.谁和刘苍松同部门?列出除了刘苍松之外的员工名字。. s5 ]2 r0 b3 r9 t) i
select deptno from emp_ning
* G6 `" A) ^6 X* W- O! q6 Kwhere ename = '刘苍松';
' O. ]' R1 v7 ]" h+ m10
% r! Y1 X, j' S5 R+ O5 g/ R! Q8 h$ k% L, R
select ename from emp_ning
, W5 t v1 {& f5 swhere deptno = 10$ @5 e/ S9 U" [& U. {$ s* r
and ename <> '刘苍松';3 ^* C, u1 a v7 o) u/ w
* n1 W! C- }% Y/ w/ C8 Eselect ename,salary,job
9 k5 w# E$ v5 ~8 N5 i& T$ A; rfrom emp_ning
1 v+ |9 o) a; L' g7 Zwhere deptno = (select deptno from emp_ning" n; K; S$ M% [* U/ d8 t* D
where ename = '刘苍松')' S' e# z& |/ o! \# U
and ename <> '刘苍松';. `% W5 L5 b7 ^1 F
8 ^" a8 c2 b, F. ^1 S
--如果子查询得到的结果是多个,不能使用单行比较运算符' p: b5 i5 x* t! l: i& `; ?
等号。改为:in
" Y# k4 M& D0 N2 D) K, L( ^insert into emp_ning(empno, ename, deptno) M- ^; n/ A+ {6 m
values(1015, '刘苍松', 20);
1 E& O% ?$ o$ X1 H! q! M+ T; i% h% |( X. B4 o
select ename,salary,job,deptno
1 C/ ?8 ?* \/ o6 jfrom emp_ning. k0 i1 o4 ^. _) y) }) y
where deptno in (select deptno from emp_ning
% c9 M) P9 w- Z, ?2 U& R0 B; _0 A where ename = '刘苍松')2 d& J( J0 u+ i( d1 u
and ename <> '刘苍松';
+ ], Y, s+ \/ b( v' j* D( G' [" B V8 a d2 ^
根据子查询返回的结果的行数:+ x9 J( Z8 T: b% x7 z0 r' j% X2 U
返回一行:> < >= <= = <>) Z* e! z4 n! G r2 f2 |2 f( ^
返回多行:>ALL >ANY <ALL <ANY in1 v$ J. k& J# p: k8 M6 R
0 Y) ^, y2 S4 ~' k+ ~; V3.每个部门拿最高薪水的是谁?
0 D$ s% i7 \8 e2 O& k/ _. i--返回多行& j6 U3 g/ C: q) L1 l
select deptno, max(salary)
% l0 U$ C8 c, t" D% }from emp_ning
" q+ J$ `$ v( i% G" \( n; k. Qwhere deptno is not null, W' }3 @3 K s B: Y& w
group by deptno;- J! I9 Q7 ]$ P
" A9 [; ]" |8 T7 `' Z: D" V 30 5000
1 @% {/ m, \% X& a 20 15000) `+ S Y. e: z! G2 @! `
10 100005 h* R& r0 f( _% e2 E/ [* \
% \3 ^2 x" r$ z. D- t6 V
select ename, salary, job, deptno- P7 q, p1 h- f0 E( r# m4 ^
from emp_ning
, y0 K5 S$ F2 f2 Z% K }. qwhere (deptno, salary)
' o& c. g0 B8 }0 p in (select deptno, max(salary)( t/ E! P4 b( a7 n" u$ c
from emp_ning
2 G0 a7 D/ d- |$ K1 t% b% c where deptno is not null4 Z! x }% g l5 I' a3 J
group by deptno);
# R0 B' U$ c0 G2 Y
- n$ @: s" u/ E/ V4.哪个部门的人数比部门30的人数多?
: {$ F# x$ X* ?select count(*) from emp_ning
9 ?* t- C8 g0 d) J" \$ Lwhere deptno = 30;
. j q% s! p! E; m# e# @4 D1 z) u" V2 u38 J/ L% ` X- {% F' N
/ J! Y; }- O% F) Q. S6 S5 Z7 Rselect deptno, count(*)7 G0 y! V6 t. l; q
from emp_ning
! `: i+ F' F2 k: n* j$ e. Pgroup by deptno
. R7 i( A. F9 H: Lhaving count(*) > (select count(*) from emp_ning
1 {6 q6 p4 N1 [/ i where deptno = 30);
5 R5 L2 Y- J* F* d
5 O$ [3 H' d- A. E% v* [. ]5.哪个部门的平均薪水比部门20的平均薪水高?' @$ k, Y, M( @6 t8 V+ M; r) V) E N
select deptno, avg(nvl(salary,0)) avg_s7 s2 s8 m" X+ C+ s7 _$ v! i
from emp_ning' q. p. q! q" X% t* c; Y
group by deptno: u' _; y- `! {3 T* M" a9 n8 c
having avg(nvl(salary,0)) >
3 | k7 m) G" o' ~* Q1 \+ X/ P (select avg(nvl(salary,0)) n0 \/ M6 Q8 S; a0 q
from emp_ning2 Y8 n: Q. B5 I4 m9 D
where deptno = 20);7 t1 a* F/ N" s7 t1 P
" `! ]( D) ^6 W$ P0 k+ o- E6.列出员工名字和职位,这些员工所在的部门平均薪水
1 G b. m) g: P- J. ?大于5000元。$ n4 d, t' j, c8 ^
select deptno, avg(nvl(salary,0))
0 t: Q2 C0 J( M: \0 l! ^from emp_ning
+ H$ l9 r- j; a9 Xgroup by deptno {+ o8 ? }7 {3 C+ W
having avg(nvl(salary,0)) > 5000;
+ \0 E, k/ B+ j* a) R1 m! h) i10 5750! g( S# b; K+ F
20 8000
1 J0 F, E, _6 Z n: F9 S4 z W# |! h$ K0 ?2 m9 @, w* {
select ename, job* c9 `( O% w- B, b/ K/ n
from emp_ning
- c( \2 a# @) S9 D" C8 n2 Jwhere deptno in (select deptno
& C4 L( B/ U( K. n$ \. L8 |7 X from emp_ning
- [1 V) q( v- H/ E1 L group by deptno# F: T6 _: N* u0 R2 D
having avg(nvl(salary,0)) > 5000);* }3 `2 Z! C- A' G4 p1 u4 F8 [
7 [$ D( G2 Z+ P: r' B2 Y+ u" E7.谁是张无忌的下属?
7 C; \7 O* O8 o, n Iselect empno from emp_ning
; U( y0 M! A7 G5 fwhere ename = '张无忌';! Y: y6 N+ `' @6 |* W: j+ z- L) @6 H2 X
10010 @% D0 l9 e8 J6 W, T
1014
7 j1 B; V L( a" f$ n4 z* f @1 N5 \' ?% c; E# v* P
select ename from emp_ning; _* A7 @4 u5 a' C5 i
where mgr in (1001, 1014);
7 {5 A8 _7 ^) }3 S& ~: ~7 U3 f9 A' u5 `; ` R
select ename from emp_ning
5 S, N8 J; d$ P' z twhere mgr in (select empno from emp_ning0 O# u( T7 _* S- ]
where ename = '张无忌');3 E( i" w6 p$ e: N3 S9 b3 i: P* H Z
7 A) e# c# j9 I9 z% x
8.研发部有哪些职位?
' ?2 Q" ^7 i1 \; `6 G; Z+ a8 fselect distinct job from emp_ning
+ _; n9 S5 Q4 I) Z9 Y* c2 |# Swhere deptno = (select deptno
& ^( `! t5 E, d( ^1 r; G from dept_ning+ B3 ~+ G; e# A$ j2 }0 D
where dname = '研发部');
+ j. K# l4 G% s6 c6 D
9 f- K' ?$ X' G9 y9 m+ F9 o) P8 k
|
|