MYSQL新手学习笔记-D04常用单行函数

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;