【2020Python修炼记】MySQL之视图、触发器、事务、存储过程、函数、索引

【目录】（其余均为了解知识）

一视图

二触发器

三事务（掌握）

四存储过程

五函数

六流程控制

七、索引理论

一、视图

1、什么是视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

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

2、为何要用视图

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可。

但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的 sql 过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

3、如何使用视图

（1）临时表应用举例：

#两张有关系的表
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
5 rows in set (0.00 sec)

#查询李平老师教授的课程名
mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老师');
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)

#子查询出临时表，作为teacher_id等判断依据
select tid from teacher where tname='李平老师'

（2）创建视图—— CREATE VIEW 视图名称 AS SQL语句

#语法：CREATE VIEW 视图名称 AS  SQL语句
create view teacher_view as select tid from teacher where tname='李平老师';

#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)

#！！！注意注意注意：
#1. 使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

#2. 而且有一个致命的问题：
视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，
那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，
而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便

（3）使用视图

#修改视图，原始表也跟着改
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)

mysql> select * from course_view;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)

mysql> update course_view set cname='xxx'; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)

mysql> select * from course; #发现原始表的记录也跟着修改了
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   1 | xxx   |          1 |
|   2 | xxx   |          2 |
|   3 | xxx   |          3 |
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
5 rows in set (0.00 sec)

注意：我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的。

（4）修改视图

 修改视图

语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
2 rows in set (0.00 sec)

（5）删除视图

语法：DROP VIEW 视图名称

DROP VIEW teacher_view

二、触发器

1、什么是触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

使用触发器可以帮助我们实现监控、日志...

触发器可以在六种情况下自动触发——增前增后删前删后改前改后

2、基本语法结构

create trigger 触发器的名字  before/after insert/update/delete on 表名
for each row
begin
    sql语句
end

# 具体使用 针对触发器的名字 我们通常需要做到 见名知意


# 针对增
create trigger tri_before_insert_t1  before insert on t1
for each row
begin
    sql语句
end
create trigger tri_after_insert_t1  after insert on t1
for each row
begin
    sql语句
end
"""针对删除和修改 书写格式一致"""

ps:修改MySQL默认的语句结束符  只作用于当前窗口
    delimiter $$  将默认的结束符号由;改为$$
    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') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
"""
当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd 
for each row
begin
    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/passwd',NOW(),'no'),
    ('jason','0755','useradd xxx',NOW(),'no'),
    ('jason','0755','ps aux',NOW(),'yes');

# 删除触发器
drop trigger tri_after_insert_cmd;

# 案例

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

六种触发情况的基本语法

使用触发器：

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

删除触发器：
drop trigger tri_after_insert_cmd;

三、事务

1、什么是事务

事务用于将某些操作的多个SQL作为原子性操作。

（开启一个事务可以包含多条sql语句，这些sql语句要么同时成功，要么一个都别想成功，称之为事务的原子性），一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

2、事务的作用

【保证对数据操作的安全性】

例子：还钱

付钱方和收款方，存在多条操作；

在操作多条数据的时候，可能会出现某些操作不成功的情况

【事务的四大特性——ACID】

A：原子性 atomicity

一个事务是不可分割的单位，事务中包含的诸多操作，要么同时成功，要么同时失败

C：一致性 consistency

事务必须是使数据库从一个一致性的状态变为另外一个一致性的状态

一致性跟原子性是密切相关的

I：隔离性 isolation

一个事务的执行不能被其他事务干扰

（即一个事务内部的操作以及使用的数据，对并发的其他事务是隔离的，并发执行的事务之间也是互不干扰的）

D：持久性 durability

也叫"永久性"。一个事务一旦提交成功执行成功，那么它对数据库中数据的修改应该是永久的，

接下来的其他操作或者故障不应该对其有任何的影响

3、如何使用事务

# 事务相关的关键字
# 1 开启事务
start transaction;

# 2 回滚(回到事务执行之前的状态)
rollback;

# 3 确认(确认之后就无法回滚了)
commit;

BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。


MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

事务控制语句

"""模拟转账功能"""
create table user(
    id int primary key auto_increment,
    name char(16),
    balance int
);
insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);


# 1 先开启事务
start transaction;
# 2 多条sql语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。

使用 SAVEPOINT

SAVEPOINT savepoint_name;    // 声明一个 savepoint

ROLLBACK TO savepoint_name;  // 回滚到savepoint
删除 SAVEPOINT

保留点再事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。

MySQL5 以来，可以用:

RELEASE SAVEPOINT savepoint_name;  // 删除指定保留点

使用保留点 SAVEPOINT

总结

当你想让多条sql语句保持一致性要么同时成功要么同时失败
你就应该考虑使用事务

四、存储过程

1、什么是存储过程

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

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

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

2、基本使用

create procedure 存储过程的名字(形参1,形参2,...)
begin
    sql代码
end
# 调用
call 存储过程的名字();

3、三种开发模型

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

【第一种】

应用程序:程序员写代码开发

MySQL:提前编写好存储过程，供应用程序调用

好处:开发效率提升了执行效率也上去了

缺点:考虑到认为元素、跨部门沟通的问题后续的存储过程的扩展性差

【第二种】

应用程序:程序员写代码开发之外设计到数据库操作也自己动手写

优点:扩展性很高

缺点:
开发效率降低
编写sql语句太过繁琐而且后续还需要考虑sql优化的问题

【第三种】

应用程序:只写程序代码不写sql语句基于别人写好的操作MySQL的python框架直接调用操作即可——如：ORM框架

优点:开发效率比上面两种情况都要高

缺点:语句的扩展性差可能会出现效率低下的问题

4、存储过程的具体演示

delimiter $$
create procedure p1(
    in m int,  # 只进不出  m不能返回出去
    in n int,
    out res int  # 该形参可以返回出去
)
begin
    select tname from teacher where tid>m and tid<n;
    set res=666;  # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;

# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;

5、在pymysql 模块中，如何调用存储过程

import pymysql


conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    passwd = '123456',
    db = 'day48',
    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
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())

五、函数

https://www.runoob.com/mysql/mysql-functions.html

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

('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 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 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 ;

七、索引理论

1、索引

https://www.runoob.com/mysql/mysql-index.html

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

索引:就是一种数据结构，类似于书的目录。意味着以后在查询数据的时候，应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO操作。

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

primary key

unique key

index key

注意foreign key不是用来加速查询用的，不在我们的而研究范围之内

上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，而最后一种index key没有任何的约束条件，只是用来帮助你快速查询数据

本质

通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件(一页一页的翻)

变成顺序事件(先找目录、找数据)

也就是说有了索引机制，我们可以总是用一种固定的方式查找数据

一张表中可以有多个索引(多个目录)

索引虽然能够帮助你加快查询速度但是也有缺点。

1 当表中有大量数据存在的前提下创建索引速度会很慢
2 在索引创建完毕之后对表的查询性能会大幅度的提升但是写的性能也会大幅度的降低
"""
索引不要随意的创建！！！

2、b+树

只有叶子节点存放的是真实的数据其他节点存放的是虚拟数据仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)

一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占得空间少一个磁盘块能够存储的数据多
那么久降低了树的高度从而减少查询次数

3、聚集索引(primary key)

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

4、辅助索引(unique,index)

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

那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树）

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

5、覆盖索引

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

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

6、测试索引是否有效的代码

**准备**

```mysql
#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();
```

``` mysql 
# 表没有任何索引的情况下
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字段一剑封喉 
```

#### 联合索引

```mysql
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';  # 速度变快
```

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

慢查询日志

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

感兴趣就康康

参考阅读：

https://zhuanlan.zhihu.com/p/120227954

https://www.cnblogs.com/linhaifeng/articles/7495918.html

https://www.cnblogs.com/linhaifeng/articles/7356064.html

posted @ 2020-05-08 16:33 bigorangecc 阅读(413) 评论(0) 编辑收藏举报

刷新页面返回顶部

bigorangecc

【2020Python修炼记】MySQL之 视图、触发器、事务、存储过程、函数、索引

【目录】（其余均为了解知识）

一 视图

二 触发器

三 事务（掌握）

四 存储过程

五 函数

六 流程控制

七、索引理论

一、视图

1、什么是视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

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

2、为何要用视图

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可。

但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的 sql 过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

3、如何使用视图

（1）临时表应用举例：

（2）创建视图—— CREATE VIEW 视图名称 AS SQL语句

（3）使用视图

注意：我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的。

（4）修改视图

（5）删除视图

二、触发器

1、什么是触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

使用触发器可以帮助我们实现监控、日志...

触发器可以在六种情况下自动触发——增前 增后 删前 删后 改前 改后

2、基本语法结构

使用触发器：

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

删除触发器：

三、事务

1、什么是事务

事务用于将某些操作的多个SQL作为原子性操作。

（开启一个事务可以包含多条sql语句，这些sql语句要么同时成功，要么一个都别想成功 ，称之为事务的原子性），一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

2、事务的作用

【保证对数据操作的安全性】

例子：还钱

付钱方和收款方，存在多条操作；

在操作多条数据的时候，可能会出现某些操作不成功的情况

【事务的四大特性——ACID】

A：原子性 atomicity

一个事务是不可分割的单位，事务中包含的诸多操作，要么同时成功，要么同时失败

C：一致性 consistency

事务必须是使数据库从一个一致性的状态 变为另外一个 一致性的状态

一致性跟原子性是密切相关的

I：隔离性 isolation

一个事务的执行不能被其他事务干扰

（即 一个事务内部的操作以及使用的数据，对并发的其他事务是隔离的，并发执行的事务之间也是互不干扰的）

D：持久性 durability

也叫"永久性"。一个事务一旦提交成功执行成功，那么它对数据库中数据的修改应该是永久的，

接下来的其他操作或者故障不应该对其有任何的影响

3、如何使用事务

# 事务相关的关键字# 1 开启事务start transaction;

# 2 回滚(回到事务执行之前的状态)rollback;

# 3 确认(确认之后就无法回滚了)commit;

当你想让多条sql语句保持一致性 要么同时成功要么同时失败 你就应该考虑使用事务

四、存储过程

1、什么是存储过程

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

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

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

2、基本使用

3、三种开发模型

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

【第一种】

应用程序:程序员写代码开发

MySQL:提前编写好存储过程，供应用程序调用

好处:开发效率提升了 执行效率也上去了

缺点:考虑到认为元素、跨部门沟通的问题 后续的存储过程的扩展性差

【第二种】

应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写

优点:扩展性很高

缺点: 开发效率降低 编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题

【第三种】

【2020Python修炼记】MySQL之视图、触发器、事务、存储过程、函数、索引

一视图

二触发器

三事务（掌握）

四存储过程

五函数

六流程控制

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

触发器可以在六种情况下自动触发——增前增后删前删后改前改后

（开启一个事务可以包含多条sql语句，这些sql语句要么同时成功，要么一个都别想成功，称之为事务的原子性），一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

事务必须是使数据库从一个一致性的状态变为另外一个一致性的状态

（即一个事务内部的操作以及使用的数据，对并发的其他事务是隔离的，并发执行的事务之间也是互不干扰的）

# 事务相关的关键字
# 1 开启事务
start transaction;

# 2 回滚(回到事务执行之前的状态)
rollback;

# 3 确认(确认之后就无法回滚了)
commit;

当你想让多条sql语句保持一致性要么同时成功要么同时失败
你就应该考虑使用事务

好处:开发效率提升了执行效率也上去了

缺点:考虑到认为元素、跨部门沟通的问题后续的存储过程的扩展性差

应用程序:程序员写代码开发之外设计到数据库操作也自己动手写

缺点:
开发效率降低
编写sql语句太过繁琐而且后续还需要考虑sql优化的问题

应用程序:只写程序代码不写sql语句基于别人写好的操作MySQL的python框架直接调用操作即可——如：ORM框架

缺点:语句的扩展性差可能会出现效率低下的问题

1 当表中有大量数据存在的前提下创建索引速度会很慢
2 在索引创建完毕之后对表的查询性能会大幅度的提升但是写的性能也会大幅度的降低
"""
索引不要随意的创建！！！

只有叶子节点存放的是真实的数据其他节点存放的是虚拟数据仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)

一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占得空间少一个磁盘块能够存储的数据多
那么久降低了树的高度从而减少查询次数

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

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