MySQL数据库技术（第3版）课件 单元7 索引与完整性约束

单元7索引与完整性约束项目知识要点与目标项目知识要点知识能力目标学时任务1索引及其应用1索引的分类2创建索引3删除索引4索引对查询的影响任务2完整性约束1主键约束2外键约束3CHECK约束任务3分区1分区类型2创建分区2管理分区【典型工作任务】1创建索引2创建外键3建立分区【技能要求】1掌握建立各种索引的方法2掌握建立各种完整性约束的方法3掌握建立和管理分区的方法【知识要求】1索引的作用与使用方法2完整性约束的作用与使用方法3分区的作用与使用方法2项目实训“员工管理系统”索引与完整性约束2索引及其应用

索引——是一种提高查找速度的机制索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。索引文件学生表student中建立“学号”索引（升序）示意图学号记录号200601011200601023200602032…………2007020110000…………20809995000学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17学生表student索引文件索引文件如何影响原表没有索引文件时：指针在原表中顺序移动如果要找位于第10000条的学号”20070201”的记录,

计算机要在表中查找10000次学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17使用索引查找有索引文件时：(二分法查找实例)计算机先在索引文件中学号为”20070201”的记录,找到相应的记录号,再到学生表中直接读取相关记录.学号记录号200601011200601023200602032…………2007020110000…………20809995000学号姓名性别年龄20060101张建男1820060202王欢女1720060102田静男18………..2080999李华女19…….…..20070201张丽女17学生表student索引文件索引文件如何加快查找速度原因：（1）索引后，指针在索引文件中顺序移动。（2）索引文件中记录是有序的。（3）有序后，可以用各种方法加快查询速度，如折半（二分）查找法，而排序前，只能顺序查找记录。B树索引示意图<50>=50……rowIDrowIDrowIDrowID……<40..50<30..40<20..30<10..20…………<80..90<70..80<60..70<50..60rowIDrowIDrowIDrowIDrowID……41rowID42rowID43rowID44rowID……索引的分类1.普通索引（INDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。2.唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。3.主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。4.全文索引（FULLTEXT）MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。

创建索引1.使用CREATEINDEX语句使用CREATEINDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。语法格式：CREATE[UNIQUE|FULLTEXT]INDEX

索引名ON

表名(列名[(长度)][ASC|DESC],...)说明：●索引名：索引的名称，索引名在一个表中名称必须是唯一的。●列名：表示创建索引的列名。

长度：表示使用列的前多少个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。BLOB或TEXT列必须用前缀索引。●UNIQUE:UNIQUE表示创建的是唯一性索引●FULLTEXT:FULLTEXT表示创建全文索引；●CREATEINDEX语句并不能创建主键。创建索引举例【例】根据Book表的书名列上的前6个字符建立一个升序索引name­_book。

CREATEINDEXname­_bookONBook(书名(6)ASC);可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。【例】在Sell表的用户号列和图书编号列上建立一个复合索引sfz_bh­_sell。CREATEINDEXuser_bh­_sellONSell(用户号,图书编号);ALTERTABLE语句创建索引使用ALTERTABLE语句使用ALTERTABLE语句修改表，其中也包括向表中添加索引。语法格式如下：ALTERTABLE表名ADDINDEX[索引名](列名,...)/*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...)/*添加主键*/|ADDUNIQUE[索引名](列名,...)/*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...)/*添加全文索引*/ALTERTABLE创建索引举例【例】在Book表的书名列上创建一个普通索引。ALTERTABLEBook ADDINDEXsm_book(书名);【例】假设Book表中主键未设定，为Book表创建以图书编号为主键索引，出版社和出版时间为复合索引，以加速表的检索速度。

ALTERTABLEBook ADDPRIMARYKEY(图书编号), ADDINDEXmark(出版社,出版时间);

这个例子中，既包括PRIMARYKEY，也包括复合索引，说明MySQL可以同时创建多个索引。记住，使用PRIMARYKEY的列，必须是一个具有NOTNULL属性的列。如果想要查看表中创建的索引的情况，可以使用SHOWINDEXFROMtbl_name语句，例如：SHOWINDEXFROMbook;创建表时创建索引在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建。在创建表的CREATETABLE语句中可以包含索引的定义。语法格式：CREATETABLE表名(列名,...|[索引项])其中，索引项语法格式如下：PRIMARYKEY(列名,...)/*主键*/|{INDEX|KEY}[索引名](列名,...)/*索引*/|UNIQUE[INDEX][索引名](列名,...)/*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...)/*全文索引*/说明：KEY通常是INDEX的同义词。在定义列选项的时候，也可以将某列定义为PRIMARYKEY，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，必须在语句最后加上一个PRIMARYKEY列名，…）子句。【例】创建sell_copy表的语句如下，sell_copy表带有身份证号和图书编号的联合主键，并在订购册数列上创建索引。

CREATETABLEsell_copy(

身份证号CHAR(18)NOTNULL,

图书编号CHAR(20)NOTNULL,

订购册数INT(5),

订购时间DATETIME, PRIMARYKEY(身份证号,图书编号), INDEXdgcs(订购册数));创建表时创建索引举例

删除索引1.使用DROPINDEX语句删除索引语法格式：

DROPINDEX索引名ON表名【例】删除Book表上的sm_book索引。

DROPINDEXsm_bookONBook;ALTERTABLE删除索引2.使用ALTERTABLE语句删除索引语法格式：ALTER[IGNORE]TABLE表名|DROPPRIMARYKEY /*删除主键*/|DROPINDEX索引名

/*删除索引*/【例】删除Book表上的主键和mark索引。

ALTERTABLEBook DROPPRIMARYKEY, DROPINDEXmark;

如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。索引对查询的影响目前本书实例中所涉及的表最多只有几十行的数据，所以有没有建立索引，还体会不到查询速度上的差异，可是当一个表里有成千上万行数据的时候，差异就非常明显了。现在假设有一个表，表里只有一列，由数值1～1000的1000行组成，现在要想查找到数字1000所在的行。如果没有索引，要从第一行开始匹配，若数值不是1000，则转到下一行进行匹配，这样直到第1000行的时候才能找到数字1000所在行，也就是说服务器进行了1000次的运算。而当在该列上创建一个索引后，则可以直接在索引值中找到1000的位置，然后找到1000所指向的行，在速度上比全表扫描至少快了100倍。当执行涉及多个表的连接查询时，索引将更有价值。索引的弊端首先，索引是以文件的形式存储的，索引文件要占用磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，在更新表中索引列上的数据时，对索引也需要更新，这可能需要重新组织一个索引，如果表中的索引很多，这是很浪费时间的。也就是说，这样就降低了添加、删除、修改和其他写入操作的效率。表中的索引越多，则更新表的时间就越长。但是这些弊端并不妨碍索引的应用，因为索引带来的好处已经基本掩盖了它的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的。数据完整性输入的类型是否正确？——年龄必须是数字输入的格式是否正确？——Email必须包含@符号是否在允许的范围内？——性别只能是”男”或者”女”是否存在重复输入？——员工信息输入了两次是否符合其他特定要求？——信誉值大于5的客户才能够加入客户表……列值要求（约束）整行要求（约束）数据完整性约束

实体完整性(EntityIntegrity)域完整性(DomainIntegrity)参照完整性(ReferentialIntegrity)用户定义的完整性(User-definedIntegrity)实体完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012….地址姓名学号江西南昌雷铜0010014×约束方法：唯一约束、主键约束域完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012….地址姓名学号湖北江门李亮8700000000×约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束引用完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012…地址姓名学号980010021数学×约束方法：外键约束科目学号分数…数学001001288数学001001374语文001001267语文001001381数学001001698自定义完整性AV121322乔峰CV0016AV372133玄痛CV0015AV378291沙悟净AV0014AV378290猪悟能AV0013AV378289孙悟空AV0012….会员证用户姓名用户编号约束方法：规则、存储过程、触发器帐号姓名信用….00192孙悟空700288猪悟能612333段誉890111虚竹4093000岳不群-10×触发器：检查信用值约束与完整性之间的关系完整性类型约束类型描述约束对象列完整性Default当使用INSERT语句插入数据时，若已定义默认值的列没有提供指定值，则将该默认值插入记录中列Check指定某一列可接受的值实体完整性PrimaryKey每行记录的惟一标识符，确保用户不能输入重复值，并自动创建索引，提高性能，该列不允许使用空值行Unique在列集内强制执行值的惟一性，防止出现重复值，表中不允许有两行的同一列包含相同的非空值参考完整性ForeignKey定义一列或几列，其值于本表或其他表的主键或UNIQUE列相匹配表与表之间主键约束

主键就是表中的一列或多个列的一组，其值能唯一地标志表中的每一行。通过定义PRIMARYKEY约束来创建主键，而且PRIMARYKEY约束中的列不能取空值。由于PRIMARYKEY约束能确保数据的唯一，所以经常用来定义标志列。当为表定义PRIMARYKEY约束时，MySQL为主键列创建唯一性索引，实现数据的唯一性，在查询中使用主键时，该索引可用来对数据进行快速访问。如果PRIMARYKEY约束是由多列组合定义的，则某一列的值可以重复，但PRIMARYKEY约束定义中所有列的组合值必须唯一。可以用两种方式定义主键：作为列或表的完整性约束。作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARYKEY。作为表的完整性约束时，需要在语句最后加上一条PRIMARYKEY（col_name，…）语句。主键约束举例【例】创建表book_copy，将书名定义为主键。

CREATETABLEbook_copy(

图书编号varchar(6)NULL,

书名varchar(20)NOTNULLPRIMARYKEY,

出版日期date);

主键约束举例

当表中的主键为复合主键时，只能定义为表的完整性约束。【例】创建course表来记录每门课程的学生学号、姓名、课程号、学分和毕业日期。其中学号、课程号和毕业日期构成复合主键。

CREATETABLEcourse(

学号

varchar(6)NOTNULL,

姓名

varchar(8)NOTNULL,

毕业日期 date NOTNULL,

课程号

varchar(3),

学分

tinyint, PRIMARYKEY(学号,课程号,毕业日期));主键约束原则

原则上，任何列或者列的组合都可以充当一个主键。但是主键列必须遵守一些规则。这些规则源自于关系模型理论和MySQL所制定的规则：

（1）每个表只能定义一个主键。（2）关系模型理论要求必须为每个表定义一个主键。然而，MySQL并不要求这样，可以创建一个没有主键的表。但是，从安全角度应该为每个基础表指定一个主键。（3）表中的两个不同的行在主键上不能具有相同的值。这就是唯一性规则。（4）如果从一个复合主键中删除一列后，剩下的列构成主键仍然满足唯一性原则，那么，这个复合主键是不正确的，这条规则称为最小化规则（minimalityrule）。也就是说，复合主键不应该包含一个不必要的列。（5）一个列名在一个主键的列表中只能出现一次。MySQL自动地为主键创建一个索引。通常，这个索引名为PRIMARY。然而，可以重新给这个索引起名。主键约束命名【例】创建course表，把主键创建的索引命名为INDEX_course。

CREATETABLEcourse(

学号 varchar(6)NOTNULL,

姓名 varchar(8)NOTNULL,

毕业日期 datetime NOTNULL,

课程号 varchar(3),

学分

tinyint, PRIMARYKEYINDEX_course(学号,课程号,毕业日期));替代键约束在关系模型中，替代键像主键一样，是表的一列或一组列，它们的值在任何时候都是唯一的。替代键是没有被选做主键的候选键。定义替代键的关键字是UNIQUE。【例】在表book_copy1中将图书编号作为主键，书名列定义为一个替代键。

CREATETABLEbook_copy1(

图书编号varchar(20)NOTNULLprimarykey,

书名

varchar(20)NOTNULLUNIQUE,

出版日期dateNULLunique,);ALTERTABLE语句添加约束使用ALTERTABLE语句使用ALTERTABLE语句修改表，其中也包括向表中添加约束。语法格式如下：ALTERTABLE表名ADDPRIMARYKEY(列名,...)/*添加主键*/|ADDUNIQUE[索引名](列名,...)/*添加替代键约束*/|DROPPRIMARYKEY

/*删除主键*/

|DROPINDEX索引名

/*删除索引*/ALTERTABLE举例【例】假设Book表中主键未设定，为Book表建立以图书编号为主键约束,以书名为唯一性约束。

ALTERTABLEBook ADDPRIMARYKEY(图书编号),ADDUNIQUEu_idx

(书名);

这个例子中，既包括主键约束,也包括唯一性约束，说明MySQL可以同时创建多个约束。记住，使用PRIMARYKEY的列，必须是一个具有NOTNULL属性的列。如果想要查看表中创建的约束的情况，可以使用SHOWINDEXFROMtbl_name语句，例如：SHOWINDEXFROMbook;【例】删除Book表上的主键和替代键约束。

ALTERTABLEBook DROPPRIMARYKEY, DROPINDEXu_idx;替代键和主键的区别在MySQL中替代键和主键的区别主要有以下几点。（1）一个数据表只能创建一个主键。但一个表可以有若干个UNIQUE键，并且它们甚至可以重合，例如，在C1和C2列上定义了一个替代键，并且在C2和C3上定义了另一个替代键，这两个替代键在C2列上重合了，而MySQL允许这样。（2）主键字段的值不允许为NULL，而UNIQUE字段的值可取NULL，但是必须使用NULL或NOTNULL声明。（3）一般创建PRIMARYKEY约束时，系统会自动产生PRIMARYKEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。引用完整性河南新乡赵可以0010016河南新乡张丽鹃0010015江西南昌雷铜0010014湖南新田吴兰0010013山东定陶李山0010012…地址姓名学号980010021数学×约束方法：外键约束科目学号分数…数学001001288数学001001374语文001001267语文001001381数学001001698主表（父表）参照表（子表）参照完整性约束现实中，有很多规则是和表之间的关系有关的。例如，在本书所举例的Bookstore数据库中，只有图书目录表中有的图书才可以销售，因此，在Sell表中的所有图书（由图书编号来标识）必须是Book表有的图书，也就是说存储在Sell表中的所有图书编号必须存在于Book表的图书编号列中。同样Sell表中的所有会员号也必须出现在Members表的会员号列中。

这种类型的关系就是参照完整性约束（referentialintegrityconstraint）。参照完整性约束是一种特殊的完整性约束，实现为一个外键。所以Sell表中的图书编号列和身份证号列都可以定义为一个外键。可以在创建表或修改表时定义一个外键声明。外键目前只可以用在那些使用InnoDB存储引擎创建的表中，对于其他类型的表，MySQL服务器能够解析CREATETABLE语句中的FOREIGNKEY语法，但不能使用或保存它。更改表的存储引擎为InnoDB语法如下：Altertable表名ENGINE=InnoDB参照完整性约束举例1、创建表的同时创建外键语法格式：

CREATETABLE表名(列名,...）|[外键定义]

2、对已有表创建外键语法格式：ALTERTABLE表名ADD[外键定义][外键定义]语法格式：FOREIGNKEY(列名)REFERENCES表名[(列名[(长度)][ASC|DESC],...)][ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION}][ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION}]RESTRICT：当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新操作。如要当要删除xs表中081102记录时，因为cj中还有081102记录，拒绝对xs表的删除操作。CASCADE：从父表删除或更新行时自动删除或更新子表中匹配的行。如从xs表更新081102学号为801102时自动更新cj表中学号为081102行为801102。学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Xs表（父表）Cj表（子表）参照完整性约束说明SETNULL：当从父表删除或更新行时，设置子表中与之对应的外键列为NULL。当从xs表删除081103行时，设置cj表中081103项为NULL。NOACTION：NOACTION意味着不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和RESTRICT一样。SETDEFAULT：作用和SETNULL一样，只不过SETDEFAULT是指定子表中的外键列为默认值。如果没有指定动作，两个参照动作就会默认地使用RESTRICT。学号姓名…081102程明081103王燕…….….学号姓名成绩081102程明80081103王燕90….Xs表（父表）Cj表（子表）参照完整性约束说明参照完整性约束举例【例】创建book_ref表，所有的book_ref表中图书编号都必须出现在Book表中，假设已经使用图书编号列作为Book表主键。

CREATETABLEbook_ref(图书编号varchar(20)NULL,

书名varchar(20)NOTNULL,

出版日期dateNULL,PRIMARYKEY(书名),FOREIGNKEY(图书编号)REFERENCESBook(图书编号)ONDELETERESTRICTONUPDATERESTRICT);【例】创建带有参照动作CASCADE的book_ref1表。

CREATETABLEbook_ref1(

图书编号varchar(20)NULL,

书名

varchar(20)NOTNULL,

出版日期dateNULL,PRIMARYKEY(书名),FOREIGNKEY(图书编号)REFERENCESBook(图书编号)ONUPDATECASCADE);

这个参照动作的作用是在主表更新时，子表产生连锁更新动作，有些人称它为“级联”操作。就是说，如果Book表中有一个图书编号为“7-115-12683-6”的值修改为“7-115-12683-1”，则book_ref1表中的图书编号列上为“7-115-12683-6”的值也相应地改为“7-115-12683-1”。参照完整性约束举例【例】在网络图书销售系统中，只有会员才能下订单。因此Sell表中的所有用户号也必须出现在Members表的用户号列中。定义参照完整性约束来实现这种约束。

ALTERTABLEsellADDFOREIGNKEY(用户号)REFERENCESmembers(用户号)ONDELETECASCADEONUPDATECASCADE;参照完整性约束举例CHECK约束主键、替代键、外键都是常见的完整性约束的例子。但是，每个数据库都还有一些专用的完整性约束。例如，KC表中星期数要在1～7之间，XS表中出生日期必须大于1986年1月1日。这样的规则可以使用CHECK完整性约束来指定。CHECK完整性约束在创建表的时候定义。可以定义为列完整性约束，也可以定义为表完整性约束。语法格式为：CHECK(expr)说明：expr是一个表达式，指定需要检查的条件，在更新表数据的时候，MySQL会检查更新后的数据行是否满足CHECK的条件。CHECK约束举例【例】创建表student，只考虑学号和性别两列，性别只能包含男或女。

CREATETABLEstudent(

学号char(6)NOTNULL,

性别char(2)NOTNULLCHECK(性别IN('男','女')));

这里CHECK完整性约束指定了性别允许哪个值，由于CHECK包含在列自身的定义中，所以CHECK完整性约束被定义为列完整性约束。【例】创建表student1，只考虑学号,出生日期,总学分，出生日期必须大于1980年1月1日。

CREATETABLEstudent1(

学号char(6)NOTNULL,

出生日期dateNOTNULL,

学分intnull,check(出生日期>'1970-01-01'));CHECK约束举例【例】修改book表，单价必须大于等于0，折扣在0.1-1之间。

altertablebookaddcheck(单价>0),

addcheck(折扣>=0.1and折扣<=1);CHECK约束举例删除完整性约束查看表的相关信息：showcreatetable表名这个命令可以查看表的所有信息，包括一些字段类型，字段的约束，外键，主键，索引，字符编码等等如果使用一条DROPTABLE语句删除一个表，所有的完整性约束都自动被删除了。被参照表的所有外键也都被删除了，使用ALTERTABLE语句，完整性可以独立地被删除，而不用去删除表本身。删除的语法和删除索引的语法一样。【例】删除表book的主键。ALTERTABLEbookDROPPRIMARYKEY;altertableselldropFOREIGNKEYsell_ibfk_1;数据分区分区类型：MySQL支持的4种类型的分区的特点如下。

RANGE分区：基于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别是它基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择，该表达式使用将要插入表中的这些行的列值进行计算。这个表达式可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。RANGE分区RANGE分区是利用取值范围将数据分区，区间要连续并且不能互相重叠，使用VALUESLESSTHAN操作符进行分区定义。语法格式如下。PARTITIONBYRANGE(表达式)（PARTITION分区1VALUESLESSTHAN(值1)，

……PARITION分区nVALUESLESSTHAN(值n|[MAXVALUE])）【例】将Sell表中的数据按“订购时间”进行分区，2021年前的放p1分区，2021年的放p2分区，2021年以后的放p3分区。