526互联

MySQL(视图、事务、存储过程、函数、流程控制、索引）

发布时间 2023-07-21 18:19:07作者: coder雪山

一 视图（了解）

  • 什么是视图

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

  • 为什么要用视图

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

视图其实也是一张表

  • 如何操作

# 固定语法
create view
# 具体操作
create view teacher2course as 
select * from teacher inner join course
on teacher.tid=course.teacher_id;
show tables;

  • 注意 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 ;
    
删除触发器
drop trigger tri_after_insert_cmd;

使用

#准备表
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中插入记录，触发触发器，根据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');


#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

三 事务

什么是事务

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

事务的作用

保证了数据操作的安全性

eg: 在转账过程中，转账方转了，接受方因故障（网络、系统）接受不了，

出现上述问题，就需要用到事务。（在操作多条数据的时候可能会出现某几条

操作不成功的情况）

事务的四大特性

ACID

A: 原子性（atomicity) 一个事务是一个不可分割的单位，事务中包含的诸多操作 要么同时成功要么同时失败

C：一致性(consistency) 事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态 一致性跟原子性是密切相关的

I：隔离性(isolation) 一个事务的执行不能被其他事务干扰 （既一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰）

D：持久性(durability) 也叫“永久性” 一个事务一旦提交成功执行，那么它对数据库中的数据的修改应该是永久的(写到硬盘) 接下来的其他操作或者故障不应该对其有任何影响

如何使用事务

# 事务相关的关键字
# 1.开启事务
start transaction；
# 2.回滚（回到事务执行之前得状态）
rollback；
# 3.确认（确认之后就无法回滚）
commit;

# 模拟转账功能
create table username(
    id int primary key auto_increment,
    name char(16),
    balance int
);

insert into username(name,balance) values(
    ('lq',1000),
    ('zd',1000),
    ('xiaobao',1000);
    
# 1.先开启事务
start transaction;
# 2.多条sql语句
update username set balance=900 where name='lq';
update username set balance=1010 where name='zd';
update username set balance=1090 where name='xiaobao';
# 3.验证rollback
rollback;
    # 能恢复到初始表格
# 4.验证commit
commit;
    # 刷到硬盘，在rollback命令，回不到初始表格
'''
总结
当你想让多条sql语句保持一致性，要么同时成功要么同时失败
你就应该考虑使用事务
'''

四 存储过程（了解）

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

它的内部包含了一系列可以执行的sql语句，

存储过程存放于MySQL服务端，你可以直接通过调用存储过程触发内部sql语句的执行

基本使用

create procedure 存储过程的名字(参数)
begin
    sql语句
end
# 调用
call 存储过程的名字();

第一种

应用程序：程序员写代码开发 MySQL:提前编写好存储过程，供应用程序调用

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

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

第二种

应用程序：程序员写代码开发之外，涉及到数据库操作也自己动手写

优点：扩展性很高

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

第三种

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

ORM框架

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

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

总结： 第一种基本不用，出现效率问题再动手写sql

存储过程具体演示

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

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

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

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='day47',
    charset='utf8',
    autocommit=True # 完整配置
)

cursor = conn.cursor(pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 5,10))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2;")
result = cursor.fetchall()

cursor.close()
conn.close()

print(result)

五 函数（了解）

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

NOW()

六 流程控制（了解）

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 ;

七 索引

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

就是一种数据结构，类似于书的目录。意味着以后在查询数据的应该先找目录再找数据，

而不是一页一页的翻书，从而提升查询速度降低IO操作 索引在MySQL中也叫“键”，是存储引擎用于快速查找记录的一种数据结构

primary key

unique key

index key

注意：foreign key不是用来加速查询用的，

不在索引研究范围 上面的三种key,前面两种除了可以增加查询速度之外各自还具有约束条件，

而最后一种index key没有任何的约束条件，只是用来帮助快速查询数据。

本质

通过不断的缩小想要的数据范围筛选出最终的结果，

同时将随机事件（一页一页的翻）变成顺序事件（先找目录、找数据）

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

一张表可以有多个索引（多个目录） 索引虽然能够帮助加快查询速度的，但也有缺点。

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

b+树

只有叶子节点存放的是真实的数据，其他节点存放的是虚拟数据，仅仅是用来指路的

树的层级越高查询数据所需经历的步骤就越多（树有几层，查询数据就要几步）

一个磁盘块存储是有限制的

为什么建议将id字段作为索引

　　 占的空间少，一个磁盘块能够存储的数据多

　　·那么减低了树的高度，从而减少查询次数

聚集索引（primary key)

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

辅助索引（unique,index)

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

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

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

覆盖索引

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

  • 给name设置辅助索引(辅助索引取值，不再通过主键取值)

　　　　select name from user where name='jason';

  • 非覆盖索引

　　　　select age from user where name='jason';