常见函数
- 概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
- 好处：
- 隐藏了实现细节
- 提高代码的重用性
- 调用： select函数名（实参列表）【FROM 表名】（如果实参列表中用到的字段属于某个表，则需要加入某个表）
- 学习的关键：
- 叫什么（函数名）
- 干什么（函数功能）
- 分类：
- 单行函数，如concat、length、ifnull等
- 分组函数（统计函数、聚合函数、组函数），功能：做统计使用
一、单行函数
1.字符函数
- length(参数)：返回字节数长度
SELECT LENGTH('avas'); 返回4
SELECT LENGTH('aaaaa'); 返回5
- concat(字符串列表)：拼接字符串
SELECT CONCAT(列名1,'_',列2) FROM 表名
返回值：列1的值_列2的值
- upper(参数)\lower(参数)：将字符串转化为大写或小写
SELECT UPPER(列名) FROM 表名
SELECT LOWER(列名) FROM 表名
- substr,substring：截取字符串
注意，索引是从1开始的，不是从0开始
截取从指定索引处后面的所有字符
SELECT SUBSTR('小白和小红',4); 返回值：小红
截取从指定索引处指定字符长度的字符
SELECT SUBSTR('小白和小红',1,2); 返回值：小白
- instr(目标串，子串) 返回子串第一次出现的索引，如果找不到则返回0
SELECT INSTR('小白和小红','小红'); 返回值：4
SELECT INSTR('小白小红和小红','小红'); 返回值：3
- trim(字符串)，删掉字符串前后的空格
SELECT LENGTH(TRIM(' 小白 ')); 返回值：6
SELECT LENGTH(' 小白 '); 返回值：10
也可以删掉前后指定的字符串
SELECT TRIM('a' FROM 'aaa小白aaaaa'); 返回值：小白
- LPAD(目标串，总长度，指定字符)：用指定字符实现左填充指定长度
SELECT LPAD('小白',6,'￥');返回值：￥￥￥￥小白
- RPAD(目标串，总长度，指定字符)：用指定字符实现右填充指定长度
SELECT RPAD('小白',6,'￥');返回值：小白￥￥￥￥
- replace（源串,目标串,替换串） 替换字符串
SELECT RPAD('小白小白和小红','小白','小黑');返回值：小黑小黑和小红
2.数学函数
- round，四舍五入
SELECT ROUND(1.4);返回值：1
SELECT ROUND(1.445,2);保留两位小数，返回值：44
- ceil,向上取整，返回 >= 改参数的最小整数
SELECT CEIL(1.4);返回值：2
SELECT CEIL(1.00);返回值：1
SELECT CEIL(-1.02);返回值：-1
- floor,向下取整，返回 <= 改参数的最小整数
SELECT FLOOR(1.4);返回值：1
SELECT FLOOR(1.00);返回值：1
SELECT FLOOR(-1.02);返回值：-2
- truncate,截断即保留几位小数
SELECT TRUNCATE(1.4444,1);返回值：1.4
SELECT TRUNCATE(1.4444,3);返回值：1.444
SELECT TRUNCATE(1.4444,5);返回值：1.44440
- mod,取余
SELECT MOD(10,3);返回值：1
SELECT MOD(-10,3);返回值：-1
SELECT MOD(-10,-3);返回值：-1
返回值的符号跟被除数相同，
至于为什么，可以使用 a-a/b*b这条式子算一下
3.日期函数
- now,返回当前系统日期+时间
SELECT NOW(); 2020-05-20 15:16:02
- curdate,返回当前系统日期,不包含时间
SELECT CURDATE(); 2020-05-20
- curtime,返回当前系统时间,不包含日期
SELECT CURTIME(); 15:16:02
- 可以获取指定的布冯，年、月、日、小时、分、秒
SELECT YEAR(NOW());返回当前年份
SELECT MONTH(NOW());返回当前月份
SELECT MONTHNAME(NOW());返回当前月份的英文名称
SELECT DAY(NOW());返回当前日
SELECT HOUR(NOW());返回当前小时
SELECT MINUTE(NOW());返回当前分钟
SELECT SECOND(NOW());返回当前秒
- str_to_date,将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2020-5-20','%Y-%c-%d');
- date_format,将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')；2020年05月20日
4.其他函数
- 查看当前版本
SELECT VERSION()
- 查看当前数据库
SELECT DATABASE()
- 查看当前用户信息
SELECT USER()
5.流程控制函数
- if函数：相当于if-else的效果
SELECT IF(条件表达式，A,B);如果条件表达式为true则返回A,否则返回B
- 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
返回结果
- 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后的字段
- sum，求和函数
参数支持类型：数值型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是
SELECT SUM(salary) FROM employees; 求employees表中salary字段的值的总和
SELECT SUM(DISTINCT salary) FROM employees; 求employees表中salary字段的不重复的值的总和
- avg，求平均值函数
参数支持类型：数值型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是
SELECT AVG(salary) FROM employees; 求employees表中salary字段的值的平均值
SELECT AVG(DISTINCT salary) FROM employees; 求employees表中salary字段的不重复的值的平均值
- max，求最大值函数
参数支持类型：数值型，字符型，日期型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是
SELECT MAX(salary) FROM employees; 求employees表中salary字段的值的最大值
- min，求最小值函数
参数支持类型：数值型，字符型，日期型
是否忽略null值：是
是否可以和DISTINCT搭配使用：是
SELECT MIN(salary) FROM employees; 求employees表中salary字段的值的最小值
- 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()来统计行数
扫一扫
1780
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
