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

1、第5章 索引与数据完整性约束一、 索引的用途与概念1. MySQL中访问表中的行，最常用的是顺序访问和索引访问。2. 索引的概念：根据表中一列或若干列按照一定顺序建立的列值与记录之间的对应关系表。（存储在索引文件中）像不像图书的目录？3. 索引的用途：在表的某些列上创建了索引之后查找数据时可以直接根据该列上的索引找到对应行的位置，从而加快了查找速度。4. 一个表中可以创建多个索引，基于一个字段也可以创建多个索引。二、 索引的分类1. B树索引：最常用a) 普通索引 i. 只包含索引关键字 indexii. 对建立索引的列没有唯一性要求b) 唯一性索引i. 关键字uniqueii. 创建索引的列

2、，或列的组合值必须唯一c) 主键索引i. 关键字 primary keyii. 只能在创建主键约束时自动创建d) 全文索引i. 关键字 fulltextii. 只能在varchar或text类型的列上创建iii. 对于大规模的数据集，通过alter table或create table命令创建全文索引要比把记录插入带有全文索引的空表更快。2. HASH（哈希）索引：速度较快a) 不需要建立树结构，但是所有的值都保存在一个列表中，这个列表指向相关页和行。三、 创建索引1. create index语句创建索引a) 格式：create unique|fulltext|spatial index 索

3、引名 using btree|hashon table_name(索引列(length) asc|desc )其中：using子句表示索引的类型，默认为btree; length表示使用索引列的前多少个字符创建索引，这在索引列值较长的情况下比较有用。如在blob或text列上创建索引。b) 例题：例5.1 create index st on student(stuname(4) asc);查看索引的命令: show index from 表名例如：show index from student;例 5.2 创建复合索引。create index xskc on studentcourse(i

4、d,cno);删除索引的命令：drop index 索引名 on 表名 例如：drop index xskc on studentcourse;create index xskc on studentcourse(id asc ,cno desc);2. alter table语句创建索引a) 格式：alter table 表名 add index 索引名 索引类型(索引字段) /普通索引|add constraint 约束名 primary key 索引类型(主键字段) /主键索引|add constraint 约束名 unique 索引名 索引类型(唯一键字段) /唯一性索引|add co

5、nstraint 约束名 foreign key 索引名 (外键字段) reference/外键索引|add fulltext|spatial索引名 (索引字段) /全文索引或空间索引|disable keys|enable keys 说明：因为表中只能有一个主键，所以主键索引的名字就是primary，不需要人为命名；disable keys表示更新表时停止更新表中的非唯一性索引；enable keys重新创建丢失的索引。b) 例题：例5.3 alter table student add index in_name using btree(stuname);例 5.4 alter table

6、 student add primary key(id),add index mark(birthday,性sex);3. create table 语句创建索引a) 格式：create table 表名(字段的定义|约束的定义|索引的定义);b) 例题：例 5.5 create table xskc(stuid char(6) not null,cno char(3) not null,result tinyint(1),credit tinyint(1),primary key(stuid,cno), /创建主键约束时自动生成的索引index cj(result) /普通索引);四、 删除

7、索引1. drop 命令删除索引：drop index 索引名 on 表名例5.6 drop index st on student;2. alter table 命令删除索引：a) 格式：alter table 表名 |drop primary key |drop index 索引名 |drop foreign key 索引名 b) 例题：例 5.7 alter table studen drop primary key,drop index mark;五、 图形化工具创建和删除索引六、 索引的应用与弊端1. 应用索引：系统自动根据条件选择已有的索引进行应用，不需要我们进行选择。对于我们来说

8、，只需要在经常用于排序、分组、查询条件的字段上建立索引即可。2. 索引的弊端：索引并不是越多越好，索引在以下方面存在弊端: 1)占用存储空间 2)在对表中索引列进行增、删、改操作时，也需要对索引数据进行维护，因此降低了操作效率。七、 数据完整性约束1. 概念：指的是数据的一致性和正确性。2. 表中一旦定义了完整性约束，对表中数据的每一次变动，系统都要检查新数据是否满足约束。3. 创建约束的统一格式：constraint 约束名 约束关键字及定义语句4. 创建约束的命令：a) create tableb) alter table5. 主键约束a) 主键约束：主键字段值不能重复、不能为空、一个表只

9、能有一个主键。b) 格式：constraint 约束名 primary key (字段名)注意：当主键约束直接建立字段后面的时候，主键字段名必须省略。c) 例题：例 5.8 create table xs1(stuid varchar(6) null,stuname varchar(8) primary key,birthday datetime);或者：create table xs1(stuid varchar(6) null,stuname varchar(8) ,primary key(stuname),birthday datetime);或者：create table xs1(st

10、uid varchar(6) null,stuname varchar(8) ,constraint pk primary key(stuname),birthday datetime);或者：create table xs1(stuid varchar(6) null,stuname varchar(8) ,birthday datetime);alter table xs1 add constraint pk_xm primary key(stuname);desc xs1;例 5.9 组合主键的创建create table course(stuid varchar(6) not null

11、,stuname varchar(8) not null,graduation_date date not null,cno varchar(3),credit tinyint,primary key(stuid,cno,graduation_date);例 5.10 create table course(stuid varchar(6) not null,stuname varchar(8) not null,graduation_date date not null,cno varchar(3),credit tinyint,primary key index_course(stuid,

12、cno,gradation_date);注意：本例中描述的修改主键的索引名的方法是不成立的，可以使用show命令查看，在其他的约束类型中可以成立。 show index from table6. 替代键约束a) 替代键约束：又称唯一键约束，限制被约束字段的唯一性。b) 格式：constraint 约束名 unique 唯一索引名称(字段名)注意：当替代键约束直接建立字段后面的时候，替代键字段名必须省略。c) 例题：例 5.11create table xs2(stuid varchar(6) null,stuname varchar(8) not null unique,birthday da

13、tetime null,primary key(stuid);或者：create table xs2(stuid varchar(6) null,stuname varchar(8) not null,birthday datetime null,primary key(stuid),unique uq_index(stuname);注意：在创建替代键约束的时候系统自动生成的索引名可以通过以上方式改名，可以使用show命令查看。7. 参照完整性约束a) 参照完整性约束：子表中的外键字段的值受父表中的主键或唯一键的约束取值只能在父表参照列的范围内，并且父表中的主键值或唯一键值一旦被子表参照那么这

14、些值就不能随意的修改或删除。b) 因此，参照完整性约束实际上对父表和子表都有一定的约束作用。c) 格式：constraint 约束名 foreign key 外键索引名称 (字段名) references 父表(主键|唯一键) on delete restrict | cascade| set null | no actionon update restrict | cascade| set null | no action注意：外键约束直接建立在字段后面的时候，“ foreign key 外键索引名称 (字段名) ” 这部分应该省略。但是利用这种方式在MySQL中创建的外键往往不生效，尽管命

15、令执行时无误。在其他关系型数据库中可以正常。restrict: 当要删除或更新父表中被参照列上在外键中出现的值时，拒绝对父表的删除或更新。 默认选项cascade: 从父表删除或更新行时自动删除或更新子表中匹配的行。set null: 当从父表删除或更新行时，设置子表中与之对应的外键列为nullno action：如果一个有相关的外键值在被参考的表里，删除或更新父表中主要键值的企图不被允许，和restrict一样。set default：作用和set null一样，只不过set default是指定子表中的外键列为默认值。补充：MYSQL中 InnoDB类型的表支持对外键限制条件进行检查。对于

16、其它存储引擎，MySQL服务器对CREATE TABLE语句中的FOREIGN KEY和REFERENCES语法进行分析，但不采取进一步的行动。d) 例题：例 5.12create table xs6(stuid char(6) null,stuname varchar(8) not null,birthday datetime null,primary key(stuname),foreign key(stuid) references student(id) on delete restrict on update restrict) engine=innodb;例 5.13create

17、table xs4(stuid varchar(6) null,stuname varchar(8) not null,birthday datetime null,primary key(stuname),foreign key(stuid) references student(id) on update cascade );/ 级联更新创建两个简单的表，实验参照约束。可以使用图形化管理工具查看外键创建的是否成功。create table class(cid int primary key,cname varchar(20);insert into class values(1,1班);i

18、nsert into class values(2,2班);create table student1(sid int primary key,sname varchar(10),cid int, foreign key(cid) references class(cid);但是若使用下面的形式创建外键命令没错，但是外键未能创建。create table student1(sid int primary key,sname varchar(10),cid int references class(cid) );或者：create table student1(sid int primary k

19、ey,sname varchar(10),cid int,index cid(cid);alter table student1 add constraint fk_cid foreign key(cid) references class(cid);在向子表中插入新数据时，试验外键的约束效果：insert into student1 values(1,张三,1);insert into student1values(2,李四,3); /插入失败，外键约束生效update class set cid=4 where cid=1; /对父表的更新被拒绝，因为外键约束默认选项是restrictup

20、date class set cid=4 where cid=2; /更改成功，因为该记录没有被子表引用，也就是说与子表的数据无关修改上面的外键约束使父表在执行更新或删除操作时，对子表能够实现级联操作。drop table student;create table student(sid int primary key,sname varchar(10),cid int, foreign key(cid) references class(cid) on delete cascade on update cascade);insert into student values(1,张三,1);s

21、elect * from class;select * from student;update class set cid=5 where cid=1; /更新成功，并且子表中的数据也更新select * from class;select * from student;8. check检查完整性约束a) 检查约束是指对一个字段或多个字段取值范围的约束。若某个（某些）字段定义了检查约束，那么当对表执行插入或修改数据的操作时，新数据必须满足检查约束规定的取值范围插入或修改操作才能成功。b) 一个检查约束可以限制一个字段的取值范围，也可以同时限制多个字段的取值范围。c) 格式：constraint

22、 约束名 check(检查约束的表达式)i. 检查约束的表达式：就是对被约束字段的取值范围进行限制的式子。d) 例题：例 5.14create table student2(id char(6) not null,sex char(1) not null check(sex in(男,女);insert into student2 values(1001,大); /插入成功select * from student;注意：MYSQL中所有的存储引擎（如，INNODB和MyISAM）均对CHECK子句进行分析，但是忽略CHECK子句。drop table student3;create table student3 (id char(6) not null, sex enum(男,女) );其中，enum表示枚举类型。insert into student2 values(1001,大); /插入失败insert into student2 values(1001,男);例 5.15例 5.16例 5.179. 命名完整性约束a) 在创建约束的时候，可以为完整性约束起名字，但只能在表级约束上为约束命名，也就是说，在字段定义完成后，单独的定义约束时可以为约束命名。b)