SQL*Plus ! U& y# B7 V1 U% q2 P: wsqlplus.exe ' F% I$ X' a. n) v
+ h/ c8 w+ C5 N/ ]; [! w
1)数据定义语言:create / drop / alter / truncate # W- k0 u# u: ?6 N6 M" b$ c对数据结构起作用。 8 a6 e9 [1 |% \1 N ******** ' x& ^5 [# ?. G% b. |% sData Difinition Language: DDL4 N) \9 J, g7 G: K4 U) f
2)数据操纵语言:insert / update / delete 8 h& a2 l6 A9 g4 f$ @, K1 p对数据起作用 ) ?7 ^) W7 y3 F" B4 T, K ****- {0 u& o, e, n8 z. x5 R0 Q
Data Manipulation Language: DML( T( y* I. s( S
3)数据查询语言:select , P5 B& k) N( j8 ] **** " \* w1 h% q& l8 UData Query Language: DQL4 g0 d7 R1 K7 n8 U, v# V
4)事务控制语句:commit / rollback& k0 l+ j% C/ y4 z. i
对DML操作确认 : n9 f/ q) C0 {' b9 W8 G3 v) a: J) R7 S
一、学习单表查询语句。6 W+ B) \) q4 D
1.计算金额的四舍五入?$ o* `0 l, G, ?# i9 X+ ?# D+ F
select ename, salary * 0.1234567 s1, $ o: {, F+ F5 e' x: @3 w" Fround(salary * 0.1234567, 2) s2,) y- C/ |) ?" l2 ^$ o+ m9 G
round(salary * 0.1234567) s3, |' O1 S8 l. C0 n" b, w% w1 [5 j
trunc(salary * 0.1234567, 2) s4 - P* q1 ]6 ] ?! Bfrom emp_ning; / I# B2 w) D( Z0 W' L. e2 a , z0 p$ R2 @" G( sround(数字, 小数点后的位数):四舍五入% v9 b- {+ R& e$ s/ Z, {& e0 ]
如果没有第二个参数,默认是0. + P& G0 y6 H- S5 q* v$ _* B6 I, k1 [: Q2 I- ~8 L
trunc(数字,小数点后的位数):截取8 q9 l; L6 s1 D- Y; j
如果没有第二个参数,默认是0." D& ]0 ?3 Y$ r" n
4 k* G. \8 ?4 d
2.Oracle中的日期4 Y6 g: {7 z7 Q2 P
1)取系统时间的函数:sysdate " D0 S+ d8 M! z# vselect sysdate from dual;" d9 ?5 ]. ]& D5 S
7 c0 N. z: H4 |6 a6 z- E
2)把时间数据按指定格式输出 7 Y% K# x- p% h2 K( k) {select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') : m5 i, a0 w( Lfrom dual;; ]# v0 ~! _" @$ U& p# b
! C( ?9 A2 ?, f2 Q4 h9 Rselect to_char(sysdate, 'year month dd day dy')! @* _( }3 n' P9 M% {9 r
from dual;. ]2 r/ m5 b+ C7 m3 N
" k! m. g2 _/ b# g$ g* N% fto_char(日期数据,格式) 4 V( a. {3 E. }! i$ R8 g6 p . \- y, ^0 j$ [4 k jpublic String to_char(Date d, String style){ 0 U/ `0 T0 ~( ?# P //SimpleDateFormat把d按照style的格式转变为字符串 5 \- g1 s: s; L8 U$ C' S) A: C return 字符串; % C3 p3 e9 [) B5 K, V: ?' J- q} 3 w) k' l N+ D+ C5 G" n" G
+ v) H0 e' ?' [
select to_char(sysdate, 'yyyy/mm/dd') ! u) }, t {* [7 g7 Xfrom dual; : Q+ D9 I# I" h2 t6 m- Y, K- M* v- q2 h
yyyy: 四位数字年 2011& [9 O$ d- l+ |5 X2 ]
year:全拼的年 twenty eleven3 n# G) W* a8 k+ @5 T5 U3 q
month: 全拼的月 november 中文:11月+ R6 |0 k- L) N
mm: 两位数字月 11 + q1 Q$ T- y9 F1 [6 a* M! t. e) A$ ~! ?mon:简拼的月 nov 4 Q( ^" v& o2 y2 N4 Odd:两位数字日 $ i& a; b& C- kday:全拼的星期 tuesday* {# b' Z- I# `0 d a3 s
dy: 简拼的星期 tue 0 V) l0 U2 Y5 b% G; s/ Iam: 上午/下午 am/pm% u* X8 e) o4 f6 d
) W6 }4 r! |3 |9 p t
sqlplus中日期的默认格式是:DD-MON-RR 9 O7 J$ b. u$ w& Z* X现在的时间是2011年 " p( ?- ^$ k* c9 }- c" a+ y& ?0 m YY RR 8 i7 F1 @6 r& j" K# f4 y- ~05年 2005 2005 5 x. Q: Z3 N( d8 u0 Z* ~: M98年 2098 1998 1 q& B. `9 _# S8 L, h/ ~* R: m: _7 o9 m/ ]* \9 P6 ?8 h
假设现在的时间是1998年1 k. T! A* V5 w1 x: U; P
05年 1905 2005* C& g" S& E4 i- q0 o# A
95年 1995 1995 ) ]. w* k" \: d* V" `0 g, {+ `6 g
1 \! F9 `0 n5 |5 L) }( D9 n: m2 D
insert into emp_ning(empno, ename, hiredate)# r" x5 U$ s- N3 h# c4 ~
values(1012, 'amy', sysdate); / E+ @4 B# E+ Z1 n--实际入职时间是2011-10-10 # K6 q2 A' g( U& |+ Oinsert into emp_ning(empno, ename, hiredate) 7 Y+ l3 r. B9 Y8 [" `values(1012, 'amy', '10-OCT-11'); ! ~' ~% R$ r9 Y, l; ]* c; w # q/ O, n m9 H* r) @1 U8 ginsert into emp_ning(empno, ename, hiredate)+ s# ?, M% _# ~
values(1012, 'amy', ) N3 ~( F P1 a; [! F4 i" O7 d- p# B
to_date('2011-10-10','yyyy-mm-dd')); 0 \ i0 k% x j$ h" R* L ! T/ Z8 v5 R' y+ v6 ?! c' e! c显示员工姓名和入职时间,显示格式为:1 J! r6 n0 `% }0 j: X
amy 2011-10-10 7 F. B& I- X" o. s# O4 _' B3 b9 q( I9 ~8 O# L
to_date& C8 D/ d% I% l" C
字符串 ---------> 日期 + ^$ t2 \( A, F( { <--------- % }: b+ U" I" y4 h! k+ w to_char2 `4 `: D5 ]% l& i- ^
/ @' ?: c A8 C' i. |" O! V n3.计算员工入职多少天?# m, {: f( f! s7 v
select ename, hiredate, (sysdate - hiredate) days " j" f3 Z6 p+ h5 o$ F6 ufrom emp; 5 ^- ]" ^/ p+ a( g& @' O. W. v$ h5 {! C
日期数据相减,得到两个日期之间的天数差。 ' t p2 q; k, S8 _* v a不足一天用小数表示。0 u$ @' } [1 ~- @ r
5 _' b( z$ @; t1 `3 |: d8 T6 lselect ename, hiredate, 8 Y3 |! L6 N, f3 k+ _( J4 u' e( S! V
round(sysdate - hiredate) days 5 x# `( \$ z0 o+ Pfrom emp; / Q7 x2 U, y6 e0 y, k" B. T ) h6 N+ o" M# M3 Q- F+ U4.计算员工入职多少个月?用整数表示。3 a B- _% v* O3 \& B$ b
select ename, hiredate,0 E- a) F6 P4 S( F/ V0 w2 v8 }
months_between(sysdate, hiredate) months ) R+ n$ c5 V T/ rfrom emp_ning;; A- j/ \3 F' c/ n" H
1 }: c9 v- z0 j2 b: _select ename, hiredate, . F) ^3 }% |8 S4 E: L$ Yround(months_between(sysdate, hiredate)) months" [" T- G% P$ a# H" |
from emp_ning;7 x' v" `6 k' W" x3 R
h9 [, U: F& \3 H& B
f3(f2(f1(p1,p2),p3),p4) : 函数的嵌套 , d0 d; G0 n/ P: p9 y+ N# [$ e * l6 z8 B+ D$ \' `: L& Y2 s8 u5.计算员工的年终奖金 # x0 Q0 y y! T2 W6 @* xbonus不是null,发bonus的数字。 4 L6 f/ |' a4 A1 P0 Y- xbonus是null,发salary * 0.5. 5 G0 q- W6 u. q0 Nsalary还是null, 100.8 r4 l& D2 c, V1 I* l/ q3 p( o
+ y: e7 W: n! B- H/ N
public double calculate(double bonus, + m% k0 t, b0 B* Q+ Z double salary,$ V5 ~# H7 I8 J
double comm){ ]& D% A7 V& [9 U$ ]; M if (bonus != null) return bonus;( h4 s3 X' M' L. K
if (salary != null) return salary * 0.5; 7 X/ `* C1 M: [- c. K) W4 v- s' } return comm; Y, Q+ |8 A+ u6 ?9 F1 g6 f" p
}2 r( Q! ~ u( N. Z' M$ z0 F1 u6 w
--返回参数列表中第一个非空数据 5 b g, a2 ~/ g; H k# V--最后一个参数通常是常量。 G0 G C- Y/ d9 q2 y
select ename, bonus, salary, , R' F: v5 ?. r% K& m2 }* Qcoalesce(bonus, salary*0.5, 100) bonus p& S6 i J. g" Kfrom emp_ning;# u! ^* s k% A8 K
X2 `0 p* v# _( S+ w4 R6.根据员工的职位,计算加薪后的薪水数据: , _ E- n& D. Z0 E, F, D4 KAnalyst: 10%% u9 ]& F+ \* m* B1 x
Programmer: 5% : V* y" c% h% @# Wclerk: 2%% X/ j) T: d, a6 Q; B
其他职位:不变。2 @; m; O: _( F _
$ m# c) M0 i% b' ?* ]" h2 n
case语句: 6 p9 h8 a9 T, E, t$ `select ename, salary, job,& v& q7 F8 I, L5 ^ _ L
case job when 'Analyst' then salary * 1.1 3 E5 Y2 A3 q1 ]/ Q$ g when 'Programmer' then salary * 1.05% L' x5 f. C" ^( ^# X M
when 'clerk' then salary * 1.02" c( u, Y! \! k5 A
else salary+ e. o7 m0 M) J+ A3 o- ?
end new_salary . z7 E5 Y5 v a) sfrom emp_ning; + A" N$ u0 J: N8 i9 L, O* U2 ^6 g2 S" D1 S& `4 i
select ename, salary, job, : o; A+ U% e" u. ydecode(job, 'Analyst', salary * 1.1,: i `' B! k; K2 S% ~
'Programmer', salary * 1.05, ; `) p. g8 G+ k; A1 l 'clerk', salary * 1.02,( U$ y$ }" j4 X: o" A
salary) new_salary . c6 N* D2 O Q, ^& \" Cfrom emp_ning; 5 j0 d/ T: I/ V# R' m/ P t' ~, y% l+ d& B: @$ ?. b
7.薪水由低到高排序 ) ~+ Z; _! R% f# v) Iselect ename, salary from emp_ning* r, j9 s( n t& b- z7 |$ q
order by salary; * j+ d8 C* m1 i- |+ f $ N" l* e6 u; { g8 qselect ename, salary from emp_ning - Q9 Y1 k- ^- l- o' K0 r. c+ norder by salary desc; --倒序排列 descend( |: H2 b9 q1 C: V' W1 e* K
, _- b. i& b; O- T
desc emp_ning; --查看表结构 describe/ N7 I2 x% z! r" a9 H
! U% G0 H# O6 \1 k
8.按入职时间排序,入职时间越早排在前面。 ; K) P1 s! [( X0 ]4 Vselect ename, hiredate0 @& B# J4 t" Y( O
from emp_ning2 g. B' a. c' i7 _0 K- K; e+ r
order by hiredate; 4 A5 k0 j% ]- Y2 ?" G6 z! G# x) g- E8 M2 o% \' y9 Q
9.按部门排序,同一部门按薪水由高到低排序 " X3 W8 o3 J+ z! s! a/ Kselect ename, deptno, salary & I0 d: _* P2 o) I& Rfrom emp_ning: N! j# ^1 l, f: o9 @( V
order by deptno, salary desc; ! k# N2 E0 Q0 W5 j ) u) @9 `. z6 ~10.员工表中有多少条记录? ; @2 p3 S n! i% [5 c% Uselect count(*) from emp_ning; / C" p: y% G+ e( S- B $ u* x/ z* p4 R- \) L6 gopenlab帐户下有多少个表?, {5 {! r8 ^: R l
select count(*) from user_tables;' ^' z$ F* ^+ G8 x" E6 V. L
8 q/ a5 O8 |# o; B& topenlab帐户下有多少个名字中包含emp的表?+ s# E: t: U. `$ s, }
select count(*) from user_tables 1 d7 ~8 W; H' Gwhere table_name like '%emp%';9 d0 e. r; W8 o6 A& t+ R
3 D& _) h9 }3 [ q5 O) R% ]0 x$ _
--表名默认大写 8 U4 n6 f3 L* S- L& Aselect count(*) from user_tables9 R& e) P* Y- c0 S0 S# f
where table_name like '%EMP%'; # f( Y2 s2 Y& e5 x6 F$ z) } ! U& L. h2 ?. M4 h* u" r/ }6 S--入职时间不是null的数据总数。 2 Y, M; m# F1 a; W( O8 V/ F6 z--count函数忽略空值。 3 @2 @0 k8 z. E. sselect count(hiredate) from emp_ning;4 N! c3 b4 G6 q- e: J: E
/ _& c3 N* o% [
11.计算员工的平均薪水和薪水总和是多少?0 y }2 W. m& R4 x9 Z. T2 p
select avg(salary) avg_sal, & [; p7 P: v- \& y8 F' Y. e6 H
sum(salary) sum_sal 7 x" W6 ?) d& w& Y8 mfrom emp_ning; , u" @" J9 L2 k( n+ F6 {$ F* ` G0 P0 P- y Y8 U" z
avg(salary) = sum(salary) / count(salary) ; l' P0 r- w3 {# n5 z/ | J 68500 / 10 = 6850 % f: I6 j4 t" ^5 i# i+ K9 K--纠正逻辑上的错误$ k: x' v* S4 |$ A3 m3 b& S
select sum(salary)/count(*) avg_sal, # ~: F# e4 t# D! i2 H1 B" ?& { sum(salary) sum_sal $ }5 m7 W7 ~8 G2 s! R) G. D: Nfrom emp_ning; ' F9 D8 l1 b- a8 J2 p4 B, c$ B4 ~6 c* H
select avg(nvl(salary,0)) avg_sal, 8 D2 K( |; x) T sum(salary) sum_sal D* g' J6 A. e; Y8 f' _from emp_ning;+ i1 J& B# \( ~# ~$ Z5 a* Q
c4 M$ y1 Q: ?' @% e- s12.计算员工的最高薪水和最低薪水 : m8 E' f# M5 uselect max(salary) max_sal,+ @: f/ s) [ [9 B5 k1 M
min(salary) min_sal) }$ N; t) B# h
from emp_ning; 8 q) f1 Z1 L% c; l! C3 N ; |( _- f6 z4 c) P组函数:count / avg / sum / max / min 忽略空值- _$ a+ s( e- E; o( i, m
其中:avg / sum 针对数字的操作。) Y" [ N9 G, G3 ^; s1 ]2 Z4 t
max / min 对所有数据类型都可以操作。: O# f" ]" f9 k0 s( m o! d
# W3 J G+ y6 B13.计算最早和最晚的员工入职时间。; N7 V) e, e" A
select max(hiredate) max_hiredate,9 o) L: s" b6 v5 i+ T' v9 H4 f/ G. x
min(hiredate) min_hiredate 4 l- C0 S& T5 n1 f! ^; K, |# Pfrom emp_ning; / u: U ~6 d0 p# b2 R% R4 ` , ~6 O& l2 t6 T$ o14.按部门计算每个部门的最高和最低薪水分别是多少?( W( N5 I8 J6 I% x5 W
格式如下: % v4 x( i \2 [2 E0 E8 _0 z10 4500 3000 . M0 n+ R' ^* t$ d) l$ t* F20 15000 8000/ y6 J3 R7 a2 C" K/ s% D% V
30 10000 5000 9 @6 ^ W( _0 O . ^( L5 P, t4 r' r/ \4 N) a1 V--group by 列名 : 表示按哪个列分组+ Q: n# B6 m2 t2 @
select deptno, max(salary) max_s, min(salary) min_s - q( w! W+ `; Q. }3 L3 Zfrom emp_ning : O7 m! Z2 T( ?3 }2 Igroup by deptno; ' X$ R( I# [0 L& ]1 u1 h( B- {+ }* M8 K s( z0 o
15.计算每个部门的薪水总和和平均薪水?9 _6 o d5 b- A
select deptno, sum(salary) sum_s,% ]( U# j$ Y! }! ^ q& `6 P
avg(nvl(salary,0)) avg_s2 z! F# C U/ B7 w, l- F/ i3 t
from emp_ning * T* R* f, A# B |% ~group by deptno;" l6 I2 L$ h" M6 j: p4 [8 W, U* n' T. i+ C
2 A0 @$ M9 M! A3 q- o4 s) A8 |# W
16.每个部门的统计信息:格式如下: ) o$ X; F( H2 L& d) I- Rdeptno max_s min_s sum_s avg_s emp_num " }; U. |( |3 A& C0 Y6 n) h' I* c10 10000 5000 23000 6789 3 - Q& x' n* D3 _. e6 H8 q+ r.... & h& A5 x+ o( j: @" H# M2 oselect deptno, max(salary) max_s,6 ~. `6 d( K8 Y2 {
min(salary) min_s, 8 F) l0 M4 P. K! G sum(salary) sum_s,2 Q9 H. s2 N' a3 E0 s
avg(nvl(salary,0)) avg_s, & P/ o% h# b! E7 {7 m; u& Z' d; h count(*) emp_num5 |* W, J1 H) {3 a( B! [8 u
from emp_ning3 ~. @9 I# G" _( H: |0 M
group by deptno; ; T7 {: ~ z9 X5 Q5 C( y7 ?, W - `" A4 C$ a/ `5 y6 w0 f6 x. {select后出现的列,凡是没有被组函数处理的列,必须 ( \( b, n3 v- h. ~# B( z出现在group by 短语中。 % G* W. |6 ~9 |" T5 |6 ~$ ^0 z* V, @$ T6 j
按职位分组,每个职位的最高、最低薪水和人数? 5 ]- G" M9 X }2 I6 e2 w% Bselect job, max(salary) max_s,0 h7 Z3 O0 y9 n# h7 P% B& K, a
min(salary) min_s, $ z: w' T3 m0 _( M/ x/ y* i) j count(*) emp_num6 l+ k8 R* z8 W5 [2 C
from emp_ning) q5 a+ ~4 k$ R3 ~, a- Y( ]2 }
group by job M1 c4 o% I" h1 c1 O
order by emp_num; # x) F4 \' B! T6 H, Q6 a2 R17.平均薪水大于5000元的部门数据? 6 B# h& Y3 s6 F xselect deptno, avg(nvl(salary,0)) avg_s; Y7 p6 c' C5 Y8 O
from emp_ning5 m4 F8 S% F/ S( e, Z& V; z# D! p
where deptno is not null ! `, u# I7 a3 q( ^+ Y6 v4 jgroup by deptno8 E6 X6 b5 }" {
having avg(nvl(salary,0)) > 5000;- h8 ^0 p" ?+ V1 @) [8 f Z5 Y
7 Z; x; k* ]/ f, u4 n18.薪水总和大于20000元的部门数据? 5 R( r3 V$ {: y" I# s5 Xselect deptno, sum(salary) sum_s 2 d9 ]5 b9 P( k. j* G+ s6 Q1 Ifrom emp_ning / z& T# O: _6 V2 X) _where deptno is not null7 w- E: S* T3 G+ V
group by deptno4 }! f# i. ^; `6 l6 D1 @
having sum(salary) > 20000;+ o+ \3 M% A! }* J
% R4 r+ | n/ a! V4 g19.哪些职位的人数超过2个人? # h# m( G0 e# C( r n$ t t: Xselect job, count(*) emp_num 8 m6 j7 H. Y7 N( B1 m1 o' ?- Lfrom emp_ning 5 ~4 b# V' }' z, ~where job is not null' e' t5 S3 F, u/ y8 c+ H) l
group by job " z7 t! H3 q" fhaving count(*) > 2 6 C; b/ g4 ]% V. a. aorder by emp_num; : r( N+ N% _6 E5 } ; N; O( J4 F6 l u% y* {+ p. q