轻松掌握MySQL数据库（二）

事物控制

事务（Transaction）是指作为一个逻辑工作单元执行的一系列操作，这些操作要么全部成功，要么全部失败。事务确保对多个数据的修改作为一个单元来处理。

• 例：在ATM机上进行转账，张三给李四转帐100块钱，要么成功，要么失败，并且需要同步操作。在张三给李四转帐一百块成功时，张三账户余额 -100，李四账户余额 +100。

1. 在MySQL中，只有使用了Innodb存储引擎的数据库或表才支持事务。
2. 事务用于维护数据库的完整性，保证成批的sql语句要么都执行，要么都不执行
3. 事务用于管理INSERT、UPDATE和DELETE语句。

• 如果某个数据库声称支持事务，那么该数据库必须具备ACID四个特性，即Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）。
• 在默认情况下，MySQL是自动提交事务的，即每一条INSERT、UPDATE、DELETE的SQL语句提交后会立即执行COMMIT操作。因此，要开启一个事务，可以使用start transaction或begin，或者将autocommit的值设置为0

使用UPDATE语句进行更新内容

• 语法： UPDATE 更新UPDATE 表名 SET 更新内容 [限制条件]
• 通过案例掌握事务控制

CREATE TABLE bank_account(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(30) COMMENT "姓名",
balance decimal(18,2) comment "账户余额"
);
INSERT INTO bank_account(id,name,balance) VALUES(1,"张三",0),(2,"李四",0);
//显示系统变量autocommit的值，值为1-->自动提交事务 可设置为0
SELECT @@autocommit;
SELECT * FROM bank_account;
//富婆给张三转钱
UPDATE bank_account SET balance = balance + 1000 WHERE id = 1;
//执行事务 张三有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 100 WHERE id = 1;
UPDATE bank_account SET balance = balance + 100 WHERE id = 2;
//提交事务
COMMIT;
SELECT * FROM bank_account;

• 操作MySQL命令难免会出现失误，这时可以用ROLLBACK命令进行回滚操作，但MySQL每执行一条事务默认提交即要开启一个事务(start transaction/begin)将autocommit的值设为0

使用ROLLBACK进行回滚操作
语法：ROLLBACK;

SELECT * FROM bank_account;
//执行事务 张三又有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 200 WHERE id = 1;
UPDATE bank_account SET balance = balance + 200 WHERE id = 2;
//这是发现钱不够，想撤回操作，进行回滚操作
ROLLBACK;
SELECT * FROM bank_account;
//这是发现，并没有回滚成功，因为MySQL每执行一条事务默认提交
//那么就要开启一个事务(start transaction/begin)将autocommit的值设为0或直接设置autocommit的值为0，再进行执行事务
SEART TRANSACTION; 或 BEGIN; 或 SET autocommit = 0;
//执行事务 张三又有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 200 WHERE id = 1;
UPDATE bank_account SET balance = balance + 200 WHERE id = 2;
//进行回滚操作
ROLLBACK;
SELECT * FROM bank_account;
//此时回滚成功

视图

视图是一个虚拟表，其内容由select查询语句定义。和真实的表一样，视图也包含行和列，对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成，视图中的数据都存储在基表中。

1. 可读性 简化了复杂的查询，使复杂的查询更易于理解和使用
   例如：一些复杂的查询结果封装到视图中，当需要的时候，直接查询视图即可
2. 安全性 视图可以隐藏一些敏感的信息可以把权限限定到行列级别
   例如：一张用户信息表，查询信息的会有一些敏感字段不需要显示，这时不能删除该字段，需要将查询的字段结果封装在视图中即可
3. 重用性 视图是对复杂查询语句的封装对数据库重构，不会影响程序的运行
   例如：将一些需要重复需要查询的操作结果封装到视图，需要时查询视图即可

视图的基本操作

视图是一个虚拟表，其内容由select查询语句定义。和真实的表一样，视图也包含行和列，对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成，视图中的数据都存储在基表中。视图表的数据变化会影响到基表，基表的数据变化也会影响视图表。

创建视图
语法：
CREATE [OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

• CREATE：表示创建视图的关键字
• OR REPLACE：表示该语句能够替换已有视图，但必须在视图上具有DROP权限
• ALGORITHM：可选值有UNDEFINED | MERGE | TEMPTABLE，表示视图使用的算法
• UNDEFINED：表示MySQL将自动选择所要使用的算法
• MERGE：表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
• TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句
• view_name：表示要创建的视图名称
• column_list：可选，表示属性清单。指定了视图中各个属性的名，默认情况下，与SELECT语句中查询的属性相同
• AS 表示指定视图要执行的操作
• SELECT_statement：是一个完整的查询语句，表示从某个表或视图中查出某些满足条件的记录，将这些记录导入视图中
• WITH CHECK OPTION：可选，表示创建视图时要保证在该视图的权限范围之内
• CASCADED：可选。表示创建视图时，需要满足跟该视图有关的所有相关和表的条件，该参数为默认值
• LOCAL：可选。表示创建视图时，只要该视图本身定义的条件即可
  注：
1. 创建视图时要求具有针对视图的CREATE VIEW权限，以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列，必须具有SELECT权限。如果还有OR REPLACE子句，必须在视图上具有DROP权限
2. 需要注意的是，视图属于数据库，在默认情况下，将在当前数据库创建新视图，要想在给定数据库中明确创建视图，创建时应将名称指定为db_name.view_name

自定义视图字段名称
语法：CREATE VIEW view_name(字段name...) AS SELECT 字段... FROM table_name;

查看视图信息
DESCRIBE语句可以查看视图的字段信息，其中包括字段名、字段类型等信息
语法：DESCRIBE view_name; 或 DESC view_view;
得到的信息对应字段

• Key：表示该列是否已经编制索引
• Default：表示该列是否有默认值
• NULL：表示该列是否可以存储NULL值
• Extra：表示获取到的与给定列相关的附加信息

使用SHOW TABLE STATUS语句查看视图
语法：SHOW TABLE STATUS LIKE 'view_name';
在上述格式中，“LIKE”表示后面匹配的是字符串，“view_name”表示要查看的视图的名称，视图名称需要使用单引号引起来

修改视图
修改视图是指修改数据库中存在的视图的定义，比如，当基本表中的某些字段发生时，可以通过修改视图的方式来保持视图与基本表的一致性

• 使用Update语句修改视图
  语法：Update view_name set 字段名= ，… WHERE id = 主键（指定修改对象），若无则修改全部

更新视图

• 使用UPDATE语句更新视图
  例：将AB_view视图中的sex字段改为"女"
  UPDATE AB_sex SET sex = “女”;
• 使用INSERT语句更新视图
  在表中插入了数据后， 对应的视图中的数据也随之改变
• 使用DELETE语句更新视图
  视图中删除某一段的记录后，视图中的一整条记录全部被删除了，对应的数据表中的整条记录也会被删除

删除视图

• 删除视图时，只能删除视图的定义，不会删除数据。删除一个或多个视图可以使用DROP VIEW语句
  语法：DROP VIEW[IF EXISTS] view_name [,view_name1]…
  视图名称可以添加多个，各个名称之间使用逗号隔开，删除视图必须拥有DROP权限

存储过程

存储过程（Stored Procedure）是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行，类似于编程语言中的方法或函数

• 存储过程的优点：

1. 存储过程是对SQL语句的封装，增强可复用性
2. 存储过程可以隐藏复杂的业务逻辑、商业逻辑
3. 存储过程支持接收参数，并返回运算结果

• 存储过程的缺点：

1. 存储过程的可移植性较差，如果更换数据库，要重写存储过程
2. 存储过程难以调试和扩展
3. 无法使用Explain对存储过程进行分析
4. 《阿里巴巴Java开发手册》中禁止使用存储过程

#存储过程定义：求两数之和
delimiter //	--> delimiter是声明语句结束符,防止定义存储过程中 ";" 出现混淆
create procedure my_sum(in a int, in b int, out result int) 
begin
 set result = a + b;
end
//
delimiter ;
# in 定义入参（也就是调用该存储过程需要传入的参数） out 定义出参数(存储过程需要返回的参数)
# SET 表明进行赋值操作
#存储过程调用
call my_sum(10, 20, @result);
select @result;

条件的定义

在MySQL中，特定异常需要特定处理。这些异常可以联系到错误，以及子程序中的一般流程控制。
定义异常是事先定义程序执行过程中遇到的问题，异常处理定义了在遇到问题时对应当采取的处理方式，并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。
语法：
DECLARE condition_name CONDITION FOR condition_value;

• condition_name参数表示条件的名称；
• condition_value参数表示条件的类型；
• sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。
• 例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

• handler_type（指明错误的处理方式）: 有以下三种取值
• continue(遇到错误不处理，继续执行) exit(遇到错误马上退出)
• undo(表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作)
• condition_value:表示错误类型,有以下六种取值

1. SQLSTATE[VALUE]  sqlstate_value包含5个字符的字符串错误值
2. condition_name  DECLARE CONDITION 定义的错误条件名称
3. sqlwarning  匹配所有以01开头的SQLSTATE错误代码
4. not found  匹配所有以02开头的SQLSTATE错误代码
5. sqlexception  匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
6. mysql_error_code  匹配数值类型错误代码

• sp_statement：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。

#方法一：捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND'; 
#方法二：捕获mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND'; 
#方法三：先定义条件，然后调用 
DECLARE  can_not_find  CONDITION  FOR  1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND'; 
#方法四：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
#方法五：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
#方法六：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

光标

在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。
光标是一种用于轻松处理多行数据的机制。
目前 mysql中的光标只能在存储过程或函数中使用
声明语法：DECLARE cursor_name CURSOR FOR select_statement
注意：声明位置要在变量和条件之后，在存储过程处理程序之前

• 光标的使用：
  打开OPEN cursor_name
  使用FETCH cursor_name INTO var_name[,var_name]…
  关闭光标：CLOSE cursor_name;

• 整体案例

DECLARE s_name,s_address CHAR(20);
DECLARE cursor_student CURSOR FOR SELECT name,address FROM student;
OPEN cursor_student;
FETCH cursor_student INTO s_name,s_address;
SELECT s_name,s_address;
CLOSE cursor_student;