mysql之视图、触发器、事务、存储过程

一.视图

视图：虚拟表，在内存中。本质就是一个sql语句。

视图只有表结构，没有表数据
在硬盘上你找到自己的mysql安装目录里面的data文件夹里面的对应的那个库的文件夹，这个文件夹里面存着咱们的表信息，打开之后你会发现，这个视图表，只有表结构的teacher_view.frm文件，没有那个.idb存放数据的文件
其实他并没有真实的数据，也没有必要再存一份数据，因为它的数据来源于其他两个表，所以他本质在后台对应的就是一个sql语句而已。

使用视图的原因：可将查到的数据作为临时表保存起来，之后再想操作就无需再写复杂的sql语句
视图操作：
    1.创建:create view 视图别名 as select * from 表
    2.查：和表操作相同
优缺点：
    优点：节省代码代码量
    缺点：查询效率低下，而且一旦有需求要修改数据库表，视图跟着也要修改

二.触发器

定义：用户对某张表进行增删改(没有查)操作前后的行为。

创建(delimiter...都是mysql语句)：

1.在向表插入数据之前创建触发器
'''NEW代表插入的行'''
delimiter //
    create trigger 触发器名 before insert on 表名1 for each row
    begin 
    if NEW.字段名 = 'no' THEN       
        insert into 表名2 values('a','b','x');
    end if ;
end //

使用：触发器无法由用户直接调用，而是对表的增删改操作被动引起的（就是说我们只需要创建触发器即可，表改动了也就触发了触发器）

删除：drop trigger 触发器名;

三.事务

定义：事务就是逻辑上的一组sql语句操作，组中的sql语句要么全部成功要么全部失败。

四大特性：

1.原子性：事务是一个不可分割的单位，事务中的sql要么都成功，要么都不成功
2.一致性：事务发生前后的完整性必须保持一致。
3.隔离性：一个事务在执行过程中是封闭的，只有执行完了才能拿到新的数据。
4.持久性：一个事务一旦被提交，它对于数据库的数据改变就是永久的，出了错误也不撤销，只能通过"补偿性事务"

原子操作：

start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='chao'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;  #只要不进行commit操作，就没有保存下来，没有刷到硬盘上,使用rollback可回归原来状态

四.存储过程

定义：存储过程=函数，存储过程中的代码相当于函数中的代码。

delimiter //
    create procedure p1()
    BEGIN
        select * from blog;
        INSERT into blog(name,sub_time) values("xxx",now());
    END //
delimiter ;

#在mysql中使用：
call p1();


#在python中基于pymysql调用
cursor.callproc('p1')    #执行存储过程
print(cursor.fetchall()) #得到存储过程中select得到的结果

delimiter //
    create procedure p2(
        in n1 int,  #n1参数是需要传入的，也就是接收外部数据的，并且这个数据必须是int类型
        in n2 int,
        out res int  #out代表返回值
    )
    BEGIN
        select * from employee where id between n1 and n2;  #直接应用变量
        set res=1;        #相当于设置全局变量，select @res可查询
    END //
delimiter ;

#mysql中调用
set @res=0
call p2(2,3,@res) 
    

#python中基于pymysql调用
import pymysql
conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="root",
    database="homework",
    charset="utf8",
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p2',(3,6,2))  #执行存储过程
cursor.execute('select @_p2_0;')  #查看传入的参数1
print(cursor.fetchall()) #得到3

1. 查看存储过程
show create procedure auto_insert1\G 

2. 删除存储过程
drop procedure p1;

五.索引

1.认识

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。
其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果
表中除了聚集索引外其他索引都是辅助索引，(unique key、index key都是辅助索引)
添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加.

2.影响

1、在表中有大量数据的前提下，创建索引速度会很慢
2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

3.聚集索引

MyISAM:三个.frm\.MYD\.MYI结尾的三个文件(frm结尾的是表结构，MYD结尾的是数据文件，MYI结尾的就是索引文件)
InnoDB:.frm\.idb结尾的两个文件(frm结尾的是表结构,idb存的是索引和数据)
好处：
    1.它对主键的排序查找和范围查找速度非常快
    2.范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

4.辅助索引(覆盖索引)

就是我们在查询的时候，where后面需要写id之外的其他字段名称来进行查询，比如说是where name=xx，没法用到主键索引的效率，怎么办，就需要我们添加辅助索引了，给name添加一个辅助索引。    
回表操作:
    select age from tb1 where name="xx";辅助索引的叶子节点不能直接拿到age的值，需要通过辅助索引的叶子节点中保存的主键id的值再去通过聚集索引来找到完整的一条记录，然后从这个记录里面拿出age的值

相同点：本质都是使用了B+数
两者区别:
    聚集索引中包含行记录全部数据。
    辅助索引的叶子节点不包含行记录的全部数据，包含主键id键值对、where后面字段的键值对。

5.索引操作

https://gitee.com/laonanhaipythonquanzhan24qi/jiaoxuejihua/blob/master/day44/day44