我的日常

登录/注册
您现在的位置:论坛 盖世程序员(我猜到了开头 却没有猜到结局) 盖世程序员 > Oracle增删查改 day01
总共48087条微博

动态微博

查看: 2207|回复: 0

Oracle增删查改 day01

[复制链接]

45

主题

5

听众

119

金钱

三袋弟子

该用户从未签到

跳转到指定楼层
楼主
发表于 2014-06-02 19:40:09 |只看该作者 |倒序浏览
数据库: DataBase,DB
' s0 P! p! b4 q4 k' B( V& N! Y2 h. X存放数据,管理数据的存储仓库。是有效组织在一起的数据集合。7 U0 r4 D7 G% L5 J9 j2 \
oracle是一种数据库软件。+ ]. s0 ]( I4 b7 Y3 _% H/ K6 f  D3 m
9 H: \  ~' e1 N* L" Z
Oracle  db2(IBM)  sybase(sybase)
& C3 A' O0 X; P& t2 M1 N: ksql server(MS)  mysql(Oracle)
, G5 N4 p  z- ~; A/ `access, r6 ]& Q6 _; J7 Z5 }" ?
7 e: I! ]: V! U' @/ ^  h
数据库管理系统(Database Management System): DBMS
" p4 J) F+ ~; p. i
2 M) b$ U5 D8 v  F' l2 |SQL:Structured Query Language+ F& w$ l0 v5 k1 [( M4 [  V. J1 u
结构化查询语言:和数据库沟通的语言。9 z; n* }2 d+ \, U

- ^0 X+ U$ j6 q8 N% U6 `2 \2 G0 @DBA:Database Administrator 数据库管理员
: ]! I/ q2 y8 U0 j7 h
. _( K9 Z: c9 |4 B0 rTable:表:数据库的基本存储单元3 F: q, s  L* O+ V" N) w8 ]
横向:行,row,record1 h6 P6 e+ y+ ]! _" {8 E3 u& O
纵向:列,column,field: b% K5 M% ]- b
* d6 E1 X( }. z$ D1 v& e8 q  P/ f
任务:把用户数据存入数据库的表中。) t; ^4 |. c2 T/ v8 q
实现:
5 I3 C, m" T4 y" m8 q% M9 g7 m1.建立一个连接
0 o  t8 I# a* v0 ]1 n- w1)数据库所在的服务器的地址:192.168.0.26
9 w7 ~; o' ~1 S* atelnet 192.168.0.26
* K5 _( [: v- L- Z) K) |& d服务器的帐号/密码:openlab/open123# U9 ^. n1 j# y- T! Z
2)数据库访问用户:
- O" n; J# F* N' \* Hopenlab/open123" E, T; C- H8 ?/ l: D2 i% T
数据库默认帐户:scott/tiger
# C+ w( J8 X& a  L6 j0 Y8 ?& [3)oracle数据库的连接工具:/ @# A) k6 V! Y* M! M5 A
SQLPlus:Oracle数据库的客户端工具; z1 Y2 I- r( Y: a# ^" B7 D
位于数据库的服务器上,192.168.0.264 h4 h: ~* K; g1 Z4 h, [
$ sqlplus openlab/open123; R8 u, f  `. J0 k. B. |
SQL>
% c' \3 z$ |7 `; n* _# [如果提示SQL>表示登录成功
+ n# A; P0 c! k/ ?
3 Q) r3 v5 Y4 e6 F4 ]9 z& J) P3 E' o4 x* N
2.把数据表建立起来。
- E, f3 W% c3 Q4 J' |1 p) J定义数据结构:9 o8 s* g9 I1 u
create table user_ning1(
) t4 e0 E2 o8 M7 C  l+ l6 ~id number(4),
# I, d3 G2 c: J5 |password char(4),% q" z3 W) A$ w' o$ @4 \& b
name char(20),9 A, o; f5 z2 u) M( L
phone char(20),
0 w2 R+ s6 j/ `9 h: j5 Pemail varchar2(50)& ^% _$ P5 f2 @: j+ X
);) d. s8 y0 O. l; l4 Y
提示Table Created. 表示表已成功创建。
6 ?: P8 Z* y3 b& O7 k9 |* G4 c3 {! q0 ]* f
数据类型:
4 e% s) o4 }% c" y1)数字:
. Q! H* S, @+ |  |" ?, Snumber(n),最长n位" ?% P: h# v1 J) c& d
number(n,m),最长n位,小数点后m位9 r5 p' L5 X0 k( Q( {
number(7,2): 99999.99
0 e3 V# Q  K/ D- Z8 ~$ ]5 [* A; q2 E; c/ c/ ], Z  c  e
2)字符串:
4 e. S$ j  e; ?8 F( i9 f2 ochar: 定长字符串( M: L2 H& Z8 h# a) Y3 |
varchar2:变长字符串
- |# I7 @/ v2 B) }char(10)  m; b( C* G& c% P/ G
varchar2(10)& x7 G9 m2 W  ]5 |0 G  w, q
3)日期& k# G1 B- h/ c; x/ y' r5 n
date
0 n5 b; z- Z2 {9 Q8 z# w, `: c
: |  ]' \1 U& t" \0 @& [. P7 ^2 `清屏: clear scr3 u+ [4 ]* R3 E+ [0 Z9 m
6 F2 ^. F( k4 F) k2 J
3.把用户数据存入数据表中。
$ I5 o  B* Y# ^' z) S+ aSQL>insert into user_ning1) ^) Y9 c9 }' S) ~( s9 Y
    values(1001,'1234','liucs','13600000000','liucs@sina.com');7 F" y) i- V  }+ n% M1 M9 d
$ v+ |; G- _  r7 M7 e
insert into user_ning1 values(1004,'1234','liyi','98765432',
  p' ~& n% M9 K1 u6 o'liyi@sina.com');# b1 ]+ v8 v1 c4 f
2 A8 |! X- q! k, Q/ b9 \' F
错误语句:
4 I' w( o5 H9 h* Q# Einsert into user_ning1 values(1001,'1234','liucs','13600000000','my email', 25);; m& N. u( E6 b8 s" s

7 M' g4 f7 ~5 i9 f- G9 Q三种SQL语句:
( `) B& d7 \" n5 t* L* o! A1)create table...; B2 M9 W! M/ T8 Z# s5 \  X
2) insert into...values...
! E/ A- K, j+ Y; V1 ^3) select ... from...# [9 n  w( s$ `5 \+ S" w

. @, v1 j! q1 ]; n8 T2 D2 M, ^' HSQLPlus命令:) X& }2 r5 n5 \. ~; @
设置每行数据的显示长度:3 U4 q) w  \9 K$ `; S
SQL>set linesize 200' C3 A. K. r& X+ q
设置列宽:10个字符
. [9 n1 T" [( w3 H0 F- VSQL>column 列名 format a10" z5 ]" N8 k* Z% V  i  Q
查看表结构:desc:describe 描述; S. |1 q3 V* B" B+ m9 S* E9 a
SQL>desc user_ning1
$ M9 P$ _  y& J# O  i1 }* v  @+ g+ C4 s' R
规范数字宽度为4位:
6 P9 a$ D8 Y' q5 f) USQL>column id format 9999% P* I+ S. x: z6 Z
简写:
1 N1 [; o" M1 g( P( QSQL>col id for 9999
& ]$ Y1 l5 a3 _
% w8 ~+ }4 T5 W7 i--*表示查询全部列8 t8 w8 Y, z8 i
select * from user_ning1;2 Z6 x( I( U4 R# O. R& O

# a0 @3 a! l) X9 b( yselect name,email from user_ning1;$ M) x$ z- C, Q2 \$ _. D

  U9 f! X" z6 B--查找1001/1234考生的名字?* J/ K) K& E( {# m
select name, email from user_ning1' ~% P+ z* E! H' M& U6 x, }
where id = 1001 ; a* L8 P9 K; h/ B0 O
and   password = '1234';
3 l6 n$ l% |/ m4 |4 x) o, t+ o
' X+ r: [: W- d' V: t数据准备:9 `8 ]+ _, s+ m1 S; S9 M
1.表dept_ning:4 C( X" i8 L4 r$ C3 D1 U  M
create table dept_ning(
& h+ E+ M, K3 y$ L+ Y) i. t$ `deptno number(2),8 I' X. f( Q8 Y+ r/ f  I) a8 W
dname char(20),: _  P  s4 d' }1 k) D) U
location char(20));
3 S/ |% W: T% W
$ t( s1 r! E1 k7 ldrop table dept_ning;  R2 `, ]. h9 z3 s3 Y; s! \

8 x% b; \+ I/ g, s* Q( q增加数据:
# B; Q' y7 g7 e  `insert into dept_ning values(10,'developer','beijing');" e% L8 w# U5 o3 \" c
insert into dept_ning values(20,'account','shanghai');: ], G; `1 ?$ a) J# I* s. q
insert into dept_ning values(30,'sales','guangzhou');1 I- f/ x+ r% u% @8 K
insert into dept_ning values(40,'operations','tianjin');
, m) _4 h6 L' T1 }$ e: F* ~+ m+ n6 I6 R
commit;& p, A" I5 M' K: A

0 v/ C% R7 `: s5 c6 [  Gselect * from dept_XXX;$ z2 q. M, |% R
: S: @  ]1 X" |4 `* _& \
2.emp_ning. H9 O5 ?' g$ G
create table emp_ning(
. S& `$ F, @3 n  U' V% i$ J. eempno number(4),1 S! v  C9 p; n4 O% j
ename varchar2(20),# ^( k# _3 Y! i; W
job varchar2(15),% N. w: s% _6 k8 p& I
salary number(7,2),
5 z: x! }% b2 p* q- O) Ebonus number(7,2),
: ^1 h2 x) Q5 B8 q& l8 [5 r2 Mhiredate date,. T, L- {' u! W' `: s
mgr number(4),
7 u; q7 m" s. l' R; j4 K( Y% Udeptno number(10)# v2 n4 v7 B, m8 M1 I7 N; {
);
& ?6 n& L( z1 M
& D1 T% d: G. T& einsert into emp_ning values(1001, '张无忌', 'Manager', 10000, 2000, '12-MAR-10', 1005, 10);$ \4 g$ Q# ?; R; O9 l3 U
insert into emp_ning values(1002, '刘苍松', 'Analyst', 8000, 1000,  '01-APR-11', 1001, 10);
) ^1 k; I$ I4 E! I9 b4 n  r) _/ I3 Tinsert into emp_ning values(1003, '李翊', 'Analyst', 9000, 1000,  '11-APR-10', 1001, 10);, v+ |8 C, q) A
insert into emp_ning values(1004, '郭芙蓉', 'Programmer', 5000, null, '01-JAN-11', 1001, 10);
1 M+ @: x7 w7 y. [% iinsert into emp_ning values(1005, '张三丰', 'President', 15000, null, '15-MAY-08', null, 20);
" N% k# i/ z9 Yinsert into emp_ning values(1006, '燕小六','Manager', 5000, 400, '01-FEB-09', 1005, 20);$ \/ B1 V. F: S  O
insert into emp_ning values(1007, '陆无双','clerk', 3000, 500, '01-FEB-09', 1006, 20);' y: A* W* a8 q( I- X
insert into emp_ning values(1008, '黄蓉','Manager', 5000, 500, '1-MAY-09', 1005, 30);" {2 \+ @8 }0 I9 j, ?% f
insert into emp_ning values(1009, '韦小宝','salesman', 4000, null, '20-FEB-09', 1008, 30);
! L+ |' \" w( B; ginsert into emp_ning values(1010, '郭靖','salesman', 4500, 500, '10-MAY-09', 1008, 30);9 k# i% \* o3 ]

2 @( S: z8 z( x9 ^* k) p! xset linesize 150
: H" b' J+ J$ rcol empno for 9999
% m- Q( B) f5 A4 b  F! v$ Kcol mgr for 9999
5 ^4 q$ P# [( s5 S6 Z' bcol deptno for 991 R% w  z! ~/ K' N8 O$ k; i
col salary for 99999.99
0 o# r) P% f) j, w; F' |. O1 X( U0 H
# [4 J# }+ K  h' f0 V* l( m  p5 qselect * from dept_ning;
& p& b. t0 C  h" D3 V* e: Fselect * from emp_ning;7 V+ j0 D% i2 p6 `8 D8 n+ I4 X2 y+ }% I- g

% {5 R% n7 h# E1 j/ _1 o* e一、学习查询语句。" I9 r0 i8 [4 E0 P  r& s) ]
1. 计算员工的名字、月薪和年薪?
; k8 E: j- s# cselect ename, salary, . Q% Q- s# ~3 l' a' m; u$ y
salary * 12 year_sal
% O8 ]  |. |, ?8 sfrom emp_ning;
* s* b: c! }$ Y9 [
1 J+ S; w) I0 [% c$ A8 c# n2.计算员工的月收入?* T( x" K3 p! Y8 e
空值和任何数据做算数运算,结果为空(null)0 k9 ^. f- }- j& U
select ename, salary, bonus,- R4 l' \- s$ l7 Q3 |# `& G
       salary + bonus month_sal
/ @* F7 V5 T( Vfrom emp_ning;# g! A2 Q  ~1 U: B- Z0 \
% M2 z6 V" k$ P. d( I
select ename, salary, bonus,
1 F  \+ T7 i! b     salary + nvl(bonus, 0) month_sal) W3 ]! y2 P& T1 c( ^) b* c
from emp_ning;# H( l) O6 e  [: M

5 a1 @2 B% \6 N' R4 Z. O& N; Wpublic double nvl(double d1, double d2){! O1 L7 A& q( X! E
        if (d1 != null)
( H/ w5 O; Z5 d  p' ]& Y            return d1;# t4 b+ x; u6 z2 f4 B7 r
        else 0 R/ f1 r3 U, \( v
            return d2;       9 C, s; @" m7 l+ `4 F
}* h2 t. V. P7 m, n) M, B
public String nvl(String s1,String s2)
' m. [( d  f" h, L: k: D{
. ]' w" Z- n; v2 z2 R5 m/ W: I2 y        if (s1 != null)
* `8 [& W8 n7 L8 p4 _                return s1;
: h$ Z% q  ]" L        else
7 t7 k7 R" [. c                 return s2;
  V6 `! K9 B* ?2 E7 y' h! I: S# W' ~}
% x5 O/ l' {! P- wpublic Date nvl(Date d1, Date d2){
7 M0 }  ]# }4 l" F        return (d1 != null) ? d1 : d2;
% Y( M4 L' q; G, y; `}7 S1 m' Y* n! V4 S) M6 i9 J
4 ~9 S  k5 Y8 Z5 u: W
insert into emp_ning! T: X3 s+ M1 P, S* D
values(1011,'余泽成',null, null, null,
% e/ Z' [3 p! ^' `1 |' Q5 Tnull, null, null);
4 x  Q- _* G/ E8 Z: Y  L简写为:' Q+ D. ~# p+ G
insert into emp_ning(empno, ename)
& G" ~5 H: ]! ]( y: V/ rvalues(1011,'余泽成');
/ o, q" D4 }8 `$ h7 _% @, D1 w4 @: L6 X9 b
查询emp_ning表,如果没有职位,显示'no position',如果有职位,显示员工的职位。6 f5 ?: z4 \9 {$ {) Q3 J$ [/ C; a7 X
select ename, nvl(job,'no position')3 e: j. R* W4 W& M4 l7 f
from emp_ning;
4 p  q) g  C7 Y# i% n1 l& U* ~3 M4 X" J# w% C7 T
查询emp_ning表,如果没有入职时间,显示2011年10月10日。如果有入职时间,显示。; m) A2 _! ~) y4 d
select ename, nvl(hiredate, '10-OCT-11') from emp_ning;
$ o9 r. Q* d" b- t3 [: n2 m% y' k7 I  y/ n4 b1 _
nvl(bonus, 0)& I& W0 F9 y* v& ?
nvl(job, 'no position')3 F! \+ i3 v% U; w
nvl(hiredate, '10-OCT-11')
: O$ f, E1 N4 y# r0 y, S2 l. H0 j- o3 L3 O& k5 x/ D
--复制表emp_YYY为emp_XXX
4 G% j9 O; L+ O# \0 [) qcreate table emp_XXX- c4 ^4 y* U4 o& }
as
& D9 S" o% l$ f$ x+ oselect * from emp_YYY;- r. Y& K# q% N
; D  H3 L. n! b$ @9 z
3. 机构中有多少种职位?- e7 \' d( B1 a4 j( Y' \0 k  S
select distinct job from emp_ning;
  \/ v' w8 y- X5 y1 z2 S
  a" L7 k: |. ?员工分布在哪些部门?  U( I- \7 s! }& d- e
select distinct deptno from emp_ning;) e/ w4 H8 }+ S9 ]" ]  k1 g: D6 o& Q

' w, c0 M3 h9 C! ydistinct必须/只能跟在select后边。7 t$ q0 w: _5 s

& `+ c6 ~! }8 Y! E4.薪水高于10000元的员工数据?1 L7 r& r4 \6 z. |. C. o
select * from emp_ning! t3 T. H) w: u+ M
where salary > 10000;/ M6 b2 E) L/ r
8 P0 v* z- H% z/ ]/ R) m
5.职位是Analyst的员工数据?
4 n' O' b1 P1 x2 E% t3 y/ o: nselect * from emp_ning
& |7 h9 v- E* h/ ?where job = 'Analyst';
) a: O  a: ~& L
4 `& U# _; F7 Y( G: Manalyst  ANALYST  anaLyst
+ i9 `, X0 t* a1 t; n  o! u$ a4 h1 o: X$ X" y$ G+ j& [' ^4 y% ^& d
select * from emp_ning* r3 {1 ]) t) e0 w! }7 _
where lower(job) = 'analyst';: D( }5 p: y% K0 b

. d' S' V& R1 G7 rselect * from emp_ning7 H0 U8 H4 j7 e) M$ A7 r
where upper(job) = 'ANALYST';- t4 X* A; K. M7 o0 Q/ z
5 O/ X( ]: S9 U. X9 B
--如果数据是analyst,查不出结果! D- x) I9 L; O6 K/ ]7 V" H
--SQL语句大小写不敏感,数据大小写敏感# s8 F3 a% T. K4 ]$ j
5 X' b9 J8 q" t' D
6.薪水大于5000并且小于10000的员工数据
3 U+ `2 j$ S& n: |/ c, z
' D- S- ]; ]# K# Jselect * from emp_ning
( D; [( \7 Q3 L' @) Lwhere salary >= 5000- t4 H* d0 L( M) m7 x9 E+ }$ }. X
and salary <= 10000;
; U% Q0 Z1 B* y* q: Q( P" {' y6 G* e8 `) @6 |1 J. l6 O+ i8 K
--在区间中:between 低值 and 高值+ r# X" J% K! p6 X8 C: `+ m* q
--闭区间:[低值,高值]
- a) u8 r' ~6 _" P$ c+ zselect * from emp_ning' k- A, b& O* u! u5 b
where salary between 5000 and 10000;  L$ F$ x# D3 i' m
0 h' w; ?: `: u- J3 Z, W
入职时间在2011年的员工?
! e7 }! I6 A, a! ]! D6 Z' Q  R['01-JAN-11','31-DEC-11']
4 A$ ^! {# l  j4 ]* k) H8 \" v% [select * from emp_ning
+ O) ]% P/ O* [' u9 c0 Bwhere hiredate between '01-JAN-11'
4 k* X; E1 N$ }$ h; Iand '31-DEC-11';3 _/ ?3 U. S. l

- v" \$ w" z6 Q; S1 p' i( b. a6 G7.列出职位是Manager或者Analyst的员工
  I, h% h& }  m" }% y
4 {. u$ x0 v8 ^( ~( S3 `5 Iselect * from emp_ning/ z4 z: _- a" j( h9 B' \
where job = 'Manager'
' V7 A( i4 O1 V+ t7 ror    job = 'Analyst';
% v+ ~. P& `" G- R6 A. s) q--等价:in (list),在列表中。
$ \8 s9 N2 [% Y4 Pselect * from emp_ning
6 r4 |5 j* V# Zwhere job in ('Manager','Analyst');
7 e' T. V/ p7 Z% a& [& v) w- w' a1 S  u
8.列出职位中有sales字符的员工数据?
, F9 x1 x4 p& y# m/ rsalesman
" Q: o4 m) p- Y) b$ N0 {sales
3 f" u6 j  I7 ^3 Y) a* o- o( O1 Rbefore sales
4 h$ l7 G3 Y2 b! J* Nafter sales( x3 _; p& ~' i: ^5 Y
before sales engineer5 r$ W) d4 |6 h! D0 ~

( v1 g' I6 Q1 ?0 B+ ~--%: 0到多个字符,跟like配合,模糊匹配
. Q6 J5 Z6 {% \% b# Fselect * from emp_ning& Z- S7 ~5 B0 T+ t$ c9 z
where job like '%sales%';+ E* I) i  s* t: Z6 l3 Z# k1 b. V
! B6 }: u1 T5 U' B# [, Q2 V
9.查询哪些员工没有奖金?% Q: Z- U1 C+ W2 l! f
select * from emp_ning2 F: M" C5 U4 Z) Z
where bonus is null;
, y' l+ u/ t! s# h# P: u9 h2 t: L# y+ y
哪些员工有奖金?9 M# ~8 Z8 n# }" Y$ K% r$ b# p
select * from emp_ning$ C  H9 @$ T, d, ]. m- \
where bonus is not null;. U* ^$ }( B- V: @& O" c; m& L2 b
. Z" E0 Z8 `7 ^
小结:: H/ o' w1 Y7 t
1)create table ...9 f2 X: l5 W4 R5 j- w* r
  drop table 表名;9 t+ }* X+ G) f* K! t
2)insert into 表名 values(...);2 c/ w% H, F8 s3 J
3)select distinct | * | 列名 | 算数表达式 | 别名5 g5 o1 k5 c( G; F% l
  from 表名
. U$ c  e" H% X+ z8 |* E; q  where 条件1 or 条件2 and 条件3;: H# {1 @' Y6 e! b# C/ V8 B
0 b8 g& D) j3 d- ~3 |) S
> >= < <= between...and... in like; j- K" Z% k0 H  t! w
/ q0 @8 T1 C4 m+ ~8 G

科帮网 1、本主题所有言论和图片纯属会员个人意见,与本社区立场无关
2、本站所有主题由该帖子作者发表,该帖子作者与科帮网享有帖子相关版权
3、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者和科帮网的同意
4、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任
5、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责
6、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意
7、科帮网管理员和版主有权不事先通知发贴者而删除本文


JAVA爱好者①群:JAVA爱好者① JAVA爱好者②群:JAVA爱好者② JAVA爱好者③ : JAVA爱好者③

快速回复
您需要登录后才可以回帖 登录 | 立即注册

   

关闭

站长推荐上一条 /1 下一条

发布主题 快速回复 返回列表 联系我们 官方QQ群 科帮网手机客户端
快速回复 返回顶部 返回列表