常见数据库对象（视图、索引、触发器、事务）

一、视图

1.1　视图的定义

　　视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询结果来定义。同真实的表一样，视图包含一系列带有名称的行和列数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。如果基表中的数据发生变化，则从视图中查询出的数据也随之变化。

1.2　视图的优点

（1）定制用户数据

　　每个组织都有不同角色的工作人员，以销售公司为例，采购人员需要一些与采购有关的数据，而与采购无关的数据，对采购人员没有任何意义。可以根据这一实际情况，专门为采购人员创建一个视图，以后采购人员在查询数据时，只需执行“select　*　from　采购视图”就可以了。

（2）简化数据操作

　　在使用查询时，很多时候需要使用聚合函数，同时还要显示其他字段的信息，可能还会关联到其他表，这时写的SQL语句会很长，如果这个多做频繁发生的话，就可以通过创建视图，简化数据来查询。

（3）细粒化的安全机制

　　因为视图是虚拟的，物理上是不存在的，它只是存储了数据的集合。因此，可以将基表中重要的字段信息不通过视图提供给用户。视图是动态的数据的集合，数据是随着基表的更新而更新的。同时，用户不可以随意地更改和删除视图，以保证数据的安全性。

（4）合并分离的数据

　　随着社会的发展，公司的业务量不断地扩大。为了管理方便，对于设有很多分公司的大公司，需要统一表的结构，从而定期查看各个分公司的业务情况。使用union关键字，可以将各分公司的数据合并为一个视图，这样既方便又高效。

1.3　视图的创建和使用

语法：

　　create　view　view_name　as　select　column_name(n)　from　table_name(n)　where　condition

说明：

　　view_name：视图名

　　column_name(n)：视图中的字段列表，可以来源于多个表

　　table_name(n)：表名，可以来源于多个表

　　condition：条件表达式，如果是多个表，则该表达式还包含标的连接条件

二、索引

2.1　索引的基本知识

　　2.1.1　理解索引

　　2.1.2　索引工作的原理

　　2.1.3　索引的作用

　　2.1.4　索引的优缺点

　　　　　优点：

　　　　　　　（1）大大加快了数据的检索速度。

　　　　　　　（2）创建唯一性索引，保证数据库表中每一行数据的唯一性。

　　　　　　　（3）加速表和表之间的连接。

　　　　　　　（4）在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

　　　　　缺点：

　　　　　　　（1）索引需要占用物理空间。

　　　　　　　（2）当对表中的数据进行增加、删除和修改时，数据库系统需要对索引进行动态维护，降低了数据的维护速度。

　　2.1.5　使用索引的场合

　　　　　由于在创建、使用索引时，会损失一定的系统性能，所以在工程实践中需综合考虑性能与效率，决定是否创建索引，以及在哪些列上创建索引。一般情况下，可依据以下几个标准创建索引：

　　　　　　　（1）在经常需要搜索的列上，可以加快搜索的速度。

　　　　　　　（2）在作为主键的列上。

　　　　　　　（3）经常用在连接的列上，这些列主要是一些外键，在这些列上建立索引可以加快连接的速度。

　　　　　　　（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

　　　　　　　（5）在经常需要排序的列上创建索引，这样查询可以利用索引的排序，节省排序查询的时间。

　　　　　　　（6）在使用where子句的列上创建索引，加快条件的判断逻辑。

　　　　　一般而言，不应该创建索引的列具有下列特点：

　　　　　　　（1）对于那些在查询中很少使用或参考的列不应该创建索引。因为既然这些列很少被使用，所以是否有索引并不能影响查询速度。相反，由于增加了索引，反而降低了系统的维护速度且增大了空间需求。

　　　　　　　（2）对于那些只有很少数据值的列而言，同样不应该增加索引。因为由于这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

　　　　　　　（3）对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。

　　　　　　　（4）当修改性能远大于检索性能时，不应该创建索引。因为修改性能和检索性能时互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。但是当减少索引时，会提高修改性能，降低检索性能。

2.2　索引分类

（1）常规索引

　　常规索引（Regular　Index，也称普通索引），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，一般所说的索引都是常规索引。

（2）主键索引

　　主键索引（Primary　Key，也称主键）可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键，被标志为自动增长的字段一定是主键，但主键不一定是自动增长。

（3）唯一索引

　　唯一索引（Unique　Key）可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

（4）全文索引

　　全文索引（Full　Text，也称全文索引）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。虽然使用like关键字可以实现模糊匹配，但这仅适用于文本体量较少的情况，对于文本体量较大的检索是不可想象的。对于大体量文本的检索，全文索引的查询速度比like模糊查询快了几个数量级，但是全文索引可能存在精度问题。

（5）外键索引

　　外检索引（Foreign　Key）可以提高查询效率。外键字段如果没有指定索引名称，会自动生成。外键会自动和对应的其他表的主键关联，它的主要作用是保证记录的一致性和完整性。（只有InnoDB存储引擎的表才支持外键，5.6机器以上版本的MySQL的默认存储引擎就是InnoDB）

（6）复合索引

　　复合索引是指一个索引中包含多个列，复合索引也称多列索引或组合索引。索引列既可以为单个列，也可以为多个列。（如果在多个列上分别创建了索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远低于在多个列上所创建的复合索引，所以在多个列上创建复合索引的检索效率要高于分别在这些列上创建单个索引的检索效率）

2.3　创建索引

（1）使用　“alter　表　add　索引”　命令创建索引

　　语法：

　　　　1、alter　table　table_name　add　index　index_name　(column_list)

　　　　2、alter　table　table_name　add　unique　index_name　(column_list)

　　　　3、alter　table　table_name　add　primary　key　(column_list)

　　说明：

　　　　1、分别创建普通索引、唯一索引和主键索引。

　　　　2、table_name为需要增加索引的表名。

　　　　3、column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

　　　　4、索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

（2）使用　“create　索引”命令创建索引

　　语法：

　　　　1、create　index　index_name　on　table_name　(column_list)

　　　　2、create　unique　index　index_name　on　table_name　(column_list)

　　说明：

　　　　1、索引名为必须项。

　　　　2、不能使用create　index语句创建主键索引。

（3）使用drop命令和alter命令删除索引

　　语法：

　　　　1、drop　index　index_name　on　table_name

　　　　2、alter　table　table_name　drop　index　index_name　

三、触发器

3.1　触发器简介

　　3.1.1　触发器概述

　　　　触发器是MySQL5.0新增的功能。数据库触发器定义了一系列操作，这一系列操作称为触发程序，当触发事件发生时，触发程序会自动进行。

　　　　触发器是和表关联的特殊的存储过程，可以在插入、删除、修改表中的数据时触发执行，比数据库本身标准的功能有更精细和更复杂的数据控制能力。

　　　　触发器主要用于监视某个表的insert、update以及delete等数据维护操作，这些维护操作可以分别激活该表的insert、update、delete类型的触发程序运行，从而实现数据的自动维护。触发器可以实现的功能包括：使用触发器实现检查约束、维护冗余数据以及维护外键列数据等。

　　3.1.2　触发器优点

　　　　1）安全性

　　　　　　触发器可以基于数据库的值使用户具有操作数据库的某种权利；基于时间来限制用户的操作，例如，不允许下班后和节假日修改数据库数据；基于数据库中的数据来限制用户的操作，例如，不允许股票价格的升幅一次超过10%。

　　　　2）审计

　　　　　　触发器可以跟踪用户对数据库的操作；审计用户操作数据库的语句；把用户对数据库的更新写入审计表。

　　　　3）实现复杂的数据完整性规则

　　　　　　触发器可以实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。

　　　　4）实现复杂的非标准的数据库相关完整性规则

　　　　　　触发器可以对数据库中相关的表进行连环更新，这些连环更新包括一下集中典型场景：

　　　　　　1.在修改或删除时级联修改或删除其他表中的与之匹配的行。

　　　　　　2.在修改或删除时把其他表中的与之匹配的的行设成null值。

　　　　　　3.在修改或删除时把其他表中的与之匹配的行级联设成缺省值。

　　　　　　4.触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。

　　　　5）同步实时地复制表中的数据

　　　　6）提供了运行计划任务的另一种方法

3.2　定义触发器

语法：

　　create　trigger　触发器名　触发时间　触发事件　on　表名　for　each　row

　　begin

　　　　触发程序

　　end;

说明：

　　1、触发器是数据库对象，因此创建触发器时，需要指定该触发器隶属于哪个数据库。

　　2、触发器是基于基表的，不能基于临时表和视图。

　　3、MySQL的触发事件有3种：insert、update和delete。

　　4、触发器的触发时间有两种：before和after。before表示在触发事件发生之前执行触发程序，after表示在触发事件发生之后执行触发程序。通常一个数据库最大可以设置6中类型的触发器，即before　insert、before　update、before　delete、after　insert、after　update和after　delete。

　　5、for　each　row表示行级触发器。for　each　row表示更新（insert、update和delete）操作影响的每一条记录都会执行一次触发程序。通常使用“表名_触发事件_触发时间_trigger”定义触发器名字（如：product_insert_before_trigger）。

　　6、触发程序中可以使用old关键字和new关键字。

　　　　1）当向表中插入新纪录时，在触发器程序中可以使用new关键字表示新纪录。当需要访问新纪录的某个字段值时，可以使用“new.字段名”的方式访问。

　　　　2）当从表中删除某条记录时，在触发器程序中可以使用old关键字表示旧记录。当需要访问旧记录的某个字段值时，可以使用“old.字段名”的方式访问。

　　　　3）当修改表的某条记录时，在触发器程序中可以使用old关键字表示修改前的旧记录，使用new关键字表示修改后的新记录。当需要访问旧记录的某个字段时，可以使用“old.字段名”的方式访问；当需要访问新纪录的某个字段值时，可以使用“new.字段名”的方式访问。

　　　　4）old记录仅是只读的，只可以引用它，而不能更改它。

四、数据库事务

4.1　事务概述

　　事务是一个由用户所定义的完整的工作单元，一个事务内的所有语句作为一个整体来执行，或者全部执行，或者全部不执行。当遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。

4.2　事务特性

　　数据库事务有四大特性，简称ACIP，分别如下：

　　（1）原子性（atomic、atomicity）

　　　　事务必须是一个原子工作单元。对于其数据修改，或者全部执行，或者全都不执行。通常与某事务相关联的操作具有共同的目标，并且是相互依赖的。如果系统仅执行这些操作的一个子集，则可能会破坏事务的总体目标，原子性消除了系统处理操作子集的可能性。

　　（2）一致性（consistent、consistency）

　　　　事务完成时，必须使所有的数据都保持一致的状态，即事务执行的结果必须使使数据库从一个一致性状态转换为另一个一致性状态。

　　（3）隔离性（isolation）

　　　　由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据时，数据所处的状态，要么是另一个并发事务修改其之前的状态，要么是另一个事务修改其之后的状态，事务不会查看处于中间状态的数据。

　　（4）持久性（duration、durability）

　　　　事务完成后，其对于系统的影响是永久性的，即事务一旦完成，即使系统出现致命的故障，也将一直保持修改之后的状态。

4.3　关闭MySQL自动提交

　　默认情况下，MySQL开启了自动提交。也就是说任意一条DML中的insert语句、update语句、delete语句，一旦发送到MySQL服务器上，MySQL服务器实例会立即解析、执行，并将新增、更新结果提交到数据库文件中，成为数据库中永久的数据。因此，对于诸如银行转账之类的逻辑而言，首要步骤是关闭MySQL自动提交，只有当所有的SQL语句成功执行后，才提交（commit）所有的更新语句，否则回滚（rollback）所有的更新语句。

　　关闭自动提交的方式有两种：

　　　　1）显式地关闭自动提交

　　　　　　使用MySQL命令“show　variables　like　'autocommit';　”可以查看MySQL是否开启了自动提交。系统变量　@@autocommit　的值为ON或1时，表示MySQL开启了自动提交，默认情况下，MySQL开启自动提交；系统变量　@@autocommit　的值为OFF或0时，表示MySQL关闭了自动提交。使用MySQL命令“set　autocommit=0;”可以显式地关闭MySQL自动提交。

　　　　2）隐式地关闭自动提交

　　　　　　使用MySQL命令“start　transaction;”可以隐式地关闭自动提交，隐式地关闭自动提交不会修改系统会话变量@@autocommit的值。

4.4　MySQL事务操作语句

　　start　transaction：标识一个事务的开始，即启动事务。

　　commit：提交事务。标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。

　　rollback：回滚事务。标识一个事务的结束，表明事务执行过程中遇到错误，事务内所有修改的数据被回滚到事务执行前的状态。