MySQL（五）——视图与索引

一、视图

视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

数据库中只存放了视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中。使用视图查询数据时，数据库会从真实表中取出对应的数据。因此，视图中的数据是依赖于真实表中的数据的。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。

1、视图与数据库表的区别

• 视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的。
• 存储在数据库中的查询操作 SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据。
• 视图没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中的。
• 视图是数据的窗口，而表是内容。表是实际数据的存放单位，而视图只是以不同的显示方式展示数据，其数据来源还是实际表。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些 SQL 语句的集合。从安全的角度来看，视图的数据安全性更高，使用视图的用户不接触数据表，不知道表结构。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。

2、创建视图

语法格式：

CREATE VIEW 视图名 AS SELECT语句

语法格式说明：

视图名：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
SELECT语句：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

• 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
• SELECT 语句不能引用系统或用户变量。 SELECT 语句不能包含 FROM 子句中的子查询。
• SELECT 语句不能引用预处理语句参数。

eg：
创建基于单表的视图：

//在 user_test 表上创建一个名为 view_user_test 的视图
CREATE VIEW view_user_test AS SELECT * FROM user_test;
//在 user_test 表上创建一个名为 view_user_test 的视图
CREATE VIEW view_user_test
    (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    AS SELECT id,name,dept_id,age,sex,height,login_date
    FROM user_test;

3、查看视图

语法格式：

DESCRIBE 视图名;

或者：

DESC 视图名;

eg：

DESCRIBE view_user_test;

注意：DESCRIBE 一般情况下可以简写成 DESC，输入这个命令的执行结果和输入 DESCRIBE 是一样的。

查看视图的详细信息：

SHOW CREATE VIEW 视图名;

eg：

SHOW CREATE VIEW view_user_test

4、修改视图

语法格式：

ALTER VIEW 视图名 AS SELECT语句

视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据。

某些视图是可更新的。也就是说，可以使用 UPDATE、DELETE 或 INSERT
等语句更新基本表的内容。对于可更新的视图，视图中的行和基本表的行之间必须具有一对一的关系。

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
位于选择列表中的子查询。
FROM 子句中的不可更新视图或包含多个表。
WHERE 子句中的子查询，引用 FROM 子句中的表。
ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

eg：

//使用 ALTER 语句修改视图 view_user_test
ALTER VIEW view_user_test
    AS SELECT id,name,age
    FROM user_test;

//使用 UPDATE 语句更新视图 view_user_test
UPDATE view_user_test SET age=25 WHERE id=1;

修改视图名称：修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

5、删除视图

语法格式：

DROP VIEW 视图名1 [ , 视图名2 …]

DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

eg：

//删除 view_user_test 视图
DROP VIEW IF EXISTS view_user_test;

二、索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。。
通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。
可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

1、索引的优缺点

（1）优点

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

（2）缺点

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

2、创建索引

（1）使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

语法格式：

CREATE 索引名 ON 表名 (列名 [长度] [ ASC | DESC])

语法说明：

• 索引名：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
• 表名：指定要创建索引的表名。
• 列名：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和WHERE 子句里经常出现的列作为索引列。
• 长度：可选项。指定使用列前的 length 个字符来创建索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

（2）使用 CREATE TABLE 语句
索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。

语法格式：

//在创建新表的同时创建该表的索引
KEY | INDEX [索引名] [索引类型] (<列名>,…)
//在创建新表的同时创建该表的唯一性索引
UNIQUE [ INDEX | KEY] [索引名] [索引类型] (<列名>,…)

（3）使用 ALTER TABLE 语句
CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。

语法格式：

//表示在修改表的同时为该表添加索引
ADD INDEX [索引名] [索引类型] (列名,…)
//表示在修改表的同时为该表添加唯一性索引
ADD UNIQUE [ INDEX | KEY] [索引名] [索引类型] (列名,…)

eg：
创建普通索引：

//创建一个表 users_test，在该表的 height 字段创建普通索引。
CREATE TABLE users_test (
    id INT NOT NULL,
    name CHAR(45) DEFAULT NULL,
    dept_id INT DEFAULT NULL,
    age INT DEFAULT NULL,
    height INT DEFAULT NULL,
    INDEX(height)
    );

创建唯一索引：

//创建一个表 users_test，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引
CREATE TABLE users_test (
    id INT NOT NULL,
    name CHAR(45) DEFAULT NULL,
    dept_id INT DEFAULT NULL,
    age INT DEFAULT NULL,
    height INT DEFAULT NULL,
    UNIQUE INDEX(height)
    );

3、查看索引

语法格式：

SHOW INDEX FROM 表名 [ FROM 数据库名]

eg：

SHOW INDEX FROM users_test;

4、删除索引

（1）使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

eg：

//删除表 tb_stu_info 中的索引
DROP INDEX height

（2）使用 ALTER TABLE 语句
根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

//删除表 users_test 中名称为 height 的索引
ALTER TABLE users_test DROP INDEX height;