三十、SQL中的函数

函数定义： 将一段代码块封装到一个结构中，在需要执行代码块的时候，调用结构执行即可(代码复用)。

1、系统函数

系统函数： 系统已经定义好的函数，直接调用即可，任何函数都有返回值，所以函数的调用是通过select调用；mysql中，字符串的基本操作单位是字符
常见的字符串函数：

1. substring(@变量名,start,length)：字符串截取函数，截取单位为字符。
   实例：

    //注：“@”符号表示自定义变量，前面已经讲过，见二十七
    //定义全局变量
    set @st = 'renrenlechaoshi';
    //使用substring函数
    select substring(@st,1,3);
    //输出结果为：ren
    这表明substring截取字符串时，最小下标为1，从1开始。
    //以此类推，截取'renren'的函数应为
    select substring(@st,1,6);

2. length(@变量名)：返回字符串的长度
   实例：

    //使用length返回@st的长度
    select length(@st);
    //输出结果为15

3. instr(@变量名,‘字符串’)：判断字符串是否在某个具体的字符串中存在，若存在返回位置，若没找到返回0
   实例：

//调用instr函数再@st字符串中查找'ren'字符串
select instr(@st,'ren');//结果为：1；因为ren字符串在@st字符串中，且其首字母在第一个位置
select instr(@st,'m');//结果为：0；因为@st中没有m字符

4. lpad(原字符串,填充至多长,用来填充的字符串)：左填充，将字符串按照某个指定的填充方式，填充到指定长度(字符为单位)
   实例：

  //将@st填充到18位，用“@”填充
  select lpad(@st,18,'@'); //输出结果：@@@renrenlechaoshi

5. insert(@字符串,开始替换的位置,替换长度,新的用来替换的字符串)：替换，找到目标位置指定长度的字符串，然后替换成指定的字符串
   实例：

  //将@st从1号开始替换，长度为3，用“dad”来替换
  select insert(@st,1,3,'dad'); //输出结果：dadrenlechaoshi

6. strcmp(字符串1,字符串2)：字符串比较,如果1<2，返回-1，1>2，返回1,1=2,返回0
   stcmp函数比较过程解析：
   1. 先取出两个字符串当中的所有的字符
   2. 比较相同位置的两个字符
      1. 如果相同，继续比较下一个字符
      2. 如果第一个字符串当中的字符大于第二个字符串当中的字符，比较结束，返回1
      3. 如果第一个字符串当中的字符小于第二个字符串当中的字符，比较结束，返回-1
      4. 如果比较完了，两个字符串的字符都相同，且字符串1和字符串2中没有剩余字符，返回0
      5. 如果比较完了，两个字符串的字符都相同，但是有某个字符串中还有剩余字符。如果字符串1有剩余字符，返回1,否则，返回-1。

         实例：

    //比较@st和'renrenlechaosh'
    select strcmp(@st,'renrenlechaosh');//输出结果为1，因为@st长度更长
    //比较@st和'renrenlechaoshi'
    select strcmp(@st,'renrenlechaoshi');//输出结果为0
    //比较@st和'rt'的长度
    select strcmp(@st,'rt'); //输出结果为-1,因为t比e大

7. datediff(date1,date2)：返回日期date1和日期date2的差值，其中，date1为较晚的时间，而date2为较早的时间。
8. concat(a,b)：将a和b两个字符串连接起来

2、自定义函数

自定义函数的要素： 函数名，参数列表(实参、形参)、返回值、函数体(作用域)
创建函数的基本语法：

delimiter 自定义结束符
create function 函数名([形参列表]) returns 数据类型  //规定要返回的数据类型
  begin
    函数体;
    return 返回值;  //返回值类型必须是returns指定的数据类型
  end
自定义结束符
delimiter ;

实例： 创建一个自定义函数display()

delimiter $$
create function display() returns int
 begin
  return 100;
 end
$$
delimiter ;

注： 自定义函数和系统函数的调用方式一致。
附： 如果出现“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de”错误，解决方案如下：
set global log_bin_trust_function_creators = 1;
这种修改是会话级的，也就是说下次重启客户端后，需要再次修改；如果要永久生效，需要在mysql的配置文件my.cnf里面修改，将log-bin-trust-function-creators改为1，但需要重启mysql服务端才能生效。

3、函数的查看&删除&修改

函数的查看

查看函数的基本语法： show function status [like ‘pattern’];
实例： 查看所有函数

show function status;

==注：==函数属于指定的数据库，也就是说函数只有在本数据库下才能调用
查看函数的创建语句： show create function 函数名;
实例： 查看自定义函数display的创建语句

show create function display;

函数的删除&修改

函数不能直接修改，只能够先删除掉旧的函数，再创建新的函数。
删除函数的基本语法： drop function 函数名;
实例： 删除上面创建的函数drop function display;

4、函数的参数

参数分为两种： 定义时的参数叫形参，调用时的参数叫实参(实参可以是数值也可以是变量)；
形参： 要求必须指定数据类型
基本语法： function 函数名(形参名字 字段类型) returns 数据类型
实例： 求1到n的和

delimiter $$
create function sum1(n int) returns int
begin
   set @i = 1;  //用来作为累加变量
   set @res = 0; //用来存储结果
   while @i<=n do
      set @res = @res + @i;
      set @i = @i +1;
   end while;
   return @res;
end
$$
delimiter ;

注： 加“@”符号的变量为全局变量，否则为局部变量； 在函数内部使用@定义的变量在函数外部也可以访问；

5、函数中参数作用域问题

mysql中的作用域与js中的作用域完全一样；全局变量可以在任何地方使用，局部变量只能在函数内部使用；两种类型的变量的定义方法如下：

1. 全局变量：使用set关键字定义，使用@符号标志
2. 局部变量：使用declare关键字声明，没有@符号，所有的局部变量的声明必须在函数体开始之前(在函数内部，不能在函数外部直接使用declare定义)
   实例： 定义一个局部变量(名字为i,类型为int,默认值为1)

declare i int default 1;