python实现数据库存储过程_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

)

BEGIN1. 声明如果出现异常则执行{

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 handlerforsqlexception

BEGIN--ERROR

set p_return_code= 1;

rollback;

END;

START TRANSACTION;

DELETEfromtb1;

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,numfromA;

declare CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

open my_cursor;

xxoo: LOOP

fetch my_cursor into row_id,row_num;ifdone 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),inarg int

)

begin1. 预检测某个东西 SQL语句合法性2. SQL =格式化 tpl +arg3. 执行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 (innid 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 DEFAULT1;

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>3000000then

leave ooxx;

END IF;

SET row_num=row_num+1;

end LOOP ooxx;

end//delimiter ;

草稿