mysql-函数

1、 数学函数

**绝对值函数 abs（x）**
功能：得到x的绝对值
sql语句：select abs(3),abs(-3.3),abs(-33);
sql结果：3，3.3，33

**格式化函数format（x,n）**
功能：将x以四舍五入的方式保留到小数点后n位。
sql语句：select format(12332.123456,4),format(12332.1,4),format(12332.2,0);
sql结果：12332.1235， 12332.1000， 12332

**圆周率函数 pi（）**
功能：获得圆周率
sql语句：select pi();
sql结果：3.141593 默认显示的小数位数是6位

**求次方函数 POWER(x,y)**
功能：求 x的y次方
sql语句：select power(2，3)
sql结果：8

**平方根函数 sqrt（x）**
功能：得到x的平方根
sql语句：select sqrt(9),sqrt(40),sqrt(-49);
sql结果：3 ，6.324555.....，null（负数没有平方根所以为null）

**求余函数 mod（x，y）**
功能：得到 x/y的余数
sql语句：select mod(31,7),mod(234,10),mod(45.5,6);
sql结果：7，4，3.5

**获取整数的函数 ceil（x），ceiling（x），floor（x）**
功能：ceil和ceiling返回一个不小于x的最小整数
sql语句：select ceil（-3.35）,ceiling(3.35);
sql结果：-3，4

功能：floor返回不大于x的最大整数
sql语句：select floor(-3.35), floor(3.5);
sql结果：-4，3

**获取随机数的函数rand（）和rand（x）**
功能：
rand（）每次产生的随机数不同
rand（x）如果x相同，则产生的随机数相同

**四舍五入的函数round（x），round（x,y）**
功能：四舍五入后只保留了整数部分
sql语句：select round(-1.14),round(-1.61),round(1.32),round(1.61);
sql结果：-1，-2， 1， 2

功能： round（x,y）y为正值保留小数点后y位，若y是负值则保留到小数点左边y位
sql语句：select round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);
sql结果：1.4， 1， 230， 200

**符号函数sign(x)**
功能：
x的值为正数，零，负数时返回 1， 0 ， -1

2、字符串函数

**计算字符串字符个数和字符串长度的函数char_length(str)，length(str)**
功能：
char_length(str)返回str的字符个数
length（str）返回str的字符长度

**合并字符串函数concat（s1，s2，...）,concat_ws(x,s1,s2,....)**
功能：将所有的字符串拼接在一起
sql语句：select concat('mysql','5.7'),concat('my',null,'sql');
sql结果：mysql5.7， null（只要有一个参数是null，结果就是null）

功能：concat_ws(x,s1,s2,....)** 用指定字符x拼接所有字符串
sql语句：select concat_ws('-','1st','2st','3st'),concat_ws('*','1st',null,'3st');
sql结果：1st-2st-3st， 1st*3st （会忽略null）

**替换字符串函数insert（s1，x，len，s2）**
功能：
s1 被 s2 从第x个字符开始替换 len长度， 如果 x超出字符串范围直接返回原字符串

**字母大小写转换**
功能：
lower（str）和lcase（str）将字符串中的所有字符转换为小写
upper（str）和ucase（str）将字符串中的所有字符转换为大写

**获取指定长度字符串的函数left（s,n）和right（s,n）**
功能：left（s,n）从s左侧截取n个字符
sql语句：select left（‘football’,5）;
sql结果：footb

功能：right（s,n）从s右侧截取n个字符
sql语句：select right（‘football’,4）;
sql结果：ball

**填充字符串函数lpad（s1，len，s2）和rpad（s1，len，s2）**
功能：lpad s1左边由s2 填充到指定字符长度len
sql语句：select lpad（'hello',4,'??'）,lpad('hello',10,'??');
sql结果：hell，?????hello（hello字符长度大于4 ，所以只需缩短到4个字符hell ）

功能：rpad s1右边由s2 填充到指定字符长度len
sql语句：select rpad（'hello',4,'??'）,rpad('hello',10,'??');
sql结果：hell，hello?????（hello字符长度大于4 ，所以只需缩短到4个字符hell ）

**删除空格的函数 ltrim(s), rtrim(s), trim(s)**
功能：
ltrim(s) 删除s左侧的空格
rtrim(s) 删除s右侧的空格
trim(s) 删除s两侧的空格

**重复生成字符串函数repeat（s,n）**
功能：重复生成相同的字符串
sql语句：select repeat('mysql',3);
sql结果：'mysqlmysqlmysql'

**空格函数space（n）和替换函数replace（s,s1,s2）**
功能：space（n）返回一个由n个空格组成的字符串，
sql语句：select concat('(', 'space(6)', ')');
sql结果：（      ）

功能：replace（s,s1,s2）使用字符串s2替换字符串s中所有的字符串s1，
sql语句：select replace（‘xxx.mysql.com’,'x','w'）;
sql结果：www.mysql.com

**比较字符串的函数 strcmp（s1，s2）**
功能：字符串相同返回0，若根据当前分类顺序，第一个参数小于第二个，返回-1，其它情况返回1
sql语句：select strcmp('txt',''txt2),strcmp('txt2','txt'),strcmp(txt);
sql结果：-1， 1， 0

**获取子串的函数subString（s,n,len）和 mid(s,n,len)**
功能：subStirng(s,n,len）字符串s 从n开始截取len的长度，mid功能一样
sql语句：select substring('breakfast',5) as col1,substring('breakfast',5,3)as col2,substring('lunch',-3)as col3,substring('lunch',-5,3);
sql结果： kfast , kfa, nch, lun

**匹配子串开始位置的函数locate(str1,str),position(str1 in str),instr(str,str1)**
功能：返回str1 在str中的开始位置
sql语句：select locate('ball','football'),position('ball' in 'football'),instr('football','ball');
slq结果：5， 5 ，5

**字符串逆序的函数 reverse（s）**
功能 ：将字符串s翻转，返回字符的顺序和s字符串顺序的相反
sql语句 select reverse('abc')
sql结果：'cba'

**返回指定位置的字符串elt（n，‘str1’，‘str2’，‘str3’，...）**
功能：若n是1则返回str1，若n是2返回str2，若 n超出了字符串个数，返回null

**返回指定字符串的位置fleld（‘str’，‘str1’，'str2','str3'）**
功能 ：若str等于str1 返回1 ，若str等于 str2 返回2，找不到返回0，若str 是null 则返回null

**返回子串位置的函数find_in_set(s1,s2)**
功能：返回子字符串在字符串列表的位置
sql语句：select find_in_set('Hi','hihi,hey,Hi,bas')
sql结果：3

选取字符串的函数 make_set(x,s1,s2,......)
功能：
sql语句：select 
make_set(1,'a','b','c') as col1,
make_set(1|4,'hello','nice','world')as col2,
make_set(1|4,'hello','nice','null','world') as col3,
make_set(0,'a','b','c') as col4;
sql结果 'a', 'hello,world','hello'

3、日期函数

**获取当前日期的函数curdate()，current_date()，获得系统当前时间的函数curtime()，current_time()**
功能：curdate()，current_date() 将当前日期按照'YYYY-MM-DD'或'YYYYMMDD'的格式返回，具体格式根据函数或字符串中的语境而定
sql语句：select curdate(),current_date() ,curdate()+0;
sql结果：2019-02-21，2019-02-21，20190221

功能：curtime()，current_time() 将当前日期按照'HH:MM:SS'或'HHMMSS''的格式返回，具体格式根据函数或字符串中的语境而定
sql语句：select curtime(),current_time(),curtime()+0;
sql结果：10:21:34，10:21:34，102134

**获取当前日期和时间的函数 current_timestamp()，localtime(),now(),sysdate()**
功能：四个函数的功能相同，格式为'YYYY-MM-DD HH:MM:SS'或'YYYYMMDDHHMMSS'

**unix 时间函数 unix_timestamp(date), from_unixtime(date)**
功能：unix_timestamp(date)，若无参调用返回一个时间戳（1997-01-01 00:00:00 之后到现在的秒数），若有date则返回1997-01-01 00:00:00 到指定dete的秒数

功能：from_unixtime(date) 将时间戳转换为普通格式的时间

**返回utc日期和返回utc时间的函数 utc_date() ,utc_time()**
功能：utc_date() 返回当前utc（世界标准时间）日期值，其格式'YYYY-MM-DD'或者'YYYYMMDD'
功能：utc_time() 返回当前utc（世界标准时间）时间值，其格式'HH:MM:SS'或者'HHMMSS'

**获取月份的函数month（date）和monthname（date）**
sql语句：select month('2016-02-13')；
sql结果：2

sql语句：select monthname('2016-02-13')；
sql结果：February

**获取星期的函数dayname(d),dayofweek(d),weekday(d)**
功能：
 dayname（d）获得日期对应的星期的英文名称如sunday ，monday
dayofweek(d)返回日期对应一周中的索引，（1是周日，2表示周一，3表示周二,.....）
weekday(d)返回日期对应一周中的索引，（0表示周一，1表示周二，2表示周三.....）

**时间和秒数的转换函数time_to_sec（time），sec_to_time(seconds)**
功能：time_to_sec（time）将时间转换为秒值,sec_to_time(seconds)相反
sql语句：select time_to_sec('23:23:00');
sql结果：84180

**计算日期和时间的函数 date_add()，adddate(),date_sub(),subdate(),addtime(),suntime(),date_diff()**
功能：

DATE_ADD(date,INTERVAL expr unit)

date_add()和adddate()是同义词.
date:起始日期或者起始时间
expr:指定的是一个间隔值,在起始时间中增加或者减少,注意:expr是一个字符串.对于负值间隔,可以以"-"开头
unit:表示的是一个单位,比如,加上的是1天还是一个小时.

1.对某个日期加上n天的操作

mysql> select date_add('2018-06-26',INTERVAL '5' day);
+-----------------------------------------+
| date_add('2018-06-26',INTERVAL '5' day) |
+-----------------------------------------+
| 2018-07-01                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2018-06-26',INTERVAL '-5' day);
+------------------------------------------+
| date_add('2018-06-26',INTERVAL '-5' day) |
+------------------------------------------+
| 2018-06-21                               |
+------------------------------------------+
1 row in set (0.01 sec)

备注:expr是字符串,如果加上的正值的天数,直接'5'即可,如果是减可以用date_sub函数或者expr为负值'-5'.

2.对某个日期加上n小时,n分钟,n秒的操作
 

mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 hour);  #对于日期加上1小时
+-------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL 1 hour) |
+-------------------------------------------------+
| 2018-06-27 00:59:59                             |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 minute);  #对于日期加上1分钟
+---------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL 1 minute) |
+---------------------------------------------------+
| 2018-06-27 00:00:59                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2018-06-26 23:59:59',INTERVAL 1 second);  #对日期加上1秒钟
+---------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL 1 second) |
+---------------------------------------------------+
| 2018-06-27 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

3.对于某个日期加上n分钟n秒

mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND);
+--------------------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND) |
+--------------------------------------------------------------+
| 2018-06-27 00:01:00                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

备注:一次性加上1分钟,一秒.

4.对于某个日期加上n小时n分钟n秒

mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND);
+--------------------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND) |
+--------------------------------------------------------------+
| 2018-06-27 01:01:00                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

备注:单位HOUR_SECOND就是从小时到秒.expr:HOURS:MINUTES:SECONDS

5.对某个日期加上n小时n分钟

mysql> select date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE);
+------------------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE) |
+------------------------------------------------------------+
| 2018-06-27 01:00:59                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

备注:加上1小时1分钟

6.对某个日期加上几天几小时几分钟几秒钟

mysql> select date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND);
+---------------------------------------------------------------+
| date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND) |
+---------------------------------------------------------------+
| 2018-06-29 02:01:00                                           |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

备注:在日期上加上2天2小时2分钟1秒钟

unit(单位)和expr(表达式)对照表:

**将日期和时间格式化的函数date_format(date,format) time_format(time,format)** 

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

sql语句：select date_format('1997-10-04 22:23:00','%W %M,%Y')as col1,
               date_format('1997-10-04 22:23:00','%D,%y，%a，%d，%m，%b，%j')as col2;
sql结果：
Saturday October 1997 , 4th 97 Sat 04 10 Oct 277

**日期格式化GET_FORMAT(VAL_TYPE,FORMAT_TYPE)**

Function Call	Result
GET_FORMAT(DATE,'USA')	'%m.%d.%Y'
GET_FORMAT(DATE,'JIS')	'%Y-%m-%d'
GET_FORMAT(DATE,'ISO')	'%Y-%m-%d'
GET_FORMAT(DATE,'EUR')	'%d.%m.%Y'
GET_FORMAT(DATE,'INTERNAL')	'%Y%m%d'
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,'EUR')	'%Y-%m-%d %H.%i.%s'
GET_FORMAT(DATETIME,'INTERNAL')	'%Y%m%d%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,'EUR')	'%H.%i.%s'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

sql语句：select date_format('2000-10-05 22:23:00',GET_FORMAT(DATE,'USA'));
sql结果：10.05.2000

4、条件判断函数

**if(expr,v1,v2)**
功能：if(expr,v1,v2) 如果表达式expr是true（expr<>0,expr<>null） ,则返回v1，否则返回v2
sql语句：
select 
if(1>2,2,3),
if(1<2,'yes','no'),
if(strcmp('test','test1'),'no','yes');

sql结果：3， yes， no

**ifnull(v1,v2)**
功能：如果v1不是null返回v1，否则返回v2 
sql语句：select ifnull(1,2),ifnull(null,10),ifnull(1/0,'wrong');
sql结果：1，10，‘wrong’

**case函数**
sql 语句：
select 
case 2
when 1 then ‘one’
when 2 then 'two'
when 3 then 'three'
else 'more'
end;
sql 结果： two