MySQL数据库原理与应用项目化教程（微课版） 课件 (含思政) 项目9-查询优化

项目九查询优化

查询数据有时要用连接查询、子查询等操作来实现，逻辑复杂、语句也很长，如果数据量大，查询速度也是一个问题。另外，如果把数据表的所有字段开放给用户查询也不够安全。

为了优化对“学生成绩管理”数据库的查询，本项目将通过创建与使用视图实现查询的简化并提高数据的安全性，通过创建与使用索引加快查询速度。知识目标：理解视图、索引的概念识记视图、索引相关操作语句的语法能力目标：能创建、使用及管理视图能创建和管理索引，并能查看索引的使用情况。任务9.1创建与使用视图任务9.2创建与使用索引

任务9.1创建与使用视图视图是虚拟表，也是存储的查询。视图可以方便用户、提高数据安全性、实现逻辑数据独立性。

为“学生成绩管理”数据库创建、管理视图，并使用视图查询或更新基本表的数据。【任务描述】创建与使用视图9.1【相关知识】创建与使用视图9.19.1.59.1.19.1.2

修改视图

视图的概念

创建视图9.1.3

查看视图

使用视图9.1.49.1.6

删除视图视图（VIEW）看上去是表，但它其实是虚拟表，因为它本身没有数据。相反地，前面用CREATETABLE语句创建的表是有数据的，为了和视图区分开，把真正存放数据的表叫做基本表。视图的数据来自于对一个或多个基本表（或视图）查询的结果,因此，视图又叫做存储的查询，定义视图的主体部分就是一条查询语句，打开视图看到的实际上就是执行这条查询语句所得到的结果集。视图主要有以下作用：（1）方便用户。（2）安全性。（3）逻辑数据独立性。视图的概念9.1.1创建视图用CREATEVIEW语句，语法格式如下：CREATE[ORREPLACE]VIEW视图名[(列1,列2,...)]ASSELECT语句[WITHCHECKOPTION]说明：ORREPLACE子句可选，作用是替换已有的同名视图。(列1,列2,...)用来声明视图中使用的列名，相当于给SELECT子句的各个数据项起别名。WITHCHECKOPTION子句用来限制通过该视图修改的记录要符合SELECT语句中指定的选择条件。创建视图9.1.21．查看视图的结构DESC[RIBE]视图名;2．查看视图的基本信息SHOWTABLESTATUS[LIKE‘视图名’];3．查看视图的创建信息SHOWCREATEVIEW视图名;查看视图9.1.31．查询数据视图创建后，可以通过视图查询基本表的数据。2．更新数据视图是虚拟表，本身没有数据，通过视图更新的是基本表的数据。不是所有的视图都可以更新数据，一般只能对“行列子集视图”进行数据更新，即视图是从单个基本表导出的某些行与列，并且保留了主键。如果创建视图的时候使用了WITHCHECKOPTION子句，那么通过视图更新的数据必须要满足视图定义时SELECT语句中WHERE子句后面的筛选条件，否则会报错。使用视图9.1.4视图创建后，可以用ALTERVIEW语句进行修改，语法格式如下：ALTERVIEW视图名[(列1,列2,...)]ASSELECT语句[WITHCHECKOPTION]其实，前面CREATEVIEW语句加上ORREPLACE也相当于实现修改已有视图。修改视图9.1.5视图创建后，如果不需要了，可以随时用DROPVIEW命令删除，一次可以删除多个视图。语法格式如下：DROPVIEW[IFEXISTS]视图名1[,视图名2]….;删除视图9.1.6【任务实施】1.创建视图v1，用来查看stuinfo表中所有女生的基本信息，并且强制以后通过该视图插入的必须是女生的记录。CREATEORREPLACEVIEWv1ASSELECT*FROMstuinfoWHEREstusex='女'WITHCHECKOPTION;创建与使用视图9.1图9.1创建视图v1【任务实施】2.创建视图v2，用来查看所有学生的学号及平均成绩。创建后查看视图V2的结构及创建信息。CREATEORREPLACEVIEWv2(stuno,avg_stuscore)ASSELECTstuno,avg(stuscore)FROMstumarksGROUPBYstuno;创建与使用视图9.1图9.2创建视图v2并查看【任务实施】3.创建视图v3，用来查看所有学生的学号、姓名、课程名及成绩。CREATEORREPLACEVIEWv3ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;创建与使用视图9.1图9.3显示已创建的所有视图【任务实施】4.查询v2视图中“S003”学生的平均成绩。SELECTavg_stuscoreFROMv2WHEREstuno=’S003’;创建与使用视图9.1图9.4使用视图v2查询表数据【任务实施】5.通过v1视图更新基本表stuinfo的数据（包括插入、修改、删除操作）。①插入记录INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','马六',’男’);创建与使用视图9.1图9.5通过视图v1往基本表插入一条男生记录失败【任务实施】INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','马六',’女’);创建与使用视图9.1图9.6通过视图v1往stuinfo表成功插入一条女生记录【任务实施】②修改记录UPDATEv1SETstubirthday='1998-12-25'WHEREstuno='S200';创建与使用视图9.1图9.7通过视图v1修改stuinfo表的数据【任务实施】③删除DELETEFROMv1WHEREstuno=’S200’;创建与使用视图9.1图9.8通过视图v1删除stuinfo表的记录【任务实施】6.修改视图v3，把列名stuno、stuname、cname、stuscore分别改为学号、姓名、课程名、成绩。ALTERVIEWv3(学号,姓名,课程名,成绩)ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;创建与使用视图9.1【任务实施】创建与使用视图9.1图9.9修改并查看视图v3的结构【任务实施】7.删除视图v1和v2。DROPVIEWv1,v2;创建与使用视图9.1图9.10删除视图v1、v2任务9.2创建与使用索引索引创建在数据表上，相当于图书的目录，可以提高数据查询速度。用SQL语句创建索引主要有三种方法。

为“学生成绩管理”数据库创建、管理索引，并查看在查询时是否使用了索引。【任务描述】创建与使用索引9.2【相关知识】创建与使用索引9.29.2.1

索引的概念

使用索引9.2.3

删除索引9.2.49.2.2

创建索引索引是一种单独的、物理的、对数据库表中一列或多列的值进行排序的存储结构，它是某个表中一列或若干列值的集合和相应地标识这些值所在数据页的逻辑指针清单。索引是创建在数据表上的，可以提高表中数据查询速度。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面，索引就存储在索引页面上。索引需要系统开销，不是越多越好。适合创建索引的列有：用于表间连接的外键，经常出现在WHERE、GROUP、ORDERBY子句中的字段等。在查询中很少被使用的字段以及重复值很多的字段则不适合建索引。索引的概念9.2.19.2思政小贴士【索引不是建的越多越好】物极必反，做人做事都要讲究“适可而止”。大千世界，万种诱惑，过度地追逐利益，往往会使人迷失方向。创建与使用索引MySQL8.0默认的存储引擎InnoDB支持以下几种常见的索引：（1）普通索引，它是最基本的索引类型，允许在创建索引的列中插入重复值或空值，只要不与约束冲突。（2）唯一索引，要求索引列的值必须唯一，可以是空值，使用UNIQUE关键字可以把索引设为唯一索引。（3）主键索引，在建立主键时自动创建，索引列的值不能重复也不能为空值。（4）单列索引，指创建索引的列是单列。（5）多列索引又叫组合索引，指创建索引的列是多列的组合，要注意的是只有在查询条件中使用了这些列中的第一列时，该索引才会被使用。一个表只能有一个主键索引，其他索引可以有多个。索引的概念9.2.1MySQL用语句创建索引有以下三种方法。1、创建表的时候创建索引语法格式如下：CREATETABLE表名(

字段名1数据类型1[列级完整性约束1][,字段名2数据类型2[列级完整性约束2]][,…][,表级完整性约束1][,…],［UNIQUE］INDEX|KEY[索引名](字段名[(长度)][ASC|DESC]));创建索引9.2.2上面创建表时指定索引的子句是：［UNIQUE］INDEX|KEY[索引名](字段名[(长度)][ASC|DESC])说明：UNIQUE可选，如有表示创建的是唯一索引。在MySQL中KEY和INDEX是一样的意思。索引名如果没有，默认是字段名。长度指的是使用列的前多少个字符创建索引。ASC|DESC可选，ASC表示升序，DESC表示降序，默认是升序。创建索引9.2.22、使用CREATEINDEX语句在已存在的表上创建索引语法格式如下：CREATE[UNIQUE]INDEX索引名ON表名(字段名[(长度)][ASC|DESC]);3、使用ALTERTABLE语句在已经存在的表上创建索引语法格式如下：ALTERTABLE表名ADD[UNIQUE]INDEX索引名(字段名[(长度)][ASC|DESC]);创建索引9.2.2查看查询时索引是否被使用，可用EXPLAIN关键字实现。

语法格式如下：EXPLAINSELECT语句

这条语句执行后，会出来一个表格，可以通过possible_keys和key这二列的值来判断是否使用了索引，这二个值的含义如下：possible_keys：可能使用的索引，可以有一个或多个，如果没有，值为NULL。key：显示实际使用的索引，如果没有使用索引，值为NULL。使用索引9.2.3删除索引有以下二种方法：1、使用ALTERTABLE语句语法格式如下：ALTERTABLE表名DROPINDEX索引名;2、使用DROPINDEX语句语法格式如下：DROPINDEX索引名ON表名;删除索引9.2.4【任务实施】1.创建一个t1(id,name,score)表，同时给id列创建普通索引。CREATETABLEt1(idINT,nameVARCHAR(20),scoreFLOAT,INDEX(id));创建与使用索引9.2

图9.11查看t1表上id字段的索引【任务实施】2.使用CREATEINDEX命令为stuinfo表的stuname列创建唯一索引uqidx。CREATEUNIQUEINDEXuqidxONstuinfo(stuname);创建与使用索引9.2

图9.12给stuinfo表的stuname列添加唯一索引【任务实施】3.使用ALTERTABLE命令为stuinfo表的stuno与stubirthday列建立多列索引，索引名为multidx，stuno列升序，stubirthday列降序ALTERTABLEstuinfoADDINDEXmultidx(stuno,stubirthdayDESC);创建与使用索引9.2

图