5.3 优化查询数据库

MySQL数据库项目五：图书管理数据库MySQL数据库5.3视图和索引任务视图12索引优化查询数据库创建视图视图什么是视图

视图是一条SELECT语句返回的结果集，这个结果集可以从一张表中查询出来，也可以从多张表中查询出来。SELECT语句使用的表可以当成基本表，而结果集则构成一张虚拟表。

虚拟表也是表，可以进行增、删、改、查操作，但是有条件限制，它可以存放SELECT语句查询的结果，但是不存放具体数据，基本表里的数据变化会影响视图虚拟表查询的结果。系统角度用户角度视图MySQL数据库网络课程

●性能：数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。●修改限制：当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。视图的优点简化用户的数据查询和处理屏蔽数据库的复杂性简化用户权限的管理便于数据共享便于重新组织数据视图的缺点视图MySQL数据库网络课程

创建视图

创建视图使用关键字VIEW来标识。CREATE[ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]

VIEW视图名[(属性清单)]

ASSELECT语句

[WITH[CASCADED|LOCAL]CHECKOPTION];例如：CREATEVIEWv_t1ASSELECT*FROM图书表;视图MySQL数据库网络课程CREATEVIEWv_t1ASSELECT*FROM图书表;MySQL数据库网络课程MySQL数据库网络课程CREATEVIEW`librarydb`.`v_t2`

ASSELECT书名,位置FROM图书表JOIN库存表ON图书表.书号=库存表.书号；视图MySQL数据库网络课程MySQL数据库网络课程视图视图的概念和优缺点视图的创建创建视图使用关键字VIEW来标识。CREATE

[ALGORITHM]={UNDEFINED|MERGE|TEMPTABLE}]

VIEW视图名[(属性清单)]

AS

SELECT语句

[WITH[CASCADED|LOCAL]CHECKOPTION];例如：CREATEVIEWv_t1ASSELECT*FROM图书表;视图优化查询数据库操作视图视图操作视图（1）聚合函数；（2）DISTINCT关键字；（3）GROUPBY子句；（4）ORDERBY子句；（5）HAVING子句；（6）UNION运算符；（7）位于选择列表中的子查询；（8）FROM子句中包含多个表；（9）SELECT语句中引用了不可更新视图；通过视图更新基本表数据，也就是在视图中使用INSERT、UPDATE或DELETE等语句操作基本表。操作视图必须保证视图是可更新视图，即视图中的行和基本表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：视图通过视图插入数据限制注意：当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。插入的数据项应该包含基本表中所有不能为空的列。当创建视图时使用了WITHCHECKOPTION，插入的记录必须满足WHERE子句指定的条件。视图CREATEVIEWv_t3ASSELECT书名,类别,数量FROMv_t1GROUPBY书名;创建视图视图MySQL数据库网络课程CREATEORREPLACEVIEWv_t4ASSELECT*FROMv_t3WHERE类别='计算机';从视图中筛选数据MySQL数据库网络课程视图通过视图插入数据INSERTINTO图书表VALUES('A0121','C语言程序设计','计算机','张琪','理工大学出版社','30','6','2016-01-15','null');使用INSERT语句通过视图向基本表插入数据【例】分别通过表和视图插入图书的信息记录。通过表插入图书的信息记录：视图通过视图插入数据INSERTINTOv_t1VALUES('A0122','Office应用实例','计算机','河北','人文大学出版社','38','12','2018-06-15','null');【例】分别通过表和视图插入图书的信息记录。通过视图插入图书的信息记录：使用INSERT语句通过视图向基本表插入数据视图通过视图修改数据当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。使用UPDATE语句可以通过视图修改基本表的数据UPDATEv_t1SET数量=数量-2;【例】将v_t1视图中所有图书数量减少2本。该语句实际上是将v_t1视图所依赖的基本表“图书表”中所有图书的数量也都减少了2本。视图视图修改数据举例【例】将v_t1视图中书号为B0130的书名改为“唐诗宋词”，作者为赵阳的单价改为20。改书名：

UPDATEv_t1SET书名='唐诗宋词'WHERE书号='B0130’;改单价：

UPDATEv_t1SET单价=20WHERE作者=’赵阳’;删除视图1.使用SQL语句删除视图DROPVIEW[IFEXISTS]视图名1[,视图名2]...语法格式：使用DROPVIEW一次可删除多个视图。例如：DROPVIEWbook,sell;一次删除视图book和sell。DROPVIEWv_t5;使用DROPVIEW可删除一个视图。例如：视图MySQL数据库网络课程一、新增、修改、删除数据新增数据：insertinto视图名[(字段列表)]values(值列表);

注：1.多表视图不能新增数据。

2.单表视图拥有基表所有不能为空或没有默认值的字段才能新增数据。使用UPDATE语句可以通过视图修改基本表的数据使用ALERT语句可以对已有视图的定义进行修改。删除数据：deletefrom视图名[where条件];注：多表视图不能删除数据。二、

删除视图DROPVIEW[IFEXISTS]视图名1[,视图名2]...视图优化查询数据库索引1索引的分类2创建索引3删除索引索引索引及其应用索引——是一种提高查找速度的机制索引用来快速地寻找那些具有特定值的记录，如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。索引索引的分类01OPTION02OPTION03OPTION04OPTION普通索引（INDEX）这是最基本的索引类型，它没有唯一性之类的限制。创建普通索引的关键字是INDEX。唯一性索引（UNIQUE）这种索引和前面的普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须是唯一的。创建唯一性索引的关键字是UNIQUE。主键（PRIMARYKEY）主键是一种唯一性索引，它必须指定为“PRIMARYKEY”。主键一般在创建表的时候指定，也可以通过修改表的方式加入主键。但是每个表只能有一个主键。全文索引（FULLTEXT）MySQL支持全文检索和全文索引。全文索引的索引类型为FULLTEXT。全文索引只能在VARCHAR或TEXT类型的列上创建，并且只能在MyISAM表中创建。索引创建索引1.使用CREATEINDEX语句CREATE[UNIQUE|FULLTEXT]INDEX索引名ON表名(列名[(长度)][ASC|DESC],...)使用CREATEINDEX语句可以在一个已有表上创建索引，一个表可以创建多个索引。语法格式：说明：索引名：索引的名称，索引名在一个表中名称必须是唯一的。列名：表示创建索引的列名。长度：表示使用列的前多少个字符创建索引。使用列的一部分创建索引可以使索引文件大大减小，从而节省磁盘空间。BLOB或TEXT列必须用前缀索引。UNIQUE：表示创建的是唯一性索引FULLTEXT：表示创建全文索引；CREATEINDEX语句并不能创建主键。索引创建索引举例可以在一个索引的定义中包含多个列，中间用逗号隔开，但是它们要属于同一个表。这样的索引叫做复合索引。

CREATEINDEXI_bmON读者表(单位DESC);【例】对借阅表中的条码和读者编号列创建复合索引I_tr。

CREATEINDEXI_trON借阅表(条码,读者编号);【例】对读者表中的单位列按降序创建普道索引I_bm。索引MySQL数据库网络课程索引创建索引举例

CREATEUNIQUEINDEXU_wzON库存表(位置);

【例】对库存表中的位置列创建唯一性索引。索引ALTERTABLE语句创建索引2.使用ALTERTABLE语句使用ALTERTABLE语句修改表，其中也包括向表中添加索引。语法格式如下：

ALTERTABLE表名

ADDINDEX[索引名](列名,...) /*添加索引*/|ADDPRIMARYKEY[索引方式](列名,...) /*添加主键*/|ADDUNIQUE[索引名](列名,...) /*添加唯一性索引*/|ADDFULLTEXT[索引名](列名,...) /*添加全文索引*/索引ALTERTABLE创建索引举例【例】对图书表中的书名列添加一个唯一性索引，对作者和出版社列添加一个复合索引。

altertable图书表adduniqueindex(书名),addindex(作者,出版社);【例】对读者类型表中的类别号列创建主健索引。altertable读者类型表addprimarykey(类别号);这个例子中，包括复合索引，说明MySQL可以同时创建多个索引。如果想要查看表中创建的索引的情况，可以使用SHOWINDEX语句，

例如：SHOWINDEXFROM图书表;索引索引MySQL数据库网络课程SHOWINDEXFROM图书表;索引MySQL数据库网络课程创建表时创建索引在前面两种情况下，索引都是在表创建之后创建的。索引也可以在创建表时一起创建。在创建表的CREATETABLE语句中可以包含索引的定义。语法格式：CREATETABLE表名(列名,...|[索引项])其中，索引项语法格式如下：PRIMARYKEY(列名,...) /*主键*/|{INDEX|KEY}[索引名](列名,...) /*索引*/|UNIQUE[INDEX][索引名](列名,...) /*唯一性索引*/|[FULLTEXT][INDEX][索引名](列名,...) /*全文索引*/说明：KEY通常是INDEX的同义词。在定义列选项的时候，也可以将某列定义为PRIMARYKEY，但是当主键是由多个列组成的多列索引时，定义列时无法定义此主键，必须在语句最后加上一个PRIMARYKEY列名，…）子句。索引创建表时创建索引举例createtablecpk(产品编号char(6)notnull,产品名称char(20)notnull,单价float(5,2),库存量int,primarykey(产品编号),index(库存量,单价)); 【例】创建表cpk(产品编号，产品名称，单价，库存量），并将产品编号设置为主键，在库存量和单价列上创建复合索引。索引索引MySQL数据库网络课程删除索引1.使用DROPINDEX语句删除索引DROPINDEX索引名ON表名语法格式：【例】删除库存表上索引名为“U_wz“的索引。DROPINDEXU_wzON库存表;索引ALTERTABLE删除索引2.使用ALTERTABLE语句删除索引ALTER[IGNORE]TABLE表名

|DROPPRIMARYKEY /*删除主键*/|DROPINDEX索引名 /*删除索引*/语法格式：【例】删除读者表上的主键和I_bm索引。ALTERTABLE读者表DROPPRIMARYKEY,DROPINDEXI_bm;如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则