上海 day39--触发器、事务、索引（数据库）

navicat可视化数据库管理软件
    本质就是一个数据库的客户端
        内部其实也是调用的sql语句操作数据

    ORM:对象关系映射(能够让不会sql语句的程序员也能够简单快速的操作数据库)
    类       >>>         表
    对象     >>>         表的一条记录
    对象获取属性   >>>   表的一条记录的某个字段对应的数据

    obj.name = 'jason'


    了解 设置数据库编码的时候 有一个utf8mb4支持存表情

MySQL练习题
    在写sql语句的时候 一定不要一口气写完 也不要一口气把题目读完
    写一点查一点再写一点

pymysql模块  支持python代码操作数据库
前提:你要事先创建好数据库


    conn = pymysql.connect(
        host = '127.0.0.1',
        port = 3306,
        user = 'root',
        password = '123'
        database = 'day39',
        charset = 'utf8',
        autocommit = True  # 自动提交  增 删 改的时候 需要自动提交确认
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute(sql,(arg1,arg2))  # 能够识别%s并且自动过滤特殊符号 完成sql语句的拼接

    cursor.fetchone()
    cursor.fetchall()
    cursor.fetchmany(n)  # 指定拿多少条数据
    获取数据类似于文件内光标的移动

    控制移动
    cursor.scroll(1,'relative')
    cursor.scroll(1,'absolute')


    insert
    update
    select
    delete

    conn.commit()  # 如果配置了 就不用写  否则必须得写



    sql注入问题
        利用特殊符号(MySQL中的注释--)巧妙的修改sql语句

        关键性的数据都不要手动拼接 全部交给execute帮你自动拼接

内容回顾

（补充ORM 概念。。。）

一、视图

什么是视图？

当我们执行SQL 查询语句时得到的结果就是一张虚拟表，我们将这张虚拟表保存下来就是一个视图。

为什么要使用视图？

当我们频繁使用多张表的连表结果的时候，我们就需要将连表结果的视图保存下来，这样就避免了多次执行连表查询操作。

视图怎么用？

create view course_teacher(视图名) as
select * from course inner join teacher 
on course.teacher_id = teacher.tid;

注意：

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 ;




delimiter $$  # 只对当前窗口有效
create trigger tri_after_insert_user after insert on user for each row
begin
    insert into user(name,password) values('老子翻车了','123');
end $$
delimiter ;


drop trigger tri_before_insert_user;

三、事务

什么是事务？

事务包含一堆sql语句，这些sql语句要么同时执行成功，要么全都失败！

联想场景：

一个人给另一个人赚钱，A转账给B，A向支付宝或网上银行提出转账请求，

A账户上减掉1000元，当数据库发送消息准备向B账户增加1000块钱时，网络中断服务失败，但这时A账户已经扣钱，B账户还没有收到钱！！

因此这需要上应用数据库的“事务”，当两边的操作没有全部完成时，已经执行的一方可以进行“回滚”到之前的转太！！！！

事务的四大特性：A C I D

　　原子性、一致性、隔离性、持久性、

原子性：一个事务是一个不可分割的最小的工作单位，事务中的诸操作要么都做，要么都不做。

一致性：一个事务必须使数据库从一个一致性状态转到另一个一致性状态，事务的一致性和原子性是密不可分的。

隔离性：一个事务的执行不能被其他事务影响。一个事务的内部操作和使用数据对并发的其他事物是隔离的，并发的事务之间是不能相互影响的。

持久性：持久性也叫“永久性”，一个事务一旦提交，对数据库数据的修改时永久不可变的，以后的任何操作都不能对它产生影响。

事务的关键的语法代码：

开启事务：

　　start transaction

事务回滚：

　　rollback

永久性更改

　　commit

代码示例：（部分）

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

四、存储过程

什么是存储过程？

数据库的存储过程就是相当于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 ;


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 ;

set @res = 10   # 设置一个变量与值的绑定关系

调用存储过程 将变量@res传入

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


存储过程在哪个库下面定义 就只能在哪个库下面使用





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 ;

View Code

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())

五、函数

详见鸡哥数据库md文档

六、流程控制

代码示例：

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

mysql中流程控制的写法

七、索引与慢查询优化