VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > temp > python入门教程 >

  • 四十九、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 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 before insert on 
t1
for each row
begin
    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 
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/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: 0

mysql> update user set banlance = 1010 where name = 'egon';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set banlance = 1090 where name = 'tank';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
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 variab
le 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 pymysql


conn = 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.4s
select count(id) from s1 where id = 1;        # 1.4s

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

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


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

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

create index idx_name on s1(name);  # 给s1表的name字段创建索引6.284s
select 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';      # 0s
select count(id) from s1 where name like 'xxx%';      # 0s
select 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.404s
select count(id) from s1 where id = 3;  # 快了      0s
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算0.671s

drop 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.559s
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速    4.895s

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

create index idx_id on s1(id);        # 4.625s
select 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.949s

drop index idx_id on s1;

create index idx_email on s1(email);        # 8.029s
select 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.734s
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快0.005s
总结:
搜索范围大，耗时长
识别度低的索引：耗时比无该索引更长


慢查询日志
设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！
复制代码
出处：https://www.cnblogs.com/MRPython/p/15231396.html

相关教程