一篇文章带你玩懂数据库的基础函数

大家好，欢迎来到IT知识分享网。

单行函数

含数据函数，字符串函数，时间函数，流程函数

1.数据函数

示例：

SELECT ABS(-5), CEIL(2.3), CEIL(-2.3), FLOOR(2.3), FLOOR(-2.3), RAND(), RAND(8), ROUND(2.3, 1), TRUNCATE(2.36, 1); 

2.字符串函数

示例：

SELECT CHAR_LENGTH('abc') , CONCAT('%','娃娃','%') , FIND_IN_SET('aa','cc,dd,aa,bb,gg,aa'); 

3.时间函数

常用的有：

获取当前时间:

​ now() 获取当前详细时间，返回 YYYY-MM-DD HH:MM:SS

​ curdate() 获取当前年月日，返回 YYYY-MM-DD

​ curtime() 考虑系统的时区，返回 HH:MM:SS

​ utc_date() utc_time() 不会考虑时区，返回 HH:MM:SS

时间部分提取:

​ year(时间) 获取传入的时间参数的年份，返回 YYYY

​ month(时间) 获取传入的时间参数的月份，返回 MM

​ week(时间) 获取传入的时间参数的周期，返回的是距离跨年的星期数

​ weekday(时间) 获取传入的时间参数的日期，返回 D（即星期几，其中0是星期1 ，即所有的返回值加一，则是当前的日期）

​ dayofweek(时间) 获取传入的时间参数的日期，返回 D（即星期几，其中1是星期1 ）

示例：

SELECT NOW(), CURDATE(), CURTIME(), UTC_DATE(),UTC_TIME(), YEAR(NOW()), MONTH(NOW()), WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW()), DAY(NOW()), DAYOFMONTH(NOW()); 

时间运算:

​ adddate | date_add(时间锚点,interval ± 值 对应时间单位的英文 day month year…)

​ subdate | date_sub(时间锚点,interval ±值 对应时间单位的英文 day month year…)

​ addtime(时间,秒) 时间的±秒的时间运算

​ datediff(日期,日期) 算两个日期之间间隔的天

​ timediff(时间,时间) 算两个时间间隔时间 时:分:秒

示例：

SELECT ADDDATE(NOW(),INTERVAL 1 MONTH), ADDDATE(NOW(),INTERVAL -1 MONTH), ADDTIME('10:10:10',20), ABS(DATEDIFF(CURDATE(),'2024-11-11')) , TIMEDIFF('12:00:00','10:00:00'); 

时间格式化输出：

​ DATE_FORMAT(date, format_string) 将日期或时间格式化为指定的format_string

​ DATE_FORMAT(时间，’格式字符串‘)

​ TIME_FORMAT(time, format_string) 将时间格式化为指定的format_string

​ TIME_FORMAT(时间，’格式字符串‘)

​ ♦️STR_TO_DATE(non_standard_date_string, format_string) 将非标准的日期字符解析为标准的日期格式字符

​ STR_TO_DATE(‘非标注时间字符串’,‘非标注时间字符串对应的格式’)

关于时间格式化的占位符：

示例：

SELECT NOW(); 

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日'); 

SELECT TIME_FORMAT(NOW(),'%H:%i:%s'); 

# 前端-> 2024年04月20日 -> 后端 -> 数据库 -> str_to_date -> 标准时间 -> '2024-04-20' SELECT STR_TO_DATE('2024年04月20日' , '%Y年%m月%d日'); 

4.流程函数

i. IF函数

用于sql执行条件时的判断，类似于三目表达式

IF(condition, true_value, false_value) 

解释：当 condition 成立时，返回 true_value，否则返回 false_value。

ii. IFNULL函数

用于对NULL值的处理

IFNULL(column, null_value) 

解释：当指定列column值为NULL时，去null_value的值作为结果。

iii. CASE函数

# 语法： # 格式1： # 当condition的值为真的时候，输出对应的result，从上倒下扫描，满足一条则输出完毕后结束CASE语句 CASE WHERE condition1 THEN result1 # 自带break的操作 WHERE condition2 THEN result2 WHERE condition3 THEN result3 WHERE condition4 THEN result4 …… ELSE END [AS 别名] # 当需要用到起别名这种方法的时候再用 # 格式2 # 当expr表达式的返回值满足下面哪个value值时，就输出对应的result值，从上倒下扫描，满足一条则输出完毕后结束CASE语句 CASE expr WHERE value1 THEN result1 # 自带break的操作 WHERE value2 THEN result2 WHERE value3 THEN result3 WHERE value4 THEN result4 …… ELSE END [AS 别名] # 当需要用到起别名这种方法的时候再用 

整体示例：

# 根据员工生日，如果在1990年前，则加薪10%，否则加薪5% SELECT ename, salary, birthday, IF(YEAR(birthday) < 1990, salary * 1.1, salary * 1.05) AS new_salary FROM t_employee; SELECT ename, salary, birthday, ROUND(IF(YEAR(birthday) < 1990, salary * 1.1, salary * 1.05),1) AS new_salary FROM t_employee; # 查询员工编号和性别，并生成一个type列，其内容根据性别显示男员工or女员工 SELECT eid, ename, gender, IF(gender = '男', '男员工', '女员工' ) AS TYPE FROM t_employee; # 查询员工的姓名和工资以及奖金数额度（奖金 = salary * commission_pct） SELECT ename, salary, salary * IFNULL(commission_pct, 0) AS 金数额度 FROM t_employee; # 查看姓名，性别，以及补助金额（补助金额按照性别的基准值*commission_pct），男性的基准值为2000，女性为3000 # 使用case when SELECT ename, gender, commission_pct, CASE WHEN gender = "女" THEN 2000 * IFNULL(commission_pct, 0) WHEN gender = "男" THEN 3000 * IFNULL(commission_pct, 0) ELSE 0 END AS 补助金额1 FROM t_employee; # 使用case 表达式|列名 when value then result SELECT ename, gender, commission_pct, CASE gender WHEN "女" THEN 2000 * IFNULL(commission_pct, 0) WHEN "男" THEN 3000 * IFNULL(commission_pct, 0) ELSE 0 END AS 补助金额2 FROM t_employee; 

多行函数

聚合函数

对一群数据进行集中处理（求和，最小/大值，平均值）

注：聚合函数不能嵌套

示例：

# 求平均工资，最大最小值，总工资 SELECT AVG(salary), MIN(salary), MAX(salary), SUM(salary) FROM t_employee; # 求最大年龄和最小年龄 SELECT MIN(birthday) AS 最大生日, MAX(birthday) AS 最小生日 FROM t_employee; # 求员工数量和有奖金的员工数量 SELECT COUNT(*), COUNT(1),COUNT(commission_pct) FROM t_employee;