7 G& u* ?2 ~2 u清屏: clear scr& Q" Z. @6 i; E
# M& J1 s3 p- H' Y' X# X8 w" b% V
3.把用户数据存入数据表中。6 K, O, t+ g/ m. S+ W/ j' T
SQL>insert into user_ning14 k: L5 O9 W/ A5 l2 j
values(1001,'1234','liucs','13600000000','liucs@sina.com');) n7 c; q! O+ H; m$ D- i
/ T0 b; W; C6 C' J$ f9 b
insert into user_ning1 values(1004,'1234','liyi','98765432', - ]( W1 U. ^2 A/ W* u% v/ A'liyi@sina.com');! f' Z; ^4 u! @
; E0 z+ T5 p+ {3 Z8 ]5 `) x
错误语句: + O; a, v3 f$ R5 jinsert into user_ning1 values(1001,'1234','liucs','13600000000','my email', 25);$ B9 C# h7 s }" E9 `
0 k( l6 K$ T' H+ @
三种SQL语句:+ [8 M3 F4 G$ T6 g5 V: Q6 F9 `2 Q U
1)create table...6 s5 e. U2 _# l
2) insert into...values... 3 i/ S2 i! t' d* {7 ~6 J3) select ... from... & \" N |8 F8 d) U ) s9 K3 ^2 y: e, a' ? YSQLPlus命令: " `# |) q$ t2 |7 H5 r' U2 v* b; v4 T设置每行数据的显示长度: # }+ h+ o, H& t+ x" YSQL>set linesize 200" F2 b, N* B$ P9 e$ W
设置列宽:10个字符 # M7 [. H4 ]! U* _* _( D; YSQL>column 列名 format a10 7 A, T7 W1 o! w. L% R3 I( S查看表结构:desc:describe 描述 * z6 e5 E& \8 h4 } R* JSQL>desc user_ning16 f- N4 H4 e; v" Y% K
4 N0 m, H" s0 M) k, A0 m2 |
规范数字宽度为4位: * I8 O% U( S, p6 ZSQL>column id format 9999 8 X9 K& F# {4 b) x2 U+ b简写: ; i. z; c& j/ @5 ~% U9 M' l1 W2 l3 VSQL>col id for 9999 ; u. b+ \! P* L8 E1 F- _! ^% c+ z ' J! D; D6 @1 x* ?6 P8 h& T1 [. L--*表示查询全部列 P6 b7 j7 e, b3 Wselect * from user_ning1;$ A& d/ U! N3 ]
0 ^3 B& c c. G( nselect name,email from user_ning1;% S* R9 m, V6 P' r
! f% Z9 m9 g9 \/ a* z
--查找1001/1234考生的名字? % l! a( J8 g8 j! V% Dselect name, email from user_ning1$ f( ^4 y8 Z e9 T6 O3 R( J
where id = 1001 " K- j2 T0 L8 ~5 J% o
and password = '1234';8 f' @4 F' t' U+ E. j# X
* X2 g) d% z p/ q: a: U数据准备: / R/ Q7 I% W9 Y% V* M0 M( y- N1.表dept_ning: , j0 Q* u6 Y' n* K9 K fcreate table dept_ning( 8 ^( S, u- @# e8 q" Sdeptno number(2), r( r G+ `( V# a
dname char(20),8 h+ Q' A0 P( |! O
location char(20)); ' `6 q: \' G. W5 P# i5 s0 P8 ~1 X+ }5 E/ O
drop table dept_ning; 5 D5 F; B6 T$ M! u0 d9 ?6 r+ d" E' S
增加数据: * k$ s4 a$ a0 E9 ?& s" }% ]insert into dept_ning values(10,'developer','beijing'); " k$ W; b& r) ]7 ~; minsert into dept_ning values(20,'account','shanghai'); o# a( t/ I `$ n5 Qinsert into dept_ning values(30,'sales','guangzhou'); w. q' s" W: e/ dinsert into dept_ning values(40,'operations','tianjin'); / `- _( S) i8 ]* j' n+ G 8 F6 ^" h5 o* }4 Ncommit; ) }# o" [& S. ~& ~; P: q5 E 3 Q7 {. f: i. \2 }6 Qselect * from dept_XXX; * ?8 s4 \$ w1 t8 d ! x5 u' w4 i; L: |+ w9 D2.emp_ning, z$ z ~) j( ~3 y2 k2 V
create table emp_ning( * J" i* i' P& l4 v6 |0 ?empno number(4), 7 c& M/ g& G0 s0 R, l; L* \ename varchar2(20), ' q$ c3 M, m) X( ~job varchar2(15),7 l7 F5 b3 Q/ A, I3 N; F8 A. [; c
salary number(7,2), $ \' l4 l1 v- ^8 {1 c8 B, ~* @bonus number(7,2), 3 i: l* I& U6 |6 Ihiredate date, 3 } Z* c4 ]9 T) Zmgr number(4), $ e& y/ Z; z$ \deptno number(10) % i; U+ C8 d/ x/ M" z8 z& Q); . M0 {. W8 ]% [7 E$ K2 A1 L& e. [ v3 Y2 [/ R c
insert into emp_ning values(1001, '张无忌', 'Manager', 10000, 2000, '12-MAR-10', 1005, 10);1 {1 X9 q: |% F* g+ [6 S
insert into emp_ning values(1002, '刘苍松', 'Analyst', 8000, 1000, '01-APR-11', 1001, 10);9 O ^; U* T2 H; X9 `/ Y$ N
insert into emp_ning values(1003, '李翊', 'Analyst', 9000, 1000, '11-APR-10', 1001, 10); 8 U6 S- q4 X# m6 _/ F- b" \insert into emp_ning values(1004, '郭芙蓉', 'Programmer', 5000, null, '01-JAN-11', 1001, 10); 2 H* a6 O) E W; B, e% Qinsert into emp_ning values(1005, '张三丰', 'President', 15000, null, '15-MAY-08', null, 20);) } ?1 C+ k! X
insert into emp_ning values(1006, '燕小六','Manager', 5000, 400, '01-FEB-09', 1005, 20);% m# X5 J- q7 k$ T
insert into emp_ning values(1007, '陆无双','clerk', 3000, 500, '01-FEB-09', 1006, 20);' Q8 l. k9 i$ R1 I# o0 c
insert into emp_ning values(1008, '黄蓉','Manager', 5000, 500, '1-MAY-09', 1005, 30);0 y: ]6 ?( C3 u g5 ?/ d+ a
insert into emp_ning values(1009, '韦小宝','salesman', 4000, null, '20-FEB-09', 1008, 30);0 R3 } o7 _/ b1 v, r% I
insert into emp_ning values(1010, '郭靖','salesman', 4500, 500, '10-MAY-09', 1008, 30); # T7 z# G& V: V; T h0 x, w2 ~( _6 }8 p E O
set linesize 150 ) j4 a/ N! G. `3 a1 ~col empno for 99990 d r9 D# o; U. y- E
col mgr for 9999 & ~; |6 \; O! u# H& [col deptno for 99 ! V( Z4 a4 i8 N" c; y; B- ucol salary for 99999.99 5 }: q8 N' b' t# D8 U 0 L, c O1 e4 C4 q4 xselect * from dept_ning;# r% |) K3 J! \1 T0 y
select * from emp_ning;/ n7 p& `; ]( o- b! U
( O0 h2 R# _5 K3 b ]$ k$ a/ W/ z一、学习查询语句。2 J! A1 |8 [) q
1. 计算员工的名字、月薪和年薪?) T" o! [: M, t9 m) A. l6 h
select ename, salary, + S# I2 d9 M' `9 a
salary * 12 year_sal 1 K& s2 {* t9 y9 Cfrom emp_ning;6 U2 C% l) D/ Z% D. z( A+ I
2 y3 N' K! X0 d' c1 E# F9 P3. 机构中有多少种职位?* f+ j5 ~. Z9 K8 l
select distinct job from emp_ning;' H, y5 x8 S8 |0 Q) g1 |; Z
" |1 B7 Z( q. R2 O- H$ B员工分布在哪些部门?6 x. l8 R `( C; M
select distinct deptno from emp_ning;( ?8 A& X5 L, |
f) X* N; x2 [: H- ^* e& H3 Edistinct必须/只能跟在select后边。 2 o2 v" T V) }, f; T 2 g; n3 H* \& g- [, c4 ^" d5 Q1 Q4.薪水高于10000元的员工数据? ( V" s+ m, g( v* F4 \select * from emp_ning : ^" x% D$ H7 F. H8 h Zwhere salary > 10000; % p) |/ O% ] q$ m. T( F( Q / |1 V* b! _9 V5.职位是Analyst的员工数据?" V* Z+ @2 v. L' d# F7 ?
select * from emp_ning 9 S! B# w* j R. \: U8 d7 q* Hwhere job = 'Analyst'; 7 X8 [ \6 D( J P* w, T# X( d + T; x. j3 g8 n; A, d* m& A7 @analyst ANALYST anaLyst; P+ b0 i) c: ~
. e5 g" g4 Z) m( S V8 y1 O8 b+ Yselect * from emp_ning9 p. q4 x) \) q5 |8 z
where lower(job) = 'analyst'; , N, l. d7 W) H" P* R) Q5 ^- R4 ^* @0 B
select * from emp_ning9 a# m1 ? a2 l, v) j
where upper(job) = 'ANALYST'; $ {+ z8 {3 k0 g/ J! s. J4 w ) C! B( y+ {4 J. G3 d3 u2 I/ ]--如果数据是analyst,查不出结果 9 }( l1 t0 Q, f' W" h! X--SQL语句大小写不敏感,数据大小写敏感% F; h, z- i" T u$ w. u: T
) N* ^; X4 h* S3 C6.薪水大于5000并且小于10000的员工数据 ( M& s0 f8 f- }, \( x?& {. n# r) y3 L% G" ^- f
select * from emp_ning . B" o+ H) x5 H) j6 f/ wwhere salary >= 50000 Z5 e; B* l. z" g
and salary <= 10000; , v7 \; s$ {$ P" t6 X8 p$ a ! {" w0 O, V( K8 ~--在区间中:between 低值 and 高值 ) ^ E7 T' Y1 G, o, O$ C--闭区间:[低值,高值] 3 ^' G; m* L1 G9 O& W/ T5 B3 o j Vselect * from emp_ning . X" t( d8 f3 C% s- `where salary between 5000 and 10000; ) J/ n( v; D$ s$ U " |; |& A) P- a" R* V入职时间在2011年的员工? 4 |5 W/ e; G2 \" F/ ?/ C& r; p- P9 M['01-JAN-11','31-DEC-11']- m# s. U% R; ]
select * from emp_ning6 v) B% y4 ]& D' O; v' X& m/ i' [
where hiredate between '01-JAN-11'8 Z8 [: l6 J# D4 N+ e* l: Y8 B
and '31-DEC-11'; $ D( w+ ?( T$ c4 Y' ~) O9 P + E% o. a& b" z7 @' q7.列出职位是Manager或者Analyst的员工 ; X. j5 w( h. x" k9 F! F# z?+ }' q+ N3 u8 v7 L2 B; E! u' ?
select * from emp_ning) X+ j7 t0 M+ ~% b
where job = 'Manager'- d3 }8 a' Y+ Q
or job = 'Analyst'; * S6 ]3 _7 E, \1 Q% T8 |--等价:in (list),在列表中。9 O0 {, ^2 R) i( i! O7 x, c
select * from emp_ning * x+ K/ A- G$ F0 L% D6 E' Wwhere job in ('Manager','Analyst'); - N$ i6 ^7 Z8 a! |7 b! C6 c% K+ A% H! k, F
8.列出职位中有sales字符的员工数据? , M/ W7 Z( L. x: @salesman7 u# ~0 Y# {8 I+ L8 F% N
sales , c7 w+ ~8 D5 N1 \( R8 ^before sales9 y6 z$ e' S5 [/ x
after sales & ^; C2 n& ^( M! J+ O6 B$ Gbefore sales engineer ! s" ?) z s# y/ M3 R7 y/ @: O + I8 M4 g' u+ u--%: 0到多个字符,跟like配合,模糊匹配+ w7 {% L9 T0 p. O
select * from emp_ning: ] `1 ], D- D5 m$ z. i
where job like '%sales%';! Z+ _4 P( }+ c3 y* a& d
& U9 Y1 K3 v m* I0 T
9.查询哪些员工没有奖金? G }7 M/ b- m* {* \select * from emp_ning% c, q" X3 _, ~2 x1 K
where bonus is null;8 T% k9 T4 V K9 P- b3 K( X
3 Q+ v2 @; }! Z- ?
哪些员工有奖金?( X# E) T% z. [" \
select * from emp_ning4 L9 A/ R5 E9 S# M- a4 f
where bonus is not null; % t5 e" B2 \$ s0 t6 S % G5 h; i) n0 ~5 F N( Y小结: 6 o% ?0 w$ x5 n" M. X1)create table ...- e: y& v! C+ \( b+ |7 R
drop table 表名; 8 }- B1 B6 B# C+ r/ N; X0 f; o2)insert into 表名 values(...); , }8 ]+ Z- A6 Q$ Y& S3)select distinct | * | 列名 | 算数表达式 | 别名 ' ^" i1 }7 ~7 H* p) ` from 表名 / [+ S' p5 Z. y2 r9 O0 r where 条件1 or 条件2 and 条件3; 0 f- h- V* Z* |0 H- e m( y3 y* ~6 F8 q/ ^/ o0 c
> >= < <= between...and... in like - I$ v& F, S8 Z' i: C7 q5 x+ a: f7 G# O1 |