Oracle主鍵約束的創(chuàng)建,添加和刪除

1、Oracle主鍵約束的創(chuàng)建，添加和刪除2010-06-2710:40:16|分類：learning|字號(hào)大中小訂閱oracle的主鍵約束添加刪除1、創(chuàng)建表的同時(shí)創(chuàng)建主鍵約束一、無(wú)命名createtableaccounts(accounts_numbernumberprimarykey,accounts_balancenumber);二、有命名createtableaccounts(accounts_numbernumberprimarykey,accounts_balancenumber,constraintyyprimarykey(accounts_number)；2、刪除表中已有的主鍵約束

2、一、無(wú)命名SELECT*FROMUSER_CONS_COLUMNSWHERETALBE_NAME='accounts'找出主鍵名ALTERTABLEACCOUNTSDROPCONSTRAINTSYS_C003063;二、有命名ALTERTABLEACCOUNTSDROPCONTRAINTyy;3、向表中添加主鍵約束ALTERTABLEACCOUNTSADDCONSTRAINTPK_ACCOUNTSPRIMARYKEY(ACCOUNTS_NUMBER);oracle中notnull約束是我們用到的最多的約束之一了。我們可以在創(chuàng)建表時(shí)讓系統(tǒng)自動(dòng)指定notnull約束的名字來(lái)創(chuàng)建，也

3、可以手動(dòng)的的指定notnull約束的名字來(lái)創(chuàng)建，也可以在表創(chuàng)建好后手動(dòng)的修改表已達(dá)到增加notnull約束的目的。下面是一個(gè)例子createtabletest_not_null(idnumbernotnull,namevarchar2(30),telvarchar2(20)constrainttest_not_null_telnotnull);在這里我們創(chuàng)建了一個(gè)表，并分別給id和tel兩列創(chuàng)建了notnull的約束，其中id的約束是系統(tǒng)自動(dòng)命名的，而tel的約束使我們手動(dòng)命名的。對(duì)于地二個(gè)列name,oracle系統(tǒng)不會(huì)自動(dòng)增加notnull的約束。我們可以在這個(gè)表里面插入一行數(shù)據(jù)，例如：i

4、nsertintotest_not_null(id,tel)values(123,'88888888');我們也還可以使用修改表的方式來(lái)增加notnull約束。例如剛才我們沒(méi)有在test_not_null上建立空值約束，我們可以現(xiàn)在通過(guò)修改的方式來(lái)建立altertabletest_not_nullmodify(namenotnull);如果你執(zhí)行了剛才我們的插入數(shù)據(jù)的操作，你執(zhí)行這句話的時(shí)候可能會(huì)有以下錯(cuò)誤：ORA-02296:無(wú)法啟用(SYS.)-找到空值這是因?yàn)槲覀儎偛旁诓迦霐?shù)據(jù)時(shí)已經(jīng)給name這一列增加了一個(gè)空值，現(xiàn)在又要在這一列上增加notnull約束，顯然是不行的。如

5、果實(shí)際問(wèn)題中我們真的遇到這種需求，例如對(duì)于某個(gè)字段我們認(rèn)為原來(lái)可以為空，并且再該列插入了很多空值，后來(lái)我們發(fā)現(xiàn)實(shí)際上是不應(yīng)該為空的，可能會(huì)需先將原來(lái)的空值全部更新到一個(gè)新的有統(tǒng)一意義的值，然后在進(jìn)行空值約束的加入操作。例如我們可以這樣做：updatetest_not_nullsetname=不知道wherenameisnull;現(xiàn)在我們?cè)僭黾恿械膎otnull約束是沒(méi)有問(wèn)題了。1. altertabletest_not_nullmodify(namenotnull);表修改創(chuàng)建表：a. 創(chuàng)建xs表中計(jì)算機(jī)專業(yè)學(xué)生的備份Createtablexs_jsjasselect*fromxswherez

6、ym='計(jì)算機(jī)'；完整的例子：Createtabletest(xmchar(20)notnull,zyvarchar(30)default(計(jì)算機(jī)')；'-CreatetablecreatetableDEPT(DEPTNONUMBER(2)notnull,DNAMEVARCHAR2(14),LOCVARCHAR2(13)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64Kminextents1maxextentsunlimited)；-Create/Recreateprimary,uniqu

7、eandforeignkeyconstraintsaltertableDEPTaddconstraintPK_DEPTprimarykey(DEPTNO)usingindextablespaceUSERSpctfree10initrans2maxtrans255storage(initial64Kminextents1maxextentsunlimited)；c規(guī)則Altertable命令有許多選項(xiàng)，一個(gè)記住語(yǔ)法的方法是Oracle執(zhí)行這個(gè)操作需要的信息：1) 、你不得不告訴Oracle你準(zhǔn)備alter什么表：Altertabletable_name、然后，你準(zhǔn)備做什么？Adding一個(gè)約束A

8、LTERTABLEtable_nameADDCONSTRAINT、強(qiáng)烈建議但不要求為約束定義個(gè)名字。約束名不需要放在引號(hào)里，但會(huì)以大寫字母形式存儲(chǔ)在數(shù)據(jù)字典里，ALTERTABLEtempADDCONSTRAINTpk_temp2) 、表示約束類型將是PrimaryKey,Unique,ForeignKey，或Check約束ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY、跟在約束類型后面有幾種特殊選項(xiàng)，PrimaryKey和Unique約束，需要指定約束的列；Check約束需要指定約束規(guī)則。ALTERTABLEtempADDCONSTRAINTpk_t

9、empPRIMARYKEY(ID);3) 、PrimaryKey和Unique約束應(yīng)當(dāng)指定索引的表空間名，使用USINGINDEXTABLESPACE子句.ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY(ID)USINGINDEXTABLESPACEstudent_index;創(chuàng)建表及索引：createtableDEPT(DEPTNONUMBER(2)notnull,DNAMEVARCHAR2(14),LOCVARCHAR2(13)tablespaceUSERSpctfree10initrans1maxtrans255storage(initial64

10、Kminextents1maxextentsunlimited)；constraintemp_pkprimarycreatetableemployees(empnonumber(5),deptnonumber(3)notnull,key(empno,deptno)/有名的主鍵usingindextablespaceindexstorage(initial64Knext64K)altertableDEPTaddconstraintPK_DEPTprimarykey(DEPTNO)usingindextablespaceUSERSpctfree10initrans2maxtrans255stora

11、ge(initial64Kminextents1maxextentsunlimited);2.修改表.Altertabletable_name增加新列修改已有列和屬性修改存儲(chǔ)特征刪除約束條件Addcolumn_nametypedefaultexpressionModifydatatypedefaultexpressionStoragestorage_clauseDropdrop_clause改變表所在的表空間altertablenamemovetablespacenewtablespacenum<imgsrc="<imgsrc="<img例：在表xs中添加

12、兩列.jsj,djsm Altertablexsadd(jxjsrc="">">">ber(1),djsmvarchar(40)default獎(jiǎng)金1000')在表xs中修改名為djsm的列的默認(rèn)值A(chǔ)ltertalbexsmodify(djsmdefault獎(jiǎng)金800')在表中刪除名為jxj和djxm的列. Altertablexsdropcolumnjxj;Altertablexsdropcolumndjsm;修改表xs_kc的存儲(chǔ)特征Altertalbexspctfree20maxtrans200為表xs_jsj加上主

13、鍵Altertablexs_jsjadd(constraintapk_jsj"primarykey(xh);增加列 語(yǔ)法：ALTERTABLEschema.table_nameADDcolumn_definitiontype例：ALTERTABLEordersADDorder_dateDATE;更改列語(yǔ)法：ALTERTABLEschema.table_nameMODIFYcolumn_namenew_attributes; 例：ALTERTABLEordersMODITY(quantitynumber(10,3),statusvarchar2(15);.更改表名：altertable

14、trenametos;.更改列名altertabletrenamecolumnntos;3約束Oracle數(shù)據(jù)庫(kù)的完整性約束規(guī)則唯一性約束(Uniqueconstraint)唯一性約束可以保護(hù)表中多個(gè)數(shù)據(jù)列，保證在保護(hù)的數(shù)據(jù)列中任何兩行的數(shù)據(jù)都不相同。唯一性約束與表一起創(chuàng)建，在唯一性約束創(chuàng)建后，可以使用ALTERTABLE語(yǔ)句修改。語(yǔ)法：column_namedata_typeCONSTRAINTconstraint_nameUNIQUE如果唯一性約束保護(hù)多個(gè)數(shù)據(jù)列，那么唯一性約束要作為表約束增加。語(yǔ)法如下：CONSTRAINTconstraint_name(column)UNIQUEUSIN

15、GINDEXTABLESPACE(tablespace_name)STORAGE(storedclause)唯一性約束由一個(gè)B-tree索引增強(qiáng)，所以可以在USING子串中為索引使用特殊特征，比如表空間或存儲(chǔ)參數(shù)。CREATETABLE語(yǔ)句在創(chuàng)建唯一性約束的同時(shí)也給目標(biāo)數(shù)據(jù)列建立了一個(gè)唯一的索引。F國(guó)內(nèi)最早的網(wǎng)管網(wǎng)站CREATETABLEinsured_autos(policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,vinVARCHAR2(10),coverage_beginDATE,coverage_termNUMBER,CONSTRAINuniqu

16、e_autoUNIQUE(policy_id,vin)bitsCN_comUSINGINDEXTABLESPACEindexSTORAGE(INITIAL1MNEXT10MPCTINCREASE0)用戶可以禁用未以性約束，但他仍然存在，禁用唯一性約束使用ALTERTABLE語(yǔ)句ALTERTABLEinsured_autosDISABLECONSTRAINunique_name;刪除唯一性約束，使用ALTERTABLE.DROPCONSTRAIN語(yǔ)句ALTERTABLEinsured_autosDROPCONSTRAINunique_name;注意用戶不能刪除在有外部鍵指向的表的唯一性約束。這種

17、情況下用戶必須首先禁用或刪除外部鍵(foreignkey)。中國(guó)網(wǎng)管聯(lián)盟刪除或禁用唯一性約束通常同時(shí)刪除相關(guān)聯(lián)的唯一索引，因而降低了數(shù)據(jù)庫(kù)性能。經(jīng)常刪除或禁用唯一性約束有可能導(dǎo)致丟失索引帶來(lái)的性能錯(cuò)誤。要避免這樣錯(cuò)誤，可以采取下面的步驟：1、在唯一性約束保護(hù)的數(shù)據(jù)列上創(chuàng)建非唯一性索引。2、添加唯一性約束主鍵(PrimaryKey)約束中國(guó)網(wǎng)管聯(lián)盟表有唯一的主鍵約束。表的主鍵可以保護(hù)一個(gè)或多個(gè)列，主鍵約束可與NOTNULL約束共同作用于每一數(shù)據(jù)列。NOTNULL約束和唯一性約束的組合將保證主鍵唯一地標(biāo)識(shí)每一行。像唯一性約束一樣，主鍵由B-tree索引增強(qiáng)。網(wǎng)管求職創(chuàng)建主鍵約束使用CREATETA

18、BLE語(yǔ)句與表一起創(chuàng)建，如果表已經(jīng)創(chuàng)建了，可以使用ALTERTABLE語(yǔ)句。CREATETABLEpolicies(policy_idNUMBERCONSTRAINTpk_policiesPRIMARYKEY,holder_nameVARCHAR2(40),genderVARCHAR2(1),marital_statusVARCHAR2(1),date_of_birthDATE);與唯一性約束一樣，如果主鍵約束保護(hù)多個(gè)數(shù)據(jù)列，那么必須作為一個(gè)表約束創(chuàng)建。中國(guó)網(wǎng)管聯(lián)盟CREATETABLEinsured_autos(policy_idNUMBER,vinVARCHAR2(40),coverage

19、_beginDATE,coverage_termNUMBER,CONSTRAINTpk_insured_autosPRIMARYKEY(policy_id,vin)USINGINDEXTABLESPACEindexSTORAGE(INITIAL1MNEXT10MPCTINCREASE0);禁用或刪除主鍵必須與ALTERTABLE語(yǔ)句一起使用ALTERTABLEpoliciesDROPPRIMARYKEY;ALTERTABLEpoliciesDISABLEPRIMARYKEY;1、創(chuàng)建約束CREATETABLEstudents(student_idVARCHAR2(10)NOTNULL,stud

20、ent_nameVARCHAR2(30)NOTNULL,college_majorVARCHAR2(15)NOTNULL,statusVARCHAR2(20)NOTNULL,stateVARCHAR2(2),license_noVARCHAR2(30)TABLESPACEstudent_data;2、創(chuàng)建主鍵ALTERTABLEstudentsADDCONSTRAINTpk_studentsPRIMARYKEY(student_id)USINGINDEXTABLESPACEstudent_index;3、創(chuàng)建Unique約束ALTERTABLEstudentsADDCONSTRAINTuk_s

21、tudents_licenseUNIQUE(state,license_no)USINGINDEXTABLESPACEstudent_index;4、創(chuàng)建Check約束ALTERTABLEstudentsADDCONSTRAINTck_students_st_licCHECK(stateISNULLANDlicense_noISNULL)OR(stateISNOTNULLANDlicense_noisNOTNULL);添加check約束altertableempaddconstraintconcheck(dept_salary>0);con為約束名，dept_salary為字段名5、創(chuàng)建

22、外鍵約束ALTERTABLEstudentsADDCONSTRAINTfk_students_stateFOREIGNKEY(state)REFERENCESstate_lookup(state);6、約束.Altertabletable_nameaddconstrantsBIDprimarykey(bookno);ALERTTABLEtable_nameMODIFY(column1PRIMARYKEY);1、創(chuàng)建表的同時(shí)創(chuàng)建主鍵約束(1) 無(wú)命名createtablestudent(studentidintprimarykeynotnull,studentnamevarchar(8),age

23、int);(2) 有命名createtablestudents(studentidint,studentnamevarchar(8),ageint,constraintyyprimarykey(studentid);2、刪除表中已有的主鍵約束(1) 有命名altertablestudentsdropconstraintyy;(2) 無(wú)命名可用SELECT*fromuser_cons_columnswhere.;查找表中主鍵名稱得student表中的主鍵名為SYS_C002715altertablestudentdropconstraintSYS_C002715;(3)使約束失效：alterta

24、bletbl_employeedisableconstraintfk_emp;刪除約束：altertabletbl_departmentdropconstraintpk_dept;查詢約束：selectCONSTRAINT_NAMEfromuser_constraintswheretable_name='TBL_EMPLOYEE'selectCONSTRAINT_NAME,COLUMN_NAMEfromuser_cons_columnswheretable_name='TBL_EMPLOYEE'3. 刪除表.Droptabletable_name;4、操作表數(shù)據(jù)

25、插入表記錄：.a.insertintotable_namecol1,col2values(val1,val2);例：Insrtintoxs(xh,xm,)values('09',to_date('19860210','yyyymmdd);insertintodeptovalues('100','xieyunchao','m','22',to_date('19861104','yyyy-mm-dd'),10000)從一個(gè)表中向另一個(gè)表中插入數(shù)據(jù)Insertint

26、otable1(col1,col2,col3)select(col1,col2,col3)fromothertable使用子查詢插入數(shù)據(jù)insertintoemployee(empno,ename,sal,deptno)selectempno,ename,sal,deptofromemp; INSERTINTOEMP(ENAME,HIREDATE)VALUE('AA',TO_DTE('19802-09','YYMIM-DD>).刪除表數(shù)據(jù)： Deletefromtable_namewherecondition;.修改表記錄Updatetable_n

27、amesetcolumn_name=expression,-wherecondition.基于一張表修改另一張表的數(shù)據(jù)UPDATEEMPLEESETDEPTNO=(SELECTDENPNOFROMEMPWHEREEMPNO=7788) WHEREJOB=(SELECTJOBFROMEMPWHEREEMPNO=7788).刪除所有記錄但保留表結(jié)構(gòu).Truncatetabletable_name;.查詢數(shù)據(jù)查詢表結(jié)構(gòu)：DESCtable_nameSelectename,sal,12*sal+100fromemp注:selectcount(dinstinct(deptno)fromempa.查詢大于

28、平均的：selectempnofromempa,(selectavg(sal)assal_salfromemp)bwherea.sal>b.sal_sal;如果列中有空值時(shí)，則結(jié)果也為空(關(guān)于null值的處理(p47).如：selectename,name,12*sal+comm如果comm的值為null,結(jié)果也為null;如上所示,comm為null時(shí).則12*sal+comm也為null;解決方法是用nvl方法替換.在兩個(gè)表中查詢：以下兩種方式都是一樣的.a.selectt_phone_operation.operation_namefromt_phone_operation,t_p

29、hone_operation_chargewheret_phone_operation.operation_id=t_phone_operation_charge.operation_idandt_phone_operation_charge.phone_num='159.；b.selectoperation_namefromt_phone_operationwhereoperation_idin(selectoperation_idfromt_phone_operation_chargewherephone_num='159.'使用日期格式顯示日期：selectena

30、me,to_char(hiredate,mm-yyyy)fromempselectenamewherehiredate>to_date(1999-12-31'','yyym-dd');使用別名的三種方式a.selectenameasname,salsalary,fromempb.selectename使用連接操作符：Selectename|jobas用連接字符：Selectename|'限制重復(fù)的行：name",sal*12"annualsalary"?"employees"fromemp|'

31、;isa'|''|jobasaemployeedetailsSelectdistinctdeptnofromemp注意大小寫：Selectename,job,deptnofromempwherejob='CLERK使用between-.andl算符Selectename,salfromempwheresalbetween1000and1500;使用in運(yùn)算符Selectempno,ename,sal,mgrfromempwheremgrin(23,231,2345);Like運(yùn)算符：(模糊查詢)%代表至多任意字符代表一個(gè)任意字符如：selectenamefrom

32、empwhereenamelike's%'；顯示第三個(gè)字符為大寫A的所有信息SELECTENAME,SALFROMEMPWHEREENAMELIKE'_A%'顯示雇員名包含"_"的雇員信息(其中ESCAPE后的字符a為轉(zhuǎn)義字符)SELECTENAME,SALWHEREENAMELIKE'%a_%ESCAPE'a'Null運(yùn)算符(關(guān)于null值的處理(p47):)測(cè)試一個(gè)值是否為空：Selectename,mgrfromempwheremgrisnull(注意是isnull,不是=null)使用NVL函數(shù)處理NULL值N

33、VL函數(shù)用于將NULL轉(zhuǎn)換為實(shí)際值，其語(yǔ)法為NVL(exp1,exp2).如果是exp1是null,則返回exp2，如果exp1不為null,則返回exp1,參數(shù)exp1和exp2是任意相同的數(shù)據(jù)類型.如：SELECTENAME,SAL,SAL+NVL(COM,0)AS”月收入"fromEMP;使用NVL2函數(shù)處理NULL值語(yǔ)法為NVL(exp1,exp2,exp3),如果exp1不是null,則返回exp2,如果exp1是null,則返回exp3;用WHERE語(yǔ)句限制日期示例一.符合默認(rèn)日期格式select*fromempwherehiredate>'01-1月-82

34、'示例2:不符合默認(rèn)日期格式(需要用TO_DATE函數(shù)轉(zhuǎn)換)SELECT*FROMEMPWHEREHIREDATE>TO_DATE('1994-9-24','yyyy-mm-dd');邏輯操作符：AndornotOrderby子句a.使用orderby子句對(duì)查詢數(shù)據(jù)排序asc升序，默認(rèn)desc降序：依據(jù)多列進(jìn)行排序：Selectename,deptno,salfromemporderbydeptno,saldesc總結(jié)：SELECTDISTINCT*,COLUMNALIAS,-./DISTINCT不重復(fù)FROMtablenameWHEREcondi

35、tionORDERBYcolumn,expr,aliasASC|DESC分組查詢：常用分組函數(shù)：MAX,MIN,AVG,SUM,COUNT,VARIANCE(取方差),STDDEV(取標(biāo)準(zhǔn)差)當(dāng)使用分組函數(shù)時(shí)，分組函數(shù)只能出現(xiàn)在選擇列表，orderby，和having子句中，而不能出現(xiàn)在where和groupby子句中.當(dāng)執(zhí)行SELECT語(yǔ)句時(shí)，如果選擇列表同時(shí)包含列，表達(dá)式，和分組函數(shù)，那么這些列和表達(dá)式必須出現(xiàn)在GROUPBY子句中.selectdeptno,avg(sal),count(*)fromempgroupbydeptno;havingavg(sal)<2000;做口果在s

36、elect語(yǔ)句中同時(shí)包含groupby,having，以及orderby子句，則必須將orderby子句放在最后.呻限制分組結(jié)果時(shí)，必須要使用having,而不能在WHERE子句中使用分函數(shù)限制分組顯示結(jié)果.子查詢：子查詢的作用：在insert或createtable語(yǔ)句中使用子查詢，可以將表數(shù)據(jù)插入到目標(biāo)表中通過(guò)update子句中使用子查詢，可以修改一列事多列數(shù)據(jù)通過(guò)在where,having,startwith子句中使用子查詢，可以提供條件值單行子查詢：selectename,sal,deptnofromempwheredeptno=(selectdeptnofromempwhereename='scott多行子查詢：selectename,job,sal,deptno