python-day44_MySQL数据库3-存储过程

1，三元运算：if(ISNULL (),0,1)

　　如成真，输出0，否则输出1

2，join 左右连表(多列连表)，union上下连接表（上下多行连表）

　　SELECT sid,sname FROM student

　　union

　　SELECT sid,sname FROM student

　　使用UNION ALL，不去重

3，视图

为某个查询语句设置别名，日后方便重复性使用

创建

create view 视图名称 as SQL

修改

alter view 视图名称 as SQL

删除

drop view 视图名称;

例：

select .. from v1 #创立视图v1
select asd from v1 #使用

4，触发器

当对某张表做：增删改操作时，可以使用触发器自定义关联行为

  delimiter // #命令结束以//结尾，不以;分号结尾
  create trigger t1 BEFORE INSERT on student for EACH ROW #在insert语句执行前触发
  BEGIN
      INSERT into teacher(tname) values(NEW.sname);
      INSERT into teacher(tname) values(NEW.sname);
  END //
  delimiter ;   #命令结束改回以;结尾

create trigger t1 AFTER INSERT on student for EACH ROW #在insert语句执行后触发。还可用于update\delete操作，用于增删改

NEW,代指新数据。OLD,代指老数据

EACH ROW，代表每操作一行，就触发一次

insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根'); #触发了2次

5，函数

内置函数：
执行函数 select CURDATE();

select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m")

自定义函数(有返回值)：

  delimiter \\
      create function f1(
          i1 int,
          i2 int)
      returns int
      BEGIN
        declare num int default 0;
          set num = i1 + i2; #函数中不能出现SQL语句，如select等
        return(num);
      END \\
  delimiter ;

  SELECT f1(1,100); #调用函数

6，存储过程

保存在MySQL上的一个别名 => 一坨SQL语句

用于替代程序员写SQL语句

  方式一：
      MySQL: 编写存储过程
      程序：调用存储过程
  方式二：
      MySQL：
      程序：SQL语句
  方式三：
      MySQL：
      程序：类和对象（SQL语句）

  1)简单
  create procedure p1() #创建存储过程
  BEGIN
      select * from student;
      INSERT into teacher(tname) values("ct");
  END

  call p1() #在mysql中，进行调用这个存储过程
  cursor.callproc('p1') #在python中，调用这个存储过程
  result=cursor.fetchall()

  2)传参数（in,out,inout加上这些对应关键字）
  delimiter //
  create procedure p2(
      in n1 int,
      in n2 int
      )
  BEGIN
      select * from student where sid > n1;
  END //
  delimiter ;

call p2(12,2)
cursor.callproc('p2',(12,2)) #在python中，调用这个存储过程

3)参数 out用法
  delimiter //
  create procedure p3(
      in n1 int,
      inout n2 int
  )
  BEGIN
      set n2 = 123123;
      select * from student where sid > n1;
  END //
  delimiter ;

  set @v1 = 10;    #@是指本次mysql会话的变量，v1是变量名
  call p3(12,@v1)
  select @v1;

7，事务

　　保证操作的原子性

delimiter //
create procedure p4(
    out status int
)
BEGIN
    1. 声明如果出现异常则执行{
        set status = 1;
        rollback;
    }
       
    开始事务
        -- 由秦兵账户减去100
        -- 方少伟账户加90
        -- 张根账户加10
        commit;
    结束
    
    set status = 2;
    
    
END //
delimiter ;

===============================
delimiter \\
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values('seven');
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 2; 
 
  END\\
delimiter ;

8，游标

　　当A表中每一行，以固定的方式，每行进行相同的运算后，再存到另一个表B中，可以使用游标来做。

delimiter //
create procedure p6()
begin 
    declare row_id int; -- 自定义变量1  
    declare row_num int; -- 自定义变量2 
    declare done INT DEFAULT FALSE;
    declare temp int;
    
    declare my_cursor CURSOR FOR select id,num from A;
    declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into row_id,row_num;
            if done then 
                leave xxoo;
            END IF;
            set temp = row_id + row_num;
            insert into B(number) values(temp);
        end loop xxoo;
    close my_cursor;
    
end  //
delimter ;

9，动态执行SQL（防SQL注入）

delimiter //
create procedure p7(
    in tpl varchar(255),
    in arg int
)
begin 
    1. 预检测某个东西 SQL语句合法性
    2. SQL =格式化 tpl + arg 
    3. 执行SQL语句
    
    set @xo = arg;
    PREPARE xxx FROM 'select * from student where sid > ?';
    EXECUTE xxx USING @xo;
    DEALLOCATE prepare prod; 
end  //
delimter ;



call p7("select * from tb where id > ?",9)

===> 以下是实际的存储过程

delimiter \\
CREATE PROCEDURE p8 (
    in nid int
)
BEGIN
    set @nid = nid;    #必须用当前会话变量来操作，赋值给当前会话变量
    PREPARE prod FROM 'select * from student where sid > ?';
    EXECUTE prod USING @nid;    #会话变量会将?进行替换
    DEALLOCATE prepare prod; 
END\\
delimiter ;

动态执行SQL

10，

　　用内部函数，会导致SQL效率不高。可以从程序或架构上，进行优化处理

数据库相关操作：
　　1. SQL语句 *****
　　　　- select xx() from xx ;
　　2. 利用MySQL内部提供的功能

11，

二、数据库
    表： id   name    email   gender
              alex1
    插三百万行数据

作业

delimiter //
CREATE PROCEDURE p10()
begin
    DECLARE usern char(30) DEFAULT "user";
    DECLARE row_num int DEFAULT 1;
    DECLARE mail char(30);
    DECLARE temp CHAR(30);
    DECLARE gen char(10);
    
    ooxx: LOOP
        SET temp=CONCAT(usern,CONCAT(row_num,""));
        SET mail=CONCAT(temp,"@abc.com");
        IF (row_num%2)=0 THEN
            SET gen="女";
        ELSE
            SET gen="男";
        END IF;
        INSERT INTO lab_c(name,email,gender) VALUES(temp,mail,gen);
        IF row_num>3000000 then
            leave ooxx;
        END IF;
        SET row_num=row_num+1;
    end LOOP ooxx;
end //
delimiter ;

草稿

posted @ 2019-07-12 17:27 烟云过眼阅读(164) 评论(0) 编辑收藏举报

刷新页面返回顶部

python-day44_MySQL数据库3-存储过程

公告