第6章 索引與數(shù)據(jù)完整性

1、第6章 索引與數(shù)據(jù)完整性1234熟悉索引的創(chuàng)建熟悉索引的創(chuàng)建理解數(shù)據(jù)完整性概念理解數(shù)據(jù)完整性概念理解各種約束的含義理解各種約束的含義學學習習目目標標理解索引的概念理解索引的概念5熟練掌握約束的創(chuàng)建方法熟練掌握約束的創(chuàng)建方法主要內容主要內容6.1 6.1 索引索引6.2 6.2 約束約束6.3 6.3 數(shù)據(jù)完整性數(shù)據(jù)完整性6.1 6.1 索引索引 索引是根據(jù)表中一列或若干列按照一定順序建立的列值與記錄行之間的對應關系表。索引依賴于數(shù)據(jù)庫的表，作為表的一個組成部分，一旦創(chuàng)建成功后，由數(shù)據(jù)庫自身進行維護。一個表的存儲是由兩部分組成，一部分用來存放表中的數(shù)據(jù)頁面，另一部分存放索引頁面。索引就存放在索引

2、頁面上。通常，索引頁相對于數(shù)據(jù)頁小得多。當進行數(shù)據(jù)檢索時，系統(tǒng)先搜索索引頁面，從中找到所需數(shù)據(jù)的指針后，再通過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)。在數(shù)據(jù)庫系統(tǒng)中建立索引主要有以下作用：l 快速存取數(shù)據(jù)l 保證數(shù)據(jù)記錄的唯一性l 實現(xiàn)表與表之間的參照完整性l 在使用ORDER BY、 GROUP BY 子句進行數(shù)據(jù)檢索時，利用索引可以減少排序和分組的時間。 6.1.16.1.1 索引的分類索引的分類 SQL Server 2008支持在表中任何列（包括計算列）上定義索引，按索引的組織方式可將SQL Server 2008索引分為聚集索引和非聚集索引兩種類型。1. 非聚集索引非聚集索引具有與表的結構完全分離

3、的結構。使用非聚集索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按照列進行排序，而是存儲索引行，每個索引行均包括非聚集索引鍵值和一個或多個指向包含該值的數(shù)據(jù)行的定位器。SQL Server在搜索數(shù)據(jù)時，先對非聚集索引進行搜索，然后再通過相應的行定位器從表中找到對應的數(shù)據(jù)。 6.1.16.1.1 索引的分類索引的分類2. 聚集索引 在聚集索引中，表中各行的物理順序與鍵值的邏輯（索引）順序相同。由于非聚集索引使用索引頁進行存儲，因此它比聚集索引需要更多的存儲空間，且檢索效率低。聚集索引比非聚集索引有更快的數(shù)據(jù)訪問速度。在SQL Server 2008中，一個表只能創(chuàng)建一個聚集索引，但可以有多個非聚集索引。 在數(shù)據(jù)庫

4、中為表定義主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特殊類型。主鍵索引要求主鍵中的每個值均是唯一的。當在查詢中使用主鍵索引時，還允許快速訪問數(shù)據(jù)。6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建 在數(shù)據(jù)庫中，為了提高查詢速度和更新速度，可以考慮對表創(chuàng)建索引。在SQL Server中既可利用圖形界面方式創(chuàng)建索引，也可利用T-SQL命令建立索引。1.使用圖形界面方式創(chuàng)建索引 啟動【Microsoft SQL Server Management Studio】，在【對象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】【學生成績管理系統(tǒng)】【學生】【索引】節(jié)點,右鍵單擊【索引】節(jié)點，在彈出的快捷菜單中選擇【新建索引】

5、菜單項，打開如圖6.1所示的【新建索引】窗口。6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建 圖6.1新建索引窗口 可以在【常規(guī)】選項卡選擇索引列，指定索引類型，在【選項】選項卡中設置填充引子（指定每個索引頁的填滿程度，一般很少指定）；單擊【添加】按鈕，在彈出的對話框中選擇需要創(chuàng)建索引的列，設置好索引的屬性后，單擊【確定】按鈕即可。6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建 2. 使用T-SQL語句創(chuàng)建索引 使用CREATE INDEX語句可以創(chuàng)建索引，創(chuàng)建索引的簡潔語法格式如下：CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON

6、table_ name ( column_name ASC | DESC ,.n )WITH FILLFACTOR = x選項說明如下：l UNIQUE 表示唯一索引，可選。l CLUSTERED | NONCLUSTERED 表示聚集索引或非聚集索引，可選。l ASC | DESC 指定索引列的排序方式，ASC表示升序，DESC表示降序。默認為升序。l FILLFACTOR表示填充因子，是一個0-100之間的值，該值表示索引頁填滿的空間所占的百分比。6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建 【例6.1】對課程表的課程名稱字段創(chuàng)建非聚集索引kcbh。USE 學生成績管理系統(tǒng)GOIF EXIS

7、TS(SELECT name FROM SYSINDEXES WHERE name=kcbh)DROP INDEX kcbh -查詢是否存在名稱為kcbh的索引，如果存在，則刪除。CREATE NONCLUSTERED INDEX kcbhON 課程(課程名稱)WITH FILLFACTOR=30 -對課程表的課程名稱創(chuàng)建非聚集索引，填充因子為30% 。6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建3. 使用圖形界面方式修改索引 啟動【Microsoft SQL Server Management Studio】，在【對象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】【學生成績管理系統(tǒng)】【學生】【索引】節(jié)

8、點,如圖6.3所示。 圖 6.3 【對象資源管理器】窗格之【索引】節(jié)點6.1.26.1.2 索引的創(chuàng)建索引的創(chuàng)建 右鍵單擊【xh】或雙擊該索引，在彈出的快捷菜單中選擇【屬性】選項，打開【索引屬性】對話框，如圖6.4所示，索引屬性-xh窗口與新建索引的窗口類似，修改相應的參數(shù)，單擊【確定】按鈕即可。圖 6.4 索引屬性窗口6.1.3 6.1.3 索引的刪除索引的刪除1. 使用圖形界面方式刪除索引 啟動【Microsoft SQL Server Management Studio】，在【對象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】【學生成績管理系統(tǒng)】【學生】【索引】節(jié)點, 右鍵單擊【xh】索引，在彈

9、出的快捷菜單中選擇【刪除】菜單項，彈出【刪除對象】窗口，選中要刪除的索引，單擊【確定】按鈕即可。6.1.3 6.1.3 索引的刪除索引的刪除2. 使用T-SQL語句刪除索引 DROP INDEX index_name DROP INDEX 語句可以一次刪除一個或多個索引。這個語句不適合刪除通過語句定義的主鍵索引（PRIMARY KEY）和唯一索引（UNIQUE），若要刪除主鍵索引和唯一索引，須通過刪除約束來實現(xiàn)。6.26.2 約束約束 約束（Constraints），顧名思義就是按照約定來束縛某種東西。在SQL Server數(shù)據(jù)庫中，無非就是對存儲的數(shù)據(jù)進行束縛，需要按照某種既定的規(guī)則來實施，

10、一般是指語義的限制以及用來制約屬性上的關系。可能在平時的工作中，尤其是對開發(fā)人員來說，約束用的不是很多，更多的時候是用代碼去做一些輸入的驗證，這其實也是約束的思想，即把不滿足條件的數(shù)據(jù)排除在外。6.2.16.2.1 約束的類型約束的類型1.PRIMARY KEY（主鍵）約束 主鍵約束用來強制數(shù)據(jù)的實體完整性，它是在表中定義一個主鍵來唯一標識表中的每行記錄。主鍵約束的特點：每個表中只能有一個主鍵，主鍵可以是一列，也可以是多列的組合；主鍵值必須唯一并且不能為空，對于多列組合的主鍵，某列值可以重復，但列的組合值必須唯一。 2.UNIQUE（唯一）約束 唯一約束用來強制數(shù)據(jù)的實體完整性，它主要用來限制

11、表的非主鍵列中不允許輸入重復值。唯一約束的特點：一個表中可以定義多個唯一約束；每個唯一約束可以定義到一列上，也可以定義到多列上；空值在定義唯一約束的字段中只能出現(xiàn)一次。 6.2.16.2.1 約束的類型約束的類型3. NOT NULL（非空）約束 非空約束用來強制數(shù)據(jù)的域完整性，它用于設定某列值不能為空。如果指定某列不能為空，則在插入記錄時此列必須有數(shù)據(jù)。 4.CHECK（檢查）約束 檢查約束用來強制數(shù)據(jù)的域完整性，它使用邏輯表達式來限制表中的列可以接受哪些數(shù)據(jù)值。 5.DEFAULT（默認）約束 默認約束用來強制數(shù)據(jù)的域完整性，它為表中某列建立一個默認值，當用戶插入記錄時，如果沒有為該列提供

12、輸入值，則系統(tǒng)會自動將默認值賦給該列。默認值可以是常量、內置函數(shù)或表達式。使用默認約束可以提高插入記錄的速度。 6.2.16.2.1 約束的類型約束的類型6.FOREIGN KEY（外?。┘s束 外鍵是指一個表中的一列或列組合，它雖不是該表的主鍵，但卻是另一個表的主鍵。通過外鍵約束可以為相關聯(lián)的兩個表建立聯(lián)系，實現(xiàn)數(shù)據(jù)的引用完整性、維護兩表之間數(shù)據(jù)的一致性關系。 當約束被定義于某個表的一列時稱為列約束，定義于某個表的多列時稱為表約束。當一個約束中必須包含一個以上的列時，必須使用表約束。6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束1）使用圖形界面方式創(chuàng)建主鍵約束 依次展開【數(shù)據(jù)庫】【學生成績管理系統(tǒng)】【

13、學生】, 右鍵，選擇【設計】菜單項，在彈出的【表設計器】窗口中，選中要設置主鍵的字段，如 “學號”字段，右鍵單擊，在彈出的快捷菜單中選擇【設置主鍵】菜單項，如圖6.5 所示。執(zhí)行命令后，在作為主鍵的字段前有一個鑰匙樣圖標。也可以在選擇好字段后，單擊工具欄中的 設置主鍵工具按鈕來設置主鍵。1. 創(chuàng)建主鍵約束(PRIMARY KEY)6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束圖6.5 設置主鍵窗口6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束2）運用T-SQL語句創(chuàng)建主鍵約束 運用T-SQL語句創(chuàng)建主鍵約束有兩種形式，第一種是使用CREATE TABLE語句在創(chuàng)建表的同時創(chuàng)建主鍵約束，語法格式如下：CREAT

14、E TABLE table_name ( cloumn_name datatype CONSTRAINT constraint_name PRIMARY KEY -創(chuàng)建列約束 ,n ,CONSTRAINT constraint_name PRIMARY KEY(column_naem,n)-創(chuàng)建表約束6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束【例6.3】創(chuàng)建成績表（學號，課程號，成績）將學號和課程號的組合字段設為主鍵。CREATE TABLE 成績 (學號 CHAR(10), 課程號 CHAR（6）, 成績 INT , CONSTRAINT p_y PRIMARY KEY (學號，課程號)6.2.

15、26.2.2 創(chuàng)建約束創(chuàng)建約束第二種是使用ALTER TABLE語句創(chuàng)建主鍵約束，其語法格式如下：ALTER TABLE table_nameADD CONSTRAINT constraint_namePRIMARY KEY CLUSTERED|NONCLUSTERED (column,n)其中：l constraint_name：指主鍵約束名稱。l CLUSTERED：表示在該列上建立聚集索引。l NONCLUSTERED：表示在該列上建立非聚集索引。6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束【例6.5】假設已創(chuàng)建學生表，但還未創(chuàng)建主鍵約束。將學生表的學號字段設為主鍵。ALTER TABLE 學

16、生 ADD CONSTRAINT pk_xh PRIMARY KEY（學號）6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束為保證字段值的唯一性，應該創(chuàng)建唯一約束。唯一約束（UNIQUE）主要是用來確保不受主鍵約束的列中的數(shù)據(jù)的唯一性。主鍵約束與UNIQUE約束的區(qū)別主要為：l 唯一約束（UNIQUE）主要用在非主鍵的一列或多列上要求數(shù)據(jù)唯一的情況。l 唯一約束（UNIQUE）允許該列上存在NULL值，而主鍵決不允許出現(xiàn)這種情況。l 可以在一個表上設置多個唯一約束（UNIQUE），而在一個表中只能設置一個主鍵約束。2.創(chuàng)建唯一約束（UNIQE）6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束 對課程表的課程名字段

17、創(chuàng)建唯一約束，確保該列取值的唯一性，其具體操作步驟為： 右擊課程表，選擇【設計】，打開【表設計器】，選擇課程名稱列并右擊鼠標，在彈出的快捷菜單中選擇【索引 /鍵】菜單項，打開【索引 /鍵】窗口。在窗口中單擊【添加】按鈕，在常規(guī)屬性區(qū)域中的【列】欄選擇要創(chuàng)建唯一約束的列。各項屬性選擇完成后，單擊【關閉】按鈕，即可創(chuàng)建唯一約束。1）使用圖形界面方式創(chuàng)建唯一約束6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束為新表中的字段創(chuàng)建唯一約束，其語法格式如下：CREATE TABLE table_name( cloumn_name datatype CONSTRAINT unique _name UNIQUE -創(chuàng)建

18、列級約束 ,n ,CONSTRAINT unique _name UNIQUE(column_naem,n)-創(chuàng)建表級約束)2）使用T-SQL語句創(chuàng)建唯一約束6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束【例6.6】創(chuàng)建一個表級的唯一約束。CREATE TABLE test( 編號 INT CONSTRAINT pk_bh PRIMARY KEY , 名稱 CHAR(20), 類型 CHAR(20), 時間 DATETIME, CONSTRAINT uniq_event UNIQUE(類型,時間) -表級約束)GO6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束為已存在的表創(chuàng)建唯一約束，其語法格式如下：ALTE

19、R TABLE table_nameADD CONSTRAINT unique_nameUNIQUE CLUSTERED|NONCLUSTERED (column,n) 【例6.7】為課程表中的課程名稱字段創(chuàng)建一個唯一約束。ALTER TABLE 課程ADD CONSTRAINT uk_kcmc UNIQUE NONCLUSTERED (課程名稱 )GO 6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束 核查約束（CHECK）通過檢查輸入表列的數(shù)據(jù)的值來維護值域的完整性。核查約束通過對一個邏輯表達式的結果進行判斷來對數(shù)據(jù)進行核查。 可以在一列上設置多個核查約束，也可以將一個核查約束應用于多列。當一列受多

20、個核查約束控制時，所有的約束按照創(chuàng)建的順序，依次進行數(shù)據(jù)有效性的核查。 一般來說，可以在下面兩種情況下，設置核查約束無效：l 在執(zhí)行INSERT語句或UPDATE語句過程：事先知道對數(shù)據(jù)的增加或修改將違反核查約束的規(guī)定，但這些操作又是必須的。l 在復制進行時，在進行不同服務器間的復制操作的過程中，由于兩個服務器之間設置的核查約束不一致，如果不事先使核查約束無效，則有可能使某些數(shù)據(jù)無法進行復制。 3.創(chuàng)建檢查約束（CHECK）6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束 為學生表的出生日期字段創(chuàng)建一個名稱為ck_csrq的檢查約束，以保證輸入的數(shù)據(jù)大于1983年1月1日，小于1999年12月31日。其具體操作步驟為： 右擊學生表，選擇【設計】，打開【表設計器】，在【表設計器】，窗口，右鍵單擊任意字段，在彈出的快捷菜單中選擇【CHECK 約束】菜單項，打開【CHECK約束】窗口。在窗口中單擊【添加】按鈕，系統(tǒng)會給出默認的CHECK 約束名“CK_學生*”顯示在【選定的CHECK約束】列表中，如圖6.7所示。1）使用圖形界面方式創(chuàng)建檢查約束（CHECK）6.2.26.2.2 創(chuàng)建約束創(chuàng)建約束圖6.7 CHECK 約束 單擊屬性窗口中【常規(guī)】屬性【表達式】，在其對應的文本框中輸入表達式（根據(jù)題意輸入“出生日期=1983