49.MySQL数据库尾声

今日内容概要

今日内容基本都是了解知识点，你在工作中基本用不到

• 视图
• 触发器
• 事务（需要掌握）
• 存储过程
• 内置函数
• 流程控制
• 索引理论

今日内容详细

视图

• 什么是视图

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

　　　　其实视图也就是表

• 为何要有视图

　　　　如果频繁的操作一张虚拟表，你就可以制作成视图，后续直接操作

• 如何操作视图

#固定语法create view 视图名 as 虚拟表的查询sql语句# 具体操作create view teacher2course as select * from teacher innter join course on teacher.id = course.teacher_id;

• 注意

"""1.创建视图在硬盘上只有表结构，没有表数据（数据还是来自于之前的表）2.视图一般只用来查询，里面的数据不要进行修改，可能会影响真正的表"""mysql> delete from teacher2course where teacher.teacher.id = 1;ERROR 1395 (HY000): Can not delete from join view 'day48.teacher2course'

• 视图的使用频率到底高不高呢？

"""不高当你创建了视图之后，会造成表不好维护"""#总结：视图了解即可，基本不用！！！

触发器

　　在满足对表数据进行增、删、改的情况下，自动触发的功能

　　使用触发器可以帮我们实现数据库的监控、日志，自动处理异常等等

　　触发器可以在6中情况下自动触发，增前/后、删前后、改前后

　　基本语法结构

create trigger 触发器的名字 before/after insert/update/delete on表名for each rowbegin    sql 语句end#具体使用，针对触发器的名字，我们通常要做到见名知意#针对增create trigger tri_before_insert_t1 before insert ont1for each rowbegin    sql语句endcreate trigger tri_after_insert_t1 before insert on t1for each rowbegin    sql 语句end"""针对删除和修改，书写格式一致"""ps:修改MySQL默认的语句结束符，只作用于当前窗口    delimiter $$       将默认的结束符号由; 变成 $$#案例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 table errlog(    id int primary key auto_increment,    err_cmd char(64),    err_time datetime);"""当cmd表中的记录success字段时no的时候，那么就触发触发器的执行errlog表中插入数据NEW指代的就是一条条数据对象"""delimiter $$create trigger tri_after_insert_cmd after insert on cmdfor each rowbegin    if NEW.success = 'no' then        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);    end if;end $$delimiter ;#在cmd中插入数据insert into cmd(    user,    priv,    cmd,    sub_time,    success) values                ('jason','0755','ls -l /etc',NOW(),'yes'),            ('jason','0755','cat /etc/password',NOW(),'no'),        ('jason','0755','useradd xxx',NOW(),'no'),        ('jason','0755','ps aux',NOW(),'yes');    mysql> select * from cmd;+----+-------+------+-------------------+---------------------+---------+| id | USER  | priv | cmd               | sub_time            | success |+----+-------+------+-------------------+---------------------+---------+|  1 | jason | 0755 | ls -l /etc        | 2021-09-04 11:44:36 | yes     ||  2 | jason | 0755 | cat /etc/password | 2021-09-04 11:44:36 | no      ||  3 | jason | 0755 | useradd xxx       | 2021-09-04 11:44:36 | no      ||  4 | jason | 0755 | ps aux            | 2021-09-04 11:44:36 | yes     |+----+-------+------+-------------------+---------------------+---------+4 rows in set (0.00 sec)mysql> select * from errlog;+----+-------------------+---------------------+| id | err_cmd           | err_time            |+----+-------------------+---------------------+|  1 | cat /etc/password | 2021-09-04 11:44:36 ||  2 | useradd xxx       | 2021-09-04 11:44:36 |+----+-------------------+---------------------+2 rows in set (0.00 sec)#删除触发器drop trigger tri_after_insert_cmd;

事务

• 什么是事务

"""开启一个事务可以包含多条SQL语句，这些SQL语句要么同时成功要么同时失败，一个也别想成功。称之为事务的原子性"""

• 事务的作用

"""保证了对数据操作的安全性"""eg:还钱的例子    egon用银行卡给我的支付宝转账        1.将egon银行卡账户的数据减1000块        2.将jason支付宝账户的数据加1000块    你在操作多条数据的时候，可能会出现某几条不成功的情况

• 事务的四大特性

"""ACID A (atomicity):原子性      一个事务就是一个不可分割的单位，事务包含诸多操作     要么同时成功，要么同时失败C(consistency): 一致性      事务必须是使数据库从一个一致性的状态，变到另一个一致性的状态      一致性跟原子性是密切相关的I(isolation)：隔离性       一个事务的执行，不能被其他事务干扰。即一个事务内部的操作，及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的D(durability):持久性       也叫永久性，指一个事务一旦提交成功，执行成功，那么它对数据库中的数据修改应该是永久性的，接下来的其他操作或者故障不应该对其有任何的影响"""

• 如何使用事务

#事务相关的关键字#1.开启事务start transaction#2.回滚操作（回到事务执行之前的状态）rollback#3.确认（确认之后，就无法回滚了）commit"""模拟转账功能"""create table user(    id int primary key auto_increment,    name varchar(16),    banlance int);insert into user(name,banlance) values('jason',1000),('egon',1000),('tank',1000);#1.先开启事务start transaction;#2.书写多条sql语句update user set banlance = 900 where name = 'jason';update user set banlance = 1010 where name = 'egon';update user set banlance = 1090 where name = 'tank';"""mysql> # 1.先开启事务mysql> start transaction;Query OK, 0 rows affected (0.00 sec)mysql> # 2.书写多条sql语句mysql> update user set banlance = 900 where name = 'jason';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update user set banlance = 1010 where name = 'egon';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update user set banlance = 1090 where name = 'tank';Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql>mysql>mysql> select * from user;+----+-------+----------+| id | name  | banlance |+----+-------+----------+|  1 | jason |      900 ||  2 | egon  |     1010 ||  3 | tank  |     1090 |+----+-------+----------+3 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.01 sec)mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from user;+----+-------+----------+| id | name  | banlance |+----+-------+----------+|  1 | jason |      900 ||  2 | egon  |     1010 ||  3 | tank  |     1090 |+----+-------+----------+3 rows in set (0.00 sec)"""总结：    当你想让多条sql语句保持一致性，要么同时成功，要么同时失败    你就应该考虑使用事务

存储过程

　　存储过程就类似于Python中的自定义函数

　　 它的内部包含了一系列可以执行的sql语句，存储过程存放于mysql服务端中，

　　　　你可以直接通过调用存储过程触发内部sql语句的执行

　　基本使用

create procedure 存储过程的名字 (形参1,形参2,...)begin    sql 语句 end    

　　三种开发模型

　　第一种

"""应用程序：程序猿写代码开发MySQL：提前编写好存储过程，供程序猿调用好处：开发效率提升了 执行效率也上去了坏处：考虑到人为因素，跨部门沟通问题，后续的存储过程的扩展性变差"""

　　　第二种

"""应用程序：程序猿写代码开发,涉及到数据库操作也自己动手写好处：扩展性很高坏处：    开发效率降低    编写sql语句太过繁琐，而且后续还需要考虑sql优化的问题"""

　　 第三种

"""应用程序：开发只写程序代码，不写sql语句，基于别人写好的操作MySQL的python框架直接调用操作即可        ORM框架    通过python代码操作数据库好处：开发效率比前两种情况都要高坏处：语句的扩展性差，可能会出现效率低下的问题"""

　　　　第一种基本不用，一般都是用第三种，出现效率问题再动手写sql

　　　　存储过程具体演示

delimiter $$create procedure p1(    in m int       #只进不出，m不能返回    in n int    out res int    #out表示该形参可以返回出去)begin    select tname from teacher where tid > m and tid < n;    set res = 666   #将res变量修改，用来标识当前的存储过程代码确实执行了end  $$delimiter ;#调用存储过程call p1(1,5,10)mysql> call p1(1,5,10)    -> ;ERROR 1414 (42000): OUT or INOUT argument 3 for routine day48.p1 is not a variable or NEW pseudo-variable in BEFORE trigger#针对形参res,不能直接传数据，应该要传一个变量名#定义变量set @res = 10;#查看变量对应的值select @ret;mysql> select @res;+------+| @res |+------+|   10 |+------+1 row in set (0.00 sec)mysql> call p1(1,5,@ret)

在pymysql模块中如何调用存储过程呢？

import pymysqlconn = pymysql.connect(    host='127.0.0.1',    port=3306,    user='root',    password='123',    database='day48',    charset='utf8',    autocommit=True)cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)#调用存储过程cursor.callpro('p1',(1,5,10))"""@_p1_0 = 1@_p1_1 = 5@_p1_2 = 10"""cursor.execute('select @_p1_2')print(cursor.fetchall())

　　函数

　　　　跟存储过程是有区别的，存储过程是自定义函数，函数就类似于是内置函数

('jason','0755','ls -l /etc',NOW(),'yes')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 data_format(sub_time,'%Y-%m');

　　流程控制

# 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 ;# 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;delimiter ;

　　索引

ps:数据都是存在于硬盘上的，查询数据不可避免的需要进行IO操作索引：就是一种数据结构。类似于书的目录，意味着以后在查询数据的时候，应该先找目录再找数据，而不是一页一页的翻书，
从而提升查询速度，降低IO操作索引在MySQL中也叫”键“，是存储引擎用于快速查找记录的一种数据结构* primary key* unique key* index key注意 foreign key不是用来加速查询用的，不在我们的研究范围之内上面的三种key，前面的两种除了可以增加查询速度之外，各自还具有约束条件，而最后一种index key没有任何的约束条件，
只是用来帮助你快速查询数据

　　　　本质

通过不断地缩写想要的数据范围，筛选出最终的结果，同时将随机事件（一页页的翻）变成顺序事件（先找目录，找数据）也就是说有了索引机制，我们可以总是用一种固定的方式查找数据一张表中，可以有多个索引（多个目录）索引虽然能够帮助你加快查询速度，但是也是有缺点的"""1.当表中有大量的数据存在的前提下，创建索引速度会非常慢2 在索引创建完毕之后，对表的查询性能会大幅度的提升，但是写的性能也会大幅度的降低"""索引不要随意的创建！！！！

　　　　b+树

"""只有叶子节点存放的是真实的数据，其他结点存放的是虚拟数据，仅仅使用来指路的树的层级越高，查询数据所经历的步骤就越多，树有几层，查询数据就有几步一个磁盘块，存储是有限制的为什么建议你将id字段作为索引    id占的空间少，一个磁盘块能够存数的数据多    那么就降低了树的高度，从而减少查询次数"""

　　　　聚集索引（primary key）

"""聚集索引指的就是主键Innodb   只有两个文件，直接将主键存放在了ibd表中MyIsam   三个文件，单独将索引放在一个文件中"""

　　　　辅助索引（unique，index）

　　　　　　查询数据的时候,不可能一直使用到主键，也有可能会使用到其他字段

　　　　　　那么这个时候，你是没有办法利用聚集索引，这个时候，你就可以根据情况给其他字段设置辅助索引

　　　　　　辅助索引也是一个b+树

　　　　　　where name = 'jason'

"""叶子节点存放的是数据对应的主键值    先按照辅助索引拿到数据的主键值    之后还是需要去主键的聚集索引里面查询数据"""

　　　　覆盖索引

　　　　在辅助索引的叶子节点，就已经拿到了想要的数据

# 给name设置辅助索引select name from user where name = 'jason';# 非覆盖索引select age from user wherr name = 'jason';

　　　　测试索引是否有效的代码

　　　　　　感兴趣的可以自己试试，不感兴趣直接忽略

#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;    # 1.4s# 避免打印带来的时间损耗select count(id) from s1 where id = 30000;    # 1.4sselect count(id) from s1 where id = 1;        # 1.4s# 给id做一个主键alter table s1 add primary key(id);  # 速度很慢    7.1sselect count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级 0sselect count(id) from s1 where name = 'jason'  # 速度仍然很慢 0.7s"""范围问题"""# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多0.653sselect count(id) from s1 where id >1 and id < 3;    # 0.001sselect count(id) from s1 where id > 1 and id < 10000;    # 0.007sselect count(id) from s1 where id != 3;            # 0.657salter table s1 drop primary key;  # 删除主键 单独再来研究name字段7.884sselect count(id) from s1 where name = 'jason';  # 又慢了1.167screate index idx_name on s1(name);  # 给s1表的name字段创建索引6.284sselect count(id) from s1 where name = 'jason'  # 仍然很慢！！！4s"""再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分那这个树其实就建成了“一根棍子”"""select count(id) from s1 where name = 'xxx';      # 0s# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了select count(id) from s1 where name like 'xxx';      # 0sselect count(id) from s1 where name like 'xxx%';      # 0sselect count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性    1.352s# 区分度低的字段不能建索引drop index idx_name on s1;# 给id字段建普通的索引create index idx_id on s1(id);            #  4.404sselect count(id) from s1 where id = 3;  # 快了      0sselect count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算0.671sdrop index idx_id on s1;select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';        # 1.41s# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件create index idx_name on s1(name);        # 6.559sselect count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速    4.895sdrop index idx_name on s1;# 给name，gender这种区分度不高的字段加上索引并不能加快查询速度create index idx_id on s1(id);        # 4.625sselect count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  
# 快了  先通过id已经讲数据快速锁定成了一条了0.001s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; 
 # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段1.949sdrop index idx_id on s1;create index idx_email on s1(email);        # 8.029sselect count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 快 通过email字段一剑封喉     0.004s# 联合索引select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';      #     0s# 如果上述四个字段区分度都很高，那给谁建都能加速查询    # 给email加然而不用email字段select count(id) from s1 where name='jason' and gender = 'male' and id > 3;     # 1.65s# 给name加然而不用name字段select count(id) from s1 where gender = 'male' and id > 3;         # 1.416s# 给gender加然而不用gender字段select count(id) from s1 where id > 3;         # 0.976s# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放9.734sselect count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快0.005s总结:搜索范围大，耗时长识别度低的索引：耗时比无该索引更长慢查询日志设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！