（2.16）Mysql之SQL基础——函数
（2.16）Mysql之SQL基础——函数
关键词：mysql函数，mysql自定义函数，mysql聚合函数，mysql字符串函数，mysql数值函数
1、自定义函数
-- （1）一般形式 create function 函数名（参数类型 数据类型） returns 返回类型 begin sql 语句 return 返回的数据值 end
-- （2）举例--不带参数的函数
delimiter //
create function fn_test()
retrurns varchar(20)
begin
return 'hello,world';
end //
delimiter ;
举例--带参数的函数
--求num的阶乘
create function fn_jieceng(num int) returns int BEGIN declare n int; set n=1; while num>0 DO set n=num*n; set num=num-1; end while; return n; end ;
-- (3)调用
【1】直接select fn_test();
【2】在存储过程，或者任何表达式中都可以运用它
-- (4)函数的查看
【1】mysql.proc：select * from mysql.proc where db='db_name' and type='function';
【2】show function status like 'fn_name';
【3】show create function fn_test();
【4】information_schema.`routines`：
select * from information.`routines`
where routine_schema='db_name' and routine_type='function';
-- (5)函数的删除
【1】drop function fn_test();
2、聚合函数
【1】max：最大值，适用于任何类型 【2】min：最小值，适用于任何类型 【3】avg：平均值，适用于数字类型 【4】sum：求和，适用于数字类型 【5】count：统计行数 【6】group_concat：将字符串从分组中连接成具有各种选项（如distinct,order by和separator）的字符串（sql server中的for xml path()，oracle中的wmsys.wm_concat）
（1）概念 ，即实现分组后，其他值默认以逗号为分隔符连接到一行
（2）最大长度参数：默认为1024，group_concat_max_len
使用实例；
【1】distinct：去重 【2】order by ：排序 【3】设定分割字符
3、常用字符串函数
【1】concat(st1,str2,'test_str')：将括号内的多个字符串组连成一个字符串（就是类似于oracle中的||，sql server中的+，拼接）
【2】length(str)、char_length(str)：以字节或者字符获取字符串的长度（英文下是一样的，但是UTF8中文一个字符是3个字节，比如 '你好呀',用char_length就是3，用length就是9）
【3】left(str,N)/right(str,N)函数：获取str左边/右边N个字符
【4】replace(str,old_str,new_str)函数：把str中的old_str字符串，替换成new_str字符串
【5】trim(str)：删除str字符串中左右两边的空格，中间不会删除。trim(leading/trailing from 'str')即只删除str前/后的空格。
【6】find_in_set(str1,str2)：在逗号分隔符的字符串列表中找到一个字符串。。例如：find_in_set('b','a,b,c'),结果为2，如果str1不存在于str2中，即返回0；
【7】format(N,D,locale)：格式化数字，例如format(30011.567,2)则结果为30,011.57。
中国默认以3位为一个逗号，来显示数字，但是如果是其他国家则不同。例如德国就可以这么查看： format(30011.567,2)，则显示为，300.11,57
【8】instr(str,find_str)：返回字符串在字符串中第一次出现的位置。例如：instr('hello_world','wo')，结果为7
【9】concat_ws('connect_str','str1','str2','str3')：意思是，str1和st2之间用connect_str字符串连接起来，str2与str3之间也是。忽略字符串中的所有Null（但不忽略空格）
【10】reverse(str)：反转字符串，比如abc变成cba
【11】substring(str,start_pos,get_length)：截取str字符串，从start_post下表开始，获取get_lenth个字符。 也可以写成substring(str, from 4 for 3)，意思是从第4个开始取，取3个字符。也可以写成负数，意味着从倒数第几个字符开始取。
【12】lower(str)/upper(str)：把str字符串所有字符转换成小写/大写
【13】strcmp(str1,str2)：字符串比较~~相等为0，不等为1
【14】repeat(str,N)：返回str重复N次的结构
【15】lpad/rpad(str,n,pad)：用字符串pad对str最左/右边进行填充，直到长度为n为止。
4、数值函数
【1】ABS(X)：绝对值
【2】ceil(x.y)：向上取整，得x+1
【3】floor(x.y)：向下取整，得x
【4】mod(x,y)：求模，等价于x%y
【5】round(x,y)：返回参数X的四舍五入有y位小数的值，例如round(5.555,2) -> 5.56
【6】truncate(x,y)：返回参数x，截断为y位小数的结果，例如round(5.555,2) -> 5.55
5、日期函数
6、流程函数
【1】IF(EXPR,T,F)：如果表达式expr为真，则返回T值，否则返回F值
【2】IFNULL(value,value1)：如果value为null，则返回value1值
7、常用函数
6.1、LAST_INSERT_ID()：获取自增长的最后插入值ID
