mysql高级包含索引建立优化_函数_存储过程_触发器_及游标

Mysql 高级部分

 

（1）索引（index）1

（2）视图（view）2

（3）触发器（trigger）6

（4）游标（cursor）8

（5）事务(Transaction)10

（6）存储过程（Stored Procedure）12

 

（1） 索引（index）

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

优点：
   大大加快数据的检索速度;
   创建唯一性索引，保证数据库表中每一行数据的唯一性;
   加速表和表之间的连接;
   在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：
    索引需要占物理空间
    当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

更好的理解索引的提示：

ü 如果经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，可以创建索引，以提高查询的速度。

ü 索引是与表关联的可选结构。

ü 通过有目的的创建索引，可以加快对表执行SELECT语句的速度。

ü 不管索引是否存在，都无需修改任何SQL语句的书写方式。索引只是一种快速访问数据的途径，它只影响查询执行的效率。

ü 可以使用CREATE INDEX命令在一列或若干列的组合上创建索引。

ü 创建索引时，将获取要创建索引的列，并对其进行排序。然后，将一个指针连同每一行的索引值存储起来，组成键值对（目录名和页码）。使用索引时，系统首先通过已排序的列值执行快速搜索，然后使用相关联的指针值来定位具有所要查找值的行。

ü 一旦创建了索引，MySQL会自动维护和使用它们。

ü 只要修改了数据，如添加新行、更新现有行或删除行， MySQL都会自动更新索引。

ü 但是为表创建过多的索引会降低更新、删除以及插入的性能，因为MySQL还必须更新与该表关联的索引。

索引的分类

Ø 普通索引：这是最基本的索引，它没有任何限制

Ø 唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

Ø 主键索引（通过主键约束间接创建）：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

Ø 组合索引：在表中的多个列上创建的索引。组合索引中列的顺序是任意的，可以是相邻的列，也可以是不相邻的列。

 

索引的创建：

Ø 普通（唯一）索引的创建：

CREATE [UNIQUE] INDEX index_name ON tbl_name(index_col_name,…)
index_col_name: col_name[(length)][ASC|DESC]
对于字符类型的列，可以编制“前缀索引”，Length表示按照列的指定长度的字符串索引

Ø 创建组合索引：

CREATE INDEX index_name ON tbl_name(index_col_name1,index_col_name2,..)

基于（列A，列B）两列创建索引：

² 可应用索引的情况：A;AB两列结合;

² 不可用索引的情况：B

² Create index index_name on table_name(列A，列B)；

Ø 其他创建索引的方式：

² 创建表时创建索引：

CREATE TABLE tbl_name

(

                  列的定义，……，

                  INDEX|KEY [idx_name](index_col_name)

);

Eg:

CREATE TABLE t1

(tid int primary key, #既创建约束，又创建索引

 tname varchar(20),

 index idx_tname(tname), #创建一个普通索引

 tbirthday date

);

² 修改表时创建索引:

ALTER TABLE tbl_name ADD INDEX|KEY [idx_name](indxe_col_name);

Eg:

ALTER TABLE t1 ADD KEY (tbirthday);

Ø 查看索引：Show index|keys from表名；（SHOW KEYS FROM t1;）

Ø 删除索引：drop index索引名 on表名。（drop index c on t3;）

 

（2） 视图（view）

从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。视图是一个虚拟表，其内容由查询定义。

 

 

 

概述:

ü  视图以经过定制的方式显示来自一个或多个表的数据

ü 视图是一种数据库对象，用户可以象查询普通表一样查询视图。

ü 视图内其实没有存储任何数据，它只是对表的一个查询。

ü 视图的定义保存在数据字典内。创建视图所基于的表为“基表”。

ü 视图一经定义以后，就可以像表一样被查询、修改、删除和更新

作用：

ü 简化数据查询语句

ü 使用户能从多角度看到同一数据

ü 提高了数据的安全性

ü 提供了一定程度的逻辑独立性

ü 减少带宽流量、优化后还可提高执行效率

优点：

ü 提供了另外一种级别的表安全性

ü 隐藏的数据的复杂性

ü 简化的用户的SQL命令

ü 通过重命名列，从另一个角度提供数据

视图的创建：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEWview_name [(column_list)]

    AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

说明：

l OR REPLACE：给定了OR REPLACE子句，语句能够替换已有的同名视图。

l ALGORITHM：可选的mysql算法扩展，算法会影响MySQL处理视图的方式。有以下三个值：

        UNDEFINED--MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

        MERGE--会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

         TEMPTABLE--视图的结果将被置于临时表中，然后使用它执行语句。

l veiw_name:视图名。

l column_list：要想为视图的列定义明确的名称，列出由逗号隔开的列名。column_list中的名称数目必须等于SELECT语句检索的列数。若使用与源表或视图中相同的列名时可以省略column_list。

l select_statement: 用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图。但对SELECT语句有以下的限制：

1. 定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）权限；

2. 在定义中引用的表或视图必须存在；

l WITH [cascaded|local] CHECK OPTION: 在关于可更新视图的WITH CHECK OPTION子句中，当视图是根据另一个视图定义的时，LOCAL和CASCADED关键字决定了检查测试的范围。LOCAL关键字对CHECK OPTION进行了限制，使其仅作用在定义的视图上，CASCADED会对将进行评估的基表进行检查。如果未给定任一关键字，默认值为CASCADED。WITH CHECK OPTION指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。

l 视图定义服从下述限制：

ü SELECT语句不能包含FROM子句中的子查询。

ü SELECT语句不能引用系统或用户变量。

ü SELECT语句不能引用预处理语句参数。

ü 在存储子程序内，定义不能引用子程序参数或局部变量。

ü 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。

ü 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

ü 在视图定义中命名的表必须已存在。

ü 不能将触发程序与视图关联在一起。

修改视图：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

    VIEW view_name [(column_list)]

    AS select_statement

    [WITH [CASCADED | LOCAL] CHECK OPTION]

说明：

该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。该语句需要具有针对视图的CREATE VIEW和DROP权限，也需要针对SELECT语句中引用的每一列的某些权限。

 

查看视图：

SHOW CREATE VIEWview_name

说明：

该语句给出了1个创建给定视图的CREATE VIEW语句。

 

删除视图：

DROP VIEW [IF EXISTS]

    view_name [,view_name] ...

[RESTRICT | CASCADE]

说明：

l DROP VIEW能够删除1个或多个视图。必须在每个视图上拥有DROP权限。

l 可以使用关键字IF EXISTS来防止因不存在的视图而出错。

l 如果给定了RESTRICT和CASCADE，将解析并忽略它们。

更新视图：

概述：

Ø 视图的使用与表一样，有增删改查四种操作，且语法也与表相同。

Ø 在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE可以统称为“通过视图更新数据”。

Ø 通过视图更新数据有如下限制：

ü 一次只能修改一个底层的基表

ü 如果修改违反了基表的约束条件，则无法更新视图

ü 如果视图中的列不是表中的原始列（如创建视图时使用了连接操作符、聚合函数等），则不能通过视图更新。

视图更新操作：

Ø 可更新的视图：要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。如果视图包含下述结构中的任何一种，那么它就是不可更新的： 

ü 聚合函数；

ü DISTINCT关键字；

ü GROUP BY子句；

ü ORDER BY子句；

ü HAVING子句；

ü UNION运算符；

ü 位于选择列表中的子查询；

ü FROM子句中包含多个表；

ü SELECT语句中引用了不可更新视图；

Ø 插入数据：使用INSERT语句通过视图向基本表插入数据

 

注意：

ü 当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。

ü 对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。

Ø 修改数据：使用UPDATE语句可以通过视图修改基本表的数据

注意：若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

 

Ø 删除数据：使用DELETE语句可以通过视图删除基本表的数据

注意：对依赖于多个基本表的视图，不能使用DELETE语句。

（3） 触发器（trigger）

它是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。

为什么要使用触发器：

ü 可以使用它来检查或预防坏的数据进入数据库。

ü 可以改变或取消INSERT、UPDATE、以及DELETE语句。

ü 可以在一个会话中监视数据改变的动作。

创建触发器：

CREATE [DEFINER = {user | CURRENT_USER }]

 TRIGGER <触发器名称>

{ BEFORE | AFTER }

{ INSERT | UPDATE | DELETE }

ON <表名称>

FOR EACH ROW

<触发的SQL语句>

       说明：

DEFINER：The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name'。

触发器名称：触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象

触发程序的动作时间：BEFORE  AFTER. 可以设置为事件发生前或后.

事件：指明了激活触发程序的语句的类型。可以是下述值之一：

ü INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

ü UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

ü DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

表：触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器，而且必须引用永久性表，不能将触发程序与TEMPORARY表或视图关联起来。

触发间隔：FOR EACH ROW通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

关于旧的和新创建的列的标识
在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识："NEW . column_name"或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。
对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。下面是一个UPDATE中同时使用NEW和OLD的例子。

CREATE TRIGGER t21_au
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD . s1;
SET @new = NEW.s1;
END;

触发的SQL语句：是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。这样，就能使用存储子程序中允许的相同语句。

删除触发器：

DROP TRIGGER [schema_name.]trigger_name

说明：

方案名称（schema_name）是可选的。如果省略了schema（方案），将从当前方案中舍弃触发程序。DROP TRIGGER语句需要SUPER权限。

查询触发器：

SHOW TRIGGERS [{FROM | IN} db_name]

    [LIKE 'pattern' | WHERE expr]

mysql> SHOW TRIGGERS LIKE 'acc%'G

*************************** 1. row ***************************

             Trigger: ins_sum

               Event: INSERT

               Table: account

           Statement: SET @sum = @sum + NEW.amount

              Timing: BEFORE

             Created: NULL

            sql_mode:

             Definer: myname@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

  Database Collation: latin1_swedish_ci

· Trigger ：The name of the trigger.

· Event ：The event that causes trigger activation: one of 'INSERT','UPDATE', or'DELETE'.

· Table ：The table for which the trigger is defined.

· Statement ：The statement to be executed when the trigger is activated. This is the same as the text shown in the ACTION_STATEMENT column ofINFORMATION_SCHEMA.TRIGGERS.

· Timing ：One of the two values'BEFORE' or'AFTER'.

· Created ：Currently, the value of this column is always NULL.

· sql_mode ：The SQL mode in effect when the trigger executes.

· Definer ：The account that created the trigger

 

（4） 游标（cursor）

是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。
  概述：

       游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

       游标实现了对mysql的存储过程中循环读取数据表中的对象的过程。

游标的特性：

Ø READ ONLY 只读，只能取值而不能赋值；

Ø NOT SCROOLABLE 不可回滚，只能顺序读取；

Ø ASENSITIVE 敏感，不能在已经打开游标的表上执行update事务；

游标操作：

声明游标：   DECLAREcursor_name CURSOR FORselect_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

打开游标:    OPENcursor_name

游标FETCH：FETCHcursor_name INTOvar_name [,var_name] ...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。 

关闭游标CLOSE：  CLOSEcursor_name

这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

使用游标的实例：

CREATE PROCEDURE curdemo(tblName VARCHAR(100))

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE b,c INT;

DECLARE cur1 CURSOR FOR SELECT id FROM v_wondyfox;声明游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;

drop view if exists v_wondyfox ;

set @sql=concat("create view v_wondyfox as select * from ", tblName);

PREPARE stmt1  FROM @sql;

EXECUTE stmt1 ;

DEALLOCATE PREPARE stmt1;

OPEN cur1; 打开游标

REPEAT

FETCH cur1 INTO b; 获取游标内容

select b;

UNTIL done END REPEAT;

CLOSE cur1; 关闭游标

 

END

 

 

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

（5） 事务(Transaction)

是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。一个事务可以是一条SQL语句，一组SQL语句或整个程序。

          特性：

事务应该具有4个属性：原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性：

ü 原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。就是说不允许事务部分的执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库的影响！

ü 一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。就拿网上购物来说，你只有既让商品出库又让商品进入顾客的购物车才能构成事务。

ü 隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。如果多个事务并发执行，应象各个事务独立执行一样！

ü 持久性（durability）：持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

          事务处理的两种方法：

ü 用begin,rollback,commit来实现

    begin开始一个事务
rollback 事务回滚
commit 事务确认

ü 直接用set来改变mysql的自动提交模式

   MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
   来实现事务的处理。

 说明：
     MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！一般MYSQL数据库默认的引擎是MyISAM,这种引擎不支持事务！如果要让MYSQL支持事务，可以自己手动修改，方法如下：

1. 修改c:appservmysqlmy.ini文件，找到skip-InnoDB,在前面加上#，后保存文件。
2. 在运行中输入：services.msc,重启mysql服务。
3. 到phpmyadmin中，mysql->show engines;(或执行mysql->show variables like 'have_%'; ),查看InnoDB为YES,即表示数据库支持InnoDB了。
也就说明支持事务transaction了。
4. 在创建表时，就可以为Storage Engine选择InnoDB引擎了。如果是以前创建的表，可以使用mysql->alter table table_name type=InnoDB;
或 mysql->alter table table_name engine=InnoDB;来改变数据表的引擎以支持事务。

 实例：

/*************** transaction--1 *******************/
$conn = mysql_connect('localhost','root','root') or die ("数据连接错误!!!");
mysql_select_db('test',$conn);
mysql_query("set names 'GBK'"); //使用GBK中文编码;
//开始一个事务
mysql_query("BEGIN"); //或者mysql_query("START TRANSACTION");
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错
$res = mysql_query($sql);
$res1 = mysql_query($sql2);
if($res && $res1){
mysql_query("COMMIT");
echo '提交成功。';
}else{
mysql_query("ROLLBACK");
echo '数据回滚。';
}
mysql_query("END");
/**************** transaction--2 *******************/
/*方法二*/
mysql_query("SET AUTOCOMMIT=0"); //设置mysql不自动提交，需自行用commit语句提交
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$sql2 = "INSERT INTO `user` (`did`, `username`, `sex`) VALUES (NULL, 'test1', '0')";//这条我故意写错
$res = mysql_query($sql);
$res1 = mysql_query($sql2);
if($res && $res1){
mysql_query("COMMIT");
echo '提交成功。';
}else{
mysql_query("ROLLBACK");
echo '数据回滚。';
}
mysql_query("END"); //事务处理完时别忘记mysql_query("SET AUTOCOMMIT=1");自动提交
/**对于不支持事务的MyISAM引擎数据库可以使用表锁定的方法**/

//MyISAM & InnoDB都支持,
/*LOCK TABLES可以锁定用于当前线程的表。如果表被其它线程锁定，则造成堵塞，直到可以获取所有锁定为止。
UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。*/

mysql_query("LOCK TABLES `user` WRITE");//锁住`user`表
$sql = "INSERT INTO `user` (`id`, `username`, `sex`) VALUES (NULL, 'test1', '0')";
$res = mysql_query($sql);
if($res){
echo '提交成功。!';
}else{
echo '失败!';
}
mysql_query("UNLOCK TABLES");//解除锁定

（6） 存储过程（Stored Procedure）

是一组为了完成特定功能的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。经编译后存储在数据库中。存储过程是数据库中的一个重要对象，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，存储过程可由应用程序通过一个调用来执行，而且允许用户声明变量 。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。

为什么要使用存储过程：

ü 存储过程是已经被认证的技术！

ü 存储过程会使系统运行更快！

ü 存储过程是可复用的组件！它是数据库逻辑而不是应用程序。

ü 存储过程将被保存！

存储过程的优点：

Ø 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 

Ø 当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 

Ø 存储过程可以重复使用，可减少数据库开发人员的工作量。 

Ø 安全性高，可设定只有某此用户才具有对指定存储过程的使用权。

存储过程与函数的区别：

Ø 自定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用。 存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。

Ø 执行的本质都一样。只是函数有如只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。

Ø 函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少

Ø 函数限制比较多，比如不能用临时表，只能用表变量。还有一些函数都不可用等等。而存储过程的限制相对就比较少

Ø 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

Ø 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

创建存储过程：

CREATE

    [DEFINER = { user | CURRENT_USER }]

    PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

说明：

ü DEFINER：指明使用存储过程的访问权限。

ü sp_name:存储过程名称。

ü proc_parameter:[ IN | OUT | INOUT ] param_name type

² in：表示向存储过程中传入参数；存储过程默认为传入参数，所以参数in可以省略；

² out：表示向外传出参数； 

² inout：表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程；

² param_name：参数名；

² type：参数的类型，可以为mysql任何合法得数据类型。

² 如果有多个参数，参数之间可以用逗号进行分割。

ü Characteristic：

   LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

² 这个LANGUAGE SQL子句是没有作用的。仅仅是为了说明下面过程的主体使用SQL语言编写。这条是系统默认的。

² 如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”（ DETERMINISTIC），否则就是“非确定”的。默认的就是NOT DETERMINISTIC。

² CONTAINS SQL表示子程序不包含读或写数据的语句。

² NO SQL表示子程序不包含SQL语句。

² READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。

² MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。

² SQL SECURITY特征可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。

² COMMENT子句是一个MySQL的扩展，它可以被用来描述存储程序。这个信息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION语句来显示。存储子程序不能使用LOAD DATA INFILE。

² 特征子句也有默认值，如果省略了就相当于：LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''

ü routine_body: 包含合法的SQL过程语句。可以使用复合语句语法, 复合语句可以包含声明，循环和其它控制结构语句。

修改存储过程：

 ALTER {PROCEDURE} sp_name [characteristic ...]

说明：

ü 这个语句可以被用来改变一个存储程序的特征。必须用ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。

ü 在ALTER PROCEDURE语句中，可以指定超过一个的改变。

删除存储过程：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

显示存储过程：

SHOW CREATE {PROCEDURE} sp_name

似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。

显示存储过程特征：

SHOW {PROCEDURE} STATUS [LIKE 'pattern']

它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。

调用存储过程：

CALL sp_name([parameter[,...]])

调用一个先前用CREATE PROCEDURE创建的程序。

CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。它也“返回”受影响的行数，客户端程序可以在SQL级别通过调用ROW_COUNT()函数获得这个数，从C中是调用the mysql_affected_rows() C API函数来获得。

 

 

存储过程实例（基本的创建、调用、删除语法）：

delimiter //

DROP PROCEDURE IF EXISTS test //    如果存在test则删除

CREATE PROCEDURE test                                         /*存储过程名 */

(IN inparms INT, OUT outparams varchar(32))                       /*输入、输出参数 */

BEGIN                                                           /*语句块头 */

     DECLARE var CHAR(10);                                      /*变量声明 */

     IF inparms = 1 THEN                                         /* IF条件开始*/

         SET var = 'hello';                                        /*赋值 */

     ELSE

        SET var = 'world';

     END IF;                                                     /* IF结束 */

  

     INSERT INTO t1 VALUES (var);                                /* SQL语句 */

     SELECT name FROM t1 LIMIT 1 INTO outparams;

END

//

delimiter ;

call test(1, @out);   /*调用存储过程*/

存储过程的变量：

l   声明变量：

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

这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。局部变量的作用范围在它被声明的BEGIN ... END块内。它可以被用在嵌套的块中，除了那些用相同名字声明变量的块。

l 变量赋值，SET语句：

SET var_name = expr [, var_name = expr] ...

也可以用语句代替SET来为用户变量分配一个值。在这种情况下，分配符必须为:=而不能用=，因为在非SET语句中=被视为一个比较 操作符，如下所示：
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=0,@t2:=0,@t3:=0;
对于使用select语句为变量赋值的情况，若返回结果为空，即没有记录，此时变量的值为上一次变量赋值时的值，如果没有对变量赋过值，则为NULL。

l 变量赋值，SELECT ... INTO语句

SELECT col_name[,...] INTOvar_name[,...]table_expr

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

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

存储过程的语句：

l BEGIN...END复合语句

[begin_label:] BEGIN

      [statement_list]

 END [end_label]

存储子程序可以使用BEGIN ... END复合语句来包含多个语句。statement_list代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。 复合语句可以被标记。除非begin_label存在，否则end_label不能被给出，并且如果二者都存在，他们必须是同样的

l 流程控制结构

Ø IF语句

    IFsearch_condition THENstatement_list   

    [ELSEIF search_condition THENstatement_list] ...   

[ELSE statement_list]

END IF 

statement_list可以包括一个或多个语句。 

举例： 

DELIMITER //

CREATE PROCEDURE p1(IN parameter1 INT)

      BEGIN

      DECLARE variable1 INT;

      SET variable1 = parameter1 + 1;

      IF variable1 = 0 THEN

          INSERT INTO t VALUES (17);

      END IF;

      IF parameter1 = 0 THEN

          UPDATE t SET s1 = s1 + 1;

      ELSE

          UPDATE t SET s1 = s1 + 2;

      END IF;

END; //

DELIMITER ;

 

Ø 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

举例：

CREATE PROCEDURE p2 (IN parameter1 INT)

BEGIN

     DECLARE variable1 INT;

     SET variable1 = parameter1 + 1;

     CASE variable1

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

 

Ø 循环语句

WHILE … END WHILE

LOOP … END LOOP

REPEAT … END REPEAT

GOTO

前三种是标准的循环方式，至于GOTO就如C语言里的GOTO一样，尽量少用！

在循环中还穿插一些循环控制语句，如LEAVE（类似C语言的break）、ITERATE（类似C语言的continue）等。

LEAVE语句

LEAVE label 这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。

ITERATE语句

ITERATE label ITERATE只可以出现在LOOP, REPEAT,和WHILE语句内。ITERATE意思为：再次循环。

WHILE … END WHILE举例：
CREATE PROCEDURE p4 ()

BEGIN

     DECLARE v INT;

     SET v = 0;

     WHILE v < 5 DO

           INSERT INTO t VALUES (v);

           SET v = v + 1;

      END WHILE;

END; //

LOOP … END LOOP举例：

CREATE PROCEDURE p5 ()

BEGIN

     DECLARE v INT;

     SET v = 0;

     loop_label: LOOP

         INSERT INTO t VALUES (v);

         SET v = v + 1;

         IF v >= 5 THEN

             LEAVE loop_label;

         END IF;

    END LOOP;

END; //

[begin_label:] LOOP   

      statement_list

END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个LEAVE语句。

REPEAT … END REPEAT举例：

CREATE PROCEDURE p6 ()

BEGIN

     DECLARE v INT;

     SET v = 0;

     REPEAT

          INSERT INTO t VALUES (v);

          SET v = v + 1;

     UNTIL v >= 5 END REPEAT;

END; //

迭代（ITERATE）语句

CREATE PROCEDURE p7 ()

BEGIN

     DECLARE v INT;

     SET v = 0;

     loop_label: LOOP

         IF v = 3 THEN

             SET v = v + 1;

             ITERATE loop_label;

         END IF;

         INSERT INTO t VALUES (v);

         SET v = v + 1;

         IF v >= 5 THEN

             LEAVE loop_label;

         END IF;

     END LOOP;

END; //

 

存储过程的注释语法：

mysql存储过程可使用两种风格的注释

Ø 双模杠：--，该风格一般用于单行注释

Ø c风格：/*注释内容 */， 一般用于多行注释

存储过程的条件和异常处理程序：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement 

handler_type:   

     CONTINUE  | EXIT  | UNDO

condition_value:   

     SQLSTATE [VALUE] sqlstate_value  | condition_name  | SQLWARNING  | NOT FOUND  | SQLEXCEPTION

 

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。 

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO处理程序类型语句还不被支持。

SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

 

声明自定义条件：

DECLARE condition_name CONDITION FOR condition_value

condition_value:

    SQLSTATE [VALUE] sqlstate_value

举例：

CREATE TABLE test.t (s1 int,primary key (s1));

delimiter //

CREATE PROCEDURE handlerdemo ()

BEGIN

     DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;

     SET @x = 1;

     INSERT INTO test.t VALUES (1);

     SET @x = 2;

     INSERT INTO test.t VALUES (1);

     SET @x = 3;

END;//

delimiter ;

 

存储过程综合实例：（包含事务,参数,嵌套调用,游标,循环等）

drop procedure if exists pro_rep_shadow_rs;  
delimiter |  
----------------------------------  
-- rep_shadow_rs  
-- 用来处理信息的增加，更新和删除  
-- 每次只更新上次以来没有做过的数据  
-- 根据不同的标志位  
-- 需要一个输出的参数，  
-- 如果返回为0，则调用失败，事务回滚  
-- 如果返回为1，调用成功，事务提交  
--  
-- 测试方法  
-- call pro_rep_shadow_rs(@rtn);  
-- select @rtn;  
----------------------------------  
create procedure pro_rep_shadow_rs(out rtn int)  
begin  
    -- 声明变量，所有的声明必须在非声明的语句前面  
    declare iLast_rep_sync_id int default -1;  
    declare iMax_rep_sync_id int default -1;  
    -- 如果出现异常，或自动处理并rollback,但不再通知调用方了  
    -- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉  
    declare exit handler for sqlexception rollback;  
    -- 查找上一次的  
    select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';  
    -- 如果不存在，则增加一行  
    if iLast_rep_sync_id=-1 then  
      insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');  
      set iLast_rep_sync_id = 0;  
    end if;  
      
    -- 下一个数字  
    set iLast_rep_sync_id=iLast_rep_sync_id+1;  
    -- 设置默认的返回值为0：失败  
    set rtn=0;  
      
    -- 启动事务  
    start transaction;  
    -- 查找最大编号  
    select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;  
    -- 有新数据  
    if iMax_rep_sync_id>=iLast_rep_sync_id then  
        -- 调用  
        call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);  
        -- 更新日志  
        update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';  
    end if;  
      
    -- 运行没有异常，提交事务  
    commit;  
    -- 设置返回值为1 
    set rtn=1;  
end;  
|  
delimiter ;  
drop procedure if exists pro_rep_shadow_rs_do;  
delimiter |  
---------------------------------  
-- 处理指定编号范围内的数据  
-- 需要输入2个参数  
-- last_rep_sync_id 是编号的最小值  
-- max_rep_sync_id 是编号的最大值  
-- 无返回值  
---------------------------------  
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)  
begin  
    declare iRep_operationtype varchar(1);  
    declare iRep_status varchar(1);  
    declare iRep_Sync_id int;  
    declare iId int;  
    -- 这个用于处理游标到达最后一行的情况  
    declare stop int default 0;  
    -- 声明游标  
    declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;  
    -- 声明游标的异常处理，设置一个终止标记  
    declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;  
      
    -- 打开游标  
    open cur;  
      
    -- 读取一行数据到变量  
    fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
    -- 这个就是判断是否游标已经到达了最后  
    while stop <> 1 do 
        -- 各种判断  
        if iRep_operationtype='I' then  
            insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;  
        elseif iRep_operationtype='U' then  
        begin  
            if iRep_status='A' then  
                insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;  
            elseif iRep_status='B' then  
                delete from rs0811 where id=iId;  
            end if;  
        end;  
        elseif iRep_operationtype='D' then  
            delete from rs0811 where id=iId;  
        end if;   
          
        -- 读取下一行的数据   
        fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;  
    end while;  -- 循环结束  
    close cur; -- 关闭游标  
 end;  
| 

 

存储过程操作符：

l 算术运算符

+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3;  3
%     取模 SET var6=10%3 ;     1

l 比较运算符

>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN  不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE         简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL  不为空 0 IS NOT NULL True

l 逻辑运算符

与(AND)

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

或(OR) 

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

异或(XOR) 

XOR	TRUE	FALSE	NULL
TRUE	FALSE	TRUE	NULL
FALSE	TRUE	FALSE	NULL
NULL	NULL	NULL	NULL

l 位运算符

|   位或
&   位与
<<  左移位
>>  右移位
~   位非(单目运算，按位取反)

 

 

 

存储过程基本函数：

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类

 

l 字符串类 

CHARSET(str) //返回字串字符集
CONCAT (string2  [,... ]) //连接字串
INSTR (string ,substring )//返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position  [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

mysql> select substring(’abcd’,0,2);
+———————–+
| substring(’abcd’,0,2) |
+———————–+
|                       |
+———————–+
1 row in set (0.00 sec)

mysql> select substring(’abcd’,1,2);
+———————–+
| substring(’abcd’,1,2) |
+———————–+
| ab                    |
+———————–+
1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格 

 

l 数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2  [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number  [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如：
(1)默认变为整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
|           1 |
+————-+
1 row in set (0.00 sec)

mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
|           2 |
+————-+
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
|           1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方

 

l 日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE (  ) //当前日期
CURRENT_TIME (  ) //当前时间
CURRENT_TIMESTAMP (  ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name  FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW (  ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

 

附:可用在INTERVAL中的类型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR