调用：select 函数名() [from 表];
分类：
-
- 单行函数：如concat、length、ifnull等。
分类：
- 字符函数：传入参数类型是字符型
- 数学函数：传入参数类型是数字型
- 日期函数：传入参数类型是日期型
- 其他函数
- 流程控制函数
- 单行函数：如concat、length、ifnull等。
- 分组函数（又叫：统计函数/聚合函数/组函数）：做统计使用
image.png
常见函数
一.字符函数
length 、concat 、substr 、instr、upper、lower、lpad、rpad、replace
1.length
SELECT LENGTH('join');-- 4
SELECT LENGTH('张三丰');-- 9
SHOW VARIABLES LIKE '%char%'; -- character_set_client utf8mb4。因为是uft8所以一个中文字符占3个字节。如果gbk的话，一个中文字符占2个字节。
2.concat 拼接字符
SELECT CONCAT(last_name,'_',first_name) FROM employees;
3.upper、lower
SELECT UPPER('john'); -- JOHN
SELECT LOWER('JOHN'); -- john
示例：将姓变大写，名变小写，然后拼接。
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
4.substr、substring
-- 1. 截取从指定索引处后面所有的字符
-- 注意：索引从1开始，不是从0开始。索引从pos开始
SELECT SUBSTR('传闻中的陈芊芊',5) 名字; -- 陈芊芊
-- 2. -- 截取从指定索引处指定长度的字符
-- 1:是pos，2：是长度 从1开始，截取2个
SELECT SUBSTR('传闻中的陈芊芊',1,2) 名字; -- 传闻
案例：名字中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 名字
FROM employees;
5.instr 、
-- 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('传闻中的陈芊芊陈芊芊','陈芊芊') as 输出; -- 5
6.trim
-- 去前后空格
SELECT TRIM(' 陈 芊芊 ') as 输出;-- 陈 芊芊
-- 去前后指定字符
SELECT TRIM('a' FROM 'aaaaaaaaa陈aaa芊芊aaaaaaaaa') as 输出; -- 陈aaa芊芊
7.lpad
-- 用指定的字符实现左填充指定的长度
SELECT LPAD('陈芊芊',10,'*') 输出; -- *******陈芊芊
SELECT LPAD('陈芊芊',2,'*') 输出; -- 陈芊
8.rpad
-- 用指定的字符实现右填充指定的长度
SELECT RPAD('陈芊芊',10,'*') 输出; -- 陈芊芊*******
SELECT RPAD('陈芊芊',2,'*') 输出; -- 陈芊
9.replace
SELECT REPLACE('传闻中的陈芊芊和陈芊芊','陈芊芊','陈小千') as 输出; -- 传闻中的陈小千和陈小千
二.数学函数
round、ceil、floor、truncate、mod、rand
1.round 四舍五入
-- 不管是正负，取绝对值四舍五入，再加正负号就行
SELECT ROUND(1.467); -- 1
-- 小数点后保留2位数
SELECT ROUND(1.467,2);-- 1.47
2.ceil 向上取整
-- 返回大于等于该参数的最小整数
SELECT CEIL(1.001); -- 2
SELECT CEIL(-1.001); -- -1
3.floor 向下取整
-- 返回小于等于该参数的最小整数
SELECT FLOOR(1.001); -- 1
SELECT FLOOR(-1.001); -- -2
4.truncate 截断
-- 小数点后保留1位
SELECT TRUNCATE(1.69,1);-- 1.6
5.mod 取余
-- mod(a,b); a-a/bb（/是取整 ）：被除数-整数商除数=余数
SELECT MOD(10,3); -- 1 相当于：10%3
SELECT MOD(-10,-3); -- -1
SELECT MOD(10,-3); -- 1
SELECT MOD(10,-3.0); -- 1.0
6.rand获取随机数。
返回0-1直接的小数
三.日期函数
now、currentdate、currenttime、year、month、day、hour、minute、second、str_to_date、date_format、datediff、monthname
1.now 返回当前系统日期+时间
SELECT NOW();-- 2020-06-02 14:14
2.current_date 返回当前系统日期，不包含时间
SELECT CURRENT_DATE();-- 2020-06-02
3.current_time 返回当前时间时间，不包含日期
SELECT CURRENT_TIME(); -- 14:16:41
获取年、月、日、时、分、秒
年
SELECT YEAR(NOW());-- 2020
SELECT YEAR('1993-06-01');-- 1993
SELECT YEAR(hiredate) 年 FROM employees;
月
SELECT MONTH(NOW());-- 6
SELECT MONTHNAME(NOw());-- June
日
SELECT DAY(NOW());
时
SELECT HOUR(NOW());
分
SELECT MINUTE(NOW());
秒
SELECT SECOND(NOW());
str_to_date 字符转日期
-- 第一个参数str格式 是第二个参数‘%Y-%c-%d’的格式
SELECT STR_TO_DATE('2020-6-2','%Y-%c-%d') as 输出; -- 2020-06-02
查询入职日期为1992-4-3的员工的信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
date_format 将日期转化成字符
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日"); -- 2020年06月02日
查询有奖金的员工名和入职日期（XX月/XX日 XX年）
SELECT last_name,DATE_FORMAT(hiredate,'%c月/%d日 %Y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL ;
datediff 两个日期相差的天数
monthname 以英文形式返回月
四.其他函数
SELECT VERSION(); -- 8.0.11
SELECT DATABASE();-- myemployees
SELECT USER();-- root@localhost
password('str'); -- 字符加密
MD5('str'); -- md5加密
五.流程控制函数
1.if函数
if(条件表达式，表达式1，表达式2)：如果表达式成立，返回表达式1，否则返回表达式2。
SELECT IF(10>8,'大','小'); -- 大
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，哈哈')
FROM employees;
2.case函数
使用一：switch case的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或者语句1;
when 常量1 then 要显示的值2或者语句2;
...
else 要显示的值n或语句n;
end
案例：查询员工的工作，要求：
-- 部门号=30，显示的工资为1.1倍数。部门号=40，显示的工资为1.2倍数。部门号=50，显示的工资为1.3倍数。
-- 其他部门显示为原工资
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1 -- then 后面是值的话不加;,如果是语句就加;
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;
case的使用二 有点类似多重if
case
when 条件1 then 要显示的值1或者语句1;
when 条件2 then 要显示的值2或者语句2;
else 要显示的值n或者语句n
end
案例：查询员工的工资情况
-- 如果工资>20000,显示A级别
-- 如果工资>15000,显示B级别
-- 如果工资>10000,显示C级别
-- 否则，显示D级别
SELECT salary,
CASE
WHEN salary>20000 THEN 'A级别'
WHEN salary>15000 THEN 'B级别'
WHEN salary>10000 THEN 'C级别'
ELSE 'D级别'
END AS '级别'
FROM employees;
案例1.显示系统时间（日期+时间）
SELECT NOW();
案例2.查询员工号、姓名、工资，以及工资提高百分之20后的结果（new salary）
SELECT employee_id,last_name,salary,salary*1.2 'new salary' FROM employees;
案例3.将员工的姓名按照首字母排序，并写出姓名的长度
SELECT last_name,LENGTH(last_name) FROM employees ORDER BY SUBSTR(last_name,1,1) ;
案例4.做一个查询，产生下面的结果
-- <lastname> earns <salary> monthly but wants <salary3>
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary3) FROM employees;
分组函数
/*
功能：用作统计使用，又称为聚合函数或者统计函数或者组函数
分类：
sum avg max min count
特点：
- sum、avg只处理数值型
max、min、count可以处理任何类型 - 以上分组函数都忽略null值
- 可以和distinct搭配实现去重的运算
- 一般用count(*)看行数
- 和分组函数一同查询的字段要求是group by后的字段
*/
1.简单使用
SELECT SUM(salary) FROM employees; -- 691400.00
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees; -- 只支持不为null的个数
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数
FROM employees;
SELECT ROUND(AVG(salary)) FROM employees;
2.参数支持的类型
-- sum
SELECT SUM(last_name) FROM employees;
3.和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;
4.count函数的详细
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees; -- 107 总行数
SELECT COUNT(1) FROM employees;-- 107 总行数
-- 效率：
-- MYISAM存储引擎下， COUNT(*)的效率最高
-- INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，但是比COUNT(字段)要高一点
5.和分组函数一同查询的字段有限制，要求是group by后的字段
-- employee_id没有意义
SELECT AVG(salary),employee_id FROM employees;
案例1.查询员工表中最大入职时间和最小入职时间相差的天数 DATEDIFF
SELECT DATEDIFF(MAX(hiredate),Min(hiredate))
FROM employees;
案例2.查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE employee_id=90;
网友评论