一、视图
视图是有一张表或多张表的查询结果构成的一张虚拟表。
可以帮我们节省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;