mysql函数一

来源：互联网发布：vb.net excel教程编辑：程序博客网时间：2024/05/01 07:49

一、数学函数
ABS(X)返回x的绝对值
CEIL(X),CEILING(X)返回大于或等于x的最小整数
FLOOR(X)返回小于或等于x的最大整数
RAND()返回0~1的随机数
RAND(X)返回0~1的随机数，x值相同时返回的随机数相同
PI()返回圆周率（3.141593）
ROUND(X)返回离x最近的整数
ROUND(X,Y)保留x小数点后y位的值，但截断时要进行四舍五入
TRUNCATE(X,Y)返回数值x保留到小数点后y位的值，直接进行截断，不进行四舍五入
sign(x)返回x的符号，x是负数返回-1，是0返回0，是正数返回1
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的长度，长度指的是字节数。
例1在gb2312下一个汉字表示两个字节：
mysql> select char_length('你好'),length('你好');
+---------------------+----------------+
| char_length('你好') | length('你好') |
+---------------------+----------------+
| 2 | 4 |
+---------------------+----------------+
1 row in set (0.00 sec)
例2在unicode下1个汉字表示三个字节：
mysql> select char_length('你好'),length('你好');
+-----------------------+------------------+
| char_length('你好') | length('你好') |
+-----------------------+------------------+
| 2 | 6 |
+-----------------------+------------------+
1 row in set (0.00 sec)
concat(s1,s2,...)将字符串s1，s2等多个字符串合并为一个字符串，如果有字符串为null则结果为null
concat_ws(x,s1,s2,...)同concat(s1,s2,...)函数，但是每个字符串之间要加上x，如果有字符串为null，则忽略null
mysql> select concat(a,'ccc',b) from pet3;
+-------------------+
| concat(a,'ccc',b) |
+-------------------+
| 11cccdsf |
| 12cccdsf |
+-------------------+
2 rows in set (0.00 sec)
insert(s1,x,len,s2)将字符串s2替换s1的x位置开始长度为len的字符串
例：
mysql> select insert('lmnopqrs',2,2,'abcdefg');
+----------------------------------+
| insert('lmnopqrs',2,2,'abcdefg') |
+----------------------------------+
| labcdefgopqrs |
+----------------------------------+
1 row in set (0.00 sec)
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
例：
mysql> select lpad('aBC',3,'def');
+---------------------+
| lpad('aBC',3,'def') |
+---------------------+
| aBC |
+---------------------+
1 row in set (0.00 sec)
例：
mysql> select lpad('aBC',10,'def');
+----------------------+
| lpad('aBC',10,'def') |
+----------------------+
| defdefdaBC |
+----------------------+
1 row in set (0.00 sec)
ltrim(s)去掉字符串s开始处的空格
rtrim(s)去掉字符串s结尾处的空格
trim(s)去掉字符串s开始处和结尾处的空格
trim(s1 from s)去掉字符串s中开始处和结尾处的字符串s1
例：
mysql> select trim('ab' from 'ababcccccabccccab');
+-------------------------------------+
| trim('ab' from 'ababcccccabccccab') |
+-------------------------------------+
| cccccabcccc |
+-------------------------------------+
1 row in set (0.00 sec)
repeat(s,n)将字符串s重复n次
space(n)返回n个空格
replace(s,s1,s2)用字符串s2替代字符串s中的字符串s1
strcmp(s1,s2)比较字符串s1和s2，小于返回-1，相等返回0，大于返回1
substring(s,n,len)获取从字符串s中的第n个位置开始长度为len的字符串
mid(s,n,len)同subtring(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个字符串
export_set(x,s1,s2)
field(s,s1,s2,...)返回第一个与字符串s匹配的字符串的位置,如果没有则返回0
find_in_set(s1,s2)返回在字符串s2与s1匹配的字符串的位置，s2必须以逗号分隔
例：
mysql> select find_in_set('bc','a,bc,d,e,b');
+--------------------------------+
| find_in_set('bc','a,bc,d,e,b') |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec)
make_set(x,s1,s2,...)按x的二进制数从s1，s2，...,sn中选取字符串，1表示选取，0表示不选取，第一个字符串对应二进制最低的一位以此类推
例：
mysql> select make_set(1|4,'a','b','c','de');
+--------------------------------+
| make_set(1|4,'a','b','c','de') |
+--------------------------------+
| a,c |
+--------------------------------+
1 row in set (0.03 sec)
注：1|4得到二进制为101，右边第一个1对应a，0对应b，左边第一个1对应c
例：
mysql> select make_set(4,'a','b','c','de');
+------------------------------+
| make_set(4,'a','b','c','de') |
+------------------------------+
| c |
+------------------------------+
1 row in set (0.00 sec)
注：4得到二进制100，1对应c
三、日期和时间函数
curdate(),current()_date()返回当前日期
curtime(),current_time()返回当前时间
now(),current_timestamp(),localtime(),sysdate(),localtimestamp()返回当前日期和时间
unix_timestamp()以unix时间戳的形式返回当前时间
unix_timestamp(d)将日期时间d以unix时间戳的形式返回
from_unixtime(d)把unix时间戳的时间转化为普通格式的时间
utc_date()返回utc日期，utc为世界统一时间
utc_time()返回utc时间
month(d)返回日期d中的月份值，范围是1~12
monthname(d)返回日期d中的月份名称，如january
dayname(d)返回日期d是星期几，如Monday，Tuesday等
dayofweek(d)返回日期d是星期几，范围1~7，1表示星期日，2表示星期一等
weekday(d)返回日期d是星期几，范围0~6，0表示星期一，1表示星期二等
week(d，n)计算日期d是本年的第几个星期，n表示一周的第一天是星期几，0表示星期日，范围是0~53
weekofyear(d)计算日期d是本年的第几个星期，范围是1~53
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,month,day,hour,minute,second等
例：
mysql> select extract(year from now());
+--------------------------+
| extract(year from now()) |
+--------------------------+
| 2015 |
+--------------------------+
1 row in set (0.00 sec)
time_to_sec(t)将时间t转化为秒
sec_to_time(s)将以秒为单位的时间s转换为时分秒的格式
to_days(d)计算日期d与0000年1月1日的天数
from_days(n)计算从0000年1月1日开始，n天后的日期，以上面的互反
datediff(d1,d2)计算日期d1~d2之间相隔的天数
adddate(d,n)计算起始日期d加上n天的日期
adddate(d,interval expr type)计算起始日期d加上一个时间段后的日期
date_add(d,interval expr type)同上
上面的interval expr type表示
expr表达式形式类型含义
YY year 年
MM month 月
DD day 日
hh hour 时
mm minute 分
ss second 秒
YY和MM之间用任意符号隔开 year_month 年和月
DD和hh之间用任意符号隔开 day_hour 日和小时
DD和mm之间用任意符号隔开 day_minute 日和分钟
DD和ss之间用任意符号隔开 day_second 日和秒钟
hh和mm之间用任意符号隔开 hour_minute 时和分
hh和ss之间用任意符号隔开 hour_second 时和秒
mm和ss之间用任意符号隔开 minute_second 分和秒
注：不能组合使用
例：
mysql> select adddate('2009-05-06',interval '-1 -1' year_month);//查询一年零一月前的日期，可以用负数
+---------------------------------------------------+
| adddate('2009-05-06',interval '-1 -1' year_month) |
+---------------------------------------------------+
| 2008-04-06 |
+---------------------------------------------------+
1 row in set (0.00 sec)
subdate(d,n)计算起始日期d减去n天的日期
subdate(d,interval expr type)计算起始日期d减去一个时间段后的日期
addtime(t,n)计算起始时间t加上n秒的时间
subtime(t,n)计算起始时间t减去n秒的时间
date_fromat(d,f)按照下表的表达式f的要求显示日期d
time_fromt(t,f)按照下表的表达式f的要求显示时间t
日期时间格式：
符号含义取值示例
%Y 以4位数字表示年份 2008，2009等
%y 以2位数字表示年份 98，99等
%m 以2位数字表示月份 01，02，...,12
%c 以数字表示月份 1，2，...,12
%M 月份的英文名 January,February,...,December
%b 月份的英文缩写 Jan,Feb,..,Dec
%U 表示星期数，其中sunday是星期的第一天 00~52
%u 表示星期数，其中monday是星期的第一天 00~52
%j 以3位数字表示年中的天数 001~366
%d 以2位数字表示月中的几号 01,02,...,31
%e 以数字表示月中的几号 1,2,...,31
%D 以英文后缀表示月中的几号 1st,2nd,...,
%w 以数字的形式表示星期几 0表示sunday，1表示Monday，...
%W 星期几的英文名 Monday,...,Sunday
%a 星期几的英文缩写 Mon,...,Sun
%T 24小时制的时间形式 00:00:00~23:59:59
%r 12小时制的时间形式 12:00:00AM~11:59:59PM
%p 上午（AM）或下午（PM） AM或PM
%k 以数字表示24小时 0,1,...,23
%l 以数字表示12小时 1,2,...,12
%H 以2位数表示24小时 00,01,...,23
%h,%I 以2位数表示12小时 01,02,...,12
%i 以2位数表示分 00,01,...,59
%S,%s 以2位数表示时 00,01,...,59
%% 标识符% %
例：下面用“Jan 1st 1986”的形式显示日期
mysql> select date_format('2009-10-25','%b %D %Y');
+--------------------------------------+
| date_format('2009-10-25','%b %D %Y') |
+--------------------------------------+
| Oct 25th 2009 |
+--------------------------------------+
1 row in set (0.05 sec)
get_fromat(type,s)根据字符串s获取type类型数据的显示格式
图.get_fromat()函数返回的格式字符串格式：
函数返回的格式字符串
GET_FORMAT(DATE,'EUR') '%d.%m.%Y'
GET_FORMAT(DATE,'USA') '%m.%d.%Y'
GET_FORMAT(DATE,'JIS') '%Y-%m-%d'
GET_FORMAT(DATE,'ISO') '%Y-%m-%d'
GET_FORMAT(DATE,'INTERNAL') '%Y%m%d'
GET_FORMAT(DATETIME,'EUR') '%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'USA') '%Y-%m-%d-%H.%i.%s'
GET_FORMAT(DATETIME,'JIS') '%Y-%m-%d-%H:%i:%s'
GET_FORMAT(DATETIME,'ISO') '%Y-%m-%d-%H:%i:%s'
GET_FORMAT(DATETIME,'INTERNAL') '%Y%m%d%H%i%s'
GET_FORMAT(TIME,'EUR') '%H.%i.%s'
GET_FORMAT(TIME,'USA') '%H:%i:%s %p'
GET_FORMAT(TIME,'JIS') '%H:%i:%s'
GET_FORMAT(TIME,'ISO') '%H:%i:%s'
GET_FORMAT(TIME,'INTERNAL') '%H%i%s'
例：
mysql> select get_format(time,'USA');
+------------------------+
| get_format(time,'USA') |
+------------------------+
| %h:%i:%s %p |
+------------------------+
1 row in set (0.03 sec)
注：GET_FORMAT()函数一般用来为DATETIME类型的数据提供格式字符串,如：
DATE_FORMAT('2014-10-11 23:17:28',GET_FORMAT(DATE,'USA'))函数返回的结果为10.11.2014。这是因为使用GET_FORMAT(DATE,'USA')函数将日期的显示格式设置为了$m.%d.%Y。另外datetime类型的数据格式化没有时间则时间部分为0时0分0秒，没有日期部分则为0年0月0日

0 0