MySQL深入之约束、事务和视图

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。在建表或添加/修改字段时使用。

目的：保证数据库中数据的正确、有效性和完整性。

PS：AUTO_INCREMENT（自增）是列属性而不是约束。

PS：当定义唯一约束时，尽管字段相等无法插入，其还是提交过申请了。

PS：外键约束是用来维系两个表之间的物理关系的约束，该物理关系基于开发者设定的逻辑关系。

1. 外键约束的添加与删除

#外键约束		添加外键约束时，被添加的主表的字段要是主键或者有唯一索引
#在创建时候添加
create table 表名(
	字段名 数据类型,
    ...,
    [constraint] [外键名] foreign key(外键字段名) references 主表名(主表字段名)
);
#在后续添加
alter table 表名 add constraint [外键名] foreign key(外键字段名) references 主表(主表字段名);

#删除外键
alter table 表名 drop foreign key 外键名;

PS：建立了外键约束后，去删除修改关联的主表数据会被阻止。

2. 外键约束父表记录的删除/更新

alter table 表名 add constraint 外键名 foreign key(外键字段名) references 主表名(主表字段名) on update cascade on delete cascade;

事务

事务：事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。（跟原子性操作一样，只有成功和失败。）

这边举一个例子就是：

这个银行转账事务，只会出现两种情况：

• 张三的钱减去了，李四的钱加上了。
• 张三的钱没减去，李四的钱也没加上。

不可能出现张三的钱减去了而李四的钱没加上。

这就是要么都失败，要么都成功。

1. 查看/设置事务提交方式

select @@autocommit;
set @@atuocommit = 0;
#@@autocommit = 1 时是自动提交
#@@autocommit = 0 时是手动提交

2. 提交事务

commit;

3. 回滚事务

rollback;

4. 开启事务

start transaction;
#或者
begin;

5. 控制事务

方式一

#设置事务提交方式为手动
set @@autocommit = 0;

#此时执行sql语句
#当不输入commit时，表中的数据是不会改变的。

#当sql语句执行完毕且正确时，执行commit提交上去。
commit;
#此时表中数据发生修改。

#当sql语句执行完毕但出现错误时，执行rollback回滚回去。
rollback;
#此时表中数据不会发生修改。

PS：到这里你可能会有疑问？

既然它不执行commit语句不会改变表中数据，那么我为什么还要使用rollback回滚回去？

当然，虽然表中数据没改变，但是sql语句是切切实实的执行了，如果不进行回滚，那么下次执行sql语句并提交时，会把本次执行出错的sql语句提交上去。

这里的sql语句相当于生成了临时变量，而rollback相当于把临时变量清空。不让其影响下一次commit操作。

方式二

#当前事务提交方式为自动提交
#当执行sql语句时使用start transaction临时开启一个需要手动提交的事务对于选中的sql语句。
start transaction;
#将start transaction与sql语句一起选中

#此时执行完sql语句时和手动提交方式一样，需要commit或rollback

6. 事务的四大特性

• 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性：事务完成时，必须是所有的数据都保持一致状态。
• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

7. 并发事务问题

事务隔离级别

PS：事务隔离级别越高，数据越安全，但是性能越差。

#查看事务隔离级别
select @@transaction_isolation;

#设置事务隔离级别
set [session|global] transaction isolation level 隔离级别

并发事务问题示例：

脏读和不可重复读

在两个窗口中分别开启事务，模拟并发执行。

第一个事务

#查看目前的事务隔离级别
select @@transaction_isolation;
#设置事务隔离级别为read uncommitted
set session transaction isolation level read uncommitted;
#开启一个手动事务
start transaction;
#第一次查看表中数据
select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  2000 |
|  2 | 李四 |  2000 |
+----+------+-------+

#在第二个事务更改过后第二次查看表中数据
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | 张三 |  1000 |
|  2 | 李四 |  2000 |
+----+------+-------+
#可以看到，此时虽然第二个事务并未手动commit且表中数据仍未改变，但是还是读到了，这就是脏读。
#同时两次查询的结果不同，这是不可重复读。
commit;

第二个事务

#开启一个事务
start transaction;
#在第一个事务第一次查看过后更改数据
update account set money = money - 1000 where name = '张三';

幻读

依然开启两个窗口。

第一个窗口

#开启一个事务
start transaction;
#设置事务隔离级别
set session transaction isolation level repeatable read;

#查找id = 3的记录
select * from account where id = 3;
#会显示Empty set (0.00 sec)，即没查到

#在第二个窗口添加后输入
insert into account(id,name,money) values(3,'王五',2000);
#结果显示ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'

#此时再查询
select * from account where id = 3;
#仍然显示Empty set (0.00 sec)

#这就奇怪了，好似出现幻觉了，这就是幻读。

第二个窗口

#开启一个事务
start transaction;
#在第一个窗口查找后添加前输入
insert into account(id,name,money) values(3,'王五',2000);
commit;

视图

**视图：**视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图是动态生成的。

通俗的讲，视图只保存了查询的sql逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条sql查询语句上。

视图的作用：

• 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
• 数据独立：视图可以帮助用户屏蔽真实表结构变化带来的影响。

1. 创建视图

#创建或替换一个视图
create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded|local]check option];

2. 查询视图

#查看视图
select * from 视图名 ...;
#查看创建视图时的语句
show create view 视图名;

3. 修改视图

#方式一
create or replace view 视图名[(列名列表)] as select语句[with[cascaded|local]check option];
#方式二
alter view 视图名[(列名列表)] as select语句 [with[cascaded|local]check option];

4. 删除视图

drop view [if exists] 视图名;

5. 视图的检查选项

当使用with check option子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入、更新、删除，以使其符合视图的定义。

MySQL允许基于另一个视图创建视图，他还会检查依赖视图中的规则以保证一致性。为了确定检查的范围，MySQL提供了两个选项：

cascaded和local，默认为cascaded。

（1）cascaded

当视图依赖视图来创建时，尽管依赖的视图没有设定检查，但是该视图设定了，那么在检查时不仅要检查该视图的条件是否满足，也要检查依赖视图的条件是否满足。

相当于在v1中也加上了检查。

当视图依赖视图来创建时，v2具有检查，但是依赖于v2创建的视图v3没加检查，那么添加数据检查时不会检查v3的条件，而会检查v2和v1的条件。

（2）local

当视图依赖视图创建时，v4依赖于表，v5依赖于v4，如果v4没有设定检查选项，v5设定了local检查选项。这里添加数据检查时依旧会向上找，但是v4没有设定检查选项就不再给其添加而是不检查。

如果v4设定了检查选项，v5也设定了检查选项，这里添加数据检查时会向上找，并且检查v5和v4。

当v5没有设定检查选项而v4设定了检查选项时，在v5中添加数据仍会向上找v4的检查选项。

6. 视图的更新

要使视图可更新，视图中行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（sum()、min()、max()、count()…）
• distinct
• group by
• having
• union或者union all