MySQL常用函数总结

总结一下在工作中经常用到的mysql常用函数：虽然我更喜欢用excel或者python代码来解决问题，但是有些时候用sql是不可避免的事情，总结一下方便自己以后使用：

Mysql常用函数总结

1. 字符串函数：

LOWER(column/str)    列或字符串转化为小写

UPPER(column/str)     列或字符串转化为大写

CONCAT(column1/str1, column2/str2,…)   将列或者字符串合并

CONCAT_WS(separator, column1/str1, column2/str2,…)  将列或字符串按指定分隔符合并

SUBSTR(str FROM pos FOR len) 将字符串str从位置pos（从1开始）起截取len长度个

LENGTH(str)   返回字符串长度

INSTR(str,substr)  从字符串str中返回substr第一次出现的位置(从1开始)

LPAD(str,len,padstr)/ RPAD(str,len,padstr)  将str字符串左边或者右边以padstr填充到len长度

TRIM([remstr FROM] str)     去除str左右两边remstr，不写默认去除空格

LTRIM(str)/ RTRIM(str)       去除str左右两边的空格

REPLACE(str,from_str,to_str)  将str中的 from_str 替换为 to_str

REVERSE(str)   将str反转后返回

2  数值函数：

       ABS(X)      返回X的绝对值

       CEIL(X)      返回大于等于X的最大整数值

       FLOOR(X)    返回小于等于X的最大整数值

MOD(X,Y)    返回x/y的模数

RAND()      返回0-1之内的随机值

ROUND(X,Y)  返回X的四舍五入保留Y位小数的值，Y不写则四舍五入到整数

TRUNCATE(X,Y)  直接将X截取到保留Y位小数

3  分组函数：

       GROUP  BY  column1, column2,  查询的时候按照某列或者某几列进行分组

分组之后的筛选： 分组之后的筛选使用 HAVING , 分组之前的筛选使用 WHERE

分组之后的排序：

分组之后使用查询出的聚合函数排序可以直接使用ORDER BY 聚合函数列

分组之后使用原列排序：

举例：需求：要查询每个组内的工资的前两名：

SELECT * from test a where (SELECT count(*) from test b where b.group = a.group and a.salary < b.salary) < 2 ORDER BY `group`, salary DESC

结果：

这句sql可以这么理解：

从 test a表中查询 在 test b表中跟a 表中的某条数据是同一组的并且工资比a表中的工资高的数量小于两个的 然后再根据 分组 和 工资排序。

意思就是 查询的时候第一条a表中的id = 3 的数据，salary=500 ,b表中在group=1的组别中工资大于500的数量为0，所以a表中的工资最高为500，同理第二名工资为250，b表中在1组中工资大于250的有一个，同理比第三名多的前面有两个依次类推

4  聚合函数：

       AVG([DISTINCT] expr)  求平均值，或者去重之后的平均值

       SUM(expr)    求和

       COUNT(expr)  求总数量

       MAX(expr)     求最大值

       MIN(expr)     求最小值

5  排序

       ORDER BY column ASC/DESC  排序 ASC默认升序 DESC降序，可以排序多列 根据第一列升序 第二列列降序

       组内排序 group by 之后 的order by  

6 格式化函数：

       数字格式化：FORMAT(X,D)   将X保留D位小数，具有四舍五入功能

       日期格式化：

DATE_FORMAT(date,format) 将日期date格式化为固定格式的日期字符串

STR_TO_DATE(str,format)  讲字符串日期转化为个定格式日期类型

7 CASE WHEN THEN ELSE END

用法：SELECT id,

       CASE group_a 

              WHEN 1 THEN '1组'

              WHEN 2 THEN '2组'

       ELSE '3组' END as group_b FROM test

结果：

CASE WHEN THEN ELSE END 还可以进行行列的转置