D04:常用单行函数
数据库结构
特点
类似于java的方法
调用
select 函数名(参数列表)【from 表】
分类
一、 单行函数
1. 字符函数
2. 数学函数
3. 日期函数
4. 其他函数
5. 流程控制函数
二、 分组函数
一、单行函数
1、字符函数
(1)length 获取参数值的字节个数
- 汉字三个字节,英文一个字节
SELECT LENGTH('join');
SELECT LENGTH('一二三');
(2)concat 拼接字符串
SELECT CONCAT(first_name,'_',last_name)
FROM employees;
(3)upper、lower 大写小写
SELECT UPPER('john');
SELECT UPPER('JOHN');
(4)substr、substring
- mysql索引从1开始
- 返回从索引6开始的全部
SELECT SUBSTR('一二三四五六七八',6)
AS out_put;
返回从索引2开始,长度为三的字符串
SELECT SUBSTR('一二三四五六七八',2,3)
AS out_put;
(5)instr
- 返回目标字符串的起始索引
- 如果没有返回0
SELECT INSTR('一二三四五六七八','六七八')
AS out_put;
(6)trim
- 去除字符串中的特定字符(只能去除两端的)
- 没选特定字符默认去除空格
SELECT TRIM(' 一 二 三 ')
AS out_put;
SELECT TRIM('a'FROM'aaaaa一a二a三aaaa')
AS out_put;
(7)lpad
- 用指定字符左填充,直到指定长度
- 若指定长度小于原长度,则从左到右保留指定长度
SELECT LPAD('一二三',10,'a')
AS out_put;
SELECT LPAD('一二三',2,'a')
AS out_put;
(8)rpad
同上
(9)replace 替换指定字符
SELECT REPLACE('一二三四五','三','山');
2.数学函数
(1)round 四舍五入
SELECT ROUND(1.45);
小数点后保留指定位数
SELECT ROUND(1.56789,2);
(2)ceil向上取整
SELECT CEIL(1.45);
(3)floor向下取整
SELECT FLOOR(1.45);
(4)truncate保留小数点后指定位数
SELECT TRUNCATE(1.123456,2);
(5)mod取余 相当于%
SELECT MOD(10,3);
3.日期函数
(1)now 返回当前系统日期+时间
SELECT NOW();
(2)curdata只返回日期,不含时间
SELECT CURDATE();
(3)curtime 返回当前时间,不含日期
SELECT CURTIME();
(4)获得指定年或月或日
SELECT YEAR(NOW())
AS 年;
SELECT MONTH(NOW())
AS 年;
SELECT MONTHNAME(NOW())
AS 年;
(5)str_to_date 将字符通过指定的格式解析成日期
- 大Y是完整年2020,小y是年的末两位20
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d')
AS out_put;
(6)data_format日期转字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
AS out_put;
4.其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
5.流程控制函数
(1)if
三元(判断条件,正确时输出,错误时输出)
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金')
FROM employees;
(2)case
方法一:
case 判断的量
when 常量一 then 输出值
when 常亮二 then 输出值
else 输出值
end
案例:部门号=30、40、50,工资显示为1.1/1.2/1.3倍。
SELECT salary AS 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees
ORDER BY department_id;
方法二:类似多重if
case
when 条件1 then 输出值
when 条件2 then 输出值
else 输出值
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.sum、avg适用于数值型。对字符型操作不会报错,但会返回0
2.max、min、count支持任何类型,count返回非空类型个数
3.分组函数都会忽略null值
4.和distinct搭配,可统计去重后的数据
5.利用count(*)可以统计数据行数
简单使用
SELECT
SUM(salary)AS 和,
AVG(salary)AS 平均,
MAX(salary)AS 最大值,
MIN(salary)AS 最小值,
COUNT(salary)AS 个数
FROM employees;
和distinct搭配使用,可以统计去重后的数据
SELECT SUM(DISTINCT salary),SUM(salary)
FROM employees;
count函数
SELECT COUNT(salary)FROM employees;
#统计行数
SELECT COUNT(*)FROM employees;
#统计行数
SELECT COUNT(1)FROM employees;