python笔记(MySQL:视图，触发器，函数，存储过程，索引，分页)

详细了解

一、大体总结
-数据行：
------------排序：order by desc/asc
------------分组：group by
------------条件：where
------------连表：left join
---------------------right join
---------------------inner join
------------临时表
------------通配符
------------分页：limit
----------- 组合：union
------------触发器
------------函数
------------存储过程
------------游标
------------事务（多个数据库操作作为一个整体，只有当所有的成功完成才算完成）
二、视图
本质：给某个查询语句设置别名，日后方便使用（虚拟的表）
1.视图创建：
--------------create view 视图名称 as SQL语句
2.视图修改：
--------------alter view 视图名称 as SQL语句
3.视图删除：
--------------drop view 视图名称
三、触发器
本质：当对某张表做：增删改操作时，可以使用触发器自定义关联行为
delimiter // ：将终止符改为//
new:代指新数据
old:代指老数据
1、插入前

delimiter //
create trigger tri_before_insert_tb1 before insert on tb1 for each row
begin
   insert into tb2(....);
end//
delimiter ;

delimiter //
create trigger tri_before_insert_tb1 before insert on tb1 for each row
begin
   insert into tb2(tname) values(new.sname);     #将新插入的数据放到触发器中（new.sname）
end//
delimiter ;

2、插入后

create trigger tri_after_insert_tb1 after insert on tb1 for each row
begin
   insert into tb2(....)
end

3、删除前

create trigger tri_before_insert_tb1 before delete on tb1 for each row
begin
   ......
end

4、删除后

create trigger tri_after_insert_tb1 after delete on tb1 for each row
begin
   ......
end

5、更新前

create trigger tri_before_insert_tb1 before update on tb1 for each row
begin
   ......
end

6、更新后

create trigger tri_after_insert_tb1 after update on tb1 for each row
begin
   ......
end

四、函数
执行函数:select f();
1、内置函数：
—字符串：

—时间：
---------select current_timestamp();#查看当前时间
---------select ctime,count from blog group by date_format(ctime,"%Y-%M") #以xxxx年-xx月分组
2、自定义函数(有返回值)：

delimiter //
create function f1(i1 int,i2 int)
returns int
begin
   declare num int;         #声明变量
   set num = i1+i2;
   return(num);
end //
delimiter ;

五、存储过程
本质：保存在MySQL上的一个别名==一堆SQL语句
执行语句：别名()
优点：用于替代程序员写SQL语句
1、简单存储过程

delimiter //
create procedure p1()
begin
  select * from student;
  insert into teacher(tname) values('xxxx');
end //
delimiter;

#执行：
    call p1()
    cursor.callproc('p1')  #pymysql的python执行语句

2、带参的存储过程（in）

delimiter //
create procedure p1(
              in n1 int,
              in n2 int
             )
begin
  select * from student where sid>n1;
end //
delimiter;

#执行：
call p1(3,2);    #执行语句
cursor.callproc('p1',(2,3))  #pymysql的python执行语句

3、参数out

delimiter //
create procedure p1(
              in n1 int,
              out n2 int
             )
begin
  set n2 = 12; 
  select * from student where sid>n1;
end //
delimiter;


set @v1=0;        #相当于全局变量，是会话级的
call p1(2,@v1)
select @v1;       #查看@v1

cursor.callproc('p1',(2,3))  #pymysql的python执行语句
r1=cursor.fetchall()
cursor.execute("select @_p1_0,@_p1_1")  #pymysql的python执行语句
r2=cursor.fetchall()

3、参数inout

delimiter //
create procedure p1(
              in n1 int,
              inout n2 int
             )
begin
  set n2 = 12; 
  select * from student where sid>n1+n2;
end //
delimiter;


set @v1=0;        #相当于全局变量，是会话级的
call p1(2,@v1)
select @v1;       #查看@v1

cursor.callproc('p1',(2,3))  #pymysql的python执行语句
r1=cursor.fetchall()
cursor.execute("select @_p1_0,@_p1_1")  #pymysql的python执行语句
r2=cursor.fetchall()

4、事务

delimiter //
create procedure p1()
begin
     （1）、声明如果出现异常则执行{
         set status = 1;
         rollback;
     }
      开始事务：
         .....
         commit;
      结束
         set status = 2; 
     
end //
delimiter;
...........................................................
delimiter //
create procedure p1(
    out p_return_code tinyint     --定义p_return_code为输出
)
begin
    declare exit handler for sqlexception
    begin
       #error
       set p_return_code=1;
       rollback;
    end;
    
    
    start transaction;
       delete from tb1;
       insrtt into tb2(name) values('liyouxiu');
    commit;
       #success
    set p_return_code=0;
end //
delimiter;

5、游标(对于单独的表要进行操作时需要游标)

（1）、声明游标
（2）、获取A表中数据
      my_cursor select id,num from A
（3）、for row_id,row_num in my_cursor:
      检测循环是否还有数据，如果五数据break;
      insert into B(num) values(row_id+row_num)
............................................................
delimiter //
create procedure p1()
begin
   declare row_id int; --自定义变量
   declare row_num varchar(20);
   declare done int default false;   --定义done默认为false
   declare temp int;
   
   declare my_cursor cursor for select id,num from A; --声明游标为my_cursor
   declare continue handler for not found set done=true; --若没有值则将done改为true
   
   open my_cursor;
       start:loop    --start为自己定义的名字
           fetch my_cursor into row_id,row_num; --获取游标中的值给row_id,row_num
           if done then          --如果为真则退出
              leave start;
           end if
           set temp = row_id+row_num;  --定义temp
           insert into B(num) values(temp);
        end loop start;
   close my_cursor;        
end //
delimiter;

6、动态执行SQL(防SQL注入)

delimiter //
create procedure p1(
    int tp1 varchar(255),
    int arg int
    )
begin
    1、预检测某个东西 SQL语句的合法性
    2、SQL=格式化 tp1+arg
    3、执行SQL语句
    
    set @xo = arg;
    perpare xxx from 'select * from student where sid>?';
    execute xxx using @xo;
    deallocate perpare prod;
end //
delimiter;
call p1('select * from tb where sid>?',9)
...............................................................
delimiter //
create procedure p1(
    int nid int
    )
begin
    set @nid = nid;
    perpare prod from 'select * from student where sid>?';
    execute prod using @nid;
    deallocate perpare prod;
end //
delimiter;

7、一般的三种结合方式
方式一:
---------MySQL:存储过程
---------程序：调用存储过程
方式二:
---------MySQL:…
---------程序员：SQL语句
方式三：
---------MySQL:…
---------程序：类和对象

问题：为什么有结果集又有out伪造的返回值？
out:设置一个值，用于标识存储过程的执行结果

8、索引
作用：约束,加速查找
1、普通索引：加速查找

create table in1(name varchar(32) not null,index ix_name(name)) :在创建时添加
create index 索引名称 on 表名(列名)  :后天添加
drop index 索引名称 on 表名 ：删除索引

2、主键索引：加速查找+不能为空+不能重复

create table in1(id int auto_increment primary key) :在创建时添加
alter table 表名 add primary key(列名)：后天添加

3、唯一索引：加速查找+不能重复

create table in1(name varchar(32) not null,unique ix_name(name)) :在创建时添加
create unique index 索引名称 on 表名(列名) ：后天添加
drop unique index 索引名称 on 表名

4、联合索引（联合唯一,多列，最左前缀索引（从左向右依次循序））：

  联合主键索引：
        alter table 表名 add primary key(列名1,列名2)：后天添加
  联合唯一索引
        create unique index 索引名称 on 表名(列名1,列名2)：后天添加
  联合普通索引
        create index 索引名称 on 表名(列名1,列名2)：后天添加

5、覆盖索引：在索引文件中直接获取数据

select id from student where id=12;

6、索引合并：把多个单列索引合并使用

 select id from student where id=12 where name='xiao';

7、加速查找：
假设：

id    name    email
......

无索引：从前到后依次查找
索引：

    create index ix_name on userinfo3(email);
    id:创建额外文件（某种格式存储）
    name:创建额外文件（某种格式存储）

8、索引种类：
-------------hash索引：索引表，取单值快，取范围慢
--------------btree索引：二叉树
创建索引：
----额外的文件保存特殊的数据结构
—查询快；插入更新删除慢
—命中索引：一般避免（!=,like,>,<,oder by,or）

  create index ix_name on userinfo3(email(10)) #以email的前10个字符做索引

9、时间：
执行计划：

   explain select * from userinfo; #让mysql来预估执行操作
    type:
    ALL(全表扫描)
    REF(走了索引,查一个或多个值)
    INDEX(全索引扫描)
    RANGE(对索引列进行范围查找)
    EQ_REF(对主键进行索引)

10、DBA工作：show variables like ‘%quer%’
慢日志
------执行时间
-----未命中索引
-----日志文件路径
--------set global slow_query_log=ON(打开全局慢日志)
--------set global slow_query_log_file=D:/..(指定慢日志文件路径)
配置文件：（注意：修改配置文件之后，需要重启服务）
--------mysqld --defaults-file='D:\config'(指定慢日志路径)

11、分页
1、一般写法

select * from userinfo limit 20,10;

2、方案：
---------结合python代码记录当前页最大id或最小id
---------页面设定只有上一页和下一页
下一页：

select * from userinfo where id > max_id limit 10;

上一页：

  select * from userinfo where id < min_id order by id desc limit 10;

上一页 192 [193]…199 下一页（假设每页十个id）:

      select * from userinfo where id in(
                                     select id from (
                                     select * from userinfo where id > max_id limit
                                     (199-193)*10
                                                     ) as n
                                     order by n.id desc limit 10);