ORACLEµÄË÷ÒýºÍÔ¼ÊøÏê½â
OracleµÄÔ¼Êø
* Èç¹ûij¸öÔ¼ÊøÖ»×÷ÓÃÓÚµ¥¶ÀµÄ×ֶΣ¬¼´¿ÉÒÔÔÚ×ֶ춶¨ÒåÔ¼Êø£¬Ò²¿ÉÒÔÔÚ±í¼¶¶¨ÒåÔ¼Êø£¬µ«Èç¹ûij¸öÔ¼Êø×÷ÓÃÓÚ¶à¸ö×ֶΣ¬
±ØÐëÔÚ±í¼¶¶¨ÒåÔ¼Êø
* ÔÚ¶¨ÒåÔ¼Êøʱ¿ÉÒÔͨ¹ýCONSTRAINT¹Ø¼ü×ÖΪԼÊøÃüÃû£¬Èç¹ûûÓÐÖ¸¶¨£¬ORACLE½«×Ô¶¯ÎªÔ¼Êø½¨Á¢Ä¬ÈϵÄÃû³Æ
¶¨Òåprimary keyÔ¼Êø(µ¥¸ö×ֶΣ©
create table employees (empno number(5) primary key,...)
Ö¸¶¨Ô¼ÊøÃû
create table employees (empno number(5) constraint emp_pk primary key,...)
¶¨Òåprimary keyÔ¼Êø(¶à¸ö×Ö¶Î,ÔÚ±í¼¶¶¨ÒåÔ¼Êø£©
create table employees
(empno number(5),
deptno number(3) not null,
constraint emp_pk primary key(empno,deptno)
using index tablespace indx
storage (initial 64K
next 64K
)
)
ORACLE×Ô¶¯»áΪ¾ßÓÐPRIMARY KEYÔ¼ÊøµÄ×Ö¶Î(Ö÷Âë×Ö¶Î)½¨Á¢Ò»¸öΨһË÷ÒýºÍÒ»¸öNOT NULLÔ¼Êø,¶¨ÒåPRIMARY KEYÔ¼Êøʱ¿ÉÒÔΪËüµÄË÷Òý
Ö¸¶¨´æ´¢Î»Öúʹ洢²ÎÊý
alter table employees add primary key (empno)
alter table employees add constraint emp_pk primary key (empno)
alter table employees add constraint emp_pk primary key (empno,deptno)
not nullÔ¼Êø(Ö»ÄÜÔÚ×ֶ춶¨ÒåNOT NULLÔ¼Êø,ÔÚͬһ¸ö±íÖпÉÒÔ¶¨Òå¶à¸öNOT NULLÔ¼Êø)
alter table employees modify deptno not null/null
uniqueÔ¼Êø
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_ename_phone_uk unique (ename,phone)
)
alter table employees
add constraint emp_uk unique(ename,phone)
using index tablespace indx
¶¨ÒåÁËUNIQUEÔ¼ÊøµÄ×Ö¶ÎÖв»ÄÜ°üº¬Öظ´Öµ£¬¿ÉÒÔΪһ¸ö»ò¶à¸ö×ֶζ¨ÒåUNIQUEÔ¼Êø,Òò´Ë£¬UNIQUE¼´¿ÉÒÔÔÚ×ֶμ¶Ò²¿ÉÒÔÔÚ±í¼¶¶¨Ò壬
ÔÚUNIQUEDÔ¼ÊøµÄ×Ö¶ÎÉÏ¿ÉÒÔ°üº¬¿ÕÖµ.
foreign keyÔ¼Êø
* ¶¨ÒåΪFOREIGN KEYÔ¼ÊøµÄ×Ö¶ÎÖÐÖ»ÄÜ°üº¬ÏàÓ¦µÄÆäËü±íÖеÄÒýÓÃÂë×ֶεÄÖµ»òÕßNULLÖµ
* ¿ÉÒÔΪһ¸ö»òÕ߶à¸ö×ֶεÄ×éºÏ¶¨ÒåFOREIGN KEYÔ¼Êø
* ¶¨ÒåÁËFOREIGN KEYÔ¼ÊøµÄÍⲿÂë×ֶκÍÏàÓ¦µÄÒýÓÃÂë×ֶοÉÒÔ´æÔÚÓÚͬһ¸ö±íÖУ¬ÕâÖÖÇé¿ö³ÆΪ"×ÔÒýÓÃ"
* ¶Ôͬһ¸ö×ֶοÉÒÔͬʱ¶¨ÒåFOREIGN KEYÔ¼ÊøºÍNOT NULLÔ¼Êø
¶¨ÒåÁËFOREIGN KEYÔ¼ÊøµÄ×ֶγÆΪ"ÍⲿÂë×Ö¶Î",±»FORGIE
Ïà¹ØÎĵµ£º
(1)SEQNAME.NEXTVALÀïÃæµÄÖµÈçºÎ¶Á³öÀ´£¿¿ÉÒÔÖ±½ÓÔÚinsert into test values(SEQNAME.NEXTVAL) ÊÇ¿ÉÒÔÓà ÕâÑù£º
SELECT tmp#_seq.NEXTVAL
INTO id_temp
from DUAL; È»ºó¿ÉÒ ......
CSDNÀïµÄÒ»¸öÅóÓÑÎʵ½ÁËÕâ¸öË÷Òý¸²¸ÇµÄ¸ÅÄî¡£ Õâ¸ö¸ÅÄîºÜСµÄ֪ʶµã£¬ÔÚÎÒµÄÂÛ̳ÀïÓнâÊÍ“”£¬²»¹ý×÷ΪOracle°æÖ÷£¬²»ÄÜÔÚ»ØÌûÀï¼ÓÉÏÍøÍâµÄµØÖ·Á´½Ó£¬ËùÒÔÕâÀïÔÚCSDNÀïÌûÉÏÒ»·Ý
±ÈÈçÓи´ºÏË÷ÒýΪ3¸ö×ֶΣºf1 + f2 + f3,ÇëÎÊ:
1: select f1, f2, f3, f4 from table where f1 = 'XX' and f2 = 'XX'. ......
ËäÈ»ÎÒÃÇÖªµÀÉ̵À¹îÒ²,ÕâƪÎÄÕ»¹ÊÇÄܹ»ÈÃÎÒÃÇ´ÓijЩ½Ç¶ÈÉϹ۲졣±ÈÈçÅ·ÃËÓµ»¤¿ª·Å¼¼Êõ,Å·ÃËÒªÆÀ¼ÛOracleÓµÓÐMySQLºÍJavaËùÔì³ÉµÄÓ°Ïì,everything is politicalÒÑÈ»ÊÇÒ»Ìõ´ó·¹æÔòÁË£¬ÎÒÃÇ»¹ÊÇÄܹ»Ðá³öÕâ¸ö»Ï×ÓÏÂÃæÂôµÃ»¹ÊÇÀûÒæ¹Ï·ÖµÄÀϾÀ¸ð£¬×ܲ»ÄÜɶºÃʶ¼ÈÃÄãÃÀ¹úÀÐÕ¼ÁË¡£µ«ÊÇÎÒÏëÒ²Ö»ÊǸöʱ¼äÎÊÌ⣬ҲÐí±³ºóÍÆÊÖÒ ......
ÈçºÎÕýÈ·ÀûÓÃRownumÀ´ÏÞÖƲéѯËù·µ»ØµÄÐÐÊý£¿
º¬Òå½âÊÍ£º
1¡¢rownumÊÇoracleϵͳ˳Ðò·ÖÅäΪ´Ó²éѯ·µ»ØµÄÐеıàºÅ£¬·µ»ØµÄµÚÒ»ÐзÖÅäµÄÊÇ1£¬µÚ¶þÐÐÊÇ2£¬
¡¡ ÒÀ´ËÀàÍÆ£¬Õâ¸öα×ֶοÉÒÔÓÃÓÚÏÞÖƲéѯ·µ»ØµÄ×ÜÐÐÊý¡£
2¡¢rownum²»ÄÜÒÔÈκλù±íµÄÃû³Æ×÷Ϊǰ׺¡£
ʹÓ÷½·¨£º
Ï ......
update t_tmprpt_firstreplycosttime t
set (t.firstreplytime,
t.dealstaff,
t.firstreplyfailcontent)
= (select a.suggesttime,
a.suggester,
substr(a.remark,instr(a.remark,'¡¿',1)+2)
from t_wf_suggesthis a
......