MySQL 的视图、事务、触发器

1、视图

　　简化sql语句的编写，限制可以查看的数据

　　一张虚拟的表，不占任何内存，查视图时都是临时从所查的表中拿数据

　　特点：

　　　　对于视图的增删改查 都会同步到原始表

　　　　对原始表的修改，会同步到视图内可查看的数据，

　　　　视图的修改会同步到原始表中，但由于视图可能部分字段，很多时候会失败。所以一般不对视图修改

　　创建语法：

　　　　create [or replace] view view_name [表的查询结果]；

　　　　or replace ,如果存在视图则替换

　　查看视图：

　　　　desc view_name; //查看数据结构

　　　　show create view view_name; //创建语句

　　修改视图

　　　　alter view view_name as select_statement;

　　删除视图

　　　　drop view view_name;

2、触发器

　　当达到某个条件时，触发某个代码的执行。

　　语法：

　　　　create trigger t_name t_time t_event on table_name for each row

　　　　begin

　　　　　　具体代码

　　　　end

　　　　支持的时间点t_name: before|after

　　　　支持的事件t_event: update inset delete

　　　　　　update 可以用old访问旧数据 new访问新数据

　　　　　　insert 可以用new访问新数据

　　　　　　delete 可以用old访问旧数据

　　　　　　(new和old是内部封装的对象，指的是这条数据的所有字段)

　　删除触发器： drop trigger trigger_name;

#准备数据
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 //
create trigger trigger1 after insert on cmd for each row
begin
if new.success = "no" then
    insert into errlog values(null,new.cmd,new.sub_time);
end if;
end//
delimiter ;

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('jerry','0755','ls -l /etc',NOW(),'yes'),
    ('jerry','0755','cat /etc/passwd',NOW(),'no'),
    ('jerry','0755','useradd xxx',NOW(),'no'),
    ('jerry','0755','ps aux',NOW(),'yes');
# 查看错误日志表中的记录是否有自动插入
select *from errlog;

　　注意：

　　　　1. delimiter 修改默认的行结束符 

　　　　2. 外键不能触发事件，主表删除摸个主键，从表删除也会相应删除，但是不会执行触发器

　　　　3. 触发器中不能使用事务，不能使用select查询数据

　　　　4. 相同时间点的相同事件的触发器不能同时存在

3、事务

　　事务是一组逻辑上的操作，要么成功要么失败

　　好处：解决了一些不可控因素造成的数据不完全修改问题，保证了数据库的完整性；

　　如何使用：

　　　　开启事务：start transaction;

　　　　设置保存点： savepoint save_name;

　　　　提交： commit; 使数据生效，

　　　　回滚： rollback; 如果没有提交，可以使数据回到原来状态,rollback save_name;

　　使用pymysql实现事务：

　　　　pymysql内部封装了事务的开启，我们需要的做的事执行成功后提交事务commit()

　　　　注：连接时，autocommit=True，则不开启事务，一般不修改，默认为False。

try:
    conn =pymysql.connect(host="127.0.0.1",
                          user="root",
                          password="",
                          db="day46")
    print("连接服务器成功!")

    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = 'update account set money = money - 1000 where id = 1;'
    sql2 = 'update account set money = money + 1000 where id = 2;'  # money打错了将导致执行失败
    try:
        cursor.execute(sql)
        cursor.execute(sql2)
        conn.commit()
        print("执行成功 提交")
    except:
        print("发送错误   回滚..")
        conn.rollback()

except Exception as e:
    print("连接服务器失败.....")
    print(type(e),e)
finally:
    if cursor:cursor.close()
    if conn:conn.close()

4、事务的特性

　　原子性：一个事务是一个不可分割的整体，要么都成功，要么都失败

　　一致性：事务前后的数据完整性应保持一致。（数据库的完整性：如果数据库在某一时间点下，所有的数据都符合所有的约束，则称数据库为完整性的状态）

　　隔离性：多个并发事务之间数据要相互隔离

　　持久性：一个事物一旦提交，他对数据的改变就是永久性的。

5、事务的用户隔离

　　遇到的并发问题：

　　　　脏读：当前事务读到的数据是别的事务想要修改但还没有修改成功的数据

　　　　不可重复读：当前事务先进行了一次数据读取，然后再次读取到的数据，

　　　　　　　　　　就是别的事务修改成功的数据，导致两次读取的数据不匹配。

　　　　幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或添加M条如何事务A搜索条件的数据，导致事务A再次搜索发现了N+M条数据了。

　　解决办法：

　　　　read uncommitted -- 不做任何隔离，可能脏读

　　　　read committed -- 可以防止脏读，不能防止不可重复读和幻读

　　　　Repeatable read -- 可以防止脏读，不可重复读，但不能防止幻读

　　　　Serializable -- 数据库运行在串行化实现，所有问题都没有，就是性能低    

　　修改隔离级别：

　　　　select @@tx_isolation; -- 查看当前级别

　　　　set [session|golbal] transaction isolation level read uncommitted; -- 修改级别

　　　　在配置文件内修改：

　　　　　　transaction_isolation=REPEATABLED-READ

6、存储过程

　　一个存储过程中包含任意sql语句，以及流程控制事务等，

　　创建存储过程

　　　　create procedure p1(type 参数名 数据类型）

　　　　type: in输入参数， out输出参数， inout既能输出又能输入

delimiter //
    create procedure p2(in a double,in b double,out res char(20))
    begin
        select *from account where money >= a and  money <= b;
        set res = "success";
    end//
    delimiter ;

    set @x='' --设置变量
    call p2(2,3,@x);
        select @x;

　　删除存储过程：drop procedure 名称；

　　查看存储过程：

　　　　# 当前库中所有的存储过程名称

　　　　select name from mysql.proc where db='db02' and type='procedure';

　　　　# 查看创建语句

　　　　show create procedure p1;

　　　　# 查看所有库的状态

　　　　show procedure status;

import pymysql

conn = pymysql.connect(host="127.0.0.1",user="root",password="123321",
                       database = "day46",autocommit = False)
cursor = conn.cursor(pymysql.cursors.DictCursor)

#调用存储过程  如果是一个输出out参数 随便给个值就行
cursor.callproc("p2",(500,2000,0))

# pymysql会自动定义变量  @_存储过程名称_参数索引
# 分别是 : @_p2_0  @_p2_1 @_p2_2

# 获取结果
print(cursor.fetchall())
# 获取存储过程的结果
cursor.execute("select @_p2_2")
print(cursor.fetchall())

注：若存储过程内有多个select，pymysql只能得到第一个select的结果

delimiter //
create PROCEDURE p5(OUT p_return_code char(20))
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        # ERROR
        set p_return_code = "出现异常,回滚!";
        rollback;
    END;
    # exit 也可以换成continue 表示发生异常时继续执行
    DECLARE exit handler for sqlwarning
    BEGIN
        # WARNING
        set p_return_code = "出现警告,回滚!";
        rollback;
    END;

    START TRANSACTION;
    update account set money = money - 1000 where id = 1;
    update account set moneys = money - 1000 where id = 1; # moneys字段导致异常
    COMMIT;

    # SUCCESS
    set p_return_code = 0; #0代表执行成功
END //
delimiter ;

#在mysql中调用存储过程
call p5(@res);
select @res;

 　　存储过程与函数的区别：

　　　　函数仅仅是一个单纯工具 与数据无关 所以函数中不能出现msl语句

　　　　存储过程即可包含mysql的逻辑代码 也能包含sql语句

 7、函数

CREATE FUNCTION f_name(paramters)
returns dataType
return value;

# 说明：
#     paramters 只能是in输入参数
#     必须有返回值
#     returns后面是返回值的类型，这里不加分号
#     return后面要的是返回值

8、数据备份

#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

1、退出数据库后
mysql -u -p < filename.sql;
2、在数据库内
use db1;
source /root/db1.sql

9、索引

什么是索引？
        一索引是一种单独的， 物理层面的数据结构，其作用是用于加速查询    
    为什么需要索引？
        mysql把数据存储到硬盘中，硬盘读写速度非常慢，
        一个应用程序，本质就是对数据进行增删改查
        一旦数据量大时，硬盘响应变慢，

        查询操作是应用程序使用中频率最高的操作，所以索引的目的就是提高查询速度
        加入索引带来的问题：
            1. 添加索引，使得整体的数据更大了
            2. 数据修改时，都会引发索引的重建(效率降低了)   
 
        索引的实现：
            查询之所以慢的原因，硬盘的IO速度问题
            mysql 通过B+树结构来组织数据，降低IO次数，
            树的结构越低越IO次数越少 所以建议把数据量小的作为索引 来降低高度
            （B+树使用最左匹配原则）

    如何使用索引？
        聚集索引(主键)
            主键索引，速度快，因为只要根据id找到椰子节点，那么该行的所有数据都能拿到
            innodb 需要主键索引来建立数据结构
            所以每个表都应该有主键
        
        辅助索引
            除了主键索引之外的所有索引都是辅助索引
            辅助索引会单独创建树结构，其中存储索引数据本身以及该数据对应主键值
        
            查找过程中可定出现的情况：
                覆盖索引：是在当前树结构中就拿到所需的数据
                回表： 在辅助索引中没有查询到需要的数，需要拿id回到主键索引中查找

    查询速度
        主键 > 辅助索引 > 回表
    
    编写sql时，如果有主键值 有限使用聚集索引
    如果没有主键值 需要用辅助索引，这时就尽量少查字段 最好保证需要的数据就要在辅助索引中
    避免 select *

索引优化：
    索引结构优化
        数据量小的
        应该把重复度低的字段作为索引

    sql语句的优化
        sql语句中条件应该时索引字段
        避免模糊查询类似的“like  '%aaaa'”法
        不要再主键进行运算，例如： where id*10=100；应该先算出来再查询
        在and 语句中mysql 会优先查询带索引的字段 无论书写位置在前或在后
        
        or 语句中 不会自动选择有索引的 时依次执行 无论是否有条件成立都查一遍
        优化：使用union
        
创建索引的语法：
    create index 索引的名字 on 表名(字段名)
 删除索引
    drop index 索引名字 on 表名;

多字段联合索引
    要查询的字段较多，如果每个字段创建索引 会造成额外的容量的占用，并且 当你修改一条记录时，有可能所有索引都需要重建

    顺序是重点：创建索引时，把重复度低的字段放到最左边
        create index all_index on user(email, name, gender);
    编写sql时，保证重复度低的字段出现即可