Mysql基础（6）函数

mysql的函数类似java中的方法，封装了若干实现细节，提高多个sql语句的复用性。

函数使用select作为调用关键字，基本语法为select 函数名(实参列表) from 表名,其中实参列表中的字段来源于数据表，当函数没有参数时，from字句可省略。其可以分为

• 单行函数

  操作行。

  只作用在某一行数据，实现某种功能。譬如length()用于计算字符串长度、concat()用于拼接字符串。
  mysql引擎会遍历表中的每一行数据，然后执行若干次（表行数）单行函数，最终结果仍是一个数据表。

• 分组函数

  在分组的基础上，操作某列数据。注意分组函数代表的是一类函数，不是指使用group by进行分组。

  作用在多行数据，实现统计功能。又称聚合函数、组函数。

  mysql引擎会读取表中某些列的全部数据，然后执行一次分组函数，最终在一行上输出列的某个维度统计数值。

• 由于分组函数输出为一行数据，因此分组函数的结果可以作为单行函数的实参。反之也可。

单行函数

mysql约定使用select调用函数，函数的参数可以是常量（字符常量、数值常量等），也可以是表字段。当参数是表字段，需存在from字句，此时表字段类似java的形参，表中的数值类似java的实参。

字符函数

一般地，字符函数指处理字符型数据的函数，其函数参数或函数返回值中至少有一项是字符型数据。

length(str)

输入：一个字符串。

输出：字符串长度。

# 参数为字符常量
SELECT LENGTH('abcdf') 
# 参数为表字段 需有from子句
SELECT LENGTH(last_name) as 长度 FROM employees 

concat(str1,str2…strN)

输入：多个字符串。

输出：拼接后的字符串。结果多配合as使用。

# 参数为字符常量
SELECT CONCAT('张','_','三') AS 名字
#参数为表字段 需有from子句
SELECT CONCAT(last_name,'_',first_name)  AS 名字 FROM employees

upper(str) lower(str)

输入：单个字符串

输出：全大写或小写字符串。

# 参数为字符常量
SELECT UPPER('aBc')
#参数为表字段 需有from子句
SELECT LOWER(last_name) FROM employees

substring()

输入：字符串，至少一个数字。

输出：单个字符串。

该函数用于截取字符串的子串。根据参数不同，有四种重载方式，但是只有两类功能。

同java截取子串不同的是，该函数对字符串的计数从1开始。

substr()是其简写形式。

• substring(str,pos)
  从目标字符串str的pos位置开始（含开始位置）直到尾部，截取子串。
  包含开始位置。

  # 参数为字符常量
  SELECT SUBSTRING('abcd',3) //输出'cd'
  #参数为表字段 需有from子句
  SELECT SUBSTRING(last_name,3) FROM employees
  

• substring(str from pos)
  等价于substring(str,pos)，只是把逗号改为了 from关键字。

  SELECT SUBSTRING('abcd' FROM 3)   //输出'cd'
  

• substring(str,pos,len)
  从pos位置开始（含开始位置），截取长度为len的子串。

  SELECT SUBSTRING('abcd',3,1)//输出'c'
  

• substring(str from pos for len)
  等价于substring(str,pos,len)，只是把逗号改为了 from、for关键字。

  SELECT SUBSTRING('abcd' FROM 3 FOR 1) //输出'c'
  

instr(str1,str2)

输入：两个字符串

输出：数值，表示第一个同str2完全匹配的字符在str1中的位置。没有则返回0。

SELECT INSTR('abcd','bc') //从'b'开始匹配，即第2项，返回2.
SELECT INSTR('abcd','bd')//无匹配，返回0.

trim(str1 from str2)

输入：两个字符串

输出：单个字符串

作用：去除 str2左右两边 同str1完全相同的字符串，返回去除后的子串。

trim(str1 from str2)是一般形式，其简写形式trim(str)用于去除字符串左右两边的空格。

# 一般形式
# 左边有6个aa,可以完全去除a，右边只能去除4个a
SELECT TRIM('aa' FROM 'aaaaaaAAAAAAAaaaaa') AS out_put //输出'AAAAAAAa'
# 简写形式.去除左右空格
SELECT TRIM('   AAA   ')

lpad(str1,len,str2)

输入：两个字符串，一个数字len表示输出的目标字符串的长度

输出：单个字符串

作用：若str1的长度小于len,那么在str1的左侧填充字符str2.这是该命令（left padding,左侧填充）的本意；若str1的长度大于len，则从1开始到位置len,截取str1的子串，此时str2无用，该函数变异为截取，不再是填充函数。

rpad操作右侧，作用同lpad类似。

# 目标长度长，正常填充
SELECT LPAD('aa',5,'b')//输出bbbaa
#目标长度短，截取
SELECT LPAD('abcde',3,'A') //截取。输出abc

repalce(str1,str2,str3)

输入：三个字符串

输出：单个字符串

作用：将str1中所有str2子串替换为str3，没有则不替换。

#存在 则替换
SELECT REPLACE('abcde','bc','BC') //输出aBCde
#不存在 不替换
SELECT REPLACE('abcde','WQ','BC') //输出abcde

数学函数

四舍五入round()

mysql提供round函数实现四舍五入功能，该函数存在两种重载形式

• round(num)
  输入：数字

  输出：该数字的四舍五入整数结果。

• round(num1,num2)
  输入：num1表示待处理的数字，num2表示小数点后保留位数。

  输出：满足小数点的四舍五入的数字结果

SELECT ROUND(2.555)  //输出2
SELECT ROUND(-2.62) //输出-3。负数先取绝对值，然后四舍五入
SELECT ROUND(2.78878,3) //输出2.79

向上取整ceil()

该函数只有一种形式，含有一个参数ceil(num)

输入：数字num

输出：大于等于num的最小整数

SELECT CEIL(1.00001) // 2
SELECT CEIL(1.0000) // 1
SELECT CEIL(-1.9999) // -1

向下取整floor()

该函数只有一个参数形式floor(num)

输入：数字

输出：小于等于num的最小整数

SELECT FLOOR(1.999) // 1
SELECT FLOOR(-1.0001) //-2
SELECT FLOOR(1.0000) // 1

截断truncate()

该函数只有两个参数形式truncate(num1,num2)

输入：num1表示被操作数；num2表示小数点后截断的位数。

输出：注意该函数只是截断，没有取舍。若num1没有小数点，则直接输出num1.若num1小数点后位数小于num2,则其后补零。

SELECT TRUNCATE(2.288,2) // 2.28.不四舍五入
SELECT TRUNCATE(2,2)     // 不截断。输出2
SELECT TRUNCATE(2.1,4)   // 右侧补零。输出2.1000

取余mod()

mod(num1,num2)函数计算逻辑为 num1 - num1/num2*num2。该函数的计算结果的符号取决于num1.

当num1是小数时，先对num1的整数部分进行取余，然后加上小数部分。

SELECT MOD(10,-3) //1
SELECT MOD(10,3) //1
SELECT MOD(-10,-3) //-1
SELECT MOD(-10,3) // -1

SELECT MOD(11.93,-3) //2.93.先 11mod3 = 2,再加上0.93
SELECT MOD(-11.93,-3) // -2.93

日期函数

基础日期函数

一般情况下，时间戳包含 日期 + 时间 两部分。mysql提供若干用于获取日期或时间的函数。

SELECT NOW()  // 2021-05-24 14:33:19 。当前 日期 + 时间
SELECT CURDATE() // 2021-05-24 。当前日期
SELECT CURTIME() //14:33:19。 当前时间
SELECT YEAR(NOW()) //2021.当前时间的年份。
SELECT MONTH(NOW()) //5.当前时间的月份。
SELECT DAY(NOW())//21.当前时间的天。
SELECT HOUR(NOW()) //14.当前时间的小时。
SELECT MINUTE(NOW())//22.当前时间的分钟。
SELECT SECOND(NOW())//39.当前时间的秒。
#以上带now()参数的函数的参数也可以是常量，但这些常量必须符合规则。
SELECT YEAR('2018-12-14') //正常输出 2018
SELECT YEAR('2018-19-14') //null。月份没有19
SELECT YEAR('2018-11-14 11:') //正常输出2018。可见后面的时间部分不完整（11：），不影响日期求值。
SELECT SECOND('2018-12-14 1:22:') //输出0。

mysql常用日期格式符

格式符	说明
%Y(大写)	四位的年份
%y(小写)	两位的年份
%m	全部为两位的月份
%c	0-9月为一位，其他为两位的月份
%d	全部为两位的日
%H	24小时制小时
%h	12小时制小时
%i	分钟
%s	秒

字符串转日期str_to_date()

str_to_date(dateStr,fmt)函数用于DBMS按照格式匹配成功后，提取字符串中的日期值，然后输出为xxxx-xx-xx日期形式。

输入：dateStr表示包含日期的字符串，fmt表示mysql格式符组成的字符串，用于告诉DBMS在str中日期值的位置。

输出：形如xxxx-xx-xx的日期，没有值的部分补零。

若dateStr中的日期值不符合fmt的格式，DBMS则无法正确解析出日期值，最后输出null。

SELECT STR_TO_DATE('2004-11-03 22:12:12','%Y-%c-%d %H:%i:%s')//输出2004-11-03 22:12:12
SELECT STR_TO_DATE('11-11-2011','%m-%d-%Y') //输出2011-11-11.DBMS通过格式符可以知道字符串各个位置的含义，然后重新组装，输出结果。程序给我们的日子字符串类似这种，需要手动处理。
SELECT STR_TO_DATE("25/11,1",'%y/%m,%d') //输出2025-11-01.通过格式符去除字符串中无效的部分（此处斜杠和逗号）
SELECT STR_TO_DATE("25/11",'%y/%m') //输出 2025-11-00.没有部分补充零。
SELECT STR_TO_DATE('2014-11-03','%Y-%c-%d')

mysql会对一些非正常日期字符串进行自动填充操作，但实际业务中不会出现，不必考虑。

日期转字符串date_format

date_format(date,fmt)函数将日期按照fmt格式输出为字符串。注意该函数第一个参数必须是日期类型，不是日期字符串。

SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日") //输出 2021年05月24日
SELECT DATE_FORMAT('2021-05-22',"%Y年%m月%d日") //null。第一个参数是字符串，不是日期类型

流程控制函数

if函数

if(exp1,exp2,exp3)有三个参数，第一个参数exp1是一个逻辑表达式，其结果为真，则返回表达式exp2的值，否则返回表达式exp3的值。

if函数作用处理逻辑类似三目表达式。

SELECT IF(10<4,4,5) //输出5
SELECT IF('10<4',4,5) //输出4。此时第一个参数是字符串，不是null和false,返回第二个参数。

case函数

case函数当与select连用时，case结构是一个较长的表达式，最终计算出一个结果，可作为查询列表的一项（case函数是单行函数）。有以下两种用法

• 等值判断。类似switch语句
• 范围判断。类似if…else if语句

等值判断

其处理逻辑类似switch语句，等值判断。其语法结构为

case 字段或表达式  
when 常量 then 表达式1 //分支
when 常量 then 表达式2
when 常量 then 表达式N
else 表达式  // 默认值
end  //结束标志

实例：查询员工姓名、新工资，邮箱。要求

• 若部门号=30，新工资为旧工资的2倍。
• 若部门号=50，新工资为旧工资的3倍。

SELECT last_name,
CASE department_id
WHEN 30 THEN salary*2 //此处没有分号
WHEN 50 THEN salary*3 //此处没有分号
ELSE salary
END 
AS '新工资',
email
FROM employees  //以上sql的2~7行代码是一个case表达式，在结果中占一个列

范围判断

其处理结构类似 if…else if…else语句。其语法结构比等值判断少了case后的内容，如下

case     
when 逻辑表达式 then 表达式1 //分支
when 逻辑表达式 then 表达式2
when 逻辑表达式 then 表达式N
else 表达式  // 默认值
end  //结束标志

实例 判断工资级别

SELECT last_name, salary,
CASE
WHEN salary < 7000 THEN 'c'
WHEN salary > 7000 AND salary < 10000 THEN 'b'
WHEN salary > 10000 AND salary < 15000 THEN 'a'
ELSE 'd'
END 
AS '工资级别',
email
FROM employees

其他函数

version()

当前mysql版本。

database()

当前再用数据库。

user()

当前登录用户。

分组函数

分组函数主要实现统计功能，包含sum(求和)、avg(平均)、min(最小)、max(最大)、count(统计非空数)，他们在组数据的基础上生效。若未分组（未使用group by分组），则把整列当成一个组。若分组，则作用在分组后的各个组上。其具有以下特点

• sum、avg一般处理数值类型，max（只要有可比性,就可排序，进而可获取最大值）、min（只要有可比性,就可排序，进而可获取最小值）、count可以处理任何类型。

• 都忽略null值

• 都存在去重统计概念，即可搭配distinct

• 统计函数时，使用count(*)。
  count(常量)表示新增一列，然后统计总行数，同count(星)类似，不推荐。

• 和分组函数一同查询的字段只能是group by后的字段。

  因为分组函数统计多行为一个值，而表字段有多行，一 和 多 无法逻辑对齐。