mysql内置功能

1、视图

http://www.cnblogs.com/Jason-lin/p/8653868.html

2、存储过程

简单地说存储过程就是一条或者多条sql语句的集合

存储过程的优点

1、用于替代程序写的SQL语句，实现程序与sql解耦

2、基于网络传输，传别名的数据量小，而直接传sql数据量大

使用存储过程的缺点：

程序员扩展不方便

补充：程序与数据库结合使用的三种方式

#方式一：
    MySQL：存储过程
    程序：调用存储过程

#方式二：
    MySQL：
    程序：纯SQL语句

#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）

将mysql的结束符设置为//

delimiter //

一、创建简单的无参存储过程

create procedure s1()
BEGIN
    select * from student;
    insert into student(sid,sname) values(20, 'test');
END //

在sql中调用

call s1 //

在pymysql中调用

cursor.callproc('s1')
print(cursor.fetchall())

二、创建有参的存储过程

对于存储过程，可以接收参数，其参数有三类：

#in          仅用于传入参数用
#out        仅用于返回值用
#inout     既可以传入又可以当作返回值

仅用于传入参数用 IN

create procedure s2(
 in n1 int,
 in n2 int
)
BEGIN
    select * from student where sid>n1;
END //

OUT

create procedure s3(
 in n1 int,
 out res int
)
BEGIN
    select * from student where sid>n1;
    set res=1;
END //

在mysql中调用

set @res=0//
call s3(3,@res)//
select @res//

在pymysql中调用

cursor.callproc('s3',(3,0) )  #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果

cursor.callproc('s3', (3, 0))
cursor.execute('select @_s3_1;')
print(cursor.fetchone())

删除存储过程

drop procedure s1//

 

3、触发器

触发器是一个特殊的存储过程，不同的是存储过程要使用call语句调用，而触发器是通过设定预定义事件，当这个预定义事件发生时，就会被mysql调用

创建触发器

# 插入前
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

准备表

CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, #提交时间
    success enum ('yes', 'no') #0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

创建触发器

create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if new.success = 'no' THEN
        insert into errlog(err_cmd, err_time) values(new.cmd,new.sub_time);
    end if;
end//

插入数据

INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes')
//

查询

select * from errlog //

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

删除触发器

drop trigger tri_after_insert_cmd;

4、事务

http://www.cnblogs.com/Jason-lin/p/8654590.html