第五章 索引与数据完整性约束.docx
《第五章 索引与数据完整性约束.docx》由会员分享，可在线阅读，更多相关《第五章 索引与数据完整性约束.docx（15页珍藏版）》请在冰豆网上搜索。
第五章索引与数据完整性约束
第5章索引与数据完整性约束
一、索引的用途与概念
1.MySQL中访问表中的行，最常用的是顺序访问和索引访问。
2.索引的概念：
根据表中一列或若干列按照一定顺序建立的列值与记录之间的对应关系表。
（存储在索引文件中）像不像图书的目录？
3.索引的用途：
在表的某些列上创建了索引之后查找数据时可以直接根据该列上的索引找到对应行的位置，从而加快了查找速度。
4.一个表中可以创建多个索引，基于一个字段也可以创建多个索引。
二、索引的分类
1.B树索引：
最常用
a）普通索引
i.只包含索引关键字index
ii.对建立索引的列没有唯一性要求
b）唯一性索引
i.关键字unique
ii.创建索引的列，或列的组合值必须唯一
c）主键索引
i.关键字primarykey
ii.只能在创建主键约束时自动创建
d）全文索引
i.关键字fulltext
ii.只能在varchar或text类型的列上创建
iii.对于大规模的数据集，通过altertable或createtable命令创建全文索引要比把记录插入带有全文索引的空表更快。
2.HASH（哈希）索引：
速度较快
a）不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。
三、创建索引
1.createindex语句创建索引
a）格式：
create[unique|fulltext|spatial]index索引名
[usingbtree|hash]
ontable_name（索引列[（length）][asc|desc]）
其中：
using子句表示索引的类型，默认为btree;length表示使用索引列的前多少个字符创建索引，这在索引列值较长的情况下比较有用。
如在blob或text列上创建索引。
b）例题：
例5.1createindexstonstudent（stuname（4）asc）;
查看索引的命令:
showindexfrom表名
例如：
showindexfromstudent;
例5.2创建复合索引。
createindexxskconstudentcourse（id,cno）;
删除索引的命令：
dropindex索引名on表名
例如：
dropindexxskconstudentcourse;
createindexxskconstudentcourse（idasc,cnodesc）;
2.altertable语句创建索引
a）格式：
altertable表名addindex[索引名][索引类型]（索引字段）//普通索引
|add[constraint约束名]primarykey[索引类型]（主键字段）//主键索引
|add[constraint约束名]unique[索引名][索引类型]（唯一键字段）//唯一性索引
|add[constraint约束名]foreignkey[索引名]（外键字段）reference//外键索引
|add[fulltext|spatial][索引名]（索引字段）//全文索引或空间索引
|disablekeys
|enablekeys
说明：
因为表中只能有一个主键，所以主键索引的名字就是primary，不需要人为命名；disablekeys表示更新表时停止更新表中的非唯一性索引；enablekeys重新创建丢失的索引。
b）例题：
例5.3altertablestudentaddindexin_nameusingbtree（stuname）;
例5.4altertablestudentaddprimarykey（id）,addindexmark（birthday,性sex）;
3.createtable语句创建索引
a）格式：
createtable表名（字段的定义|约束的定义|索引的定义）;
b）例题：
例5.5
createtablexskc
（
stuidchar（6）notnull,
cnochar（3）notnull,
resulttinyint
（1）,
credittinyint
（1）,
primarykey（stuid,cno）,//创建主键约束时自动生成的索引
indexcj（result）//普通索引
）;
四、删除索引
1.drop命令删除索引：
dropindex索引名on表名
例5.6dropindexstonstudent;
2.altertable命令删除索引：
a）格式：
altertable表名|dropprimarykey|dropindex索引名
|dropforeignkey索引名
b）例题：
例5.7altertablestudendropprimarykey,dropindexmark;
五、图形化工具创建和删除索引
六、索引的应用与弊端
1.应用索引：
系统自动根据条件选择已有的索引进行应用，不需要我们进行选择。
对于我们来说，只需要在经常用于排序、分组、查询条件的字段上建立索引即可。
2.索引的弊端：
索引并不是越多越好，索引在以下方面存在弊端:
1）占用存储空间2）在对表中索引列进行增、删、改操作时，也需要对索引数据进行维护，因此降低了操作效率。
七、数据完整性约束
1.概念：
指的是数据的一致性和正确性。
2.表中一旦定义了完整性约束，对表中数据的每一次变动，系统都要检查新数据是否满足约束。
3.创建约束的统一格式：
[constraint约束名]约束关键字及定义语句
4.创建约束的命令：
a）createtable
b）altertable
5.主键约束
a）主键约束：
主键字段值不能重复、不能为空、一个表只能有一个主键。
b）格式：
[constraint约束名]primarykey[（字段名）]
注意：
当主键约束直接建立字段后面的时候，主键字段名必须省略。
c）例题：
例5.8
createtablexs1
（
stuidvarchar（6）null,
stunamevarchar（8）primarykey,
birthdaydatetime
）;
或者：
createtablexs1
（
stuidvarchar（6）null,
stunamevarchar（8）,
primarykey（stuname）,
birthdaydatetime
）;
或者：
createtablexs1
（
stuidvarchar（6）null,
stunamevarchar（8）,
constraintpkprimarykey（stuname）,
birthdaydatetime
）;
或者：
createtablexs1
（
stuidvarchar（6）null,
stunamevarchar（8）,
birthdaydatetime
）;
altertablexs1addconstraintpk_xmprimarykey（stuname）;
descxs1;
例5.9组合主键的创建
createtablecourse
（
stuidvarchar（6）notnull,
stunamevarchar（8）notnull,
graduation_datedatenotnull,
cnovarchar（3）,
credittinyint,
primarykey（stuid,cno,graduation_date）
）;
例5.10
createtablecourse
（
stuidvarchar（6）notnull,
stunamevarchar（8）notnull,
graduation_datedatenotnull,
cnovarchar（3）,
credittinyint,
primarykeyindex_course（stuid,cno,gradation_date）
）;
注意：
本例中描述的修改主键的索引名的方法是不成立的，可以使用show命令查看，在其他的约束类型中可以成立。
showindexfromtable
6.替代键约束
a）替代键约束：
又称唯一键约束，限制被约束字段的唯一性。
b）格式：
[constraint约束名]unique[唯一索引名称][（字段名）]
注意：
当替代键约束直接建立字段后面的时候，替代键字段名必须省略。
c）例题：
例5.11
createtablexs2
（
stuidvarchar（6）null,
stunamevarchar（8）notnullunique,
birthdaydatetimenull,
primarykey（stuid）
）;
或者：
createtablexs2
（
stuidvarchar（6）null,
stunamevarchar（8）notnull,
birthdaydatetimenull,
primarykey（stuid）,
uniqueuq_index（stuname）
）;
注意：
在创建替代键约束的时候系统自动生成的索引名可以通过以上方式改名，可以使用show命令查看。
7.参照完整性约束
a）参照完整性约束：
子表中的外键字段的值受父表中的主键或唯一键的约束取值只能在父表参照列的范围内，并且父表中的主键值或唯一键值一旦被子表参照那么这些值就不能随意的修改或删除。
b）因此，参照完整性约束实际上对父表和子表都有一定的约束作用。
c）格式：
[constraint约束名][foreignkey[外键索引名称]（字段名）]references父表（主键|唯一键）[ondeleterestrict|cascade|setnull|noaction]
[onupdaterestrict|cascade|setnull|noaction]
注意：
外键约束直接建立在字段后面的时候，“[foreignkey[外键索引名称]（字段名）]”这部分应该省略。
但是利用这种方式在MySQL中创建的外键往往不生效，尽管命令执行时无误。
在其他关系型数据库中可以正常。
restrict:
当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新。
默认选项
cascade:
从父表删除或更新行时自动删除或更新子表中匹配的行。
setnull:
当从父表删除或更新行时，设置子表中与之对应的外键列为null
noaction：
如果一个有相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和restrict一样。
setdefault：
作用和setnull一样，只不过setdefault是指定子表中的外键列为默认值。
补充：
MYSQL中InnoDB类型的表支持对外键限制条件进行检查。
对于其它存储引擎，MySQL服务器对CREATETABLE语句中的FOREIGNKEY和REFERENCES语法进行分析，但不采取进一步的行动。
d）例题：
例5.12
createtablexs6
（
stuidchar（6）null,
stunamevarchar（8）notnull,
birthdaydatetimenull,
primarykey（stuname）,
foreignkey（stuid）referencesstudent（id）ondeleterestrictonupdaterestrict
）engine=innodb;
例5.13
createtablexs4
（
stuidvarchar（6）null,
stunamevarchar（8）notnull,
birthdaydatetimenull,
primarykey（stuname）,
foreignkey（stuid）referencesstudent（id）onupdatecascade
）;
//级联更新
创建两个简单的表，实验参照约束。
可以使用图形化管理工具查看外键创建的是否成功。
createtableclass
（
cidintprimarykey,
cnamevarchar（20）
）;
insertintoclassvalues（1,'1班'）;
insertintoclassvalues（2,'2班'）;
createtablestudent1
（
sidintprimarykey,
snamevarchar（10）,
cidint,
foreignkey（cid）referencesclass（cid）
）;
但是若使用下面的形式创建外键命令没错，但是外键未能创建。
createtablestudent1
（
sidintprimarykey,
snamevarchar（10）,
cidintreferencesclass（cid）
）;
或者：
createtablestudent1
（
sidintprimarykey,
snamevarchar（10）,
cidint,
indexcid（cid）
）;
altertablestudent1addconstraintfk_cidforeignkey（cid）referencesclass（cid）;
在向子表中插入新数据时，试验外键的约束效果：
insertintostudent1values（1,'张三',1）;
insertintostudent1values（2,'李四',3）;//插入失败，外键约束生效
updateclasssetcid=4wherecid=1;//对父表的更新被拒绝，因为外键约束默认选项是restrict
updateclasssetcid=4wherecid=2;//更改成功，因为该记录没有被子表引用，也就是说与子表的数据无关
修改上面的外键约束使父表在执行更新或删除操作时，对子表能够实现级联操作。
droptablestudent;
createtablestudent
（
sidintprimarykey,
snamevarchar（10）,
cidint,
foreignkey（cid）referencesclass（cid）ondeletecascadeonupdatecascade
）;
insertintostudentvalues（1,'张三',1）;
select*fromclass;
select*fromstudent;
updateclasssetcid=5wherecid=1;//更新成功，并且子表中的数据也更新
select*fromclass;
select*fromstudent;
8.check检查完整性约束
a）检查约束是指对一个字段或多个字段取值范围的约束。
若某个（某些）字段定义了检查约束，那么当对表执行插入或修改数据的操作时，新数据必须满足检查约束规定的取值范围插入或修改操作才能成功。
b）一个检查约束可以限制一个字段的取值范围，也可以同时限制多个字段的取值范围。
c）格式：
[constraint约束名]check（检查约束的表达式）
i.检查约束的表达式：
就是对被约束字段的取值范围进行限制的式子。
d）例题：
例5.14
createtablestudent2
（
idchar（6）notnull,
sexchar
（1）notnullcheck（sexin（'男','女'））
）;
insertintostudent2values（'1001','大'）;//插入成功
select*fromstudent;
注意：
MYSQL中所有的存储引擎（如，INNODB和MyISAM）均对CHECK子句进行分析，但是忽略CHECK子句。
droptablestudent3;
createtablestudent3
（idchar（6）notnull,
sexenum（'男','女'）
）;
其中，enum表示枚举类型。
insertintostudent2values（'1001','大'）;//插入失败
insertintostudent2values（'1001','男'）;
例5.15
例5.16
例5.17
9.命名完整性约束
a）在创建约束的时候，可以为完整性约束起名字，但只能在表级约束上为约束命名，也就是说，在字段定义完成后，单独的定义约束时可以为约束命名。
b）格式：
constraint约束名约束的定义
c）例题5.18
10.删除完整性约束
a）删除主键约束的命令：
i.因为主键约束|主键索引的名称为primary而且人为的命名后仍然无效，所以删除主键约束的格式如下：
ii.altertable表名dropprimarykey;
b）删除唯一约束|索引的命令：
i.唯一约束建立后自动的建立唯一性索引，而且通过在图形化管理工具中，mysql只有唯一性索引的名称，所以，在删除唯一性索引的同时就删除了唯一性约束，这一点可以利用desc表名命令验证。
因此删除唯一性约束的命令就是删除唯一性索引的命令，如下：
ii.格式：
a）altertable表名dropindex约束名|索引名
b）dropindex约束名|索引名on表名
iii.例如：
createtablet1
（idint,constraintuq_idunique（id）,
namevarchar（8）
）;
altertablet1dropindexuq_id ;
dropindexuq_idont1 ;
c）删除外键约束的命令：
i.在Mysql中，既有外键约束的名称又有外键约束自动创建的索引名称，所以外键约束可以独立于索引而被删除，格式如下：
ii.altertable表名dropforeignkey外键约束的名称
iii.例题：
createtablet2（idintprimarykey）;
createtablet3（idint,constraintfk_idforeignkey（id）referencest2（id））;
altertablet3dropforeignkeyfk_id;
升级会员 