MySQL数据库应用技术与实战电子教案 单元5 MySQL视图和索引

单元5MySQL视图和索引课程名称：MYSQL数据库应用课程类别：必修适用专业：计算机技术类相关专业总学时：64学时（其中理论31学时，实践33学时）总学分：4.0学分本章学时：4学时（其中理论2学时，实践2学时）学情分析：学生已完成单元四的学习，掌握了多表连接查询、子查询和分组查询等高级查询技术，能够编写较复杂的SQL查询语句。但学生在日常查询中经常重复编写相同的复杂查询语句，效率较低；在数据量逐渐增大时，查询速度也可能变慢，学生对此缺乏优化意识。本单元将学习视图和索引——视图可以把复杂的查询语句封装成“虚拟表”，简化重复查询；索引可以大幅提升查询效率。学生初次接触“虚拟表”和“索引”等抽象概念，需要通过具体案例演示帮助理解。材料清单《MYSQL数据库应用技术与实战（微课版）》教材。配套PPT。代码及SQL脚本。引导性提问。探究性问题。拓展性问题。教学目标与基本要求教学目标先掌握视图的概念以及创建、查看、修改、删除视图的方法，然后掌握索引的概念、分类以及创建、查看、删除索引的方法，最后能够合理使用视图和索引提高查询效率。基本要求掌握创建视图的方法掌握查看视图的方法掌握修改视图定义的方法掌握删除视图的方法掌握创建索引的方法掌握查看和删除索引的方法能够分析表结构并合理创建索引问题引导性提问引导性提问需要教师根据教材内容和学生实际水平，提出问题，启发引导学生去解决问题，提问，从而达到理解、掌握知识，发展各种能力和提高思想觉悟的目的。在教务系统中，教师经常需要查询“学生姓名、班级名称、成绩”等信息，每次都要编写三表连接查询语句，有什么办法可以把这个查询保存下来直接使用？视图和表有什么区别？视图中的数据是真实存储在磁盘上的吗？为什么要创建索引？索引是不是越多越好？探究性问题探究性问题需要教师深入钻研教材的基础上精心设计，在提问的角度或者在引导性提问的基础上，从重点、难点问题切入，进行插入式提问。或者是对引导式提问中尚未涉及但在课文中又是重要的问题加以设问。对视图进行INSERT、UPDATE、DELETE操作时，数据会直接修改到对应的基表中吗？为什么视图不总是支持更新操作？创建视图时，如果基表的数据发生了变化，视图查询到的数据会跟着变化吗？为什么？普通索引和唯一索引有什么区别？全文索引适用于什么场景？在哪些字段上创建索引效果最明显？为什么索引不是越多越好？拓展性问题拓展性问题需要教师深刻理解教材的意义、学生的学习动态后，根据学生学习层次，提出切实可行的关乎实际的可操作问题。亦可以提供拓展资料供学生研习探讨，完成拓展性问题。视图的算法（ALGORITHM）有MERGE、TEMPTABLE、UNDEFINED三种，它们有什么区别？索引的底层数据结构是什么？为什么MySQL使用B+Tree而不是哈希表来实现索引？在实际项目中，如何分析慢查询日志来发现需要优化的SQL语句，并针对性地创建索引？主要知识点、重点与难点主要知识点视图的概念与作用创建视图（CREATEVIEW）查看视图（DESC/SHOWTABLESTATUS/SHOWCREATEVIEW）修改视图定义（ALTERVIEW）删除视图（DROPVIEW）索引的概念与分类（普通索引/唯一索引/全文索引/单列索引/多列索引/空间索引）创建索引的三种方式查看和删除索引重点视图的创建、查看、修改和删除方法索引的概念与分类创建索引的三种方式查看和删除索引的方法根据表结构和查询需求合理创建索引难点视图与基表的关系及视图的更新限制普通索引、唯一索引、全文索引的区别与适用场景多列索引（复合索引）的理解分析表结构并合理设计索引教学过程设计第一次课（4课时：理论2学时+实践2学时）理论教学（2课时，80分钟）第1课时视图的概念与创建、查看（40分钟）1.课堂导入（5分钟）回顾单元四的三表连接查询。提问：“教师每次查询学生成绩都要编写三表连接语句，语句很长、容易出错，而且不同的教师都要重复编写。有没有办法把这条复杂的查询语句‘保存’起来，以后直接通过一个简单的名称就能查询？”引出本课主题——视图。2.视图的概念与作用（10分钟）讲解视图的概念：视图是一种虚拟表，它本身不存储数据，数据仍然存放在原始表（基表）中。视图是由SELECT查询语句定义的，保存的是查询逻辑，而不是查询结果。（1）视图的特点：视图是虚拟表，不对应真实的存储数据；视图的内容来自基表的查询结果；对视图的查询就是对基表的查询。（2）视图的作用：简化查询——把复杂的连接查询、子查询封装成视图，用户查询视图即可；安全性——可以只向用户开放部分字段，隐藏敏感数据（如密码字段）；逻辑独立性——基表结构变化时，可以通过修改视图定义保持对外接口不变。通过“视图像是一扇定制的窗户”的比喻帮助学生理解：窗户（视图）展示的是房间（基表）内的景象，窗户本身不存放东西。3.创建视图——CREATEVIEW（12分钟）讲解创建视图的语法：CREATEVIEW视图名ASSELECT查询语句;（1）示例：CREATEVIEWv_stu_scoreASSELECTs.stuName,c.className,sc.subject,sc.scoreFROMstuinfosINNERJOINclassinfocONs.classid=c.idINNERJOINstuscorescONs.stuNo=sc.stuNo;创建学生成绩信息视图。（2）创建视图后，直接查询视图：SELECT*FROMv_stu_score;即可得到连接查询的结果，无需再写复杂的连接语句。（3）视图命名规范：建议以v_或view_开头，便于与表区分。（4）创建视图时也可以为字段指定别名，例如CREATEVIEWv_stuASSELECTstuNameAS姓名FROMstuinfo;演示创建视图后使用SELECT*FROM视图名查询的效果，让学生直观感受视图简化查询的作用。4.查看视图（8分钟）讲解查看视图的三种方法：（1）DESC视图名;查看视图的字段结构，与查看表的字段结构方式相同。（2）SHOWTABLESTATUSLIKE‘视图名’;查看视图的状态信息（包括视图名称、创建时间、字符集等），注意视图的Engine字段为NULL。（3）SHOWCREATEVIEW视图名;查看创建视图的定义语句。逐一演示三种查看方法，强调：可以用SHOWTABLES查看所有表和视图的列表，视图也显示在列表中。5.课堂小结（5分钟）总结本课核心知识：视图是保存查询逻辑的虚拟表，本身不存储数据；视图的作用是简化查询、提高安全性和逻辑独立性；创建视图用CREATEVIEW...ASSELECT；查看视图用DESC、SHOWTABLESTATUS、SHOWCREATEVIEW三种方法。下节课学习视图的修改、删除以及索引。第2课时修改与删除视图、索引（40分钟）1.复习导入（3分钟）快速回顾视图的概念和创建方法。提问：“视图创建之后，如果基表新增了字段，想把这个字段也加进视图，怎么办？视图不再需要了，如何删除？另外，随着数据量增大，查询越来越慢，有什么办法提高查询效率？”引出本课内容——修改删除视图和索引。2.修改视图定义——ALTERVIEW（5分钟）讲解修改视图定义的语法：ALTERVIEW视图名ASSELECT查询语句;修改视图本质上是重新定义视图对应的查询语句。（1）示例：ALTERVIEWv_stu_scoreASSELECTs.stuName,c.className,sc.subject,sc.score,sc.addTimeFROMstuinfosINNERJOINclassinfocONs.classid=c.idINNERJOINstuscorescONs.stuNo=sc.stuNo;在原有视图基础上增加录入时间字段。（2）强调：ALTERVIEW必须提供完整的SELECT语句，而不是只写变化的部分。3.删除视图——DROPVIEW（3分钟）讲解删除视图的语法：DROPVIEW视图名;删除视图不会影响基表中的数据，因为视图本身不存储数据。（1）示例：DROPVIEWv_stu;删除视图v_stu。（2）强调：删除视图只是删除保存的查询逻辑，基表数据不受任何影响；如果删除不存在的视图会报错，可以使用DROPVIEWIFEXISTS视图名;避免报错。4.索引的概念与分类（10分钟）讲解索引的概念：索引是一种特殊的文件（在InnoDB存储引擎中，索引是表的一部分），它记录了数据的位置信息，类似于书的目录。查询时先通过索引快速定位数据位置，再读取数据，从而大幅提高查询效率。（1）索引的优缺点：优点——加快查询速度，尤其在数据量大的表中效果明显；缺点——占用存储空间，降低INSERT、UPDATE、DELETE的速度（因为维护索引需要额外开销）。（2）索引的分类：按功能分为普通索引（INDEX）、唯一索引（UNIQUE）、全文索引（FULLTEXT）、空间索引（SPATIAL）；按字段个数分为单列索引和多列索引（复合索引）。（3）普通索引：没有任何限制，允许重复值和空值，是最基本的索引类型。（4）唯一索引：索引列的值必须唯一，允许有空值，适用于身份证号等需要唯一约束的字段；主键索引是一种特殊的唯一索引。（5）全文索引：针对文本内容进行全文检索，适用于大文本字段的模糊搜索，只能建立在CHAR、VARCHAR、TEXT类型字段上。（6）单列索引与多列索引：一个索引只包含一个字段为单列索引；一个索引包含多个字段为多列索引（复合索引），适用于经常同时作为查询条件的多个字段。5.创建索引的三种方式（10分钟）讲解创建索引的三种方式：（1）方式一：在CREATETABLE语句中直接创建索引。在字段定义时或表定义末尾使用INDEX/UNIQUE/FULLTEXT等关键字定义索引。示例：CREATETABLEt1(idINTPRIMARYKEY,nameVARCHAR(20),INDEXidx_name(name));（2）方式二：使用CREATEINDEX语句在已存在的表上创建索引。语法：CREATEINDEX索引名ON表名(字段名);示例：CREATEINDEXidx_scoreONstuscore(score);创建唯一索引：CREATEUNIQUEINDEXidx_idcardONstudetail(IDCard);（3）方式三：使用ALTERTABLE语句在已存在的表上创建索引。语法：ALTERTABLE表名ADDINDEX索引名(字段名);示例：ALTERTABLEstuinfoADDINDEXidx_classid(classid);逐一演示三种方式的语法，强调：CREATEINDEX只能添加索引，而ALTERTABLE除了添加索引外还可以修改表结构，功能更全面；创建索引的命名规范建议以idx_开头。6.查看和删除索引（6分钟）讲解查看索引的方法：SHOWINDEXFROM表名;可以查看指定表的全部索引信息（包括索引名称、索引列、是否唯一、索引顺序等）。讲解删除索引的两种方法：（1）DROPINDEX索引名ON表名;示例：DROPINDEXidx_scoreONstuscore;（2）ALTERTABLE表名DROPINDEX索引名;示例：ALTERTABLEstuinfoDROPINDEXidx_classid;逐一演示查看和删除索引的操作，强调：主键索引不能直接删除；索引删除后查询不再利用该索引，但数据本身不受影响。7.课堂小结（3分钟）总结本课核心知识：ALTERVIEW修改视图定义、DROPVIEW删除视图（不影响基表）；索引类似书的目录，能加快查询但增加存储和写入开销；索引按功能分为普通、唯一、全文、空间索引，按字段分为单列和多列索引；创建索引有三种方式（建表时定义、CREATEINDEX、ALTERTABLEADDINDEX）；用SHOWINDEX查看索引，用DROPINDEX或ALTERTABLE删除索引。实践教学（2课时，80分钟）1.任务导入（5分钟）明确本节课的实践任务：完成任务书5.1~5.4，包括创建学生基本信息查询视图和学生成绩信息查询视图、修改视图增加字段、分析学生成绩表索引需求并创建合适索引、查询和删除索引。本节课将视图和索引的操作全部落地到教务系统数据库中。2.任务书5.1与5.2演示与实操（25分钟）演示创建学生基本信息查询视图：CREATEVIEWv_stu_infoASSELECTs.stuNo,s.stuName,s.stuSex,c.classNameFROMstuinfosINNERJOINclassinfocONs.classid=c.id;注意视图中不包含密码等敏感字段，体现视图的安全性作用。演示创建学生成绩信息查询视图：CREATEVIEWv_stu_scoreASSELECTs.stuName,c.className,sc.subject,sc.scoreFROMstuinfosINNERJOINclassinfocONs.classid=c.idINNERJOINstuscorescONs.stuNo=sc.stuNo;演示使用视图查询：SELECT*FROMv_stu_info;SELECT*FROMv_stu_score;与直接写连接查询的结果对比，体会视图简化查询的作用。学生自主完成任务书5.1和5.2，巡回指导，重点关注：视图的SELECT语句是否正确、视图命名是否符合规范。要求学生分别使用三种方式（DESC/SHOWTABLESTATUS/SHOWCREATEVIEW）查看视图，并截图保存。3.任务书5.3演示与实操（15分钟）演示修改视图增加字段：ALTERVIEWv_stu_infoASSELECTs.stuNo,s.stuName,s.stuSex,s.stuTel,c.classNameFROMstuinfosINNERJOINclassinfocONs.classid=c.id;在视图中增加联系方式字段。强调：ALTERVIEW需要写出完整的SELECT语句；修改视图后，通过SELECT*FROMv_stu_info验证新字段是否生效。学生自主完成任务书5.3，要求修改后查看视图结构验证结果，截图保存。4.任务书5.4演示与实操（20分钟）讲解任务书5.4的要求：分析学生成绩表stuscore的查询需求，找出经常作为查询条件的字段，创建合适的索引提高查询效率。分析：成绩表经常按stuNo（学号）和subject（科目）查询，这两个字段适合建立索引；score字段也常用于排序和范围查询，可以建立索引。演示创建索引：CREATEINDEXidx_stuNoONstuscore(stuNo);CREATEINDEXidx_subjectONstuscore(subject);演示创建多列索引：ALTERTABLEstuscoreADDINDEXidx_stu_s