该用户从未签到
|
复习:SQL语句' ?& J& x: U/ `' K; y: Z
select5 n) j" q c, W8 \' c& f; f; o5 S+ m
DML: insert / update / delete
4 W) A- [0 i5 Z6 b; m" h$ C* r7 Q% u! k. V
CRUD
) p( I, z/ S9 ?& HC:Create8 @; A- F$ `6 k2 J8 f7 i
R: Retrive
; o- h2 E Y% B+ s0 ?U: Update" A( z; t2 `: I( H
D: Delete7 Z( a3 s) s8 I0 O" J
0 b }' T. N" R; }8 H( z% C1 dDDL: create / drop / truncate / alter2 |3 C: b: o: f+ R% p7 R5 s& p0 R
" y9 L- K5 p" c7 E6 s' STCL: commit / rollback / savepoint
! {: Z! K9 `; l1 H @6 `! f/ r$ V3 R* U& P$ t
DCL: grant / revoke
" b5 Y2 f% X! {3 E. a7 r( G0 c# }- C1 C& q: i8 W
grant: 赋予权限
2 C0 n3 m( W7 M! B# orevoke: 剥夺权限3 ^0 t- r/ s5 `0 A; f
数据库中的用户: openlab hr scott b# J6 Q& M2 o
emp
2 w& j6 K d6 z: p! i0 b( c+ r$ @' u+ P$ _9 _: ?
假设现在的用户是openlab. M! W0 n2 A1 U
SQL>grant select on emp to scott;
3 n4 t; X4 M/ [SQL>revoke select on emp from scott;
+ ~8 i% t1 X: L+ C6 ^' Y7 K& R& u$ C* y; y
scott的会话:8 U. n' W+ f# C% v/ \
SQL>select * from openlab.emp;$ W' q8 J) p- I6 R1 t
SQL>select * from emp;5 g: o) H! w f
. F+ |3 R4 r4 L: |* o$ m0 I3 Z8 f
6 `4 a; p' ]* J9 k$ l- l: ?Oracle数据库的用户:( F' G2 @3 E5 P) ~, P; O' x
sys
/ s( O" c. r" P0 Xsystem. Q" b. s" T. N4 @2 _9 E
4 j! B- v' O& s! G% `" U0 \' xscott/tiger
, h5 ^/ \/ ?0 t7 H; Jopenlab/open123
5 ?# F* `* d2 K# Z; Uninglj/******7 [% k, ?# x4 q) a% J& k8 N
exam/exam123" }* k b0 ~ F( R
: W3 g& O, t# iC:>sqlplus scott/tiger@192.168.0.26:1521/tarena' f7 ^& M- ]0 n# ^+ i- Y
SQL>
, o: Z# w! f/ q& u
6 K2 K8 T$ y2 i/ n6 _7 O/ u2 B- i2 {8 C8 y' A4 h2 B A
今天的内容:7 k, D C' e! e9 H% h/ ?1 j
1、约束条件
2 x1 j- s) @3 { P. L7 \2、数据库的其他对象
. b9 D0 M* q, e) W, i3 x8 J+ O# G2 I
一、约束条件 Constraint7 e; _5 z Z% F! }) k' R
1。主键约束:Primary key, 简称PK
1 u# ^( y: m8 o* M+ A E7 x% Z--建表时增加主键约束条件& f3 K) W$ ^$ x, Z6 O4 J+ l' @& d: C
create table dept_ning1(, C7 m. g/ f+ h( i
deptno number(2) primary key, --列级约束条件8 T4 V- Q' c7 V ]" q' K
dname varchar2(20),
$ I1 y3 h2 o2 ^7 rlocation varchar2(40)
! O/ H" H5 D8 \. U& M. P5 k);$ q; K' j7 j+ l0 C) S
insert into dept_ning1
8 n& L1 p( ^) m& Z; W6 Evalues(10,'developer','beijing');9 R2 V' H. Q/ w9 |
insert into dept_ning1
9 I) k R% J3 w- R4 B6 @2 ~values(10,'market','shenzhen');: Y6 r" \1 V% j7 H6 Z9 A3 K
--如果插入重复编码,会提示:
" a. g/ u* p+ E5 b" BORA-00001: unique constraint (NINGLJ.SYS_C00634053) violated
" O: K; f. [. q9 ^: I+ U其中,SYS_C00634053是数据库自定义的主键名
# N( |; t! }6 K; B--约束条件如果没有显式命名,2 C& `, B; F6 A) d, G7 \" k5 Y, {& Q
数据库给约束条件命名:SYS_C*****( N& f. ^ g- B7 z7 `5 J
+ N! R( j' \5 E9 ^+ u* l' H--在建表时自定义约束条件名
( q8 e9 v2 g$ _: v--建议命名规则:表名_列名_约束条件的类型
3 K4 B2 N$ z" O* l& U2 Pcreate table dept_ning2(
/ e$ r- v- w6 k% P3 _# ndeptno number(2),* G/ ]8 M6 j% |: ?& B( i" b) h. l* P
dname varchar2(20),
6 M1 Q. P( P+ g& }location varchar2(40),
+ X0 w8 S: _5 D hconstraint dept_ning2_deptno_pk
) n; Q5 ~; v2 b; A- z+ z8 wprimary key (deptno) --表级约束条件! i8 w% j. I# X5 x$ U
);
9 v. F% P. _! ^5 v4 i--当插入重复编码时,会提示具体的约束条件名字错误。- Q; Y4 L# B' u& b
--方便定位出错的原因1 M! v% Z' y3 q9 v
$ x: a9 ~. n( O) ~0 }: n主键约束:primary key = 不能重复 + 不能为空: o6 b G( v. d$ o5 [* m0 x
) U# T, D- [+ z
2、非空约束: not null,简称NN
' C- _0 L2 t3 P% h2 D- q学生姓名必须提供,但是可以重复
7 G) F$ @" @1 p--只能定义在列级
1 S4 X! |8 ]* t, Q- ^create table student_ning(9 i/ r' d$ ~( F- G8 @' @
id number(4) primary key,, k# E) J# E3 W5 G2 \
name varchar2(10) not null,
0 x" k; I5 Q, B; Zage number(2)' t+ A% }* N3 ?' X5 q$ G
);
6 f( r8 ~$ N8 u7 {& |insert into student_ning
; R# v% C p# O# Q! _values(1, 'zhangwei', 20);$ f+ ?& o' k% b) [4 A# n4 `6 j3 K
--名字可以重复
. n, t4 h7 `/ p; q0 ginsert into student_ning ( ]8 p( c& d4 Y4 J$ P2 y* R
values(2, 'zhangwei', 19);9 d6 o! _7 y( B' s+ D8 p$ O
--提示name列不能为NULL- S6 K; p& {2 P8 ^2 P/ R
insert into student_ning 4 k# M b5 v: ]7 g
values(3, null, 18);( [4 o* Q$ |9 n9 B* s2 o9 j0 f; [' |
) Q0 s$ r. z" ^
3、唯一约束:Unique,简称UK
/ C. I9 P8 ^9 h: U' {4 H; qcreate table student_ning1(3 Y& E* S5 z6 t
id number(4) primary key,
! `7 y* |- D W$ F6 aname varchar2(10) not null,& [+ K2 ~6 b3 e- n! `
email varchar2(30) unique,$ [/ N' [4 Y/ j7 |# D
age number(2): z" Y+ d V c- Y
);
- j* w5 m+ U k& pinsert into student_ning1 " w! t/ J+ m* C0 |# x" E8 ~5 \2 ~; h
values(1,'amy','amy@doctor.com', 19);
; k5 `+ W4 z: T6 ~. p# r0 p--ORA-00001: 唯一约束条件被违反3 p4 N4 H1 n* a! S, u
insert into student_ning1 ! {! [+ }: D2 p' f, k: W
values(2,'rory','amy@doctor.com', 19);% a5 Z# z% W% B( c5 s
3 }( F8 @3 f0 g9 P a8 U--唯一约束建立在表级
% j; T. e- p, b, l% x' e--主键约束建立在表级
5 |, I8 ?4 Z& X# fcreate table student_ning2(
! z# Z! c# N- S- F! g; did number(4),4 [+ L- D- K% z4 h6 D) A }
name varchar2(10) not null,, a4 d) H M, ]* V. t) E
email varchar2(30),1 z+ x# h: h1 L+ }( h
age number(2),
( V1 f+ E; u/ i7 q" W; Fconstraint student_ning2_id_pk
9 A5 l' y0 l# W& sprimary key (id),
/ x$ [- n8 @5 N& zconstraint student_ning2_email_uk
* f' z% c( o! o& z4 u/ Eunique (email)
* F( {; z% e+ [: {5 r- J+ h);
$ t5 h7 F& H8 I3 g' M* E, e y1 @7 F/ ]0 o
insert into student_ning2* d0 W a9 u. p' O
values(1,'amy','amy@doctor.com',19);- @" W1 Y) l" b0 q
0 b, [) _2 J8 }% i \$ N6 e
--unique约束只要求不能重复,可以为NULL( ?& O& R1 U! w! ~( Y
insert into student_ning2
, l- Q4 x ~9 o/ _5 F- {values(2, 'rory', null, 20);
8 @6 w* `% @# p' e, u) G* G9 m7 H# x# F9 L) A% ` f
--不管是insert还是update,email都不能重复。
. G! ^; G$ _, U# M/ e7 E8 Uupdate student_ning2 set email = 'amy@doctor.com'
5 k7 s3 E7 L" n/ O/ W2 p9 k6 u3 Z! Gwhere id = 2;
! L- q1 x5 r7 l6 q0 G' \9 C
' y$ j0 B& \* v" O5 v4、检查约束 check 简称 CK) }1 m5 O' y7 L) a" i
create table student_ning3(
! y2 ~+ N) j, v" I* D2 d% Aid number(4),
* y6 z7 n* m; Y" v7 b( Ename varchar2(10) not null,7 R4 h& x% m. L9 `$ K
email varchar2(30),
) X' k% f" ?7 N: v' k0 y. Vage number(2),# G' w) ?1 H3 M0 l5 O
gender char(1), --'F':女生; 'M':男生: x) T$ A* r+ q* F" H- H: G
constraint student_ning3_id_pk0 X: R, c) t1 B( A, Q4 u R9 C2 |( r
primary key (id),
( [6 ]1 [5 `- U& @2 {: ~1 l, {constraint student_ning3_email_uk
( I$ ~* R3 v4 M& q+ _ x1 k3 }" p/ aunique (email),
* N3 m8 f% u' `) ]' bconstraint student_ning3_age_ck
# u8 h0 c( n3 Tcheck (age > 10),
9 S( w% j! b% @5 h) N# v1 Jconstraint student_ning3_gender_ck r' _+ V+ g- ^5 `4 l
check (gender in ('F', 'M', 'f', 'm'))+ ^' r/ T% W2 Z, f) y5 f! F
);
5 L1 n# r* i* Y; u; R1 oinsert into student_ning3
' |' `; H' O' F: w! k1 A; mvalues(1,'amy',null,19,'F');
M( x0 q' S( w- h) Z. b. hinsert into student_ning3
( o3 M7 }; {- {* Hvalues(2,'rory',null,8,'M'); --违反check约束 age > 10
7 T1 K! R$ U* vinsert into student_ning3 + q- s( P2 x5 D
values(3,'doctor',null,50,'A'); --违反check约束 gender in ('F','M')
6 I$ b4 D0 v6 ^+ ]3 I) i( d
H9 h& d+ O/ ^9 K, i/ ?$ `PK / NN / UK / CK / FK, L7 v' I. s' Z
( ^* W2 e: e$ Q5.外键. Foreign key, 简称FK
! a. r9 L' ~/ Ccreate table major_ning (
8 s) {2 x/ T' O3 n0 qid number(2) primary key, . {* W! r& _" a
name char(20)# ]" J$ u: N9 [. G9 q
);6 X: j+ Q) Y' Z3 G
insert into major_ning values(1, 'computer');
! o% Z. |- s4 y9 ^insert into major_ning values(2, 'history');8 n: w6 J3 c* e4 p0 a. M7 V
insert into major_ning values(3, 'music');6 G4 B/ A/ z6 o+ I' A
insert into major_ning values(4, 'sing');1 ~0 Q6 {1 \0 l) d4 G3 L; ~
commit;) z3 N) E9 G" j
create table student_ning4(- \9 ?5 K, x- V8 H, ]2 u3 a" N' I# W
sid number(3),
?" }# B* }$ [name varchar2(20) not null,
3 k6 h8 i* S& Semail varchar2(30),
+ r8 o% T& ~, z' _; Bgender char(1),
8 p+ I- e" t# V# zmajorid number(2),
& B8 b% B! x+ T" d9 ^4 Cconstraint stu_n4_sid_pk primary key(sid)," n. c: i. r, m
constraint stu_n4_email_uk unique (email),% f2 A2 K( H0 v4 ?$ A
constraint stu_n4_g_ck check (gender in ('F','M')),8 n; ]! d: @+ t! { s
constraint stu_n4_mid_fk foreign key
; x; y+ h1 ?3 P+ Y1 d d6 w& F (majorid) references major_ning(id)' T5 S, U4 i! t9 ]5 V/ m2 m
);
0 W# D% H8 } P2 L' W- D$ n' _1 V3 v7 h$ l2 m: J
insert into student_ning46 k- R# }; X, u
values(101,'amy',null,'F',1);9 s4 S& S; m- [
--新增数据,不存在9这个专业
/ B: ~: H& J+ N1 _! g/ Jinsert into student_ning4* j$ q* s. G* ~) r/ {6 C2 G
values(102,'river',1 S8 q2 n; I- i( G9 i
'river@sina.com','F', 9);
X' C9 W1 m4 R' V--提示错误:1 x' w J1 W# M/ p @3 j Y% i' s. |
ORA-02291: integrity constraint (NINGLJ.STU_N4_MID_FK)
* O# z4 F7 Y. e violated - parent key not found( T2 @0 ~* a4 [% p
insert into student_ning4
( t; c" i0 A' G# kvalues(102,'river',2 M4 I' y, R m
'river@sina.com','F', null);9 K8 m8 I# T: y% Z9 D$ o
* z& Z C0 F; F5 @$ ]% C
--有学生属于专业1(computer)3 V) i. R% E5 }
delete from major_ning where id = 1; b7 f( j4 s7 p5 w3 G
--ORA-02292: child record found
1 j$ `# f5 }, r! T3 [: }8 ~7 [) L# u
create table student_ning5(+ j B/ b, B; N( Z. o
sid number(3),
& Z6 P6 d. T- w) _8 M% x2 y! \name varchar2(20) not null,8 Y5 Y2 q- H7 t% Z$ u* ~
email varchar2(30),1 U6 w3 v4 u2 _ j! i
gender char(1),& k3 E: [ {! P0 m' _" t4 ^9 D
majorid number(2),
$ F9 {+ L& g. A5 h" W" |7 Y: O: kconstraint stu_n5_sid_pk primary key(sid),
; l, H' ^" c7 ^, e+ p; [, v2 {% `constraint stu_n5_email_uk unique (email),0 g2 F% a* | ^0 b4 i, L8 E
constraint stu_n5_g_ck check (gender in ('F','M')),
: U: j3 ]4 F4 |, {$ Q3 Rconstraint stu_n5_mid_fk foreign key 6 _5 ^, S6 I8 d+ h5 ~
(majorid) references major_ning(id)3 l) g- P' B* m# p# @0 g* _) X* p* G
on delete set null);5 B) |5 m* X, t7 a
8 N: _, A8 x; n1 k
insert into student_ning5
/ O D* f) r( A7 C: t' Dvalues(101,'amy',null,'F',4); --amy是4专业的学生6 [8 l) _# r, B1 f
--删除编码为4的专业- ]) K O- ]) H) x' Y! i# l# u; [% ^+ r
delete from major_ning where id = 4;+ a9 i6 P4 `+ d$ N! `: d
--amy的专业被设置为NULL9 x8 P4 [4 X3 H$ B. Y9 p& {/ ]6 ~8 v
select * from student_ning5; 4 c# ?( k. [/ o! v! f" S
, I ~4 K; V$ ]8 R! l8 A
create table student_ning6($ {# p! ]' g8 f1 N4 x8 F6 l
sid number(3),1 X) D4 I- {) I: e) c" l2 \2 T
name varchar2(20) not null,. g! k H$ g2 d3 d1 U5 a
email varchar2(30),
+ Y% `6 s; Z/ B/ ?& p- _gender char(1),
% F( m" {' N q$ l! bmajorid number(2)," }$ j2 M* B7 R% g
constraint stu_n6_sid_pk primary key(sid),
6 J# E7 Y$ o+ H% H+ i' F# L& Wconstraint stu_n6_email_uk unique (email),
6 Z1 n) V/ R3 z# l. P% gconstraint stu_n6_g_ck check (gender in ('F','M')),% V- o* G2 \7 W
constraint stu_n6_mid_fk foreign key
9 P: H9 s( L9 K. @ (majorid) references major_ning(id)
o2 A) y+ _# x5 A' d, ?, y on delete cascade);
: u* [& }4 C3 W3 }3 \# w0 W9 W2 P1 l9 i3 b
--复制表,不复制约束条件。
8 _, I* p, f2 h/ M# Bcreate table 表名 as 查询语句
9 f. K7 k0 W) u9 d9 @$ T n5 ~, N
9 B; ?. y' S0 v9 G$ n- Z--建立约束条件的时机
0 J% m1 n4 v, L P7 p--建表同时建立约束条件:1 F2 I. d2 ]( v* {5 S- H4 f
create table student(
f. |( \' _& Bid number(3),
" Q3 ?; q z: a# ~( p1 l- A4 w1 A9 Gname char(20) not null,
+ B4 z3 r8 i. p; }majorid number(2),! r" i* L+ U4 @: b0 g* N+ h& [
constraint stu_id_pk primary key(id),
3 q7 y7 |$ L6 f# V" i0 yconstraint stu_mid_fk foreign key(majorid). W7 m1 P5 A" U# f
references major(id): S; l. X" X/ ~$ u! e4 _$ ~+ c! u& M
);
5 }* V9 J: ?6 H$ n- t--在创建完表以后创建约束
* G5 q' e% K5 h6 K1 c7 |% U: f1 gcreate table student(
3 s0 m( G, I: Hid number(3),9 c& L1 r7 N+ g: H- o3 p) d
name char(20) not null,! p- D9 H# T9 X
majorid number(2));5 y; _% E8 V! J# o/ |
alter table student
6 Y9 [1 v7 X1 m& l add constraint stu_id_pk primary key(id);; }' U( ~5 c& h$ i
alter table student
/ b* w1 X6 A6 j add constraint stu_mid_fk foreign key(majorid)- j. f3 L3 ]" N0 S- _/ s5 I
references major(id);
2 D, O( p* G Y
3 _( f/ G' F C( h-----------脚本文件begin---------6 Z, D0 X) f' p) t$ ]
alter table student drop constraint stu_mid_fk;
& ?# c" M; h6 k6 X7 U& ~drop table student;
9 T7 y" _& F c6 Z* |* }; mdrop table major;$ w1 d' _, [! a, Z( L
create table major(....);
/ f1 G; d& r( a5 Z0 V0 R9 g* e1 gcreate table student(....);, }2 T1 x) P+ N4 |: \
alter table student add constraint ....( g) ^+ H! }6 P4 b1 x
-----------脚本文件end------------
3 k, g, h4 k: \* q. ]4 K: `8 s& c' p A9 F4 [& }% b
user_tables :用户所有的数据表$ @5 u6 _+ P2 V% l9 u/ E
user_constraints:用户所有的约束条件
& n9 f$ K' d, V2 r6 muser_objects :用户所有的对象(表、视图、索引...). D. q2 @" P# D d- F
; m1 j3 q B+ y( ball_tables :用户能访问的数据表,& K4 K3 }1 T& o e9 [5 q
包括自己的和别的用户允许自己访问的- U1 a. x# A |
all_constraints:用户能访问的约束条件
2 I9 u H* A* s- I" U1 j% ^9 {all_objects :用户能访问的对象(表、视图、索引...)
1 b6 A8 p. ^2 s' U6 r0 s6 G" ~0 J( u( t* E1 K. }8 T& V
PK / FK
& O! |3 @2 \$ r: o) X9 V. g3 RNOT NULL / UNIQUE1 v( X4 E: y- B! `: q# N
CHECK* o5 t' c" ?7 j# ^
其中:CHECK和NOT NULL可以在程序级别控制; Z6 Z4 C: K2 B' B; K
0 F8 D8 s5 }4 _# o1 e* k二、数据库的其他对象
% o1 _+ T+ f- o0 m+ i+ x4 z表 Table- t% c; c7 c% H' f
视图 View
, z' k$ w8 z" U, @4 X8 q* I0 d索引 Index9 O6 e% L4 h) E5 T3 y
序列 Sequence. u% D2 w8 {. Y% V _
过程 Procedure5 d; o: ^0 l; A. a
函数 Function
2 w' x) s* R5 b包 Package- e1 T- i8 z; A; w9 U3 O
触发器 Trigger
% _6 ^1 r8 k$ @$ q- u( h同义词 Synonym
$ s/ n5 M b- I8 v* T1 A....: G% _! I k4 Q# c2 {
4 F. @5 i8 y' E4 x
1.视图View5 j' y8 ^, {6 _" s/ G' T: Q8 Q
create view v_emp_ning
& J1 Z% Z1 D+ D, A0 I4 @as9 E" W9 [; _ ? F
select empno, ename, job from emp_ning: O3 K; e, Q5 a' E# z
where deptno = 20;
' Z* L, |% w% c' i/ i* e6 A--使用和表相同
- j. \: P* G. Rdesc v_emp_ning2 i |' ], P* \8 y
select * from v_emp_ning;/ x* h# _( S [! X! m7 z. B
--视图的好处:简化查询;隐藏数据表的列
2 r# O: M* \" n: d2 C9 H% O
' z9 N! t t: [) z' Bcreate view v_emp_count
4 U) c$ g' i# R& e# \1 T, f+ cas) \! z; x; h, M; ?# ?
select deptno, count(*) emp_num8 L, j% A* u; i7 o5 C4 t; H0 i
from emp_ning
1 p3 H3 J' l. {) V1 {& xgroup by deptno;8 e5 S3 { S9 L0 r4 @ {
--修改基表数据! j# B. p( _. A7 m3 {
update emp_ning set deptno = 10
) ?: W( s/ t/ B" q, w0 q1 ~ where deptno is null;& F0 t/ e! t% M% _" a( f. o9 I
--视图查询到的是修改后的数据。
4 G! F1 j! }+ r% |# v: c5 x--视图不包含任何数据。是基表数据的投影。. s2 z6 z7 M9 v# I5 F
select * from v_emp_count;
4 D9 b+ } ]! ?" S- W8 O& c* U0 G/ o+ Y1 @ F
--创建或修改视图+ w7 Q! p) b. x0 ^6 A8 ^
create or replace view v_emp_count! h. A+ X' o' e+ [- m: {
as
d: T- s6 \* s6 |select deptno, count(*) emp_num,# q$ } { n/ C* J$ B) f. x3 ]
sum(salary) sum_s,
4 y1 {, G; C- R) k* pavg(nvl(salary,0)) avg_s,
) I% X# g; _% y* dmax(salary) max_s,- i( m0 y. K% K4 K# h4 @( ~# _
min(salary) min_s
; Z: m3 l9 H0 S% w/ }from emp_ning1 r$ A2 j& \; [: X4 p* ]
group by deptno;
- w% m8 F! q$ G" `5 G3 ]; H
* @+ Q) o3 w. h" ^$ ]--查询视图的定义- b8 _8 {8 E3 I3 g* ?1 s
select text from user_views
) Y! l2 d' C& C/ O" owhere view_name = 'V_EMP_COUNT';
) s* d( B; v {) R. A& \* U3 w--如果视图对应的sql语句显示不全
/ E4 U' t' a$ o3 ~: y) h6 rset long 1000
- W, G0 A5 Y& H9 Y& L" _* K
- P% d9 y8 s% q8 h5 M M% d1 T2.索引 Index :用来提高查询效率的机制。
6 G# d8 L, x& x$ j/ G. d全表扫描: Full Table Scan: 查询效率极低
$ n: k' o2 r: | o! H索引查询:比全表扫描快。
% s9 \1 ~8 E5 k5 W- |. `+ z, J; A$ n$ o( c2 c% _
索引的结构:数据 + 地址1 i3 Y, o7 u* E% j
张三 + Room2036 T* U Z/ i% m5 K1 h! `- |
" d% R3 d( q% I4 x7 a/ X& ^
对于数据变更频繁(DML操作频繁)的表,# F% w: ^8 f0 u! N! q
索引会影响性能。
3 S+ Z7 ?. l* t5 P" w
# q% P& A! t, w' A/ K& S" X. ?如果数据表有PK/Unique两种约束,索引自动创建
y& b( b# h. I, r+ t, r6 d3 Q" y- x除此以外,索引必须手动创建。
- s5 Z, h) r9 ]" @" e+ F( @5 d; tcreate table student_ning7(4 M" s, D3 ?% |* t( G8 q; `0 H) }
id number(4),1 D5 F+ {( r$ n7 ~" @4 b( v- o
name char(20),
' ] E8 |; z o/ ~0 @+ j5 pemail char(40),
5 S2 S/ j$ h# z6 K }constraint stu_n7_id_pk primary key(id),
4 X; _7 k& P F0 q5 d* Cconstraint stu_n7_email_uk unique(email)' C5 X7 F4 v, l
);) B0 u! q' `( @! m0 }
select constraint_name * E8 }: V' X$ N' h9 L. R: R
from user_constraints: C8 f& d& V5 j/ q, r' P
where table_name = 'STUDENT_NING7';
1 g9 z' k& ?; E% s7 B- R5 H' M* ^! u+ o. V
--查询student_ning7表上的索引,
5 j% t. ~ R! [2 K2 u1 G和主键/唯一约束条件同名,数据库自动创建的索引。, g" O) D& @. F
select index_name from user_indexes
9 o* \4 W" p+ n) N$ F; T6 rwhere table_name = 'STUDENT_NING7';
0 Y! \) m2 |1 Z$ }; m" P( G( n) E+ Y: z+ f+ g% u
--凡是id或email上的查询,会使用索引+ x$ ^# E0 y9 A# T
select * from student_ning7* l e. M3 S" n s$ |: f
where id = 1001;" `& w8 o& j; m3 w' z
% E' S# D) M" M6 N
--这种查询用不到索引:全表扫描
7 V+ V' F; y; J' b' H( L- Iselect * from student_ning7
1 r2 l. r/ }8 y2 _: ?4 _8 Uwhere name = 'zhangsan';% Y$ W! P2 O1 \) Q" u
1 L7 x4 o: i- X0 f# @$ T' g( _& k
--创建基于名字字段的索引,索引名自定义
0 y. X( ?9 G, z7 x" @: |create index 索引名 on 表名(列名);6 l& J# w% y) {1 k
create index idx_stu7_name
) g& {8 h1 C/ G; r2 ~8 \$ c: r Fon student_ning7(name);
# r& C# t8 A9 Q2 q$ r9 `; R( G8 e0 K" r; I+ {6 w' W6 t
索引:
5 `3 N' p4 `) }/ _' p' Z1)了解索引的工作原理3 `0 m8 z( b p: k
2)pk/uk自动创建索引
E, g0 a# s* J! _1 Y0 v' ~6 j& t3)根据查询情况决定手动创建哪些索引。
0 D3 _4 f6 ]- }, i$ i& S- F- H1 t( ^% u- c* Q" V1 I! `2 c. D
1 M5 I7 n9 {+ a6 F! D7 i. q7 T0 K3、序列 Sequence --Oracle独有的
+ L# P7 A8 v7 ]/ ^# o! F--产生从1开始的数字值,步进是16 z' v1 _: }0 X* x+ A
create sequence myseq_ning;
: T6 |- _1 A+ p0 M* K1 C8 iselect myseq_ning.nextval from dual;; e+ n. }9 b' o+ F4 ^" j& d' z
) h9 D' o! ?0 {+ I
序列的特性:产生连续的不同的数字值0 H' [. o% I; g, L1 C) a* W5 q
用来作为数据表的主键。
/ u8 a; v6 q% u( T% H2 v" C" n- N6 K( T( p
--使用序列产生的值作为表的主键值
# W. J5 x! `6 B5 ], ~2 Qinsert into student_ning7(id,name)
2 O6 p1 N. n) I7 U; Ivalues(myseq_ning.nextval, 'amy');4 x/ u$ e9 a ]6 t
( a- M4 l4 f) P9 e- f* Sstudent_ning7和序列myseq_ning的关系:. z1 K5 H. |- g& X6 R
是数据库中的独立对象! K* B" h9 F% ]+ P9 E
--表可以用序列产生的值作为主键,也可以不用6 \6 V$ T# Q7 I+ `" E3 p' Q0 u: ~
--序列可以为一个或多个表产生主键,也可以不用* ?- c, b9 |, `* S Q
insert into student_ning7(id,name)
9 O/ J3 ?& ~- p/ E* w4 D# m1 Xvalues(100,'river');
: r2 k; ^& c: {" K( z0 sinsert into dept_ning
1 \7 K# f. S6 |& K6 p( Pvalues(myseq_ning.nextval,'market','bj');
0 R% {" y2 d2 b! U8 P& I
# q. s* L: _) [ P; o8 v--建议:一个序列为一个表产生主键/ i" J& o8 x& t1 b+ k" r( U
E2 k X F. F* E希望主键值从1000开始,步进是2?$ [" g+ o" O% j# k5 A% V5 g
create sequence myseq_ning1
$ d$ B. {7 n" s! b9 u/ u- j; Sstart with 1000) _4 h& e% G" ]' G8 l
increment by 2;) z* n3 [$ u! X9 ~
$ u7 z9 K T( B8 x
insert into student_ning7
" z1 _0 j, e5 Lvalues(myseq_ning1.nextval, 'song', null);
" z1 `0 o% U! Q7 g2 t, k7 c" j9 ~7 _! G! o" ?5 q5 |- _& o; d
--删除序列,对曾经产生过的数据没有任何影响。, ~3 a. }5 ^) Y" S7 Y) C3 v
drop sequence myseq_ning1;8 x0 A0 X- @# ]+ u# q
, U8 q4 ^6 D5 A& c2 v0 e7 e
SQL> edit
2 e3 A9 w: Y5 I+ D" N. }- v! L3 h: [' w4 ^: b- ]$ V. |
|
|