MySQL学习笔记：第五天（二）视图和索引

视图

视图是逻辑上来自一个或多个表的数据集合

为什么使用视图

• 限制其它用户对数据库表的访问,因为视图可以有选择性的显示数据库表的一部分；
• 容易实现复杂的查询；
• 对于相同的数据可以产生不同的视图；

简单来讲就是，安全，在软件开发中，有隔离效果

视图分类（最基本差别在DML操作上）

• z简单视图
• 复杂视图

创建视图

CREATE VIEW view_name [(字段名称)] 
AS select_statement
 [WITH [CASCADED | LOCAL] CHECK OPTION]

• select_statement ：一条完整的SELECT语句；
• WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须满足视图所定义的查询；

从视图检索数据

从视图中检索数据，同从表中检索数据一样，只不过是只能看到视图所定义的那些列。

修改视图

用 CREATE OR REPLACE VIEW子句修改视图

CREATE OR REPLACE VIEW empvu10
    	(employee_number, employee_name, job_title)
  AS SELECT 	empno, ename, job
  FROM				emp
   WHERE				deptno = 10;

CREATE VIEW子句中别名的顺序必须和内部查询中的列的顺序一一对应。

视图上执行DML操作

在简单视图上可以执行 DML 操作；

您可以通过视图删除基表中数据，只要视图中不出现以下情况：

• Group 函数；
• GROUP BY 子句；
• DISTINCT 关键字；

您可以通过视图修改基表中数据，只要视图中不出现以下情况：

• GROUP函数、GROUP BY子句，DISTINCT关键字；
• 使用表达式定义的列；

您可以通过视图向基表插入数据，只要视图中不出现以下情况：

• GROUP函数、GROUP BY子句，DISTINCT关键字；
• 使用表达式定义的列；
• 基表中未在视图中选择的其它列定义为非空并且没有默认值；

如果要确保在视图上执行的DML操作仅限于一定的范围，便可使用WITH CHECK OPTION子句；

CREATE OR REPLACE VIEW empvu20
    AS SELECT	*
    FROM	        emp
    WHERE	        deptno = 20
    WITH CHECK OPTION;

在视图中任何修改部门编号的操作都会失败，因为这违反了 WITH CHECK OPTION约束。

删除视图

删除视图并不会删除数据，因为视图是基于数据库中的基表的虚表。

DROP VIEW view; 

索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

索引简介

• 索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
• 索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。
• 索引可以提高查询的速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列。
• 索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
• 索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

索引分类

• 普通索引
• 惟一性索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

创建索引

创建索引有三种方式

• 创建表的时候创建索引
• 在已经存在的表上创建索引
• 使用ALTER TABLE语句来创建索引

创建表的时候创建索引

CREATE TABLE  表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);

普通索引

Create table index1(
Id int,
Name varchar(20),
Sex boolean,
Index(id)
);

创建唯一性索引

Create table index2(
Id int unique,
Name varchar(20),
Unique index index2_id(id asc)
);

创建全文索引

只能创建在char,varchar或text类型的字段上，而且，现在只有MyISAM存储引擎支持全文索引。

Create table index3(
Id int,
Info varchar(20),
Fulltext index index3_info(info)
)engine=myisam;

创建单列索引

Create table index4(
Id int,
Subject varchar(30),
Index index4_st(subject(10))
);

在已经存在的表上创建索引

在已经存在的表上，可以直接为表上的一个或几个字段创建索引。

CREATE  [ UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
     ON  表名  (属性名  [ (长度) ]  [  ASC | DESC] );

用ALTER TABLE语句来创建索引

在已经存在的表上，可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。

ALTER  TABLE  表名  ADD   [ UNIQUE | FULLTEXT | SPATIAL ]   INDEX  
     索引名（属性名  [ (长度) ]  [ ASC | DESC]）;

删除索引

删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。

对应已经存在的索引，可以通过DROP语句来删除索引。

DROP  INDEX  索引名  ON  表名 ;

索引的设计原则

• 选择惟一性索引
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引 限制索引的数目
• 尽量使用数据量少的索引 尽量使用前缀来索引
• 删除不再使用或者很少使用的索引选择惟一性索引
• 为经常需要排序、分组和联合操作的字段建立索引
• 为常作为查询条件的字段建立索引 限制索引的数目
• 尽量使用数据量少的索引 尽量使用前缀来索引
• 删除不再使用或者很少使用的索引