mysql函数

函数可以分为：单行函数 和 多行函数。

 

单行函数

所谓的单行函数就是将每条数据进行独立的计算，然后每条数据得到一条结果。

 

1.流程控制函数

case

 

select name, age,
 (
    case sex 
    when 1 then "男"
    when 0 then "女"
    else "火星人"
    end
)
from user;

 

 

if

 

select id, name, course, if(socre<60, "fail", "pass")
from student;

 

 

2. null处理函数

ifnull

 

select ifnull(birthday, "is null birthady") from user;

如果birthday为null，就返回后面的字符串。

 

nullif

 

select nullif(age, 245) from user;

如果age等于245就返回null，不等于就返回age。

 

isnull

 

select isnull(birthday) from user;

判断birthday是否为Null.

 

select if(isnull(birthday), "birthday is null", "birthday is not null") from user;

 

coalsece

 

返回第一个非null的值

select coalesce(null, 1);

 

3.加密函数

md5(str)

 

password(str):单向，不可逆。

 

4.数学函数

abs(x):绝对值

 

greatest 返回最大值

 

select greatest(2,3);

 

least 返回最小值

 

select least(2,3);

 

round(x) round(x,d) 四舍五入

 

select round(1.58); #2

select round(1.298, 1); #1.3

select round(23.298, -1); #20

 

fromat(x,d)

 

将数字x的格式写为“#,###,###.###”,以四舍五入有方式保留小数点后d位。

select format(12332.123456,4);   #结果：12,332.1234

 

5.日期时间函数

获得当前系统日期

 

select curdate();

 

获得当前系统时间

 

select curtime();

 

增加日期或时间

 

select date_add('2010-06-21', interval 2 month);

interval是一个关键字，2 month 2个月，2是数值，month是单位

 

select addDate('2011-2-24', 2);

在前面的日期上加上后面的天数。

 

select date_add('1999-01-01', interval 1 day);   #增加一天

 

select date_add('1999-01-01', interval 1 hour);  #增加一小时

 

格式化日期

 

date_format(date,fmt)

 

select date_format(birthday, '%Y-%m-%d %H:%i:%s') from user;

 

将字符串转换为日期

 

str_to_date(str,fmt)

 

select str_to_date('04/31/2004', '%m/%d/%Y');

 

6.字符串函数

char_length(str) 返回字符串str的长度，单位为字符。

 

length(str) 返回字符串的长度，单位为字节。

 

upper(str) 转换为大写字母。

 

lower(str) 转换为小写字母。

 

left(str, len) 返回从字符串开始的len最左字符。

 

right(str, len) 返回从字符串开始的len最右字符。

 

char() 将整数转换为字符。

 

concat(str1, str2,...) 连接字符串。

 

select concat('My','S','QL'); #结果：MySQL

 

replace(str, from_str, to_str) 替换字符串

 

select replace('www.mysql.com', 'w', 'Ww'); #结果：WwWwWw.mysql

 

reverse(str) 反转字符串

 

instr(str, substr) 返回字符串str中子字符串的第一个出现位置。

 

select instr('Hello World', 'or'); #结果：8

 

trim( [{both | leading | trailing}[remstr] from]  str) 去除前缀后缀

 

select trim('    bar   '); #结果：'bar'

select trim(leading 'x' from 'xxxbarxxx'); 结果：'barxxx'

select trim(both 'x' from 'xxxbarxxx'); 结果：'bar'

select trim(trailing 'xyz' from 'barxxyz'); 结果：'barx'

 

lpad 左填充

 

rpad右填充

 

select rpad('Smith', 10, '*'); #结果：'Smith**********'

 

substring(str,pos), substring(str,pos,len) 子字符串

 

select substring('Sakila',2); #结果：'akila'

select substring('Sakila',-3); #结果：'ila'

select substring('Sakila',-3,2); #结果：'il'

 

7.类型转换函数

cast(val as type)   /         convert(str type)

将传入的参数值转为另一种数据类型

 

类型：binary[(N)], char[(N)],    date, datetime,     decimal,    signed,time,    unsigned [integer]

 

select cast('2000-01-01 02:10:30' as date);

select cast('24.36' as signed); #转换为整型

select cast('100' as decimal); #转换为浮点型

 

convert(str using char_set)

 

将字符串转换编码

select convert('ab中国' using utf8)

 

多行函数

就是多条记录同时计算，得到最终一条结果记录。也成为聚集函数、分组函数，主要用于完成一些统计功能。

 

avg平均值

 

select avg(age) from user;

select avg(distinct age) from user;

 

count记录条数统计

 

select count(*), count(age), count(distinct age) from user;

 

max最大值

 

select max(age), max(distinct age) from user;

 

min最小值

 

select min(age), min(distinct age) from user;

 

sum求和

 

select sum(age), sum(distinct age) from user;

select sum( ifnull(age, 0) ) from user;

 

group by子句

 

1.出现在select列表中的字段 或者 出现在order by后面的字段，如果不是包含在分组分数中，那么该字段必须同时在group by子句中出现。

 

2.包含在group by子句中的字段则不必须出现在select列表中。

 

3.如果没有group by子句，select列表中不允许出现字段（单行函数）与分组函数混用的情况。

 

4.不允许在where子句中使用分组函数，having中才能用。