MySQL学习日记（四）视图、索引、存储过程、触发器

视图、索引、存储过程、触发器

1、什么是MySQL视图

1.1、视图

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

1.2、存储

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

1.3、用途

• 视图可以从原有的表上选取对用户有用的信息，那些对用户没用，或者用户没有权限了解的信息，都可以直接屏蔽掉，作用类似于筛选。这样做既使应用简单化，也保证了系统的安全。
• 如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据。

1.4、视图的优点

1、优点
视图与表在本质上虽然不相同，但视图经过定义以后，结构形式和表一样，可以进行查询、修改、更新和删除等操作。同时，视图具有如下优点：

1. 定制用户数据，聚焦特定的数据
   在实际的应用过程中，不同的用户可能对不同的数据有不同的要求。
   例如，当数据库同时存在时，如学生基本信息表、课程表和教师信息表等多种表同时存在时，可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图，安排课程人员查看修改课程表和教师信息的视图，教师查看学生信息和课程信息表的视图。
2. 简化数据操作
   在使用查询时，很多时候要使用聚合函数，同时还要显示其他字段的信息，可能还需要关联到其他表，语句可能会很长，如果这个动作频繁发生的话，可以创建视图来简化操作。
3. 提高数据的安全性
   视图是虚拟的，物理上是不存在的。可以只授予用户视图的权限，而不具体指定使用表的权限，来保护基础数据的安全。
4. 共享所需数据
   通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次。
5. 更改数据格式
   通过使用视图，可以重新格式化检索出的数据，并组织输出到其他应用程序中。
6. 重用 SQL 语句
   视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。

1.5、使用视图注意事项

使用视图的时候，还应该注意以下几点：

• 创建视图需要足够的访问权限。
• 创建视图的数目没有限制。
• 视图可以嵌套，即从其他视图中检索数据的查询来创建视图。
• 视图不能索引，也不能有关联的触发器、默认值或规则。
• 视图可以和表一起使用。
• 视图不包含数据，所以每次使用视图时，都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图，可能会发现系统运行性能下降得十分严重。因此，在部署大量视图应用时，应该进行系统测试。

2、视图常见操作

2.1、 创建视图

可以使用 CREATE VIEW 语句来创建视图。

语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

语法说明如下。

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

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

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

注意事项

• 视图定义中引用的表或视图必须存在。但是，创建完视图后，可以删除定义引用的表或视图。可使用 CHECK TABLE 语句检查视图定义是否存在这类问题。
• 视图定义中允许使用 ORDER BY 语句，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。
• 视图定义中不能引用 TEMPORARY 表（临时表），不能创建 TEMPORARY 视图。
• WITH CHECK OPTION 的意思是，修改视图时，检查插入的数据是否符合 WHERE 设置的条件。

2.1.1、创建基于单表的视图

2.1.2、创建基于多表的视图

2.2、修改视图

可以使用 ALTER VIEW 语句来对已有的视图进行修改。

语法格式如下：

ALTER VIEW <视图名> AS <SELECT语句>

语法说明如下：

• <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

• <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

• 修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

2.2.1、修改视图的内容

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

对视图的修改就是对基本表的修改，因此在修改时，要满足基本表的数据定义。

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

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

• 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
• DISTINCT 关键字。
• GROUP BY 子句。
• HAVING 子句。
• UNION 或 UNION ALL 运算符。
• 位于选择列表中的子查询。
• FROM 子句中的不可更新视图或包含多个表。
• WHERE 子句中的子查询，引用 FROM 子句中的表。
• ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

下面我们将上次创建的基于多表的视图修改，让它符合条件。

2.2.2、修改视图的名字

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

2.3、删除视图（Drop view）

删除视图是指删除 MySQL 数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。可以使用 DROP VIEW 语句来删除视图。

语法格式如下：

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

其中：<视图名>指定要删除的视图名。DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限。

3、MySQL索引（Index）

3.1、什么是索引

索引概念：
索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。
访问数据表的方式

• 1、顺序访问
  顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。
• 2、索引访问
  索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

索引的优缺点
索引的优点如下：

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

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

3.2、索引类型的分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

3.2.1、按照存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类
1、B-树索引（BTREE索引）
目前大部分的索引都是采用B-树索引来存储的（也就是B树、平衡树）；B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

• 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
• 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
• 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。
  
  

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

• 查询必须从索引的最左边的列开始。
• 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
• 存储引擎不能使用索引中范围条件右边的列。

2、hash索引（哈希表结构）
MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

3.2.2、按照逻辑方式区分

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1. 普通索引
   普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
   创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
   

2. 唯一索引
   唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。其中，id 字段可以有唯一性约束，也可以没有。
   

3. 主键索引
   顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
   主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4. 空间索引
   空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。例如在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：
   CREATE SPATIAL INDEX index_line ON tb_student(line);
   其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5. 全文索引
   全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。创建全文索引使用 FULLTEXT 关键字。例 4在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：
   CREATE FULLTEXT INDEX index_info ON tb_student(info);
   其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

3.2.3、按照实际使用的角度区分

在实际使用中，索引通常被创建成单列索引和组合索引。

• 1、单列索引
  单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
• 2、多列索引
  组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。
  下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：
  CREATE INDEX index_na ON tb_student(name,address);#选择了两个列
  该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

3.3 MySQL创建索引

3.3.1 Create index

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

实例：

3.3.2 Create Table时创建索引

我们之前已经学过在创建表的同时创建主键、外键等约束。创建索引也是类似的过程。

3.3.3 使用 ALTER TABLE 语句add index

如同使用alter table修改主键、外键等约束一样，可以用add index来创建修改索引。

3.4 查看索引

语法

show index from 表名 [from 数据库名]

实例：

3.5 修改和删除索引（Drop index）

删除索引是指将表中已经存在的索引删除掉。不用的索引建议进行删除，因为它们会降低表的更新速度，影响数据库的性能。对于这样的索引，应该将其删除。
删除索引
使用 DROP INDEX 语句
语法格式：

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

语法说明如下：

• <索引名>：要删除的索引名。
• <表名>：指定该索引所在的表名。

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

• DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
• DROP INDEX index_name：表示删除名称为 index_name 的索引。
• DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。
在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

实例

3.6 MySQL索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。下面是最基本的设计原则：

1. 选择唯一性索引
   唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引
   经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引
   如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
   注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目
   索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引
   如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引
   由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引
   如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引
   表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

4、存储过程

4.1 什么是存储过程

我们前面所学习的 MySQL 语句都是针对一个表或几个表的单条 SQL 语句，但是在数据库的实际操作中，经常会有需要多条 SQL 语句处理多个表才能完成的操作。
例如，为了确认学生能否毕业，需要同时查询学生档案表、成绩表和综合表，此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。
定义：
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
性质
一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。
MySQL 5.0 版本以前并不支持存储过程，这使 MySQL 在应用上大打折扣。MySQL 从 5.0 版本开始支持存储过程，既提高了数据库的处理速度，同时也提高了数据库编程的灵活性

存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

使用存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性。

对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。只是简单调用即可，它可以被 Java 和 C# 等编程语言调用。
使用存储过程的好处

1. 封装性
   通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
2. 可增强 SQL 语句的功能和灵活性
   存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
3. 可减少网络流量
   由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
4. 高性能
   当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。
5. 提高数据库的安全性和数据的完整性
   存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
6. 使数据独立
   数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

存储过程的存储位置
存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;

在 information_schema 数据库下的 routines 表中，存储着所有存储过程的定义。所以，使用 SELECT 语句查询 routines 表中的存储过程和函数的定义时，一定要使用 routine_name 字段指定存储过程的名称，否则，将查询出所有的存储过程的定义。

4.2 创建存储过程

语法：

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

1. 过程名
   存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。
   需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
2. 过程参数
   存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
3. 过程体
   存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

实例：

4.3 查看存储过程

1、查看存储过程的状态
语法 ：

show procedure status like 存储过程名;

2、查看存储过程的定义

show create procedure 存储过程名;

4.4 修改存储过程（ALTER PROCEDURE）

在实际开发过程中，业务需求修改的情况时有发生，所以修改 MySQL 中的存储过程是不可避免的。MySQL 中通过 ALTER PROCEDURE 语句来修改存储过程。
MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

• CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
• NO SQL 表示子程序中不包含 SQL 语句。
• READS SQL DATA 表示子程序中包含读数据的语句。
• MODIFIES SQL DATA 表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
• DEFINER 表示只有定义者自己才能够执行。
• INVOKER 表示调用者可以执行。
• COMMENT ‘string’ 表示注释信息。

实例

4.5 删除存储过程（DROP PROCEDURE）

存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当 MySQL 数据库中存在废弃的存储过程时，我们需要将它从数据库中删除。

注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

删除后，可以通过查询 information_schema 数据库下的 routines 表来确认上面的删除是否成功。SQL 语句和运行结果如下：

4.6 调用存储过程（call sp_name）

call sp_name[(过程参数)];

5、MySQL触发器

5.1 什么是MySQL触发器

简介
MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，而是通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行。
触发器的作用
触发器与数据表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。
在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器，其它 SQL 语句则不会激活触发器。
那么为什么要使用触发器呢？比如，在实际开发项目时，我们经常会遇到以下情况：
在学生表中添加一条关于学生的记录时，学生的总数就必须同时改变。
增加一条学生记录时，需要检查年龄是否符合范围要求。
删除一条学生信息时，需要删除其成绩表上的对应记录。
删除一条数据时，需要在数据库存档表中保留一个备份副本。

虽然上述情况实现的业务逻辑不同，但是它们都需要在数据表发生更改时，自动进行一些处理。这时就可以使用触发器处理。例如，对于第一种情况，可以创建一个触发器对象，每当添加一条学生记录时，就执行一次计算学生总数的操作，这样就可以保证每次添加一条学生记录后，学生总数和学生记录数是一致的。
触发器的优缺点
触发器的优点如下：

• 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。
• 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。
• 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

触发器的缺点如下：

• 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
• 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，
• 如果需要变动的数据量较大时，触发器的执行效率会非常低。

MySQL支持的触发器
在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

触发器类型	简介	注意事项
INSERT 触发器	在 INSERT 语句执行之前或之后响应的触发器。	在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行；在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）；对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
UPDATE 触发器	在 UPDATE 语句执行之前或之后响应的触发器。	在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值；在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值；在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）；OLD 中的值全部是只读的，不能被更新。
DELETE 触发器	在 DELETE 语句执行之前或之后响应的触发器。	在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行；OLD 中的值全部是只读的，不能被更新。

小结技巧
触发器使用的过程中，MySQL 会按照以下方式来处理错误。

• 对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

• 若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

• 若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

• 仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

5.2、创建触发器（CREATE TRRIGER）

语法：

CREATE
    [DEFINER = user]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body//触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。

参数解析：

• trigger_time: { BEFORE | AFTER }//触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。

• trigger_event: { INSERT | UPDATE | DELETE }//触发事件，用于指定激活触发器的语句的种类。

• trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

• FOR EACH ROW:一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

5.2.1、创建BEFORE触发器

5.2.2 创建AFTER触发器

5.3 查看触发器（show triggers）

方式一SHOW TRIGGERS
在 MySQL 中，可以通过 SHOW TRIGGERS 语句来查看触发器的基本信息，语法格式如下：

SHOW TRIGGERS;

方式二
在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看，具体的语法如下：

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

其中，'触发器名’用来指定要查看的触发器的名称，需要用单引号引起来。这种方式可以查询指定的触发器，使用起来更加方便、灵活。

5.4 删除和修改触发器（DROP TRIGGER）

修改触发器
修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。

删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

实例：

6、MySQL 事件（Event,执行定时任务）

6.1 什么是事件

在数据库管理中，经常要周期性的执行某一命令或 SQL 语句，于是 MySQL 5.1 版本以后就提供了事件，它可以很方便的实现 MySQL 数据库的计划任务，定期运行指定命令，使用起来非常简单方便。

事件（Event）也可称为事件调度器（Event Scheduler），是用来执行定时任务的一组 SQL 集合，可以通俗理解成 MySQL 中的定时器。一个事件可调用一次，也可周期性的启动。

事件可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。

事件和触发器类似，都是在某些事情发生时启动。当数据库启动一条语句的时候，触发器就启动了，而事件是根据调度事件来启动的。由于他们彼此相似，所以事件也称为临时性触发器。

查看事件是否开启——3种方式
在 MySQL 中，调度器 event_scheduler 负责调用事件。我们可以通过以下几种命令查看事件是否开启，一般情况下默认值为 OFF。

开启\关闭事件

• 1、通过设置全局参数修改
  可以使用 SET GLOBAL 命令设定全局变量 event_scheduler 的值，开启或关闭事件。

SET GLOBAL event_scheduler = ON ;//OFF

通过 SET GLOBAL 命令开启或关闭事件，MySQL 重启服务后事件又会回到原来的状态，如果想要始终开启或关闭事件，可以修改 MySQL 配置文件。

• 2、更改配置文件
  在 MySQL 配置文件中找到 [mysqld] 选项，然后在下面添加以下代码开启事件。
  event_scheduler = ON
  在配置文件中添加代码并保存文件后，重启 MySQL 服务才能生效。
  通过该方法开启或关闭事件，重启 MySQL 服务后，不会回到原来的状态。

6.2 创建事件（CREATE EVENT）

语法：

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

从上面的语法可以看出，CRATE EVENT 语句由多个子句组成，各子句的详细说明如下表所示。

参数	描述
DEFINER	可选，用于定义事件执行时检查权限的用户
IF NOT EXISTS	可选，用于判断要创建的事件是否存在
EVENT event_name	必选，用于指定事件名称，event_name 的最大长度为 64 个字符如果未指定 event_name，则默认为当前的 MySQL 用户名（不区分大小写）
ON SCHEDULE schedule	必选，用于定义执行的时间和时间间隔
schedule	表示触发点,AT语句
ON COMPLETION [NOT] PRESERVE	可选，用于定义事件是否循环执行，即是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE
ENABLE/DISABLE / DISABLE ON SLAVE	可选，用于指定事件的一种属性。其中，关键字 ENABLE 表示该事件是活动的，即调度器检查事件是否必须调用；关键字 DISABLE 表示该事件是关闭的，即事件的声明存储到目录中，但是调度器不会检查它是否应该调用；关键字 DISABLE ON SLAVE 表示事件在从机中是关闭的。如果不指定以上 3 个选项中的任何一个，默认为 ENABLE
COMMENT ‘comment’	可选，用于定义事件的注释
DO event_body	必选,用于指定事件启动时所要执行的代码，可以是任何有效的 SQL 语句、存储过程或者一个计划执行的事件。如果包含多条语句，则可以使用 BEGIN…END 复合结构

实例：

6.3 查看事件

创建好事件后，用户可以通过以下 方式来查看事件的状态信息：

select * from information_schema.events where event_name='事件名'；

切换到相应的数据库后执行

 SHOW EVENTS;

实例

参数解析：

6.4 删除事件

修改事件
在 MySQL 中，事件创建之后，可以使用 ALTER EVENT 语句修改其定义和相关属性。

修改事件的语法格式如下：

ALTER EVENT event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

ALTER EVENT 语句还有一个用法就是让一个事件关闭或再次让其活动。

删除事件
在 MySQL 中，可以使用 DROP EVENT 语句删除已经创建的事件。语法格式如下：

DROP EVENT [IF EXISTS] event_name;