MySql的高级使用

一、存储过程

 

1、语法

     create procedure name([IN |OUT |INOUT ]    参数名     数据类形)

     begin

         ......

     end;

     IN：输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回

       OUT：输出参数:该值可在存储过程内部被改变，并可返回

       INOUT：输入输出参数:调用时指定，并且可被改变和返回

 

2、优缺点：

      缺点：

      （1） 较应用程序代码，存储过程的代码效率稍微要差些

      （2）给数据库增加额外压力，其扩展性比应用程序差

      （3）安全性：可能会有安全隐患，比如说加密信息，如果将加密算法放在应用程序，那么攻击需要将数据库和应用程序都击破才能获得数据，如果全部都放在数据库，则只要攻破数据库，那么数据将会泄露

       优点：

        （1）节省带宽和网络延迟

         （2）存储过程能实现较快的执行速度

         （3）存储过程增强了SQL语言的功能和灵活性

 

3、存储过程的变量

       （1）定义局部变量

                DECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 

 

        （2）赋值

                 SET 变量名 = 表达式值 [,variable_name = expression ...] 

        

         （3）用户变量

                   用户变量名一般以@开头 

                  例子:

                     mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); 

                mysql > SET @greeting='Hello'; 
                mysql > CALL GreetWorld( );

 

4、控制语句

     （1）if then else endif

                 ->if parameter=0 then 

                -> update t set s1=s1+1; 
                -> else 
                -> update t set s1=s1+2; 
                -> end if; 

 

      （2）case var

               when  then

               when  then

               else

               end case

               例子:

                 -> case var 

-> when 0 then 
-> insert into t values(17); 
-> when 1 then 
-> insert into t values(18); 
-> else 
-> insert into t values(19); 
-> end case; 
-> end; 

            （3）while ···· end while：

 

4、一些基本命令
      SHOW CREATE PROCEDURE 数据库.存储过程名; 

 

二、触发器

          1、语法

              create trigger triggerName

              after/before insert/update/delete on 表名

              for each row   #这句话在mysql是固定的

              begin

                    sql语句;

 

              end;

 

           2、优缺点

          （1）问题难以排查

          （2）可能导致死锁和锁等待

          （3）更新不一定是原子性的

 

三、游标

       1、游标只能在存储过程中使用

 

        2、语法

1. create procedure test2()  
2. declare o int 0;
3. begin  
4.     declare cursorTest cursor  
5.     for  
6. select *  
7. from allIntersection;
8. open cursorTest;
9. fetch  cursorTest into o;
10. close cursorTest;
11. end;