目录:
1. 视图
2. sql注入问题
3. 事务
4. 事务的隔离级别
5. 存储过程
一. 视图
1. 视图是什么
本质是一张虚拟的表
他的数据来自select语句
创建视图
create view 视图名 as (select 查询语句);
create view test_view as select * from t1;
2. 有什么用?
原表安全
案例: 在一个公司中需要一张表保存所有人的薪资信息
这个表不是所有员工都能看到,只有老板和财务可以查看
普通的员工只能看到自己的信息,所有不能把整个表的信息开放给员工
功能1:隐藏部分数据,开放指定的数据
功能2:因为视图可以将查询结果保存特性,我可以用视图,来达到减少书写sql的次数
例如:select *from emp where dept_id = (select id from dept where name = "市场");
要查询市场的人
将查询结果作为一个视图 以后在使用到这个需求 就直接查看视图
3. 如何使用
创建视图
create view test_view as select *from t1;
特点:
1.每次对视图进行的查询 其实都是再次执行了 as 后面的查询
语句:
2.可以对视图进行修改 修改会同步到原表
3.视图是永久存储的 存储的不是数据 而就是一条 as sql语句
ps:基本不用:因为你开放程序的数据,不是sql语句,而是查询结果
二. sql注入问题
示例:
import pymysql
conn = pymysql.Connect(
user="root",
password="admin",
host="localhost",
database="day43",
charset="utf8"
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 当用户输入的用户名为字符串 为 yy' -- 时
# 最终产生的sql select *from user where user = 'yy' -- ' and pwd = '987657890';
# -- 用于mysql注释 意思是 后面的内容忽略掉
# 从而导致 密码是否正确都能登录成功
# "select *from user where user = 'axxax' or 1=1;
count = cursor.execute("select *from user where user = %s and pwd = %s;",args=(input("user"),input("pwd")))
print(count)
if count:
print("login success")
else:
print("login error")
cursor.close()
conn.close()
了解:
sql注入攻击
是什么意思?
一些了解sql语法的用户,可以输入一些关键字或合法sql,来导致原始的sql逻辑发生变化,从而跳过登录验证或者删除数据库
如何避免
在接收用户输入的数据时,可以加上限制,比如:不能输 -- ' ; where 等等
上面这种方式只能避免黑客从你的客户端软件注入 sql
但是无法避免,中间人攻击(在你的客户端和服务器中间加一个中转服务器)
这样就绕过了客户端的输入限制,此时只能将sql合法性验证放在服务器端
总结:
python如何避免sql注入? 把你的slq(用户输入的)参数 放execute函数的arg参数中 让pymysql 自动帮你屏蔽注入攻击
三. 事务
需要掌握:
START TRANSACTION; # 开启事务
COMMIT; # 提交事务
ROLLBACK; # 回滚
生活中的事务,你可以理解为事情
一件事情要完成,通常不能一步到位,需要拆分多个小步骤
例如 请您帮我买水
1.给你钱
2.你去买水
3.你给钱
4.拿水找钱
5.把水交给我
在mysql中事务 是什么?
是一组sql语句集合,是原子性,要么全部执行,要么全部不执行
事务的特征:
1、原子性
原子性是指事务必须被认为是一个不可分割的单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功，事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态。这在一些关键系统中尤其重要，现实世界的应用程序（如金融系统）执行数据输入或更新，必须保证不出现数据丢失或者数据错误，以保证数据安全性。
2、一致性
一致性是指事物将数据库从一种状态转变为下一种一致的状态。例如，在表中有一个字段为姓名，具有唯一约束，即姓名不能重复，如果一个事物对姓名进行了修改，使姓名变得不唯一了，这就破坏了事务的一致性要求，如果事务中的某个动作失败了，系统可以自动撤销事务，返回初始化的状态。
在MySQL当中，一致性主要由MySQL的日至机制处理，它记录了数据库的所有变化，为事务恢复提供了跟踪记录。如果系统在事务处理中间发生错误，MySQL恢复过程将使用这些日志来发现是否已经完全成功的执行，否则需要返回，因此一致性属性保证了数据库从不返回一个未处理完的事务。
3、隔离性
隔离性还可以成为并发控制，可串行化，锁等，当多个用户并发访问数据库时，数据库为每一个用户开起的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
4、持久性
事物一旦提交，其所做的修改就会永久保存到数据库中。即使数据库发生故障也不应该对其有任何影响。需要注意的是，事物的持久性不能做到百分之百的持久。只能从事物本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所有提交的数据可能都会丢失。
四. 事务的隔离级别
数据库是多线程并发访问的，所以很容易出现多个线程同时开启事务的情况，这样就会出现脏读，重复读，以及幻读的情况，为了避免这种情况的发生，就需要为事物设置隔离级别。在MySQL中，事物有四种隔离级别，接下来将针对这四种隔离级别进行详细的讲解。
1、READ UNCOMMITTED
READ UNCOMMITTED(未提交读)是事务中最低的级别，该级别下的事务可以读取到另一个事务中未提交的数据，也被称为脏读（Dirty Read），这是相当危险的。由于该级别比较低，在实际开发中避免不了任何情况，所以一般很少使用。
2、READ COMMITTED
大多数的数据库管理系统的默认级别都是READ COMMITTED（提交读），该级别下的事务只能读取其他事务已经提交的内容，可以避免脏读，但不能避免重复读和幻读的情况。重复读就是在事务内重复读取了别的线程已经提交的数据，但两次读取的结果不一致，原因是查询的过程中其他事物做了更新的操作。幻读是指在一个事务内两次查询中数据条数不一致，原因是查询的过程中，其他的事务做了添加操作。这两种情况并不算错误，但有些情况是不符合实际需求的，后面会具体讲解。
3、REPEATABLE READ
REPEATABLE READ（可重复读）是MySQL默认的事务隔离级别，它可以避免脏读，不可重复读的问题，确保同一事物的多个实例在并发读取数据时，会看到同样的数据行，但理论上，该级别会出现幻读的情况，不过MySQL的存储引擎通过多版本并发控制机制，解决了该问题，因此该级别是可以避免幻读的。
4、SERIALIZABLE
SERIALIZABLE（可串行化）是事务的最高隔离级别，他会强制对事物进行排序，使之不会发生冲突，从而解决脏读、幻读、重复读的问题。实际上，就是在每个读的数据行上加锁，这个级别可能导致大量的超时现象和锁竞争，实际应用中很少使用。
五. 存储过程
1. 存储过程是什么?
你可以理解为mysql的编程语言
为什么 有了python 还要弄出来这种编程语言?
他的作用 可以将你的程序业务逻辑 放到mysql中来处理
这样可以降低网络访问次数 从而提高你的程序效率
既然如此 你不能把所有与数据存储相关的业务逻辑 全都放mysql中?
能
但是 对于公司而言 需要再请一个 mysql开发者
对于你个人来说 提高沟通成本
2. 三种开发的模型
对于同样一个业务 你可以放到python也可以放到mysql 有什么区别?
1. 应用程序 处理逻辑
需要手动编写 sql语句
优点:执行效率高
缺点: 开发效率低
mysql
2.应用程序
mysql 处理逻辑
特点: 应用程序开发者不需要需要手动编写 sql语句
mysql开发者来编写
优点: 应用程序开发效率高
缺点: 执行效率略低,沟通成本增高
3.使用 ORM(object relation map) 对象关系映射
自动帮你生成对应的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
//
定义变量 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 过程名称
call add_p(10,20);
其他的流程控制
switch
case
while
repeat == do while
总结:
实际上一个mysql中的类似函数的东西 我们可以用它实现一些逻辑处理
特点:里面可以包含流程控制语句 和 普通的sql语句
使用存储过程的优势
提高应用程序开发效率
降低网络访问次数
