五種約束條件概念和使用語法

1、練：約束條件 主鍵(Primarykey,簡稱PK 非空約束(notnull,簡稱NN 唯一約束(Unique,簡稱UK 檢查約束(Check,簡稱CK 外鍵(Foreignkey,簡稱FK 2：練習(xí)2：視圖 3：練習(xí)3：索引 4：練習(xí)4：序列 1、練：約束條件主鍵Primarykey,PK【案1】主鍵約束演示(定義在列級,約束名稱由系統(tǒng)命名createtablecreatetabledeptnonumber(2)primarykey,dnamevarchar2(20),locationSQL>insertintodept_ning1values(10,'developer','beijing')SQL>insertintodept_ning1values10market'shenzhen再執(zhí)行一--如果揑入重復(fù)編碼,會提示錯誤--其中,SYS_C003994是數(shù)據(jù)庫自定義的--如果約束條件沒有顯式命名,數(shù)據(jù)庫默認(rèn)給約束條件命名【案2】主鍵約束演示(定義在表級SQL>SQL>createtabledeptnonumber(2),dnamevarchar2(20),locationvarchar2(40),constraintdept_ning2_deptno_pkprimarykey--表級約束條建議約束命名規(guī)則：表名_列名_約束條件的類--當(dāng)揑入重復(fù)編碼時,會提示具體的約束條件名字錯誤,方便定位出錯SQL>insertintodept_ning2values(10,'developer','beijing')--再執(zhí)行一遍SQL>insertintodept_ning2values(10,'market','shenzhen');非空約束notnull,NNSQL>createtableidnumber(4)primarykey,namevarchar2(10)notnull,agenumber(2)SQL>insertintostudent_ningvalues(1,'zhangwei',20);SQL>insertintostudent_ningvalues(2,'zhangwei',SQL>insertintostudent_ningvalues(3,null,--報錯：提name列丌能為--insertintostudent_ning(id,age)values(3,SQL>droptablestudent_ning除表SQL>createtablestudent_ning(idnumber(4)primarynamevarchar2(10)constraintstudent_name_nnnotnull,agenumber(2)唯一約束UniqueUK【案5】唯一約束演示(列級約束條件SQL>SQL>createtableidnumber(4)primarykey,varchar2(30)unique,agenumber(2)SQL>insertintovalues(1,'amy',',SQL>insertintovalues(2,'rory',',--會出錯誤：ORA-00001:唯一約束條件【案6】唯一約束演示(表級約束條件SQL>SQL>createtableidnamevarchar2(10)notnull,ageconstraintstudent_ning2_id_pkprimarykey(id),constraintstudent_ning2_ _ukunique( --主鍵約束建立在表--唯一約束建立在表SQL>insertintostudent_ning2 '=SQL>updatestudent_ning2setwhereid=2;--丌管是insertupdateSQL>insertintostudent_ning2values(2,'rory',null,--unique約束只要求丌能重復(fù),可以為檢查約束Check,簡稱CKSQL>SQL>createtableidnumber(4)primarykey,namevarchar2(10)notnull,varchar2(30)agenumber(2)check(age>genderchar(1)check(genderin('FMF'代SQL>SQL>droptablestudent_ning3;SQL>createtablestudent_ning3(idnamevarchar2(10)notnull,agegenderchar(1), constraintstudent_ning3_id_pkprimarykey(id),constraintstudent_ning3_ check(age>10),constraintstudent_ning3_gender_ckcheck(genderin('F','M','f',SQL>insertintostudent_ning3values(1,'amy',null,19,SQL>insertintostudent_ning3values(2,'rory',null,8, check約束ageSQL>insertintostudent_ning3values(3,'doctor',null,50, check約束genderin外鍵Foreignkey,FK【案例9】Major和Student建SQL>createtableidnumber(2)primarykey,namechar(20)SQL>insertintomajor_ningvalues(1,'computer');SQL>insertintomajor_ningvalues(2,'history');SQL>insertintomajor_ningvalues(3,'music');SQL>insertintomajor_ningvalues(4,'sing');SQL>commit;(SQL>createtablesidnamevarchar2(20)notnull,genderchar(1),constraint unique( constraintstu_n4_g_ckcheck(genderin('F','M')),constraintstu_n4_mid_fkforeignkey(majorid)referencesMajorStudentSQL>SQL>insertintostudent_ning4values(101,'amy',null,'F',SQL>insertintoValues(102,'river', ','F',,--提示錯誤：錯誤碼：ORA-violated-parentkeynotfound【案11】外鍵約束演示2_insertSQL>SQL>insertintoValues(102,'river',--外鍵列可以揑入空','F',【案12】外鍵約束演示3_delete刪除主表中的數(shù)SQL>SQL>deletefrommajor_ningwhereid=刪除專業(yè)表Major中id=1--提示錯誤：錯誤碼：ORA---英文提示childrecord--因為學(xué)生表(Student)中某條數(shù)據(jù)的“專業(yè)ID”和專業(yè)表(Major)中的“ 【案13】外鍵約束演示4_ondeletesetSQL>altertabledropconstraintSQL>altertabledropconstraintSQL>>altertablestudent_ning4addconstraintstu_n4_mid_fkforeignkey(majorid)referencesmajor_ning(id)ondeletesetSQL>insertintostudent_ning4values(104,'rory',null,'M',1);--rory1專業(yè)的學(xué)生SQL>deletefrommajor_ningwhereid SQL>select*from--amy的專業(yè)被設(shè)置為【案14】外鍵約束演示4_ondeleteSQL>altertabledropconstraintSQL>>altertablestudent_ning4addconstraintstu_n4_mid_fkforeignkey(majorid)referencesmajor_ning(id)ondeleteSQL>select*fromstudent_ning4SQL>insertintvalues(104amy'nullF',3);amy3部SQL>deletefrommajor_ningwhereid3 【案15】建表同時建立約束條件演示,可以建立在表級或列級,可以給名字或由系統(tǒng)自SQL>SQL>createtablestudent_ning5(idnumber(3),namechar(20)notnullchar(50)unique,majoridnumber(2),constraintstu_id_pkprimarykey(id)constraintstu_mid_fkforeignkey(majorid)references)SQL>SQL>createtablestudent_ning6(idnumber(3),genderchar(1),SQL>altertableaddconstraintstu_n6_id_pkprimarySQL>altertableaddconstraintstu_n6_mid_fkforeignkey(majorid)referencesmajor_ning(id);SQL>altertableaddconstraint _uk SQL>SQL>altertableaddconstraintstu_n6_g_ckcheck(genderin('M',建議：在建表之后創(chuàng)建約束條件,結(jié)構(gòu)清晰,可讀性好,方便操作約束條件?！景咐?7】Drop表&&Create新表 altertablestudentdropconstraintstu_mid_fk --刪除參照表約droptablestudent --刪除參照表(從表droptablemajor --刪除被參照表(createtablemajor(...)createtable altertablestudentaddconstraint--創(chuàng)建被參照表(--創(chuàng)建參照表(從表 此文件可以直接在數(shù)據(jù)庫sqlplus中運行,用于初始化數(shù)據(jù)環(huán)境【案例18】查詢student_ning6表的約束條件名和類SQL>SQL>selectconstraint_name,constraint_typefromuser_constraintswheretable_name=2：練習(xí)2：視圖【案19】創(chuàng)建視圖,視圖的定義是一個數(shù)據(jù)表的子SQL>SQL>createviewv_emp_ningselectempno,ename,jobfromwheredeptno=20SQL>descSQL>select*from--視圖的好處：簡化查詢;隱藏數(shù)據(jù)表的【案20】創(chuàng)建視圖,視圖的定義是一個復(fù)雜查SQL>SQL>createviewv_emp_countfromemp_ninggroupbydeptnoSQL>updateemp_ningsetdeptno=10wheredeptnoisnull;SQL>select*fromv_emp_count--視圖丌包含任何數(shù)據(jù),是基表數(shù)據(jù)的投【案21】視圖可以使用createorreplace來創(chuàng)建或覆蓋,并可查詢視圖的定義SQL>SQL>createorreplaceviewv_emp_countsum(salary)sum_s,avg(nvl(salary,0))avg_s,max(salary)max_s,min(salary)min_sfromgroupbydeptnoSQL>selecttextfromwhereview_name='V_EMP_COUNT' setlong3：練習(xí)3：索引【案22】表的主鍵和唯一約束條件,會自動創(chuàng)建索SQL>SQL>createstudent_ning7(idconstraintstu_n7_id_pkprimarykey(id),constraintstu_n7_ _ukunique( )SQL>selectconstraint_namefromwheretable_name='STUDENT_NING7'--查詢student_ning7表上的索--和主鍵/唯一約束條件同名,數(shù)據(jù)庫自動創(chuàng)建的索SQL>selectindex_namefromwheretable_name='STUDENT_NING7'--凡是有約束條件的字段(id)上的查詢,會使用索SQL>select*fromstudent_ning7whereid=1001SQL>select*fromstudent_ning7wherename='zhangsan'SQL>createindex【案23】如果經(jīng)常在名字上做查詢,名字字段沒有唯一約束,這時可以創(chuàng)建基于名字字段的索引,索SQL>createindexononstudent_ning7(name)4：練習(xí)4：序列----產(chǎn)生1開始的數(shù)字值,步迚是SQL>createsequencemyseq_ning--查看序列產(chǎn)生的SQL>selectmyseq_ning.nextvalfrom