第9章 索引

9.1 索引简介

9.1.1 索引的含义和特点

9.1.2 索引的分类

9.1.3 索引的设计原则

9.2 创建索引

9.2.1 创建表的时候创建索引

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

9.3 删除索引

9.4 综合案例——创建索引

9.5 专家解惑

9.6 经典习题

第10章 存储过程和函数

10.1 创建存储过程和函数

10.1.1 创建存储过程

10.1.2 创建存储函数

10.1.3 变量的使用

10.1.4 定义条件和处理程序

10.1.5 光标的使用

10.1.6 流程控制的使用

10.2 调用存储过程和函数

10.2.1 调用存储过程

10.2.2 调用存储函数

10.3 查看存储过程和函数

10.3.1 使用SHOW STATUS语句查看存储过程和函数的状态

10.3.2 使用SHOW CREATE语句查看存储过程和函数的定义

10.3.3 从information schema.Routines表中查看存储过程和函数的信息

10.4 修改存储过程和函数

10.5 删除存储过程和函数

10.6 综合案例——创建存储过程和函数

10.7 专家解惑

10.8 经典习题

第11章 视图

11.1 视图概述

11.1.1 视图的含义

11.1.2 视图的作用

11.2 创建视图

11.2.1 创建视图的语法形式

11.2.2 在单表上创建视图

11.2.3 在多表上创建视图

11.3 查看视图

11.3.1 使用DESCRIBE语句查看视图基本信息

11.3.2 使用SHOW TABLE STATUS语句查看视图基本信息

11.3.3 使用SHOW CREATE VIEW语句查看视图详细信息

11.3.4 在views表中査看视图详细信息

11.4 修改视图

11.4.1 使用 CREATE OR REPLACE VIEW 语句修改视图

11.4.2 使用ALTER语句修改视图

11.5 更新视图

11.6 删除视图

11.7 综合案例——视图应用

11.8 专家解惑

11.9 经典习题

第12章 MySQL触发器

12.1 创建触发器

12.1.1 创建只有一个执行语句的触发器

12.1.2 创建有多个执行语句的触发器

12.2 查看触发器

12.2.1 SHOW TRIGGERS语句查看触发器信息

12.2.2 在triggers表中查看触发器信息

12.3 触发器的使用

12.4 删除触发器

12.5 综合案例——触发器的使用

12.6 专家解惑

12.7 经典习题

第9章 索引

9.1索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。本节将介绍索引的含义、分类和设计原则。

9.1.1索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种:BTREE和HASH，具体和表的存储引擎相关; MylSAM和InnoDB存储引擎只支持BTREE索引用; MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点主要有以下几条:

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

增加索引也有许多不利，主要表现在如下几个方面:

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

9.1.2索引的分类

MySQL的索引可以分为以下几类:

1. 普通索弓和唯一索引
   普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
   唯一索引 ，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2. 单列索引和组合索引
   单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
   组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3. 全文索引
   全文索引类型为FULLTEXT, 在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。 MySQL中只有MylSAM存储引擎支持全文索引。

4. 空间索引
   空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是:GEOMETRY, POINT. LINESTRING 和 POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声 明为NOT NULL,空间索引只能在存储引擎为MylSAM的表中创建。

9.1.3索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。

设计索引时，应该考虑以下准则:

• 索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT, DELETE. UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。
• 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
• 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的"性别"字段上只有"男"与"女"两个不同值，因此就无须建立索引。如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
• 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
• 在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

9.2 创建索引

MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。本节将详细介绍这3种方法。

9.2.1创建表的时候创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引。创建表时创建索引的基本语法格式如下:

CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX I KEY] [index_name] (col_name [length]) [ASC | DESC]

UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引;INDEX与KEY为同义词，两者作用相同，用来指定创建索引;col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择;index_name指定索引的名称，为可选参数，如果不指定，MySQL默认col_name为索引值;length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;ASC或DESC指定升序或者降序的索引值存储。

1.创建普通索引
最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

EXPLAIN语句输出结果的各个行解释如下:

• select_type行指定所使用的SELECT查询类型，这里值为SIMPLE,表示简单的SELECT, 不使用UNION或子查询。其他可能的取值有:PRIMARY, UNION、SUBQUERY等。
• table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。
• type行指定了本数据表与其他数据表之间的关联关系，可能的取值有system, const、eq_ref、ref、range, index 和 All。
• possible_keys行给出了 MySQL在搜索数据记录时可选用的各个索引。
• key行是MySQL实际选用的索引。
• key len行给出索引按字节计算的长度，key len数值越小，表示越快。
• ref行给出了关联关系中另一个数据表里的数据列的名字。
• rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
• extra行提供了与关联操作有关的信息。

2.创建唯一索引
创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的就是:索引列的 值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

3.创建单列索引
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。

4.创建组合索引
组合索引是在多个字段上创建一个索引。

5.创建全文索引
FULLTEXT (全文索引)可以用于全文搜索。只有MylSAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行，不支持局部(前缀)索引。

6.创建空间索引
空间索引必须在MylSAM类型的表中创建，且空间类型的字段必须为非空。

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

在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句，
本节将介绍如何使用ALTER TABLE和CREATE INDEX语句在已知表字段上创建索引。

1. 使用ALTER TABLE语句创建索引
   ALTER TABLE创建索引的基本语法如下:
   ALTER TAB1E table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEXI KEY] [index一name] (col_name [length}[ASC | DESC]
   与创建表时创建索引的语法不同的是，在这里使用了 ALTER TABLE和ADD关键字,ADD表示向表中添加索引。

2.使用CREATE INDEX创建索引
CREATE INDEX语句可以在已经存在的表上添加索引，MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name(length],...) [ASC | DESC]
可以看到CREATE INDEX语句和ALTER TABLE语句的语法基本一样 只是关键字不同。

9.3删除索引

MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者可实现相同的功
能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

1.使用ALTER TABLE删除索引
ALTER TABLE删除索引的基本语法格式如下:
ALTER TABLE table name DROP INDEX index name;
添加AUTO_INCREMENT约束字段的唯一索引不能被删除。

2.使用DROP INDEX语句删除索引
DROP INDEX删除索引的基本语法格式如下:
DROP INDEX index_name ON table_name;
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

9.4 综台案例—创建索引

9.5 专家解惑

疑问1:索引对数据库性能如此重要,应该如何使用它?
为数据库选择正确的索引是一项复杂的任务。如果索引列较少，则需要的磁盘空间和维护开销都较少。如果在一个大表上创建了多种组合索引,索引文件也会膨胀很快。而另一方面，索引较多可覆盖更多的查询。可能需要试验若干不同的设计，才能找到最有效的索引。可以添加、修改和删除索引而不影响数据库架构或应用程序设计。因此，应尝试多个不同的索引从而建立最优的索引。

疑问2:尽量使用短索引。
对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或30个字符内，多数值是唯一的，则不需要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

第10章 存储过程和函数

10.1 创建存储过程和函数

存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是: CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

10.1.1创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下:
CREATE PROCEDURE sp__name ( [proc一parameter]) [characteristics .・.] routine_body
CREA TE PROCEDURE为用来创建存储函数的关键字;sp_name为存储过程的名称;
proc_parameter为指定存储过程的参数列表，列表形式如下:[IN | OUT | INOUT ] param_name type
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

characteristics指定存储过程的特性，有以下取值:

• LANGUAGE SQL:说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
• [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC.
• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA说明子程 序包含读数据的语句;MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL„
• SQL SECURITY { DEFINER | INVOKER }:指明谁有权限来执行，DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER.,
• COMMENT 'string':注释信息，可以用来描述存储过程或函数。
  routine body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

编写存储过程并不是件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限;但是使用存储过程将简化操作，减 少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此存储过程是非常有用的，而且应该尽可能地学会使用。

"DELIMITER //"语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号';'，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以"END//"结束存储过程。存储过程定义完毕之后再使用"DELIMITER ;" 恢复默认结束符。DELIMITER也可以指定其他符号做为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠()字符，因为反斜线是MySQL的转义字符。

10.1.2创建存储函数

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下:
CREATE FUNCTION func_name ( [func_parameter]) RETURNS type [characteristic ...] routine_body
CREATE FUNCTION为用来创建存储函数的关键字;fimc_name表示存储函数的名称;
func_parameter为存储过程的参数列表，参数列表形式如下: [IN 丨 OUT | INOUT ] param_name type
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出; param_name表示参数名称;type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
RETURNS type语句表示函数返回数据的类型;characteristic指定存储函数的特性，取值与创建存储过程时相同，这里不再赘述。

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集的相应的ENUM成员，从函数返回的值是字符串。

指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。(FUNCTION中总是默认为IN参数)。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

10.1.3变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END程序中，本小节主要介绍如何定义变量和为变量赋值。

1. 定义变量
   在存储过程中使用DECLARE语句定义变量，语法格式如下:
   DECLARE var name [, varname]... date type [ DEFAULT value];
   var_name为局部变量的名称。DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

2. 为变量赋值
   定义变量之后为变量赋值可以改变变量的默认值MySQL中使用SET语句为变量赋值，
   语法格式如下:
   SET var_name = expr [, var一name = expr] ...;
   在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。
   在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a =x, b=y,...这样的扩展语法。其中不同的变量类型(局域声明变量及全局变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

MySQL中还可以通过SELECT ... INTO为一个或多个变量赋值，语法如下:
SELECT col_name [,..•] INTO var_name table_expr;
这个SELECT语法把选定的列直接存储到对应位置的变量。col_name表示字段名称; var_name表示定义的变量名称table_expr表示查询条件表达式包括表名称和WHERE子句。

10.1.4定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式 ，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处 理问题的能力，避免程序异常停止运行。本节将介绍使用DECLARE关键字来定义条件和处理程序。

1.定义条件
定义条件使用DECLARE语句，语法格式如下:
DECLARE condition_name CONDITION FOR [condition_type] [condition一type]: SQLSTATE [VALUE] sqlstate_value | mysql_error_code
其中，condition_name参数表示条件的名称; condition_type参数表示条件的类型; sqlstat_value 和 MySQL_error_code 都可以表示 MySQL 的错误，sqlstate_value 为长度为 5 的 字符串类型错误代码，MySQL_error_code为数值类型错误代码。
这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

2定义处理程序
定义处理程序时，使用DECLARE语句的语法如下:

DECLARE handler_type HANDLER FOR condition_value[, ...] sp_statement handler_type:
    CONTINUE | EXIT | UNDO
    condition—value:
    SQLSTATE [VALUE] sqlstate_value | condition一name | SQLWARNING
    | NOT FOUND | SQLEXCEPTION
        | mysql_error_code

其中，handler_type为错误处理方式，参数取3个值:CONTINUE. EXIT和UNDO。 CONTINUE表示遇到错误不处理，继续执行;EXIT遇到错误马上退出;UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
condition value表示错误类型，可以有以下取值:

• SQLSTATE [VALUE] sqlstate_value:包含5个字符的字符串错误值;
• condition name:表示DECLARE CONDITION定义的错误条件名称;
• SQLWARNING:匹配所有以01开头的SQLSTATE错误代码;
• NOT FOUND:匹配所有以02开头的SQLSTATE错误代码;
• SQLEXCEPTION:匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码。
• My_SQLerror_code:匹配数值类型错误代码。
  sp_statement参数为程序语句既表示在遇到定义的错误时，需要执行的存储过程或函数。

"@var_name"表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

10.1.5光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。 本节将介绍如何声明、打开、使用和关闭光标。
光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.声明光标
MySQL中使用DECLARE关键字来声明光标，其语法的基本形式如下:
DECLARE cursor_name CURSOR FOR select_statement
其中，cursor_name参数表示光标的名称;select_statement参数表示SELECT语句的内容,返回一个用于创建光标的结果集。

2.打开光标
打开光标的语法如下:
OPEN cursor_name{光标名称}
这个语句打开先前声明的名称为cursor_name的光标。

3.使用光标
使用光标的语法如下:
FETCH cursor_name INTO var_name [, var_name] • ..{参数名称}
其中，cursor_name参数表示光标的名称;var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

4.关闭光标
关闭光标的语法如下:
CLOSE cursor__name {光标名称}
这个语句关闭先前打开的光标。
如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

10.1.6流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有:IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。
每个流程中可能包含一个单独语句，或者是使用BEGIN ... END构造的复合语句，构造可以被嵌套。本节将介绍这些控制流程语句。

1. IF语句
   IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下:

IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list] ••• [ELSE statement_list]
END IF

IF实现了一个基本的条件构造。如果expr_condition求值为真(TRUE)，相应的SQL语句列表被执行.如果没有expr condition匹则ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

2. CASE语句
   CASE是另一个进行条件判断的语句，该语句有2种语句格式

第1种格式如下:

CASE case__expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement__list]...
        [ELSE statement_list]
END CASE

其中，case_expr参数表示条件判断的表达式，决定了哪一个WHEN子句会被执行; when_value参数表示表达式可能的值如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句;statement_list参数表示不同 when_value值的执行语句。

第2种格式如下:

CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement__list]...
        [ELSE statement_list]
END CASE

其中，expr_condition参数表示条件判断语句;statement_list参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个expr_condition表达式为真，则执行对应THEN关键字后面的statement_list语句。如果没有条件匹配，ELSE子句里的语句被执行。

3. LOOP语句
   LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE子句，LOOP语句的基本格式如下:

[loop_label:] LOOP 
    statement_list
END LOOP [loop_Label]

loop label表示LOOP语句的标注名称，该参数可以省略;statement_list参数表示需要循环执行的语句。

4. LEAVE语句
   LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式如下:LEAVE lable;
   其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。

5. ITERATE语句
   ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下: ITERATE label
   ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为"再次循环"，label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

6. REPEAT 语句
   REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束:否则重复执行循环中的语句。
   REPEAT语句的 基本格式如下:

[repeat—label:] REPEAT
    statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略;REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

7. WHILE 语句
   WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时， 先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。
   WHILE语句 的基本格式如下:

(while_label:] WHILE expr_condition DO
     statement_list
END WHILE [while一label]

while label为WHILE语句的标注名称;expr_condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

10.2 调用存储过程和函数

存储过程已经定义好了，接下来需要知道如何调用这些过程和函数。存储过程和函数有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname=存储函数的调用与MySQL中预定义的函数的调用方式相同。本节介绍存储过程和存储函数的调用，主要包括调用存储过程的语法、调用存储函数的语法，以及存储过程和存储函数的调用实例。

10.2.1调用存储过程

存储过程是通过CALL语句进行调用的，语法如下:CALL sp_name([parameter...]])
CALL语句调用一个先前用CREA TE PROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。

10.2.2调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

10.3 查看存储过程和函数

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREA TE语句来查看，也可直接从系统的infbrmation_schema数据库中查询。本节将通过实例 来介绍这3种方法。

10.3.1使用SHOW STATUS语句查看存储过程和函数的状态

SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下:SHOW {PROCEDURE I FUNCTION} STATUS [LIKE 'pattern']
这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用 的语句，所有存储程序或存储函数信息都被列出。PROCEDURE和FUNCTION分别表示查看存储过程和函数;LIKE语句表示匹配存储过程或函数的名称。

10.3.2使用SHOW CREATE语句查看存储过程和函数的定义

除了 SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和函数的状态。
SHOW CREATE {PROCEDURE I FUNCTION} sp_name
这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重 新创建己命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函 数;sp name参数表示匹配存储过程或函数的名称。

10.3.3 从information_schema.Routines表中查看存储过程和函数的信息 MySQL中存储过程和函数的信息存储在information schema数据库下的Routines表中。

可以通过查询该表的记录来查询存储过程和函数的信息。
其基本语法形式如下:SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME= * sp一name f;
其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称;sp_name参数表示存储 过程或函数的名称。

10.4 修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，本节将介绍如何使用ALTER语句修 改存储过程和函数。
语法如下: ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
其中，sp_name参数表示存储过程或函数的名称;characteristic参数指定存储函数的特性， 可能的取值有:

• CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句。
• NO SQL表示子程序中不包含SQL语句。
• READS SQL DATA表示子程序中包含读数据的语句。
• MODIFIES SQL DATA表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER丨INVOKER }指明谁有权限来执行。
• DEFINER表示只有定义者自己才能够执行。
• INVOKER表示调用者可以执行。
• COMMENT 'string'表示注释信息。
  修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

10.5 删除存储过程和函数

删除存储过程和函数，可以使用DROP语句
语法结构如下:DROP {PROCEDURE | FUNCTION} [it EXISTS] sp_name
这个语句被用来移除一个存储过程或函数。sp_name为要移除的存储过程或函数的名称。
IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS查看的警告。

10.6 综合案例——创建存储过程和函数

10.7 专家解惑

疑问1: MySQL存储过程和函数有什么区別?
在本质上它们都是存储程序。函数只能通过return语句返回单个值或者表对象;而存储过 程不允许执行return语句，但是可以通过out参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等;而存储过程的限制相对就比较少。函数可 以嵌入在SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分调用;而存储过程 一般是作为一个独立的部分来执行。

疑问2:存储过程中的代码可以改变吗?
目前，MySQL还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用DROP语句删除之后，再重新编写代码，或者创建一个新的存储过程。

疑问3:存储过程中可以调用其他存储过程吗?
存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程，当然在存储过程中也可以使用CALL语句调用其他存储过程，但是不能使用DROP语句删除其他存储过程。

疑问4:存储过程的参数不要与数据表中的字段名相同。
在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出现无法预期的结果。

疑问5:存储过程的参数可以使用中文吗?
一般情况下，可能会出现存储过程中传入中文参数的情况，例如某个存储过程根据用户的名字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时候，在后面加上character set gbk不然调用存储过程使用中文参数会出错，比如定义userlnfo存储过程 代码如下:
CREATE PROCEDURE use Info (IN u__name VARCHAR (50) character set gbk, OUT u—age INT)

第11章 视图

11.1视图概述

视图是 从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。从MySQL 5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。

11.1.1视图的含义

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。
视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化;同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

通过DESC命令可以查看表的设计，可以获得字段、字段的定义、是否为主键、是否为 空、默认值和扩展信息。
视图提供了一个很好的解决方法，创建视图的信息来自表的部分信息，只取需要的信息。这样既能满足要求也不破坏表原来的结构。

11.1.2视图的作用

与直接从数据表中读取相比，视图有以下优点:

1.简单化
看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2.安全性
通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子集上:

• 使用权限可被限制在基表的行的子集上。
• 使用权限可被限制在基表的列的子集上。
• 使用权限可被限制在基表的行和列的子集上。
• 使用权限可被限制在多个基表的连接所限定的行上。
• 使用权限可被限制在基表中的数据的统计汇总上。
• 使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。

3.逻辑数据独立性
视图可帮助用户屏蔽真实表结构变化带来的影响。

11.2 创建视图

视图中包含了 SELECT查询的结果，因此视图的创建基于SELECT语句和已存在的数据表，视图可以建立在一张表上，也可以建立在多张表上。本节主要介绍创建视图的方法。

11.2.1创建视图的语法形式

创建视图使用CREATE VIEW语句，基本语法格式如下:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE 丨 TEMPTABLE}] VIEW view_name [(column一list)]
AS SELECT一statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

其中，CREATE表示创建新的视图;REPLACE表示替换已经创建的视图;ALGORITHM表示视图选择的算法;view name为视图的名称，column list为属性列;SELECT statement 表示SELECT语句 WITH [CASCADED丨LOCAL] CHECK OPTION参数表示视图在更新时保证在视图的权限范围之内。
ALGORITHM的取值有3个，分别是 UNDEFINED | MERGE | TEMPTABLE, UNDEFINED 表示MySQL将自动选择算法;MERGE表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分;TEMPTABLE表示将视图的结果存入临时表，然后
用临时表来执行语句。
CASCADED与LOCAL为可选参数，CASCADED为默认值，表示更新视图时要满足所有相关视图和表的条件;LOCAL表示更新视图时满足该视图本身定义的条件即可。
该语句要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限。视图属于数据库。在默认情况下，将在当前数据库创建新视图。要想在给定数据库中明确创建视图，创建时应将名称指定为db_name.view_name。

11.2.2在单表上创建视图

MySQL可以在单个数据表上创建视图。

11.2.3在多表上创建视图

MySQL中也可以在两个或者两个以上的表上创建视图，可以使用CREATE VIEW语句实现。

11.3 查看视图

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限， MySQL数据库下的user表中保存着这个信息。查看视图的方法包括:DESCRIBE. SHOW TABLE STATUS和SHOW CREATE VIEW,本节将介绍查看视图的各种方法。

11.3.1使用DESCRIBE语句查看视图基本信息

DESCRIBE可以用来查看视图，具体的语法如下:DESCRIBE 视图名;
DESCRIBE 一般情况下都简写成DESC,输入这个命令的执行结果和输入DESCRIBE的 执行结果是一样的。

11.3.2 使用SHOW TABLE STATUS语句查看视图基本信息

查看视图的信息可以通过SHOW TABLE STATUS的方法，具体的语法如下:SHOW TABLE STATUS LIKE '视图名';

11.3.3 使用SHOW CREATE VIEW语句查看视图详细信息

使用SHOW CREATE VIEW语句可以查看视图详细定义，语法如下:SHOW CREATE VIEW 视图名;

11.3.4 在views表中查看视图详细信息

在MySQL中，infbrmation schema数据库下的views表中存储了所有视图的定义。
通过对views表的查询，可以查看数据库中所有视图的详细信息，查询语句如下:SELECT * FROM information_schema.views;

11.4 修改视图

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化的时候，可以通过修改视图来保持与基本表的一致性。MySQL中通过CREATE OR REPLACE VIEW语句和 ALTER语句来修改视图。

11.4.1 使用 CREATE OR REPLACE VIEW 语句修改视图

MySQL中如果要修改视图，使用CREATE OR REPLACE VIEW语句，语法如下:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED i I MERGE 1 i TEMPTABLE}] VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

可以看到，修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，修改语句对视图进行修改;当视图不存在时，创建视图。

11.4.2使用ALTER语句修改视图

ALTER语句是MySQL提供的另外一种修改视图的方法，语法如下:

ALTER [ALGORITHM = {UNDEFINED | MERGE I TEMPTABLE}] VIEW view一name [ (column_list)]
AS SELECT一statement
[WITH [CASCADED I LOCAL] CHECK OPTION]

这个语法中的关键字和前面视图的关键字是一样的，这里就不再介绍。

11.5更新视图

更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录， 实际上是对其基本表增加或者删除记录。
本节将介绍视图更新的3种方法:INSERT. UPDATE 和 DELETE。

当视图中包含有如下内容时，视图的更新操作将不能被执行:

• 视图中不包含基表中被定义为非空的列。
• 在定义视图的SELECT语句后的字段列表中使用了数学表达式。
• 在定义视图的SELECT语句后的字段列表中使用聚合函数。
• 在定义视图的SELECT语句中使用了 DISTINCT, UNION, TOP, HAVING 子句。

11.6 删除视图

当视图不再需要时，可以将其删除，删除一个或多个视图可以使用DROP VIEW语句
语法如下:

DROP VIEW [IF EXISTS] 
    view一name [,view一name]
[RESTRICT 1 CASCADE]

其中，view_name是要删除的视图名称，可以添加多个需要删除的视图名称，各个名称之间使用逗号分隔开。删除视图必须拥有DROP权限。

11.7 综合案例--视图应用

11.8 专家解惑

专家解惑 疑问:MySQL中视图和表的区别以及联系是什么?
1.两者的区别

• 视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。
• 视图没有实际的物理记录，而表有。
• 表是内容，视图是窗口。
• 表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能用创建的语句来修改。
• 视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。
• 表属于全局模式中的表，是实表:视图属于局部模式的表，是虚表。
• 视图的建立和删除只影响视图本身，不影响对应的基本表。
  2.两者的联系
  视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有记录)都来自基本表，它依据基本表存在而存在。一个视 图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

第12章 MySQL触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATAE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对 象，当表上出现特定事件时，将激活该对象。本章通过实例来介绍触发器的含义、如何创建触发器、查看触发器、触发器的使用方法以及如何删除触发器。

12.1创建触发器

触发器(trigger)是个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义 的事件发生的时候，就会被MySQL自动调用。触发器可以査询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。

12.1.1创建只有一个执行语句的触发器

创建一个触发器的语法如下:CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt
其中trigger name标识触发器名称，用户自行指定:trigger_time标识触发时机，可以指定为before或after; trigger_event 标识触发事件包括INSERT、UPDATE和DELETE; table_name标识建立触发器的表名，即在哪张表上建立触发器;trigger_stmt是触发器执行语句。

12.1.2创建有多个执行语句的触发器

创建多个执行语句的触发器的语法如下:

CREATE TRIGGER trigger一name trigger_time trigger_event ON tbl_name FOR EACH ROW
BEGIN
    语句执行列表
END

其中trigger_name标识触发器的名称，用户自行指定;trigger_time标识触发时机，可以 指定为 before 或 after; trigger_event 标识触发事件，包括 INSERT、UPDATE 和 DELETE; tbl_name标识建立触发器的表名，即在哪张表上建立触发器;触发器程序可以使用BEGIN和 END作为开始和结束，中间包含多条语句。

12.2 查看触发器

查看触发器是指查看数据库中己存在的触发器的定义、状态和语法信息等。可以通过命令来查看己经创建的触发器。本节将介绍两种查看触发器的方法，分别是:SHOW TRIGGERS 和在triggers表中查看触发器信息。

12.2.1 SHOW TRIGGERS语句查看触发器信息

通过SHOW TRIGGERS查看触发器的语句如下:SHOW TRIGGERS;
SHOW TRIGGERS语句查看当前创建的所有触发器信息，在触发器较少的情况下，使用该语句会很方便。如果要查看特定触发器的信息，可以直接从infbrmation_schema数据库中的triggers表中查找。

12.2.2在triggers表中查看触发器信息

在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT来查看
具体的语法如下:SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE CONDITION;
也可以不指定触发器名称，这样将查看所有的触发器，命令如下:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS \G

12.3 触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。
触发程序与表相关，当对表执行INSERT. DELETE或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前，或在更新每一行之后激活触发程序。

12.4删除触发器

使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器
基本语法格式如下:DROP TRIGGER [schema_name.]trigger_name
其中，schema_name表示数据库名称，是可选的。如果省略了 schema,将从当前数据库中舍弃触发程序;trigger_name是要删除的触发器的名称。

12.6 综合案例——触发器的使用

12.7 专家疑惑

疑问1:使用触发器时须特别注意。
在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对表account创建了一个BEFORE INSERT触发器，那么如果对表account再次创建一个BEFORE INSERT触发器，MySQL将会报错，此时，只可以在表account上创建AFTER INSERT或者 BEFORE UPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

疑问2:及时删除不再需要的触发器。
触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响新的数据的完整性。因此，要将不再使用的触发器及时删除。