mySQL中的函数

不同数据库中的函数不同的

函数分类

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MSQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

单行函数

数值函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
PI()	返回圆周率的值
CEL(x), CEILING(x)	返回某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大正数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近的值，并保留到小数点后面位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL

字符串函数

函数	用法
ASCII(S)	返回字符串S中的第一个字符的ASCI码值
CHAR_LENGTH(s)	返回字符串s的字符数.作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,.…,sn)	连接s1，s2，…，sn为一个字符串
CONCAT_WS(x, s1,s2,.,sn)	同CONCAT(s1，s2，。)函数，但是每个字符串之间要加上x
INSERT(str,idx,len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s)或UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s)或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str,len,pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str,len,pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
…	…

时间函数

函数	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化日期time值
GET_FORAMT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str,fmt)	按照一个字符串fmt对str进行解析，解析为一个日期

上述 非GET_FORAMT 函数中fmt参数常用的格式符：

格式符	说明	格式符	说明
%Y	4为数字表示年份	%y	2位数字表示年份
%M	月名表示月份(January…)	%m	2位数字表示月份(01,02,03,…)
%b	缩写的月名(Jan,…)	%c	表示月份(1,2,3,…)
%D	英文后缀表示月中的天数(1st,2nd,3rd,… )	两位数字表示月中的天数	01,02,03,…
%e	数字形式表示月中的天数(1,2,3,…)
%H	两位数字表示小时，24小时制(01,02,03,…)	%h和%l	；两位数字形式表示小时，12小时制(01,02,…)
%k	数字形式的小时，24小时制(1,2,3,…)	%l	数字形式表示小时，12小时制(01,02,…)
%i	两位数字表示分钟(00,01,02,…)	%S和%s	两位数字表示秒(00,01,02,…)
%W	一周中星期的名称(Saturday,…)	%a	一周中星期的缩写(Sun.,mon.,…)
…	…	…	…

流程控制函数

函数	用法
IF(value,value1,value2)	如果value的值为true，返回vualue1，否则返回value2
IF(value1,value2)	如果value1不为null，返回vualue1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …[ELSE resultn] END	相当于if…else if…else…
CASE sxpr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2…[ELSE 值 n] END	switch…case

聚合函数

常见的几个聚合函数

1、AVG(平均值) / SUM(求和) -- 会自动过滤null

select AVG(salary),SUM(salary) from emplayees

2、MAX(最大值) / MIN(最小值) -- 会自动过滤null

select MAX(salary),MIN(salary) from emplayees

3、COUNT

• 作用：计算指定字段在查询结构中出现的次数
  • 如果计算表中有多少条记录，如何实现？
  • 方式一： COUNT(*)
  • 方式二：COUNT(1)
  • 方式三：COUNT(指定字段) ：不一定对 因为计算时会自动排除null
• 公式： AVG = SUM / COUNT 永远成立

4、GROUP BY

• 将表中的数据根据字段中的相同值进行若干分组

# 单个字段分组
select dept_id, AVG(salary) from employees GROUP BY dept_id
# 多字段分组
select dept_id, gid AVG(salary) from employees GROUP BY dept_id, gid

• 结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY中，声明在GROUP BY中的字段可以不出现在SELECT中
• 结论2 GROUP BY声明在 FROM和WHERE后边，ORDER BY 、LIMIT 前面