mysql sql 函数_（2.16）Mysql之SQL基础——函数

(2.16)Mysql之SQL基础——函数

关键词：mysql函数，mysql自定义函数，mysql聚合函数，mysql字符串函数，mysql数值函数

1、自定义函数

--(1)一般形式

create function函数名(参数类型 数据类型)returns返回类型beginsql 语句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>0DOset n=num*n;set num=num-1;end while;returnn;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