仅作为学习笔记,学习资源来源于B站视频:BV1xW411u7ax
内置函数
1 单行函数
调用:select 函数名(实参列表) 【from 表】;
1.1 字符函数
- length 获取参数值的字节个数,utf-8中一个汉字代表3个字节
SELECT LENGTH('john'); #返回4
SELECT LENGTH('哈哈哈hahaha'); #返回15
SHOW VARIABLES LIKE '%char%' #查看客户端使用的字符集,如utf8
- concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
- upper、lower 大小写转换函数
#示例:将姓变大写,名变小写,然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
- substr、substring 字符截取函数
SELECT SUBSTR(str,start,len) #索引从1开始,没有lenth则索引后面所有字符
#案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM employees;
- instr 返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('哈哈孙小果蛤爱上了孙小果','孙小果') AS out_put; #输出3
- trim 去前后空格/去前后指定字符,中间不能去除
SELECT TRIM(' 哈哈啊 ') AS out_put; #输出哈哈啊
SELECT TRIM('a' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaa') AS out_put; #输出张aaaaaaaaaaaa翠山
- lpad 用指定的字符实现左填充指定长度,指定长度是总长度(字符个数)
#LPAD(str,len,padstr)
SELECT LPAD('哈哈',10,'*') AS out_put; #输出********丽丽
- rpad 用指定的字符实现右填充指定长度(字符个数)
#RPAD(str,len,padstr)
SELECT RPAD('丽丽',9,'ab') AS out_put; #输出丽丽abababa
- replace 替换
#REPLACE(str,from,to)
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
1.2 数学函数
- round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
- ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
- floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);
- truncate 截断
SELECT TRUNCATE(1.69999,1); #返回1.6
- mod 取余
/*
mod(a,b): a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*(-3)=-1
*/
SELECT MOD(10,-3);
1.3 日期函数
- now 返回当前系统日期+时间
SELECT NOW();
- curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
- curtime 返回当前时间,不包含日期
SELECT CURTIME();
- year、month…可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
- monthname 返回月份名
SELECT MONTHNAME(NOW()) 月;
- str_to_date 将字符通过指定的格式转换成日期
各种格式符的功能:
格式符 | 功能 |
---|---|
%Y | 4位的年份 |
%y | 2位的年份 |
%m | 月份(补充0) |
%c | 月份(不补充0) |
%d | 日(补充0) |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟 |
%s | 秒 |
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992-4-3的员工信息,但给定的日期格式为4-3 1992,则需要转化
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
- date_format 将日期转换成字符
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;
1.4 其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
2 分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
注意:
- sum、avg一般用于处理数值型;max、min、count可以处理任何类型
- 以上分组函数都忽略null值
- 可以和distinct搭配实现去重的运算
- 一般使用count(*)用作统计行数
- 和分组函数一同查询的字段要求是group by后的字段
SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;
#和distinct搭配实现去重的运算
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
#统计行数
SELECT COUNT(*) FROM employees;