【数据库——MySQL】（10）视图和索引

1. 视图

1.1 创建视图

基础语法：

CREATE [OR REPLACE] VIEW 视图名[(列名表)]ASSELECT语句[WITH CHECK OPTION]

说明：

1. 在默认情况下，将在当前数据库创建新视图，否则采用“数据库名.视图名”的形式。视图名必须遵循标识符命名规则，不能与已有的表或视图同名。如果创建视图前不确定是否已有同名视图存在，可加上“OR REPLACE”替换可能存在的同名视图。若视图列名与 SELECT 语句输出项名相同，则可以省略列名表。
2. SELECT 语句是用来创建视图的查询语句，可在其中查询已经存在的一个或以上的表或视图
3. WITH CHECK OPTION ：指出在可更新视图上所进行的修改都要符合 SELECT 语句所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。
4. 如果与视图相关联的表或视图被删除，则该视图将不能再使用。

【例】创建视图显示所有逾期未归还的借阅信息（包括借阅人姓名，借阅人类别，书名，借出日期，应归还日期，逾期时长）。

这里还是用的 library 数据库，数据库的获取见前面的文章，除非文章例子特别说明使用其它的数据库之外，默认都是使用 library 数据库，后续将不再对此进行说明~

use library;
# 删除视图
drop view if exists Overdue_view;
# 创建视图
CREATE VIEW `library`.`Overdue_view` AS SELECTborrower.`name` AS 借阅人姓名, borrower.category AS 借阅人类别, book.`name` AS 书名, information.leadDate AS 借出日期, information.returnDate AS 应归还日期,TO_DAYS(NOW()) - TO_DAYS(information.returnDate) AS '逾期时长(天)'
FROMbookINNER JOINinformationON book.id = information.bookidINNER JOINborrowerON information.borrowerid = borrower.id
WHERE returnDateReality IS NULL AND TO_DAYS(NOW()) - TO_DAYS(information.returnDate) > 0;

视图如下：

说明：

1. 因为视图采用 SELECT 输出项作为列名，所以视图名后没有定义列名。
2. 对定义的视图查询所有记录和输出所有项，效果与执行定义的查询语句效果相同。输出项名称与定义该视图时 SELECT 输出项名称相同。

1.2 查询视图

【例】查询逾期未归还天数超过 220 天的借阅人信息。

SELECT id, `name`, category, tel, email FROM borrowerWHERE `name` IN (SELECT `借阅人姓名` FROM overdue_viewWHERE `逾期时长(天)` > 220);

由于逾期时长是当前时间减去借书时间得到的，所以大家运行的结果可能会比我的结果多出几条记录！

视图的插入、修改以及删除和表的操作类似，基本上只是将关键字 DATABASE 改为关键字 VIEW，关键字不区分大小写，但是建议关键字都使用大写，便于代码的查阅！

2. 索引

索引相当于书籍的目录，方便 MySQL 快速查找数据，从而提高效率！但是注意最好在数据量大的时候才建立索引，而要经常修改的表则不建议建立索引！

2.1 索引的分类

MySQL 8.0 支持的主要索引有如下几种。

1. 普通索引（INDEX）。这是最基本的索引类型，索引列不一定唯一和非空，可以在任何数据类型的列上创建。
2. 唯一性索引（UNIQUE）。一个表索引列的所有值不能重复，即必须是唯一的。
3. 主键索引（PRIMARY KEY）。主键索引是一种特殊的唯一性非空索引，一般在创建表时通过“PRIMARY KEY”关键字指定，每个表只能有一个主键索引。
4. 多列索引。可在多个列上创建的索引，它同时关联表的多个列，但查询时只有在条件中使用多列索引的第一列，多列索引才会真正发挥作用。
5. 全文索引（FULLTEXT）。在定义这种索引的列上支持值的全文检索，允许在这些索引列中插入重复值和空值。全文索引只能在 char、varchar 或 text 类型的列上创建。
6. 空间索引（SPATIAL）。这是 MySQL 支持的专门针对空间数据类型列的索引，空间类型列的值不能为空。

2.2 索引的建立

我建议大家用可视化界面，如 Navicat 来创建索引（方便），因此代码创建语句这里就不展示了。

可以在左侧的表上右键选择设计表，即可在索引栏里面创建、修改、删除索引了~

JSON 数据索引 和 空间数据索引这里不进行介绍，感兴趣的同学可以阅读相关书籍。

参考书籍

《MySQL实用教程（第4版）》

上一篇文章：【数据库——MySQL】（9）函数、查询练习及讲解