SQL学习经验总结（第六期）共6期

目录

十一、事务与锁

一、事务

1、事务的基本概念

2、事务的特性

3、事务的控制

4、关闭与打开隐式事务自动提交

5、回滚

6、提交

7、显式事务

8、事务的隔离

9、事务隔离级别的验证

二、锁

1、锁的基本概念

2、锁的类型

3、读锁和写锁

4、加锁和解锁

十二、游标和条件处理

一、游标

1、游标的基本概念

2、游标的使用

3、游标使用时的注意事项

4、游标应用示例

二、条件处理

1、条件处理的基本概念

2、定义条件处理

3、条件处理演示示例

4、条件处理函数

5、条件处理函数演示示例

6、获取错误信息

7、更改错误信息

---

十一、事务与锁

一、事务

1、事务的基本概念

        事务是作为单个逻辑单元所执行的一系列操作，是一个整体，不可被拆分。

        如：新建一个数据库或表格，对表数据的插入、更新、删除，执行一个存储过程或函数等，都是一个事务。

2、事务的特性

        事务有 ACID 四个基本要素（特性）。

        ①A：Atomicity ，[ˌætəˈmɪsəti] 是指事务要具有原子性。事务是单独逻辑单元，是最小单元，是一个整体，不可再分。

        一个逻辑单元要么全部完成，要么全部不完成。也就是说要么都执行成功，要么都不执行，没有一半执行，一半不执行的，也就是不可细分的。同理，存储过程、函数和触发器等都是符合这一特性的。

        ②C：consistency，[kənˈsɪstənsi] 是指事务要具有一致性。一个事务改变了表中的数据，另一个事务查询出来被改变的数据应该要是一致 的。

        ③I：Isolation，[ˌaɪsəˈleɪʃn] 是指事务要具有隔离性。多个事务之间是互相隔离，互不干扰的。

        ④D： Durability，[ˌdjʊərəˈbɪlɪti] 是指事务的持久性。事务一旦成功提交，数据就不会因为其他原因或者无缘无故的改变。

        每一个事务都应该同时满足以上四个特性，不可能存在不满足这四个特性的事务。

3、事务的控制

        事务的控制有两种方法：

        ① 隐式事务

        隐式事务是不用管理员手动写的，是指单独的 insert 、update 、 delete 等进行数据操作的时候默认是隐式事务，由系统自动提交。

        ② 显式事务

        显式事务就不是由系统自动提交的，是需要用户手动提交的。

4、关闭与打开隐式事务自动提交

        隐式事务自动提交是由一个变量来控制的。可以查看自动提交变量的状态，它默认是打开的。

①查看事务的状态：

show variables like 'autocommit';

②关闭隐式事务自动提交

通过设置自动提交变量的值为off或者0来关闭自动提交。

关闭自动提交的基本格式：

set autocommit = off; # 或者设置为0

关闭自动提交后，测试一下执行delete/insert/update语句执行后的效果。

如：删除数据表test1中的数据

delete from test1 where id = 4; #这里是真的删除了么 
select * from test1; # 没有真的删除只是把临时结果集中的数据删除了而已

③打开隐式事务自动提交

通过设置自动提交变量的值为on或者1来打开自动提交。

打开自动提交的基本格式：

set autocommit = on; # 或者设置为1

5、回滚

        没有设置提交的数据并没有从数据表中真实删除，就是因为数据没有自动提交，数据只是从查询结果集 中删除了，此时我们打开另外一个命令列界面就可以查询出没提交之前的数据。

        所以没有自动提交的数据是支持回滚（撤销）的，回滚用来撤销原来没有提交的操作。

        回滚关键字：rollback

        回滚的基本格式：

rollback;

        执行一下回滚，然后再次查看数据表test1中的数据，会发现刚才执行的删除语句没有效果，或者说是被撤销了。

6、提交

        提交（确认事务、刷新事务）想要将没有自动提交的事务确认提交，也就是需要手动提交了。

        提交事务关键字：commit

        提交的基本格式：

commit;

        确认提交后就不能撤销已提交的事务了，回滚也就没有用了。

7、显式事务

set autocommit = 1; # 确认开启隐式事务自动提交

①开启事务的显示控制

start transaction; # 开始显式事务

②测试数据的删除

delete from orders where id = 2; # 删除数据

是否删除？

rollback; # 回滚（不删除）

commit; # 提交（确认删除）

        显示控制就相当于删除文件时会提示“是否确认删除”一样，显式事务需要手动提交（确认）。

        显式的处理事务，会暂时停止自动提交，一次回滚或者提交之后还原成自动提交。

8、事务的隔离

        事务的隔离主要用于解决多个用户同时进行同一数据表的读写操作而导致的一系列问题。

        在数据库的使用过程中，无法避免的会有多个用户同时使用一个数据表的情况，所以就有了事务并发控 制的概念，也就是每个用户的事务要隔离开来。

        事务的隔离级别（从低到高）如下所示：

        ① 读未提交：就是一个用户执行一个事务但没提交，另一个用户可以读到没提交的数据。 读未提交级别会导致“脏读”问题。什么是脏读？例如：A用户执行一个事务，B用户紧接着读取这些数 据，但是之后A用户回滚了这个事务，那B用户所读取的数据有效么。 # 其中会存在有些数据是无效的。

        ② 读提交（大多数数据库默认）：就是一个用户执行一个事务，只有提交了，另一个用户才可以读到提 交后的数据。其解决了“脏读”问题，但是会产生一个新的问题：“不可重读”问题。 所谓的“不可重读”，例如：B用户先查找表中的数据，然后A用户删除了表中的某些数据，最后B用户对 之前查找出来的但现在已被A用户删除的数据进行修改，但那个数据已被删除，是不存在的。

        ③ 可重读（Mysql默认）：解决了不可重读的问题，但是会出现新的问题：“幻读”问题。 所谓的“幻读”问题，例如：用户A和用户B同时进行事务操作，对于它们读取数据所形成的临时表，它们 可以对各自的临时表分别进行不同的操作，这样操作的结果最终是以最后提交的事务为准的，那先提交 的用户再次看到数据时会发现数据不一致了。

        ④ 串行化读：就是根本不允许并发，也就是不允许多个用户同时操作一个表。例如：最先使用事务修改表1的用户A就会把表1给占用了，其他用户只能等用户A提交事务完成后才能执 行对表1的操作。也就是先来后到，一个一个轮流着来，只要前面还有一个用户在操作，之后的用户就需 要等待。

此方法虽然解决了许多存在的问题，但是这样做的坏处也很明显，也就是不能多用户同时操作，牺牲了用户的时间来保证数据的准确性和一致性。

9、事务隔离级别的验证

        隔离级别有两种：

① 会话级别的隔离（当前连接有效）

        查看当前会话级别的隔离：

5.7 版本：

select @@session.tx_isolation;

8.0以上版本：

select @@transaction_isolation;

Mysql默认为'REPEATABLE-READ',可重读。

会话级别隔离的设置：

# 设置当前会话隔离级别为-读未提交 
set session transaction isolation level read uncommitted; 

# 设置当前会话隔离级别为-读提交 
set session transaction isolation level read committed;
 
# 设置当前会话隔离级别为-可重读 
set session transaction isolation level repeatable read; 

# 设置当前会话隔离级别为-串行化读 
set session transaction isolation level serializable;

② 全局级别的隔离（当前服务有效）

select @@global.tx_isolation;

        它是由一个全局变量tx_isolation保存的，Mysql默认为'repeatable-read'，可重读。

        查看当前全局级别的隔离：

        全局级别隔离的设置：

# 设置当前全局隔离级别为读未提交 
set global transaction isolation level read uncommitted; 
# 设置当前全局隔离级别为读提交 
set global transaction isolation level read committed; 
# 设置当前全局隔离级别为可重读 
set global transaction isolation level repeatable read; 
# 设置当前全局隔离级别为串行化读 
set global transaction isolation level serializable;

③隔离级别的验证

        以会话级别的隔离举例：

        首先创建一个测试表并插入数据。

        然后打开两个 cmd 命令界面，进入Mysql，打开数据库，每一个界面都分别代表一个用户。

        然后分别设置两个cmd窗口中的会话隔离级别，在两个界面中测试各个级别的隔离效果，是否会出现事 务隔离的问题。

        最后研究各种隔离级别的特点和其所出现的问题。

二、锁

1、锁的基本概念

        锁是计算机协调多个进程或线程并发访问某一资源的机制。锁能够保证数据并发访问的一致性、有效性。锁冲突也是影响数据库并发访问性能的一个重要因素。

        #多个用户同时读不需要加锁。只有在用户写时才需要加锁，用于保证数据的一致性。

2、锁的类型

        Mysql中不同的存储引擎所使用的锁是不同的。

① 表级锁

        一个用户在访问一个表时，对整张表进行加锁，另外一个用户就访问不了这个表了。多个用户不能同时 使用一张表。

        MyISAM和MEMORY存储引擎采用表级锁。

② 页面锁

        将表分成许多页，只对某一页加锁。页面锁可以精确到表的一部分，只锁表里的一部分数据。 BDB存储引擎采用页面锁；而innodb存储引擎表级锁和行级锁都有采用，默认是行级锁。

3、读锁和写锁

① read读锁（共享锁）

        当MySQL的一个进程为某一表加了读锁之后，其他的进程包含自身都没有权利去修改这张表的内容，但是所有的进程可以读出表里面内容。

例如：

        事务N在A对象上加了共享锁S，其他事务就不可修改表，但可以查询A，并且也可以加共享锁。

② write写锁（排他锁）

        当某一个进程在对某一张表加了写锁，完成某些操作之后，如果不释放写锁，其他的进程连查看这张表的权限都没有，只有等它解除写锁后，其他的进程才可以完成相应的操作。如果该进程没有对该表进行 更新操作，其他的进程只能做查询操作，但是无法实现更新操作。

例如：

        事务N在A对象上加了排他锁X，其他事务不可读不可写，也不可以加锁。

4、加锁和解锁

        用户读数据时自动加read锁，修改数据自动加write锁。

① 加锁的基本格式

Lock tables <表名> <锁类型>; # 锁类型：read/write

② 解锁的基本格式

unlock tables; #解除当前锁的限制，不用给定表名，直接解除当前用户所有加锁的表

③ 锁多张表

lock tables 表名1 <表名2 表名3 …… 表名n >锁类型

十二、游标和条件处理

一、游标

1、游标的基本概念

①什么是游标

        在查询表时，会获得一个查询结果集，游标就是用来遍历这个结果集中每一条记录的，把查询结果集看 作是一个容器，那游标就是类似于迭代器一样的东西。

②游标的作用

        游标可以返回结果集中一行或多行数据，结果集是存在数据缓冲区里的，游标可以从数据缓冲区里读取 相应的数据。

        游标只能用在存储过程和函数中，并且一次只能指向一条记录。 游标类似于C语言中的指针，可以指向一块数据内存，然后通过指针访问数据，也可以理解成c++中的容器的迭代器。

        事务是作为单个逻辑单元所执行的一系列操作，是一个整体，不可被拆分。

        如：新建一个数据库或表格，对表数据的插入、更新、删除，执行一个存储过程或函数等，都是一个事务。

2、游标的使用

        游标的基本使用如下：

①声明游标(定义游标)

declare 游标名 cursor for select查询语句;

②打开定义的游标

open 游标名;

③遍历游标(使用游标)

fetch 游标名 into 值列表(变量);

④使用完释放游标(关闭游标)

close 游标名;

3、游标使用时的注意事项

        游标在遍历完成后会指向最后一条记录的下一条，由于下一条没有数据，就会出现 no data to fetch 的错误。

        为了解决这个错误，就需要定义一个条件处理函数NOT FOUND 来捕获这一个异常。没有异常处理的话 就会报错。

如：

declare 游标 cursor for # 定义游标 
select 属性名 from 表格 where 条件; 
# 声明错误处理函数 not found ，来捕获遍历结束的错误 
declare continue handler for not found; 
# 条件处理，遇到没有找到数据的错误，就执行一段命令 
# 这条命令要写在定义游标之后

4、游标应用示例

使用游标读取一张成绩表中的内容：

delimiter $$
create procedure cursor_test()
begin
declare id int; -- 定义三个用于存储数据的变量
declare title varchar(255);
declare c_date date;
declare counts int default 0; -- 定义统计变量
declare cur_exit bool default false; -- 定义退出条件的变量
declare cur_test cursor for select * from course_tb;-- 定义游标
declare continue handler for not found set cur_exit=true;
-- 当游标读到末尾的时候，就将退出的条件设置为true
open cur_test; -- 打开游标
cur:loop
fetch cur_test into id,title,c_date; -- 循环遍历游标
if cur_exit then leave cur; -- 退出条件为true就跳出循环
end if;
select id,title,c_date; -- 将读取出的数据输出
set counts = counts+1; -- 统计数量
end loop;
select counts;
close cur_test; -- 关闭游标
end$$
delimiter ;

二、条件处理

1、条件处理的基本概念

①什么是条件处理

        条件处理就是MySQL数据库中的异常处理（错误处理）。

②条件处理的作用

        MySQL通过条件处理来捕获与处理错误和异常。

        注意：条件处理只能在存储过程中使用，并且存储过程和条件处理只有 MySQL5.5 版本之后才支持，如果用的是 MySQL5.5 之前的版本就不要用条件处理了。

2、定义条件处理

        使用命令行（？ declare handler）语句获取语法格式

①条件处理的基本格式：

DECLARE <handler_action> HANDLER
FOR <condition_value [, condition_value] ...>
statement
# 参数解析：
handler_action: CONTINUE | EXIT | UNDO # 处理动作名
-- 捕获后怎么处理？ 继续 | 退出 | 不处理(MySQL不支持)
condition_value: # 错误值
mysql_error_code | SQLSTATE [VALUE]sqlstate_value
-- 错误码 | 状态值
|condition_name|SQLWARNING|NOT FOUND| SQLEXCEPTION
-- |所有以01开头的|所有以02开头的|除了00、01、02开头的错误

②错误码和状态值：

-- 例如：use 不存在的数据库名;
-- 使用不存在的数据库
use db_6;
ERROR 1049 (42000): Unknown database 'db_6' -- 错误信息
-- 1049为错误码，42000为状态值
-- 声明错误条件不能单独使用，要配合处理函数使用

3、条件处理演示示例

① 无错误处理的存储过程

create procedure p_test1()
begin
select * from untable; -- 数据表untable不存在
end;
call p_test1();
-- 出现错误：
ERROR 1146 (42S02): Table 'db_5.untable' doesn't exist
-- 调用存储过程后会报错：错误码 1146 (42S02)数据表不存在
-- 所有mysql中所定义的错误码和状态值都可以在MySQL官网上查找

② 在存储过程中用错误码进行错误处理

create procedure p_test2()
begin
declare continue handler for 1146 -- 定义条件处理，使用错误码处理此类错误
select '此表不存在' as 错误;
select * from untable;
end;
call p_test2();
-- 调用存储过程后不会报错，而是按照自己所设置的进行处理

③在存储过程中用状态值进行错误处理

create procedure p_test3()
begin
declare continue handler for sqlstate '42S02' -- 定义条件处理，使用状态值处理
此类错误
select '此表真的不存在' as 错误;
select * from untable;
end;
call p_test3();

4、条件处理函数

        错误条件（？ declare condition） 语句获取语法格式

错误条件函数的声明（？ declare condition）
DECLARE condition_name CONDITION FOR condition_value -- 声明一个条件处理
-- 给抛出的异常取一个有意义的名字，相当于给错误取个别名
condition_value: -- 条件值
mysql_error_code -- 错误码
| SQLSTATE [VALUE] sqlstate_value -- 状态类 错误类名

5、条件处理函数演示示例

①在存储过程中用条件处理函数给错误码取别名进行错误处

create procedure p_test4()
begin
declare no_have_table condition for 1146; -- 定义错误处理函数no_have_table
处理错误
declare continue handler for no_have_table
select '此表还是不存在' as 错误;
select * from untable;
end;
call p_test4();

②在存储过程中用状态值进行错误处理

create procedure p_test5()
begin
declare no_have_table condition for sqlstate '42S02';
declare continue handler for no_have_table
select '说了此表不存在了还查' as 错误;
select * from untable;
end;
call p_test5();

6、获取错误信息

①通过get diagnostics获取错误信息。

一般格式：

get diagnostics condition 错误信息位置 -- 当前位置之前的第几条错误
@变量名1= mysql_errno, -- 获取错误码
@变量名2= returned_sqlstate, -- 获取状态值
@变量名3= message_text; -- 获取错误信息文本

如：

-- 删除一个不存在的表
drop table untable;
-- 错误信息：ERROR 1051 (42S02): Unknown table 'db_5.untable'
get diagnostics condition 1 -- 捕获最近的一条错误信息（上一条错误信息）
@x = mysql_errno,
@y = returned_sqlstate,
@z = message_text;
select @x,@y,@z; -- 查询已获取到的错误信息

7、更改错误信息

①通过resignal / signal 更改错误信息。

一般格式：创建一个存储过程实现错误信息的处理

create procedure test_signal()
begin
declare continue handler for sqlstate '42S02'
begin
signal sqlstate '42S02' # sqlstate '42S02' = 1051
-- 这里使用resignal不用指定错误，直接写resignal就可以了
set schema_name = 'newdb', -- 数据库名
table_name = 'newdb_tb', -- 表名
message_text = '没有这个表', -- 错误信息
mysql_errno = 11; -- 错误码
end;
drop table untable;
end;
call test_signal();

经过更改错误信息的处理之后，出错的语句仍然会报错，但是错误提示信息已经被改变了。

resignal在功能和语法方面与SIGNAL语句相似，只是其默认修改上一条错误信息，必须在错误或警告 处理程序中使用。

signal可以指定错误信息，也可以省略SIGNAL语句的所有属性，默认修改上一条错误信息，能在存储过程中的任何位置使用。

②其他错误信息：

class_origin -- 原始类名
subclass_origin -- 原始子类名
constraint_catalog -- 约束目录
constraint_name -- 约束名
catalog_name -- 目录名
column_name -- 列名
cursor_name -- 游标