python 导出mysql 视图_Python9-MySQL-MySQL存储过程-视图-触发器-函数-day45

视图：某个查询语句设置别名，日后方便使用

CREATE VIEW v1 as SELECT * FROM student WHERE sid >10

-创建：

create view 视图名称 as SQL

视图是虚拟的

-修改

alter view 视图名称 as SQL

-删除

drop view 视图名称

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

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW

BEGIN

...

END#插入后

CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW

BEGIN

...

END#删除前

CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW

BEGIN

...

END#删除后

CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW

BEGIN

...

END#更新前

CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END#更新后

CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW

BEGIN

...

END

delimiter//create TRIGGER t1 BEFORE INSERT on studentforeach ROW

BEGIN

INSERT into teacher(tname) VALUES('tim');

END//delimiter ;--INSERT INTO student(gender,class_id,sname) VALUES('女',2,'多长');#NEW 代指新数据，在两张表中插入同样的数据#OLD 代指老数据 在两张表中删除和更新同样的数据

-- delimiter //

-- create TRIGGER t1 BEFORE INSERT on student foreach ROW--BEGIN--INSERT into teacher(tname) VALUES(NEW.sname);-- END //

-- delimiter ;

内置函数：

执行函数-- SELECT CURDATE() #日期

-- SELECT CHAR_LENGTH('st') #字符串长度

-- SELECT CONCAT('tim','ttutu','ssl') #拼接

时间格式化：

SELECT DATE_FORMAT(date,format)

SELECT DATE_FORMAT('2009-10-04', '%W %M %Y');

自定义函数：

delimiter \\

create function f1(

i1 int,

i2 int)

returns int

BEGIN

declare num int;

set num= i1 +i2;return(num);

END \\

delimiter ;

# 存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

# 保存在MySQL上的一个别名---》一坨SQL语句

方式一：

Mysql：存储过程

程序：调用存储过程

方式二：

mysql：

程序：sql语句

方式三：

mysql

程序：类和对象(sql语句)

delimiter //CREATE PROCEDURE p1()

BEGIN

SELECT*FROM student;

INSERT into teacher(tname) VALUES("ct");

END//delimiter ;

call p1()

importpymysql

conn= pymysql.connect(host='127.0.0.1',user='root',password='123',database='homework666',charset='utf8')

cursor=conn.cursor()

cursor.callproc('p1',(12,2))

conn.commit()

result=cursor.fetchall()print(result)

cursor.close()

conn.close()

1. 简单

create procedure p1()

BEGIN

select * from student;

INSERT into teacher(tname) values("ct");

END

call p1()

cursor.callproc('p1')

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

3.参数out

delimiter //

create procedure p3(

in n1 int,

out n2 int

)

BEGIN

set n2 = 123123;

select * from student where sid > n1;

END //

delimiter ;

set @v1=123;

call p3(12,@v1);

SELECT @v1;

importpymysql

conn= pymysql.connect(host='127.0.0.1',user='root',password='123',database='homework666',charset='utf8')

cursor=conn.cursor()

cursor.callproc('p3',(12,2))

r1=cursor.fetchall()print(r1)

cursor.execute('select @_p3_0,@_p3_1')

r2=cursor.fetchall()print(r2)

cursor.close()

conn.close()

#事务：

'''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 ;'''

#游标#1、声明游标#2、获取A表中数据#my_cursor select id,num form A#3、for row_id,row_num in my_cursor:#检测循环是否还有数据，如果无数据#break#insert into B(num) values(row_id+row_num)

'''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;

执行：

call p6()'''

#动态执行SQL(防SQL注入)

'''伪代码

delimiter //

create procedure p7(

in tp1 varchar(225),

in arg int

)

begin

1.预检测某个东西 sql语句的合法性

2.格式化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 ;'''