项目6 优化查询学生信息管理数据库

1、MySQL数据库原理及应用（微课版|第3版）-项目6 优化查询学生信息管理数据库MySQL数据库原理及应用（微课版|第3版）情景导入情景导入 上节课李老师给同学们布置了一道思考题：向学生基本信息表上节课李老师给同学们布置了一道思考题：向学生基本信息表student_new中插中插入入100万条记录。万条记录。 王宁按照题目要求和老师提供的王宁按照题目要求和老师提供的SQL脚本，花费近脚本，花费近1个小时的时间，将个小时的时间，将100万万条记录成功插入到了条记录成功插入到了student_new中。在完成数据的插入后，他尝试使用中。在完成数据的插入后，他尝试使用select语句查询学号语句查询

2、学号sno为为1000000的记录，发现用时的记录，发现用时26.83秒（不同机器、秒（不同机器、不同配置，时间稍有偏差）。这个响应时间太长了，让人无法忍受，可是王不同配置，时间稍有偏差）。这个响应时间太长了，让人无法忍受，可是王宁不知道怎样才能优化查询速度。宁不知道怎样才能优化查询速度。 李老师告诉王宁，为了提高学生信息管理系统中数据的安全性、李老师告诉王宁，为了提高学生信息管理系统中数据的安全性、完整性和查询速度，在应用系统的实际开发过程中，开发人员完整性和查询速度，在应用系统的实际开发过程中，开发人员一般会利用索引、视图等来提高系统响应速度和其他性能参数。一般会利用索引、视图等来提高系统

3、响应速度和其他性能参数。MySQL数据库原理及应用（微课版|第3版）? 使用索引优化查询性能使用索引优化查询性能? 使用视图优化查询性能使用视图优化查询性能主要内容主要内容MySQL数据库原理及应用（微课版|第3版）项目项目1 1 理解理解数据库数据库职业能力目标了解索引、视图的作用了解索引、视图的作用掌握索引、视图的创建及使用方法掌握索引、视图的创建及使用方法掌握索引、视图的修改及删除方法掌握索引、视图的修改及删除方法MySQL数据库原理及应用（微课版|第3版）任务任务6-1 使用索引优化查询性能使用索引优化查询性能【任务提出】【任务提出】为了提高查询速度，王宁需要在为了提高查询速度，王宁需

4、要在student_new表的表的sno字段上创建唯一索引字段上创建唯一索引id_sno，并通过查询，并通过查询sno为为1000000的记录，验证查询速度是否明显提的记录，验证查询速度是否明显提升。升。MySQL数据库原理及应用（微课版|第3版） 理解索引（一）索引概述（一）索引概述索引是一个单独的、物理的数据库结构，是某个表中一列或者若干列的集合以及相应的标识这些值所在的数据页的逻辑指针清单。索引依赖于表建立，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。在某种程度上，可以把数据库看作一本书，把索引看作书的目录

5、，通过目录查找书中的信息，显然比查找没有目录的书要方便、快捷。MySQL数据库原理及应用（微课版|第3版） 理解索引索引一旦创建，将由数据库自动管理和维护。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。索引可以提高数据的访问速度索引可以确保数据的唯一性。（一）索引概述（一）索引概述MySQL数据库原理及应用（微课版|第3版）普通索引和唯一索引单列索引和组合索引全文索引空间索引（二）索引的类型（二）索引的类型MySQL数据库原理及应用（微课版|第3版）索引并非越多越好避免对经常更新的表建立过多的索引数据量小的表最好不要使用索引在不同值少

6、的列上不要建立索引指定唯一索引是由某种数据本身的特征来决定为经常需要排序、分组和联合操作的字段建立索引（三）索引的设计原则（三）索引的设计原则MySQL数据库原理及应用（微课版|第3版）（四）使用（四）使用Navicat工具创建索引工具创建索引当给表创建UNIQUE约束时，MySQL会自动创建唯一索引。索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。可以在创建表时创建索引，或是给现存表创建索引。只有表的所有者才能给表创建索引。 创建索引时的注意事项MySQL数据库原理及应用（微课版|第3版）（1）在Navicat中，连接到MySQL服务器。展开【mysql80】|【gradem】|

7、【表】，在创建student表的窗口中选中【索引】选项卡。我们以给gradem数据库中的student表创建一个普通索引“index_sname”为例介绍创建索引的操作步骤：（四）使用（四）使用Navicat工具创建索引工具创建索引（2）分别在【索引】选项卡的【名】、【栏位】、【索引类型】及【索引方式】等列里输入索引名称、输入参与索引的字段、选择索引的类型及索引方式等信息，然后单击【保存】按钮，该索引创建成功。MySQL数据库原理及应用（微课版|第3版）（五）使用（五）使用SQL语句创建索引语句创建索引语法格式：CREATE TABLE ( , , ,UNIQUE|FULLTEXT|SPATI

8、AL 索引名(属性名(长度) ,) );1、使用CREATE TABLE语句在创建表时创建索引MySQL数据库原理及应用（微课版|第3版）参数说明如下。 UNIQUE|FULLTEXT|SPATIAL：是可选参数，三者选一，分别表示唯一索引、全文索引和空间索引。此参数不选，则默认为普通索引。 INDEX或KEY：为同义词，用来指定创建索引。 索引名：是指定索引的名称，为可选参数，若不指定，MySQL默认字段名为索引名。 属性名：指定索引对应的字段名称，该字段必须为表中定义好的字段。 长度：指索引的长度，必须是字符串类型才可以使用。1、使用CREATE TABLE语句在创建表时创建索引（五）使用

9、（五）使用SQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版）CREATE TABLE student(UNIQUE INDEX id_sno(sno) );【例6.1】 为student表sno列创建唯一索引id_sno。【例6.2】为sc表的sno和cno列创建普通索引id_sc。CREATE TABLE sc(INDEX id_sc(sno,cno);（五）使用（五）使用SQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版）语法格式：CREATE UNIQUE|FULLTEXT|SPATIAL INDEX ON (属性名(长度) ,);2、使用C

10、REATE INDEX语句在现存表中创建索引（五）使用（五）使用SQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版）CREATE INDEX id_birth ON student (sbirthday);【例6.3】 为student表sbirthday列创建一个普通索引id_birth。（五）使用（五）使用SQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版）语法格式：ALTER TABLE 表名 ADD UNIQUE|FULLTEXT|SPATIAL INDEX (属性名(长度) ,);3、使用ALTER TABLE语句创建索引（五）使用（五）使

11、用SQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版） 1、使用Navicat管理工具删除索引（1）在Navicat中，连接到mysql服务器。（2）展开【mysql80】|【gradem】|【表】，选中要创建索引的表，进入【设计表】窗口，在窗口中选中【索引】选项卡，单击工具栏上的【删除索引】按钮，或者用鼠标右键单击要删除的索引，在快捷菜单中执行【删除索引】命令即可。（六）删除索引（六）删除索引MySQL数据库原理及应用（微课版|第3版） 2、使用SQL语句删除索引使用SQL语言的DROP INDEX语句可删除索引，语句格式如下：DROP INDEX ON ;例如：DRO

12、P INDEX id_name ON student;（六）删除索引（六）删除索引MySQL数据库原理及应用（微课版|第3版）【任务实施】【任务实施】针对本任务提出中的问题，王宁使用SQL语句创建索引来解决，具体实现代码如下。（1）使用CREATE INDEX语句创建索引。 CREATE UNIQUE INDEX id_sno ON student_new(sno);（2）使用WHERE语句查询sno=1000000的记录，观察反应时间。 SELECT * FROM student_new WHERE sno=1000000;任务任务6-1 使用索引优化查询性能使用索引优化查询性能MySQL数

13、据库原理及应用（微课版|第3版）任务任务6-2 使用视图优化查询性能使用视图优化查询性能【任务提出】【任务提出】王宁已经能够熟练使用多表连接查询实现“查询20200101班选修“高等数学”课程且成绩在80-90分的学生姓名、学号、班级号及成绩”的题目。但是他发现频繁用到这段代码的时候需要重写代码、重新编译、重新执行，这种实现方式存在着代码复用性差、效率低等缺点。因此，王宁需要通过创建视图来解决这些问题。MySQL数据库原理及应用（微课版|第3版） 理解视图（一）视图概述（一）视图概述视图是从一个或者几个基本表或者视图中导出的虚拟表，是从现有基表中抽取若干子集组成用户的“专用表”，这种构造方式必

14、须使用SQL中的SELECT语句来实现。在定义一个视图时，只是把其定义存放在数据库中，并不直接存储视图对应的数据，直到用户使用视图时才去查找对应的数据。MySQL数据库原理及应用（微课版|第3版） 使用视图的优点简化对数据的操作自定义数据数据集中显示 导入和导出数据合并分割数据安全机制（一）视图概述（一）视图概述MySQL数据库原理及应用（微课版|第3版）v 例如：为“gradem”数据库创建一个视图View_stud，要求连接student表、sc表和course表，视图内容包括所有男生的sno、sname、ssex、cname和degree。（二）使用（二）使用Navicat工具创建工具创

15、建视图视图MySQL数据库原理及应用（微课版|第3版）操作步骤：（1）在Navicat中，连接到mysql服务器。（2）展开【mysql】|【gradem】|【视图】，右键单击该节点，选择【新建视图】命令。（3）打开【视图】对话框，选中【视图创建工具】选项卡，将所需的表student、sc和course，拖入到右上侧窗口中。（4）确定视图中的输出列。在此选择student表中的“sno”、“sname”和“ssex”，sc表中的“degree”，course表中的“cname”。（二）使用（二）使用Navicat工具创建工具创建视图视图（5）设置3个表的连接条件。（6）设置视图的条件。（7）单

16、击工具栏上的【保存】按钮，在弹出的【视图名】窗口中输入视图名称“View_stud”，单击【确定】按钮即可完成。MySQL数据库原理及应用（微课版|第3版）v 语法格式：语法格式：CREATE VIEW view_name (Column ,n) AS select_statement WITH CHECK OPTION;v 参数说明：参数说明：（1）view_name：定义视图名，其命名规则与标识符的相同，并且在一个数据库中要保证是唯一的，该参数不能省略。（2）Column：声明视图中使用的列名。（3）AS：说明视图要完成的操作。（4）select_statement：定义视图的SELECT

17、命令。（5）WITH CHECK OPTION：强制所有通过视图修改的数据满足select_statement语句中指定的选择条件。（三）使用（三）使用CREATE VIEW语句创建语句创建视图视图MySQL数据库原理及应用（微课版|第3版）【例6.4】 有条件的视图定义。定义视图v_student，查询所有选修数据库课程的学生的学号（sno）、姓名（sname）、课程名称（cname）和成绩（degree）。CREATE VIEW v_student AS SELECT A.sno,sname,cname,degree FROM student A,course B,sc C WHERE A

18、.sno=C.sno AND B.cno=C.cno AND cname=数据库;（三）使用（三）使用CREATE VIEW语句创建语句创建视图视图MySQL数据库原理及应用（微课版|第3版）v 1 1、使用视图进行数据检索、使用视图进行数据检索视图的查询总是转换为对它所依赖的基本表的等价查询。利用SQL的SELECT命令和Navicat都可以对视图进行查询，其使用方法与基本表的查询完全一样。v 2 2、通过视图修改数据、通过视图修改数据视图也可以使用INSERT命令插入行，当执行INSERT命令时，实际上是向视图所引用的基本表插入行。视图中的INSERT命令与在基本表中使用INSERT命令的

19、格式完全一样。（四）视图的使用（四）视图的使用MySQL数据库原理及应用（微课版|第3版）【例6.5】 利用V1_student视图向表student中插入一条数据。CREATE VIEW V1_studentAS SELECT sno,sname,saddress FROM student;【例6.6】 将例6.5中插入的数据删除。DELETE FROM V1_student WHERE sname=王小龙;INSERT INTO V1_student VALUES(2005020301,王小龙,山东省临沂市);（四）视图的使用（四）视图的使用MySQL数据库原理及应用（微课版|第3版）v

20、1、使用、使用Navicat修改视图修改视图（1）展开服务器，展开数据库。（2）单击【视图】节点，用鼠标右键单击要修改的视图名称，在快捷菜单中选择【设计视图】命令，进入视图设计窗口，用户可以在这个窗口中对视图进行修改。（五）视图的修改（五）视图的修改MySQL数据库原理及应用（微课版|第3版）v 2、使用、使用SQL语句修改视图语句修改视图语法格式：ALTER VIEW view_name (Column,n) AS select_statement WITH CHECK OPTION;v 【例例6.7】 修改修改例例6.5中的视图中的视图V1_student。ALTER VIEW V1_student AS SELECT sno,sname FROM student;（五）视图的修改（五）视图的修改MySQL数据库原理及应用（微课版|第3版）v 1、使用、使用Navicat删除视图删除视图（1）在当前数据库中展开【视图】节点。（2）用鼠标右键单击要删除的视图（如V1_student），在弹出的快捷菜单中选择【删除视图】命令；或单击要删除的视图，然后单击上方的【删除视图】按钮。（3）在弹出的【确认删除】对话框中单击【删除】按钮即可。（六）视图的删除（六）视图的删除MySQL数据库原理及应用（微课版|第3版）v 【例例6.8】 删除视图删除视图V1_student。DROP VI