第06章索引與數據完整性課件

陳章良E-MAIL：SDIBT2008@126.COM

SQLServer實用教程第6章索引與數據完整性案例一:從21萬數據出查詢一條記錄。案例二:重復插入數據。select*fromuser_fileswhereuser_name='李木聲‘goinsertintoperson(bh,xm,xb,dz)values('01001','王長好','1','山東煙臺')go山東工商學院陳章良第6章索引與數據完整性6.3數據完整性6.2默認值約束與默認值對象6.1索引山東工商學院陳章良6.1.2索引的創(chuàng)建6.1.1索引的分類6.1索引6.1.3索引的刪除山東工商學院陳章良6.1索引索引是一個單獨的、物理的數據庫結構，它是某個表中一列或若干列值的集合，以及相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引包括：數據和指針。指針I(yè)D編碼出版社0x011SHS上海三聯(lián)出版社0x022JXG機械工業(yè)出版社0x033TSH清華大學出版社0x044DZG電子工業(yè)出版社0x055BJU北京大學出版社編碼指針BJU0x05DZG0x04JXG0x02SHS0x01TSH0x03索引頁數據頁山東工商學院陳章良6.1索引數據檢索流程：(1)搜索索引頁；(2)找到所需數據的指針；(3)直接通過指針從數據頁面中讀取數據。索引頁數據頁指針I(yè)D編碼出版社0x011SHS上海三聯(lián)出版社0x022JXG機械工業(yè)出版社0x033TSH清華大學出版社0x044DZG電子工業(yè)出版社0x055BJU北京大學出版社編碼指針BJU0x05DZG0x04JXG0x02SHS0x01TSH0x03DZG0x040x04DZG電子工業(yè)出版社假設查找”電子工業(yè)出版社”山東工商學院陳章良6.1.1索引的分類SQLServer中索引包括：（1）聚集索引(Clustered)（2）非聚集索引(NonClustered)1.聚集索引(Clustered)(1)聚集索引指數據行的物理存儲順序與索引順序完全相同，即聚集索引與數據是混為一體的。(2)用聚集索引查找數據很快，它所需要的空間也就特別大。(3)表的數據行只能以一種排序方式存儲在磁盤上，所以一個表只能有一個聚簇索引。(4)創(chuàng)建表主健時自動創(chuàng)建的索引是聚集索引.山東工商學院陳章良6.1.1索引的分類2.非聚集索引(NonClustered)(1)非聚集索引不改變表數據行的物理順序，數據存儲在一個地方，索引存儲在另一個地方，數據與索引完全分離。(2)一個表可以建多個非聚集索引。山東工商學院陳章良6.1.2索引的創(chuàng)建(1)對于XSB表，按學號建立主鍵（PRIMARYKEY約束），索引組織方式為聚集索引；(2)對于XSB表，按姓名建立唯一索引（UNIQUE約束），索引組織方式為非聚集索引；(3)對于KCB表，按課程號建立主鍵，索引組織方式為聚集索引；(4)對于CJB表，按學號+課程號建立主鍵，索引組織方式為聚集索引。下面以XSB表中按姓名建立索引為例，介紹索引的創(chuàng)建方法。在PXSCJ數據庫中，經常要對XSB、KCB、CJB三個表查詢和更新，為了提高查詢和更新速度，可以考慮對三個表建立如下索引：山東工商學院陳章良6.1.2索引的創(chuàng)建第2步選擇選擇XSB表的圖標，右擊，出現如圖6.1所示的快捷菜單，選擇“設計表”菜單項，出現如圖6.2所示XSB的表設計器界面；第1步進入資源管理器，打開PXSCJ數據庫；1.通過資源管理器建立索引山東工商學院陳章良6.1.2索引的創(chuàng)建第3步在XSB表設計器界面上右擊，出現如圖6.3所示的快捷菜單，快捷菜單中選擇“索引/鍵”菜單項，出現如圖6.4所示XSB的屬性界面；山東工商學院陳章良6.1.2索引的創(chuàng)建第4步在“索引／鍵”屬性頁選擇“新建”命令，然后，確定新索引的名稱（用系統(tǒng)缺省的名或重新取名）；在“列名”下選擇要創(chuàng)建索引的列，在此選擇“學號”這一列，最多可選擇16列。山東工商學院陳章良6.1.2索引的創(chuàng)建2.利用SQL命令建立索引CREATE[UNIQUE] /*是否為唯一索引*/[CLUSTERED|NONCLUSTERED] /*索引的組織方式*/INDEXindex_name /*索引名稱*/

ON{table|view}(column[ASC|DESC][,...n])/*索引定義的依據*/[WITH<index_option>[,...n]] /*索引選項*/[ONfilegroup]/*指定索引文件所在的文件組*/

<index_option>::=

{PAD_INDEX|

FILLFACTOR=fillfactor|

IGNORE_DUP_KEY|

DROP_EXISTING|

STATISTICS_NORECOMPUTE|

SORT_IN_TEMPDB

}語法格式山東工商學院陳章良6.1.2索引的創(chuàng)建USEPXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='kc_name_ind')DROPINDEXKCB.ix_kc_nameGOCREATEINDEXix_kc_nameONKCB(kcm)GOUSEPXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='kc_id_ind')DROPINDEXKCB.ix_kc_idGOCREATEUNIQUECLUSTEREDINDEXix_kc_idONKCB(kch)GO【例6.1】為KCB表的課程名列創(chuàng)建索引?！纠?.2】根據KCB表的課程號列創(chuàng)建唯一聚集索引，因為指定了CLUSTERED子句，所以該索引將對磁盤上的數據進行物理排序。山東工商學院陳章良6.1.2索引的創(chuàng)建USEPXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname=‘cj_ind')DROPINDEXix_cjGOCREATEINDEXix_cjONCJB(xh,kch)GOUSEPXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='score_ind')DROPINDEXix_scoreGOCREATENONCLUSTEREDINDEXix_scoreONXSB(xf)WITHFILLFACTOR=60GO【例6.3】根據CJB表的學號列和課程號列創(chuàng)建復合索引。【例6.4】根據XSB表中的總學分列創(chuàng)建索引，例中使用了FILLFACTOR子句。山東工商學院陳章良6.1.2索引的創(chuàng)建USEPXSCJIFEXISTS(SELECTnameFROMsysindexesWHEREname='xs_ind')DROPINDEXXSB.ix_xsCREATEUNIQUECLUSTEREDINDEXix_xsONXSB(xh)WITHIGNORE_DUP_KEY【例6.5】根據XSB表中學號列創(chuàng)建唯一聚集索引。如果輸入了重復的鍵，將忽略該INSERT或UPDATE語句。創(chuàng)建索引有如下幾點要說明：（1）在計算列上創(chuàng)建索引（2）在視圖上創(chuàng)建索引山東工商學院陳章良6.1.2索引的創(chuàng)建Createviewv_xswithschemabindingAsSelectxm,zyFromdbo.xsbWheredbo.xsb.zy='計算機'【例6.6】創(chuàng)建一個視圖，并為該視圖創(chuàng)建索引。USEPXSCJGO--在視圖上創(chuàng)建索引CREATEUNIQUECLUSTEREDINDEXix_xmONv_xs(xm)GO山東工商學院陳章良6.1.3索引的重建語法格式：ALTERINDEXindex_name|AllOntable_nameREBUILDALTERINDEXix_xsb_xmOnXSBREBUILD【例6.7】重建表XSB的一個索引名為ix_xsb_xm的索引。ALTERINDEXAllOnXSBREBUILD【例6.8】重建XSB上所有索引山東工商學院陳章良6.1.4索引的刪除第1步進入資源管理器，并與指定的數據庫相連接；第2步為要刪除索引的表打開表設計器，在表設計器中右擊，然后從快捷菜單中選擇“屬性”命令；第3步選擇“索引/鍵”選項卡，從“選定的索引”列表中選擇要刪除的索引，選擇“刪除”按鈕。1.通過資源管理器界面刪除索引山東工商學院陳章良6.1.4索引的刪除語法格式：DROPINDEX'table.index|view.index'[,...n]DROPINDEXXSB.ix_xm2.通過SQL命令刪除索引【例6.9】刪除PXSCJ數據庫中表XSB的一個索引名為ix_xm的索引。山東工商學院陳章良6.2.2默認值對象的定義、使用與刪除6.2.1在表中定義及刪除默認值約束6.2默認值約束與默認值對象用戶在插入新的數據行時，如果沒有為某一列指定數據，那么系統(tǒng)將默認值賦給該列。（1）默認約束（2）默認對象注意：SQLServer的未來版本中將刪除Default對象。山東工商學院陳章良6.2.1在表中定義及刪除默認值約束USEPXSCJCREATETABLEXSB(xhvarchar(6)NOTNULL,xmvarchar(8)NOTNULL, zyvarchar(10)NULL, xbchar(1)NOTNULL, cssjdatetimeNOTNULL, zxfnumeric(6,2)NULL, bztextNULL, rxsjdatetimedefaultgetdate())GO【例6.8】在定義表時定義一個字段的默認值約束。CREATETABLEtable_name/*指定表名*/(column_namedatatypeNOTNULL|NULL[DEFAULTconstraint_expression]

/*缺省值約束表達式*/[,…n])語法格式1.默認值約束的定義山東工商學院陳章良6.2.1在表中定義及刪除默認值約束USEPXSCJALTERTABLEXSBADDrxsjdatetimeNULLDEFAULTgetdate()USEPXSCJALTERTABLEXSBDROPCONSTRAINTdft_rxsjGO【例6.9】在修改表時定義一個字段的默認值約束。2.默認值約束的刪除【例6.10】刪除上例定義的默認值約束。CONSTRAINTdft_rxsjDEFAULTgetdate()山東工商學院陳章良6.2.2默認值對象的定義、使用與刪除語法格式：CREATEDEFAULTdefault_nameASconstant_expression語法格式：sp_bindefault[@defname=]'default',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']注意：SQLServer的未來版本中將刪除Default對象。（1）通過SQL命令定義DEFAULT默認值對象（2)通過系統(tǒng)存儲過程綁定DEFAULT默認值對象山東工商學院陳章良6.2.2默認值對象的定義、使用與刪除--1定義表CREATETABLEbook(book_idchar(6)namevarchar(20)NOTNULL,hire_datedatetimeNOTNULL)GO【例6.12】首先在PXSCJ數據庫中定義表book以及名為today的默認值，然后將其綁定到book表的hire_date列。--3綁定默認值對象EXECsp_bindefault'today','book.hire_date'--2創(chuàng)建默認值對象CREATEDEFAULTtodayASgetdate()GO山東工商學院陳章良6.2.2默認值對象的定義、使用與刪除CREATEDEFAULTzxf_defaultAS0USEPXSCJEXECsp_bindefault‘zxf_default’,’XSB.zxf’GO【例6.11】對于如前所述的PXSCJ數據庫中XSB表的總學分字段，可用如下程序段實現初始值設置為0。山東工商學院陳章良6.2.2默認值對象的定義、使用與刪除USEPXSCJEXECsp_unbindefault‘xsb.zxf'DROPDEFAULTzxf_default【例6.14】解除默認值對象zxf_default與xsb.zxf的綁定關系，然后刪除名為zxf_default的默認值對象。語法格式：DROPDEFAULT{default}[,...n]刪除默認值對象語法格式：sp_unbindefault[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']利用sp_unbindefault解除綁定關系（3)默認值對象的刪除山東工商學院陳章良6.3.3域完整性的實現6.3.1數據完整性的分類6.3數據完整性6.3.2實體完整性的實現6.3.4參照完整性的實現山東工商學院陳章良6.3.1數據完整性的分類2.域完整性實體完整性又稱為行的完整性，要求表中有一個主鍵，其值不能為空且能唯一地標識對應的記錄。1.實體完整性3.參照完整性又稱為引用完整性又稱為列完整性，指給定列輸入的有效性CREATETABLEKCB( kchvarchar(6)NOTNULL, kcmvarchar(8)NOTNULL,xqintNOTNULL,

xfnumeric(4,2)CHECK(xf>=0ANDxf<=10)NULL, bztextNULL)GO【例6.15】定義表KCB的同時定義學分的約束條件。山東工商學院陳章良6.3.1數據完整性的分類XSB和CJB表的對應關系如表6.2、6.3所示。(1)從表不能引用不存在的鍵值。(2)如果主表中的鍵值更改了，那么在整個數據庫中，對從表中鍵值的所有引用要進行一致的更改。(3)如果主表中沒有關聯(lián)的記錄，則不能將記錄添加到從表。山東工商學院陳章良6.3.2實體完整性的實現實體完整性PRIMARYKEY約束UNIQUE約束(1)一個數據表只能創(chuàng)建一個PRIMARYKEY約束，但一個表中可根據需要對不同的列創(chuàng)建若干個UNIQUE約束；(2)PRIMARYKEY字段的值不允許為NULL，而UNIQUE字段的值可取NULL；(3)一般創(chuàng)建PRIMARYKEY約束時，系統(tǒng)會自動產生索引，索引的缺省類型為簇索引。PRIMARYKEY約束與UNIQUE約束的主要區(qū)別如下：PRIMARYKEY約束與UNIQUE約束的相同點在于：二者均不允許表中對應字段存在重復值。山東工商學院陳章良6.3.2實體完整性的實現1．利用資源管理器創(chuàng)建和刪除PRIMARYKEY約束（1)利用資源管理器創(chuàng)建PRIMARYKEY約束第1步選擇XSB表圖標，右擊，打開XSB表的表設計器，進入如圖6.16所示的表設計器界面?！纠?.22】

XSB表按學號建立PRIMARYKEY約束第2步選中“學號”對應的這一行，選擇主鍵圖標，這樣在“學號”對應的這一行前面，將出現一主鍵圖標。山東工商學院陳章良6.3.2實體完整性的實現第1步選擇XSB表圖標，右擊，打開XS表設計器，進入如圖6.17所示的表設計器界面。第2步選中XSB表設計器中主鍵對應的行，選擇工具欄的“設置主鍵圖標”，則取消了原來定義的主鍵。如果要對XSB表中的“身份證號碼”列創(chuàng)建UNIQUE約束，以保證該列取值的唯一性，可按以下步驟進行：第1步選擇XS表圖標，右擊，打開XSB表設計器，在表設計器中右擊，出現如圖6.18所示的快捷菜單。第2步選擇“屬性”菜單項，出現屬性界面，在此界面中選擇“索引/鍵”選項卡，如圖6.19所示。2．利用資源管理器創(chuàng)建和刪除UNIQUE約束（2)利用資源管理器刪除PRIMARYKEY約束（1)利用資源管理器創(chuàng)建UNIQUE約束山東工商學院陳章良6.3.2實體完整性的實現第3步在圖6.19的屬性界面中選擇“新建”，輸入新建索引的名字或使用系統(tǒng)缺省名，在“列名”下拉表中選擇“身份證號碼”，并設置索引順序，將“創(chuàng)建UNIQUE約束”單選按鈕設置為選中狀態(tài)，如圖6.20所示。山東工商學院陳章良6.3.2實體完整性的實現進入如圖6.20所示的屬性窗口，在“選定的索引”下拉框中選擇要刪除的UNIQUE約束的索引名，再點擊“刪除”按鈕即刪除了指定的UNIQUE約束。（2)利用資源管理器刪除UNIQUE約束山東工商學院陳章良6.3.2實體完整性的實現3．利用SQL命令創(chuàng)建及刪除PRIMARYKEY約束或UNIQUE約束(1)創(chuàng)建表的同時創(chuàng)建PRIMARYKEY約束或UNIQUE約束(2)通過修改表創(chuàng)建PRIMARYKEY約束或UNIQUE約束創(chuàng)建PRIMARYKEY約束ALTERTABLEtable_nameADD

[CONSTRAINTconstraint_name]

PRIMARYKEYCLUSTERED|NONCLUSTERED(column[,...n])語法格式CREATETABLEtable_name/*指定表名*/(column_namedatatype/*定義字段*/[CONSTRAINTconstraint_name]/*約束名*/

NOTNULLPRIMARYKEY｜UNIQUE/*定義約束類型*/[CLUSTERED|NONCLUSTERED]/*定義約束的索引類型*/ [,

…n]

)/*n表示可定義多個字段*/語法格式山東工商學院陳章良6.3.2實體完整性的實現USEXSCJCREATETABLEXSB(xhvarchar(6)NOTNULLCONSTRAINTpk_xhPRIMARYKEY,xmvarchar(8)NOTNULL,sfzvarchar(20)CONSTRAINTuk_sfzUNIQUE,--身份證zyvarchar(10)NULL,xbchar(1)NOTNULL,cssjdatetimeNOTNULL,zxfnumeric(6,2)NULL,bztextNULL,rxsjdatetimeCONSTRAINTdft_rxsjdefaultgetdate())【例6.24】對PXSCJ數據庫中XS表的學號字段創(chuàng)建PRIMARYKEY約束，對身份證號碼字段定義UNIQUE約束。山東工商學院陳章良6.3.2實體完整性的實現【例6.25】先在PXSCJ數據庫中創(chuàng)建表XSB，然后通過修改表，對學號字段創(chuàng)建PRIMARYKEY約束，對身份證號碼字段定義UNIQUE約束。USEPXSCJCREATETABLEXSB( xhvarchar(6)NOTNULL,xmvarchar(8)NOTNULL,sfzvarchar(20),zyvarchar(10)NULL,xbchar(1)NOTNULL,cssjdatetimeNOTNULL,zxfnumeric(6,2)NULL,bztextNULL,rxsjdatetimeCONSTRAINTrxsjfltdefaultgetdate())GOALTERTABLEXSBADDCONSTRAINTpk_xsPRIMARYKEY(xh)GOALTERTABLEXSB

ADDCONSTRAINTuk_sfzUNIQUE(sfz)GO山東工商學院陳章良6.3.2實體完整性的實現(3)刪除PRIMARYKEY約束或UNIQUE約束ALTERTABLEXSBDROPCONSTRAINTpk_xs,uk_sfzGO【例6.26】刪除前面例中創(chuàng)建的PRIMARYKEY約束和UNIQUE約束。ALTERTABLEtable_nameDROPCONSTRAINTconstraint_name[,…n]語法格式山東工商學院陳章良6.3.3域完整性的實現域完整性檢查約束(Check)規(guī)則對象(Rule)注意：SQLServer的未來版本中將刪除Rule對象。山東工商學院陳章良6.3.3域完整性的實現第1步在CJB表設計器界面右擊鼠標，出現如圖6.8所示的快捷菜單；1．CHECK約束的定義與刪除（1)通過資源管理器創(chuàng)建與刪除CHECK約束山東工商學院陳章良6.3.3域完整性的實現第2步選擇CHECK約束菜單項進入如圖6.9所示的CHECK選項卡屬性窗口；第3步選擇“新建”，進入CHECK約束的輸入窗口，輸入約束表達式“cj>=0andcj<=100”，如圖6.10所示，選擇“關閉”。山東工商學院陳章良6.3.3域完整性的實現（2)利用SQL語句在創(chuàng)建表時創(chuàng)建CHECK約束CREATETABLEtable_name/*指定表名*/(column_namedatatypeNOTNULL|NULL[DEFAULTconstraint_expression]/*缺省值約束表達式*/[[check_name]CHECK

(logical_expression)]/*CHECK約束表達式*/ [,…n])語法格式CREATETABLEKCB( kchvarchar(6)NOTNULL, kcmvarchar(8)NOTNULL,

xqintCHECK(xqbetween1and8)NOTNULL, xfnumeric(4,2)CHECK(xf>=0ANDxf<=10)NULL, bztextNULL)GO【例6.15】定義表KCB的同時定義學期、學分的約束條件。山東工商學院陳章良6.3.3域完整性的實現語法格式：ALTERTABLEtable_nameADDCONSTRAINTcheck_nameCHECK(logical_expression)USEPXSCJALTERTABLECJBADDCONSTRAINTchk_cjCHECK

(cj>=0andcj<=100)語法格式：ALTERTABLEtable_nameDROPCONSTRAINTcheck_nameUSEPXSCJALTERTABLECJBDROPCONSTRAINTchk_cjGO（3)利用SQL語句在修改表時創(chuàng)建CHECK約束【例6.18】刪除PXSCJ數據庫中CJB表成績字段的CHECK約束?！纠?.17】通過修改XSCJ數據庫的XS_KC表，增加成績字段的CHECK約束。（4)利用SQL語句刪除CHECK約束山東工商學院陳章良6.3.3域完整性的實現語法格式：CREATERULEruleAScondition_expression1)規(guī)則對象的定義注意：SQLServer的未來版本中將刪除Rule對象。山東工商學院陳章良6.3.3域完整性的實現另外有如下幾點需說明：創(chuàng)建的規(guī)則對先前已存在于數據庫中的數據無效。規(guī)則表達式的類型必須與列的數據類型兼容。如果列同時有默認值和規(guī)則與之關聯(lián)，則默認值必須滿足規(guī)則的定義。語法格式：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']2)將規(guī)則對象綁定到自定義類型或列3)應用舉例

山東工商學院陳章良6.3.3域完整性的實現USEPXSCJCREATERULEkc_ruleAS@rangelike‘[1-5][0-9][0-9]’GOUSEPXSCJEXECsp_bindrule'kc_rule','KCB.kch'GOUSEPXSCJCREATERULElist_rule

AS@listIN('C語言','離散數學','微機原理')GOGOUSEPXSCJEXECsp_bindrule'list_rule','KCB.kcm'GO【例6.20】創(chuàng)建一個規(guī)則，用以限制輸入到該規(guī)則所綁定的列中的值只能是該規(guī)則中列出的值。【例6.19】如下程序創(chuàng)建一個規(guī)則，并綁定到表KCB的課程號列，用于限制課程號的輸入范圍。山東工商學院陳章良6.3.3域完整性的實現USEPXSCJEXECsp_unbindrule'KCB.kch'DROPRULEkc_rule【例6.22】解除課程號列與kc_rule之間的綁定關系，并刪除規(guī)則對象kc_rule。4)規(guī)則對象的刪除語法格式：DROPRULE{default}[,...n]刪除規(guī)則對象語法格式：sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly_flag']利用sp_unbinderule解除綁定關系山東工商學院陳章良6.3.4參照完整性的實現第1步定義XSB表中的學號字段為主碼。第2步選擇企業(yè)管理器目錄樹中PXSCJ數據庫目錄下的“關系圖”圖標右擊，出現如圖6.21所示的快捷菜單。第3步選擇菜單項“新建數據庫關系圖”，進入如圖6.22所示的歡迎界面。1．利用資源管理器定義表間的參照關系例如：要建立XSB表與CJB表之間的參照完整性山東工商學院陳章良6.3.4參照完整性的實現第4步選擇歡迎界面中的“下一步”按鈕，進入如圖6.23所示“創(chuàng)建關系圖向導”的界面，從可用表中選擇要添加到關系圖中的表，本例中選擇了XSB表和CJB表。第5步點擊“下一步”按鈕，并按提示進行后繼操作，進入如圖6.24所示的關系圖界面。山東工商學院陳章良6.3.4參照完整性的實現第6步在關系圖上，將鼠標指向主表的主鍵并拖動到從表。對于本例：將XSB表中的學號字段拖動到從表CJB，出現如圖6.25所示的關系設置界面。第7步退出圖6.26的關系圖界面，并根據提示，將關系圖的有關信息存盤，即創(chuàng)建了主表與從表之間的參照關系。山東工商學院陳章良6.3.4參照完整性的實現第1步進入XSB表設計器右擊，出現一快捷菜單如圖6.27所示課程類別。第2步選擇菜單項“關系”，出現如圖6.28所示的屬性界面。第3步在圖6.28的屬性界面的關系下拉表中選擇要刪除的關系，然后點擊“