需要学习的mysql函数

一直都觉得我对mysql了解的还不错，记得应聘的时候还信誓旦旦的和老大说自己精通mysql，现在想想真是大言不惭啊。最近，师父给我分配任务，涉及到一些mysql去读取数据，我心想不就是select * from 表 么。然而，师父把sql文件都发到了我的手中后，我打开一看，OH MY GOD!!!我不是很能看懂。sql里面有变量、有if语句、有左联、有内联、还有我压根没见过的一些函数。唉，又要涨知识了。今天早上挤地铁时顺便再手机上查了查关于mysql的一些函数，现在贴到下边，大家有时间也了解一下哇，省的掉坑~

一、mysql中的日期和时间函数

1.DATE_ADD()函数

DATE_ADD() 函数向日期添加指定的时间间隔。它有两个参数，一个呢是要操作的日期，一个是INTERVAL expr type。expr 参数是要添加的时间间隔，type是时间的类型。
使用格式：DATE_ADD(date,INTERVAL expr type)
举例：假如你有一个表Orders，表中有两个字段，一个是id，一个是时间，如下:

id	name	time
1	lxs	2017-05-26 13:23:44.657

这个时候，你想让这个时间在当前时间的基础上添加两天:
SELECT id,DATE_ADD(time,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
结果如下：

id	OrderPayDate
1	2017-05-28 13:23:44.657

2.DATE_SUB()函数

有时间增加函数就有时间减少的函数：DATE_SUB() 函数从日期减去指定的时间间隔。他的格式同DATE_ADD()一样：DATE_SUB(date,INTERVAL expr type)
举例：对上面的Orders表，如果你想让时间在当前的时间上减少两天：
SELECT id,DATE_SUB(time,INTERVAL 2 DAY) AS SubtractDate
FROM Orders
结果如下：

id	SubtractDate
1	2017-05-24 13:23:44.657

噢，对忘了说，以上两个函数的type类型都有：MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH

3.DATEDIFF()函数

DATEDIFF() 函数返回两个日期之间的天数。
使用格式：DATEDIFF(date1,date2)。其中，date1 和 date2 参数是合法的日期或日期/时间表达式。
注意啊，这里返回的是两个日期之间的天数。天数。天数。重要的事情说三遍！！！也就是说：只有值的日期部分参与计算。
举例: SELECT DATEDIFF('2017-05-26','2017-05-27') AS DiffDate
结果：

DiffDate
1

4.DATE_FORMAT() 函数

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
使用格式： DATE_FORMAT(date,format)

举例：
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
结果如下：
Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243

5.NOW()函数、CURDATE()函数、CURTIME()函数

把这三个函数放在一起为了对比明显一点：
NOW()函数是一个常用函数了，相信大家也都知道：NOW() 返回当前的日期和时间。
CURDATE() 返回当前的日期。
CURTIME() 返回当前的时间。
举个例子就很明白了：
SELECT NOW(),CURDATE(),CURTIME()
结果如下：

NOW()	CURDATE()	CURTIME()
2017-05-26 12:45:34	2017-05-26	12:45:34

6.DATE()函数

DATE() 函数提取日期或日期/时间表达式的日期部分。这也是常用函数了，举个例子建单说明一下：
还是用1中的Orders表，
SELECT name, DATE(time) AS OrderDate FROM Orders WHERE id=1
结果如下：

name	OrderDate
lxs	2017-05-26

7.EXTRACT() 函数

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
使用格式：EXTRACT(unit FROM date)，其中unit的值与DATE_ADD和DATE_SUB中的type取值相同。
举例：SELECT EXTRACT(YEAR FROM time) AS OrderYear,
EXTRACT(MONTH FROM time) AS OrderMonth,
EXTRACT(DAY FROM time) AS OrderDay,
FROM Orders
WHERE id=1

OrderYear	OrderMonth	OrderDay
2017	05	26

二、mysql中的字符串函数

字符串函数是在师父给我的sql中出现较多的，concat和concat_ws 是较为常用的。

A.字符串连接函数

1.concat()

CONCAT(str1,str2,…) 用来连接一个或多个的字符串。返回结果为连接参数产生的字符串。
如有任何一个参数为NULL ，则返回值为 NULL。
注意：
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
SELECT CONCAT(CAST(int_col AS CHAR), char_col)

2.concat_ws()

CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
如连接后以逗号分开：select concat_ws(',','11','22','33');字符串结果为：11,22,33
和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL

3.group_concat ()

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
举例：

mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200   |
|3 | 500   |
+------+------+
6 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，分号分隔

mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500   |
+------+----------------------------------+
3 rows in set (0.00 sec)

以id分组，把去冗余的name字段的值打印在一行，
逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20   |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10   |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

4.repeat()

用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数

 mysql> select repeat('ab',2);

+----------------+
| repeat('ab',2) |
+----------------+
| abab           |
+----------------+

   1 row in set (0.00 sec)

mysql向表中某字段后追加一段字符串:
update table_name set field=CONCAT(field,'',str)
mysql 向表中某字段前加字符串:
update table_name set field=CONCAT('str',field)

B.字符串截取函数

1.从左开始截取字符串

left（str, length）
说明：left（被截取字段，截取长度）
例：
select left(content,200) as abstract from my_content_t

2.从右开始截取字符串

right（str, length）
说明：right（被截取字段，截取长度）
例：
select right(content,200) as abstract from my_content_t

3.截取字符串

substring（str, pos）
substring（str, pos, length）
说明：substring（被截取字段，从第几位开始截取）
substring（被截取字段，从第几位开始截取，截取长度）
例：
select substring(content,5) as abstract from my_content_t
select substring(content,5,200) as abstract from my_content_t
（注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度）

4.按关键字截取字符串

substring_index（str,delim,count）
说明：substring_index（被截取字段，关键字，关键字出现的次数）
例：
select substring_index("www.w3cschool.cn",".",2) as abstract from wiki_user
结果：www.w3cschool
（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）
函数简介：
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

还有一些就是比较简单的数学函数了，大家感兴趣的话可以自己查阅。深刻感觉到好好学习mysql是编程的重要部分啊！大家共勉~

本文参考www.w3cschool.cn