mysql 视图 事务 存储过程 SQL注入

视图

　　视图的本质就是一张虚拟的表

　　虚拟表：在硬盘中没有的，通过查询在内存中拼接的表

　　视图：通过查询得到一张虚拟表，保存下来，下次可以直接使用

为什么要用视图

　　如果要频繁使用一张虚拟表，可以不用重复查询

如何用视图

　　create view  teacher_course as select * from teacher inner join course on teacher.id=course.id;

　　create view test_view as select * from t1;

删除视图

　　drop view teacher_course;

特点：

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

　　2.可以对视图进行修改，修改会同步到原表

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

强调

　　1.在硬盘中，视图只有表结构文件（.frm）没有表数据文件（.idb）其在后台对应的是一条sql语句

　　2.视图通常是用于查询，尽量不要修改视图中的数据

事务

　　事务是一组sql语句集合

事务的特性

　　1.原子性：开启一个事务可以包含一些sql语句，这些sql语句要么同时成功；要么一个都别想成功，这称之为事务的原子性

　　2.隔离性：事务之间要相互隔离为了维护数据完整性：你有一张你有一张银行卡 第一次查看了余额发现有1000 开开心心买东西去了
买完东西 回来付钱再一查发现余额不足 , 原来你在第一次查询后你的媳妇把钱转走了
　　　　上面这个问题称为不可重复读
　　　　如何避免 我在查的时候你不能修改 查询和 修改不能同时进行

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

　　　　1.脏读：一个事务读到了另一个事务未提交的数据 查询之前要保证所有的更新都已经完成

　　　　2.幻读： 一个查询事务没有结束时 数据被另一个事务执行 insert delete

　　　　3.不可重复读： 一个事务在查询 另一个事务在 update

　　　　四种隔离级别

　　　　　　读未提交

　　　　　　读已提交

　　　　　　可重复读 默认

　　　　　　串行化

　　3.一致性

　　　　当事务执行后，所有的数据都是完整的（外键约束 非空约束）

　　4.持久性

　　　　一旦事务提交 数据就永久保存

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

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

pymysql默认是不自动提交的需要用commit来提交修改

start transacton;开启一个事务

commit 提交事务

rollback 回滚事务

使用事务

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

try:
    update user set balance=900 where name='wsb'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='ysb'; #卖家拿到90元
except 异常:
    rollback;   #如果异常，回滚到前一个状态，即balance都为1000
else:
commit;   #如果无异常，修改数据库（硬盘上的数据）

1.开启一个事务
begin  | start transaction 
sql........
2.事务执行完毕后 使用commit来提交 一旦提交就不可恢复了
commit

3.在事务还未提交时我们可以使用rollback来回滚 默认回滚到事务开始前的状态
rollback

创建一张银行账户表 包含 id 账号 姓名 余额
CREATE table account(id int primary key auto_increment,number char(20),name char(10),money double);


为事务创建保存点 保存点就像游戏中的记录 你可回滚到指定的一个点
savepoint  point_name;

回滚至一个保存点
rollback to point_name;

如果有多个保存点 我们可以 多次往回回滚 但是 不能往下前进;因为 一旦回滚了 那么响应的语句也就不存在了;

默认情况下 每一条sql都是一个单独的事务 默认自动提交
也就是一旦执行就不可恢复 
如果想要手动提交 将自动提交关闭
查看是否开启
show variables like 'autocommit';
设置开启状态
set autocommit = 1 | 0;  打开 | 关闭

 mysql中的变量分为三种

　　1.全局变量  @@代表系统变量 系统以及定好的变量

　　2.会话级变量  @代表用户变量 自己定义的变量

　　3.局部变量

查看当前隔离级别

　　select @@global.tx_isolation,@@tx_isolation;

　　设置隔离级别分两种

　　全局与会话级

　　set global tx_isolation = "read-committed"；

　　set tx_isolation="read-committed"；

存储过程

　　在mysql 中函数时不能单独使用的，必须放在sql语句中使用；但存储过程是可以单独使用的

　　存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调试它的名字可以执行其内部的一堆sql

　　使用场景：通常情况下 我们需要把业务逻辑放在客户端处理，例如：注册新用户

　　1.发起一个请求，将要注册的账户名发给服务器进行查询

　　2.服务器将查询结果返回给客户端

　　3.客户端根据结果判断是否可以注册

　　4.如果可以，则再发起请求 想数据库中插入数据

　　5.服务器返回插入结果

　　需要与数据库服务器进行多次通讯，如果网络状态很差，将会需要很长时间，这是就可以使用存储过程，来将原本需要在客户端处理的逻辑放在数据库服务器中，服务器只需要返回一个注册成功或失败即可，大大减少了通讯次数

　　缺点：不同数据库的存储过程大不相同，不可移植，重用性太低

三种开发模型

　　1.（如果不考虑非技术因素，各方面效率最高的选择；但考虑到实际情况，一般不被选择）

　　　　应用程序：需要开发应用程序的逻辑

　　　　mysql处理逻辑，mysql编写好存储过程，以供应用程序调用

　　　　优点：开发效率，执行效率都高

　　　　缺点：考虑到人为因素，部门沟通等问题，会导致扩展性差

　　2.应用处理逻辑

　　　   应用程序：除了开发应用程序的逻辑，还需要编写原生sql

　　　　优点：比方式1 扩展性高（非技术性的）

　　　　缺点：

　　　　　　1.开发效率、执行效率都不如方式1

　　　　　　2.编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题

　　3.使用 ORM(object relation map) 对象关系映射（考虑到非技术因素，最常见的选择）

　　　　应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM（object relationship matching）

　　　　优点：不用再编写纯生sql，这意味着开发效率比方式2 高，同时兼容方式2扩展性高的好处

　　　　缺点：执行效率连方式2都比不过

　　

一: 创建存储过程
create procedure pro_name(p_Type p_name data_type)
begin
sql语句......
end

p_type 表示参数输入输出的类型 有 in out inout;
p_name 参数名
data_type 数据类型 int char ....

注意在过程中 一行结束使用分号
需求 编写存储过程 实现 将两数相加并输出结果
create procedure add_pro(in a int,in b int,out c int)
begin
  //注释使用双斜杠
  //要为变量赋值 需要使用set 关键字
  set c = a + b;
end + 结束符

由于 mysql默认结束符就是分号  而存储过程也是用分号来表示结束 那么这时 mysql就分不清 到底哪个分号才一行结束
方案:更换 结束符
delimiter + 结束符;
delimiter ;;

* 在存储过程中可以插入任意的sql语句


二: 调用存储过程
使用关键字 call
例如: call add_pro(参数);
如果有返回值 需要用变量接收
定义变量的方式
set @变量名 = 值; 定义的变量在断开连接后被释放

三: if语句的使用
if 条件  then
语句;
end if;
第二种 if elseif
if 条件  then
语句1;
elseif 条件 then
语句2;
else 语句3;
end if;

编写过程 实现 输入一个整数type  范围 1 - 2 输出  type=1 or type=2 or type=other;

create procedure showType(in type int,out result char(20))
begin
if type = 1 then 
set result = "type = 1";
elseif type = 2 then 
set result = "type = 2";
else 
set result = "type = other";
end if;
end


CASE 语句  大体意思与Swtich一样的  你给我一个值 我对它进行选择 然后执行匹配上的语句
语法:
CASE 变量名
when 值1 then 语句1;
when 值2 then 语句2;
when 值3 then 语句3;
else 语句4;
end case;

create procedure caseTest(in type int)
begin
CASE type 
when 1  then select "type = 1";
when 2  then select "type = 2";
else select "type = other";
end case;
end


定义局部变量  
declare  变量名 类型 default 值;
例如: declare i int default 0;


WHILE语句  用来执行循环
语法
WHILE 条件 DO
语句.....
end WHILE;

循环输出10次hello mysql
create procedure showHello()
begin 
declare i int default 0;
while  i < 10 do
select "hello mysql";
set i  = i + 1;
end while;
end

LOOP语句  也是用来循环的 没有条件 需要自己定义结束语句
语法:
循环名: LOOP
语句.....
end LOOP 循环名; 
leave 关键字用于跳出某个循环 需要指定循环的名字 loop 专用
iterate 跳过本次循环 loop专用
例如: leave aloop;


输出十次hello mysql;
create procedure showloop()
begin 
declare i int default 0;
aloop: LOOP
select "hello loop";
set i = i + 1;
if i > 9 then leave aloop;
end if;
end LOOP aloop;
end


REPEAT 语句  也用于循环  执行流程和do while
语法:
repeat 
语句......;
until 结束条件
end repeat;

输出10次hello repeat
create procedure showRepeat()
begin
declare i int default 0;
repeat
select "hello repeat";
set i = i + 1;
until i > 9
end repeat;
end


输出0-100之间的奇数
create procedure showjishu2()
begin
declare i int default 0;
aloop: loop
#如果i的值大于等于101时结束循环
if i >= 101 then leave aloop; end if;
#如果取余2 = 0 就跳过本次
if i % 2 = 0 then iterate aloop; end if;
/*输出i*/
select i;
#将i的值+1;
set i = i + 1;
/*循环结束*/
end loop aloop;
end

create procedure showjishu1()
begin
declare i int default 0;
aloop: loop
set i = i + 1;
if i >= 101 then leave aloop; end if;
if i % 2 = 0 then iterate aloop; end if;
select i;
end loop aloop;
end

存储过程使用
a、直接在mysql中调用
set @res=10   #mysql中变量的定义要用@abc的形式
call p1(2,4,@res);

select @res;  #查看结果

b、在python程序中调用
import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db42'
)

cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p1',(2,4,10)) #@_p1_0=2,@_p1_1=4,@_p1_2=10  #pymysql帮助对传入变量进行以上变形
print(cursor.fetchall())
cursor.execute('select @_p1_2;')  #查看返回值，确认执行结果
print(cursor.fetchone())

cursor.close()
conn.close()

事务的使用 （事务+存储过程）
delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception  #如果出现错误，执行
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;

    DECLARE exit handler for sqlwarning    #如果出现警告，执行
    BEGIN
        -- WARNING
        set p_return_code = 2;
        rollback;
    END;

    START TRANSACTION;                     #事务的应用
        update user set balance=900 where id =1;
        update user123 set balance=1010 where id = 2;
        update user set balance=1090 where id =3;
    COMMIT;

    -- SUCCESS
    set p_return_code = 0; #0代表执行成功

END //
delimiter ;

#在python中调用存储过程
import pymysql

conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    charset='utf8',
    database='db44'
)

cursor=conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p6',(100,)) #@_p5_0 = 100
cursor.execute('select @_p6_0')
print(cursor.fetchone())

cursor.close()
conn.close()