数据库学习6高级应用

一、存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。它有点像内置函数。使用存储过程的好处有以下几点。换句话说就是三点，简单、安全、高性能：

提高性能。因为使用存储过程比使用单独的SQL语句要快；

通过把处理封装在容易使用的单元中，简化复杂的操作；

由于不要求反复建立一系列处理步骤，这保证了数据的完整性；

需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性；

简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要

知道这些变化。

当然，使用存储过程也有一些缺点：

一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验；

你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们

创建存储过程。

1、执行存储过程

使用CALL语句。CALL接受存储过程的名字以及需要传递给它的任意参数。

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。所有MySQL变量都必须以@开始。

2、创建存储过程

（1）创建没有参数的存储过程

CREATE PROCEDURE productpricing()

BEGIN

    SELECT Avg(prod_price) AS priceaverage

    FROM product;

END;

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。如果存储过程接受参数，它们将在()中列举出来。此存储过程没有参数，但后跟的()仍然需要。 BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。

（2）创建有参数的存储过程

CREATE PROCEDURE productpricing(

    OUT pl DECIMAL(8,2),

    OUT ph DECIMAL(8,2),

    OUT pa DECIMAL(8,2),

)

BEGIN

    SELECT Min(Prod_price) INTO pl FROM product;

    SELECT Max(Prod_price) INTO ph FROM product;

    SELECT Avg(Prod_price) INTO pa FROM product;

END;

此存储过程接受3个参数： pl存储产品最低价格， ph存储产品最高价格， pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。 MySQL支持IN（传递给存储过程）、 OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

二、游标

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。不像多数DBMS， MySQL游标只能用于存储过程（和函数）。

三、触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？这就需要使用触发器。触发器分为DDL触发器和DML触发器。DDL触发器是表结构改变时触发，DML触发器是数据改变时触发。MySQL只支持DML触发器，也就是响应DELETE、INSERT和UPDATE语句而自动执行。其他MySQL不支持触发器。

四、事务

事务处理（ transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。事务具有以下几个特性：原子性，一次性，隔离性，持久性，ACID。

五、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

六、改善性能的一些方法

参考资料《MySQL必知必会》