子程序

子程序包括存储过程、自定义函数、游标、触发器。可以被编译和存储在数据库中，它具有模块化、重用性、可维护性、可扩展性、安全性等特点。其目的是完成特定的功能，能被程序和客户端工具直接调用。子程序也属于数据库对象，可以被授权能否执行。

存储过程

存储过程是一种存储复杂程序，方便外部程序调用的数据库对象。是为了完成某个特定功能的 SQL 语句集合，用户可以通过存储过程的名字和参数进行调用。MySQL 从 5.0 版本开始支持存储过程（Stored Procedure）。

创建和删除存储过程

创建存储过程，其关键语法如下：

CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
……
END

对存储过程进行参数定义时，多个参数用 ， 分割，共有三种参数类型：IN，OUT，INOUT：

• IN: 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不会影响调用环境的数据值；
• OUT: 该值可在存储过程内部被改变，同时引起调用环境中数据值的改变；（有out需要 @变量 接收）
• INOUT: 调用时指定，兼具 IN 和 OUT 类型参数的特点。

以 BEGIN 和 END 对过程体的开始和结束进行标识。

需要强调一点，MySQL 中存储过程默认以 ; 作为结束符，如果不改变结束符，编译器会把存储过程当成 SQL 语句进行处理，因此编译过程会报错。所以要事先用 DELIMITER // 声明当前的分隔符，其目的是让编译器把两个 // 之间的内容当作一个存储过程，使用 DELIMITER ; 则恢复结束符为 ; 。

删除存储过程使用 DROP PROCEDURE 语句，其语法如下：

DROP PROCEDURE 存储过程名;

函数

自定义函数是一种对 MySQL 的扩展，其用法和内置函数相同。在前面文章，我们使用的函数是 MySQL 内置函数（已经写好的），直接调用即可完成某个特定功能，下面将会介绍 MySQL 自定义函数。

创建和删除自定义函数

创建自定义函数使用 CREATE FUNCTION 语句，语法如下：

CREATE FUNCTION 函数名([变量名1 变量类型1, ……, 变量名n 变量类型n]) RETURNS 数据类型
BEGIN
sql语句;
RETURN 值;
END;

MySQL 安装完成后默认不允许创建自定义函数，需要在 my.cnf 配置文件中增加 log-bin-trust-FUNCTION-creators=1，然后重启数据库，使其具有创建函数的权限。也可以在 MySQL 命令行中输入以下命令来解决。 SET GLOBAL log_bin_trust_function_creators = 1;。

删除自定义函数使用 DROP FUNCTION 语句，其语法如下：

DROP FUNCTION 函数名;

需要注意，删除自定义函数时，函数名后面不能加括号

游标

游标（CURSOR）是一个存储在 MySQL 服务器上面的数据库查询机制，类似于数组的下标。使用游标后，可以逐步提取查询结果。

使用游标需要注意以下几点：

1. 声明游标之后，必须先打开游标才能使用；
2. 在游标结束之后，要关闭游标。

其使用的基本步骤如下：

1. 声明游标，其语法如下；

   declare 游标名 cursor for select_statement
   

2. 打开游标

   open 游标名
   

3. 从游标中取值，使用 fetch 进行取值，语法如下：

   fetch 游标名 into var1,var2,……
   

   利用fetch将取到的一条记录中的字段赋值给多个变量。

4. 关闭游标

   close 游标名
   

触发器（TRIGGER）

是一种特殊的存储过程，它在插入、修改或删除表中的数据时触发执行，拥有更精细、更复杂的数据控制能力。MySQL 从 5.0 版本开始支持触发器。

举个例子，现有用户表和日志表。当一个用户被创建时，我们用日志来记录用户的创建过程。如果不使用触发器，则需要手动编写程序来实现；而一旦使用触发器，我们可以在信息插入用户表后，立刻触发对日志表的操作，使其记录创建用户的信息。

创建和删除触发器

创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON table_name 
FOR EACH ROW 
trigger_statement;

参数解释：

• trigger_name：触发器名称，自己定义；
• trigger_time：触发时机，只有两个值，before（某事件之前），after（某事件之后）；
• trigger_event：触发事件，取值 INSERT（插入）、UPDATE（更新）、DELETE（删除）；
• table_name：需要建立触发器的表名；
• trigger_statement：触发器程序体，一条 SQL 语句或存储过程等；

查看触发器

使用 SHOW TRIGGERS; 命令查看所有触发器，因为触发器有自己的保存机制，显示出来的信息量比较大

删除触发器使用的语法如下：

DROP TRIGGER 触发器名;

视图

视图是从一个或多个表中糅合出来的虚拟表。一个视图并不包含真实的数据，它提供了另一个视角去查看或改变表中的数据。

打个比喻：把视图想象成一扇窗户，通过窗户往里看，我们只能看到一部分，而这部分就是数据库系统允许你看到的数据。而不允许你看到的内容会被遮挡住，让你不可见。

使用视图可以提高我们对数据的操作效率，同时增加安全性：

• 提高效率：将经常使用复杂查询定义为视图，由于对视图的权限、语法解析都会被存储，就避免了重复解析；
• 增加数据安全性：通过视图，用户只能查询和更改指定的数据；
• 提高表的逻辑独立性：看到的视图可能来源于一张表或多张表的局部，屏蔽了原有表结构变化带来的影响。

总之，使用视图的主要作用就是保障数据的安全性，同时提高查询效率。

创建视图

创建视图使用的语句是 CREATE VIEW，完整语法看上去比较复杂，大家可以到官网上去查看。

CREATE VIEW viewname[column1, ……, columnn]
AS
SELECT 语句;

查询视图

视图是一种虚拟的表，也符合 DQL 操作，视图的查询和表的查询相同，查看 city_VIEW 视图中的所有数据，其 SQL 语句如下：

SELECT * FROM city_view;

删除视图

使用 DELETE FROM 子句可以删除视图，其语法如下：

DELETE FROM 视图名 [WHERE子句];

更新视图数据

把 id 为 2 的城市人口更新为 100000。其 SQL 语句如下：

UPDATE city_view SET population = 100000 WHERE id = 2;

查看视图和基表，结果如下：

SELECT * FROM city_view WHERE id = 2;

查看基表数据如下：

SELECT * FROM city WHERE id = 2;

插入视图数据

给视图插入数据和表插入数据一样，使用 INSERT INTO 语句，我们在视图中插入一条 id 为 4080，名字为 test ，人口数量为 500 的记录，其 SQL 语句如下：

INSERT INTO city_view VALUES(4080, 'test', 500);

序列

前面我们在介绍主键的时候知道，主键必须是唯一的。为了方便管理主键同时满足主键唯一性要求，我们把主键设置为自增长。实现自增长需要用到序列。

序列就是一组有特定变化规律的整数，其最主要的用途就是创建主键，确保主键的唯一性。序列是一个数据库对象，独立于表进行存储，可以为多个表使用。

目前 MySQL 是不支持类似建表或视图的方式来直接创建序列对象（Oracle 支持）。虽然有 auto_increment 来实现自增长，但不能设置步长、起始值、是否循环等。最重要的是，在 MySQL 中一张表只能有一个字段设置为自增长，如果我们需要两个或以上的字段实现自增长该怎么办呢？ 需要做一些间接处理。

新建表时设置起始值

下面我们在 demo 数据库下新创建一个表 demo2：

CREATE TABLE demo2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)AUTO_INCREMENT=1000;

插入新的值：

INSERT INTO demo2(name) VALUES('小明');
SELECT * FROM demo2;

可以看到起始值为 1000。

创建表后设置起始值

修改 demo2 表的起始值为 1500：

ALTER TABLE demo2 AUTO_INCREMENT = 1500;

这时如果再插入新值：

INSERT INTO demo2(name) VALUES('小红');
SELECT * FROM demo2;

可以看到新的 id 起始值已经变为 1500 了 使用 AUTO_INCREMENT 属性时我们需要注意：

• 每一个表中只能有一个 AUTO_INCREMENT 列
• 同时该列必须要有 NOT NULL 属性来进行约束，在 MySQL 中会自动将 NOT NULL 约束隐式添加到列中。
• AUTO_INCREMENT 列必需建立索引，可以为 PRIMARY KEY 或 UNIQUE 索引。

AUTO_INCREMENT 列具有以下属性：

• 默认起始值为 1，当在该列中插入 NULL 值或是在 INSERT 语句中忽略该值时，它的起始值将会自增 1。
• 如果使用 DELETE 语句删除最后插入的行，则 MySQL 可能会也可能不会重复使用删除的序列号，具体取决于表的存储引擎。如 MySQL 默认的 InnoDB 就不会重用序列号。
• 如果使用 UPDATE 语句将 AUTO_INCREMENT 列中的值更新为已经存在的值，且该列具有唯一索引，则 MySQL 将发出重复键错误。
• 获得最后生成的序列号可以使用 LAST_INSERT_ID() 函数。

索引

所有数据操作可简单分为读操作（获取数据）和写操作（插入数据、修改数据、删除数据）。一般情况下，读写比例在 10:1 左右，大量的读操作给数据库性能带来不小的考验。因此，对查询语句的优化是重中之重，优化的关键就是利用好索引。

索引在 MySQL 中又叫作 键 ，英文名 key ，是存储引擎用于快速找到记录的一种数据结构。索引对于性能的提升非常关键，尤其是当表中的数据量越来越庞大的时候。我们前面介绍约束使用到的 primary key ，其实就是一种索引，叫做主键索引。

举个简单的例子：我们把数据库比作汉语字典，那么索引就是这本字典的音序表，通过音序表可以快速查找到需要的汉字。索引的目的就是为了提高查询效率。 在 MySQL 中常用的索引可以分为三类，分别是：普通索引、唯一索引、联合索引。

普通索引

普通索引使用关键字 INDEX 定义，根据建立索引的时机不同，书写方式有细微差别。分为以下 3 种情况：

• 创建表的时候创建索引；
• 创建表后创建索引；
• 修改表的时候添加索引。

唯一索引

唯一索引不仅加速查找，还具有约束性。

• 主键索引 primary key
• 唯一键索引 unique

联合索引

联合索引即为索引同时设置多个字段。

• primary key(id,name)，联合主键索引
• index(id,name)，联合普通索引 在建表的时候创建索引。其语法如下：

INDEX 索引名(字段名)

给已经存在的表中某字段添加索引，其语法如下：

CREATE INDEX 索引名 ON 表名(字段名);

修改表时创建索引，其语法如下：

ALTER TABLE 表名 ADD INDEX 索引名(字段名)

查看索引，借助表信息查看表中是否存在索引，其语法如下：

SHOW CREATE TABLE 表名;

查看 teacher 表中的索引，其 SQL 语句如下：

SHOW CREATE TABLE teacher;

删除索引，使用的关键字是DROP INDEX … on …，其语法如下：

DROP INDEX 索引名 ON 表名;