目录

视图

1、什么是视图

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

2、为什么要使用视图

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

3、如何使用视图

create view teacher2course as select * from teacher inner join courseon teacher.tid=course.teacher_id;# 强调：1、在硬盘中，视图只有表结构文件，没有表数据文件2、视图通常是用于查询，尽量不要修改视图中的数据# 删除视图：drop view teacher2course;

思考：开发过程中会不会去使用视图？

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

触发器

1、什么是触发器

在满足 对某张表中数据的增，删，改的情况下，自动触发的功能 称之为触发器

2、为什么要用触发器

触发器专门针对我们对某一张表的insert，delete，update的行为，这类行为一旦执行，就会触发运行我们的触发器的代码

3、怎么用触发器

# 创建触发器的语法# 针对 ‘增’create trigger tri_after_insert_t1 after insert on 表名 for each rowbegin     sql代码end;# 针对 ‘删’create trigger tri_after_insert_t1 after insert on 表名 for each rowbegin     sql代码end;# 针对 ‘改’create trigger tri_after_insert_t1 after insert on 表名 for each rowbegin     sql代码end;

代码示例：

create table cmd(    id int primary key auto_increment,    user char(32),    priv char(10),    cmd char(64),    sub_time datetime,    sucess eunm ('yes','no'))create table errlog(    id int primary key auto_increment,    err_cmd char(64),    err_time datetime)delimit $$  # 将mysql默认结束符;换为$$create trigger tri_after_insert_cmd after insert on cmd for each rowbegin     if NEW.success = 'no' then        insert into error (err_cmd,err_time) values (NEW.cmd,NEW.sub.time);    end if;end $$delimiter ;  # 结束之后记得再改回来，不然后面的结束符都是$$了insert into cmd(    user,prive,cmd,sub_time,sucess) values     ('egon','0755','ls -l /etc',NOW(),'yes'),    ('egon','0755','cat /etc/passwd',NOW(),'no'),    ('egon','0755','useradd xxx',NOW(),'no'),    ('egon','0755','ps aux',NOW(),'yes');    # 查询errlog表记录select * from errlog;# 删除触发器drop trigger tri_after_insert_cmd

事务

1、什么是事务

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

2、为什么要用事务

保证了操作数据的数据安全性案例：用交行的卡操作建行ATM机给工商的账户转钱

事务的4个属性

原子性，一致性，隔离性，持久性，这四个属性通常称为ACID特性原子性(atomicity)：    一个事务是不可分割的单位，其中的诸多操作要么不完成，要么一起完成一致性(consistency)：    事务必须是使数据从从一个一致性状态转换到另一种一致性状态，一致性与原子性是密切相关的隔离性(isolation)：    事务中的 操作和数据 是不受其他并发事务的影响的。持久性(durability)：    持久性也称永久性(permanence)，只一个事务完成的操作一旦提交，那么对数据库的影响就是永久的，不可撤回，这个特性不受其他操作影响。

3、怎么用事务

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元# 回滚到上一个状态(start transaction之前的状态)rollback；# 开始事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘中commit;# 用python伪代码体现 事务的原子性start transaction;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;

存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于mysql中，通过调用他的名字即可执行其内部的sql代码

三种开发模型

第一种

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

第二种

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

第三种

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

创建存储过程

delimiter $$create procedure p1(    in m int,  # in标识这个参数只能被传入，不能被返回出去    in n int,    out res int  # out标识这个参数可以被返回出去，    # inout res int  #  inout标识这个参数既可以被传入也可以被返回出去    )    begin     select tname from teacher where id>m and tid

如何使用存储过程

# 大前提：存储过程在哪个库下面创建的 就只能在对应的库下面才能使用！# 1、直接在mysql中调用set @res=10call p1(2,4,10);  # 报错call p1(2,4,@res);# 查看结果select @res;  # 执行成功，@res变量值发送了变化# 2、在python程序中调用# pymysql建立连接 获得 conn,cursorcursor.callproc('p1',2,4,10))# 内部原理： @_p1_o=2, @_p1_1_4, @_p1_2_10;# 3.存储过程与事务过程使用举例(了解)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 user set balance=1010 where id = 2;        update user set balance=1090 where id =3;    commit;        -- SUCCESS    set p_return_code=0;  # 0代表执行成功end $$delimiter ;

函数

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

函数的使用

# mysql中的 函数都是内置函数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');# DATE_FORMAT(date,format)函数会  根据format字符串格式化date值select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_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 3;    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;end $$delimiter ;

索引与慢查询优化

索引在MySQL中也叫做‘键’，是存储引擎用于快速找到记录的一种数据结构primary key unique keyindex key注意 foreign key不是用来加速查询用的，不在我们研究范围之内，上面三种key前两除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一)，(unique key：唯一)，而 index key没有任何约束功能，只会帮你加速查询索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该吓着目录再找数据，而不是用翻页的方式查询数据# 本质都是：    通过不断的缩小想要获取的数据的范围来筛选出最终想要的结果，同时把随机的时间变成顺序的时间，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的影响

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

b+树

只有叶子节点存放真实数据，根 和 树枝 节点存的仅仅是虚拟数据查询次数由树的层级决定，层级越低，次数越少一个磁盘块的大小是一定的，那就意味着能存的数据量是一定的如何保证树的层级最低呢？一个磁盘块存放占用空间比较小的数据项思考我们应该给我们一张表里的什么字段建立索引能够降低树的层级高度>>>主键id字段

聚焦索引(primary key)

聚焦索引其实就是表的主键，innodb引擎规定一张表中必须要有主键，先来回顾一下存储引擎myisam在建表的时候对应到硬盘有几个文件？==>3个innodb在建表的时候对应到硬盘有几个文件？==>2个  frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb的表数据文件中特点：叶子节点放的是一条条完整的记录

辅助索引(unique，index)

辅助索引：查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，name这个时候就无法利用到 聚集索引 的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引特点：叶子节点存放的是辅助索引字段对应的那条记录的主键的值(比如：按照name字段创建索引，那么叶子节点存放的是：{name对应的值：name所在的那条记录的主键值})select name from user where name='jason';上述语句叫覆盖索引：只在辅助索引的叶子节点中就已经找到了所有我们想要的数据select age from user where name='jason';上述语句叫非覆盖索引：虽然查询的时候命中了索引字段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 s1create index idx_email on s1(email);select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

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; aaa# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间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';  # 速度变快

总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了

慢查询日志

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