MySQL学习之路（六）：常见函数

常见函数

• 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 好处：

1. 隐藏了实现细节
2. 提高代码的重用性

• 调用： select函数名（实参列表）【FROM 表名】（如果实参列表中用到的字段属于某个表，则需要加入某个表）
• 学习的关键：

1. 叫什么（函数名）
2. 干什么（函数功能）

• 分类：

1. 单行函数，如concat、length、ifnull等
2. 分组函数（统计函数、聚合函数、组函数），功能：做统计使用

一、单行函数

1.字符函数

1. length(参数)：返回字节数长度

SELECT LENGTH('avas');  返回4
SELECT LENGTH('aaaaa');  返回5

2. concat(字符串列表)：拼接字符串

SELECT CONCAT(列名1,'_',列2) FROM 表名
返回值：列1的值_列2的值

3. upper(参数)\lower(参数)：将字符串转化为大写或小写

SELECT UPPER(列名) FROM 表名
SELECT LOWER(列名) FROM 表名

4. substr,substring：截取字符串

注意，索引是从1开始的，不是从0开始

截取从指定索引处后面的所有字符
SELECT SUBSTR('小白和小红',4); 返回值：小红
截取从指定索引处指定字符长度的字符
SELECT SUBSTR('小白和小红',1,2); 返回值：小白

5. instr(目标串，子串) 返回子串第一次出现的索引，如果找不到则返回0

SELECT INSTR('小白和小红','小红'); 返回值：4
SELECT INSTR('小白小红和小红','小红'); 返回值：3

6. trim(字符串)，删掉字符串前后的空格

SELECT LENGTH(TRIM('  小白  ')); 返回值：6
SELECT LENGTH('  小白  '); 返回值：10

也可以删掉前后指定的字符串
SELECT TRIM('a' FROM 'aaa小白aaaaa'); 返回值：小白

7. LPAD(目标串，总长度，指定字符)：用指定字符实现左填充指定长度

SELECT LPAD('小白',6,'￥');返回值：￥￥￥￥小白

8. RPAD(目标串，总长度，指定字符)：用指定字符实现右填充指定长度

SELECT RPAD('小白',6,'￥');返回值：小白￥￥￥￥

9. replace（源串,目标串,替换串） 替换字符串

SELECT RPAD('小白小白和小红','小白','小黑');返回值：小黑小黑和小红

2.数学函数

1. round，四舍五入

SELECT ROUND(1.4);返回值：1
SELECT ROUND(1.445,2);保留两位小数，返回值：44

2. ceil,向上取整，返回 >= 改参数的最小整数

SELECT CEIL(1.4);返回值：2
SELECT CEIL(1.00);返回值：1
SELECT CEIL(-1.02);返回值：-1

3. floor,向下取整，返回 <= 改参数的最小整数

SELECT FLOOR(1.4);返回值：1
SELECT FLOOR(1.00);返回值：1
SELECT FLOOR(-1.02);返回值：-2

4. truncate,截断即保留几位小数

SELECT TRUNCATE(1.4444,1);返回值：1.4
SELECT TRUNCATE(1.4444,3);返回值：1.444
SELECT TRUNCATE(1.4444,5);返回值：1.44440

5. mod,取余

SELECT MOD(10,3);返回值：1
SELECT MOD(-10,3);返回值：-1
SELECT MOD(-10,-3);返回值：-1

返回值的符号跟被除数相同，
至于为什么，可以使用 a-a/b*b这条式子算一下

3.日期函数

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

SELECT NOW(); 2020-05-20 15:16:02

3. curdate,返回当前系统日期,不包含时间

SELECT CURDATE(); 2020-05-20

4. curtime,返回当前系统时间,不包含日期

SELECT CURTIME(); 15:16:02

5. 可以获取指定的布冯，年、月、日、小时、分、秒

SELECT YEAR(NOW());返回当前年份
SELECT MONTH(NOW());返回当前月份
SELECT MONTHNAME(NOW());返回当前月份的英文名称
SELECT DAY(NOW());返回当前日
SELECT HOUR(NOW());返回当前小时
SELECT MINUTE(NOW());返回当前分钟
SELECT SECOND(NOW());返回当前秒

6. str_to_date,将日期格式的字符转换成指定格式的日期
   

SELECT STR_TO_DATE('2020-5-20','%Y-%c-%d');

7. date_format,将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')；2020年05月20日

4.其他函数

1. 查看当前版本

SELECT VERSION()

2. 查看当前数据库

SELECT DATABASE()

3. 查看当前用户信息

SELECT USER()

5.流程控制函数

1. if函数：相当于if-else的效果

SELECT IF(条件表达式，A,B);如果条件表达式为true则返回A,否则返回B

2. case函数的使用一：相当于JAVA中的switch case的效果,一般用于等值判断

语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；（如果是语句的记得加分号）
when 常量2 then 要显示的值2或语句2；
when 常量3 then 要显示的值3或语句3；
… … …
else 默认值，常量n then 要显示的值n或语句n
end

SELECT salary AS 原工资,department_id,
CASE department_id
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
WHEN 40 THEN salary*1.4
WHEN 50 THEN salary*1.5
ELSE salary
END as 新工资
FROM employees
ORDER BY department_id ASC

返回结果

3. case函数的使用二：类似于java中的多重if，一般用于区间判断

语法：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值1或语句2
when 条件3 then 要显示的值1或语句3
… … …
else 要显示的值n或语句n
end

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END as 工资级别
FROM employees
ORDER BY 工资级别

结果：

二、分组函数

使用分组函数的一个限制：和分组函数一同查询的字段要求是group by后的字段

1. sum，求和函数

参数支持类型：数值型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是

SELECT SUM(salary) FROM employees; 求employees表中salary字段的值的总和
SELECT SUM(DISTINCT salary) FROM employees; 求employees表中salary字段的不重复的值的总和

2. avg，求平均值函数

参数支持类型：数值型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是

SELECT AVG(salary) FROM employees; 求employees表中salary字段的值的平均值
SELECT AVG(DISTINCT salary) FROM employees; 求employees表中salary字段的不重复的值的平均值

3. max，求最大值函数

参数支持类型：数值型，字符型，日期型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是

SELECT MAX(salary) FROM employees; 求employees表中salary字段的值的最大值

4. min，求最小值函数

参数支持类型：数值型，字符型，日期型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是

SELECT MIN(salary) FROM employees; 求employees表中salary字段的值的最小值

5. count，计算个数

参数支持类型：any
是否忽略null值：是
是否可以和DISTINCT搭配使用：是

SELECT COUNT(salary) FROM employees; 求employees表中salary字段值为非null的总个数;
SELECT COUNT(*) FROM employees;  求employees表中的行数
SELECT COUNT(1) FROM employees;  求employees表中的行数

效率：

• MYISAM存储引擎下，count(*)的效率高
• INNOB存储引擎下,count()和count(1)效率差不多，比ocunt(字段)高一些
  推荐使用count()来统计行数