试图索引触发器这些是数据库的什么

 

视图

# 视图也是一张表，但在data文件里只有表结构，没有表数据
# 不建议使用，扩展性差，程序需改变时，依赖的视图也要改变
# 视图牵涉到多张表时，视图中的记录不能修改。
create view course2teacher as select * from course inner join teacher on course.teacher_id = teacher.tid
alter view 
select * from course2teacher

 

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

create trigger tri_before_insert_tb1 before insert on tb1 for each row

上图中，delimiter //将原本 ；号的结束符改为//，语句写完后，又换成 ；结束符。

NEW 表示即将插入的数据行，OLD表示即将删除的数据行。

使用触发器：无法由用户直接调用，而是由于对表的增删改操作被动引发。

删除触发器：drop triggeer tri_after_insert_cmd。

触发器的逻辑尽量在应用程序里写，否则还要找数据库人员修改触发器。

 

函数

内置函数，写在select后面，有char_length()，date_format()等

自定义函数，
create function f1(
　　t1 int,
　　t2 int)
return int
BEGIN
　　declare num int;
　　set num = t1+t2;
　　return num;
END //
delimiter  ;

 

存储过程

# 无参存储过程
delimiter //
create procedure p1()
BEGIN
　　select * from db1.teacher;
END //
delimiter  ;
show create procedure p1;　　# 查看存储过程
 
# 在MySQL中调用
call p1();
# 在Python中调用
cursor.call_proc('p1')
 
# 有参存储过程
delimiter //
create procedure p2(
in n1 int,
in n2 int,
out res int ,     # 用于标识执行结果
inout n3 int)
BEGIN
　　select * from db1.teacher where tid > n1 and tid < n2;
　　set res = 1;　　# res是out伪造的返回值，如果要取select语句返回的表格，需从@_p2_0和@_P2_1中取
　　　　　　　　　 # 为什么有结果集又有out伪造的返回值？
END //
delimiter ;
# 在MySQL中调用
set @x=0  　　# 给session级别的变量设值
call p2(2,4,@x);
select @x;
# 在Python中调用
cursor.call_proc('p2',(2,4,0))　　#@_p2_0 = 2,@_P2_1=4,
拿返回值需再进行一次查询
cursor.execute('select @_p2_0,@_P2_1,@_p2_2')
cursor.fetchone()

 有了存储过程后，真正实现了应用程序和数据库的集成开发。

应用程序和数据库结合的三种方式：

方式一：只需通过网络发存储过程名

　　Python调用存储过程

　　MySQL编写存储过程

方式二：

　　Python编写原生SQL语句

方式三：

　　Python用ORM框架生成SQL语句

 

事务

事务常被用于存储过程中

事务用于将某些操作的多个sql作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

delimiter //
create procedure p4(
　　out status int
)
BIGIN
　　DECLARE exit handler for sqlexception
　　BEGIN
　　　　-- ERROR
　　　　set p_return_code = 1;
　　　　rollback;
　　END
　　start transaction;
　　　　update user set balance = 900 where name='wdb'; # 购买支付100元
　　　　user set balance = 1010 where name='egon' # 商家增加10元
　　commit;
　　--SUCCESS
　　set p_return_code = 2;
END //
delimiter  ;

 

 

通过游标实现循环的存储过程

delimiter //
create procedure p6()
begin
　　declare row_id int;
　　declare row_num varchar(50);
　　declare done int default false;
　　
　　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(num) values(temp);
　　　　end loop xxoo;
　　close my_cursor;
end //
delimiter  ;
 
动态执行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 //
delimiter ;