MYSQL 常用【函数】大全- case 已吸收-待废

 

1. 数学函数 greatest(x1,x2,...,xn)返回集合中最大的值

least(x1,x2,...,xn) 返回集合中最小的值

rand()返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。

truncate(x,y) 返回数字x截短为y位小数的结果

2. 聚合函数(常用于GROUP BY从句的SELECT查询中)

COUNT(col)返回指定列中非NULL值的个数

GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

#group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
select GROUP_CONCAT(score),stuName from grade1 GROUP BY stuName; #以stuName分组，把score字段的值打印在一行，逗号分隔(默认)

 

3. 字符串函数

 

函数	说明
SUBSTRING_INDEX	# substring_index（被截取字段，关键字，关键字出现的次数） -- 按关键字截取字符串 SELECT substring_index('Hn_P00001', 'P', -1); -- 结果是00001 若为-1则是从后往前截取 SELECT SUBSTRING_INDEX('www.yuanrengu.com', '.', 2); # 1.截取第二个“.”之前的所有字符 结果为：www.yuanrengu SELECT SUBSTRING_INDEX('www.yuanrengu.com', '.', -2); # 2.截取倒数第二个“.”之后的所有字符 结果为：yuanrengu.com SELECT SUBSTRING_INDEX('www.yuanrengu.com', 'ha', 1); # 3.如果关键字不存在，则返回整个字符串 结果为：www.yuanrengu.com
char_length(s)	返回字符串s的字符数,SELECT CHAR_LENGTH('你好123') -- 5
length(s)	返回字符串s的长度,SELECT LENGTH('你好123') -- 9
concat(s1,s2)	将字符串s1,s2等多个字符串合并为一个字符串  select concat('h1', ',', 'h2', '-', 'h3');#分隔符不能为null，如果为null，则返回结果为null。
concat_ws(x,s1,s2)	同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x select concat_ws('=', 'h1', 'h2', 'h3'); #分隔符不能为null，如果为null，则返回结果为null。
upper(s)	将字符串s的所有字母变成大写字母
lower(s)	将字符串s的所有字母变成大写字母
left(s,n)	返回字符串s的前n个字符
right(s,n)	返回字符串s的后n个字符
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的字符串  SELECT SUBSTRING('mysql_substring', 5); 　　# _substring(初始位置为1不是0) SELECT SUBSTRING('mysql_substring', 5, 3 ); # _su
reverse(s)	将s字符串反转
load_file(file_name)	读入文件并作为一个字符串返回文件内容
INSERT(s1,x,len,s2)	返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符 select INSERT('abcdefg', 2, 3, 'is') #aisefg
find_in_set(str,strlist)	select find_in_set('b','a,b,c,d'); #2 返回str在字符串集strlist中的序号(任何参数是null则返回 null,如果str没找到返回0,参数1包含","时工作异常)
field(str,str1,str2,str3,...)	select field('ej', 'hej', 'ej', 'hej','foo'); #2 返回str等于其后的第n个字符串的序号(如果str没找到返回0)
elt(n,str1,str2,str3,...)	select elt(4, 'ej', 'heja', 'hej', 'foo');  #4 返回第n个字符串(n小于1或大于参数个数返回null)
load_file(file_name)	update table_name set blob_column=load_file ("/tmp/picture") where id=1;# 读入文件并且作为一个字符串返回文件内容(文件无法找到,路径 不完整,没有权限,长度大于max_allowed_packet会返回null)
strcmp( 'h3', 'h2')	比较大小，前者大返回 1，前者小返回 -1 类似collections的集合比较

3. 日期和时间函数

MySQL根据出生日期计算年龄的五种方法比较

链接：https://www.jianshu.com/p/64b53c26bea1 

方法一

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age

 

方法一，作者也说出了缺陷，就是当日期为未来日期时结果为0，而不是负数；这里使用了5个函数和两个运算符。

方法二

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birthday, '00-%m-%d')) AS age

方法二，解决了方法一为负数的问题，但看起来更复杂；这里使用了6个函数和3个运算符。

看了这篇贴子后，我就晕了，怎么会这么复杂，以前用Sql Server很简单就可以了。我坚信一定有简单高效的方法。很快就找到了根据以上方法改良后的方法。

改良后方法一和方法二

SELECT year( from_days( datediff( now( ), birthdate)));
SELECT YEAR(CURDATE())-YEAR(birthday)-(RIGHT(CURDATE(),5)<RIGHT(birthday,5));

改良后的方法一，少了一个函数和一个运算符，当日期为未来日期时计算结果还是为0；
改良后的方法二，还是6个函数和3个运算符，看起来简单些；取日期的右边五位，当日期格式为‘2013-01-01’时取到的是‘01-01’，没有问题；当日期格式为‘2013-1-1’缩写格式时，取右边的五位取出的是‘3-1-1’，会导致出错。

然后自己根据MYSQL的帮助文档中的日期函数想到了第三种方法:

方法三

SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422) 

取生日和当前日期之前的天数除以一年的实际天数（365天5小时48分46秒），然后取整。这样只用了三个函数和一个运算符就搞定了。

然后，很快在国外网站找到了第四种方法：

方法四

 SELECT  TIMESTAMPDIFF(YEAR, @birthday, CURDATE()) 

这种方法只用了两个函数就搞定了，应该是最佳方法了。

测试了一下以上四种方法，假如当前日期为'2017-1-13'，当生日为‘2013-1-14’时，还差一天就要过生日了，离4岁只 差一天了，结果还是3岁，感觉不是很合理；把方法三改造一下，四舍五入得到方法五：

方法五

SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422) 

这样计算出的年龄离实际的周岁最接近了，但可能方法四是最符合年龄定义的了。

 

 

函数	说明
curdate()或current_date()	返回当前时间的年月日   以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所 处上下文是字符串或数字)     select curdate();         -> '1997-12-15'     select curdate() + 0;    -> 19971215
curtime()或current_time()	返回当前时间的时分秒 以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上 下文是字符串或数字)  mysql> select curtime();     　　-> '23:50:26'     mysql> select curtime() + 0;     -> 235026
now() 或sysdate()   或 current_timestamp()	返回当前时间的日期和时间  以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期 时间(根据返回值所处上下文是字符串或数字)      mysql> select now();        -> '1997-12-15 23:50:26'     mysql> select now() + 0;  -> 19971215235026
unix_timestamp()  unix_timestamp(date)	返回一个unix时间戳(从'1970-01-01 00:00:00'gmt开始的秒 数,date默认值为当前时间)   mysql> select unix_timestamp();     　　　　　　　　　　  -> 882226357     mysql> select unix_timestamp('1997-10-04 22:23:00');  -> 875996580
from_unixtime(unix_timestamp)	以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回时间戳的 值(根据返回值所处上下文是字符串或数字)      mysql> select from_unixtime(875996580);     　　-> '1997-10-04 22:23:00'     mysql> select from_unixtime(875996580) + 0;     　　-> 19971004222300
from_unixtime(unix_timestamp,format)	以format字符串格式返回时间戳的值   mysql> select from_unixtime(unix_timestamp(),'%y %d %m % h:%i:%s %x');     　　-> '1997 23rd december 03:43:30 x'
sec_to_time(seconds)	以'hh:mm:ss'或hhmmss格式返回秒数转成的time值(根据返回值所处上下文是字符串或数字)      mysql> select sec_to_time(2378);     　　-> '00:39:38'     mysql> select sec_to_time(2378) + 0;     　　-> 3938
time_to_sec(time)	返回time值有多少秒     mysql> select time_to_sec('22:23:00');     　　-> 80580     mysql> select time_to_sec('00:39:38');     　　-> 2378
month(d)	返回日期d中的月份值
monthname(d)	返回日期当中的月份名称，如Janyary
dayname(d)	返回日期d是星期几，如Monday,Tuesday
dayofweek(d)	日期d今天是星期几，1星期日，2星期一
from_unixtime(10位时间戳)	将unix时间戳转换为2017-03-24 11:15:05的格式
week(d)	计算日期d是本年的第几个星期，范围是0->53
dayofmonth(d)	计算日期d是本月的第几天
dayofyear(d)	计算日期d是本年的第几天
quarter(d)	返回日期d是第几季节，返回1->4
hour(d)	返回d中的小时值
minute(d)	返回d中的分钟值
second(d)	返回d中的秒钟值
datediff(d1,d2)	计算日期d1->d2之间相隔的天数
adddate(d,n)	计算日期d加上n天的日期
subdate(d,n)	日期d减去n天后的日期

 

对日期时间进行加减法运算  

date_add(date, interval expr type)，给日期【添加】指定的时间间隔。 interval: 间隔

date_sub(date, interval expr type)，给日期【减少】指定的时间间隔。

adddate(date,  interval expr type)
subdate(date,  interval expr type)

 

( adddate()和subdate()是date_add()和date_sub()的同义词,也 可以用运算符+和-而不是函数
date是一个datetime或date值,expr对date进行加减法的一个表 达式字符串type指明表达式expr应该如何被解释
[type值 含义 期望的expr格式]:
　second 　　秒 seconds
　minute 　　分钟 minutes
　hour　　 时间 hours
　day 　　　　天 days
　month　　月 months
　year 　　 年 years
　minute_second 　　分钟和秒 "minutes:seconds"
　hour_minute 　　　　小时和分钟 "hours:minutes"
　day_hour 　　　　　　天和小时 "days hours"
　year_month 　　　　年和月 "years-months"
　hour_second 　　　　小时, 分钟， "hours:minutes:seconds"
　day_minute 天, 　　小时, 分钟 "days hours:minutes"
　day_second 天, 　　小时, 分钟, 秒 "days
hours:minutes:seconds"
　expr中允许任何标点做分隔符,如果所有是date值时结果是一个 date值,否则结果是一个datetime值)
　如果type关键词不完整,则mysql从右端取值,day_second因为缺 少小时分钟等于minute_second)
　如果增加month、year_month或year,天数大于结果月份的最大天 数则使用最大天数)
mysql> select "1997-12-31 23:59:59" + interval 1 second;　　 　　　　　　　　　　

 

　　　　　　-> 1998-01-01 00:00:00
mysql> select interval 1 day + "1997-12-31"; 　　　　　　 　　　　　　　　　　

 

　　　　　　　　　　　　 -> 1998-01-01
mysql> select "1998-01-01" - interval 1 second; 　　　　　　　　　　　　　　　

 

　　　　 -> 1997-12-31 23:59:59
mysql> select date_add("1997-12-31 23:59:59",interval 1 second); 　　　　　　

 

　　　　　　　　　　-> 1998-01-01 00:00:00
mysql> select date_add("1997-12-31 23:59:59",interval 1 day); 　　　　　　　　

 

　　　　　　　　　　-> 1998-01-01 23:59:59
mysql> select date_add("1997-12-31 23:59:59",interval "1:1" minute_second);

 

　　　　　　　　　　 -> 1998-01-01 00:01:00

 

mysql> select date_sub("2000-01-01 00:00:00",interval "1:1:1" day_second);

 

　　　　　　　　　　 ->1999-12-31 22:58:59
mysql> select date_add("1998-01-01 00:00:00", interval "-1 10" day_hour); 　　

 

　　　　　　　　　　-> 1997-12-30 14:00:00
mysql> select date_sub("1998-01-02", interval 31 day); 　　　　　　　　　　

 

　　　　　　　　　　 -> 1997-12-02
mysql> select extract(year from "1999-07-02");

 

　　　　　　　　　　　　　 -> 1999
mysql> select extract(year_month from "1999-07-02 01:02:03"); 　　

 

　　　　　　　　　　　　　　　　 -> 199907
mysql> select extract(day_minute from "1999-07-02 01:02:03"); 　　

 

　　　　　　　　　　　　　　　　 -> 20102

 

 

 7天

 

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

 

近30天

select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)

 

 

 

select * from table where date_sub(curdate(), interval 1 month) <= date(column_time);

 

select * from xxx where out_time >= date_sub( current_date(), interval 3 month) 离开时间为近三个月的记录

 

 

 

select add_date(now(), interval 1 month); -- 当前月份加1
select add_date(now(), interval -1 month); -- 当前月份减1

 

 

 

date_format(date,format)    
　根据format字符串格式化date值  
　(在format字符串中可用标志符:  
　%m 月名字(january……december)    
　%w 星期名字(sunday……saturday)    
　%d 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）    
　%y 年, 数字, 4 位    
　%y 年, 数字, 2 位    
　%a 缩写的星期名字(sun……sat)    
　%d 月份中的天数, 数字(00……31)    
　%e 月份中的天数, 数字(0……31)    
　%m 月, 数字(01……12)    
　%c 月, 数字(1……12)    
　%b 缩写的月份名字(jan……dec)    
　%j 一年中的天数(001……366)    
　%h 小时(00……23)    
　%k 小时(0……23)    
　%h 小时(01……12)    
　%i 小时(01……12)    
　%l 小时(1……12)    
　%i 分钟, 数字(00……59)    
　%r 时间,12 小时(hh:mm:ss [ap]m)    
　%t 时间,24 小时(hh:mm:ss)    
　%s 秒(00……59)    
　%s 秒(00……59)    
　%p am或pm    
　%w 一个星期中的天数(0=sunday ……6=saturday ）    
　%u 星期(0……52), 这里星期天是星期的第一天    
　%u 星期(0……52), 这里星期一是星期的第一天    
　%% 字符% )  
mysql> select date_format('1997-10-04 22:23:00','%w %m %
y');    
　　-> 'saturday october 1997'    
mysql> select date_format('1997-10-04 22:23:00','%h:%i:%
s');    
　　-> '22:23:00'    
mysql> select date_format('1997-10-04 22:23:00','%d %y %a
%d %m %b %j');    
　　-> '4th 97 sat 04 10 oct 277'    
mysql> select date_format('1997-10-04 22:23:00','%h %k %i
%r %t %s %w');    
　　-> '22 22 10 10:23:00 pm 22:23:00 00 6'    
 
time_format(time,format)  
　和date_format()类似,但time_format只处理小时、分钟和秒(其
余符号产生一个null值或0)  

 

4. 条件判断

函数	说明
if(expr,v1,v2)	select if(1 > 0,'正确','错误')
ifnull(v1,v2)	如果v1的值不为NULL，则返回v1，否则返回v2。

 

case语法1:

CASE 
　　WHEN e1 THEN v1
　　WHEN e2 THEN e2
　　...
　　ELSE vn
END

 

case语法2:

CASE expr 
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　...
　　ELSE vn
END