电工与电子技术电子商务电子课件MySQL数据库应用4.1 索引的创建和管理1ppt

1、电子课件MySQL数据库应用4.1 索引的创建和管理1项目4：创建数据库对象数据库应用任务1 创建索引与视图 索引的创建和管理(1) 教学目标 素质目标 知识目标1.养成对知识归纳、总结，并在此基础上记忆的好习惯；2.培养学生养成温故而知新的良好的学习方法；3.思政内容：本次课讲创建数据表的索引，可融入在人类历史上提高生产效率的经历1.了解创建索引的意义及在哪些情况下需要创建索引；2.理解普通索引、唯一索引、主键索引、多列索引和全文索引的作用及应用场景；3.掌握创建普通索引、唯一索引、主键索引、多列索引和全文索引的方法； 能力目标1.能够理解创建索引的意义和作用；2.能够根据应用需求的不同创建

2、不同类型的索引。 教学重点创建普通索引、唯一索引、主键索引、多列索引和全文索引 教学难点对几种索引创建方法的归纳、总结并回顾数据表的创建方法教学重难点1.抽查与评讲作业；2.复习查询结果输出与合并;3.检查课程预习情况；4.互动:1）索引的作用是什么？2）有哪些情况需要建立索引？ 5索引的创建和管理知识储备1什么是索引索引是对某个表中一列或若干列值进行排序的结构。它由该表的一列或多列的值，以及指向这些列值对应存储位置的指针所构成。索引是依赖于表建立的，一个表由两部分组成：一部分用来存放表的数据页面，另一部分存放索引页面。由于索引页面比数据页面小得多，在进行数据检索时，系统会先搜索索引页面，从中

3、找到所需数据的指针，再通过指针从数据页面中读取数据。这种操作模式类似于图书的目录。（一）索引的作用相对于没有使用索引而言，使用索引可以提高数据查询速度百倍、千倍、万倍以上。 通过对多个字段使用唯一索引，可以保证多个字段的唯一性。在表与表之间连接查询时，如果创建了索引，就可以提高表与表之间连接的速度。 索引的创建和管理 6知识储备（二）适合创建索引的情况经常被查询的字段。分组字段。设置了唯一性约束的字段。另外，指定为主键的列会自动创建主键索引，外键列的索引会由MySQL根据参照的主键列自动创建的。（三）不适合创建索引的情况在查询中很少用到的字段。具有大量重复值的字段，例如性别字段。较小的数据表，

4、这种情况使用索引并不能改善任何检索性能。 索引的创建和管理 7知识储备2创建索引（一）创建普通索引 （1）创建表时创建普通索引在MySQL数据库中，可以在创建数据表时创建普通索引。语法规则：CREATE TABLE 表名 (列名 数据类型， INDEX|KEY 索引名（列名i 长度ASC|DESC); 索引的创建和管理 8知识储备【创建表时创建普通索引示例】根据表4-1所示的结构，在测试数据库XSCJ_db中新建XSQK1表并创建普通索引，相关列为“学号”。mysql use xscj_db;Database changedmysql create table xsqk1( - 学号 char

5、(10), 表4-1 XSQK1表的结构 - 姓名 varchar(10), - 性别 char(2), - index index_xh(学号) );其中，index_xh是创建的普通索引名称。 索引的创建和管理 9列名数据类型长度（字节）索引学号char10index_xh姓名varchar10 性别char2知识储备互动：按下表结构，新建XSQK2表并创建普通索引，相关列为“学号”。 索引的创建和管理 10列名数据类型长度（字节）索引学号char10index_xh姓名varchar10 性别char2知识储备（2）在已经存在的表上创建普通索引在MySQL数据库中，可以在已经存在的表上创

6、建普通索引。语法规则：CREATE INDEX 索引名 ON 表名（列名长度 ASC|DESC）; 【在已经存在的表上创建普通索引示例】在XSCJ_db数据库中的KC_db表的“课程名”上创建索引。mysql create index index_kcm - on kc_db(课程名); 索引的创建和管理 11知识储备互动：在XSCJ_db数据库中的KC_db表的“授课教师”上创建索引index_skjs。索引的创建和管理 12知识储备（3）通过ALTER TABLE语句创建普通索引语法规则：ALTER TABLE 表名 ADD INDEX|KEY 索引名(列名长度 ASC|DESC);【通过

7、ALTER TABLE语句创建普通索引示例】在XSCJ_db数据库中XSQK1表的“姓名”列上创建普通索引。mysql alter table xsqk1 - add index index_xm(姓名); 索引的创建和管理 13知识储备互动：通过ALTER TABLE语句创建普通索引：在XSCJ数据库中XSQK表的“专业名”列上创建普通索引。索引的创建和管理 14知识储备(二)创建唯一索引 唯一索引和普通索引类似，但唯一索引要求索引列的值是唯一的，需要使用关键字UNIQUE来标明。创建唯一索引与创建普通索引一样也有三种方式。一是建表时创建唯一索引，语法规则：CREATE TABLE 表名 (

8、列名 数据类型， UNIQUE INDEX|KEY 索引名（列名i 长度ASC|DESC); 索引的创建和管理 15知识储备【在新建表上创建唯一索引示例】根据表4-2所示的结构，在测试数据库XSCJ_db中新建XSQK2表，创建唯一索引，相关列为“学号”。 表4-2 XSQK2表的结构 mysql create table xsqk2( - 学号 char(10), - 姓名 varchar(10), - 性别 char(2), - unique index index_xh(学号);索引的创建和管理 16列 名数 据 类 型长 度（字节）索 引学号char10index_xh姓名varcha

9、r10 性别char2知识储备二是在已经存在的表上创建唯一索引，语法规则：CREATE UNIQUE INDEX 索引名 ON 表名(列名长度 ASC|DESC);【在已存在的表上创建唯一索引示例】在XSCJ_db数据库中的XSQK_db的姓名列上创建唯一索引，SQL语句：mysql create unique index index_kcm - on xsqk_db(姓名);索引的创建和管理 17知识储备三是通过ALTER TABLE语句创建唯一索引，语法规则：ALTER TABLE 表名 ADD UNIQUE INDEX|KEY 索引名(列名长度 ASC|DESC)；【通过ALTER TA

10、BLE语句创建唯一索引示例】在XSCJ_db数据库中XSQK2表的“姓名”列上创建唯一索引，SQL语句：mysql alter table xsqk2 - add unique index index_xm(姓名);索引的创建和管理 18知识储备互动1：根据如表所示的结构，在测试数据库XSCJ中新建XSQK4表，创建唯一索引，相关列为“学号”。 表4-2 XSQK4表的结构 互动2：在已存在的表XSQK4上创建唯一索引:在互动中创建的XSQK4的姓名列上创建唯一索引互动3：通过ALTER TABLE语句在已存在的表XSQK上创建唯一索引相关列为“姓名”列上创建唯一索引索引的创建和管理 19列

11、名数 据 类 型长 度（字节）索 引学号char10index_xh姓名varchar10 性别char2知识储备（三）创建主键索引 每张表都有一个主键索引，并且只有一个，在创建表的主键时就会创建主键索引，也可以通过关键字ALTER增加主键索引（创建主键索引的方法在“项目二”中讲过）.索引的创建和管理 20知识储备（四）创建全文索引 索引一般建立在数字型或长度比较短的文本型字段上，如编号、姓名等。如果建立在比较长的文本型字段上，会使索引的更新花费很多的时间。在MySQL中，提供了一种称为“全文索引”的技术，主要关联在数据类型为Char、Varchar和Text等的长字符字段上。创建全文索引有三

12、种方式:（1）创建表时创建全文索引在MySQL数据库中，可以在创建数据表的时候创建全文索引。创建表时创建全文索引语法规则是：CREATE TABLE 表名 (列名 数据类型， FULLTEXT INDEX|KEY 索引名（列名i 长度ASC|DESC);索引的创建和管理 21知识储备【创建表时创建全文索引示例】根据表4-3所示结构，在测试数据库XSCJ_db中新建XSQK3表，并创建全文索引，相关列为“备注”列。 表4-3 XSQK3表的结构mysql create table xsqk3( - 学号 char(10), - 姓名 varchar(10), - 备注 char(100), -

13、Fulltext index index_bz(备注);索引的创建和管理 22列 名数 据 类 型长度（字节）索 引学号Char10 姓名Varchar10 备注Varchar100Index_bz知识储备（2）在已经存在的表上创建全文索引【在已经存在的表上创建全文索引示例】先删除XSQK3表上的全文索引，然后在备注列上创建全文索引。mysql create fulltext index index_bz - on xsqk3(备注);索引的创建和管理 23知识储备（3）通过ALTER TABLE语句创建全文索引【通过ALTER TABLE语句创建全文索引示例】先删除XSQK3表上的全文索引，

14、然后在备注列上创建全文索引：mysql alter table xsqk3 - add fulltext index index_bz(备注);索引的创建和管理 24知识储备互动操作：在测试数据库XSCJ_db中新建XSQK3表，并创建全文索引，相关列为“备注”列。 索引的创建和管理 25列 名数 据 类 型长度（字节）索 引学号Char10 姓名Varchar10 备注Varchar100Index_bz知识储备（五）创建多列索引 如果在创建索引时，所关联的列有两个或多个列，就称为多列索引。需要注意的是，只有查询条件中使用了这些列中的第一个列时，多列索引才会被使用。（1）创建表时创建多列索引

15、语法规则是：CREATE TABLE 表名 (列名 数据类型， INDEX|KEY 索引名（列名1长度ASC|DESC，列名1长度ASC|DESC，);索引的创建和管理 26知识储备【创建表时创建多列索引示例】根据表4-4所示结构，新建XSQK4表并创建多列索引，索引列为表中的“学号”、“姓名”列。 表4-4 XSQK4表的结构mysql create table xsqk4( - 学号 int, - 姓名 char(10), - 性别 char(2), - 专业名 varchar(20), - index index_xh_xm(学号,姓名);索引的创建和管理 27列 名数 据 类 型长度（

16、字节）索 引学号Char10Index_xh_xm姓名Varchar10性别Char2 专业名Varchar20知识储备（2) 在已经存在的表上创建多列索引语法规则是：CREATE INDEX 索引名 ON 表名（列名1长度ASC|DESC，列名1长度ASC|DESC，）;【在已经存在的表上创建多列索引示例】先删除XSQK4表上的多列索引，然后在XSQK4表上创建多列索引，索引列为表中的“学号”“姓名”列。mysql create index index_xh_xm - on xsqk4(学号,姓名);索引的创建和管理 28知识储备（3）通过ALTER TABLE语句创建多列索引语法规则是：ALTER TABLE 表名 ADD INDEX|KEY 索引名(列名长度 ASC|DESC，列名长度 ASC|DESC，);【通过ALTER TABLE语句创建多列索引示例】先删除XSQK4表上的多列索引，然后在XSQK4表上创建多列索引，索引列为表中的“学号”“姓名”列。mysql alter table xsqk4 - add key index_xh_xm(学号,姓名);索引的创建和管理 29