项目8 MySQL索引与视图（课件）-《数据库MYSQL》同步教学（河北科技出版社）

数据库原理与应用技术MySQL 项目8 MySQL索引与视图 1 《数据库原理与应用技术-MySQL》 MySQL索引与视图 任务1 索引 任务2 视图 1 《数据库原理与应用技术-MySQL》 任务1 索引 子任务1 创建索引 子任务2 删除索引 1 《数据库原理与应用技术-MySQL》 子任务1 创建索引 索引也称作“键（key）”，是存储引擎用于快速查找记录的一种数据结构，用来快速查询数据库表中的特定记录。 根据表中列的特性，逻辑上将索引分为主键索引和辅助索引两类。 主键索引是由PRIMARY KEY 约束定义的一种特殊的唯一性索引，用于根据主键自身的唯一性标识每条记录，防止添加主键索引的字段值重复或为NULL。InnoDB存储引擎中数据的保存顺序与主键索引的顺序一致，这类索引也被称为聚族索引，一张表只能有一个聚族索引。 1 《数据库原理与应用技术-MySQL》 子任务1 创建索引 从性能来说，主键索引的性能相对来说最好，但查询优化更多的是对辅助索引进行建立和维护。MySQL常用的辅助索引如下： 索引名称 说明 普通索引 普通索引是MySQL中的基本索引，允许在定义索引的列中插入重复值和空值 唯一索引 唯一索引，索引列的值必须唯一，但允许有空值 全文索引 全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引可以在char、varchar或者text类型的列上创建 空间索引 空间索引是定义在空间数据类型上的索引，且索引字段不能为空 子任务1 创建索引 若数据表已经创建完成，可以使用CREATE INDEX语句创建索引，语法格式如下： CREATE[UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名（字段名[(长度)] [ASC|DESC]）; 子任务1 创建索引 说明： （1）索引名是索引的名称，在表中索引名称必须唯一。 （2）字段名表示索引创建的列名，可以是多列；长度表示在字段左边多少个字符上创建索引。 （3）UNIQUE表示唯一索引；FULLTEXT表示全文索引；SPATIAL表示空间索引 （4）ASC|DESC分别表示升序排列和降序排列。 （5）CREATE INDEX 语句不能创建主键索引。 子任务1 创建索引 在创建表时创建索引，可以使用CREATE TABLE语句，语法格式如下： CREATE TABLE 表名 （字段定义1， 字段定义2， … 字段定义N， [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY 索引名（字段名[(长度)] [ASC|DESC]）[VISIBLE|INVISIBLE] ); 子任务1 创建索引 说明： （1）INDEX和KEY表示索引关键字，只选其一即可。 （2）VISIBLE|INVISIBLE：标识索引的可见性。VISIBLE为可见，是缺省该项时的默认值。当创建索引时设置为INVISIBLE，则表示该索引为不可见索引，在数据查询时，优化器会忽略不可见索引。 子任务1 创建索引 使用ALTER TABLE语句也可以创建索引，语法格式如下： ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名（字段名[(长度)] [ASC|DESC]）; 说明： 关键字ADD表示向表中添加索引，其他参数与使用CREATE TABLE语句创建索引时相同。 子任务1 创建索引 【例8-1】在Navicat中，为student表Stuid列创建名为SY_Stuid的普通索引。 操作步骤如下： （1）打开Navicat，右键单击对象资源浏览器中表对象下的student表，选择“设计表”菜单选项，在打开的student表设计器中选择索引选项卡。 （2）在索引选项卡的“名”中输入SY_Stuid,“字段”中选择Stuid，“索引类型”选择NORMAL，因为InnoDB存储引擎只支持BTREE索引，所以“索引方法”选择BTREE。 （3）单击索引设计工具栏中的“保存”按钮。 子任务1 创建索引 【例8-2】在student表的Speciality列上创建名为SY_FT_Speciality的全文索引。 CREATE FULLTEXT INDEX SY_FT_Speciality ON student(Speciality); 说明：可以通过“设计表”页面，“索引”选项卡查看索引结果，如图所示。 子任务1 创建索引 【例8-3】创建Speciality表，并在Spename列上创建名为SY_Spename的唯一索引。 CREATE TABLE Speciality (Speid Int NOT NULL,Spename Varchar(50) NOT NULL,UNIQUE INDEX SY_Spename(Spename)); 说明：执行上述语句，成功在Spename列上创建唯一索引，如图所示。当向表中插入数据时，要保证Spename列上数据的唯一性。 子任务1 创建索引 【例8-4】在student表的Stusex、Speciality、Address三列上创建名为IX_student的复合索引。 ALTER TABLE student ADD INDEX IX_student (Stusex,Speciality,Address); 说明：执行结果如图所示。创建复合索引时，索引列按照从左至右的顺序进行排序，只有在查询条件中使用了这些列左边的列时，索引才会被使用，也就是索引的使用要遵从最左前缀原则。 子任务2 删除索引 创建索引之后，对数据的添加、修改、删除等操作会使索引页出现碎片，影响数据查询性能。为了提高查询效率，数据库管理员需要定期对索引进行相应的维护，其中包括删除和修改索引。 使用ALTER TABLE语句删除索引 ALTER TABLE 表名 DROP INDEX 索引名； 使用DROP INDEX语句删除索引 DROP INDEX 索引名 ON 表名; 1 《数据库原理与应用技术-MySQL》 子任务2 删除索引 【例8-5】删除Speciality表上名为SY_Spename的索引。 ALTER TABLE Speciality DROP INDEX SY_Spename; 说明：执行上述语句，并使用以下命令，查看Speciality表上的索引信息，结果如图所示。 SHOW INDEX FROM Speciality; 子任务2 删除索引 【例8-6】删除student表上名为SY_FT_Speciality的索引。 DROP INDEX SY_FT_Speciality ON student; 说明：删除表中的列时，会删除与该列相关的索引信息。若待删除的列为索引的组成部分，则该列也会从索引中删除。若组成索引的所有列都被删除，则整个索引将被删除。 任务2 视图 子任务1 认识视图 子任务2 创建视图 子任务3 查看视图 子任务4 使用视图 子任务5 修改视图 子任务6 删除视图 1 《数据库原理与应用技术-MySQL》 子任务1 认识视图 使用SQL语句查询数据时，查询结果会直接输出到客户端并且不会被保存。若需要多次查询相同的数据，可以将查询的定义封装成视图，以简化查询操作。 视图是虚表，存储的内容是SQL语句，其关联的数据在视图被使用时动态生成，数据随着基本表的变化而变化 ，视图就像一个窗口，用户只需要关心视图窗口提供的数据即可。 视图是从一个或多个表（或视图）中导出来的数据库对象，视图可以像表一样使用。但数据库中仅存储视图的定义，不存储视图的数据。 1 《数据库原理与应用技术-MySQL》 子任务2 创建视图 使用Navicat创建视图。使用Navicat等图形工具，在对象管理器中选中视图节点，单击“新建视图”按钮，打开“视图创建工具”窗口，然后在图形界面中添加相应的表并选择相关字段即可定义视图。 使用命令动词CREATE VIEW，直接在编辑器中编写视图定义代码。语法格式如下： CREATE VIEW 视图名 [(列1[,列2,…n])] AS SELECT 语句 [WITH[CASCADED|LOCAL]CHECK OPTION]； 子任务2 创建视图 说明： （1）视图名应符合标识符的命令规则。 （2）(列1[,列2,…n])用于指定视图中各列的名称，这里指定的列名与SELECT语句结果集的列一一对应。若省略列名列表，将直接使用SELECT语句中的列名称。 （3）SELECT语句用于定义视图，SELECT语句中可以使用多个表或其他视图。 （4） [WITH[CASCADED|LOCAL]CHECK OPTION]为可选参数，表示要保证在该视图的限权范围之内更新视图。其中CASCADED是默认值，表示更新视图时要满足所有相关视图和表的条件。LOCAL表示更新视图时要满足该视图本身定义的条件。 子任务2 创建视图 【例8-8】创建名为view_teacher的视图，用来显示教师年龄信息，列出工号、姓名和年龄。 CREATE VIEW view_teacher (工号,姓名,年龄) AS SELECT TeaID,Teaname,Age FROM teacher; 说明：视图的命名必须遵循命名规则，且不能与表同名，视图命名建议以“view_”开头。若想查看视图数据结果集，只需在对象浏览窗口中双击视图名即可。 子任务2 创建视图 【例8-9】创建学生成绩view_xscj视图，显示学生学号，姓名,课程号及分数信息，并按学号升序排列。 分析：视图是基本SELECT查询建立的对象，所以要建立名为view_xscj的视图，首先要明确该视图是基本什么样的查询来建立的？由于视图中显示的列分别来源于表SCORE和表student，因此该查询是两张表的连接查询。 结合前面的CREATE VIEW语法，在编辑窗口编写代码如下： CREATE VIEW view_xscj (学号,姓名,课程号,分数) AS SELECT SCORE.Stuid,Stuname,Cno,Grade FROM SCORE JOIN student ON SCORE.Stuid = student.Stuid ORDER BY SCORE.Stuid; 子任务3 查看视图 视图是基于数据表而建立的数据库对象，视图可以像表一样使用，但数据库中仅存储视图的定义不存储视图的数据。 视图的创建完成以后，可以通过三类语句来查看视图的基本信息、结构信息和定义文本。 1.通过SHOW TABLE STATUS语句查看视图的基本信息，语法格式为： SHOW TABLE STATUS LIKE ‘视图名’； 2.使用DESCREIBE或DESC语句查看视图的结构信息，语法格式为： DESCRIBE ‘视图名’/DESC ‘视图名’； 子任务3 查看视图 3.使用SHOW CREATE VIEW语句查看视图的定义文本，语法格式为： SHOW CREATE VIEW ‘视图名’； 子任务3 查看视图 【例8-10】查看视图view_xscj的结构信息。 DESCRIBE text_teacher; 说明：执行结果如图所示。 子任务3 查看视图 【例8-11】查询李桂花的所学课程的成绩信息。 SELECT 姓名,分数 FROM view_xscj WHERE 姓名="李桂花"; 说明：由于视图view_xscj中包括了所需要数据内容，因此数据可以直接从该视图中获取。结果如图所示。 子任务4 使用视图 视图定义好后，可以像使用基本表查询数据一样通过视图查询数据。 视图不仅可以查询数据，而且可以更新数据。 由于视图是一张虚表，因此可以使用INSERT或UPDATE语句通过视图插入或更新基本表中的数据，并可以使用DELETE语句通过视图删除基本表中的数据。 子任务4 使用视图 使用UPDATE语句通过更新视图来更新基本表。其语法格式如下： UPDATE 视图名 SET 列名1=值1，列名2=值2，…，列名n=值n WHERE 条件表达式； 各参数与UPDATE语句参数相同。虽然可以通过更新视图操作基本表的数据，但是限制较多。实际情况下，最好将视图仅作为查询数据的虚表，而不要通过视图更新数据。 1 《数据库原理与应用技术-MySQL》 子任务4 使用视图 使用INSERT语句更新视图向基本表插入数据，语法格式为： INSERT [INTO] 视图名[(列名列表)] VALUES(值列表1)[,(值列表2),…,(值列表n)]； 各参数与INSERT语句参数相同。 通过DELETE语句更新视图来删除基本表中的数据，语法格式为： DELETE FROM 视图名 [WHERE 条件表达式]； 各参数与DELETE语句参数相同。当视图所依赖的基本表有多个时，不能通过视图插入和删除数据。 1 《数据库原理与应用技术-MySQL》 子任务4 使用视图 【例8-12】通过视图view_teacher修改李博的年龄为30。 UPDATE view_teacher SET 年龄=30 WHERE 姓名="李博"; 说明：执行上述语句之后，可以使用SELECT语句查看教师“李博”的年龄。 SELECT Age FROM teacher WHERE Teaname="李博"; 执行结果如图所示。从结果可以看出，teacher表中李博的年龄已经修改为30，表示通过视图更新数据成功。 1 《数据库原理与应用技术-MySQL》 子任务4 使用视图 【例8-13】通过视图view_teacher，向teacher表中插入一行数据。 INSERT INTO view_teacher (工号,姓名,年龄) VALUES ("2004","杨柳",27); 说明：执行上述语句，并查询teacher表，结果如图所示。从结果集可以看到，教师杨柳的数据通过视图view_teacher成功插入teacher表中。 1 《数据库原理与应用技术-MySQL》 子任务4 使用视图 【例8-14】通过视图view_teacher，删除teacher表中姓名为杨柳的数据。 DELETE FROM view_teacher WHERE 姓名="杨柳"; 说明：执行上述语句，并查询teacher表，结果如图所示，表中不存在教师“杨柳”的数据，表明已通过视图成功删除基本表中的数据。 1 《数据库原理与应用技术-MySQL》 子任务5 修改视图 修改视图可以使用图形工具也可以使用SQL语句。 使用ALTER VIEW语句可以修改视图，语法格式如下： ALTER VIEW 视图名[(列1[,列2,…n])] AS SELECT 语句 [WITH[CASCADED|LOCAL]CHECK OPTION]； 语法说明与创建视图相同。 子任务5 修改视图 【例8-15】修改名为view_teacher的视图，在原有查询的基础上增加教师的性别。 ALTER VIEW view_teacher AS SELECT TeaID,Teaname,Age,Teasex FROM teacher; 说明：执行结果如图所示，表明视图修改成功。 1 《数据库原理与应用技术-MySQL》 子任务6 删除视图 当不再需要视图时，就需要将其从数据库中删除。可以选择使用Navicat等图形工具或直接使用SQL语句。 图形工具只需要在对象浏览器窗口中选中相应视图，单击鼠标右键选择“删除视图”选项即可。 在SQL语句中使用DROP VIEW语句删除视图，其语法格式如下： DROP VIEW [IF EXISTS] 视图名； 其中，IF EXISTS为可选参数，用于判断视图是否存在，如果存在则执行，不存在则不执行。 子任务6 删除视图 【例8-16】删除视图view_teacher。 DROP VIEW view_teacher; 说明：执行上述语句，删除视图。 执行视图定义查看语句： SHOW CREATE VIEW view_teacher; 执行语句SHOW CREATE VIEW view_teacher的目的是验证该视图是否真的被删除，从执行结果来看，结果显示不存在名为view_teacher的视图，表示视图删除成功。 1 《数据库原理与应用技术-MySQL》 $$