数据库之视图，事务，存储过程-34

内容：
        1.navicat
        2.视图
        3.事务
        4.存储过程
        5.触发器（无）
        6.函数（无）

2.视图

视图是什么?

本质是一张虚拟的表

他的数据来自select语句

 

 

有什么用?

原表安全

案例: 在一公司中需要一张表保存所有人的薪资信息

这个表不是所有都能午全看到 老板 财务 可以

某一个员工 只能看到自己的信息

所以不能把整个表的信息开发给这个员工

 

功能1,隐藏部分数据 开放指定的数据

功能2,因为视图可以将查询结果保存特性 我可以用视图 来达到减少书写sql的次数

例如:select *from emp where dept_id = (select id from dept where name = "市场");

要查询市场的人

将查询结果作为一个视图 以后在使用到这个需求 就直接查看视图

 

如何使用

创建视图

create view test_view as select *from t1;

 

特点:

1.每次对视图进行的查询 其实都是再次执行了 as 后面的查询语句

2.可以对视图进行修改 修改会同步到原表（修改原表也会影响视图，因为第一个特点）

3.视图是永久存储的 存储的不是数据 而就是一条 as sql语句

 

基本不用 因为 你的程序开放的数据 不是开放sql语句 而开放的是查询结果

3.事务

 

因为并发访问 导致的一些问题

1.脏读：A事务读取B事务尚未提交的更改数据，并在这个数据的基础上进行操作，这时候如果事务B回滚，那么A事务读到的数据是不被承认的。例如常见的取款事务和转账事务：

 

 

2.不可重复读

不可重复读是指A事务读取了B事务已经提交的更改数据。假如A在取款事务的过程中，B往该账户转账100，A两次读取的余额发生不一致。

3.幻读

A事务读取B事务提交的新增数据,会引发幻读问题。幻读一般发生在计算统计数据的事务中，例如银行系统在同一个事务中两次统计存款账户的总金额，在两次统计中，刚好新增了一个存款账户，存入了100，这时候两次统计的总金额不一致。

 

 

 

注意：不可重复读和幻读的区别是：前者是指读到了已经提交的事务的更改数据（修改或删除），后者是指读到了其他已经提交事务的新增数据。对于这两种问题解决采用不同的办法，防止读到更改数据，只需对操作的数据添加行级锁，防止操作中的数据发生变化；二防止读到新增数据，往往需要添加表级锁，将整张表锁定，防止新增数据（oracle采用多版本数据的方式实现）。

4.第一类丢失更新

A事务撤销时，把已经提交的B事务的更新数据覆盖了。例如：

 

 

这时候取款事务A撤销事务，余额恢复为1000，这就丢失了更新。

 

四种隔离级别

读未提交

读已提交

可重复读 （默认此级别）

串行化

 

 

强调: 事务就是一堆sql语句的集合 它们是原子性的 要么全部执行 要么都不执行

 

 

mysql这个客户端 默认 开启自动提交 一条sql语句就是一个单独的事务

 

pymysql 默认是不自动提交 需要手动commit （默认就开启了事务）

 

事务 你需要掌握的就是

 start transaction; 开启一个事务

 commit 提交事务

rollback 回滚事务

===========================================================

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！（也就是一系列的sql语句组成的功能）

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。（pymysql模块默认就是不支持commit的，需手动提交）

事务控制语句：

• BEGIN或START TRANSACTION；显式地开启一个事务；
• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier；把事务回滚到标记点；
• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

=============================================================================================

4.存储过程

存储过程是什么?

你可以理解为mysql的编程语言

为什么 有了python 还要弄出来这种编程语言?

 

他的作用 可以将你的程序业务逻辑 放到mysql中来处理

这样可以降低网络访问次数 从而提高你的程序效率

 

既然如此 你不能把所有与数据存储相关的业务逻辑 全都放mysql中?

能

但是 对于公司而言 需要再请一个 mysql开发者

对于你个人来说 提高沟通成本

 

三种开发的模型

对于同样一个业务 你可以放到python也可以放到mysql 有什么区别?

 

 

1. 应用程序 处理逻辑

需要手动编写 sql语句

优点:执行效率高

缺点: 开发效率低

mysql

 

 

2.

应用程序

 

mysql 处理逻辑

特点: 应用程序开发者不需要需要手动编写 sql语句

mysql开发者来编写

 

优点: 应用程序开发效率高

缺点: 执行效率略低,沟通成本增高

 

自动帮你生成对应的sql语句 比如你要注册用户 本来要写insert 语句 现在使用orm调用save(用户对象)

优点:开发效率高

缺点:执行效率降低

 

 

存储过程相当于python中的一个函数

简单地说 学习存储过程就是学习 如何使用mysql编写一个函数

 

语法:

create procedure 过程的名称 ({in,out,inout} 参数名称 数据类型 )

begin

具体的sql代码

end

参数前面需要指定参数的作用

in 表示该参数用于传入数据

out 用于返回数据

inout 即可传入 也可返回

 

参数类型是 mysql中的数据类型

 

案例:创建一个存储过程 作用是将两个整数相加

create procedure add_p (in a int,in b int)

begin

select a + b;

end

//

调用 call add_p(1,2)

 

案例:创建一个存储过程 作用是将两个整数相加 将结果保存在变量中

定义一个变量

set @su = 100;

create procedure add_p2 (in a int,in b int,out su int)

begin

set su = a + b;

end

 

//

调用存储过程 使用 call 过程名称

定义变量 set @su = 100;

调用过程 call add_p2(10,20,@su);

注意 在存储过程中 需要使用分号来结束一行 但是分号有特殊含义

得将原始的结束符 修改为其他符号

delimiter //  #结束符更换为//

delimiter;     #用完再改回来

 

mysql中的if语句

if 条件 then

代码

elseif 条件 then

代码

else then

代码

end if;

 

案例:

使用存储过程 完成 输入 一个 数字 1或2 显示 壹 或 贰

create procedure show_p (in a int)

begin

if a = 1 then

select "壹";

elseif a = 2 then

select "贰";

else

select "other";

end if;

end //

 

call add_p(10,20);

 

 

其他的流程控制

switch

case

while

repeat == do while

 

 

总结: 实际上一个mysql中的类似函数的东西 我们可以用它实现一些逻辑处理

特点:里面可以包含流程控制语句 和 普通的sql语句

 

使用存储过程的优势：

               提高应用程序开发效率

               降低网络访问次数