MySQL函数详细讲解，让你轻松入门MySQL

1.单行函数

1.字符函数

# 显示客户端的字符集
SHOW VARIABLES LIKE '%char%';
-- 字符函数
# length: 获取参数值的字节个数
SELECT LENGTH('john');

# concat: 拼接字符串
SELECT CONCAT(sno,'_',sname) FROM student;

# upper:大写 lower: 小写
SELECT UPPER('john');
SELECT LOWER('HELLO WORLD');

# substr、substring 截取字符串
# 注意：索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',6); # 截取从指定索引处后面所有字符
SELECT SUBSTRING('李莫愁爱上了陆展元',1,3); # 截取从指定索引处指定字符长度的字符

# instr: 返回子字符串在父字符串的起始索引，如果没有返回0
SELECT INSTR('杨不梅爱上了殷六霞','殷六霞') AS out_put;

# trim: 
# 注意：只去除字符串中前后的字符或空格，不去除字符串中间的字符或空格
SELECT TRIM('-' FROM '-----qzp-----'); # 根据要求去除指定的字符
SELECT LENGTH(TRIM('    你好    ')); # 去除空格

# lpad: 用指定的字符实现左填充到指定长度
# 注意：当字符串长度大于给定的指定长度，则会从左边开始截取指定长度
SELECT LPAD('qzp',10,'*');
SELECT LPAD('qzp',2,'*');

# rpad: 用指定的字符实现右填充指定长度
SELECT RPAD('qzp',12,'*');
SELECT RPAD('qzp',2,'*');

# replace: 替换，选取需要被替换的字符，用指定的字符代替
SELECT REPLACE('qzpxihuanwjw','qzp','wo');

2.数学函数

# round 四舍五入
# 诀窍：取绝对值，再添加+-号
SELECT ROUND(1.65);# 结果：2
SELECT ROUND(-1.65); # 结果：-2
# 保留指定的小数点位数，正数表示小数点向右保留对应位数，负数表示小数点向左保留对应位数，实际小数点不会移动
SELECT ROUND(11.567,1); # 结果：11.6
SELECT ROUND(11.567,-1); # 结果：10

# ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.42); # 结果：2
SELECT CEIL(1.00); # 结果：1
SELECT CEIL(-1.02); # 结果：-1

# floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(1.42); # 结果：1
SELECT FLOOR(-1.42); # 结果：-2

# truncate 截断，根据指定的小数点位数开始截断
SELECT TRUNCATE(1.65,1) # 结果：1.6

# mod(被除数,除数) 取余
# 诀窍：mod(a,b)==>a-a/b*b;被除数为正，结果则为正数，被除数为负数，结果则为负数
SELECT 10%3;
SELECT MOD(10,3);# 结果：1
SELECT MOD(-10,-3);# 结果：-1
SELECT MOD(-10,3);# 结果：-1
SELECT MOD(10,-3);# 结果：1

3.日期函数

注意：必须先对格式符熟悉

格式符	功能
%Y	四位数的年份
%y	二位数的年份
%m	月份（01，02，03，04，…，11，12）
%c	月份（1，2，3，4…，11，12）
%d	日（01，02，…）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00，01，02，…,59）
%s	秒（00，01，02，…,59）

# now 返回当前系统日期+时间
SELECT NOW(); # 返回系统时间

# CURDATE 返回当前系统日期，不包含时间
SELECT CURDATE(); # 返回当前日期

# CURTIME 返回当前时间，不包含日期
SELECT CURTIME(); # 返回当前时间

# 可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()); # 返回当前系统时间是年份
SELECT YEAR('1996-08-20');# 返回指定时间是年份
SELECT MONTH(NOW());# 中文表示返回当前系统时间是月份
SELECT MONTHNAME(NOW()) # 英文表示返回当前系统时间是月份
SELECT DAY(NOW());# 返回当前系统时间的号数
SELECT HOUR(NOW());# 返回当前系统时间的小时
SELECT MINUTE(NOW());# 返回当前系统时间的分钟数

# str_to_date:将日期格式的字符转换成指定格式的日期,返回结果是按照系统格式输出
SELECT STR_TO_DATE('9-15-2020','%m-%d-%Y');

# date_fromat:将日期转换成字符
SELECT DATE_FORMAT('2020/09/15','%Y年%m月%d日');

4.其他函数

SELECT VERSION(); # 查看数据库的版本
SELECT DATABASE(); # 查看当前数据库名
SELECT USER(); # 查看当前用户信息

5.流程控制函数

# if函数，相当于if-else作用
# 格式：if(表达式,true,false) 
SELECT IF(10>5,'大','小');

# case函数
# case函数的使用方式一：
/*
CASE 要判断的字段或表达式1
WHEN 常量1 THEN 显示的值1或者语句1;
WHEN 常量2 THEN 显示的值2或者语句2;
WHEN 常量3 THEN 显示的值3或者语句3;
....
ELSE 显示的值或者语句n;
END
*/
# 注意：语句需要;,显示值不需要;类似于java中的switch-case用法
# 代码实例
SELECT * ,CASE degree
WHEN 90 THEN '优秀'
WHEN 80 THEN '良好'
WHEN 70 THEN '一般'
WHEN 60 THEN '及格'
ELSE '不合格' 
END FROM score;

# case函数的使用方式二：类似于多重if
/*
CASE 
WHEN 条件1 THEN 显示的值1或者语句1; 
WHEN 条件2 THEN 显示的值1或者语句1;
WHEN 条件3 THEN 显示的值1或者语句1;
WHEN 条件4 THEN 显示的值1或者语句1;
ELSE 显示的值或者语句n;
END 
*/
# 注意：语句需要;,显示值不需要;
# 代码实例：
SELECT * ,CASE 
WHEN degree>=90 THEN '优秀'
WHEN degree>=80 AND degree <90 THEN '良好'
WHEN degree>=70 AND degree <80 THEN '一般'
WHEN degree>60 AND degree <70 THEN '及格'
ELSE '不合格' 
END AS 成绩水平 FROM score;

2.分组函数

作用：用来统计使用，又称聚合函数。

1.聚合函数的分类

函数	作用	支持参数类型
SUM(字段名)	求和	数值型
AVG(字段名)	求平均值	数值型
MAX(字段名)	求最大值	任何类型
MIN(字段名)	求最小值	任何类型
COUNT(*) COUNT(1) COUNT(字段名)	统计，不过滤null值 统计，不过滤null值 统计，过滤null值	任何类型

SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;

/*COUNT:统计表中的数据*/
SELECT COUNT(studentname) FROM student;-- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student;-- Count(*),不会忽略null值，本质计算行数
SELECT COUNT(1) FROM student;-- Count(1),不会忽略null值，本质计算行数

2.聚合函数的特点：

• 聚合函数忽略null值
• 可以和distinct搭配使用
• 和聚合函数一同查询的字段要求是group by后的字段

3.count函数的特点

COUNT(*)和COUNT(1)：使用的时候，不会自动过滤null值（简单理解，COUNT(星)是统计行记录，只要这行记录至少有一个字段不为空，就可以+1；COUNT(1)是相当于在表前面加了一列，字段内容为1，然后统计 1 的个数，来统计行数）

COUNT(字段名)：使用的时候自动过滤null值

效率：

MYISAM存储引擎下，count(*)的效率高

INNODB存储引擎下，count(*)和count(1)的效果差不多，比count(字段名)效率高一点