Mysql常用函数总结

字符函数

length 获取字节数

英文字符占一个字节，中文字符根据编码不同，可能占两个字节，也可能占三个字节。

SELECT 
  LENGTH('hello') ;
  
SELECT 
  LENGTH('你好') ;

concat 拼接字符串

字符串拼接即可以拼接字符串，也可以拼接字段名。

拼接字符串（结果为：Mysql）

SELECT 
  CONCAT('My', 'sq', 'l') ;

拼接字段名(结果相当于查询得到的数据进行拼接)

SELECT 
  CONCAT(last_name, ' and ', first_name) AS 'name' 
FROM
  employees ;

upper 和 lower 转换大小写

upper转换成大写，lower转换成小写。

SELECT 
  UPPER('hello') ;

SELECT 
  LOWER('HELLO') ;

SELECT 
  * 
FROM
  employees 
WHERE first_name = LOWER('LEX') ;

substr 截取字符串

索引从1开始计算，也可写成substring

截取从指定位置之后的所有字符，包括当前位置。

#结果为：56789
SELECT 
  SUBSTR('123456789', 5) AS result ;

截取从指定位置指定长度的字符，包括当前位置。

#结果为：567
SELECT 
  SUBSTR('123456789', 5, 3) AS result ;

instr 返回子串在主串中的第一次出现的索引

如果子串在主串中出现，返回索引位置

#结果为：4
SELECT 
  INSTR('123你好45你好', '你好') AS result ;

如果子串在主串中没有出现，返回0

#结果为：0
SELECT 
  INSTR('123你好45你好', 'qqq') AS result ;

trim 去除字符串前后空格

去除字符串前后空格

#结果为：去除空格
SELECT 
  TRIM('  去除空格  ') AS result ;

去除字符串前后指定的字符串

#结果为：A 
SELECT 
  TRIM(
    '去除' FROM '去除去除A去除'
  ) AS result ;

lpad 和 rpad 左填充和右填充

在左边填充指定的内容使总长度达到指定的长度，填充内容可以是一个字符，也可以是字符串。

#结果为：**123
SELECT 
  LPAD('123', 5, '*') AS result ;

#结果为：qwerqw123
SELECT 
  LPAD('123', 9, 'qwer') AS result ;

如果指定的长度小于被填充的字符串，相当于从头开始截取长度为指定长度的字符串。

#结果为：12
SELECT 
  LPAD('123', 2, '*') AS result ;

replace 字符串替换

#结果为：你好,你好 
SELECT 
  REPLACE('hello,hello', 'hello', '你好') AS result ;

数学函数

round 四舍五入

两种重载方法，第一种直接进行四舍五入变成整数，第二种可以设置保留小数点后几位进行四舍五入。

直接四舍五入

#结果为：2
SELECT 
  ROUND(1.5) ;

#结果为：1 
SELECT 
  ROUND(1.3) ;

#结果为：-2
SELECT 
  ROUND(-1.78) ;

保留小数点后几位进行四舍五入

#结果为：-1.78
SELECT 
  ROUND(- 1.783, 2) ;
 
#结果为：1.4
SELECT 
  ROUND(1.37, 1) ;

ceil 和 floor 向上向下取整

向上取整

#结果为：2 
SELECT 
  CEIL(1.32) ;

#结果为：-1
SELECT 
  CEIL(- 1.32) ;

向下取整

#结果为：1
SELECT 
  FLOOR(1.75) ;

#结果为：-2 
SELECT 
  FLOOR(- 1.32) ;

trunbcate 截断

截取到小数点后第几位，其余的部分舍弃不要。

#结果为：1.69
SELECT 
  TRUNCATE(1.6999, 2) ;

mod 取模

和使用%取余同样的功能，取余得到的值的正负和被取余数相同。

#结果为：1
SELECT 
  MOD(10, 3) ;

#结果为：-1
SELECT 
  MOD(-10, 3) ;

日期函数

now 返回当前系统日期+时间

#结果为：2022-06-02 17:37:44
SELECT NOW();

curtime 返回当前时间

#结果为：17:38:23
SELECT CURTIME();

str_to_date 将字符串通过指定的格式转换成日期

格式符

#结果为：1992-04-03
SELECT 
  STR_TO_DATE('04-03 92', '%m-%d %Y') AS result ;

date_format 将日期转换成字符

#结果为：2022年06日02日
SELECT 
  DATE_FORMAT(NOW(), '%Y年%m日%d日') AS result ;

流程控制函数

if 函数

比较字符串长度输出对应的字符串

#结果为：小
SELECT 
  IF(
    LENGTH('123') > LENGTH('1234'),
    '大',
    '小'
  ) ;

case 函数

第一种使用方式和switch...case...同理。

#结果为：字符串长度为3
SELECT 
  CASE
    LENGTH('111') 
    WHEN 3 
    THEN '字符串长度为3' 
    WHEN 4 
    THEN '字符串长度为4' 
    WHEN 5 
    THEN '字符串长度为5' 
    ELSE '不知道长度' 
  END AS lenth ;

第二种使用方式和多重if判断条件同理。

#结果为：字符长度小于5
SELECT 
  CASE
    WHEN LENGTH('123') > 5 
    THEN '字符长度大于5' 
    WHEN LENGTH('123') < 5 
    THEN '字符长度小于5' 
    ELSE '字符长度等于5' 
  END AS result ;

分组函数

sum函数和avg函数一般用于处理数值型

max、min、count可以处理任何类型

sum、avg、max、min、count都忽略了null值

SELECT 
  SUM(salary) 求和,
  AVG(salary) 平均值,
  MAX(salary) 最大值,
  MIN(salary) 最小值,
  COUNT(salary) 总条数,
  COUNT(DISTINCT salary) 去重总条数 
FROM
  employees ;