MySQL 中的索引与约束

悠悠 2022-05-29 10:11 254阅读 0赞

索引：用来提高数据的检索速度，  
约束：用来保证数据的完整性。

一、索引

首先创建需要的数据表：  
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，否则会因为违反外键约束执行失败。