数据库编程

为了提高SQL语句的重用性，MySQL可以将频繁使用的业务逻辑封装成程序进行存储，这类程序主要包括存储过程、函数、触发器等。MySQL在SQL标准的基础上，扩展了一些程序设计语言的元素，如变量、流程控制语句等。这些程序设计语言的元素可以让程序更加健全，以提高数据库系统的性能。

存储过程

存储过程是一组可以完成特定功能的SQL语句集合，它可以将常用或复杂的操作封装成一个代码块存储在数据库服务器中。

创建存储过程

创建语法：

CREATE PROCEDURE 存储过程名 ([[IN | OUT | INOUT] 参数名称 参数类型]) 
[characteristic ...] routine_body

在上述格式中，参数名称之前有IN、OUT、INOUT三个选项，它们可以指定参数的来源和用途。这3个选项的具体含义如下所示。

IN：表示输入参数，该参数需要在调用存储过程时传入。

OUT：表示输出参数，初始值为NULL，它是将存储过程中的值保存到OUT指定的参数中，返回给调用者。

INOUT：表示输入输出参数，既可以作为输入参数也可以作为输出参数。

routine_body：表示存储过程中的过程体，是包含在存储过程中的有效的SQL例程语句，以BEGIN表示过程体的开始，以END表示过程体的结束。如果过程体中只有一条SQL语句，则可以省略BEGIN和END的标志。

characteristic：表示存储过程中的例程可以设置的特性，特性的值如下表所示。

特性值	描   述
COMMENT '注释信息'	为存储过程的例程设置注释信息
LANGUAGE SQL	表示编写例程所使用的语言，默认仅支持SQL
[NOT] DETERMINISTIC	表示例程的确定性，如果一个例程对于相同的输入参数总是产生相同的结果， 那么它就被认为是“确定性的” ，否则就是“非确定性的”。
CONTAINS SQL	表示例程包含SQL语句，但不包含读或写数据的语句
NO SQL	表示例程中不包含 SQL 语句
READS SQL DATA	表示例程中包含读数据的语句
MODIFIES SQL DATA	表示例程中包含写数据的语句
SQL SECURITY  DEFINER	表示只有定义者才有权执行存储过程
SQL SECURITY INVOKER	表示调用者有权执行存储过程

 例如，员工管理系统中经常需要查询数据库ems的员工表emp中工资大于指定金额的员工信息，将这个需求编写成存储过程，以提高数据处理的效率，具体SQL语句如下所示。

DELIMITER //

CREATE PROCEDURE pro_emp(IN tmp_money decimal(7,2))
BEGIN
SELECT * FROM emp WHERE sal > tmp_money;
END //

DELIMITER ;

查看存储过程

存储过程创建之后，用户可以使用SHOW PROCEDURE STATUS语句和SHOW CREATE PROCEDURE语句分别显示存储过程的状态信息和创建信息，也可以在数据库information_schema中的Routines数据表查询存储过程的信息。

（1）使用SHOW PROCEDURE STATUS语句显示存储过程的状态信息

SHOW PROCEDURE STATUS语句可以显示存储过程的状态信息，如存储过程名称、类型、创建者及修改日期。SHOW PROCEDURE STATUS语句显示存储过程状态信息的基本语法格式如下。

SHOW PROCEDURE STATUS [LIKE 'pattern']

 上述语法格式中，PROCEDURE表示存储过程；LIKE 'pattern'表示匹配存储过程的名称。

下面通过案例演示SHOW PROCEDURE STATUS语句的使用。例如，显示数据库ems下存储过程pro_emp的状态信息，具体SQL语句如下所示。

SHOW PROCEDURE STATUS LIKE 'pro_emp' \G

运行结果： 

mysql> SHOW PROCEDURE STATUS LIKE 'pro_emp' \G
*************************** 1. row ***************************
                  Db: ems
                Name: pro_emp
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-11-11 12:25:27
             Created: 2022-11-11 12:25:27
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.74 sec)

 （2）使用SHOW CREATE PROCEDURE语句显示存储过程的创建信息

使用SHOW CREATE PROCEDURE语句可以显示存储过程的创建语句等信息，其基本语法格式如下。

SHOW CREATE PROCEDURE 存储过程名;

下面通过案例演示SHOW CREATE PROCEDURE语句的使用。例如，显示数据库ems下存储过程pro_emp的创建信息，具体SQL语句如下所示。

SHOW CREATE PROCEDURE pro_emp \G

 运行结果：

mysql> SHOW CREATE PROCEDURE pro_emp \G
*************************** 1. row ***************************
           Procedure: pro_emp
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_emp`(IN tmp_money decimal(7,2))
BEGIN
SELECT * FROM emp WHERE sal > tmp_money;
END
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

（3）从information_schema.Routines表中查看存储过程的信息

在MySQL中，存储过程的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录获取存储过程的信息，查询语句如下。

SELECT * FROM  information_schema.Routines
WHERE ROUTINE_NAME='pro_emp' AND ROUTINE_TYPE='PROCEDURE'\G

 需要注意的是，information_schema数据库下的Routines表，存储着所有存储过程的定义。使用SELECT语句查询Routine表中某一存储过程的信息时，一定要使用ROUTINE_NAME字段指定存储过程的名称，否则将查询出所有存储过程的定义。如果有存储过程和函数名称相同，则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

调用存储过程

基本语法：

CALL [数据库名称.]存储过程名称 ([实参列表]);

上述语法格式中，实参列表传递的参数需要与创建存储过程的形参相对应，当形参被指定为IN时，实参值可以为变量或者是具体的数据；当形参被指定为OUT或INOUT时，调用存储过程传递的参数必须是一个变量，用于接收返回给调用者的数据。

 例如，验证存储过程pro_emp的效果，调用数据库ems中的存储过程pro_emp，查询数据库ems的员工表emp中工资大于3000的员工信息，具体SQL语句及执行结果如下所示。

CALL pro_emp(3000);

mysql> CALL pro_emp(3000);
+-------+-------+------+------+---------+---------+--------+
| empno | ename | job  | mgr  | sal     | comm    | deptno |
+-------+-------+------+------+---------+---------+--------+
|  9977 | 唐僧  | 人事 | 9982 | 4000.00 | 1900.00 |     40 |
+-------+-------+------+------+---------+---------+--------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.08 sec)

 修改存储过程

基本语法：

ALTER PROCEDURE 过程名称 [characteristic ...];

需要注意的是，上述语法格式不能修改存储过程的参数，只能修改存储过中例程的特征值，可修改的特征值为创建存储过程时所包含的特征值除“[NOT] DETERMINISTIC”之外的其他8个。

根据修改存储过程的语法格式，修改ems中的存储过程pro_emp的特征值，将执行存储过程pro_emp的执行权限从定义者修改为调用者，并且添加注释信息，具体SQL语句及执行结果如下所示。

ALTER PROCEDURE pro_emp
    SQL SECURITY INVOKER
    COMMENT '统计工资大于指定金额的员工个数';

 通过查询存储过程状态的语句进行验证，具体SQL语句及执行结果如下所示。

mysql> SHOW PROCEDURE STATUS LIKE 'pro_emp' \G
*************************** 1. row ***************************
                  Db: ems
                Name: pro_emp
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2022-11-11 14:07:43
             Created: 2022-11-11 12:25:27
       Security_type: INVOKER
             Comment: 统计工资大于指定金额的员工个数
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

删除存储过程

基本语法：

DROP PROCEDURE [IF EXISTS] 存储过程名称;

 IF EXISTS用于判断要删除的存储过程是否存在，如果要删除的存储过程不存在，它可以产生一个警告避免发生错误。IF EXISTS产生的警告可以使用SHOW WARNINGS进行查询。

例如，删除数据库ems中的存储过程pro_emp：

DROP PROCEDURE IF EXISTS pro_emp;

查询 information_schema数据库下Routines表中存储过程pro_emp的记录，验证存储过程pro_emp是否删除成功：

SELECT * FROM  information_schema.Routines
    WHERE ROUTINE_NAME='pro_emp' AND ROUTINE_TYPE='PROCEDURE'\G

 结果：Empty set (0.00 sec)

存储函数

创建存储函数

MySQL支持函数的使用，MySQL中的函数可以分为两种，一种是内置函数，一种是自定义函数。在MySQL中，通常将用户自定义的函数称为存储函数，存储函数和MySQL内置函数性质相同，都用于实现某种功能。

存储函数和存储过程类似，都是存储在数据库中的一段SQL语句的集合，它们的区别在于存储过程没有直接返回值，主要用于执行操作，而存储函数可以通过return语句返回数据。创建存储函数的基本语法格式如下所示。

CREATE FUNCTION func_name ([func_parameter[...]])
RETURNS type
[characteristic ...] 
Routine_body

上述语法格式中，func_name表示存储函数的名称；func_parameter表示存储函数的参数列表，其形式和存储过程相同；RETURNS type指定函数返回值的类型；characteristic 参数指定存储函数的特征，该参数的取值与存储过程是一样的；Routine_body表示包含在存储函数中的SQL语句块，和存储过程中的SQL语句块一样，可以用 BEGIN...END来标识SQL代码的开始和结束。Routine_body中必须包含一个RETURN value语句，其中value的数据类型必须和定义的返回值类型一致。

例如，员工管理系统中经常需要根据输入员工的姓名返回对应的工资信息，将这个需求编写成存储函数，以提高数据处理的效率，具体SQL语句如下所示。

DELIMITER &&

CREATE FUNCTION func_emp(emp_name VARCHAR(20))
RETURNS  decimal(7,2)
BEGIN
   RETURN (SELECT sal FROM emp WHERE ename=emp_name );
END &&

DELIMITER ;

 可能会出现如下错误提示：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA 
in its declaration and binary logging is enabled (you *might* want to use the less 
safe log_bin_trust_function_creators variable)

出现上述错误的原因是mysql的设置默认不允许创建自定义函数。针对上述错误，可以先更改对应的配置，再进行自定义函数的创建，更改配置的语句如下。

SET GLOBAL log_bin_trust_function_creators = 1; 

查看存储函数

存储过程创建之后，用户可以使用SHOW FUNCTION STATUS语句和SHOW CREATE FUNCTION语句分别显示存储函数的状态信息和创建信息，也可以在数据库information_schema中的Routines数据表查询存储函数的信息，下面对这3个语句查看存储函数的信息进行讲解。

（1）使用SHOW FUNCTION STATUS语句显示存储函数的状态信息，基本语法格式如下。

SHOW FUNCTION STATUS [LIKE 'pattern'];

 （2）使用SHOW CREATE FUNCTION语句显示存储函数的创建信息，基本语法格式如下。

SHOW CREATE FUNCTION 存储函数名;

（3）在information_schema.Routines表中查看存储函数的信息，基本语法格式如下。

SELECT * FROM  information_schema.Routines
WHERE ROUTINE_NAME='存储函数名' AND ROUTINE_TYPE='FUNCTION'\G

 从上述3种查看语法格式可以看出，查看存储函数和查看存储过程的区别在于，存储过程的查看使用PROCEDURE 关键字，存储函数的查看使用FUNCTION关键字。

下面以SHOW FUNCTION STATUS语句为例，查看数据库ems中，存储函数func_emp的状态信息，具体SQL语句如下所示。

SHOW FUNCTION STATUS LIKE 'func_emp' \G

结果如下： 

mysql> SHOW FUNCTION STATUS LIKE 'func_emp' \G
*************************** 1. row ***************************
                  Db: ems
                Name: func_emp
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2022-11-16 14:08:20
             Created: 2022-11-16 14:08:20
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (1.80 sec)

 调用存储函数

和存储过程一样，如果想要让创建的存储函数在程序中发挥作用，需要调用才能使其执行，存储函数的调用和MySQL内置函数的调用方式类似，基本语法格式如下所示。

SELECT [数据库名.]函数名1（实参列表）[函数名2 (实参列表)…];

上述语法格式中，数据库名是可选参数，指调用存储函数时，函数所属的数据库的名称，如不指定默认为当前数据库；实参列表中的值需和定义存储函数时设置的类型一致。

通过案例演示存储函数的调用，例如，调用ems中存储函数func_emp，具体SQL语句如下所示。

SELECT func_emp('刘一');

因为不存在“刘一”这个人，所以结果为null，如下：

mysql> SELECT func_emp('刘一');
+------------------+
| func_emp('刘一') |
+------------------+
|             NULL |
+------------------+
1 row in set (1.82 sec)

 删除存储函数

MySQL中，如果需要删除存储函数，可以使用DROP FUNCTION语句，删除存储函数的语法格式如下所示。

DROP FUNCTION [IF EXISTS] 函数名称;

上述语法格式中，IF EXISTS是可选参数，用于防止因删除不存在的存储函数而引发错误。

例如，删除数据库ems中的存储函数func_emp，具体SQL语句及执行结果如下所示。

DROP FUNCTION IF EXISTS func_emp;

 查询 information_schema数据库下Routines表中存储函数func_emp的记录来确认是否删除成功，具体SQL语句及查询结果如下所示。

SELECT * FROM  information_schema.Routines
    WHERE ROUTINE_NAME='func_emp' AND ROUTINE_TYPE='FUNCTION'\G

变量

变量就是在程序执行过程中，其值可以改变的量。在MySQL中，可以利用变量存储程序执行过程中涉及的数据，如输入的值、计算结果等。根据变量的作用范围可以将其划分为系统变量、用户变量和局部变量。

系统变量

系统变量又分为全局（GLOBAL）变量和会话（SESSION）变量，其中全局变量指的是MySQL系统内部定义的变量，对所有MySQL客户端都有效。默认情况下，MySQL会在服务器启动时为全局变量初始化默认值，用户也可以通过配置文件完成系统变量的设置。每次建立一个新的连接时，MySQL会将当前所有全局变量复制一份作为会话变量，会话变量只对当前的数据库连接生效。 在程序执行过程中，变量的值会不断的变化，下面分别介绍系统变量的查看与修改。

1.查看系统变量

在MySQL中可以通过SHOW语句显示所有的系统变量，其语法格式如下所示。

SHOW [GLOBAL | SESSION] VARIABLES [LIKE '匹配字符串' | WHERE 表达式];

 上述语法格式中，GLOBAL和SESSION是可选参数，其中GLOBAL用于显示全局变量，SESSION用于显示会话变量，如果不显式指定的话，默认值为SESSION。

下面通过一个案例演示系统变量的查看，例如，显示变量名以auto_inc开头的所有系统变量，具体SQL语句如下所示。

mysql> SHOW  VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (1.84 sec)

auto_increment_increment表示自增长字段每次递增的量，auto_increment_offset表示自增长字段从哪个数开始。

 2.修改系统变量

在MySQL中，系统变量可以通过SET语句进行修改，修改的语法格式如下所示。

SET   [GLOBAL | @@GLOBAL.| SESSION |@@SESSION.] 系统变量名 = 新值;

上述语法格式中，系统变量名使用GLOBAL或@@GLOBAL.修饰时，修改的是全局变量，使用SESSION或@@SESSION.修饰时，修改的是会话变量，不显式指定修饰的关键字时，默认修改的是会话变量；新值指的是为系统变量设置的新值。

通过案例演示系统变量的修改。例如，如果想要将自增字段从2开始自增，可以将系统变量auto_increment_offset的值修改为2，具体SQL语句如下所示。

SET auto_increment_offset = 2;

 在当前客户端窗口使用SHOW语句查看系统变量auto_increment_offset的值，具体SQL语句如下所示。

SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| auto_increment_offset | 2     |
+-----------------------+-------+
1 row in set (0.95 sec)

 新打开一个客户端，在新打开的客户端中使用SHOW语句查看系统变量auto_increment_offset的值，具体SQL语句如下所示。

SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| auto_increment_offset | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)

 从上述显示结果可以看出，新打开的客户端中显示的系统变量auto_increment_offset的值并没有修改，说明上述语句修改的是会话变量，修改仅对执行操作的客户端连接有效，并不影响其他客户端。

将系统变量auto_increment_offset的值修改为5，具体SQL语句如下所示。

SET GLOBAL auto_increment_offset = 5;

在当前客户端窗口使用SHOW语句查看系统变量auto_increment_offset的值，具体SQL语句及执行结果如下所示。

SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| auto_increment_offset | 2     |
+-----------------------+-------+
1 row in set (0.00 sec)

 从上述显示结果可以看出，当前连接中系统变量auto_increment_offset的值并未修改为5。

在第二个客户端执行该语句：

mysql> SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| auto_increment_offset | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)

 从上述显示结果可以看出，第二个连接中系统变量auto_increment_offset的值并未修改为5。

此时，打开一个新的客户端，在新打开的客户端中使用SHOW语句查看系统变量auto_increment_offset的值，具体SQL语句及执行结果如下所示

SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';

mysql> SHOW  VARIABLES WHERE Variable_name= 'auto_increment_offset';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| auto_increment_offset | 5     |
+-----------------------+-------+
1 row in set (0.00 sec)

从上述显示结果可以看出，新打开的客户端中显示的系统变量auto_increment_offset的值修改为5，说明修改全局变量时，对所有正在连接的客户端无效，只针对重新连接的客户端永久生效。

 用户变量

用户变量指的是用户自己定义的变量，它和连接有关，即用户变量仅对当前用户使用的客户端生效，不能被其他客户端看到和使用。如果当前客户端退出时，该客户端连接的所有用户变量将自动释放。

用户变量由符号@和变量名组成，在使用用户变量之前，需要对用户变量进行定义并赋值。MySQL中为用户变量赋值有以下3种方式。

（1）使用SET语句完成赋值。

（2）在SELECT语句中使用赋值符号:=完成赋值。

（3）使用SELECT…INTO语句完成赋值。

下面在数据库ems下，分别使用上述3种方式演示用户变量的定义和赋值，具体SQL语句和执行结果如下所示。

# 方式1：使用SET语句赋值
mysql> SET @ename='刘';
Query OK, 0 rows affected (0.00 sec)

# 方式2：在SELECT语句中使用赋值符号:=赋值
mysql> SELECT @sal:= sal FROM emp WHERE ename='刘一';
+------------+
| @sal:= sal |
+------------+
|    6000.00 |
+------------+
1 row in set, 1 warning (0.00 sec)

# 方式3：使用SELECT…INTO语句赋值
mysql> SELECT empno,ename,sal FROM emp LIMIT 1
    -> INTO @e_no,@e_name,@e_sal;
Query OK, 1 row affected (0.00 sec)

方式1使用SET语句和=运算符直接为定义的用户变量@ename赋值；

方式2中将查询出的sal字段的值，通过:=为定义的用户变量@sal赋值；

方式3中将SELECT语句查询出的字段的值，通过INTO关键字依次为定义的用户变量@e_no,@e_name,@e_sal赋值。 

为用户变量赋值后，可以通过SELECT语句查询出用户变量的值，具体SQL语句及查询结果如下所示。

mysql> SELECT @ename,@sal,@e_no,@e_name,@e_sal;
+--------+---------+-------+---------+--------+
| @ename | @sal    | @e_no | @e_name | @e_sal |
+--------+---------+-------+---------+--------+
| 刘     | 6000.00 |  9369 | 张三    | 900.00 |
+--------+---------+-------+---------+--------+
1 row in set (0.00 sec)

局部变量

在MySQL中，相对于系统变量和会话变量，局部变量的作用范围仅在语句块BEGIN…END之间，在语句块BEGIN…END之外，局部变量不能被获取和修改。 局部变量使用DECLARE语句定义，定义的基本语法格式如下。

DECLARE 变量名1 [,变量名2…] 数据类型 [DEFAULT 默认值];

上述语法格式中，局部变量的名称和数据类型是必选参数，如果同时定义多个变量，变量名称之间使用逗号（,）分隔，并且多个变量只能共用一种数据类型。DEFAULT是可选参数，用于给变量设置默认值，省略时变量的初始默认值为NULL。

下面根据上述语法格式演示局部变量的使用。例如，在存储函数中创建局部变量，并在函数中返回该局部变量，具体SQL语句如下所示。

DELIMITER &&
CREATE FUNCTION func_var()
RETURNS INT
BEGIN
DECLARE sal INT DEFAULT 1500;
RETURN sal;
END &&

DELIMITER ;

 下面调用存储函数func_var()，具体SQL语句如下所示。

mysql> SELECT func_var();
+------------+
| func_var() |
+------------+
|       1500 |
+------------+
1 row in set (0.00 sec)

下面使用SELECT语句直接查询局部变量sal，具体SQL语句及执行结果如下所示。

mysql> SELECT sal;
ERROR 1054 (42S22): Unknown column 'sal' in 'field list'

流程控制

判断语句

判断语句可以根据一些条件做出判断，从而决定执行哪些SQL语句。MySQL中常用的判断语句有IF和CASE两种。

IF语句

IF语句可以对条件进行判断，根据条件的真假，来执行不同的语句，其语法格式如下所示。

IF 条件表达式1 THEN 语句列表
    [ELSEIF 条件表达式2 THEN 语句列表]...
    [ELSE 语句列表]
END IF

上述语法格式中，当条件表达式1结果为真时，执行THEN子句后的语句列表；当条件表达式1结果为假时，继续判断条件表达式2，如果条件表达式2结果为真，则执行对应的THEN子句后的语句列表，以此类推；如果所有的条件表达式结果都为假，则执行ELSE子句后的语句列表。

下面通过一个案例演示IF语句的使用。 例如，员工管理系统中经常需要根据输入的员工姓名返回对应的员工信息，如果输入为空，则显示输入的值为空；如输入的员工姓名在员工表中不存在，则显示员工不存在。将这个需求编写成存储过程，具体SQL语句及执行结果如下所示。

DELIMITER &&
CREATE PROCEDURE proc_isnull(IN e_name VARCHAR(20))
 BEGIN
  DECLARE ecount INT DEFAULT 0;
  SELECT COUNT(*) INTO ecount FROM emp WHERE ename=e_name;
  IF e_name IS NULL
          THEN SELECT '输入的值为空';
  ELSEIF ecount=0
          THEN SELECT '员工不存在';
  ELSE
          SELECT * FROM emp WHERE ename=e_name;
  END IF;
 END &&
DELIMITER ;

下面调用存储过程proc_isnull，具体SQL语句如下所示。

mysql> CALL proc_isnull(NULL);
+--------------+
| 输入的值为空 |
+--------------+
| 输入的值为空 |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

mysql> CALL proc_isnull('刘大');
+------------+
| 员工不存在 |
+------------+
| 员工不存在 |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)

mysql> CALL proc_isnull('刘一');
+-------+-------+--------+------+---------+------+--------+
| empno | ename | job    | mgr  | sal     | comm | deptno |
+-------+-------+--------+------+---------+------+--------+
|  9839 | 刘一  | 董事长 | NULL | 6000.00 | NULL |     10 |
+-------+-------+--------+------+---------+------+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)

 CASE语句

CASE语句也可以对条件进行判断，它可以实现比IF语句更复杂的条件判断，CASE语句的语法格式有2种，具体如下所示。

# 语法格式1
CASE 表达式
    WHEN 值1 THEN 语句列表
    [WHEN 值2 THEN 语句列表]...
    [ELSE 语句列表]
END CASE

从上述语法格式可以看出，CASE语句中可以有多个WHEN子句，CASE后面的表达式的结果决定哪一个WHEN子句会被执行；当WHEN子句后的值与表达式结果值相同时，执行对应的THEN关键字后的语句列表；如果所有WHEN子句后的值都和表达式结果值不同，则执行ELSE后的语句列表；END CASE表示CASE语句的结束。

# 语法格式2
CASE 
    WHEN 条件表达式1 THEN 语句列表
    [WHEN 条件表达式2 THEN 语句列表]...
    [ELSE 语句列表]
END CASE

 在上述语法格式中，当WHEN子句后的条件表达式结果为真时，执行对应THEN后的语句列表；当所有WHEN子句后的条件表达式都不为真时，则执行ELSE后的语句列表。

下面通过一个案例演示CASE语句的使用。 例如，员工管理系统中经常需要根据输入的员工工资返回对应的工资等级，如果工资大于等于5000，则返回高薪资；如果小于5000并且大于等于4000则返回中等薪资；如果小于4000并且大于等于2000则返回低薪资；其他金额则返回不合理薪资。将这个需求编写成存储函数，具体SQL语句如下所示。

DELIMITER &&
CREATE FUNCTION func_level(esal DECIMAL(7,2))
  RETURNS VARCHAR(20)
  BEGIN
    CASE
      WHEN esal>=5000 THEN RETURN '高薪资';
      WHEN esal >=4000 THEN RETURN '中等薪资';
      WHEN esal >=2000 THEN RETURN '低薪资';
      ELSE RETURN '不合理薪资';
    END CASE;
  END &&
DELIMITER ;

循环语句

循环语句指的是符合条件的情况下，重复执行一段代码。例如，计算给定区间内数据的累加和。MySQL提供的循环语句有LOOP、REPEAT和WHILE三种，下面分别进行介绍。

LOOP语句

LOOP语句通常用于实现一个简单的循环，其基本语法格式如下所示。

[标签:]LOOP
语句列表
END LOOP[标签];

 上述语法格式中，标签是可选参数，用于标志循环的开始和结束。标签的定义只需符合MySQL标识符的定义规则即可，但两个位置的标签名称必须相同。LOOP会重复执行语句列表，所以在循环时务必给出结束循环的条件，否则会出现死循环。LOOP语句本身没有停止语句，如果要退出LOOP循环，需要使用LEAVE语句。

下面通过一个案例演示LOOP语句的使用，例如，在存储过程中实现0~9之间整数的累加计算，具体SQL语句如下。

DELIMITER &&
CREATE PROCEDURE proc_sum()
 BEGIN
  DECLARE i,sum INT DEFAULT 0;
  sign: LOOP
          IF i >=10 THEN
                  SELECT i,sum;
                  LEAVE sign;
          ELSE
                  SET sum=sum+i;
                  SET i=i+1;
          END IF;
  END LOOP sign;
 END &&
DELIMITER ;

调用存储过程proc_sum: 

CALL proc_sum();

 REPEAT语句

REPEAT语句用于循环执行符合条件的语句列表，每次循环时，都会对语句中的条件表达式进行判断，如果表达式返回值为TRUE，则结束循环，否则重复执行循环中的语句。REPEAT语句的基本语法格式如下所示。

[标签:] REPEAT
    语句列表
    UNTIL 条件表达式
END REPEAT [标签]

上述语法格式中，程序会无条件地先执行一次REPEAT语句中的语句列表，然后再判断UNTIL后的条件表达式的结果是否为TRUE，如果为TRUE，则结束循环；如果不为TRUE，则继续执行语句列表。

例如，在存储过程内实现0~10之间奇数的累加计算:

DELIMITER &&
CREATE PROCEDURE proc_odd()
 BEGIN
  DECLARE i,sum INT DEFAULT 0;
  sign: REPEAT
          IF i%2 != 0 THEN SET sum=sum+i;
          END IF;
          SET i=i+1;
  UNTIL i>10
  END REPEAT sign;
  SELECT i,sum;
 END &&
DELIMITER ;

 调用存储过程proc_odd:

CALL proc_odd();

WHILE语句

WHILE语句也用于循环执行符合条件的语句列表，与REPEAT 语句不同的是， WHILE语句是先判断条件表达式，再根据判断结果确定是否执行循环内的语句列表，WHILE 语句的基本语法格式如下所示。

[标签:]WHILE 条件表达式 DO
  语句列表
END WHILE[标签]

 上述语法格式中，只有条件表达式为真时，才会执行DO后面的语句列表，语句列表执行完之后，再次判断条件表达式的结果，如果结果为真，继续执行语句列表；如果结果为假，则退出循环。在使用WHILE循环语句时，可以在语句列表中设置循环的出口，以防出现死循环的现象。

例如，在存储过程内实现0~10之间偶数的累加计算:

DELIMITER &&
CREATE PROCEDURE proc_even()
 BEGIN
  DECLARE i,sum INT DEFAULT 0;
  WHILE i<=10 DO
          IF i % 2=0
                  THEN SET sum=sum+i;
          END IF;
          SET i=i+1;
  END WHILE;
  SELECT i,sum;
 END &&
DELIMITER ;

调用存储过程proc_even:

CALL proc_even();

 跳转语句

跳转语句用于实现执行过程中的流程跳转。MySQL中常用的跳转语句有LEAVE语句和ITERATE语句，跳转语句的基本语法格式如下所示。

{ITERATE|LEAVE} 标签名;

上述语法格式中，ITERATE语句用于结束本次循序的执行，开始下一轮循序的执行，而LEAVE语句用于终止当前循环，跳出循环体。

 通过计算2的累加和直到和大于等于5为止这个例子来说明ITERATE和LEAVE的区别:

DELIMITER &&
CREATE PROCEDURE proc_jump()
 BEGIN
  DECLARE num INT DEFAULT 0;
  my_loop: LOOP
          SET num=num+2;
          IF num <5
                  THEN ITERATE my_loop;
          ELSE SELECT num;LEAVE my_loop;
          END IF;
 END LOOP my_loop;
 END &&
DELIMITER ;

调用存储过程proc_jump:

CALL proc_jump();

错误处理

程序在运行过程中可能会发生错误，发生错误时，默认情况下，MySQL将自动终止程序的执行。有些时候，如果不希望程序因为错误而停止执行，可以通过MySQL中的错误处理机制自定义错误名称和错误处理程序，让程序遇到警告或错误时也能继续执行，从而增强程序处理问题的能力。

自定义错误名称

MySQL提供了比较丰富的错误代码，当程序出现错误时，会将对应的错误信息抛出以提醒开发人员。自定义错误名称就是给程序出现的错误声明一个名称，便于处理错误。例如，手机中存放了很多电话号码，可以给每个号码设置对应的名字，使用时只需通过名字就能找到对应的电话号码，而不需要记住那么多的电话号码。

在MySQL中可以使用DECLARE语句为错误声明一个名称，声明的基本语法格式如下所示。

DECLARE 错误名称 CONDITION FOR 错误类型;

在上述语法格式中，错误名称指自定义的错误名称，错误类型有两种可选值，分别为mysql_error_code和SQLSTATE [VALUE] sqlstate_value，其中mysql_error_code是MySQL数值类型的错误代码；sqlstate_value是长度为5的字符串类型的错误代码。mysql_error_code和sqlstate_value都可以表示MySQL的错误。

例如：

ERROR 1062 (23000): Duplicate entry '9839' for key emp.PRIMARY'

 上述错误信息是在插入重复的主键值时抛出的错误信息，其中1062就是一个mysql_error_code类型的错误代码，23000是对应的SQLSTATE类型的错误代码。

下面使用DECLARE语句为上述错误代码声明一个名称，具体SQL语句如下所示。

DELIMITER &&
CREATE PROCEDURE proc_err()
 BEGIN
       DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000';
 END &&
DELIMITER ;

另外，以上示例DECLARE语句中还可以为mysql_error_code类型的错误代码定义名称，具体SQL语句如下。

DECLARE duplicate_entry CONDITION FOR 1062;

自定义错误处理程序

程序出现异常时默认会停止继续执行，MySQL中允许自定义错误处理程序，在程序出现错误时，可以交由自定义的错误处理程序处理，避免直接中断程序的运行。自定义错误处理程序的基本语法格式如下所示。

DECLARE 错误处理方式 HANDLER FOR 错误类型[,错误类型...] 程序语句段

上述语法格式中，程序语句段表示在遇到定义的错误时，需要执行的一些存储过程或函数。MySQL支持的错误处理方式有CONTINUE和EXIT，其中CONTINUE 表示遇到错误不进行处理，继续向下执行；EXIT 表示遇到错误后马上退出。

错误类型有6种可选值，分别如下。

（1）sqlstate_value：匹配SQLSTATE错误代码。

（2）condition_name：匹配DECLARE定义的错误条件名称。

（3）SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。

（4）NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。

（5）SQLEXCEPTION：匹配所有没有被 SQLWARNING或 NOT FOUND捕获的SQLSTATE错误代码。

（6）mysql_error_code：匹配mysql_error_code类型的错误代码。

下面通过一个案例对错误处理程序的使用进行演示。例如，由于员工表中设有主键，如果在存储过程中往员工表中插入多条数据时，插入的数据中有相同的主键值会导致存储过程执行出现错误，导致程序程序中断。此时可以通过自定义错误处理程序确保存储过程的执行不被中断，具体SQL语句及执行结果如下所示。

DELIMITER &&
CREATE PROCEDURE proc_handler_err()
 BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
  SET @num=1;
  INSERT INTO emp VALUES(9944,'白龙马','人事',9982,1000,500,40);
  SET @num=2;
  INSERT INTO emp VALUES(9944,'白龙马','人事',9982,1000,500,40);
  SET @num=3;
 END &&
DELIMITER ;

 创建完存储过程proc_handler_err后，调用存储过程，并查询当前会话变量num的值，具体SQL语句如下所示。

mysql> CALL proc_handler_err();
Query OK, 0 rows affected (0.01 sec)
mysql> select @num;
+------+
| @num |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

游标

使用SELECT语句可以返回符合指定条件的结果集，但是没有办法对结果集中的数据进行单独的处理，例如，使用SELECT语句查询出多条员工信息的结果集后，无法获取结果集中的单条记录。为此，MySQL提供了游标机制，利用游标可以对结果集中的数据进行单独处理。

游标的本质是一种带指针的记录集，在MySQL中游标应用于存储过程和函数中。

游标的操作流程

游标的使用要遵循一定的操作流程，游标的操作流程一般分为4个步骤，分别定义游标、打开游标、利用游标检索数据和关闭游标。下面对这4个步骤进行讲解。

1.定义游标

MySQL中使用DECLARE关键字定义游标，因为游标要操作的是SELECT语句返回的结果集，所以定义游标时，需要指定与其关联的SELECT语句。定义游标的基本语法格式如下所示。

DECLARE 游标名称 CURSOR FOR SELECT语句

上述语法格式中，游标名称必须唯一，因为在存储过程和函数中可以存在多个游标，而游标名称是唯一用于区分不同游标的标识。需要注意的是，SELECT语句中不能含有INTO关键字。 使用DECLARE…CURSOR FOR语句定义游标时，因为与游标相关联的SELECT语句并不会立即执行，所以此时MySQL服务器的内存中并没有SELECT语句的查询结果集。

需要注意的是，变量、错误触发条件、错误处理程序和游标都是通过DECLARE定义的，但它们的定义是有先后顺序要求的，变量和错误触发条件必须在最前面声明，然后是游标的声明，最后才是错误处理程序的声明。

2.打开游标 声明游标之后，要想从游标中提取数据，需要先打开游标。在 MySQL 中，打开游标通过 OPEN 关键字实现，其语法格式如下。

OPEN 游标名称

打开游标后，SELECT语句根据查询条件，将查询到的结果集存储到MySQL服务器的内存中。

3.利用游标检索数据 打开游标之后，就可以通过游标检索SELECT语句返回的结果集中的数据，游标检索数据的基本语法格式如下所示。

FETCH 游标名称 INTO 变量名1 [,变量名2]... 

每执行一次FETCH语句就在结果集中获取一行记录，FETCH语句获取记录后，游标的内部指针就会向前移动一步，指向下一条记录。在上述语法格式中，FETCH语句根据指定的游标名称，将检索出来的数据存放到对应的变量中，变量名的个数需要和SELECT语句查询的结果集的字段个数保持一致。

FETCH语句一般和循环语句一起完成数据的检索，FETCH语句通常和REPEAT循环语句一起使用。因为无法直接判断哪条记录是结果集中的最后一条记录，当利用游标从结果集中检索出最后一条记录后，再次执行FETCH语句，将产生“ERROR 1329(02000）：No data to FETCH”错误信息，因此，使用游标时通常需要自定义错误处理程序处理该错误，从而结束游标的循环。

4.关闭游标 游标检索完数据后，应该利用MySQL提供的语法关闭游标，释放游标占用的MySQL服务器的内存资源，关闭游标的基本语法格式如下所示。 

CLOSE 游标名称

在程序内，如果使用CLOSE关闭游标后，不能再通过 FETCH 使用该游标。如果想要再次利用游标检索数据，只需使用OPEN打开游标即可，而不用重新定义游标。如果没有使用CLOSE关闭游标，那么它将在被打开的BEGIN…END语句块的末尾关闭。

使用游标检索数据

通过案例演示使用游标检索数据，例如，将员工表emp中奖金为NULL的员工信息存放在一个新的数据表emp_comm，数据表emp_comm的结构和员工表保持一致，具体实现如下所示。

首先创建用来存放结果数据的数据表emp_comm，具体SQL语句如下所示。

CREATE TABLE emp_comm(
 empno   INT PRIMARY KEY,
 ename   VARCHAR(20) UNIQUE,
 job     VARCHAR(20),
 mgr     INT,
 sal     DECIMAL(7,2),
 comm    DECIMAL(7,2),
 deptno  INT
 );

接着创建存储过程，在存储过程中将奖金为NULL的员工信息添加到数据表emp_comm，具体SQL语句及执行结果如下所示。

DELIMITER &&
CREATE PROCEDURE proc_emp_comm()
 BEGIN
 DECLARE mark INT DEFAULT 0; -- mark:游标结束循环的标识
 DECLARE emp_no INT ; -- emp_no:存储员工表empno字段的值
 DECLARE emp_name VARCHAR(20); -- emp_name:存储员工表ename字段的值
 DECLARE emp_job VARCHAR(20); -- emp_job:存储员工表job字段的值
 DECLARE emp_mgr INT; -- emp_mgr:存储员工表mgr字段的值
 DECLARE emp_sal decimal(7,2); -- emp_sal:存储员工表sal字段的值
 DECLARE emp_comm decimal(7,2); -- emp_comm:存储员工表comm字段的值
 DECLARE emp_deptno INT; -- emp_deptno:存储员工表deptno字段的值
 # 定义游标
 DECLARE cur CURSOR FOR SELECT * FROM emp WHERE comm IS NULL;
 # 定义错误处理程序
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
   SET mark=1;
 #打开游标
 OPEN cur;
 REPEAT
  # 通过游标获取结果集的记录
  FETCH cur INTO emp_no,emp_name,emp_job,emp_mgr,emp_sal,emp_comm,emp_deptno;
  IF mark!=1 THEN
  INSERT INTO emp_comm(empno,ename,job,mgr,sal,comm,deptno)
  VALUES(emp_no,emp_name,emp_job,emp_mgr,emp_sal,emp_comm,emp_deptno);
  END IF;
 UNTIL mark=1 END REPEAT;
  # 关闭游标
 CLOSE cur;
 END &&
DELIMITER ;

 上述代码中，创建了存储过程proc_emp_comm，第4行代码定义了变量mark用于存储游标结束循环的标识；第5~11行代码定义了7个变量，分别用于存储员工表emp中7个字段的值；第13行代码定义了游标cur，cur与员工表emp中奖金为NULL的记录相关联；第15~16行代码定义了错误处理程序，用于当游标获取最后一行记录后再获取记录时，继续执行程序，并且设置mark的值为1；第18行打开游标；第19~26行代码通过REPEAT遍历游标，每循环一次，FETCH取出游标标记的一行记录，并将记录中的值存入到第5~11行定义的变量中；接着会判断mark是否等于1，如果不等于1，则将记录插入到数据表emp_comm中；当mark的值为1时，说明已经将结果集的数据检索完毕，会执行第26~28行代码，结束循环并关闭游标。

在调用存储过程之前，先查看下员工表emp中记录，具体SQL及执行结果如下所示:

mysql> SELECT * FROM emp WHERE comm IS NULL;
+-------+--------+--------+------+---------+------+--------+
| empno | ename  | job    | mgr  | sal     | comm | deptno |
+-------+--------+--------+------+---------+------+--------+
|  9369 | 张三   | 保洁   | 9902 |  900.00 | NULL |     20 |
|  9839 | 刘一   | 董事长 | NULL | 6000.00 | NULL |     10 |
|  9900 | 萧十一 | 保洁   | 9698 | 1050.00 | NULL |     30 |
|  9902 | 赵六   | 分析员 | 9566 | 4000.00 | NULL |     20 |
|  9936 | 张%一  | 保洁   | 9982 | 1200.00 | NULL |   NULL |
|  9988 | 王五   | 分析员 | 9566 | 4000.00 | NULL |     20 |
+-------+--------+--------+------+---------+------+--------+
6 rows in set (0.00 sec)

从上述查询结果可以看出，员工表emp中奖金为空的记录有6条，下面调用存储过程proc_emp_comm，并在调用后查看数据表emp_comm中的记录，具体SQL语句及执行结果如下所示。

mysql> CALL proc_emp_comm();
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM emp_comm;
+-------+--------+--------+------+---------+------+--------+
| empno | ename  | job    | mgr  | sal     | comm | deptno |
+-------+--------+--------+------+---------+------+--------+
|  9369 | 张三   | 保洁   | 9902 |  900.00 | NULL |     20 |
|  9839 | 刘一   | 董事长 | NULL | 6000.00 | NULL |     10 |
|  9900 | 萧十一 | 保洁   | 9698 | 1050.00 | NULL |     30 |
|  9902 | 赵六   | 分析员 | 9566 | 4000.00 | NULL |     20 |
|  9936 | 张%一  | 保洁   | 9982 | 1200.00 | NULL |   NULL |
|  9988 | 王五   | 分析员 | 9566 | 4000.00 | NULL |     20 |
+-------+--------+--------+------+---------+------+--------+
6 rows in set (0.00 sec)

 触发器

触发器可以看作是一种特殊的存储过程，它与存储过程的区别在于，存储过程使用CALL语句调用时才会执行，而触发器会在预先定义好的事件，例如发生INSERT、DELETE等操作时自动调用。

创建触发器时需要与数据表相关联，当数据表发生特定事件（如INSERT、DELETE等操作）时，会自动执行触发器中的SQL语句，例如，插入数据前，强制检验或转换数据等操作，或是在触发器中代码执行错误后，撤销已经执行成功的操作，保障数据的安全。

从上述内容可以知道，触发器具有以下优点。

(1)触发器自动执行，在触发器相关联的数据表中的数据发生修改，触发器中定义的语句会自动执行。

(2)触发器对数据进行安全校验，保障数据安全。

(3)通过和触发器相关联的表，可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

触发器的基本操作

触发器的基本操作包括创建触发器、查看触发器、触发触发器和删除触发器。

1.创建触发器

创建触发器时，需要指定触发器操作的数据表，创建触发器的基本语法格式如下所示。

CREATE TRIGGER 触发器名称 触发时机 触发事件 ON 数据表名 FOR EACH ROW 
触发程序

上述语法格式中，触发器的名称必须在当前数据库中唯一，如果要在指定的数据库中创建触发器，触发器名称前面应该加上数据库的名称。

触发时机指触发程序执行的时间，可选值有BEFORE和AFTER；触发事件表示激活触发器的操作类型，可选值有INSERT、UPDATE和DELETE；

触发程序指的是触发器执行的SQL语句，如果要执行多个语句，可使用BEGIN…END 作为语句的开始和结束。触发程序中可以使用NEW和OLD分别表示新纪录和旧记录。

下面通过一个案例演示在数据表中创建触发器。例如，在删除员工信息后，自动将删除的员工信息添加在其他数据表，以防后续需要查询删除了的员工信息，具体操作如下所示。

首先创建一张新的数据表，用于存放被删除的员工信息，具体SQL语句如下所示。

CREATE TABLE emp_del(
 empno   INT PRIMARY KEY,
 ename   VARCHAR(20) UNIQUE,
 job     VARCHAR(20),
 mgr     INT,
 sal     DECIMAL(7,2),
 comm    DECIMAL(7,2),
 deptno  INT
 );

 接着在员工表emp中创建触发器，当删除员工表的数据后，触发该触发器，并且在触发器的触发程序中将被删除的员工添加到数据表emp_del中，具体SQL语句及执行结果如下所示。

#在员工表emp中创建触发器trig_emp
CREATE TRIGGER trig_emp
 AFTER DELETE ON emp FOR EACH ROW
 INSERT INTO emp_del(empno,ename,job,mgr,sal,comm,deptno)
	VALUES(old.empno,old.ename,old.job,old.mgr,old.sal,old.comm,old.deptno);

 2.查看触发器

如果想通过语句查看数据库中已经存在的触发器的信息，MySQL中提供了2种查看触发器的方法，一种是利用SHOW TRIGGER语句查看触发器，另一种是利用SELECT语句查看数据库information_schema下数据表TRIGGERS中的触发器数据。

利用SHOW TRIGGER语句查看触发器信息的语法格式如下所示。

SHOW TRIGGERS;

在MySQL中，触发器信息都保存在数据库information_schema下的数据表TRIGGERS中，可以通过SELECT语句查看该数据表获取触发器信息，通过TRIGGERS数据表查询触发器的语法格式如下所示。

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

 上述语法格式中，可以通过WHERE子句指定触发器的名称，如果不指定触发器名称，则会查询出information_schema数据库中所有已经存在的触发器信息。

例如，使用SELECT语句查询触发器trig_emp的信息，具体SQL语句如下所示。

SELECT * FROM information_schema.triggers WHERE trigger_name= 'trig_emp' \G

3.触发触发器

触发器trig_emp创建成功后，会根据触发时机和触发事件触发。

例如，删除员工表中的一条员工记录，并且想要在删除操作后查看数据表emp_del中的记录，验证触发器是否触发，具体SQL语句及执行结果如下所示。

DELETE FROM emp WHERE ename="悟空";

SELECT * FROM emp_del;

结果： 

+-------+-------+------+------+---------+---------+--------+
| empno | ename | job  | mgr  | sal     | comm    | deptno |
+-------+-------+------+------+---------+---------+--------+
|  9999 | 悟空  | 人事 | 9982 | 3000.00 | 1800.00 |     40 |
+-------+-------+------+------+---------+---------+--------+

 从上述执行结果可以看出，删除员工表emp中员工悟空的记录后，数据表emp_del中新增了一条记录，由此可以得出，对员工表emp执行删除操作后，触发了触发器trig_emp。

4.删除触发器

当创建的触发器不再符合当前的需求时，可以将触发器删除。删除触发器的操作很简单，只需使用MySQL提供的DROP TRIGGER语句即可，DROP TRIGGER语句的基本语法格式如下所示。

DROP TRIGGER [ IF EXISTS ] [数据库名.] 触发器名;

在上述语法格式中，利用“数据库名.触发器名”的方式可以删除指定数据库下的触发器，当省略“数据库名.”时，则删除当前选择的数据库下的触发器。

例如，删除数据库ems中的触发器trig_emp：

DROP TRIGGER IF EXISTS ems.trig_emp;

 再次查询触发器trig_emp的信息：

SELECT * FROM information_schema.triggers WHERE trigger_name= 'trig_emp' \G

结果：Empty set (0.00 sec) 

上机实践：数据库编程实战

实践需求1：创建一个存储过程proc_1，用于获取图书的名称、价格和借阅状态，如果是已借阅的状态，则显示借阅人。创建完存储过程proc_1后，执行该存储过程查看效果。

动手实践1：创建名称为proc_1的存储过程，创建后执行存储过程proc_1。

存储过程的过程体如下：

查询图书表book的name、price、borrower_id字段信息，如果borrower_id为NULL，则显示borrower_id的值为“未借阅”；如果不为NULL，则显示borrower_id对应的用户名称；

具体的SQL语句如下所示。

DELIMITER //
# 创建存储过程proc_1
CREATE PROCEDURE proc_1()
 BEGIN
  SELECT b.name,b.price,IF(borrower_id IS NULL,'未借阅',u.name) 借阅人
  FROM book b LEFT JOIN user u ON b.borrower_id=u.id;
 END //
DELIMITER ;
# 调用存储过程proc_1
mysql>  CALL proc_1();

实践需求2：创建一个存储过程proc_2，用于获取所有可借阅的图书信息，图书信息只需显示图书名称、图书价格和上架时间。创建完存储过程proc_2后，执行该存储过程查看效果。

动手实践2：创建名称为proc_2的存储过程，创建后执行存储过程proc_2。

存储过程的过程体如下：

查询图书表book图书信息中state值为0的name、price、upload_time字段信息。

具体的SQL语句如下所示。

DELIMITER //
# 创建存储过程proc_2
CREATE PROCEDURE proc_2()
 BEGIN
  SELECT name,price,upload_time   FROM book WHERE state=0;
 END //
DELIMITER ;
# 调用存储过程proc_2
CALL proc_2();

实践需求3：创建一个存储函数func_1，可以根据输入的用户名称，显示用户当前借阅中的图书名称。创建完存储函数func_1后，执行该存储函数查看效果。

动手实践3：创建名称为func_1的存储函数，创建后执行存储函数func_1。

存储函数的过程体如下：

根据输入的用户名称，查询图书表book图书信息中name字段的信息，其中查询出的信息中id字段和用户名称对应的id一致，并将查询的结果返回

具体的SQL语句如下所示。

DELIMITER //
# 创建存储函数func_1
CREATE FUNCTION func_1(uname varchar(20))
  RETURNS  varchar(20)
  BEGIN
   RETURN (SELECT b.name FROM user u,book b WHERE u.id=b.borrower_id AND u.name=uname);
  END //
DELIMITER ;
# 调用存储函数func_1
SELECT func_1('张三');

实践需求4：创建一个存储函数func_2，执行时输入图书名称，显示图书当前的价格档位；如果价格小于等于40元，显示“平民价格”；如果价格大于40元并且小于等于60元，则显示“主流价格”；如果价格大于60元，则显示“高价格”。创建完存储函数func_2后，执行该存储函数查看效果。

动手实践4：创建名称为func_2的存储函数，创建后执行存储函数func_2。

存储函数的过程体中，根据输入的图书名称查询图书价格，根据查询出的图书价格得出价格的档位，设置一个变量存储该价格档位。

具体的SQL语句如下所示。

DELIMITER //
CREATE FUNCTION func_2(bname varchar(20))
  RETURNS  varchar(20)
  BEGIN
    DECLARE blevel VARCHAR(10);
    DECLARE bprice decimal(6, 2);
    SELECT price INTO bprice FROM book WHERE name=bname;
    IF bprice>60
        THEN SET blevel='高价格';
    ELSEIF bprice<=60 AND bprice>40
        THEN SET blevel='主流价格';
    ELSEIF empsal<=40
        THEN SET blevel='平民价格';
    END IF;
   RETURN blevel;
   END //
DELIMITER ;
SELECT func_2('西游记');

实践需求5：查看当前数据库中存储过程proc_1和存储函数func_1的信息。

动手实践5：使用SHOW语句查看当前数据库中存储过程proc_1和存储函数func_1的信息，具体的SQL语句如下所示。

# 查看存储过程proc_1
SHOW PROCEDURE STATUS LIKE 'proc_1'\G
# 查看存储函数func_1
SHOW FUNCTION STATUS LIKE 'func_1' \G

实践需求6：删除存储过程proc_1和存储函数func_1。

动手实践6：使用DROP PROCEDURE 语句和DROP FUNCTION语句分别删除存储过程proc_1和存储函数func_1，具体的SQL语句如下所示。

# 删除存储过程proc_1
DROP PROCEDURE IF EXISTS proc_1;
# 删除存储函数func_1
DROP FUNCTION IF EXISTS func_1;

实践需求7：创建一个触发器trig_book，在借阅记录表中插入数据时，自动修改图书表中借阅相关的信息。

动手实践7：创建一个名称为trig_book的触发器，在借阅记录表record中插入数据后触发执行触发程序，触发程序中根据借阅记录的图书编号，分别设置图书表中borrower_id 、borrow_time、state字段的值为NULL、NULL、0，具体的SQL语句如下所示。

CREATE TRIGGER trig_book
 AFTER INSERT ON record FOR EACH ROW
 UPDATE book SET borrower_id =NULL,borrow_time=NULL,state=0 WHERE book.id=NEW.book_id;

实践需求8：创建好触发器trig_book后，对该触发器的信息进行查看。

动手实践8：在数据库information_schema的TRIGGERS表中查询trigger_name值为trig_book的记录，具体的SQL语句如下所示。

SELECT * FROM information_schema.triggers WHERE trigger_name= 'trig_book' \G