《MySQL必知必会》学习笔记（24-29）
《MySQL必知必会》学习笔记 目录
https://www.jianshu.com/p/97bab08c6755
本篇内容
使用游标、使用触发器、管理事务处理、全球化和本地化、安全管理、数据库维护
第 二 十 四 章 使 用 游 标
讲授什么是游标以及如何使用游标
24.1 游标
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。
游标（cursor） 是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
24.2 使用游标
使用游标涉及几个明确的步骤
- 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
- 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
- 对于填有数据的游标，根据需要取出（检索）各行。
- 在结束游标使用时，必须关闭游标。
24.2.1 创建游标
游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
END;*
注意使用 DELIMITER // 切换改变输入结束符为//，不然 ； 会被解析为结束符导致命令失败
*DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
END//
DELIMITER ;
定义了名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
24.2.2 打开和关闭游标
OPEN ordernumbers;
在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。
游标处理完成后，应当使用如下语句关闭游标：
CLOSE ordernumbers;
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
隐含关闭 如果你不明确关闭游标， MySQL将会在到达END语句时自动关闭它。
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 // 切换改变输入结束符为//
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 ;
image.png
这个存储过程声明、打开和关闭一个游标。但对检索出的数据什么也没做。
24.2.3 使用游标数据
在一个游标被打开后，可以使用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的局部声明的变量中。对检索出的数据不做任何处理。
image.png
循环检索数据，从第一行到最后一行：
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 ;
与前一个例子一样，这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTILdone END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。那么， done怎样才能在结束时被设置为真呢？答案是用以下语句：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里， 它指出当SQLSTATE '02000'出现时， SET done=1。SQLSTATE'02000'是一个未找到条件， 当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
image.png
为了把这些内容组织起来，下面给出我们的游标存储过程样例的更进一步修改的版本，这次对取出的数据进行某种实际的处理
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 ;
增加了另一个名为t的变量（存储每个订单的合计）。
此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals,这个表将保存存储过程生成的结果。
FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程（我们在前一章中创建）来计算每个订单的带税的合计（结果存储到t）。最后，用INSERT保存每个订单的订单号和合计。
image.png
查询上述命令的结果
SELECT * FROM ordertotals;
第 二 十 五 章 使用触发器
什么是触发器，为什么要使用触发器以及如何使用触发器
25.1 触发器
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：
- DELETE；
- INSERT；
- UPDATE。
其他MySQL语句不支持触发器。
25.2 创建触发器
在创建触发器时，需要给出4条信息：
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动（DELETE、 INSERT或UPDATE）；
- 触发器何时执行（处理之前或之后）。
触发器用CREATE TRIGGER语句创建
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。
这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。
触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、 UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。
25.3 删除触发器
为了删除一个触发器，可使用DROP TRIGGER语句
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
25.4 使用触发器
25.4.1 INSERT触发器
INSERT触发器在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;
创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时， MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFOREINSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。
image.png
Mysql版本差异：
https://blog.csdn.net/chenmoshashou2/article/details/80461213
25.4.2 DELETE触发器
DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：
- 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
- OLD中的值全都是只读的，不能更新。
CREATE TRIGGER deleteorder BEFORE DELECTE 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;
25.4.3 UPDATE触发器
UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：
- 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
- 在BEFORE UPDATE触发器中， NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
- OLD中的值全都是只读的，不能更新。
第 二 十 六 章 管理事务处理
介绍什么是事务处理以及如何利用COMMIT和ROLLBACK语句来管理事务处理。
26.1 事务处理
事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行，以保证数据库不包含不完整的操作结果。
利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
关于事务处理需要知道的几个术语：
- 事务（transaction）指一组SQL语句；
- 回退（rollback）指撤销指定SQL语句的过程；
- 提交（commit）指将未存储的SQL语句结果写入数据库表；
- 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。
26.2 控制事务处理
管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。
标识事务的开始：
START TRANSACION
26.2.1 使用ROLLBACK
ROLLBACK命令用来回退（撤销） MySQL语句
SELECT * FROM ordertotals;
START TRANSATION;
DELETE FROM ordertotal;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
ROLLBACK只能在一个事务处理内使用（在执行一条STARTTRANSACTION命令之后）。
哪些语句可以回退？
事务处理用来管理INSERT、 UPDATE和DELETE语句。你不能回退SELECT语句。你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
26.2.2 使用COMMIT
在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句
START TRANSATION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE prder_num = 20010;
COMMIT;
最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交,实际上它是被自动撤销的。
隐含事务关闭 当COMMIT或ROLLBACK语句执行后，事务会自动关闭
26.2.3 使用保留点
复杂的事务处理可能需要部分提交或回退，为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这些占位符称为保留点。
SAVEPOINT delete1;
每个保留点都取标识它的唯一名字，以便在回退时
回退到delete1保留点
ROLLBACK TO delete1;
释放保留点 保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASESAVEPOINT明确地释放保留点。
26.2.4 更改默认的提交行为
默认的MySQL行为是自动提交所有更改。任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。
为指示MySQL不自动提交更改
SET autocommit = 0;
autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。
标志为连接专用 autocommit标志是针对每个连接而不是服务器的。
第 二十七 章 全球化和本地化
介绍MySQL处理不同字符集和语言的基础知识
27.1 字符集和校对顺序
数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。因此， MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法
- 字符集为字母和符号的集合；
- 编码为某个字符集成员的内部表示；
- 校对为规定字符如何比较的指令。
27.2 使用字符集和校对顺序
MySQL支持众多的字符集。为查看所支持的字符集完整列表，使用
SHOW CHARACTER SET;
27.2SET.png
查看所支持校对的完整列表
SHOW COLLATION;
此语句显示所有可用的校对，以及它们适用的字符集。
为了确定所用的字符集和校对，可以使用以下语句：
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
image.png
常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对。
为了给表指定字符集和校对，可使用带子句的CREATE TABLE
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
除了能指定字符集和校对的表范围外， MySQL还允许对每个列设置它们
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
columnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
第 二 十 八 章 安 全 管 理
MySQL的访问控制和用户管理。
28.1 访问控制
MySQL服务器的安全基础是： 用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。
访问控制的目的不仅仅是防止用户的恶意企图。数据梦魇更为常见的是无意识错误的结果，如错打MySQL语句，在不合适的数据库中操作或其他一些用户错误。
28.2 管理用户
MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表，但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。
USE mysql
SELECT user FROM user;
image.png
28.2.1 创建用户账号
了创建一个新用户账号，使用CREATE USER语句
CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
image.png
重新命名一个用户账号，使用RENAME USER语句
RENAME USER ben to bforta;
image.png
28.2.2 删除用户账号
删除一个用户账号（以及相关的权限），使用DROP USER语句
DROP USER bforta;
image.png
28.2.3 设置访问权限
在创建用户账号后，必须接着分配访问权限。新创建的用户账号没有访问权限。它们能登录MySQL，但不能看到数据，不能执行任何数据库操作。
为看到赋予用户账号的权限，使用SHOW GRANTS FOR
SHOW GRANTS FOR bforta;
image.png
为设置权限，使用GRANT语句,要求你至少给出以下信息：
- 要授予的权限；
- 被授予访问权限的数据库或表；
- 用户名。
GRANT SELECT ON crashcourse.* TO bforta;
image.png
此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。
GRANT的反操作为REVOKE，用它来撤销特定的权限。
REVOKE SELECT ON crashcourse.* FROM bforta;
image.png
GRANT和REVOKE可在几个层次上控制访问权限：
- 整个服务器，使用GRANT ALL和REVOKE ALL；
- 整个数据库，使用ON database.*； 例子中就是这个
- 特定的表，使用ON database.table；
- 特定的列；
- 特定的存储过程
权限
| 权 限 | 说 明 |
|---|---|
| ALL | 除GRANT OPTION外的所有权限 |
| ALTER | 使用ALTER TABLE |
| ALTER ROUTINE | 使用ALTER PROCEDURE和DROP PROCEDURE |
| CREATE | 使用CREATE TABLE |
| CREATE ROUTINE | 使用CREATE PROCEDURE |
| CREATE TEMPORARY TABLES | 使用CREATE TEMPORARY TABLE |
| CREATE USER | 使用CREATE USER、 DROP USER、 RENAME USER和REVOK ALL PRIVILEGES |
| CREATE VIEW | 使用CREATE VIEW |
| DELETE | 使用DELETE |
| DROP | 使用DROP TABLE |
| EXECUTE | 使用CALL和存储过程 |
| FILE | 使用SELECT INTO OUTFILE和LOAD DATA INFILE |
| GRANT | OPTION 使用GRANT和REVOKE |
| INDEX | 使用CREATE INDEX和DROP INDEX |
| INSERT | 使用INSERT |
| LOCK | TABLES 使用LOCK TABLES |
| PROCESS | 使用SHOW FULL PROCESSLIST |
| RELOAD | 使用FLUSH |
| REPLICATION CLIENT | 服务器位置的访问 |
| REPLICATION SLAVE | 由复制从属使用 |
| SELECT | 使用SELECT |
| SHOW DATABASES | 使用SHOW DATABASES |
| SHOW VIEW | 使用SHOW CREATE VIEW |
| SHUTDOWN | 使用mysqladmin shutdown（用来关闭MySQL） |
| SUPER | 使用CHANGE MASTER、 KILL、 LOGS、 PURGE、 MASTER和SET GLOBAL。还允许mysqladmin调试登录 |
| UPDATE | 使用UPDATE |
| USAGE | 无访问权限 |
简化多次授权 可通过列出各权限并用逗号分隔，将多条GRANT语句串在一起，如下所示：
GRANT SELECT , INSERT ON crashcourse.* TO bforta;
28.2.4 更改口令
为了更改用户口令，可使用SET PASSWORD语句。
SET PASSWORD FOR bforta = Password('n3wp@$$w0rd');
SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。
SET PASSWORD还可以用来设置你自己的口令
SET PASSWORD = Password('n3wp@$$w0rd');
在不指定用户名时， SET PASSWORD更新当前登录用户的口令。
第 二 十 九 章 数据库维护
如何进行常见的数据库维护。
29.1 备份数据
- 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。
- 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
- 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。
首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。
29.2 进行数据库维护
- ANALYZE TABLE，用来检查表键是否正确。
ANALYZE TABLE orders;
image.png
- CHECK TABLE用来针对许多问题对表进行检查。
CHECK TABLE orders, orderitems;
image.png
29.3 诊断启动问题
在排除系统启动问题时，首先应该尽量用手动启动服务器。 MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：
- --help显示帮助——一个选项列表；
- --safe-mode装载减去某些最佳配置的服务器；
- --verbose显示全文本消息（为获得更详细的帮助消息与--help联合使用）；
- --version显示版本信息然后退出。
29.4 查看日志文件
主要的日志文件有以下几种。
- 错误日志。
show variables like 'log_error';
image.png
- 查询日志。
它记录所有MySQL活动
show variables like 'general_log_file';
image.png
- 二进制日志。
它记录更新过数据（或者可能更新过数据）的所有语句。
- 缓慢查询日志。
顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。
show variables like 'slow_query_log_file';
image.png
GitHub链接：
https://github.com/lichangke/LeetCode
知乎个人首页：
https://www.zhihu.com/people/lichangke/
简书个人首页：
https://www.jianshu.com/u/3e95c7555dc7
个人Blog:
https://lichangke.github.io/
欢迎大家来一起交流学习
