mysql存储过程等

参考：

文档：

MySQL 5.1参考手册

http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/

游标：

参考文章：

http://blog.youkuaiyun.com/rdarda/article/details/7881648

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Mysql高级：

存储过程，触发器，事务；函数，自定义函数；视图；游标；定时任务；

触发器：---完成；

表的关联操作在数据库中实现；| 计算所需空间在数据库中；

也可以在应用程序中实现，代码放入应用程序事务中；

存储过程：---初步；

表的数据操作在数据库中实现；| 计算所需空间在数据库中；

一般在应用程序中实现；

函数：---初步；

一些计算在数据库中执行；| 计算所需空间在数据库中；

视图：---初步；

创建一些临时数据表在数据库中；| 方便查询代价；

事务：---初步；

并发死锁问题的解决，使用事务；事务内的语句会隔离性执行；

可以加条件，通过commit或rollback ，来实现多条语句的统一执行或不执行；| 计算所需空间在数据库中；

存储语句中需要事务保证完整性；

一般使用应用程序事务；

游标：---初步；

 Mysql提供了只能向前的只读游标，它只能在存储过程中使用。

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；

事件(Event)：---初步；

你可以创建事件，它会在某个特定时间或时间间隔执行一次预先写好的sql语句。

通常的方式是将复杂的sql语句包装到一个存储过程中，然后调用一下就好。

适合事件的任务包括周期性的维护工作、重新建立缓存和汇总表以模拟物化视图，或者保存用于监视和诊断的状态值。

可以使用get_lock()来保证每次只有一个事件在运行；

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

存储过程：procedure

创建：

create procedure p_select() //查询

begin

select * from admin;

end

在navicat的查询--新建查询中创建，运行后，保存到“函数”中，刷新“函数”可以看到；

调用：

查询--新建查询中，call p_select;调用存储过程；

删除：

Drop  procedure  p_select;

----------------------------------------------------------

//统计，返回结果；

drop procedure if exists  p_count;   //先删除；

create procedure p_count (out param1 int)

begin

 select count(*) into param1 from admin;

End

调用，

call p_count(@param1); 

运行，

select @param1;

~返回统计数字；

------------------------------------------------------------------

存储程序中的变量：

1. declare局部变量

declare仅被用在begin ... end复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

DECLARE var_name[,...] type [DEFAULT value]

declare @strtemp varchar(1000)

2. 变量set语句

SET var_name = expr [, var_name = expr]

set @strtemp = '<(select min'

3. select ... into语句

SELECT col_name[,...] INTO var_name[,...] table_expr

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

示例：

create procedure sp2 (x varchar(5))

begin

    declare name varchar(5) default 'bob';

    declare newname varchar(5);

    declare xid int;

    

    select name,admin_id into newname,xid    

      from admin where name = name;

    select newname;

select xid;

  End

···数据表只能有一条记录；

运行，

call sp2('ccc'); ---这个ccc好像这是个占位的东西，没实际意义；

~结果，newname--bob， xid--10；(数据表中：name--bbb,admin_id--10)

------------------------------------------------------------------

流程控制：

If：

if search_condition then statement_list

    [elseif search_condition then statement_list] ...

    [else statement_list]

end if

Case:

case case_value

    when when_value then statement_list

    [when when_value then statement_list] ...

    [else statement_list]

end case

or:

case

    when search_condition then statement_list

    [when search_condition then statement_list] ...

    [else statement_list]

end case

Loop：循环；

[begin_label:] loop

    statement_list

end loop [end_label]

Leave：退出； 

leave label

Iterate：再次循环；

iterate label

示例：

create procedure doiterate(p1 int)

begin

  label1: loop

    set p1 = p1 + 1;

    if p1 < 10 then iterate label1; end if;

    leave label1;

  end loop label1;

  set @x = p1;

end

运行：

call doiterate(5);

查看：

select @x;

Repeat：重复； ---直到search_condition为真；

[begin_label:] repeat

    statement_list

until search_condition

end repeat [end_label]

示例：

create procedure dorepeat(p1 int)

 begin

   set @x = 0;

   repeat set @x = @x + 1; until @x > p1 end repeat;

 end

While：重复，直至search_condition 为真

[begin_label:] while search_condition do

    statement_list

end while [end_label]

···

示例：

create procedure dowhile()

begin

  declare v1 int default 5;

  while v1 > 0 do

    ...

    set v1 = v1 - 1;

  end while;

End

---------------------------------------------------

应用程序dao调用存储过程；

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

函数：function 

创建：

create function hello (s char(20)) returns char(50)

return concat('hello, ',s,'!');

使用：

select hello('world');

~输出结果为，Hello, world!

删除：

drop  function if exists hello ;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

触发器：trigger 

trigger ：

create trigger trigger_name trigger_time trigger_event

    on tbl_name for each row trigger_stmt

···

trigger_time：before或after；

trigger_event：insert，update，delete；

使用别名old和new，能够引用与触发程序相关的表中的列

删除：

Drop trigger testref;

示例：

1建测试表：

CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 

  b4 INT DEFAULT 0

);

2建立触发器：

CREATE TRIGGER testref BEFORE INSERT ON test1

  FOR EACH ROW BEGIN

    INSERT INTO test2 SET a2 = NEW.a1;

    DELETE FROM test3 WHERE a3 = NEW.a1;  

    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

  END

···查询窗口运行后，表--设计--触发器中可见；

NEW.a1,将要插入新行的ai列的值；

3添加数据：

INSERT INTO test3 (a3) VALUES 

  (NULL), (NULL), (NULL), (NULL), (NULL), 

  (NULL), (NULL), (NULL), (NULL), (NULL);

···

INSERT INTO test4 (a4) VALUES 

  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

4触发触发器：

INSERT INTO test1 VALUES 

     (1), (3), (1), (7), (1), (8), (4), (4);

---------------------------------------------------

修改：

create trigger bigtable1_tri_luceneStatus_update after update on bigtable1

  for each row begin

    update lucene_status set ls_manage_type=2,ls_status=0 where bigtable1_id = old.id;

  End

删除：

create trigger bigtable1_tri_luceneStatus_delete after delete on bigtable1

  for each row begin

    update lucene_status set ls_manage_type=3,ls_status=0 where bigtable1_id = old.id;

  End

/////////////////////////////////////////////////////////////////////////////////////////////////////////////

视图：

创建：

create [or replace] [algorithm = {undefined | merge | temptable}]

    view view_name [(column_list)]

    as select_statement

    [with [cascaded | local] check option]

修改：

alter [algorithm = {undefined | merge | temptable}]

    view view_name [(column_list)]

    as select_statement

    [with [cascaded | local] check option]

DROP VIEW销毁视图；

SHOW CREATE VIEW显示视图元数据；

示例：基表admin；

create view v as select * from admin

select * from v

/////////////////////////////////////////////////////////////////////////////////////////////////////////////

事务：

start transaction, commit和rollback语法：

innoDb引擎；

事务隔离级别：多版本并发控制；默认可重读隔离级；

start transaction | begin [work]

commit [work] [and [no] chain] [[no] release]

rollback [work] [and [no] chain] [[no] release]

set autocommit = {0 | 1}

start transaction或begin语句可以开始一项新的事务。commit可以提交当前事务，是变更成为永久变更。rollback可以 回滚当前事务，取消其变更。set autocommit语句可以禁用或启用默认的autocommit模式，用于当前连接。

start transaction;

set autocommit = 0;

update lucene_status set ls_status=1 where bigtable1_id = 1903344;

update bigtable1 set book='这条数据不存在' where id = 1903344;

commit; /rollback; 

···

两条语句都会执行或不执行；

结果控制，需要添加条件；

将事务写在存储过程中，进行调用；

create procedure p_tra()

begin

... //事务语句；

set autocommit = 0;

update lucene_status set ls_status=1 where bigtable1_id = 1903344;

update bigtable1 set book='这条数据不存在' where id = 1903344;

commit;

end;

---调用：call p_tra

////////////////////////////////////////////////////////////////////////////////////////////////////////////

游标：

声明光标

DECLARE cursor_name CURSOR FOR select_statement

打开光标

OPEN cursor_name

光标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...

光标CLOSE语句

CLOSE cursor_name

示例：

create procedure curdemo()

begin

  declare done int default 0;

  declare a char(16);

  declare b,c int;

  declare cur1 cursor for select id,data from test.t1;  //光标获得数据；

  declare cur2 cursor for select i from test.t2;

  declare continue handler for sqlstate '02000' set done = 1;  //条件；

 

  open cur1;

  open cur2;

 

  repeat

    fetch cur1 into a, b;

    fetch cur2 into c;

    if not done then

       if b < c then

          insert into test.t3 values (a,b);

       else

          insert into test.t3 values (a,c);

       end if;

    end if;

  until done end repeat;

 

  close cur1;

  close cur2;

end

/////////////////////////////////////////////////////////////////////////////////////////////////////////////

定时任务：

查看当前事件开启状况：

show variables like 'event_scheduler';

开启事件：

set global event_scheduler = 1;

关闭：

set global event_scheduler = 0;

删除事件：

drop event if exists e_test_insert

添加：//if not exists 可以不要

create event  if not exists  e_test_insert

 on schedule every 5 second 

 do insert into admin(name,pwd) values ('ccc','111');

删除数据：

create event e_test_delete

 on schedule at current_timestamp + interval 5 second

 do truncate table admin;

每天定时清空admin表：

create event e_admin

 on schedule every 1 day

 do truncate table admin;

调度执行存储过程：

1），创建存储过程：

create procedure e_call_p ()

begin

 insert into admin(name,pwd) values ('aaa','aaa');

End

···

普通调用：call e_call_p;   ---会添加一条记录到数据库；

2），事件调度执行存储过程；

create event if not exists e_called_p

 on schedule every 5 second

 on completion preserve

 do

 call e_call_p;

////////////////////////////////////////////////////////////////////////////////////////////////////////////

建立存储过程：插入30w测试数据；

---mysql保存在函数中；

DROP PROCEDURE dowhile; 

BEGIN  

    DECLARE i int DEFAULT 0;  

    START TRANSACTION;--定义事务  

          WHILE i<900000 DO  

                INSERT INTO bigTable1(name,title,food,book)

                        VALUES(CONCAT('陈鹏',i),CONCAT('爱好',i),CONCAT('苹果，西安的话在华润万家买陕北的苹果好吃，上海的话在沃尔玛买山东的苹果好吃，怪的！',i),CONCAT('文学，好的书《道德经》，然后的《沉思录》，然后的《庄子》，都是好的东西！',i));  

        set i=i+1;  

          END WHILE; 

    COMMIT;--开始执行事务 

END

···

call dowhile;--调用过程