随笔- 11 文章- 0 评论- 0 阅读- 491

数据库4/视图/触发器/事务/存储过程/函数/流程控制/索引

视图

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

select *from emp inner join dep on emp.dep_id=dep.id inner join t on t.id=emp.t_id left join tt on tt.id=''



# 查询慢了
# SQL优化
# 慢查询

2、为什么要用视图

如果要频繁使用一张虚拟表(拼表组成)，就可以制作成视图，后续直接操作

3、如何用视图

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


create view 视图名称 as ················(拼接代码/虚拟表的查询语句);

drop view teacher2course; # 删除视图

总结：
1 视图创建好之后，硬盘上只有表结构，没有表数据，数据还是来自之前的表
2 视图一般只用来查询，里面的数据不要进行修改，可能会影响真正的表。
3 去删除的视图中的数据时，会报错，因为会影响原表的数据错乱

"""
创建好了之后 验证它的存在navicat验证 cmd终端验证
最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
delete from teacher2course where id=1; # 删除不成功，不能删除 只能查
"""

注意事项以及思考

强调

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

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

3 ，当创建了很多视图之后，会造成表的不好维护

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

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

触发器

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

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

触发器可以在六种情况下自动触发， 增前，增后，删前，删后，改前改后。 针对不同的情况可以写不同的触发器

# 只针对增删改的行为，

触发器基本语法结构

"""语法结构
delimiter $$
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    sql语句
end
delimiter ; 
"""
# 删除触发器
drop trigger tri_after_insert_cmd;

ql代码的时候结束符是;
而整个触发器的结束也需要分号;
这就会出现语法冲突 
需要我们临时修改结束符号
# 这种修改只在当前窗口生效

触发器例子:

# 针对插入
create trigger tri_after_insert_t1 after 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_after_update_t1 after update on 表名 for each row
begin
    sql代码。。。;
end

#········································································
delimiter $$
触发器语法结构。。。。。。；
delimiter ; 
# 由于触发器语法结构中也有';' 号，所以要临时修改sql语句结束符，该语法只在当前窗口有效  
#········································································
# 案例
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
);

delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then  # 每插入一条记录都会被MySQL封装成一个对象，
        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
    ('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;

事务（掌握）

什么是事务

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

事务的作用

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

案例:用交行的卡操作建行ATM机给工商的账户转钱

事务应该具有4个属性：原子性、一致性、隔离性、持久性。

这四个属性通常称为'ACID特性'。
'A'
原子性（atomicity）。'一个事务是一个不可分割的工作单位,事务中包含诸多的操作'事务中包括的诸操作要么都做，要么都不做。
'C'
一致性（consistency）。'事务必须是使数据库从一个一致性状态变到另一个一致性状态。'一致性与原子性是密切相关的。
'I'
隔离性（isolation）。一个事务的执行不能被其他事务干扰。'即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰'。
'D'
持久性（durability）。持久性也称永久性（permanence），'指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的'。接下来的其他操作或故障不应该对其有任何影响。

事务相关的关键字

# 1 开启事务
start transaction;

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

# 3 二次确认，提交数据(刷到硬盘中) 
commit; '确认之后就无法回滚了'

* 开启事务检测操作是否完整，完整提交数据，不完整回滚。
想让程序保持执行，就开启事务。

补充； 存储过程在pycharm中如何调用？
cursor.callproc('p1',(1,5,10))

如何用事务？

# 先介绍事务的三个关键字 再去用表实际展示效果
start transaction;
...

commit;
rollback;
#·····················模拟转账功能····················
create table user(            # 创建表
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)        # 插入数据
values
('jason',1000),
('egon',1000),
('tank',1000);

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

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
rollback;

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

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

#·····································································
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

# 那如何检测异常？
# 事务要尽量少的开
# 不要写一个SQL语句都写到事务里面去

# 事务的隔离级别(了解)
# 脏读  幻读... 可重复读

存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数

基本使用/语法结构

# 执行sql代码的时候，会有 ';' 作为结束条件，所以要临时更换结束符号
delimiter $$ 
create procedure 存储过程中的名字(形参1，形参2....)
begin
    sql代码;
end $$
delimiter;

# 调用
call 存储过程中的名字()

三种开发模型

一般用哪种？？
'一般都是第一种和第三种'


"""
第一种
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用

优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""

"""
第二种
应用程序：除了开发应用程序的逻辑，还需要编写原生sql

优点：比方式1，扩展性高(非技术性的)
缺点：

1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""

"""
第三种
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM框架

优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：语句的扩展性差，可能出现效率底下的问题
"""

创建存储过程

# 介绍形参特点  再写具体功能

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

set @res=10;  # 形参res 不能直接传数据，应该传一个变量名，set设置变量名
select @res;  查看
call p2(1,5,@res)  调用
select @res;  查看


补充； 存储过程在pycharm中如何调用？
cursor.callproc('p1',(1,5,10))

如何用存储过程

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')


# 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 user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;

函数

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

# 用到MySQL的内置函数
date_format()

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

例子：

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

char_length  'mysql'的内置函数
思路：

id | NAME                                  | sub_time            |        sub_month
+----+--------------------------------------+---------------------+
|  1 | 第1篇                                | 2015-03-01 11:31:21 |        2015-03
|  2 | 第2篇                                | 2015-03-11 16:31:21 |        2015-03
|  3 | 第3篇                                | 2016-07-01 10:21:31 |        2016-07
|  4 | 第4篇                                | 2016-07-22 09:23:21 |        2016-07
|  5 | 第5篇                                | 2016-07-23 10:11:11 |        2016-07
|  6 | 第6篇                                | 2016-07-25 11:21:31 |        2016-07
|  7 | 第7篇                                | 2017-03-01 15:33:21 |        2017-03
|  8 | 第8篇                                | 2017-03-01 17:32:21 |        2017-03
|  9 | 第9篇                                | 2017-03-01 18:31:21            2017-03

select count(*), sub_month from blog group by sub_month;

# 把年月日 时分秒的时间格式转为 年-月的形式，然后在对年-月分组

# 用到MySQL的内置函数
date_format()

# 能使用代码操作的尽量使用代码操作，不要过多的使用MySQL的函数

流程控制

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

END //
delimiter ;

索引

索引是一种数据结构，类似于书的目录，意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查找数据，索引也叫做键，存储引擎中用于快速找到记录的一种数据结构

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

forergn key 不是用来查询的，不在我们的研究范围内 不能提速


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

索引的影响

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

# 以后实际添加索引的时候，尽量在空表的时候添加，在创建表的时候就添加索引，此时添加索引是最快的

# 如果表中数据已经有了，还需要添加索引，也可以，只不过创建索引的速度会很慢，不建议这样做



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

# 但是，写的性能影响不是很大，因为在实际中，写的频率很少，大部分操作都是查询

# 如何添加索引？到底给哪些字段加索引呢?
'''没有固定答案，具体给哪个字段加索引，要看你实际的查询条件'''
select * from user where name='' and password='';

# 索引的使用其实是需要大量的工作经验，才能正确的判断出

'''不要一创建表就加索引，在一张表中，最多最多不要超过15个索引，索引越多，性能就会下降'''

# 如何数据量比较小，不需要加索引，100w一下一般不用加，mysql针对于1000w一下的数据，性能不会下降太多.

b+树

# 树------>二叉树 平衡树  b树  b+树 b-树...

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

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

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

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项


# 以后加索引的时候，尽量给字段中存的是数字的列加,我们使用主键查询速度很快
select * from user where name = ''
select * from user where id = ''  # 主键查询的更快一些

在一张表里面在什么字段建立索引能够降低树的层级高度>>> 主键id字段

聚集索引(primary key)

聚集索引其实指的就是'表的主键 '

InnoDB 只有两个文件 直接将主键存放在了数据表中，idb表中

MyISam    三个文件 单独将索引存在一个文件 

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

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

叶子节点存放的是数据对应的主键值
    先按照辅助索引拿到数据的主键值
    之后按照逐渐主键的聚焦索引去查询数据

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

覆盖索引

select name from user where name='jack';

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

select age from user where name='jack';

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

测试索引

准备

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

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<100000)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=1000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 1000000;
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'  # 速度仍然很慢

10000000
1000


select * from s1 limit 0,1000;
select * from s1 limit 1000,1000;
select * from s1 limit 2000,1000;

select * from s1 limit 9999000,1000; # 把100000万条数据都查一遍，然后把前99000条丢掉，要最后1000
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
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字段一剑封喉

联合索引

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; 

where id=1;
where name='';
where name='jason' and age=18;
where name='jason' and age=18 and gender ='male';

"""最左匹配原则"""
KEY `index_1` (`name`,`gender`,`email`) # 联合索引
where name = '';
where name='' and gender ='';
where name = and gender = and email=;


KEY `index_1` (`name`) # 
KEY `index_1` (`name`,`gender`) 
KEY `index_1` (`name`,`gender`,`email`)

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
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语句，然后针对性的进行优化！

# 公司中一般会使用云数据库------->购买的阿里云---------->阿里云的rds数据库
# 自己在公司中需要自己搭建一个本地的测试数据库

posted @ 2023-07-14 20:42 路过不说话阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mysql数据库3 表关系/多表查询等

· 数据库mysql

· 视图，触发器，存储过程，函数，流程控制，事务，索引，sql注入

· 39.事务视图触发器函数 (内置) 存储过程流程控制索引

· python入门学习篇三十五

公告

昵称：路过不说话
园龄： 1年9个月
粉丝： 2
关注： 10

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

python(4)

xiaoyou898