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

项目九查询优化E-mail:358542298@江苏海事职业技术学院

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

为了优化对“学生成绩管理”数据库的查询，本项目将通过创建与使用视图实现查询的简化并提高数据的安全性，通过创建与使用索引加快查询速度。知识目标：理解视图、索引的概念识记视图、索引相关操作语句的语法能力目标：能创建、使用及管理视图能创建和管理索引，并能查看索引的使用情况。任务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）是虚拟表，因为它本身没有数据。真正存放数据的表叫做基本表。视图的数据来自于对一个或多个基本表（或视图）查询的结果,因此，视图又叫做存储的查询，定义视图的主体部分就是一条查询语句，打开视图看到的就是执行这条查询语句所得到的结果集。视图主要有以下作用：（1）方便用户。（2）提高安全性。（3）实现逻辑数据独立性。视图的概念9.1.1语法格式：

CREATE[ORREPLACE]VIEW

视图名[(列1,列2,...)]

AS

SELECT语句[WITHCHECKOPTION]说明：ORREPLACE子句可选，作用是替换已有的同名视图。(列1,列2,...)用来声明视图中使用的列名，相当于给SELECT子句的各个数据项起别名。WITHCHECKOPTION子句用来限制通过该视图修改的记录要符合SELECT语句中指定的筛选条件。创建视图9.1.21．查看视图的结构

DESC[RIBE]视图名;2．查看视图的基本信息

SHOWTABLESTATUS[LIKE‘视图名’];3．查看视图的创建信息

SHOWCREATEVIEW

视图名;查看视图9.1.31．查询数据

通过视图查询基本表的数据。2．更新数据

通过视图更新的是基本表的数据。一般只能对“行列子集视图”进行数据更新，即视图是从单个基本表导出的某些行与列，并且保留了主键。

若创建视图时使用了WITHCHECKOPTION子句，那么通过视图更新的数据必须要满足视图定义时SELECT语句中的筛选条件，否则会报错。使用视图9.1.4

可以用ALTERVIEW语句修改视图。

语法格式如下：

ALTERVIEW视图名[(列1,列2,...)]

ASSELECT语句[WITHCHECKOPTION]修改视图9.1.5删除视图用DROPVIEW命令删除。语法格式如下：

DROPVIEW[IFEXISTS]视图名1[,视图名2]….;说明：一次可以删除多个视图。删除视图9.1.6【任务实施】准备工作：数据库studb三个数据表的数据如任务1.2中表1-3、表1-4、表1-5所示。9.1创建与使用视图【任务实施】1.创建视图v1，用来查看stuinfo表中所有女生的基本信息，并且强制以后通过该视图插入的必须是女生的记录。CREATEORREPLACEVIEWv1ASSELECT*FROMstuinfoWHEREstusex='女'WITHCHECKOPTION;创建与使用视图9.1【任务实施】2.创建视图v2，用来查看所有学生的学号及平均成绩。查看视图V2的结构及创建信息。CREATEORREPLACEVIEWv2(stuno,avg_stuscore)ASSELECTstuno,avg(stuscore)FROMstumarksGROUPBYstuno;创建与使用视图9.1【任务实施】3.创建视图v3，用来查看所有学生的学号、姓名、课程名及成绩。CREATEORREPLACEVIEWv3ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stunoJOINstucoursecONo=o;创建与使用视图9.1【任务实施】4.查询v2视图中“S003”学生的平均成绩。

SELECTavg_stuscoreFROMv2WHEREstuno=’S003’;创建与使用视图9.1【任务实施】5.通过v1视图更新基本表stuinfo的数据（包括插入、修改、删除操作）。①插入记录INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','马六','男’);创建与使用视图9.1【任务实施】INSERTINTOv1(stuno,stuname,stusex)VALUES('S200','马六','女');创建与使用视图9.1声明：学生信息纯属虚构记录插入基本表中【任务实施】②修改记录UPDATEv1SETstubirthday='1998-12-25'WHEREstuno='S200';创建与使用视图9.1声明：学生信息纯属虚构【任务实施】③删除记录DELETEFROMv1WHEREstuno=’S200’;创建与使用视图9.1声明：学生信息纯属虚构【任务实施】6.修改视图v3，把列名stuno、stuname、cname、stuscore分别改为学号、姓名、课程名、成绩。ALTERVIEWv3(学号,姓名,课程名,成绩)ASSELECTi.stuno,stuname,cname,stuscoreFROMstuinfoiJOINstumarksmONi.stuno=m.stuno

JOINstucoursecONo=o;创建与使用视图9.1【任务实施】7.删除视图v1和v2。DROPVIEWv1,v2;创建与使用视图9.1重要知识点:1.视图是虚拟表（本身没有数据），也是存储的查询2.创建视图：CREATEVIEW3.使用视图：查询数据，更新基本表的数据(行列子集~)4.删除视图：DROPVIEW9.1创建与使用视图任务9.2创建与使用索引索引可以提高数据查询速度。为“学生成绩管理”数据库创建、管理索引，并查看在查询时是否使用了索引。（具体任务见任务实施部分）【任务描述】创建与使用索引9.2【相关知识】创建与使用索引9.29.2.1

索引的概念

使用索引9.2.3

删除索引9.2.49.2.2

创建索引索引是一种单独的、物理的、对数据库表中一列或多列的值进行排序的存储结构，它是某个表中一列或若干列值的集合和相应地标识这些值所在数据页的逻辑指针清单。索引是创建在数据表上的，可以提高表中数据查询速度。表的存储由两部分组成，一部分是表的数据页面，另一部分是索引页面，索引就存储在索引页面上。索引需要系统开销，不是越多越好。适合创建索引的列有：用于表间连接的外键，经常出现在WHERE、GROUPBY、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

【任务实施】2.使用CREATEINDEX语句为stuinfo表的stuname列创建唯一索引uqidx。CREATEUNIQUEINDEXuqidxONstuinfo(stuname);创建与使用索引9.2

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

【任务实施】4.查看查询st