该用户从未签到
|
复习:
# z) v7 r) s! n" p0 X1.查询语句的基本格式。
7 E, w, ?; P$ d3 Jselect ...
% j3 Q, W$ E$ B- S0 S' dfrom 表名
9 J. I4 \6 D" g7 s( [* wwhere 条件
! k3 n J. l3 G! @/ m/ h8 R; U& \group by 列名
" z( F0 q; {# m- i$ R6 D2 Dhaving 带组函数的条件
/ k' D4 a0 `! b7 }order by 列名, H) \9 m8 ~, l6 J3 X
$ m/ k& H) m* r+ {1 v& M3 x2.函数; {( H g6 \4 j+ M3 ~% ]* Q2 P
1)组函数:count / avg / sum / max / min
* D" n5 F7 L6 W# h6 t
7 [; r$ z$ W+ r- E8 n. A2)单行函数:% ]0 ]: H/ e# Q7 y; u8 v
a.字符函数:upper / lower / initcap
t+ p: R3 Y1 i3 _* B0 u7 F length / lpad / rpad
! Q, @2 K' { c8 m! Q
0 _2 M. G+ }, f# M% z l : left ; r: right3 T- j! W. u7 b
7 U; Z: C) W P0 `select rpad(ename, 10, '*') from emp_ning;
3 a" S* C4 f9 Q2 @3 z+ {8 X3 O1 g j0 j" ?; }% h
b.数字函数: round / trunc / mod$ w5 T- r$ R: F; M7 w
select mod(salary, 1000) from emp_ning;
" D! w8 Y8 \# M2 I f' z
a$ ] W! h: @* Uc.日期函数: months_between / add_months / : E2 n: Q- `6 m/ ~' I
last_day(sysdate)
# ~1 @7 P; r0 [+ V: j( h, m
- g. h: A6 T1 X3 _8 j* L4 |d.转换函数: to_char / to_date / to_number; x ^' W0 w9 w4 _2 \+ y! _# s
( M3 r* O! {6 M2 `1 P- u( u' k
to_char to_number! i/ e0 u, Q; t' E4 ?$ O, e/ W
日期 ---------> 字符 ---------->数字; S2 E$ K' R, T
<--------- <---------
7 a1 G$ c; C- J9 k( c) X to_date to_char& b7 X& H' H# c% q% f# Z& L! _
) t) i# K) k. ?& X7 z
0 H( J5 x: ^. o( Q2 v
where password = '1234' and id = '1001'' i, C4 m1 g3 h" N5 Y
7 x/ T, w, }; {: p5 C
select to_number('$7,912,345.67','$9,999,999.99')& m0 [7 k5 r! y
from dual;
) {+ h) c3 Z j6 ?+ H* r' S0 \0 t" s3 C( c8 r8 P4 y6 }
7912345.67
7 y8 K0 f/ q J
# }2 b; k9 V/ O' j3 \/ `" \select to_char(7912345.67, '$9,999,999.99'): d/ ^5 f! T$ D! ^( [+ V
from dual;
, z# `$ B1 w6 s' {2 S M
" ?' P4 t2 l3 D' ?$7,912,345.67
# o- f" O P( a5 \$ q1 X. g
+ z. x J6 E, ?. le.通用函数:nvl / coalesce / decode6 M; Y; R% ~6 U1 P; n3 T8 L. _7 t) X# q
, C2 @0 {6 A! w# E0 r3 c; d7 K使用频率比较高的单行函数:) q4 a. l" i' x5 [5 X* I2 `9 D4 j
upper / round / to_char / to_date / nvl
" E9 {# x# ^% a" A1 F5 d- J5 a/ w, P9 G2 T) u
9 f, r- E6 F4 T" P+ e0 W! q9 s一、子查询* t6 V3 K+ `- V
1.谁的薪水比张无忌高?' f3 n& u7 L: A: ~
子查询
! g+ q, p: a m6 V- S3 Mselect salary from emp_ning
# [: |8 N/ Q- Q4 @1 `% p. `where ename = '张无忌';0 B6 p+ J% L$ s5 m5 V$ u4 Z
+ T0 F. i% S: G3 l! \
10000
: } B* Y: Z* g6 ]0 t c, O/ M9 A4 b/ l+ }' c0 E) _: u
主查询
3 _* l! d; M& Jselect ename from emp_ning0 z) H! R" t5 c! ?3 ~$ A
where salary > 10000;
4 m4 y* z# `- e* \: A* S1 G% t; j8 k* e$ f# W9 F
张三丰4 h- f6 h# M2 G
+ t6 P4 e! z) g5 p/ C合并到一条SQL语句:* S" i) O" v# H x$ x+ V6 ~" O6 z
select ename from emp_ning
& g) F; O" P% S* Z* R3 N) e R8 a4 cwhere salary > (select salary from emp_ning
0 n3 ?) B. w% ?* P- ^% p where ename = '张无忌');) r4 B8 q6 `: k+ E
5 v- s0 R( h5 Z$ ^: s# [> < >= <= = <>: 单行比较运算符,9 L$ ]4 \" ~1 d% ?
只能和一个数字比较。
/ P ~- W4 I$ O9 T2 ?1 j7 k7 c; s" e
, D. w% S& z5 ~0 x; Dinsert into emp_ning(empno, ename, salary)
& e/ n2 Z$ L" L; ^: {1 i+ p$ Wvalues(1014, '张无忌', 8000);+ a; ~# ^- r: y
/ M$ b9 p9 J* H+ z+ d谁的薪水比所有叫张无忌的人的薪水都高?大于最大
G$ n T7 ?" h5 X' Y& f4 s" q10000/ |) A# D9 P7 Q
8000# P: k. g: @1 a: K u
/ s' B. S9 R3 U# g- C
select ename from emp_ning% S5 z" M9 j S& M, A
where salary > ALL(select salary from emp_ning
4 O+ N" D: ]2 V/ p& Q8 q+ J where ename = '张无忌');) ]* K- C. f: ~; n# q9 l
$ a) ?$ u$ C, @7 Y! \, q1 J! @& g哪些人的薪水比任何一个叫张无忌的人高都行?大于最小
$ }5 K5 Q1 V& s' T# P0 [9 n10000
3 b( K3 f/ J1 Q9 ~3 C8000
# z& I6 w% u9 ]9 p9 v7 {. K& ~6 o. X" k; g, d/ E
select ename from emp_ning
. s9 T# J' X8 r% l/ cwhere salary > ANY(select salary from emp_ning
2 Z0 G' v, m( F where ename = '张无忌');
" x; ?7 {% M! A- U
4 p9 y: @8 Q& q- a5 @2 ?. I$ K2.谁和刘苍松同部门?列出除了刘苍松之外的员工名字。9 G& T' k8 ^1 X5 d$ o
select deptno from emp_ning+ S: y& Q" H0 Y/ T
where ename = '刘苍松';/ ?: N7 `% P5 z' O+ l7 y
102 n+ `3 ]+ u1 \- [" T
* {3 s! h! A" V1 V9 ^0 W
select ename from emp_ning
6 H* t$ Z0 S" owhere deptno = 101 @+ L; l0 K4 j: Y* ]( W1 m6 B
and ename <> '刘苍松';# E& `$ S/ Q, \' F3 B$ z7 `# {
1 d" E* {: n' ?$ a
select ename,salary,job
" N7 A. b1 i8 e3 z: Y# hfrom emp_ning
: I& }4 w4 t; r$ ~where deptno = (select deptno from emp_ning
8 r3 ?8 ]7 i6 W# [$ ?, V! a where ename = '刘苍松')* d- J9 B4 K; L, _; T& b L1 g! [
and ename <> '刘苍松';
) C" v% A3 ~/ g% n4 v- r% |1 \( J! D5 q
--如果子查询得到的结果是多个,不能使用单行比较运算符- Q( J/ C; W1 `
等号。改为:in
# B0 x2 A! {9 S& F; Dinsert into emp_ning(empno, ename, deptno)
' ?/ Q5 X7 a; G: i y6 p, {values(1015, '刘苍松', 20);2 p6 \; ]; R4 e8 W1 X$ u: _) c
4 [: u4 H' x% p
select ename,salary,job,deptno
+ Q U' y% B9 Z# A9 o6 \. kfrom emp_ning
% q5 D4 N2 }( j% P( n/ K, Swhere deptno in (select deptno from emp_ning4 }; P, R: s7 @6 n E/ P6 P
where ename = '刘苍松')
" P# \/ P4 ~' W* u' Mand ename <> '刘苍松';: H0 z1 }: R v: X
u" d3 h8 p. `5 u, K" w* Z根据子查询返回的结果的行数:
6 \7 f3 A! g6 r7 |返回一行:> < >= <= = <>, U7 ?1 P8 C- }
返回多行:>ALL >ANY <ALL <ANY in
7 D: |, y- ~1 M: S% s# z. N7 P+ W+ m, K! j
3.每个部门拿最高薪水的是谁?7 @! |) N$ R0 X
--返回多行6 g$ D! l# u' u; M. a( x l
select deptno, max(salary)
6 w V: G7 e4 x/ tfrom emp_ning
/ D! A6 f$ E2 j3 n; J* ?where deptno is not null
M- N% ~( s# Mgroup by deptno;4 U, R2 X1 ]& W. \1 C- N5 d( z
( w0 Z3 X$ a/ V# `* ~8 | 30 5000) ^$ F% Z7 D1 E% r5 @7 a/ W
20 15000" V$ n4 I( ?* R1 U7 R
10 10000
* K- W% C2 h% s6 D& O
' W5 [. l% g7 b, f5 H1 f( z% p ?select ename, salary, job, deptno. F0 Z2 M2 U) T/ O/ V% z; k3 D
from emp_ning; v, G$ T' e& C' C
where (deptno, salary)
# q6 ~/ X1 f6 C% {# L in (select deptno, max(salary)
: N" ^- z; i P: U9 e from emp_ning
. ^5 I4 d+ h" M) [; M m where deptno is not null4 y% f% F& D- O& Y1 u1 g5 N2 N
group by deptno);
5 g R/ o, k, Z( L' v& R$ M
3 v) h! k' s, ]7 ?' J4.哪个部门的人数比部门30的人数多?* o6 ] p3 w$ h |
select count(*) from emp_ning
% P/ O" Y( m/ ]' G- ~- Twhere deptno = 30;( `8 E- O v5 z
3' Z+ ?3 Y9 h: D2 G ]# U
* I" Y" K- j; \5 }$ N' P7 X/ z
select deptno, count(*)3 K! s1 W2 k' z2 K; {& S
from emp_ning4 R( g9 Q7 B6 B" {& N$ z! f( ]. Q
group by deptno
* r6 ~5 p8 f+ j. h4 W" Ahaving count(*) > (select count(*) from emp_ning
/ ^9 y/ r1 a( B where deptno = 30);
& @5 @, D9 Z; ?1 J
3 V) |7 Q7 |) e; ^7 C5.哪个部门的平均薪水比部门20的平均薪水高?
* o3 K. Z3 [; e( {" |select deptno, avg(nvl(salary,0)) avg_s6 t" `7 Y. x1 _" X% Z% x
from emp_ning0 U# A3 A/ H: W/ O
group by deptno
9 D4 e j3 y, i0 y; F: O" Ehaving avg(nvl(salary,0)) >
5 h2 @* x7 }, I) Y4 Q (select avg(nvl(salary,0))/ j) x( D$ U' p9 j' Y
from emp_ning) r/ z1 ~0 v. B+ [* `2 n
where deptno = 20);
+ p, d- d2 b8 g3 P2 X& I% v7 m& [3 ~2 [
6.列出员工名字和职位,这些员工所在的部门平均薪水
1 \) ^3 V9 x$ `! @- x大于5000元。1 L" [" S: k6 P+ v* Z
select deptno, avg(nvl(salary,0))
) L) T" G( ^4 ^9 u0 J4 h% Ffrom emp_ning
- q& X) n) b5 {group by deptno1 ]- p6 f/ `0 i1 p$ l
having avg(nvl(salary,0)) > 5000;
- y% `& s" t+ U$ o: M10 5750
5 s% Y# r' {1 u1 h20 8000
9 C7 \+ E C" A) N6 [, o G0 [0 J5 _* o9 U' }- D& t! Y. z
select ename, job
4 d* B0 _# ?" V( k5 X& N) [from emp_ning+ ?6 h; H5 V( C
where deptno in (select deptno
; I1 Y. O5 J& k! l( e. A from emp_ning
5 l2 q1 y' g) u5 S2 I; R group by deptno2 `) f' t% w* R* _$ P
having avg(nvl(salary,0)) > 5000);
# a* H' P) z2 E) e0 ~3 ]: x r
* U/ X2 L" N2 a/ k7.谁是张无忌的下属?& q4 X3 \: B$ _* R
select empno from emp_ning4 _+ y) h S1 i
where ename = '张无忌'; `$ W( n D+ w& }5 Z
1001- c9 B" g6 B: D
1014' [* ]. N& o( ?; R- }; D1 L; \
* T6 G! F) M' x
select ename from emp_ning. s; p6 r7 m3 q6 {2 e* i; [9 e
where mgr in (1001, 1014);
2 H7 f% b: q5 k! P
8 H$ U: \5 `9 G$ T6 gselect ename from emp_ning0 \. h7 { O: g: I) o
where mgr in (select empno from emp_ning+ V+ L7 H. p1 J) w9 C6 t' }
where ename = '张无忌');
9 s/ a8 |: t/ E1 ]% U, m' n4 @ _5 Q7 Y) u# X5 p% m) R
8.研发部有哪些职位?- B. ^% S% A/ [
select distinct job from emp_ning! K7 }$ r6 [$ O% I$ I2 w! f
where deptno = (select deptno
- P# e) b- p) R% m# H7 S from dept_ning
* d% r# |% Q- y5 [8 C where dname = '研发部');
c4 N5 i o* R
2 }/ b5 W. R9 i4 q+ _7 }6 ]# Z; z5 o
+ a' R& q2 V5 C4 }) ^) w" y |
|