Mysql——常用的一些函数

1、判断两个时间的时间差

-- 秒
TIMESTAMPDIFF(SECOND ,time1,time2);     
-- 分
TIMESTAMPDIFF(MINUTE ,time1,time2);  
-- 时
TIMESTAMPDIFF(HOUR ,time1,time2);     
-- 天
TIMESTAMPDIFF(DAY ,time1,time2);     
-- 月
TIMESTAMPDIFF(MONTH ,time1,time2);    
-- 年
TIMESTAMPDIFF(YEAR ,time1,time2); 

2、将每一行的 id拼接起来

GROUP_CONCAT(id SEPARATOR ';')

SELECT cp_id,GROUP_CONCAT(id SEPARATOR ";") AS id FROM z_01 GROUP BY cp_id;

表：z_01

              查询结果： 

3、将字符串中的 某个字符或字符串 替换  成指定的字符串

UPDATE  z_02  SET cp_id=REPLACE(cp_id,";","");

表：z_02  处理前                                表：z_02处理后                    

                        

4、截取字符串

SELECT SUBSTR(cp_id ,5, 4) AS cp_id FROM  z_03;

从第5位截取，截取长度为4；

表：z_03                                                        查询结果

                    

5、获取每一行的最大值，最小值

SELECT GREATEST(num1,num2,num3,num4,num5) as max FROM z_01;

SELECT LEAST(num1,num2,num3,num4,num5) as min FROM z_01;

表: z_01

     查询结果：

 

6、获取每一列的最大值，最小值，平均值

SELECT avg(num1) FROM z_01;

SELECT MAX(num1) FROM z_01;

SELECT MIN(num1) FROM z_01;

表：z_01

 

  查询结果：

 

7、字符串拼接

SELECT *,CONCAT(str1,str2,str3,str4,str5) AS str FROM z_02;

表: z_02                                                                        

     查询结果：

8、将字符串变成大写(小写)

SELECT LOWER('ABC') AS lower,UPPER('abc') AS upper; 

9、去除字符串中的空格

UPDATE z_02 SET 
str2=LTRIM(str1),
str3=RTRIM(str1),
str4=TRIM(str1),
str5=REPLACE(replace(replace(str1,CHAR(10),""),CHAR(13),"")," ",'');

 

1. LTRIM();          去掉字符串左边空格
2. RTRIM();          去掉字符串右边空格
3. TRIM();            去掉字符串左右两边空格
4. CHAR(10);       换行符
5. CHAR(13);        回车符

表：z_02 处理之前

表：z_02 处理之后

10、LPAD(s,len,pad)，RPAD(s,len,pad)

LPAD(s,len,pad) 用字符串pad来对s左侧进行填充，直至长度达到len;

 

LPAD(s,len,pad) 用字符串pad来对s右侧进行填充，直至长度达到len;

 

SELECT id,LPAD(id,5,0) AS L_id,RPAD(id,5,0) AS R_id FROM z_02;

11、数字函数

1. CEIL(n) 返回大于n最小的整数
2. FLOOR(n) 返回小于n最大的整数
3. RAND() 返回0到1的随机数
4. ROUND(n,y) 将n四舍五入保留y位小数
5. TRUNCATE(n,y) 将n保留y位小数

SELECT CEIL(12.01),FLOOR(12.01),ROUND(12.6548,1) ,RAND(),TRUNCATE(12.1666,2); 

查询结果：

12、日期函数

（1）NOW() 返回当前日期时间

（2）CURDATE() 返回当前日期

（3）CURTIME() 返回当前时间

（4）WEEK(date) 返回日期为一年中的周

（5）YEAR(date) 返回日期中的年

（6）DATE_FORMAT(s,pattern) 格式化日期

   格式化参数：

%y  表示两位数字年份。例如：（2017返回17）

%Y 表示四位数字年份。例如：（2017返回2017）

%m 表示两位数字月份。例如：（01,02，....，12）

%c  表示数字的月份。例如：（1,2,3,4.....,12）

%M 表示月明，英文名称。

%d  表示两位数字的天数。例如：（01,02,03,.....31）

%e 表示数字的天数。例如（1,2,3,4,.....,31）

%H 表示两位数字的小时数，24小时制。例如：（01,02，.....，24）

%i  表示两位数字的分钟数。例如：（01,02...,60）

%S %s 表示两位数字的秒数。例如：（01,02...,60）

（7）DATE_ADD(d,INTERVAL y type) 给日期加上指定类型y值

例如：select DATE_ADD('2017-06-07',Interval 1 month) from dual;

输出：2017-07-07

Type参数：

YEAR 表示年

MONTH 表示月

DAY 表示日

HOUR 表示小时

MINUTE 表示分钟

SECOND 表示秒