mysql之视图、触发器、事务、存储过程、内置函数、流程控制、索引

一、视图

1、什么是视图？

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可。

2、为什么用视图？

有时候需要频繁的把几张表连起来查询，这时就可以创建视图，方便查询。

3、如何用视图？

create view teacher2course as
select * from teacher
inner join course
on teacher.tid=course.teacher_id;

注意：

      在硬盘中，视图只有表结构文件，没有表数据文件

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

4、删除视图：

drop view teacher2course;

开发中会不会使用视图？

        不会。视图是mysql的功能，如果你的项目里大量的用到了视图，那意味着你后期想要扩张某个功能的时候，这个功能又恰好需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到了跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能。

二、触发器

1、什么是触发器？

在对某张表的数据进行增删改时，自动触发的功能称之为触发器。

2、为何用触发器？

触发器针对我们对某张表的数据增insert、删delete、改update的行为，这类行为一旦执行 ​ 就会触发触发器的执行，即自动运行另外一段sql代码。

3、创建触发器的语法

-- 针对插入之后
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
    sql代码
end

-- 针对插入之前
create trigger tri_brfore_insert_t1 before insert on 表名 for each row
begin
    sql代码
end

-- 针对删除之后
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
    sql代码
end

-- 针对删除之前
create trigger tri_before_delete_t1 before delete on 表名 for each row
begin
    sql代码
end

-- 针对修改之后
create trigger tri_after_update_t1 after update on 表名 for each row
begin
    sql代码
end

-- 针对修改之前
create trigger tri_before_update_t1 before update on 表名 for each row
begin
    sql代码
end

4、案例

create table cmd(
    id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char(64),
    sub_time datetime,  -- 提交时间
    success enum('yes','no')
);

create errlog(
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);


-- 触发器内写的sql语句也是;结尾，为了让语句正常执行，需要把当前的结束符改为其他符号

-- 将mysql默认的结束符由;换成$$
delimiter $$
-- 触发器：如果执行失败就往错误表插入数据
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    -- 新插入的记录都会被MySQL封装成NEW对象
    if NEW.success='no' then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
-- 结束之后记得再改回来，不然后面结束符就都是$$了
delimiter;


-- 往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
insert into cmd(user,priv,cmd,sub_time,success) values
('shj','0755','ls -l /etc',NOW(),'yes'),
('shj','0755','cat /etc/passwd',NOW(),'no'),
('shj','0755','useradd xxx',NOW(),'no'),
('shj','0755','ps aux',NOW(),'yes');


-- 查询errlog表记录,发现执行失败（success为no）的都自动插入了这个表
select * from errlog;

-- 查询cmd表
select * from cmd;

-- 删除触发器
drop trigger tri_after_insert_cmd;

三、事务

1、什么是事务？

事务包含一系列sql语句，可以完成提交与回滚的操作。

2、事务的作用？

保证了对数据操作的数据安全性

3、事务的四个属性（ACID）

A-atomicty：原子性

        一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

C-consistency：一致性

        事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

I-isolation：隔离性

        一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

D-durability：持久性

        持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

4、如何用事务？

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);

-- 修改数据之前先开启事务操作
start transaction;

-- 修改操作
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元

-- 回滚到上一个状态
rollback;

-- 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;

在事务中，还可以增加回滚点的操作，回到我们需要回去的位置。

-- 增加回滚点
savepoint 回滚点名字

-- 回到回滚点
rollback to 回滚点名字   -- 一旦回去，那这个点后的操作都没了

开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作。

站在python代码的角度，应该实现的伪代码逻辑如下

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;
else:
    commit;

那么在mysql中要怎么去检测异常呢？答案是存储过程！

四、存储过程

1、什么是存储过程？

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

2、三种开发模型

'''
第一种：

    应用程序：只需要开发应用程序的逻辑
    mysql：编写好存储过程，以供应用程序调用
    优点：开发效率，执行效率都高
    缺点：考虑到认为因素，跨部门沟通等问题，会导致扩展性差



第二种：
    
    应用程序：除了开发应用程序的逻辑，还需要编写原生sql
    优点：比方式一的扩展性高
    缺点：
        1、开发效率，执行效率都不如方式一
        2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题



第三种
    
    应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
    优点：不用再编写原生sql，这意味着开发效率比方式二高，同时兼容方式二扩展性高的好处
    缺点：执行效率连方式二都比不过
'''

3、创建存储过程

-- 先改结束符
delimiter $$
-- 创建存储过程
-- in表示这个参数必须只能是传入，不能被返回
-- out表示这个参数可以返回，还有一个inout表示既可以传入也可以被返回出去
create procedure p1(
    in m int,  
    in n int,
    out res int  
)
begin
    select tname from teacher where tid>m and tid<n;
    set res=0;
end $$
-- 结束符改回来
delimiter ;



-- 小知识点补充，当一张表的字段特别多记录也很多的情况下，终端下显示出来会出现显示错乱的问题
select * from mysql.user\G;

4、如何用存储过程

大前提：存储过程在哪个库下面创建的，只能在对应的库下面使用

1、直接在mysql中调用

set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10

# 调用存储过程
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化。我们在前面的存储过程里将它设为0了，只要查看的结果为0，表示存储过程执行成功

2、在python程序中调用

import pymysql

# 链接
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'day41',
    charset = 'utf8',
    autocommit = True
)
# 游标
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))  # 内部原理：@_p1_0 = 1   @_p1_1 = 5  @_p1_2 = 10
# 执行查看res的值的sql
cursor.execute('select @_p1_2')
# 打印结果
print(cursor.fetchall())

3、存储过程与事务应用举例

delimiter //
create procedure p2(
    out p_return_code tinyint
)
begin
    -- 为sql异常声明句柄
    declare exit handler for sqlexception
    begin 
        -- ERROR
        set p_return_code=1;
        rollback;
    end;
    
    -- 为sql警告声明句柄
    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,0代表执行成功
    set p_return_code=0;
end //
delimiter ;

五、函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

参考博客：http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);

INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

六、流程控制

1、if条件语句

delimiter //

create procedure proc_if()
begin
    
    declare i int default 0;
    if i=1 then
        select 1;
    elseif i=2 then
        select 2;
    else
        select 7;
    end if
end //

delimiter ;

2、while循环

delimiter //

create procedure proc_while()
begin

    declare num int;
    set num=0;
    while num<10 do
        select
            num;
        set num=num+1;
    end while;
end //

delimiter ;

七、索引与慢查询优化

数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

primary key

unique key

index key

注意foreign key不是用来加速查询用的，上面的三种key，前两种除了有加速查询的效果之外还有额外的约束条件（primary key：非空且唯一，unique key：唯一），而index key没有任何约束功能只会帮我们加速查询。

1、什么是索引？

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

其本质是：通过不断地缩小想要获取的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

2、索引的影响：

        在表中有大量数据的前提下，创建索引速度会很慢

        在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

3、b+树

https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png

只有叶子节点存放真实数据，根和树枝节点存的仅仅是虚拟数据，只是为了缩小查询范围

查询次数由树的层级决定，层级越低，查询的次数就越少

一个磁盘的大小是一定的，它能存储的数据量也是一定的。为了保证树的层级最低，那么一个磁盘应该存放占用空间比较小的数据项（一般存主键id）。

3.1、聚集索引（primary key）

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。

myisam引擎在建表的时候在硬盘有三个文件，一个存表结构，一个存索引，一个存数据。

innodb引擎在建表的时候在硬盘有两个文件，一个存表结构，一个存的是索引和数据。

特点：

        叶子节点存放的是一条条完整的记录

3.2、辅助索引（unique，index）

查询数据的时候不可能都是以id作为筛选条件，也可能会用name，password等字段信息。那么这个时候就无法利用聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫做辅助索引。

特点：

        叶子节点存放的是辅助索引字段对应的那条记录的主键的值（比如，按照name字段创建索引，那么叶子节点存放的是：{name对应的值:name所在的那条记录的主键值}）

select  name  from  user  where  name='shj';

上述的语句叫覆盖索引：只在辅助索引的叶子节点就已经找到了我们想要的数据

select  age  from  user  where  name='shj';

上述语句叫非覆盖索引：虽然查询的时候命中了索引字段name，但是要查的是age，所以还需要利用主键才去查找

测试索引：

-- 1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

-- 2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; -- 重新声明分号为结束符号

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

-- 4. 调用存储过程
call auto_insert1();

-- 表没有任何索引的情况下
select * from s1 where id=30000;
-- 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

-- 给id做一个主键
alter table s1 add primary key(id);  -- 速度很慢

select count(id) from s1 where id = 1;  -- 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  -- 速度仍然很慢


"""
范围问题
"""
-- 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  -- 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  -- 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  -- 又慢了

create index idx_name on s1(name);  -- 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  -- 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
-- 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  -- 慢 最左匹配特性

-- 区分度低的字段不能建索引
drop index idx_name on s1;

-- 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  -- 快了
select count(id) from s1 where id*12 = 3;  -- 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
-- 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  -- 并没有加速

drop index idx_name on s1;
-- 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  -- 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  -- 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; -- 快 通过email字段一剑封喉 

3.3、联合索引 

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

4、慢查询优化

设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化，缩短查询时间！