MySQL常用函数

MySQL常用函数表

数学函数	作用
ABS(x)	返回x的绝对值
CEIL(x),CEILING(x)	返回大于或者等于x的最小整数
FLOOR(x)	返回小于或者等于x的最大整数
RAND()	返回0~1的随机数
RAND(x)	返回0~1的随机数，x值相同时返回的随机数相同
SIGN(x)	返回x的符号，x是负数、0、正数分别返回-1、0和1
PI()	返回圆周率
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值
ROUND(x)	返回离x最近的整数
ROUND(x,y)	保留x小数点后y位的值，但截断时要进行四舍五入
POW(x,y),POWER(x,y)	返回x的y次方
SQRT(x)	返回x的平方根
EXP(x)	返回e的x次方
MOD(x,y)	返回x除以y以后的余数
LOG(x)	返回自然对数(以e为底的对数)
LOG10(x)	返回以10为底的对数

常用字符串函数表

字符串函数	作用
CHAR_LENGTH(s)	返回字符串s的位数
LENGTH(s)	返回字符串s的长度
CONTACT(s1,s2,…)	将字符串s1,s2等多个字符串合并为一个字符串
CONTACT_WS(x,s1,s2,..)	同CONTAC(S1,S2,…)函数，但是每个字符串要直接加上x
INSERT(s1,x,len,s2)	将字符串s2替换s1的位置开始长度为len的字符串
UPPER(s),UCASE(s)	将字符串s的所有字母都变成大写字母
LOWER(s),LCASE(s)	将字符串s的所有字母都变成小写字母
LEFT(s,n)	返回字符串s前n个字符
RIGHT(s,n)	返回字符串s后n个字符
LPAD(s1,len,s2)	字符串s2来填充s1的开始处，使字符串的长度达到len
RPAD(s1,len,s2)	字符串s2来填充s1的结尾处，使字符串的长度达到len
LTRIM(s)	去掉字符串s开始处的空格
RTRIM(s)	去掉字符串s结尾处的空格
TRIM(s)	去掉字符串s开始处和结尾处的空格
TRIM(s1 FROM s)	去掉字符串s开始处和结尾处的字符串s1
REPEAT(s,n)	将字符串s重复n次
SPACE(n)	返回空格n次
REPLACE(s,s1,s2)	用字符串s2代替字符串s中的字符串s1
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,n,len)	获取从字符串s中第n个位置开始长度为len的字符串
MID(s,n,len)	同SUBSTRING(s,n,len)
LOCATE(s1,s),POSITION(s1 IN S)	从字符串s中获取字符串s1的开始位置
INSTR(s,s1)	从字符串s中获取字符串s1的开始位置
REVERSE(s)	将字符串s的顺序反过来
ELT(n,s1,s2,…)	返回第n个字符串
FIELD(s,s1,s2,…)	返回第一个与字符串s匹配的字符串的位置
FIND_IN_SET(s1,s2)	返回在字符串s2中与字符串s1匹配的字符串的位置
MAKE_SET(x,s1,s2,…)	按x的二进制数从s1，s2，…，sn中选取字符串

日期和时间函数

日期和时间函数	作用
CURDATE(),CURRENT_DATE()	返回当前日期，如2016-07-30
CURTIME(),CURRENT_TIME()	返回当前时间，如22:13:41
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()	返回当前日期和时间
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(d)	将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d)	把UNIX时间戳的时间转换为普通格式时间
UTC_DATE()	返回UTC日期
UTC_TIME()	返回UTC时间
MONTH(d)	返回日期d中月份值，范围为1~12
MONTHNAME(d)	返回日期d中的月份名称，如January,Febuary
DAYNAME(d)	返回日期d是星期几，如Monday，Tuesday
DAYOFWEEK(d)	返回日期d是星期几，如1是星期一，2是星期二等
WEEKDAY(d)	返回日期d是星期几，如0是星期一，1是星期二等
WEEK(d)	计算日期d是本年的第几周，范围0~53
WEEKOFYEAR	计算日期d是本年的第几周，范围1~54
DAYOFYEAR(d)	计算日期d是本年的第几天
DAYOFMONTH(d)	计算日期d是本月的第几天
YEAR(d)	返回日期d的年份值
QUARTER(d)	返回日期d是第几个季度，范围1~4
HOUR(t)	返回时间t中的小时值
MINUTE(t)	返回时间t中的分钟值
SECOND(t)	返回时间t中的秒钟值
EXTRACT(type FROM d)	从日期d中获取指定值，type指定返回的值，如YEAR，HOUR等
TIME_TO_SEC(t)	将时间t转换为秒
SEC_TO_TIME(s)	将以秒为单位的时间s转换为时分秒的格式
DATEDIFF(d1,d2)	计算日期d1~d2之间相隔的天数
SUBDATE(d,n)	计算起始日期d减去n天的日期

MySQL系统函数

系统函数	作用
VERSION()	返回数据库的版本号
CONNECTION_ID	返回服务器的连接数
DATABASE(),SCHEMA()	返回当前数据库名
USER(),SYSTEM_USER(),SESSION_USER()	返回当前用户
CURRENT_USER()	返回当前用户
CHARSET(str)	返回字符串str的字符集
COLLACTION(str)	返回字符串str的字符排列方式
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENET值

MySQL自定义函数

  除了MySQL自带的内置函数外，我们还可以自己自定义函数。默认的子程序和当前数据库相关联，如果要把一个子程序和数据库相关联起来，可以在创建子程序的时候为其指定数据库名db_name.function_name
创建函数的语句形式如下：

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

  MySQL支持在当前用户下自定义变量，该用户定义的变量不能被其他客户端使用，当用户退出客户端时，该客户端连接的所有变量将被释放。变量的形式为@var_name，变量的申明有两种方式，如下：

mysql> SET @t1=0, @t2=0, @t3=0; #使用set关键字申明变量
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3; #直接使用select，但是要注意给变量赋值时要使用:=这种形式

mysql>DELIMITER $$ #以上把分号转换成双美元符。

一个简单的不带参数的函数

mysql> CREATE FUNCTION myTime()
mysql> RETURNS varchar(30) ##定义返回的参数
mysql> RETURN date_format(now(), "%Y年%m月%d日 %H时%i分%s秒"); 函数体

一个带有参数的函数：

mysql> CREATE FUNCTION MySum(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
mysql> RETURN FLOAT(3, 2)
mysql> RETURN (num1 + num2);

使用BEGIN…END复合语句构造的函数：

mysql> CREATE FUNCTION addUser(name varchar(20), gender varchar(10))
mysql> RETURNS varchar(255)
mysql> BEGIN  ##用来表示SQL语句的开始指导end结束
mysql> INSERT INTO table_name (name, gender) VALUES (name, gender);
mysql> RETURN (SELECT count(*) FROM table_name);
mysql> END

其他详见http://blog.csdn.net/u013457382/article/details/51155412