MySQL必知必会 学习笔记 四

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语句用来限定存储过程体。

使用这个存储过程:

CALL productpricint();

因为存储过程实际上一种函数，所以存储过程名后需要()符号，即使不传递参数

删除存储过程
存储过程在创建之后，被保留在服务器上以供使用，直至被删除。

为删除刚创建的存储过程，可使用以下语句:
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存储计算出来的合计

调用这个存储过程，使用以下语句:

CALL ordertotal(20005,@total);

如下显示此合计

SELECT @total;

建立智能存储过程
假如你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客，需做:
①获得合计
②把营业税有条件地添加到合计
③返回合计

–为注释

DELIMITER //-- Name:ordertotal-- Parameters: onumber=order number--             taxable=0 if not taxable,1 if taxable--             ototal = order total variableCREATE 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 6;   -- 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/100*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(20005,0,@total);SELECT @total;

CALL ordertotal(20005,1,@total);SELECT @total;

通过指定taxable，有条件地将营业所加到订单合计上。

检查存储过程
显示用来创建一个存储过程的CREATE语句，使用:

SHOW CREATE PROCEDURE ordertotal;

列出所有存储过程
为了限制其输出，可使用LIKE指定一个过滤模式，例如:

SHOW PROCEDURE STATU LIKE 'ordertotal';

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=1;   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 0;   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=1;   CREATE TABLE IF NOT EXISTS ordertotals      (order_num INT,total DECIMAL(8,2));   OPEN ordernumbers;   REPEAT      FETCH ordernumbers INTO o;      CALL ordertotal(o,1,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 productsFOR EACH ROW SELECT 'Product added';

创建名为newproduct的触发器，在INSERT语句成功执行后执行。
指定FOR EACH ROW，因此代码对每个插入行执行。本例中，文本Product added将对每个插入的行显示一次。

只有表才支持触发器，视图和临时表不支持

触发器每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此每个表最多支持6个触发器(每条INSERT,DELETE和UPDATE之前和之后)。
单一触发器不能与多个事件或多个表关联。

25.3 删除触发器

DROP TRIGGER newproduct;

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使用下面语句来标识事务的开始:

START TRANSACTION

使用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=20010;DELETE FROM orders WHERE order_num=20010;COMMIT;

使用事务处理块来保证订单不被部分删除，最后的COMMIT语句仅在不出错时写出更改。
如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它被自动撤销）

当COMMIT或ROLLBACK语句执行后，事务会自动关闭

使用保留点
复杂的事务处理需要部分提交或回退。

为了支持回退部分事务处理，必须在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

这些占位符称为保留点。为了创建占位符使用SAVEPOINT语句:

SAVEPOINT delete1;

每个保留点标识它的唯一名字。

为了回到本例给出的保留点，可如下进行:

ROLLBACK TO delete1;

保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。也可以用RELEASE SAVEPOINT明确地释放保留点。

更改默认的提交行为
默认的MySQL行为是自动提交所有更改。即任何时候执行一条MySQL语句，实际上都是针对表执行的，而且所做的更改立即生效。

为了指示MySQL不自动提交或更改，需要以下语句:

SET autocommit=0;

autocommit为0指示MySQL不自动提交更改（直到被设置为真）

27 全球化和本地化

27.1 字符集和校对顺序

字符集为字母和符号的集合

编码为某个字符集成员的内部表示

校对为规定字符如果比较的指令

27.2 使用字符集和校对顺序

为查看所有支持的字符集完整列表，使用:

SHOW CHARACTER SET;

显示所有可用的字符集以及每个字符集的描述和默认校对

为了查看所有校对的完整列表，使用:

SHOW COLLATION;

显示所有可用的校对，以及它们使用的字符集

为了给表指定字符集和校对，可使用带子句的CREATE TABLE:

CREATE TABLE mytable(   columnn1 INT,   colummn2 VARCHAR(10))DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

①如果指定CHARACTER SET和COLLATE两者，则使用这些值
②如果只指定 CHARACTER SET，则使用此字符集及其默认的校对
③如果两者都不指定，则使用数据库默认，

MySQL还允许对每个列设置字符集和校对

CREATE TABLE mytable(   columnn1 INT,   colummn2 VARCHAR(10)   column3  VARCHAR(10) CHARACTER SET latin1  COLLATE latin1_general_ci;)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

校对在使用ORDER BY子句检索出来的数据排序时起重要作用。
如果需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行:

SELECT * FROM customersORDER 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>

这里写代码片
“`

RENAME USER ben TO bb;

删除用户账号
为了删除一个用户账号(以及相关的权限)，使用DROP USER语句

DROP USER bb;

设置访问权限
在创建用户账号后，必须分配访问权限。新创建的用户账号没有访问权限，它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。

看到赋予用户账号的权限,使用SHOW GRANTS FOR:

SHOW GRANTS FOR ben;

结果表示在任意数据库和任意表上对任何东西没有权限。

为设置权限，使用GRANT语句。需给出以下信息:
①要授予的权限
②被授予访问权限的数据库或 表
③用户名

GRANT SELECT ON crashcourse.* TO ben;

允许用户在crashcourse.*上使用SELECT。只授予只读访问权限。
用SHOW GRANTS 反映这个更改:

用REVOKE撤销特定权限(被撤销的权限必须存在):

REVOKE SELECT ON crashcourse.* FROM ben;

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语句来刷新和重新开始所有日志文件