Mysql学习笔记（十）存储过程与函数 + 知识点补充（having与where的区别）

学习内容：
存储程序与函数。。。这一章学的我是云里雾里的。。。

1.存储过程。。。

  Mysql存储过程是从mysql 5.0开始增加的一个新功能.存储过程的优点其实有很多，不过我觉得存储过程最重要的优点就是实现了SQL代码的封装，那么我们为什么需要封装SQL语句呢？原因就是当我们在面对一个庞大的数据库的时候，当我们使用外部程序去访问数据库的时候。。。我们总不能在外部程序中内嵌很多的SQL语句吧。。。那样执行的效率不高，并且也不容易维护...因此存储过程将我们的操作进行封装，当我们需要对其进行操作的时候，我们只需要调用存储过程就可以了...

create procedure cl_add
(
    a int,
    b int
)
begin                 //存储过程的执行过程需要定义在begin。。。。end语句中...
  declare c int;      //声明一个变量。。declare只能使用在存储过程或者函数里面，否则会出错..
  if a is null then   //if 语句 ，用来进行条件判断。。满足条件则执行满足条件的语句..这里的if和if()是不同的。。if()是控制流程函数。。if表示条件判断的语句...二者是不一样的..
     set a=0;
  end if;
  if b is null then
     set b=0;         //set 赋值语句...可以接简单的语句还可以接复杂的函数...
  end if;
  set c=a+b;
  select c as sum;
  /* 
   return c; 这会产生错误...
  */
end;

注：存储过程中不能使用return...return只能使用在函数中...

存储过程的调用...

call cl_add(10,20); //存储过程需要使用call函数来进行调用...

set @a=10; set @b=20; //我们还可以定义两个用户变量...

call cl_add(@a,@b); //将用户变量的值传递过去...

2.存储函数。。。

 

存储函数也是由一个或多个SQL语句组成的，目的是将代码封装以便重新使用...也是为了方便开发人员操作数据库...

 

存 储函数使用的限制：1.不能使用临时表。2.不能在存储函数中定义timestamp，cursor,table的数据类型...3.定义函数时参数只允 许是in类型...4.系统内置了一些存储函数，在调用这些函数的时候...需要加::前缀...并启用allow updates服务器选项，才能将用户自定义的函数的所有者定义为系统类型..

 

 

DELIMITER //

CREATE FUNCTION NameByT()
RETURNS CHAR(50)
RETURN (SELECT NAME FROM t3 WHERE id=2);
//
DELIMITER ;

 

创建存储函数，名称为NameByT，该函数返回SELECT语句的查询结果，数值类型为字符串型...

注意：RETURNS CHAR(50)数据类型的时候，RETURNS 是有S的，而RETURN (SELECT NAME FROM t3 WHERE id=2)的时候RETURN是没有S的

 

3.存储函数和存储过程的区别...

本质上都是实现SQL代码块的封装，方便对数据库的操作...

存储过程和函数存在以下几个区别：

    1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

    2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有 IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的 RETURN语句。

    3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

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

总结：

  用户自定义函数在处理同一数据行中的各个字段时，特别方便有用。虽然这里使用存储过程也能达到查询目的，但是显然没有使用函数方便。而且，即使使用存储过程也无法处理SELECT查询中的同一数据行中的各个字段的运算。因为存储过程不返回值（唯一可以直接返回整型值，虽然没有返回值，但是可以在存储过程中输出参数来完成返回），使用时只能单独调用；而函数却能出现在能放置表达式的任何位置。

4.Mysql也可以使用declare定义条件和存储程序来解决一些问题...这里所说的问题一般就是错误，当我们在处理一些错误的时候，我们可以自定义一个程序来处理错误....

CREATE TABLE t8(s1 INT,PRIMARY KEY(s1))

DELIMITER //
CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END;
//
DELIMITER ;

/* 调用存储过程*/
CALL handlerdemo();

/* 查看调用存储过程结果*/
SELECT @X

  这里我们插入了两次1。。这在主键里是不允许出现的....因此我们使用了DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;这个语句来处理这个错误发生...如果没有这句话。。这段代码就是错误的....

5.知识点有所遗漏,也是今天偶尔发现自己不会的一个知识点。。。

数据库查询语句中的having与where的区别。。。

一个很小的知识点。。。不过比较重要。。。

  一般在sql中，大多数情况下都是使用where，而很少使用having，where和having基本差不多，having子句在查询过程中慢于聚合语句，where子句在查询过程中快与查询语句，因此大多数的情况下都是使用where的。。。

SELECT * FROM `welcome` HAVING id >1 LIMIT 0 , 30
SELECT * FROM `welcome` WHERE id >1 LIMIT 0 , 30

//这两种运行结果是一样的。。。在多数情况下能使用where的时候就尽量不要使用having，。因为where要快于聚合语句。。。having的使用是要弥补where在分组数据判断时的不足。。。比如说下面代码。。。。
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary)>10;
SELECT user, MAX(salary) FROM users GROUP BY user WHERE MAX(salary)>10;
第二种语句就会出现错误，在数据库中where的后面是不允许加带判断性的聚合函数的。。因此如果当我们统计数据的时候使用到了聚合语句...我们就只能使用having了。。如果不用这些关系，那么当然where是首选。。。

再补充几点：

1、SQL标准要求HAVING必须引用GROUP BY子句中的列或用于总计函数中的列。不过，MySQL支持对此工作性质的扩展，并允许HAVING涉及SELECT清单中的列和外部子查询中的列。

2、HAVING子句必须位于GROUP BY之后ORDER BY之前。

3、如果HAVING子句引用了一个意义不明确的列，则会出现警告。在下面的语句中，col2意义不明确，因为它既作为别名使用，又作为列名使 用：mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

标准SQL工作性质具有优先权，因此如果一个HAVING列名既被用于GROUP BY，又被用作输出列清单中的起了别名的列，则优先权被给予GROUP BY列中的列。

4、HAVING子句可以引用总计函数，而WHERE子句不能引用。【这应该是开发者在特定的情况下采用HAVING子句的最大原因】

5、不要将HAVING用于应被用于WHERE子句的条目，从我们开头的2条语句来看，这样用并没有出错，但是mysql不推荐。而且也没有明确说明原因，但是既然它要求，我们遵循就可以了。