23 使用存储过程
23.1 存储过程
迄今为止使用的大多数SQL语句都是针对一个或多个表的单条语句，并非所有操作都这么简答，经常会有一个完整的操作需要多条语句才能完成。
考虑下面例子:
可以单独编写每条语句，并根据结果有条件地执行另外的语句.在每次需要这个处理时（以及每个需要它的应用中）都必须做这些工作。
可以创建存储过程，存储过程简单说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
23.2 为什么要使用存储过程
①通过把处理封装在容易使用的单元中，简化复杂的操作
②由于不要求反复建立一系列处理步骤，这就保证了数据的完整性，防止错误，防止错误保证了数据的一致性
③简化对变化的管理。延伸就是安全性
④提高性能，使用存储过程比使用单独的SQL语句更快
⑤存在一些只能用在单个请求中的MySQL元素和特性，存储过程中可以使用它们来编写功能更强更灵活的代码
即有3个主要好的好处：简单，安全，高性能。
也有一些缺陷：
①存储过程的编写比基本SQL语句复杂
②可能没有创建存储过程的安全访问权限。
23.3 使用存储过程
执行存储过程
MySQL成存储过程的执行为调用，因此MySQL执行存储过程的语句就是CALL。
CALL接受存储过程的名字以及需要传递给它的任意参数，看例子:
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
执行名为productpricing的存储过程，计算并返回产品的最低，最高和平均结果
创建存储过程
下面创建一个新的存储productpricing
CREATE PROCEDURE pp()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
()接受参数。BEGIN和END语句用来限定存储过程体。
使用这个存储过程:
因为存储过程实际上一种函数，所以存储过程名后需要()符号，即使不传递参数
删除存储过程
存储过程在创建之后，被保留在服务器上以供使用，直至被删除。
为删除刚创建的存储过程，可使用以下语句:
DROP PROCEDURE productpricing;
不需要()
使用参数
变量(variable) 内存中的一个特定位置，用来临时存储数据
以下是productpricing的修改版本(需先删除)
DELIMITER //
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 products;
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END //
此存储过程接受3个参数。每个参数必须具有指定的类型，这里使用十进制。
(8,2) 8为精度，2为小数位数。
关键字OUT指出响应的参数用来从存储过程传出一个值（返回给调用者）。
MySQL支持IN（传递给存过程），OUT(从传递过程传出)和INOUT（对存储过程传入和传出）类型的参数。
存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）
记录集不是允许的参数类型。
调用此存储过程，必须指定3个变量名，如下:
CALL productpricing(@pricelow,@pricehigh,@priceaverage);
存储过程要求3个参数，因此必须传递3个参数，这条CALL语句给出3个参数，它们是存储过程将保存结果的3个变量的名字。
变量名
所有MySQL变量必须以@开始
调用这条语句，并不显示任何数据，它返回后可以显示（或在其他处理中使用）的变量
为了获得3个值，使用以下语句:
SELECT @pricehigh,@pricelow,@priceaverage;
下面是另一个例子，这次使用IN和OUT参数。
DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num=onumber
INTO ototal;
END //
onumber定义为IN,因此订单号被传入存储过程。ototal被定义为OUT，因为要从存储过程返回合计。
SELECT语句使用这两个参数，WHERE子句使用onumber选择正确的行，INTO使用ototal存储计算出来的合计
调用这个存储过程，使用以下语句:
如下显示此合计
建立智能存储过程
假如你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客，需做:
①获得合计
②把营业税有条件地添加到合计
③返回合计
–为注释
DELIMITER //
-- Name:ordertotal
-- Parameters: onumber=order number
-- taxable= if not taxable, if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT ;
-- Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num=onumber
INTO total;
-- Is this taxable?
IF taxable THEN
-- Yes,so add taxrate to the total
SELECT total+(total/*taxrate) INTO total;
END if;
-- And finally,save to out variable
SELECT total INTO ototal;
END //
增加了注释，这在复杂性增加时很重要。
添加了一个参数taxable，为boolean(如要增加税则为真，否则为假)。
在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，也支持可选的默认值(taxrate默认设置为6%)。
IF语句检查taxable是否为真，如果为真，则利用另一SELECT语句增加营业税到局部变量total。
使用下面语句:
CALL ordertotal(,,@total);
SELECT @total;
CALL ordertotal(,,@total);
SELECT @total;
通过指定taxable，有条件地将营业所加到订单合计上。
检查存储过程
显示用来创建一个存储过程的CREATE语句，使用:
列出所有存储过程
为了限制其输出，可使用LIKE指定一个过滤模式，例如:
24 使用游标
24.1 游标
有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。
游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
24.2 使用游标
几个明确的步骤：
①在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
②一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来
③对于填有数据的游标，根据需要取出（检索）各行
④在结束游标使用时，必须关闭游标
创建游标
游标用DECLARE（声明）语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。如:
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END //
DECLARE语句用来定义和命名游标。存储过程处理完成后，游标就会消失，因为它局限于存储过程
打开和关闭游标
游标用OPEN CURSOR语句来打开：
OPEN ordernumbers;
在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动
关闭游标:
CLOSE ordernumbers;
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
如果你不明确关闭游标，MySQL会在到达END语句时自动关闭它
使用游标数据
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据(所需的列)，检索出来的数据存储在什么地方。它还想前移动游标中的内部行指针，使下一条FETCH语句检索下一行（不重复读取同一行）。
从游标中检索单个行 （第一行）：
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;
END //
其中FETCH用来检索当前行的order_num列(自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。
循环检索数据，从第一行到最后一行:
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=;
OPEN ordernumbers;
-- Loop through all rows
REPEAT
FETCH ordernumbers INTO o;
-- End of loop
UNTIL done END REPEAT;
CLOSE ordernumbers;
END //
如果调用这个存储过程，它将顶一个几个变量和一个CONTINUE HANDLER ，定义并打开一个游标，重复读取所有行，然后关闭游标。
这个例子使用FETCH检索当前order_num到声明为o的变量中，这个例子的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定的）
为使它起作用，用一个DEFAULT 0(假，不结束)定义变量done。
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done=1;
定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。指出当SQLSTATE ‘02000’出现时，SET done=1.SQLSTATE ‘02000’是一个未找到条件，由REPEAT由于没有更多的行提供循环时而不能继续时，出现这个条件。
DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在任意游标或句柄之前定义，而句柄必须在游标之后定义。
进一步修改，这次对取出的数据进行某种实际的处理”
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE done BOOLEAN DEFAULT ;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=;
CREATE TABLE IF NOT EXISTS ordertotals
(order_num INT,total DECIMAL(,));
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
CALL ordertotal(o,,t);
INSERT INTO ordertotals(order_num,total)
VALUES(o,t);
UNTIL done END REPEAT;
CLOSE ordernumbers;
END //
此例中，我们增加了另一个名为t的变量（存储每个订单的合计）。在运行过程中创建了一个新表（如果不存在），名为ordertotals。这个表将保存存储过程生成的结果。
FETCH取每个order_num,然后用CALL执行另一个存储过程ordertotal来计算每个订单的带税的合计（结果存储到t），最后用INSERT保存每个订单的订单号和合计
调用此过程,用SELECT 查看该表
CALL processorders();
SELECT * FROM ordertotals;
25 使用触发器
25.1 触发器
某条语句或存储过程在某个表发生更改时自动处理，这需要触发器。
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句:
①DELETE
②INSERT
③UPDATE
其他语句不支持触发器
25.2 创建触发器
在创建触发器时，需给出4条信息:
①唯一的触发器名(每个表中唯一)
②触发器关联的表
③触发器应该响应的活动(DELETE,UPDATE或INSERT)
④触发器何时执行
触发器用CREATE TRIGGER语句创建:
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
创建名为newproduct的触发器，在INSERT语句成功执行后执行。
指定FOR EACH ROW，因此代码对每个插入行执行。本例中，文本Product added将对每个插入的行显示一次。
只有表才支持触发器，视图和临时表不支持
触发器每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此每个表最多支持6个触发器(每条INSERT,DELETE和UPDATE之前和之后)。
单一触发器不能与多个事件或多个表关联。
25.3 删除触发器
25.4 使用触发器
INSERT 触发器
INSERT触发器在INSERT语句执行之前或之后执行，须知：
①在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
②在BEFORE INSERT 触发器中，NEW的值也可以被更新(允许更改被插入的值)
③对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。
26 管理事务处理
26.1 事务处理
事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
订单存储在orders和orderitems两个表中:orders存储实际的订单，而orderitems存储订购的各项物品
给系统添加订单的过程如下:
①检查数据库中是否存在相应的客户(从customers表查询),如果不存在则添加
②检索客户的ID
③添加一行到orders表，把它与客户ID关联
④检索orders表中赋予的新订单ID
⑤对于订购的每个物品在orderitems表中添加一行，通过检索出来的ID与它与orders表关联（以及通过产品ID与products表关联）
如果由于数据库某种故障阻止了这个过程的完成，会使数据库中存在不完整的订单。
这需要事务处理来解决这个问题。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。使用事务处理，可以确保一组操作不会中途停止，它们或作为整体执行或完全不执行。
看相同例子事务处理如何工作:
①检查数据库中是否存在相应的客户(从customers表查询),如果不存在则添加
②提交客户信息
③检索客户的ID
④添加一行到orders表
⑤如果在添加行到orders表时出现故障，回退
⑥检索orders表中新赋予的新订单ID
⑦对于订购的每项物品，可以添加新行到orderitems表
⑧如果在添加新行到orderitems时出现故障，回退所有添加的orderitems行和orders行
⑨提交订单信息
事务(transaction)
一组SQL语句
回退(rollback)
撤销指定SQL语句的过程
提交(commit)
将未存储的SQL语句结果写入数据库表
保留点(savepoint)
事务处理中设置的临时占位符，你可以对它发布回退(与回退整个事务处理不同)
26.2 控制事务处理
MySQL使用下面语句来标识事务的开始:
使用ROLLBACK
ROLLBACK命来用来回退MySQL语句:
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
首先执行一条SELECT以显示该表不为空，然后开始一个事务处理，用一条DELETE语句删除ordertotals总的所有行，另一条SELECT语句验证ordertotals确实为空。
这是用一条ROLLBACK语句回退START TRANSACTION之后的所有语句，最后一条SELECT语句显示该表不为空。
ROLLBACK只能在一个事务处理内使用。
事务处理用来管理INSERT，UPDATE和DELETE语句，你不能回退SELECT(因为没意义)，不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果执行回退，它们不会被撤销。
使用COMMIT
一般的MySQL语句都是直接针对数据库表执行和编写的。这就是隐含提交(implicit commit),即提交（写或保存）操作是自动进行的。
但在事务处理块中，提交不含隐含地进行。为了进行明确的提交，使用COMMIT语句，如下:
START TRANSACTION;
DELETE FROM orderitems WHERE order_num=;
DELETE FROM orders WHERE order_num=;
COMMIT;
使用事务处理块来保证订单不被部分删除，最后的COMMIT语句仅在不出错时写出更改。
如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它被自动撤销）
当COMMIT或ROLLBACK语句执行后，事务会自动关闭
使用保留点
复杂的事务处理需要部分提交或回退。
为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
这些占位符称为保留点。为了创建占位符使用SAVEPOINT语句:
每个保留点标识它的唯一名字。
为了回到本例给出的保留点，可如下进行:
保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。也可以用RELEASE SAVEPOINT明确地释放保留点。
更改默认的提交行为
默认的MySQL行为是自动提交所有更改。即任何时候执行一条MySQL语句，实际上都是针对表执行的，而且所做的更改立即生效。
为了指示MySQL不自动提交或更改，需要以下语句:
autocommit为0指示MySQL不自动提交更改（直到被设置为真）
27 全球化和本地化
27.1 字符集和校对顺序
字符集为字母和符号的集合
编码为某个字符集成员的内部表示
校对为规定字符如果比较的指令
27.2 使用字符集和校对顺序
为查看所有支持的字符集完整列表，使用:
显示所有可用的字符集以及每个字符集的描述和默认校对
为了查看所有校对的完整列表，使用:
显示所有可用的校对，以及它们使用的字符集
为了给表指定字符集和校对，可使用带子句的CREATE TABLE:
CREATE TABLE mytable
(
columnn1 INT,
colummn2 VARCHAR()
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
①如果指定CHARACTER SET和COLLATE两者，则使用这些值
②如果只指定 CHARACTER SET，则使用此字符集及其默认的校对
③如果两者都不指定，则使用数据库默认，
MySQL还允许对每个列设置字符集和校对
CREATE TABLE mytable
(
columnn1 INT,
colummn2 VARCHAR()
column3 VARCHAR() CHARACTER SET latin1 COLLATE latin1_general_ci;
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
校对在使用ORDER BY子句检索出来的数据排序时起重要作用。
如果需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行:
SELECT * FROM customers
ORDER BY lastname,firstname COLLATE latin1_general_cs;
COLLATE还可以用于GROUP BY,HAVING,聚集函数，别名等。
串可以在字符集之间进行转换，为了使用Cast()函数和Convert()函数
28 安全管理
28.1 访问控制
需要给用户提供它们所需的访问权，且仅提供他们所需的访问权，这就是访问控制，管理访问控制需要创建和管理用户账号。
28.2 管理用户
MySQL用户账号和信息存储在名为mysql的数据库中。
获得所有用户账号列表：
use mysql;
SELECT user FROM user;
创建用户
使用CREATE USER语句:
CREATE USER ben IDENTIFIEND BY 'p@$$w0rd';
```
创建了一个ben用户账号。不一定需要口令，但这里使用'p@$$w0rd'给出了一个口令。
IDENTIFIED BY指定的口令为纯文本，MySQL将在保存到user表之前对其进行加密。为了作为散列值指定口令，使用IDENTIFIED BY PASSWORD
重命名一个用户账号，使用RENAME USER语句
<div class="se-preview-section-delimiter"></div>
这里写代码片
“`
删除用户账号
为了删除一个用户账号(以及相关的权限)，使用DROP USER语句
设置访问权限
在创建用户账号后，必须分配访问权限。新创建的用户账号没有访问权限，它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。
看到赋予用户账号的权限,使用SHOW GRANTS FOR:
结果表示在任意数据库和任意表上对任何东西没有权限。
为设置权限，使用GRANT语句。需给出以下信息:
①要授予的权限
②被授予访问权限的数据库或 表
③用户名
允许用户在crashcourse.*上使用SELECT。只授予只读访问权限。
用SHOW GRANTS 反映这个更改:
用REVOKE撤销特定权限(被撤销的权限必须存在):
GRANT和REVOKE可在几个层次上控制访问权限:
①整个服务器，使用GRANT ALL和REVOKE ALL
②整个数据库，使用ON database.*;
③特定的表，使用 ON database.table;
④特定的列
⑤特定的存储过程
更改口令
使用SET PASSWORD语句，新口令必须如下加密:
SET PASSWORD FOR ben=Password('n3w p@$$w0rd')
新口令必须传递到Password()函数进行加密;
在不指定用户名时，SET PASSWORD更新当前登录用户的口令。
29 数据库维护
29.1 备份数据
MySQL数据库是基于磁盘的文件，普通的备份系统和历程就能备份MySQL的数据。
但由于文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效的。
解决方案：
①使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。
②可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
③使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储到所有数据到某个外部文件。数据可以用RESTORE TABLE来复原。
29.2 进行数据库维护
①ANALYZE TABLE,用来检查表键是否正确
ANALYZE TABLE orders;
②CHECK TABLE 用来针对许多问题对表进行检查。CHECK TABLE发现和修复问题：
CHECK TABLE orders,orderitems;
③如果MyISAM表产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表
④如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。
29.3 诊断启动问题
服务器问题通常在MySQL配置或服务器本身更改时出现。
MySQL服务器通过在命令行执行mysqld启动。几个命令行选项:
29.4 日志文件
①错误日志。包含启动和关闭问题以及任意关键错误的细节。通常名为hostname.err，位于data目录中，日志名可用–log-error命令行选项更改
②查询日志。记录所有MySQL活动，在诊断问题时非常有用。通常名为hostname.log，位于data目录中，可以–log命令行选项更改
③二进制日志。记录更新过数据(或者可能更新过数据)的所有语句，通常名为hostname-bin，位于data目录内，用–long-bin命令行选项更改
④缓慢查询日志。记录执行缓慢的任何查询，在确定数据库何处需要优化很有用。通常名为hostname-slow.log，位于data目录中，用–log-slow-queries命令行选线更改。
可用FLUSH LOGS语句来刷新和重新开始所有日志文件