MySQL视图、事务

视图

什么是视图?

　　一个查询语句的结果是一张虚拟表，将这种虚拟表保存下来，它就变成了一个视图

为什么要用视图？

当频繁需要用到多张表的连表结果，你就可以事先生成号视图之后直接调动即可，避免了反复写连表操作的sql语句

如何使用？

 

create view teacher_course as select * from teacher INNER JOIN course
    on teacher.tid = course.teacher_id;

 

1.视图只有表结构,视图中的数据还是来源于原来的表
2.不要改动视图表中的数据
3.一般情况下不会频繁的使用视图来写业务逻辑

 

 

 

触发器

到达某个条件时自动触发

当你在对数据进行增、删、改的情况下会自动触发触发器的运行

 

触发器分为六种情况

固定语法结构

 

create trigger 触发器的名字 after/before insert/update/delete on 表名 for each row
            begin
                sql语句
            end
# 详细版本
create trigger tri_before/after_insert/update/delete_t1 after/before insert/update/delete on t1 for each row
            begin
                sql语句
            end

 

 

 

增前

　　可以修改MySQL默认的结束符（；）

  delimiter $$  # 只对当前窗口有效
            create trigger tri_before_insert_user before insert on user for each row
            begin
                select * from user
            end $$
            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
);

补充: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 ;

 

 

 

事务

事务包含一大堆sql语句，这些语句要么同时成功，要么一个也别想成功（有一个失败，所有的都失败）

事务四大特征：ACID

　　A：原子性

　　C：一致性

　　I：隔离性

　　D：持久性

原子性（atomicity）： 表示组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。

 

一致性（consistency）：事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

 

隔离性（isolation）：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据，该数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

 

持久性（durability）：持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

 

存储过程

就类似于python中的自定义函数，内部封装了操作数据库的sql语句，后续想要实现相应的操作，只需要调用存储过程即可。

# 语法结果
      delimiter $$
      create procedure p1()
      begin
          select * from user;
      end $$
      delimiter ;

#带参数的
      delimiter $$
      create procedure p1(
          in m int,  # 不能被返回出去
          in n int,
          out res int,  # 可以被返回
          inout xxx int,  # 既可以进又可以出
      )
      begin
          select * from user;
      end $$
      delimiter ;

#调用
call p1()

#实例
delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 就类似于是一个标志位 用来标识存储器是否执行成功
end $$
delimiter ;

call p1()  # 调用存储过程并传参
##传参，将标志位直接传进去会报错
set @res = 10   # 设置一个变量与值的绑定关系

之后通过
select @res
查看存储过程执行完成后的返回结果

注意：存储过程在那个库下面定义就只能在那个库下面使用。

 

 

pymysql调用存储过程

import pymysql

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '123',
    database = 'day38',
    charset = 'utf8',
    autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# call p1()  mysql中调用
# callproc()  pymysql中调用
cursor.callproc('p1',(1,5,10))
# 内部自动用变量名存储了对应的值
print(cursor.fetchall())
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10

@_存储过程名_索引值
"""
cursor.execute('select @_p1_0')
print(cursor.fetchall())
cursor.execute('select @_p1_1')
print(cursor.fetchall())
cursor.execute('select @_p1_2')
print(cursor.fetchall())

 

函数

自定义函数

#！！！注意！！！
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程

"""
delimiter //
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END //
delimiter ;
"""
delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;
"""

 

 

 

流程控制

条件语句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 ;

 

索引

知识回顾：数据都是存在硬盘上的，查询数据不可避免的需要进行IO操作

索引在MySQL中也叫作"键"，是存储引擎用于快速找到记录的一种数据结构。

• primary key
• unique key
• index key

 

因为foregin key不是用来加速查询用的，所以不在我们的研究范围内，上面三种key中，前两种除了有加速查询的效果之外还有额外的约束条件（primary key:非空且唯一；unique key:唯一），而index key没有任何约束功能只会帮你加速查询。

 

索引本质就是一种数据结构，类似书的目录，意味着我们在查数据的时候应该先找目录再找数据，而不是用翻页的方式查询数据。

 

本质：都是通过不断缩小想要获取数据的范围来帅选出最终想要的结果，同时把随机的时间变成顺序的时间，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

 

 

索引的影响

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

B+树

 

只有叶子节点存放真实数据，根和树枝节点存的仅仅是虚拟数据，查询次数由树的层级决定，层级越低次数越少。

 我们知道一个磁盘的大小是一定的，也就是存放的数据量也是一定的，我们在每一个数据项内存放的数据越多那么分的层就越少，查询的速度也快，那么在一张表里，用什么字段建立的索引能够降低树的层级高度>>>主键id字段。

 

聚集索引（primary key）

聚集索引指的就是表的主键，innodb引擎规定一张表中必须要有主键。

我们回顾一下存储引擎建表，myisam在建表的时候有三个文件，而innodb在建表是有两个文件，frm文件只存放表结构，不能存放索引，也就是说innodb的索引跟数据都放在idb表数据文件中。 

特点：叶子节点放一条条完整的记录

 

 

辅助索引（unique,index）

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name,password等字段信息，那么这个时候就无法利用聚集索引的加速查询效果。就需要给其它字段建立索引，这些索引就交辅助索引。

特点：叶子结点存放的是辅助索引对应的那条记录的主键的值

select name from user where name='jason';

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

select age from user where name='jason';

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