% {0 Q# F; Z$ T" U1 l& w. O--复制表emp_YYY为emp_XXX * ?8 [5 y) n) k4 {create table emp_XXX - v) E# k+ b& ] f. P0 Tas2 I) ? A# e7 W6 @+ u% \+ b
select * from emp_YYY; " u% e1 b5 w" L - V( g& _0 f3 c& ?' |! g8 y' p3. 机构中有多少种职位?3 `& g, h' p' A, w5 G4 [6 e p# l
select distinct job from emp_ning; : T, Y3 x! m9 n+ ~/ h6 M 1 h* q7 N/ S- s$ P* P4 ]员工分布在哪些部门? ' X% o6 ?% x% w$ }" v" H$ N) r9 T zselect distinct deptno from emp_ning;- m9 ^: z6 G7 S5 d9 n6 q4 }
- X% l- b+ @/ J/ N8 F* K( Kdistinct必须/只能跟在select后边。: \( D) C' z/ Z) i& y r: z2 q
* W) A" f8 S$ W( @
4.薪水高于10000元的员工数据?0 l2 }# T' ^9 J! d v4 X* G" ^! e. D
select * from emp_ning& D9 e! {* Q1 x& g( g$ I
where salary > 10000; ) e% G+ z% n5 R# Q 5 K9 R9 \3 X- P6 [5.职位是Analyst的员工数据? b ?% t' H! p S' _0 ^' gselect * from emp_ning - U0 `/ g8 T/ {* L4 A2 A2 Vwhere job = 'Analyst';7 l( }7 c. ^; S" Y
]% L Y9 `+ @- Danalyst ANALYST anaLyst + ?* f9 Z! B6 C7 \2 C; p" x" S$ J9 S# E2 O
select * from emp_ning2 ~7 X3 s$ Y/ D! N
where lower(job) = 'analyst';4 N( `8 [% N7 o8 c
1 S; E8 q, s. W% u2 ]select * from emp_ning' V; K1 w& p2 g) h& p2 W
where upper(job) = 'ANALYST';1 {8 N! F$ V4 }& @4 |+ P2 Z
$ u( V( i% w- Z/ ^5 R z2 I' `$ }
--如果数据是analyst,查不出结果; U Z) M) E& f2 y) q' L
--SQL语句大小写不敏感,数据大小写敏感# _: Z2 m) I% [3 ^: o& V$ L
1 D7 y+ }; _ Z p5 d* f6.薪水大于5000并且小于10000的员工数据$ M# v8 h! U" ]3 p+ e% t) }$ Z8 f# Z
? 0 d( Z5 j' Y& l9 ^) ?( I: Zselect * from emp_ning+ V( ^, F I" h1 h- D+ A& G
where salary >= 5000& v8 e+ r! x% x0 w
and salary <= 10000;( @2 S( i! O6 B* l8 V' e6 P5 t
6 K8 {. M3 l" T2 t1 a
--在区间中:between 低值 and 高值, b/ a; g( C* r3 s, a. Q9 z
--闭区间:[低值,高值] 4 x5 h$ F5 Q1 _- o4 Vselect * from emp_ning, l" k) W3 M3 c4 [: X
where salary between 5000 and 10000;/ p3 W2 V0 r8 V% V+ N: W \ Y
8 l5 J- F$ M: d& ]4 r4 V入职时间在2011年的员工? ' V: `7 D$ M% N['01-JAN-11','31-DEC-11']+ L# ~8 b9 j1 x9 k k R
select * from emp_ning h+ Y6 h0 s) g; [where hiredate between '01-JAN-11' / j1 Y. Q; j! ~! Band '31-DEC-11';# J7 L' k' L! v5 `/ K
5 _ y6 M+ p; M0 @! } P C" k" V8 k9 X
7.列出职位是Manager或者Analyst的员工 f4 L5 g7 m; Y) M0 c* a? 1 z$ x8 ]+ y+ Zselect * from emp_ning 2 i; Z+ T- R; S8 D* d/ F, j* ^where job = 'Manager' ' F4 r, W* a x( w B' A' ^0 `or job = 'Analyst';6 D1 e3 `! o# s: B! Q: L0 K
--等价:in (list),在列表中。# |( W/ x, k# h" J! K1 h2 [3 S
select * from emp_ning 1 Z/ _, y' _/ U7 J- X" iwhere job in ('Manager','Analyst');& A& b6 H( ]- B/ Z) {
, l3 d9 m7 q* U" ]; G8.列出职位中有sales字符的员工数据?9 g1 p Z8 B7 a8 o5 L
salesman' K. c2 O9 R" ?/ X
sales0 @' d9 X: _$ _4 U
before sales. Y- u0 a5 f. T c4 @( r
after sales5 F$ Q( u& S( X, b, b) \9 s
before sales engineer # \. c2 @2 V9 Y1 A - j' I0 z' h" N1 O9 U7 M--%: 0到多个字符,跟like配合,模糊匹配 9 m7 _' |+ g- A' k' T0 Xselect * from emp_ning L" _5 j3 r" N5 F2 b) o7 |+ Kwhere job like '%sales%';; P$ P) f3 H( u5 A' J
' q8 ^3 e. m# d, _4 E) k0 |9.查询哪些员工没有奖金? : A/ |/ V3 T1 z' d9 }( Hselect * from emp_ning 7 K% S% ]* \3 Pwhere bonus is null; * c5 z$ Z2 ~4 u) A3 t' q& h9 a0 o' o. s' e$ O7 I
哪些员工有奖金?1 @) b5 x% x/ I" |8 ]
select * from emp_ning& M0 q2 b& N2 T2 b; ^
where bonus is not null;" U4 _) W$ [& V+ r/ f9 |# B0 J
: \' {6 ~7 i H5 Y3 U5 q小结: 8 {; d6 \ b+ Z9 W6 B; p1)create table ... : M6 j* U' q ]$ M drop table 表名; 3 B! N& z' L- w& a' S2)insert into 表名 values(...);( S! i' F) H1 @8 x( L- q9 E
3)select distinct | * | 列名 | 算数表达式 | 别名 * B, }. S% c/ J4 ~' t from 表名 / M+ {# J" |, B. i6 ~ where 条件1 or 条件2 and 条件3;$ v9 O; [' Q5 `: _9 X A, ^
) {9 O. r5 ^0 k4 Z4 d$ }- C> >= < <= between...and... in like- ~2 @; \. F4 K4 ?- g# P
6 H* u3 D3 Q' E1 }' j1 l& G1 m; A1 Q: s