11.26

一、视图

　　　　视图是有一张表或多张表的查询结果构成的一张虚拟表。

可以帮我们节省sql的编写，视图的另一个作用是,可以不同的视图来展示开放不同数据的访问。

创建视图

　　

CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS select_statement

加上OR REPLACE 时如果已经存在相同视图则替换原有视图

column_list指定哪些字段要出现在视图中

使用视图

视图是一张虚拟表 所以使用方式与普通表没有任何区别

二、事务

　　　　事务是逻辑上的一组操作,要么都成功,要么都失败。

很多时候一个数据操作,不是一个sql语句就完成的,可能有很多个sql语句,如果部分sql执行成功而部分sql执行失败将导致数据错乱!

使用事务

start transaction; --开启事物,在这条语句之后的sql将处在同一事务,并不会立即修改数据库

commit;--提交事务,让这个事物中的sql立即执行数据的操作,

rollback;--回滚事务,取消这个事物,这个事物不会对数据库中的数据产生任何影响

import pymysql

conn = pymysql.connect(
    user="root",
    password="root",
    database="day48",
    charset="utf8"
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
# cursor.execute("delete from account where id =1")
# conn.commit()

sql = "update account set money = money - 100    where id = 2"
sq2 = "update account set moneys = money + 100    where id = 3"
try:
    cursor.execute(sql)
    cursor.execute(sq2)
    conn.commit()
    print("提交了!")
except:
    print("回滚了!")
    conn.rollback()

事务的四个特性:

原子性:

​ 事务是一组不可分割的单位,要么同时成功,要么同时不成功

一致性:

​ 事物前后的数据完整性应该保持一致,(数据库的完整性:如果数据库在某一时间点下,所有的数据都符合所有的约束,则称数据库为完整性的状态);

隔离性:

​ 事物的隔离性是指多个用户并发访问数据时,一个用户的事物不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离

持久性:

​ 持久性是指一个事物一旦被提交,它对数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

三、存储过程

　　　　存储过程是一组任意的sql语句集合,存储在mysql中,调用存储过程时将会执行其包含的所有sql语句;与python中函数类似。

回顾触发器与视图都是为了简化应用程序中sql语句的书写,但是还是需要编写,而存储过程中可以包含任何的sql语句,包括视图,事务,流程控制等,这样一来,应用程序可以从sql语句中完全解放,mysql可以替代应用程序完成数据相关的的逻辑处理!

三种开发方式对比

1.应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给mysql来完成,通过存储过程(推荐使用)

优点:

应用程序与数据处理完解耦合,一堆复杂的sql被封装成了一个简单的存储过程,考虑到网络环境因素,效率高

应用程序开发者不需要编写sql语句,开发效率高

缺点:

python语法与mysql语法区别巨大,学习成本高

并且各种数据库的语法大不相同,所以移植性非常差

应用程序开发者与BDA的跨部门沟通成本高,造成整体效率低

2.应用程序不仅编写业务逻辑,还需要编写所有的sql语句

优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高

缺点:sql语句过于复杂,导致开发效率低,且需要考虑sql'优化问题

3.应用程序仅负责业务逻辑,sql语句的编写交给ORM框架,(常用解决方案)

优点:应用程序开发者不需要编写sql语句,开发效率高

缺点:执行效率低,由于需要将对象的操作转化为sql语句,且需要通过网络发送大量sql

创建存储过程

create procedure pro_name(p_Type p_name data_type)
begin
sql语句......流程控制
end

p_type 参数类型

in 表示输入参数

out 表示输出参数

inout表示既能输入又能输出

p_name 参数名称

data_type 参数类型 可以是mysql支持的数据类型

 

案例:使用存储过程完成对student表的查询

delimiter //
create procedure p1(in m int,in n int,out res int)
begin
    select *from student where chinese > m and chinese < n;
    #select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败
    set res = 100;
end//
delimiter ;
set @res = 0;
#调用存储过程
call p1(70,80,@res);
#查看执行结果
select @res;