（中职）MySQL 8.0数据库管理与应用第4章教学课件（）.pptx

《（中职）MySQL 8.0数据库管理与应用第4章教学课件（）.pptx》由会员分享，可在线阅读，更多相关《（中职）MySQL 8.0数据库管理与应用第4章教学课件（）.pptx（19页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、（中职）MySQL 8.0 数据库管理与应用第4章教学课件（工信版）第4章 索引与视图4.1.1 索引的概念在关系型数据库中，索引是一种对表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或多列的值的集合与指向表中标识这些值的数据页的逻辑指针列表。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需要的内容。索引提供指向存储在表的指定列中的数据值的指针，并且根据指定的排序顺序对这些指针进行排序。查询数据库时可以通过索引找到特定值，然后跟随指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可以快速访问数据库表中的特定信息。当表中包含大量记录时，从表中查询数据通常有以

2、下两种方式。p 全表搜索，即从表取出所有的记录，并与查询条件逐一进行对比，然后返回满足条件的记录，这样做会消耗大量系统时间，并造成大量磁盘I/O操作。p 事先在表中基于一列或多表创建索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的行标识（相当于页码）快速找到表中对应的记录。使用MySQL数据库时也不是总要通过索引来优化查询。使用索引固然可以提高检索数据的速度，但创建和维护索引需要耗费一定的时间，而且存储索引也需要占用一定的磁盘空间。索引可以提高查询数据的速度，但也会影响插入数据的操作，这是因为向有索引的表中插入记录时，这些记录会按索引进行排序。4.1 索引概述4.1.2 索引

3、的作用在数据库中创建索引主要有以下作用。p 快速读取数据。p 保证数据记录的唯一性。p 实现表与表之间的参照完整性。p 在SELECT语句中使用GROUP BY、ORDER BY子句进行数据查询时，通过索引可以缩短分组和排序的时间。在MySQL中，对所有数据类型的列都可以创建索引，但使用时需要注意以下几点。p 一个表中最多可以有16个索引，最大索引长度为256字节。p 对于CHAR和VARCHAR类型的列可以索引列的前缀。这样，索引的速度更快，并且比索引整个列所占用的空间要少。p 可以在多个列上创建索引，索引列最多可以由15个列组成。p 只有当表使用InnoDB、BDB或MyISAM存储引擎时

4、，才能向具有NULL、TEXT或BLOB的列添加索引。4.1 索引概述4.1.3 索引的分类在MySQL中，大多数索引都是以B-树方式存储的，这种方式构建为多个节点的一棵树。顶部的节点为索引的起始点，称为根。每个节点中包含索引列的几个值，节点中的每个值指向另一个节点，或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的节点称为叶子页。叶子页本身也是相互连接的，一个叶子页包含一个指针，该指针指向下一组。索引中的节点是存储在文件中的。在MySQL中，一个表中的索引都保存在同一个索引文件中。如果在表添加或删除一行，或者更新表中的值，MySQL会自动地更新索引，因此索引始终与表的内容保持一致

5、。以B-树方式存储的索引有以下几种主要类型。p 普通索引（INDEX）：这是最基本的数据类型，它没有唯一性之类的限制，创建普通索引时使用关键字INDEX。p 唯一索引（UNIQUE）：这种索引与普通索引基本相同，所不同的是，索引列的值不能重复，即必须是唯一的。创建唯一索引时使用关键字UNIQUE。p 主键（PRIMARY KEY）：这也是一种唯一索引，但一个表只能有一个主键。主键通常是在创建表时设置，也可以通过修改表来设置主键。创建主键时使用关键字PRIMARY KEY。p 全文索引（FULLTEXT）：在定义全文索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能在T

6、EXT、CHAR或VARCHAR类型的列上创建，并且只能在MyISAM表中创建。全文索引可以在创建表或修改表时创建。p 空间索引（SPATIAL）：这是在MyISAM表的空间数据类型的字段上建立的索引。MySQL支持4种空间数据类型。创建空间索引时要使用SPATIAL关键字进行扩展。4.1 索引概述4.1.4 索引的设计原则p 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响数据增删改操作的性能，因为当表中数据更改时索引也会进行调整和更新。p 避免对经常更新的表设置过多的索引，并且索引中的列尽可能少。对于经常查询的字段应创建索引，但要避免添加不必要的字段。p 数据量小的表

7、最好不要索引，由于数据量小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。p 在条件表达式中经常用到的不同值较多的列上创建索引，在不同值很少的列上不要创建索引。p 当唯一性是某种数据本身的特征时应创建唯一索引，使用唯一索引可以保证定义的列的数据完整性，从而提高查询速度。p 在频繁进行排序或分组的列上创建索引，如果要排序的列有多个，可考虑在这些列上创建组合索引。4.1 索引概述4.2.1 创建表时创建索引使用CREATE TABLE语句创建表时可以直接包含索引的定义：CREATE TEMPORARY TABLE IF NOT EXISTS 表名(列定义,.|索引定义)表选项

8、索引定义：CONSTRAINT 字符串 PRIMARY KEY 索引类型(索引列,.)/*主键*/|INDEX|KEY 索引名 索引类型(索引列,.)/*普通索引*/|CONSTRAINT 字符串 UNIQUE INDEX|KEY/*唯一性索引*/索引名 索引类型(索引列,.)|FULLTEXT|SPATIAL INDEX|KEY 索引名(索引列,.)/*全文索引*/|CONSTRAINT 字符串 FOREIGN KEY/*外键*/索引名(列名,.)引用定义索引类型：USING BTREE|HASH索引列：列名(长度)ASC|DESC4.2 创建索引4.2.2 使用ALTER TABLE创建索

9、引对于已经存在的表，可以使用ALTER TABLE语句在表中指定的列上创建索引：ALTER TABLE 表名.|ADD INDEX|KEY 索引名/*添加普通索引*/索引类型(索引列,.)索引选项.|ADD CONSTRAINT 字符串 PRIMARY KEY/*添加主键*/索引类型(索引列,.)索引选项.|ADD CONSTRAINT 字符串 UNIQUE INDEX|KEY 索引名/*添加唯一性索引*/索引类型(索引列,.)索引选项.|ADD FULLTEXT INDEX|KEY 索引名/*添加全文索引*/(索引列,.)索引选项.|ADD SPATIAL INDEX|KEY 索引名/*添加

10、空间索引*/(索引列,.)索引选项.|ADD CONSTRAINT 字符串/*添加外键*/FOREIGN KEY 索引名(列名,.)引用定义|DISABLE|ENABLE KEYS4.2 创建索引4.2.3 使用CREATE INDEX创建索引使用CREATE INDEX语句可以在现有的表中创建索引：CREATE UNIQUE|FULLTEXT|SPATIAL INDEX 索引名索引类型ON 表名(索引列,.)索引类型：USING BTREE|HASH索引列：列名(长度)ASC|DESC关键字UNIQUE用于创建唯一性索引，FULLTEXT用于创建全文索引，SPATIAL用于创建空间索引。使用

11、CREATE INDEX语句不能创建主键。索引名表示索引的名称，在一个表中索引名必须是唯一的。索引类型为BTREE或HASH，只有部分存储引擎可以在创建索引时指定索引的类型。索引列可以是一个或多个，使用格式为(索引列1,索引列2,.）的列清单可以创建出一个多列索引。对于CHAR和VARCHAR列，只用一列的一部分就可以创建索引。创建索引时，使用列名(长度)格式可以对前缀编制索引。前缀包括每列值前面的部分字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。4.2 创建索引4.2.4 查看索引信息使用SHOW INDEX返回表中的索引信息：SHOW EXTENDED INDEX|IND

12、EXES|KEYSFROM|IN 表名FROM|IN 数据库名WHERE 条件其中表名指定索引所在的表，数据库名指定索引所在的数据库；“表名 FROM 数据库名”语法的替代方法是“数据库名.表名”。4.2 创建索引4.2.5 删除索引使用DROP INDEX语句从表中删除索引：DROP INDEX 索引名 ON 表名算法选项|锁定选项.算法选项:ALGORITHM=DEFAULT|INPLACE|COPY锁定选项:LOCK=DEFAULT|NONE|SHARED|EXCLUSIVE其中索引名指定要删除的索引，表名指定该索引所在的表。这个语句映射为一个ALTER TABLE，用于删除指定的索引。

13、4.2 创建索引4.3.1 视图的概念使用SELECT语句从数据库中查询数据时，这些语句并不存储在数据库中，为了重复使用这些语句通常是把它们存储在外部脚本文件中。当需要执行数据查询时，可以使用命令行客户端工具mysql或某个GUI客户端工具（如MySQL Workbench或Navicat for MySQL）提供的SQL编辑器来执行脚本文件。实际上，为了便于执行比较复杂的数据查询，一个更好的方案是将那些常用的SELECT语句以视图对象的形式存储在数据库中。所谓视图，就是从一个或多个表（或视图）中导出的虚拟表。视图与SELECT语句的关系十分密切，视图中的数据就是通过执行SELECT语句获取的

14、。与真实的表一样，视图也包含一系列带有名称的列和行数据，但这些列和行数据来自SELECT语句所引用的表，并且是在引用视图时动态生成的，而不是以数据集形式存在于数据库中。数据库中的数据存储在表中，创建视图时也只存储的视图的定义。视图定义中所引用的表也称为基表。当在数据库中创建视图以后，视图也可以像表一样用在SELECT语句的FROM子句中作为查询的数据源来使用，或者用在数据的更新和删除操作中。当通过视图进行数据操作时，MySQL将根据视图的定义去操作与视图相关联的基表。4.3 视图概述4.3.2 视图的作用视图可以用来集中、简化和自定义每个用户对数据库的不同访问，也可以用作安全机制。视图通常用在

15、以下3种场合。p 简化数据操作。使用选择查询检索数据时，如果查询中的数据分散在两个或多个表中，或者所用搜索条件比较复杂，往往要多次使用JOIN运算符来编写很长的SELECT语句。如果需要多次执行相同的数据检索任务，则可以考虑在这些常用查询的基础上创建视图，然后在SELECT语句的FROM子句中引用这些视图，而不必每次都输入相同的查询语句。p 自定义数据。视图允许用户以不同方式查看数据，即使在他们同时使用相同的数据时也是如此。这在具有许多不同目的和技术水平的用户共用同一数据库时尤其有用。例如，可以创建一个视图以仅检索由客户经理处理的客户数据，该视图可以根据使用它的客户经理的登录ID来决定检索哪些

16、数据。p 提高数据库的安全性。通常的作法是让用户通过视图来访问表中的特定列和行，而不对他们授予直接访问基表的权限。此外，可以针对不同的用户定义不同的视图，在用户视图上不包括那些机密数据列，从而提供对机密数据的保护。4.3 视图概述4.4.1 查看创建视图权限MySQL用户要在数据库中创建视图，则必须拥有CREATE VIEW权限和查询涉及的列的SELECT权限。这些权限信息包含在系统数据库mysql的user表中，可以使用下列SELECT语句进行查询。SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user=用户名;其中Sel

17、ect_priv列表示用户是否具有SELECT权限，如果该列的值为Y，则表示拥有SELECT权限，如果该列的值为N，则表示没有此权限。Create_view_priv列表示用户是否拥有CREATE VIEW权限，如果该列的值为Y，则表示拥有CREATE VIEW权限，如果该列的值为N，则表示没有此权限。mysql.user表示MySQL系统数据库mysql中的user表。用户名的值必须用引号括起来。4.4 创建视图4.4.2 CREATE VIEW语句使用CREATE VIEW语句创建一个新的视图：CREATEOR REPLACEALGORITHM=UNDEFINED|MERGE|TEMPTA

18、BLEDEFINER=用户|CURRENT_USER SQL SECURITY DEFINER|INVOKER VIEW 视图名(列名列表)AS SELECT语句WITH CASCADED|LOCAL CHECK OPTION如果给定了OR REPLACE子句，则能替换已有的同名视图。ALGORITHM子句规定MySQL算法，这会影响MySQL处理视图的方式。4.4 创建视图4.5.1 查看视图1.使用DESCRIBE语句查看视图使用DESCRIBE（DESC）语句可以查看表的结构，也可以查看视图的结构。这个语句返回的结果集包含以下6个列：Field（列名）、Type（类型）、Null（是否允

19、许为空）、Key（索引）、Default（默认值）以及Extra（附加属性，例如自增）。2.使用SHOW TABLE STATUS语句查看视图SHOW TABLE STATUS语句的作用类似于SHOW TABLES语句，可以用来显示数据库中表或视图的信息，但它提供了很多关于每个非临时表的信息，语法格式如下。SHOW TABLE STATUSFROM|IN 数据库名LIKE 模式|WHERE 条件LIKE子句（如果存在）指示要匹配的表或视图的名称。使用WHERE子句可以选择一般条件的行。3.使用SHOW CREATE VIEW语句查看视图SHOW CREATE VIEW 视图名4.5 视图操作4

20、.5.2 修改视图使用ALTER VIEW语句可以修改一个现有视图的定义：ALTERALGORITHM=UNDEFINED|MERGE|TEMPTABLEDEFINER=用户|CURRENT_USER SQL SECURITY DEFINER|INVOKER VIEW 视图名(列名列表)AS SELECT语句WITH CASCADED|LOCAL CHECK OPTIONALTER VIEW语句的语法格式与CREATE VIEW语句非常类似，这里不再进行重复说明。执行该语句需要拥有CREATE VIEW权限、针对视图的DROP权限以及SELECT语句中引用的每个列的某些权限。ALTER VIE

21、W语句仅允许具有SET_USER_ID或SUPER权限的定义者或用户使用。4.5 视图操作4.5.3 更新视图对视图的更新实际上就是对基表的更新。更新视图是指通过视图来插入（INSERT）、修改（UPDATE）和删除（DELETE）基表中的数据。由于视图只是一个虚拟表，其中不存储任何数据，因此通过视图进行更新操作时最终都要回归到基表来操作。更新视图时，只能更新权限范围内的数据。如果超出了这个权限范围，就不能更新。实际上，并非所有视图都可以更新。在下列几种情况下是不能更新视图的。p 视图中包含COUNT()、SUM()、MAX()和MIN()等函数。p 视图中包含UNION、UNION ALL、

22、DISTINCT、GROUP BY和HAVING等关键字。p 没有使用FROM子句的常量视图。p 视图定义中的SELECT语句包含子查询。p 由不可更新的视图导出的视图。p 创建视图时，ALGORITHM指定为TEMPTABLE算法。p 视图对应的基表存在没有默认值的列，而且该列未包含在视图中。4.5 视图操作4.5.4 删除视图视图是作为对象存储在数据库中的。如果不再需要某个视图，则可以从数据库中删除该视图，此时只是删除视图的定义，不会删除数据（因为数据是存储在表中的）。在MySQL中，可以使用DROP VIEW语句来删除一个或多个视图，语法格式如下。DROP VIEW IF EXISTS视图名,视图名.RESTRICT|CASCADE执行DROP VIEW语句时要求用户必须对每个视图都拥有DROP权限。如果参数列表中指定的视图不存在，则该语句将失败并显示一个错误，并且不进行任何更改。要避免出现这种情况，请在DROP VIEW中使用IF EXISTS。DROP VIEW语句从数字字典中删除指定的视图定义。如果从该视图导出了其他视图，则使用CASCADE进行级联删除，或者先显式删除导出的视图，然后删除该视图。删除基表时，由该表导出的所有视图都必须显式删除。4.5 视图操作