需要学习的mysql函数

超、凢脫俗 2022-06-15 10:57 154阅读 0赞

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

### 1.DATE\_ADD()函数 ###

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

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

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>OrderPayDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2017-05-28 13:23:44.657</td> 
  </tr> 
 </tbody> 
</table>

### 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  
结果如下：

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>SubtractDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>2017-05-24 13:23:44.657</td> 
  </tr> 
 </tbody> 
</table>

噢，对忘了说，以上两个函数的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  
结果：

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>DiffDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

### 4.DATE\_FORMAT() 函数 ###

DATE\_FORMAT() 函数用于以不同的格式显示日期/时间数据。  
使用格式： DATE\_FORMAT(date,format)  
![bVOhSU_w_557_h_661][]  
![bVOhSK_w_568_h_307][]

举例：  
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()  
结果如下：

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>NOW()</th> 
   <th align="left">CURDATE()</th> 
   <th>CURTIME()</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2017-05-26 12:45:34</td> 
   <td align="left">2017-05-26</td> 
   <td>12:45:34</td> 
  </tr> 
 </tbody> 
</table>

### 6.DATE()函数 ###

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

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>OrderDate</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>lxs</td> 
   <td>2017-05-26</td> 
  </tr> 
 </tbody> 
</table>

### 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

<table style="color:rgb(85,85,85); font-family:&quot;microsoft yahei&quot;; font-size:15px; line-height:35px"> 
 <thead> 
  <tr> 
   <th>OrderYear</th> 
   <th align="left">OrderMonth</th> 
   <th>OrderDay</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2017</td> 
   <td align="left">05</td> 
   <td>26</td> 
  </tr> 
 </tbody> 
</table>

## 二、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 使用一个负值。

[bVOhSU_w_557_h_661]: /images/20220615/b0b7747df12849e4892efece99a51693.png
[bVOhSK_w_568_h_307]: /images/20220615/b118bd3c2e344caeb74fce35269397e4.png
[MySQL]: http://lib.csdn.net/base/mysql