MySQL 中的索引與限制

索引：用來提高資料的檢索速度，

限制：用來保證資料的完整性。

一、索引

首先建立需要的資料表：

CREATE TABLE T_Person (FNumber VARCHAR(20),

FName VARCHAR(20),FAge INT)

索引是針對字段的，是以建立索引索引的時候需要指定要在那個字段上建立索引，還可以為多個字段建立一個索引，這樣還可以指定索引相關的字段清單。

建立索引的 SQL 語句是 CREATE INDEX，其文法如下：

CREATE INDEX 索引名 ON 表名 (字段1, 字段2,……字段n)

其中：

【索引名】為被建立的索引的名稱，這個名稱必須是唯一的；

【表名】為要建立索引的表；

【字段1, 字段2,……字段n】為組成這個索引的字段清單，允許一到多個。

在 T_Person 表的 FName 字段上建立索引，索引名為 idx_person_name：

CREATE INDEX idx_person_name ON T_Person(FName)

在 T_Person 表的 FName 和 FAge 字段上建立索引，索引名為 idx_person_nameage：

CREATE INDEX idx_person_nameage ON T_Person(FName,FAge)

索引建立後是可以被删除的，删除索引使用的語句為 DROP INDEX

1、MYSQL 中的 DROP INDEX 文法如下：

DROP INDEX 索引名 ON 表名

删除剛才建立的兩個索引：

DROP INDEX idx_person_name ON T_Person;

DROP INDEX idx_person_nameage ON T_Person;

2、MSSQLServer 中的 DROP INDEX 文法如下：

DROP INDEX 表名.索引名

來删除剛才建立的兩個索引：

DROP INDEX T_Person.idx_person_name;

DROP INDEX T_Person.idx_person_nameage;

二、限制

資料庫系統中主要的幾種限制：非空限制；唯一限制；CHECK 限制；主鍵限制；外鍵限制。

1、非空限制

在定義資料表的時候，預設情況下所有字段都是允許為空值的，如果需要禁止字段為空，那麼就需要在建立表的時候顯示指定。

指定一個字段為空的方式就是在字段定義後增加 NOT NULL。

非空限制不僅對通過INSERT 語句插入的資料起作用，而且對于使用UPDATE 語句進行更新時也起作用。

例：

建立表 T_Person，并且設定 FNumber 字段不允許為空：

CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL ,FName

VARCHAR(20),FAge INT)

2、唯一限制（UNIQUE 限制）

用于防止一個特定的列中兩個記錄具有一緻的值，

比如在員工資訊表中希望防止兩個或者多個人具有相同的身份證号碼。

唯一限制分為 單字段唯一限制 與 複合唯一限制 兩種類型。

單字段唯一限制：

如果希望一個字段在表中的值是唯一的，那麼就可以将唯一限制設定到這個字段上，設定方式就是在字段定義後增加 UNIQUE。

例：

建立表 T_Person，并且将唯一限制設定到 FNumber 字段上：

CREATE TABLE T_Person (FNumber VARCHAR(20) UNIQUE,

FName VARCHAR(20),FAge INT)

複合唯一限制：

定義複合唯一限制需要定義在所有字段清單之後，文法如下：

CONSTRAINT 限制名 UNIQUE (字段1,字段2……字段n)

“字段1,字段2……字段n” 為組成限制的多個字段，如果隻有一個字段則可以看做是單字段唯一限制定義的另外一種形式。

通過這種形式定義的唯一限制由于有一個确定的名稱，是以可以很容易的通過這個名字來删除這個限制。

例：

建立表 T_Person，并在部門編号字段 FDepartmentNumber 和工号字段 FNumber 上設定複合唯一限制，并命名為 unic_dep_num：

CREATE TABLE T_Person (FNumber VARCHAR(20),

FDepartmentNumber VARCHAR(20),

FName VARCHAR(20),FAge INT,

CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

可以在一個表中添加多個複合唯一限制，隻要為它們指定不同的名稱即可。

例：

建立表 T_Person，并且為字段 FNumber 和 FDepartmentNumber 建立一個複合唯一限制

以及為 FDepartmentNumber 和 FName 建立一個複合唯一限制：

CREATE TABLE T_Person (FNumber VARCHAR(20),

FDepartmentNumber VARCHAR(20),

FName VARCHAR(20),FAge INT,

CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber) ,

CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))

在已經建立好的資料表上添加新的唯一限制，需要使用 ALTER TABLE 語句，

文法如下：

ALTER TABLE 表名 ADD CONSTRAINT 唯一限制名 UNIQUE (字段1,字段2……字段n)

例：

為 T_Person 表添加一個建立在字段 FName 和字段 FAge 上的新的唯一限制：

ALTER TABLE T_Person ADD CONSTRAINT unic_3 UNIQUE(FName, FAge)

ALTER TABLE 語句也可以删除已經建立好的複合唯一限制，

文法如下：

ALTER TABLE 表名 DROP CONSTRAINT 唯一限制名

不過上邊的文法不能在 MYSQL 中執行，MYSQL 中删除限制的文法為：

ALTER TABLE 表名 DROP INDEX 唯一限制名

例如:

将剛才建立的三個複合唯一限制删除：

MSQLServer、Oracle、DB2：

ALTER TABLE T_Person DROP CONSTRAINT unic_1;

ALTER TABLE T_Person DROP CONSTRAINT unic_2;

ALTER TABLE T_Person DROP CONSTRAINT unic_3;

MYSQL：

ALTER TABLE T_Person DROP INDEX unic_1;

ALTER TABLE T_Person DROP INDEX unic_2;

ALTER TABLE T_Person DROP INDEX unic_3;

三、CHECK 限制

CHECK 限制會檢查輸入到記錄中的值是否滿足一個條件，如果不滿足這個條件則對資料庫做的修改不會成功。

可以在 CHECK 條件中使用任意有效的 SQL 表達式，CHECK 限制對于插入、更新等任何對資料進行變化的操作都進行檢查。

在字段定義後添加 CHECK 表達式就可以為這個字段添加 CHECK限制，幾乎所有字段中都可以添加 CHECK 限制，也就是一張表中可以存在多個CHECK 限制。

例：

建立一張用于儲存人員資訊的表 T_Person：

CREATE TABLE T_Person (

FNumber VARCHAR(20),FName VARCHAR(20),

FAge INT CHECK(FAge >0),

FWorkYear INT CHECK(FWorkYear>0))

除了可以在 CHECK 限制中使用常量表達式之外，還可以在 CHECK 限制中使用函數，

例：

人員編号長度要大于 12：

MYSQL,DB2:

CREATE TABLE T_Person (

FNumber VARCHAR(20) CHECK (LENGTH(FNumber)>12),

FName VARCHAR(20),

FAge INT CHECK(FAge >0),

FWorkYear INT CHECK(FWorkYear>0))

MSSQLServer:

CREATE TABLE T_Person (

FNumber VARCHAR(20) CHECK (LEN(FNumber)>12),

FName VARCHAR(20),

FAge INT CHECK(FAge >0),

FWorkYear INT CHECK(FWorkYear>0))

這種直接在列定義中通過 CHECK 子句添加 CHECK 限制的方式的缺點是限制條件不能引用其他列。

如果希望 CHECK 子句中的條件語句中使用其他列，則必須在 CREATE TABLE 語句的末尾使用 CONSTRAINT 關鍵字定義它。

文法為：

CONSTRAINT 限制名 CHECK (限制條件)

例：

MYSQL,DB2:

CREATE TABLE T_Person (

FNumber VARCHAR(20),

FName VARCHAR(20),

FAge INT,

FWorkYear INT,

CONSTRAINT ck_1 CHECK(FWorkYear< FAge)) MSSQLServer: CREATE TABLE T_Person ( FNumber VARCHAR(20), FName VARCHAR(20), FAge INT, FWorkYear INT , CONSTRAINT ck_1 CHECK(FWorkYear< FAge)) 這種定義 CHECK 限制的方式幾乎與定義一個複合唯一限制的方式一緻。 同樣，可以通過 ALTER TABLE 的方式為已經存在的資料表添加 CHECK 限制。 例：在 T_Person上添加新的限制： ALTER TABLE T_Person ADD CONSTRAINT ck_2 CHECK(FAge>14)

上面的 SQL 語句中為限制指定了顯式的名稱，是以可以通過下面的 SQL 語句将 CHECK 限制 ck_2 删除（這個語句在 MYSQL 中無效）：

ALTER TABLE T_Person

DROP CONSTRAINT ck_2;

四、主鍵限制

第一範式要求每張表都要有主鍵，是以主鍵限制是非常重要的，而且主鍵限制是外鍵關聯的基礎條件。主鍵限制為表之間的關聯提供了連結點。

主鍵必須能夠唯一辨別一條記錄，也就是主鍵字段中的值必須是唯一的，而且不能包含 NULL 值。從這種意義上來說，主鍵限制是UNIQUE 限制和非空限制的組合。

雖然一張表中可以有多個 UNIQUE 限制和非空限制，但是每個表中卻隻能有一個主鍵限制。

在 CREATE TABLE 語句中定義主鍵限制非常簡單，隻要在字段定義後添加 PRIMARY KEY 關鍵字即可。

例：

建立員工資訊表，并且将字段 FNumber 設定為主鍵字段：

CREATE TABLE T_Person (FNumber VARCHAR(20) PRIMARY KEY,

FName VARCHAR(20),FAge INT)

除了這種由單一字段組成的主鍵之外，還可以由多個字段來組成主鍵，這樣的主鍵被稱為複合主鍵或者聯合主鍵。

複合主鍵的定義和複合唯一限制的定義類似

例：

建立員工資訊表，并且将字段 FNumber 和 FName 設定為複合主鍵：

CREATE TABLE T_Person (FNumber VARCHAR(20),

FName VARCHAR(20),FAge INT,

CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName))

也可以在以後添加主鍵，其添加方式與添加 UNIQUE 限制類似，也就是使用 ALTER TABLE 語句。

不過通過這種方式添加主鍵的時候有一個附加條件，那就是組成主鍵的字段必須包含 NOT NULL 限制。

如果在沒有添加非空限制的字段上建立主鍵，系統将會爆出錯誤資訊。

首先建立一個沒有主鍵的 T_Person 表，其中的字段 FNumber 和 FName 添加了非空限制：

CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL,

FName VARCHAR(20) NOT NULL,FAge INT)

可以執行下面的 SQL 語句為 T_Person 建立主鍵限制：

ALTER TABLE T_Person

ADD CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName)

删除主鍵限制的方式與删除 UNIQUE 限制以及 CHECK 限制的方式相同，隻要使用帶有 DROP 子句的 ALTER TABLE 語句即可：

ALTER TABLE T_Person

DROP CONSTRAINT pk_1;

這個語句在 MYSQL 中無效，在 MYSQL 中要執行下面的 SQL 語句才能删除主鍵：

ALTER TABLE T_Person

DROP PRIMARY KEY;

五、外鍵限制

當一些資訊在表中重複出現的時候，我們就要考慮要将它們提取到另外一張表中了，然後在源表中引用新建立的中的資料。

比如很多作者都著有不止一本著作，是以在儲存書籍資訊的時候，應該把作者資訊放到單獨的表中。

外鍵限制指定一個表中的一個列的值是另外一個表的外鍵，即一個表中的一個列是引用另外一個表中的記錄。

例如，可以設定 T_Book 表中的 FAuthorId 字段是一個依賴于T_AUTHOR 表的 FId 列中存在的主鍵值。

可以在建立表的時候就添加外鍵限制，其定義方式和複合主鍵類似，文法如下：

FOREIGN KEY 外鍵字段 REFERENCES 外鍵表名(外鍵表的主鍵字段)

添加了外鍵限制的 T_AUTHOR 表和 T_Book 表的建立語句：

CREATE TABLE T_AUTHOR

(

FId VARCHAR(20) PRIMARY KEY,

FName VARCHAR(100),

FAge INT,

FEmail VARCHAR(20)

);

CREATE TABLE T_Book

(

FId VARCHAR(20) PRIMARY KEY,

FName VARCHAR(100),

FPageCount INT,

FAuthorId VARCHAR(20) ,

FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)

);

在删除一個表中的資料的時候，如果有其他的表中存在指向這些資料的外鍵關系，那麼這個删除操作會是失敗的，除非将所有的相關的資料。

可以首先執行下面的SQL 語句将作者long 的所有著作删除：

DELETE FROM T_Book

WHERE FAuthorId =5;

然後就可以執行下面的SQL語句将作者long從T_AUTHOR表中删除了：

DELETE FROM T_AUTHOR

WHERE FName=’badboy’

如果在建立表的時候沒有添加外鍵限制，也可以使用 ALTER TABLE 語句添加外鍵限制，其文法與添加 UNIQUE 限制類似。

以 ALTER TABLE 語句的方式添加外鍵限制：

ALTER TABLE T_Book

ADD CONSTRAINT fk_book_author

FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)

删除 T_AUTHOR 表和 T_Book 表：

DROP TABLE T_Book;

DROP TABLE T_AUTHOR;

注：删除順序是首先删除 T_Book，再删除 T_AUTHOR，否則會因為違反外鍵限制執行失敗。