第4章 索引的创建和使用

第4章索引的创建和使用1.索引概述2.创建索引3.删除索引4.约束管理本章内容1.索引概述索引是一种与数据表相关的类似于目录的一种数据结构，使用索引可以提高查询的效率。使用索引提高查询效率的原理索引包含由列生成的键值+数据页地址的指针组成的。索引的键值是排序的，排序的数据可以利用各种高效的查找算法（例如，折半查找等）。索引的优点提高查询速度。提高表与表之间连接的效率。唯一索引还可以保证数据记录的唯一性。1.索引概述索引的缺点索引可以极大提高查询效率，但并不是索引创建的越多越好。这是因为：索引需要额外的维护时间，导致插入数据、更新数据需要更多的时间。索引需要额外的存储空间。1.索引概述使用索引的原则（1）考虑设置索引的情况：经常检索的列（在WHERE子句中使用的列）。主键列、外键列（事实上，主键约束列、唯一约束列会自动创建索引）。经常用于表间连接的列。（2）考虑不设置索引的情况：检索中几乎不涉及到的列。重复值太多的列。数据类型为text、blob的列。行数极少的表没必要创建索引。插入、更新效率比查询效率更重要的情况。2.创建索引（1）使用Navicat对话方式创建索引2.创建索引（2）在CREATETABLE语句中创建索引可以使用CREATETABLE语句在创建数据表的同时创建索引，其语法格式如下：CREATETABLE<表名>(

<字段名1>[,……n]|索引项)[存储引擎][表字符集];其中索引项的语法格式为：PRIMARYKEY[<索引名>](<字段名1>[ASC|DESC][,……n])|UNIQUE[INDEX][<索引名>](<字段名1>[ASC|DESC][,……n])|INDEX|KEY[<索引名>](<字段名1>[ASC|DESC][,……n])|SPATIAL[INDEX][<索引名>](<字段名1>[ASC|DESC][,……n])|FULLTEXT[INDEX][<索引名>](<字段名1>[ASC|DESC][,……n])2.创建索引（2）在CREATETABLE语句中创建索引示例4-1：创建学生表（student1），在sNo字段上创建主键索引，在sName字段上创建普通索引。2.创建索引（2）在CREATETABLE语句中创建索引示例4-2：创建课程表（course1），在id字段上创建主键索引。2.创建索引（3）在ALTERTABLE语句中创建索引如果数据表已经存在，可以使用ALTERTABLE语句创建索引，其语法格式如下：ALTERTABLE<表名>ADD索引项;2.创建索引（3）在ALTERTABLE语句中创建索引示例4-3：创建成绩表（score1），在id字段上创建主键索引。2.创建索引（3）在ALTERTABLE语句中创建索引示例4-4：修改课程表（course1），在cNo字段上创建唯一索引、在cName字段上创建普通索引。2.创建索引（4）使用SQL语句创建索引创建索引的语句使用CREATEINDEX，其语法格式如下：CREATE[UNIQUE]|[FULLTEXT]INDEX<索引名>ON<表名>(<列名1>[ASC|DESC][,……n]);2.创建索引（4）使用SQL语句创建索引示例4-5：在成绩表（score1）的sId、cId字段上创建唯一索引（复合索引）。2.创建索引（5）使用SQL语句查看索引查看索引使用语句SHOWINDEX，其语法格式如下：SHOWINDEXFROM<表名>[FROM<数据库名>];2.创建索引（5）使用SQL语句查看索引示例4-6：查看学生表（student1）中的索引。3.删除索引（1）使用Navicat对话方式删除索引3.删除索引（2）使用SQL语句删除索引删除索引使用语句DROPINDEX，其语法格式如下：DROPINDEX<索引名>ON<表名>;3.删除索引（2）使用SQL语句删除索引示例4-7：删除课程表（course1）中的索引ix_cName。4.约束管理约束是是对列进行限制的规则，以确保输入数据的一致性和正确性。约束是实现数据完整性的主要途径。常见的约束有：主键约束、唯一性约束、默认约束、外键约束等。其中，主键约束与主键索引相对应，唯一性约束与唯一性索引相对应。约束可以在创建数据表时创建，也可以在修改数据表时创建。4.约束管理（1）默认约束（DEFAULT）默认约束是用来给表中某列赋予一个常量值（默认值），当向该表插入数据时，如果用户没有明确给出该列的值，MySQL会自动为该列输入默认值。每列只能有一个DEFAULT约束。4.约束管理（1）默认约束（DEFAULT）①使用Navicat对话方式创建默认约束4.约束管理（3）默认约束（DEFAULT）②在CREATETABLE语句中创建默认约束在CREATETABLE语句中创建默认约束的语法格式如下：CREATETABLE<表名>(

<字段名1>DEFAULT默认值

[,......n])[存储引擎][表字符集];4.约束管理（3）默认约束（DEFAULT）②在CREATETABLE语句中创建默认约束示例4-8：创建学生表（student2），为sex字段设置默认值“女”。4.约束管理（3）默认约束（DEFAULT）③在ALTERTABLE语句中创建默认约束在ALTERTABLE语句中创建默认约束的语法格式如下：删除已存在的默认约束ALTERTABLE<表名> ALTERCOLUMN<字段名>DROPDEFAULT设置默认约束ALTERTABLE<表名>

ALTERCOLUMN<字段名>SETDEFAULT默认值4.约束管理（3）默认约束（DEFAULT）③在ALTERTABLE语句中创建默认约束示例4-9：修改学生表（student2），把sex字段的默认值删除。4.约束管理（3）默认约束（DEFAULT）③在ALTERTABLE语句中创建默认约束示例4-10：修改学生表（student2），为sex字段设置默认值“男”。4.约束管理（4）外键约束（FOREIGNKEY）外键约束是指表A的主键列或唯一索引列是表B的列（外键列），主表A和从表B之间满足以下关系：B表外键列的取值必须是A表主键列或者唯一索引列中的取值。A表主键列或者唯一索引列取值发生改变，B表外键列的值也发生改变。A表主键列或者唯一索引列取值删除，B表外键列取值与该值相同的行会被删除。在MySQL中，目前只有InnoDB存储引擎支持外键约束。4.约束管理（4）外键约束（FOREIGNKEY）①使用Navicat对话方式创建外键约束4.约束管理（4）外键约束（FOREIGNKEY）②在CREATETABLE语句中创建外键约束在CREATETABLE语句中创建外键约束的语法格式如下：CREATETABLE<表名>( <字段名1>[,......n]|外键约束项)[存储引擎][表字符集];其中外键约束项的语法格式为：FOREIGNKEY[约束名](<字段名>)REFERENCES<表名>(<字段名>) [ONDELETE{RESTRICT|NOACTION|CASCADE|SETNULL}] [ONUPDATE{RESTRICT|NOACTION|CASCADE|SETNULL}]4.约束管理（4）外键约束（FOREIGNKEY）②在CREATETABLE语句中创建外键约束示例4-11：创建成绩表（score2），并设置学生ID（sId）字段的外键约束，参照学生表（student1）中的学生ID（id）字段（不执行任何操作）。4.约束管理（4）外键约束（FOREIGNKEY）③在ALTERTABLE语句中创建外键约束如果数据表已经存在，可以使用ALTERTABLE语句创建外键约束，其语法格式如下：ALTERTABLE<表名>ADD外键约束项;4.约束管理（4）外键约束（FOREIGNKEY）③在ALTERTABLE语句中创建外键约束示例4-12：修改成绩表（score2），为课程ID（cId）字段设置外键约束，参照课程表（course1）中的课程ID（id）字段（不执行任何操作）。PPT模板下载：/moban/行业PPT模板：/hangye/节日PPT模板：/jieri/PPT素材下载：/sucai/PPT背景图片：/beijing/PPT图表下载：/tubiao/