《MYSQL必知必会》阅读随记 （ 二十四 - 二十九章）

工具准备

MySQL Workbench 8.0

create schema crashcourse;

use crashcourse;

分别运行 create.sql 和 populate.sql

这两个脚本下载地址：

脚本地址

第二十四章 使用游标

游标

在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（ cursor ）是一个存储在 MySQL 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

只能用于存储过程 不想多数 DBMS， MySQL 游标只能用于存储过程（ 和函数 ）。

使用游标

• 在能够使用游标前，必须声明（ 定义 ）它。这个过程似乎机上没有检索数据，它只是定义要使用的 SELECT 语句
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来
• 对于填有数据的游标，根据需要取出（ 检索 ）各行
• 在结束游标使用时，必须关闭游标

创建游标

游标用 DECLARE 语句创建。DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END //
DELIMITER ;

DECLARE 语句用来定义和命名游标，这里为 ordernumbers。存储过程处理完成后，游标就消失（ 因为它局限于存储过程 ）。

打开和关闭游标

游标用 OPEN CURSOR 语句来打开
OPEN ordernumbers;
在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动

游标处理完成后，应当使用如下语句关闭游标：
CLOSE ordernumbers;

CLOSE 释放游标使用的所有内部和资源，因此在每个游标不再需要时都应该关闭

在一个游标关闭后，如果没有重新打开，就不能使用，但不需要再次声明。

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Open the cursor
    OPEN ordernumbers;
    
    -- Close the cursor
    CLOSE ordernumbers;
END //
DELIMITER ;

使用游标数据

在一个游标被打开后，可以使用 FETCH 语句分别访问它的每一行。FETCH 指定检索什么数据（ 所需的列 ），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（ 不重复读取同一行 ）。

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE o INT;
	-- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Open the cursor
    OPEN ordernumbers;
    
    -- GET order number
    FETCH ordernumbers INTO o;
    
    -- Close the cursor
    CLOSE ordernumbers;
END //
DELIMITER ;

FETCH 用来检索当前行的 order_num 列（ 将自动从第一行开始 ）到一个名为 o 的局部声明的变量中。对检索出的数据不做任何处理

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    -- Open the cursor
    OPEN ordernumbers;
    
    -- Loop through all rows
    REPEAT
		-- Get order number
        FETCH ordernumbers INTO o;
	-- End of loop
    UNTIL done END REPEAT;
    -- Close the cursor
    CLOSE ordernumbers;
END //
DELIMITER ;

02000 主要代表的意思可以理解为：
发生下述异常之一：
SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
就是说你定义了，当fetch游标到了数据库表格最后一行的时候，设置done=1.

使用 FETCH 检索当前 order_num 到声明的名为 o 的变量中。但与前一个例子不一样的是，这个例子中的 FETCH 是在 REPEAT 内，因此它反复执行知道 done 为真（由 UNTIL done END REPEAT; 规定）。为使它起作用，用一个 DEFAULT 0( 假，不结束 )定义变量 done。用 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 在结束时设置 done 为真。
这条语句定义了一个 CONTINUE HANDLER，它实在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000' 出现时， SET done = 1。 SQLSTATE '02000' 是一个未找到的条件，当 REPEAT 邮局没有更多的行供循环时，出现这个条件

DECLARE语句的次序 DECLARE 语句的发布存在特定的次序。用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

重复或循环 除这里使用的 REPEAT 语句外，MySQL 还支持循环语句，知道使用 LEAVE 语句手动退出为止。

DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8, 2);
    
    -- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    -- Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
		(order_num INT, total DECIMAL(8, 2));
        
	-- Open the cursor
    OPEN ordernumbers;
    
    -- Loop through all rows
    REPEAT
		-- Get order number
        FETCH ordernumbers INTO o;
        
        -- Get the total for this order
        CALL ordertotal(o, 1, t);
        
        -- Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total) 
        VALUES(o, t);
        
        -- End of loop
        UNTIL done END REPEAT;
        
        -- Close the cursor
        CLOSE ordernumbers;
END //
DELIMITER ;

CALL processorders();

这个例子中，增加了另一个名为 t 的变量（ 存储每个订单的合计 ）。此存储过程还在运行中创建了一个新表（ 如果它不存在的话 ），名为 ordertotals。然后用 CALL 执行另一个存储过程来计算每个订单的带税的合计（ 结果存储到 t ）。最后，用 INSERT 保存每个订单的订单号和合计

此存储过程不返回数据，但它能够创建和填充另一个表，可以用 SELECT 语句查看( 记得要 CALL processorders(); )

SELECT *
FROM ordertotals;

第二十五章 使用触发器

让某条语句（ 或某些语句 ）在事件发生时自动执行， 例如：

• 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确
• 每当订购一个产品时，都从库存数量中减去订购的数量
• 无论何时删除一行，都在某个存到表中保留一个副本

触发器是 MySQL 相应以下任意语句而自动执行的一条语句（ 或位于 BEGIN 和 END 语句之间的一组语句 ）

• DELETE
• INSERT
• UPDATE
  其他语句不支持触发器

创建触发器

创建触发器需要给出 4 条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该相应的活动（ DELETE、INSERT 或 UPDATE ）；
• 触发器何时执行（ 处理之前或之后 ）

保持每个数据库的触发器名唯一

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added' INTO @asd;

MYSQL5以后，不允许触发器返回任何结果，因此使用INTO @变量名，将结果赋值到变量中，用select调用即可

CREATE TRIGGER 用来创建名为 newproduct 的新触发器。触发器课在一个操作发生之前货之后执行。这个触发器还指定 FOR EACH ROW，因此代码对每个插入行执行。这个例子中，文本 Product added 将对每个插入的行显示一次

仅支持表 视图不支持

触发器按每个表每个事件每次地定义，每个表每个事件每次质询与一个触发器。因此，每个表最多支持 6 个触发器（ 每条 INSERT、UPDATE 和 DELETE 的之前和之后 ）。单一触发器不能与多个事件或多个表关联，，所以，如果需要一个对 INSERT 和 UPDATE 操作执行的触发器，则应该定义两个触发器

删除触发器

DROP TRIGGER newproduct;

使用触发器

INSERT 触发器

• 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行
• 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（ 允许更改被插入的值 ）
• 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含 0，在 INSERT 执行之后包含新的自动生成值

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @asd;

此代码创建一个名为 neworder 的触发器，它按照 AFTER MySQL 生成一个新订单号并保存到 order_num 中。触发器从 NEW.order_num 取得这个值并返回它。此触发器必须按照 AFTER INSERT 执行，因为在 BEFORE INSERT 语句执行之前，新 order_num 还没有生成。对于 orders 的每次插入使用这个触发器将总时返回新的订单号。

测试：

INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);
SELECT @asd;

DELETE 触发器

• 在 DELETE 触发器代码内，可以引用一个名为 OLD 的虚拟表，访问被删除的行
• OLD 中的值全都是只读的，不能更新

DELIMITER //
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END //
DELIMITER ;

在任意订单被删除前将执行此触发器。它使用一条 INSERT 语句将 OLD 中的值（ 要被删除的订单 ）保存到一个名为 archive_orders 的存到表中（ 为实际使用这个而例子，需要用于 orders 相同的列创建一个名为 archive_orders 的表 ）

使用 BEFORE DELETE 触发器的优点为，如果由于某种原因，订单不能存档，DELETE 本身将被放弃

多语句触发器 触发器 deleteorder 使用 BEGIN 和 END 语句标记触发器体。

UPDATE 触发器

• UPDATE 触发器代码中，可以引用一个名为 OLD 的虚拟表访问以前的值，引用一个名为 NEW 的虚拟表访问新更新的值
• 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新
• OLD 中的值全都是只读的，不能更新
  下面的例子将保证州名缩写总时大写

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

第二十六章 管理事务处理

事务管理

并非所有引擎都支持事务处理 MyISAM 和 InnoDB 是两种常使用的引擎。前者不支持明确的事务处理管理，而后者支持

事务处理（ transaction processing ）可以用来维护数据库的完整性，它保证成批的 MySQL 操作要么完全执行，要么完全不执行

关系数据库设计把数据存储在多个表中，使数据更容易操纵、维护和重用。不用深究如何以及为什么进行关系数据库设计，在某种程度上说，设计良好的数据库模式都是关联的

如前面的例子，给系统添加订单的过程如下

1. 检查数据库中是否存在相应的客户（从 customers 表查询），如果不存在，添加。
2. 检索客户的 ID
3. 添加一行到 orders 表，把它与客户 ID 关联
4. 检索 orders 表中赋予的新订单 ID
5. 对于订购的每个物品在 orderitems 表中添加一行，通过检索出来的 ID 把它于 orders 表关联（ 以及通过产品 ID 与 products 表关联 ）

如果某种数据库故障（超出磁盘空间、安全限制、表锁等）组织了这个过程的完成。会有什么情况？

如果故障发生在添加客户之后，orders 表添加之前，没啥问题。某些客户没有订单完全合法。再重新执行此过程，所插入的客户记录将被检索和使用。可以有效地从出故障地地方执行此过程。

但如果故障发生在 orders 行添加之后， orderitems 行添加之前，数据库中会有一个空订单。

如果系统再添加 orderitems 行自会后出现故障。结果是数据库中存在不完整订单，还不知道。

这时就需要使用 事务处理 来解决这种问题了。事务处理是一种机制，用来管理必须成批执行地 MySQL 操作，以保证数据库不包含不完整地操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者整体执行，或者完全不执行（ 除非明确指示 ）。如果没有错误发生，整租语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态
因此，刚才的过程：

1. 检查数据库中是否存在相应的客户，如果不存在，添加
2. 提交客户信息
3. 检索客户 ID
4. 添加一行到 orders 表
5. 如果在添加行到 orders 表时出现故障，回退
6. 检索 orders 表中赋予的新订单 ID
7. 对于订购的每项物品，添加新行到 orderitems 表
8. 如果在添加新行到 orderitems 时出现故障，回退所有添加的 orderitems 行 和 orders 行
9. 提交订单信息

术语

• 事务（ transaction ） 指一组 SQL 语句
• 回退（rollback） 指撤销自会顶 SQL 语句
• 提交（commit） 指将为存储的 SQL 语句结果写入数据库表
• 保留点（savepoint） 指事务处理中设置的临时占位符（palce-holder），可以对它发布回退（与回退整个事务处理不同）

控制事务处理

MySQL 使用下面的语句来标识事务的开始
START TRANSACTION

使用 ROLLBACK

MySQL 的 ROLLBACK 命令用来回退。

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

这个例子最后一条 SELECT 语句显示不为空
显然，ROLLBACK 只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后）

哪些语句可以回退 事务处理用来管理 INSERT、UPDATE 和 DELETE 语句。

使用 COMMIT

一般的 MySQL 语句都是直接针对数据库表执行和编写的。这就是所谓隐含提交( implicit commit )，即提交（写或保存）操作时自动进行的
在事务处理块中，提交不会隐含地进行。为了进行明确的提交，使用 COMMIT 语句

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

在本例中，从系统中完全删除订单 20010。因为设计更新两个数据库表 orders 和 orderitems，所以使用事务处理块来保证订单不被部分删除。最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 DELETE 起作用，第二条失败，则 DELETE 不会提交（实际上它是被自动撤销的）

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

使用保留点

简单的 ROLLBACK 和 COMMIT 语句就可以写入或撤销整个事务管理。但是，支队简单的事务处理才这样做，更复杂的事务处理可能需要部分提交或回退

为了子好吃回退部分事务处理，必须能在事务处理块中何时的位置放置占位符。占位符被称为报六点。创建占位符，使用 SAVEPOINT 语句

SAVEPOINT delete1;

每个报六点取标识它的唯一名字。回退到保留点：
ROLLBACK TO delete1;

** 保留点越多越好**

释放保留点 保留点在事务处理完成后自动释放

更改默认的提交行为

指示 MySQL 不自动提交更改

SET autocommit=0;

autocommit 标志决定是否自动提交更改，不管有没有 COMMIT 语句。设置 autocommit 为 0（假）指示 MySQL 不自动提交更改

第二十七章 全球化和本地化

• 字符集 字母和符号的集合
• 编码 为某个字符集成员的内部标识
• 校对 规定字符如何比较的指令

使用字符集和校对顺序

MySQL 支持众多的字符集。为查看所支持的字符集完整列表：
SHOW CHARACTER SET;

查看所支持校对的完整列表

SHOW COLLATION;

为了确定所用的字符集和校对，使用如下语句：

SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collaction%';

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

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

此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序

除了能指定字符集和校对的表范围外，MySQL 还允许对每个列设置它们

CREATE TABLE mytable
(
	cloumnn1 INT,
	columnn2 VARCHAR(10),
	columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE
	latin1_general_ci
)DEEFAULT CHARACTER SET hebrew
 COLLATE hebrew_general_ci;

第二十八章 安全管理

管理用户

MySQL 用户账号和信息存储在名为 mysql 的 MySQL 数据库总。一般不需要直接访问 mysql 数据库和表，但有时需要直接访问。需要直接访问它的实际之一是在需要获得所有用户账号列表时：

USE mysql;
SELECT user FROM user;

mysql 数据库有一个名为 user 的表，它包含所有用户账号。user 表有一个名为 user 的列，它存储用户登录名。西南祖杭的服务器可能只有一个用户，过去建立的服务器可能具有很多用户。

创建用户账号

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

CREATE USER 创建一个新用户账号。在创建用户账户时 不一定需要口令，不过这个例子用 IDENTIFIED BY 'p@$$w0rd' 给出了一个口令。

如果再次列出用户账号，将会在输出中看到新账号

指定散列口令 IDENTIFIED BY 指定的口令为纯文本，MySQL 将在保存到 user 表之前对其进行加密。为了作为散列值指定口令，使用 IDENTIFIED BY PASSWORD

使用 GRANT 或 INSERT GRANT 语句也可以创建用户账号。此外也可以通过直接插入行到 user 表来增加用户。

为重命名一个用户账号，使用 RENAME USER 语句
RENAME USER ben TO bforta;

删除账号

DROP USER bforta;

设置访问权限

SHOW GRANTS FOR admin;

为设置权限，使用 GRANT 语句。GRANT 要求至少给出如下信息：

• 要授予的权限
• 被授予访问权限的数据库或表
• 用户名

GRANT SELECT ON crashcourse.* TO bforta;

此 GRANT 允许用户在 crashcourse.* ( crashcourse 数据库的所有表 ) 上使用 SELECT 。通过只授予 SELECT 访问权限，用户 bforta 对 crashcourse 数据库中所有数据都具有只读访问权限

GRANT 的反操作为 REVOKE ，用它来撤销特定的权限

REVOKE SELECT ON crashcourse.* FROM bforta;

GRANT 和 REVOKE 可在几个层次上控制访问权限：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL
• 整个数据库，使用 ON database.*;
• 特定的表，使用 ON database.table;`
• 特定的列
• 特定的存储过程

更改口令

更改用户口令（密码）可使用 SET PASSWORD 语句。新口令必须如下加密：
SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');

SET PASSWORD 更新用户密码。新密码必须传递到 Password() 函数进行加密

SET PASSWORD 还可以设置自己的密码

SET PASSWORD = Password('n3w p@$$w0rd');

第二十九章 数据库维护

备份数据

• 使用命令行使用程序 mysqldump 转储所有数据库内容到某个外部文件。
• 可用命令实用程序 mysqlhotcopy 从一个数据库复制所有数据
• 可以使用 MySQL 的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件

数据库维护

• ANALYZE TABLE 检查表键是否正确。
  ANALYZE TABLE orders;
  
• CHECK TABLE 针对许多问题对表进行检查。在 MyISAM 表上还对索引进检查。CHECK TABLE 支持一系列的用于 MyISAM 表的方式。CHANGED 检查自最后一次检查以来改动过的表。EXTENDED 执行最彻底的检查，FAST 检查未正常关闭的表，MEDIUM 检查所有被删除的联结比进行键检查，QUICK 只进行快速扫描

  CHECK TABLE orders, orderitems;
  

  
• 如果 MyISAM 表访问产生不正确和不一致的结果，可能需要 REPAIR TABLE 来修复相应的表。
• 如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来收回所用的空间，从而优化表的性能

诊断启动问题

• –help 显示帮助
• –safe-mode 装载减去某些最佳配置的服务器
• –verbose 显示全文本消息
• –version

查看日志文件

• 错误日志。包含启动和关闭问题以及任意关键错误的细节。此日志常名为 hostname.err，位于 data 目录中。名字可用 --log-error 命令行选项更改(下同)
• 查询日志。记录所有 MySQL 活动。通常命名为 hostname.log，位于 data 目录中。
• 二进制日志。 记录更新过数据的所有语句。hostname.bin，位于 data 目录
• 缓慢查询日志。 记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。名字常为：hostname-slow.log，位于 data 目录