MySQL数据库索引与数据完整性

一、索引的作用：

1. 快速读取数据；
2. 保证数据记录的唯一性；
3. 实现表与表之间的参照完整性；
4. 在使用group by order by子句进行数据检索时，利用索引可以减少排序和分组的时间； 注意：
5. 一个表最多可以有16个索引；
6. 可以由单个字段创建索引，也可以在多个字段的组合上创建索引，最多不超过15个字段。 二、索引的分类： 按B-树形式存储的主要索引的分类： 1、普通索引（index）没有唯一性要求； 2、唯一索引（unique）必须具有唯一性； 3、主键（primary key）是一种唯一性索引，可以通过修改表的方式加入主键，但每个只能有一个主键。 三、索引的利弊： 1、索引可加快查询的速度（利） 2、索引会增大表文件的容量（弊） 3、在更新含有索引列的数据时，由于索引也要更新，所以会降低添加、删除、修改和其他写入操作的效率（弊） 四、创建索引 1、在创建表时指定索引： 例：在当前数据库创建一个xs表，包含学号、姓名、身份证三个字段，要求： 学号为主索引、姓名为普通索引、sfz为唯一索引： Create table if not exists xs( 学号 char(6) not null primary key, 姓名 char(4),身份证 char(18), Index xm_xs(姓名), Unique index sfz_xs(身份证)); 2、为已建好的表添加索引： 2.1使用create index语句创建索引 使用create index语句可以在一个已创建好的表上创建索引，一个表可以创建多个索引。 2.2创建命令： Create [unique] index index_name On tb_name(col_name); Create index 索引名 on 表名(字段名) 注意：此命令语句不能创建主索引，创建索引时必须为它取名。 例：为xsl表的姓名字段创建一个名为xm_xsl的索引索引，并查看 Create index xm_xsl on xsl(姓名); Desc xsl; 可以使用整个字段作为索引，也可以使用字段的一部分作为一个索引； 例2：使用xs表的学号前3位数字，创建一个索引xh_xsl，并查看 Create index xh_xsl on xsl (学号(3)); Show index from xsl; 五、使用alter table语句添加索引 由两个以上的字段组合而成的索引叫复合索引。 Alter table tb_name add index In_name(index_xol_name,………) 例：在xs表中，为学号添加一个主索引，为出生日期和姓名添加一个复合索引mark，并查看 Create table xs6 as select * from xsl; Alter table xs6 Add primary key(学号), Add index mark(出生日期,姓名); 例2：在kb中为课程名添加一个候选索引kcm_kb，并查看 Alter table kb add unique index kcm_kb(课程名); 或create unique index kcm_kb on kb(课程名); 例3：在cjb中为成绩字段创建一个普通索引cj_cj，并查看 Alter table cjb add index cj_cjb(成绩 desc); 查看索引的方法： 1、 desc 表名; 2、 show index from 表名; 3、 show create table 表名; 删除索引： 当一个索引不需要是可以用drop index语句或alter table语句删除；
7. 使用drop index 索引名 on 表名; 例：删除xsl表中的xh_xsl索引 drop index xh_xsl on xsl;
8. 使用alter table 表名 drop index 索引名; 例：删除xsl表中的xm_xsl索引 alter table xsl drop index xm_xsl;
9. 使用alter table 语句删除索引语法： Alter table tb_name(表名) Drop primaty key //删除主键 Drop index index_name(索引名) //删除索引 Drop foreign key fk_name(索引名) //删除外键 例：删除xs表中的主键和mark索引 Alter table xs Drop primary key, Drop index mark; 注意：不能使用drop来创建主键，也不能使用drop命令删除主键 数据完整性约束 在了解数据完整性约束先了解什么是子表什么是父表： 子表与父表： 例：1：n（一对多关系） 1：1方为父表 n：n方为字表 查看外键：show crate table 表名 数据完整性约束 参照完整性约束 外键（foreign key）约束标识表之间的关系，用于强制参照完整性。 可在创建表时定义外键，也可在修改表时添加外键。 当两个表存在一对多关系时，一方是父表，多方是子表，如果父子表中相同的这段，在父表中主键，则在子表中创建外键，以保证子表外键字段的值必须来自父表，同时可设置两个表中数据的一致性，实现参照完整性。