教学课件项目6 优化查询学生信息管理数据库.pptx

《教学课件项目6 优化查询学生信息管理数据库.pptx》由会员分享，可在线阅读，更多相关《教学课件项目6 优化查询学生信息管理数据库.pptx（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、完成数据的插入后，他尝试使用中。在完成数据的插入后，他尝试使用selectselect语句查询学号语句查询学号snosno为为10000001000000的记录，发现用时的记录，发现用时26.8326.83秒（不同机器、秒（不同机器、不同配置，时间稍有偏差）。这个响应时间太长了，让人无法忍受，可是王不同配置，时间稍有偏差）。这个响应时间太长了，让人无法忍受，可是王宁不知道怎样才能优化查询速度。宁不知道怎样才能优化查询速度。李老师告诉王宁，为了提高学生信息管理系统中数据的安全性、李老师告诉王宁，为了提高学生信息管理系统中数据的安全性、完整性和查询速度，在应用系统的实际开发过程中，开发人员完整性和

3、查询速度，在应用系统的实际开发过程中，开发人员一般会利用索引、视图等来提高系统响应速度和其他性能参数。一般会利用索引、视图等来提高系统响应速度和其他性能参数。MySQL数据库原理及应用（微课版|第3版）?使用索引优化查询性能使用索引优化查询性能?使用视图优化查询性能使用视图优化查询性能主要内容主要内容MySQL数据库原理及应用（微课版|第3版）项目项目1 1 理解理解数据库数据库职业能力目标（含课程思政）了解索引、视图的作用了解索引、视图的作用掌握索引、视图的创建及使用方法掌握索引、视图的创建及使用方法掌握索引、视图的修改及删除方法掌握索引、视图的修改及删除方法MySQL数据库原理及应用（微课

4、版|第3版）任务任务6-1 6-1 使用索引优化查询性能使用索引优化查询性能【任务提出】【任务提出】为了提高查询速度，王宁需要在为了提高查询速度，王宁需要在student_newstudent_new表的表的snosno字段上创建唯一索引字段上创建唯一索引id_snoid_sno，并通过查询，并通过查询snosno为为10000001000000的记录，验证查询速度是否明显提的记录，验证查询速度是否明显提升。升。MySQL数据库原理及应用（微课版|第3版）理解索引（一）索引概述（一）索引概述索引是一个单独的、物理的数据库结构，是某个表中一列或者若干列的集合以及相应的标识这些值所在的数据页的逻辑

5、指针清单。索引依赖于表建立，提供了数据库中编排表中数据的内部方法。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面。索引就存放在索引页面上。在某种程度上，可以把数据库看作一本书，把索引看作书的目录，通过目录查找书中的信息，显然比查找没有目录的书要方便、快捷。MySQL数据库原理及应用（微课版|第3版）理解索引索引一旦创建，将由数据库自动管理和维护。在编写SQL查询语句时，具有索引的表与不具有索引的表没有任何区别，索引只是提供一种快速访问指定记录的方法。索引可以提高数据的访问速度索引可以确保数据的唯一性。（一）索引概述（一）索引概述MySQL数据库原理及应用（微课版|第3版）普通索

6、引和唯一索引单列索引和组合索引全文索引空间索引（二）索引的类型（二）索引的类型MySQL数据库原理及应用（微课版|第3版）索引并非越多越好避免对经常更新的表建立过多的索引数据量小的表最好不要使用索引在不同值少的列上不要建立索引指定唯一索引是由某种数据本身的特征来决定为经常需要排序、分组和联合操作的字段建立索引（三）索引的设计原则（三）索引的设计原则MySQL数据库原理及应用（微课版|第3版）（四）使用（四）使用NavicatNavicat工具创建索引工具创建索引当给表创建UNIQUE约束时，MySQL会自动创建唯一索引。索引的名称必须符合MySQL的命名规则，且必须是表中唯一的。可以在创建表时

7、创建索引，或是给现存表创建索引。只有表的所有者才能给表创建索引。创建索引时的注意事项MySQL数据库原理及应用（微课版|第3版）（1）在Navicat中，连接到MySQL服务器。展开【mysql80】|【gradem】|【表】，在创建student表的窗口中选中【索引】选项卡。我们以给gradem数据库中的student表创建一个普通索引“index_sname”为例介绍创建索引的操作步骤：（四）使用（四）使用NavicatNavicat工具创建索引工具创建索引（2）分别在【索引】选项卡的【名】、【栏位】、【索引类型】及【索引方式】等列里输入索引名称、输入参与索引的字段、选择索引的类型及索引方

8、式等信息，然后单击【保存】按钮，该索引创建成功。MySQL数据库原理及应用（微课版|第3版）（五）使用（五）使用SQLSQL语句创建索引语句创建索引语法格式：CREATE TABLE(,UNIQUE|FULLTEXT|SPATIAL 索引名(属性名(长度),);1、使用CREATE TABLE语句在创建表时创建索引MySQL数据库原理及应用（微课版|第3版）参数说明如下。UNIQUE|FULLTEXT|SPATIAL：是可选参数，三者选一，分别表示唯一索引、全文索引和空间索引。此参数不选，则默认为普通索引。INDEX或KEY：为同义词，用来指定创建索引。索引名：是指定索引的名称，为可选参数，若

9、不指定，MySQL默认字段名为索引名。属性名：指定索引对应的字段名称，该字段必须为表中定义好的字段。长度：指索引的长度，必须是字符串类型才可以使用。1、使用CREATE TABLE语句在创建表时创建索引（五）使用（五）使用SQLSQL语句创建索引语句创建索引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);（五）

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

11、据库原理及应用（微课版|第3版）语法格式：ALTER TABLE 表名 ADD UNIQUE|FULLTEXT|SPATIAL INDEX (属性名(长度),);3、使用ALTER TABLE语句创建索引（五）使用（五）使用SQLSQL语句创建索引语句创建索引MySQL数据库原理及应用（微课版|第3版）1、使用Navicat管理工具删除索引（1）在Navicat中，连接到mysql服务器。（2）展开【mysql80】|【gradem】|【表】，选中要创建索引的表，进入【设计表】窗口，在窗口中选中【索引】选项卡，单击工具栏上的【删除索引】按钮，或者用鼠标右键单击要删除的索引，在快捷菜单中执行【删

12、除索引】命令即可。（六）删除索引（六）删除索引MySQL数据库原理及应用（微课版|第3版）2、使用SQL语句删除索引使用SQL语言的DROP INDEX语句可删除索引，语句格式如下：DROP INDEX ON;例如：DROP INDEX id_name ON student;（六）删除索引（六）删除索引MySQL数据库原理及应用（微课版|第3版）【任务实施】【任务实施】针对本任务提出中的问题，王宁使用SQL语句创建索引来解决，具体实现代码如下。（1）使用CREATE INDEX语句创建索引。CREATE UNIQUE INDEX id_sno ON student_new(sno);（2）使用

13、WHERE语句查询sno=1000000的记录，观察反应时间。SELECT*FROM student_new WHERE sno=1000000;任务任务6-1 6-1 使用索引优化查询性能使用索引优化查询性能MySQL数据库原理及应用（微课版|第3版）v在实际工作中，随着公司业务的高速发展，数据库中单个表的在实际工作中，随着公司业务的高速发展，数据库中单个表的数据规模可能达到几百万甚至几千万条记录。在这种情况下，数据规模可能达到几百万甚至几千万条记录。在这种情况下，往往会导致业务系统的响应时间过长，引起用户的不满往往会导致业务系统的响应时间过长，引起用户的不满。思政小贴士思政小贴士v为了缩短

14、响应时间，数据库管理员要想尽各种办法优为了缩短响应时间，数据库管理员要想尽各种办法优化性能，首选方案是为合适的字段创建合适的索引。化性能，首选方案是为合适的字段创建合适的索引。建立索引后，如果依旧不能有效解决问题，再尝试其建立索引后，如果依旧不能有效解决问题，再尝试其他方法进行优化。所以，在数据库开发过程中，我们他方法进行优化。所以，在数据库开发过程中，我们往往会根据需要，逐步优化表结构，从而使性能达到往往会根据需要，逐步优化表结构，从而使性能达到最优。这就需要我们最优。这就需要我们。MySQL数据库原理及应用（微课版|第3版）任务任务6-2 6-2 使用视图优化查询性能使用视图优化查询性能【

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

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

17、Navicat中，连接到mysql服务器。（2）展开【mysql】|【gradem】|【视图】，右键单击该节点，选择【新建视图】命令。（3）打开【视图】对话框，选中【视图创建工具】选项卡，将所需的表student、sc和course，拖入到右上侧窗口中。（4）确定视图中的输出列。在此选择student表中的“sno”、“sname”和“ssex”，sc表中的“degree”，course表中的“cname”。（二）使用（二）使用NavicatNavicat工具创建工具创建视图视图（5）设置3个表的连接条件。（6）设置视图的条件。（7）单击工具栏上的【保存】按钮，在弹出的【视图名】窗口中输入视图

18、名称“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命令。（5）WITH CHECK OPTION：强制所有通过视图修改

19、的数据满足select_statement语句中指定的选择条件。（三）使用（三）使用CREATE VIEWCREATE 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.sno=C.sno AND B.cno=C.c

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

21、（四）视图的使用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版）v1 1、使用、使用NavicatNa

22、vicat修改视图修改视图（1）展开服务器，展开数据库。（2）单击【视图】节点，用鼠标右键单击要修改的视图名称，在快捷菜单中选择【设计视图】命令，进入视图设计窗口，用户可以在这个窗口中对视图进行修改。（五）视图的修改（五）视图的修改MySQL数据库原理及应用（微课版|第3版）v2 2、使用、使用SQLSQL语句修改视图语句修改视图语法格式：ALTER VIEW view_name(Column,n)AS select_statement WITH CHECK OPTION;v【例例6.76.7】修改修改例例6.56.5中的视图中的视图V1_studentV1_student。ALTER VIE

23、W V1_student AS SELECT sno,sname FROM student;（五）视图的修改（五）视图的修改MySQL数据库原理及应用（微课版|第3版）v1 1、使用、使用NavicatNavicat删除视图删除视图（1）在当前数据库中展开【视图】节点。（2）用鼠标右键单击要删除的视图（如V1_student），在弹出的快捷菜单中选择【删除视图】命令；或单击要删除的视图，然后单击上方的【删除视图】按钮。（3）在弹出的【确认删除】对话框中单击【删除】按钮即可。（六）视图的删除（六）视图的删除MySQL数据库原理及应用（微课版|第3版）v【例例6.86.8】删除视图删除视图V1_s

24、tudentV1_student。DROP VIEW V1_student;v2 2、使用、使用SQLSQL语句删除视图语句删除视图语法格式：DROP VIEW view,n;（六）视图的删除（六）视图的删除MySQL数据库原理及应用（微课版|第3版）【任务实施】【任务实施】（1 1）使用）使用CREATE VIEWCREATE VIEW语句创建视图。语句创建视图。CREATE VIEW view_stuandscCREATE VIEW view_stuandsc ASAS SELECT sname,a.sno,classno,degree SELECT sname,a.sno,classno

25、,degree FROM student a,sc b,course cFROM student a,sc b,course c WHERE a.sno=b.sno AND o=oWHERE a.sno=b.sno AND o=o AND cname=AND cname=高等数学高等数学 AND degree BETWEEN 80 AND 90;AND degree BETWEEN 80 AND 90;（2 2）使用）使用SELECTSELECT语句查询语句查询view_stuandscview_stuandsc。SELECT*FROM view_stuandsc;SELECT*FROM vi

26、ew_stuandsc;任务任务6-2 6-2 使用视图优化查询性能使用视图优化查询性能针对本任务提出中的问题，王宁使用针对本任务提出中的问题，王宁使用SQLSQL语句创建视图来解决，具体实现代码语句创建视图来解决，具体实现代码如下。如下。MySQL数据库原理及应用（微课版|第3版）v当删除不存在的表、视图、存储过程、函数、触发器时，会提当删除不存在的表、视图、存储过程、函数、触发器时，会提示示“Unknown X X X”,“Unknown X X X”,根据提示解决即可。但是根据提示解决即可。但是SQLSQL经常会被嵌经常会被嵌入到高级程序设计语言中，如入到高级程序设计语言中，如C C、J

27、avaJava、PythonPython语言。这样，我语言。这样，我们就必须保证每次编译和运行都能够正常进行，因此需要加入们就必须保证每次编译和运行都能够正常进行，因此需要加入IF EXISTSIF EXISTS子句来提高程序的健壮性。子句来提高程序的健壮性。思政小贴士思政小贴士v随着学习的深入和知识的积累，我们还应该随着学习的深入和知识的积累，我们还应该。MySQL数据库原理及应用（微课版|第3版）项目总结项目总结本项目带领大家学习了索引和视图的基本操作，包括索引的概念、类型、设计原则及创建与删除操作，以及视图的概念、优点、创建、使用、修改与删除操作。要求大家加强复习，增进理解。把各个知识点学会、领悟，能举一反三。v主要内容主要内容重点要求大家掌握索引和视图的创建与使用。v重难点要求重难点要求MySQL数据库原理及应用（微课版|第3版）志存高远志存高远 自强不息自强不息