第五章索引与数据完整性约束

第5章索引与数据完整性约束一、索引的用途与概念1.MySQL中访问表中的行，最常用的是顺序访问和索引访问。2.索引的概念：根据表中一列或若干列按照一定顺序建立的列值与记录之间的对应关系表。（存储在索引文件中）像不像图书的目录？3.索引的用途：在表的某些列上创建了索引之后查找数据时可以直接根据该列上的索引找到对应行的位置，从而加快了查找速度。4.一个表中可以创建多个索引，基于一个字段也可以创建多个索引。二、索引的分类1.B树索引：最常用a)普通索引i.只包含索引关键字indexii.对建立索引的列没有唯一性要求b)唯一性索引i.关键字uniqueii.创建索引的列，或列的组合值必须唯一c)主键索引i.关键字primarykeyii.只能在创建主键约束时自动创建d)全文索引i.关键字fulltextii.只能在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.5createtablexskc(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)createtableb)altertable5.主键约束a)主键约束：主键字段值不能重复、不能为空、一个表只能有一个主键。b)格式：[constraint约束名]primarykey[(字段名)]注意：当主键约束直接建立字段后面的时候，主键字段名必须省略。c)例题：例5.8createtablexs1(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.10createtablecourse(stuidvarchar(6)notnull,stunamevarchar(8)notnull,graduation_datedatenotnull,cnovarchar(3),credittinyint,primarykeyindex_course(stuid,cno,gradation_date));注意：本例中描述的修改主键的索引名的方法是不成立的，可以使用show命令查看，在其他的约束类型中可以成立。showindexfromtable6.替代键约束a)替代键约束：又称唯一键约束，限制被约束字段的唯一性。b)格式：[constraint约束名]unique[唯一索引名称][(字段名)]注意：当替代键约束直接建立字段后面的时候，替代键字段名必须省略。c)例题：例5.11createtablexs2(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:当从父表删除或更新行时，设置子表中与之对应的外键列为nullnoaction：如果一个有相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和restrict一样。setdefault：作用和setnull一样，只不过setdefault是指定子表中的外键列为默认值。补充：MYSQL中InnoDB类型的表支持对外键限制条件进行检查。对于其它存储引擎，MySQL服务器对CREATETABLE语句中的FOREIGNKEY和REFERENCES语法进行分析，但不采取进一步的行动。d)例题：例5.12createtablexs6(stuidchar(6)null,stunamevarchar(8)notnull,birthdaydatetimenull,primarykey(stuname),foreignkey(stuid)referencesstudent(id)ondeleterestrictonupdaterestrict)engine=innodb;例5.13createtablexs4(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));或者：createtablestudent