数据库的函数
- 单行函数
- 1.数据函数
- 2.字符串函数
- 3.时间函数
- 4.流程函数
- 多行函数
- 聚合函数
阅读指南：
本文章讲述了对于数据库的单行和多行函数，如果读者感兴趣，后续我们会更新高级的操作在我们的对于数据库教程的合集中，大家可以来很我们一起学习讨论
合集链接：
数据库详细基础教程
单行函数
含数据函数，字符串函数，时间函数，流程函数
1.数据函数
| 函数名 | 用法 |
|---|---|
| ABS(x) | 返回x的绝对值 |
| SIGN(X) | 返回x的符号。正数返回1，负数返回-1，0返回0 |
| PI() | 返回圆周率的值 |
| CEIL(X) , CEILING(X) | 返回大于或等于某个值的最小整数（取大函数） |
| FLOOR(x) | 返回小于或等于某个值的最大正数（取小函数） |
| LEAST(e1,e2,e3……) | 返回列表中的最小值 |
| GREATEST(e1,e2,e3……) | 返回列表中的最大值 |
| MOD(x, y) | 返回X除以Y后的余数 |
| RAND() | 返回0~1的随机数 |
| RAND(x) | 返回0~1的随机数，其中x用作种子值，相同的x会产生相同的随机数 |
| ROUND(X) | 返回一个对x进行四舍五入的值 |
| ROUND(x, y) | 返回应该对x进行四舍五入的值，同时保留到小数点后y位 |
| TRUNCATE(x, y) | 返回数字x阶段为y位小数的结果 |
| SQRT(x) | 返回x的平方根，当x的值为负数时，返回NULL |
示例：
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.字符串函数
| 函数名 | 用法 |
|---|---|
| CHAE_LENGTH(s) | 返回字符串s的字符数，作用于CHARACTER_LENGTH(s)相同 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,s3……) | 连接s1,s2……,Sn作为一个字符串 |
| INSERT(str, idx, len, replaceStr) | 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replaceStr |
| REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s)或UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s)或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(str, n) | 返回字符串str最左边的n个字符 |
| RIGHT(str, n) | 返回字符串str最右边的n个字符 |
| TRIM(s) | 去掉字符串s的开始与结尾的空格 |
| SUBSTR(s, index, len) | 返回从字符串s的index位置去len个字符 |
| FIND_IN_SET(s1, s2) | 返回字符串s1在字符串s2中出现的位置，其中s2字符串是一个一逗号分割的字符串 |
| REVERSE(s) | 返回s反转后的字符串 |
| NULLIF(value1, value2) | 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
示例：
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(‘非标注时间字符串’,‘非标注时间字符串对应的格式’)
关于时间格式化的占位符：
| 格式 | 描述 | 格式 | 描述 |
| %a | 缩写星期名 | %p | AM 或 PM |
| %b | 缩写月名 | %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %c | 月，数值 | %S | 秒(00-59) |
| %D | 带有英文前缀的月中的天 | %s | 秒(00-59) |
| %d | 月的天，数值(00-31) | %T | 时间, 24-小时 (hh:mm:ss) |
| %e | 月的天，数值(0-31) | %U | 周 (00-53) 星期日是一周的第一天 |
| %f | 微秒 | %u | 周 (00-53) 星期一是一周的第一天 |
| %H | 小时 (00-23) | %V | 周 (01-53) 星期日是一周的第一天，与 %X 使用 |
| %h | 小时 (01-12) | %v | 周 (01-53) 星期一是一周的第一天，与 %x 使用 |
| %I | 小时 (01-12) | %W | 星期名 |
| %i | 分钟，数值(00-59) | %w | 周的天 （0=星期日, 6=星期六） |
| %j | 年的天 (001-366) | %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %k | 小时 (0-23) | %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %l | 小时 (1-12) | %Y | 年，4 位 |
| %M | 月名 | %y | 年，2 位 |
| %m | 月，数值(00-12) |
示例：
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;
多行函数
聚合函数
对一群数据进行集中处理（求和，最小/大值，平均值）
| 函数名 | 用法 |
|---|---|
| AVG(列名) | 计算某一列的平均值（数值类型） |
| SUM(列名) | 计算某一列的和（数值类型） |
| MIN(列名) | 计算某一列的最小值（任意类型） |
| MAX(列名) | 计算某一列的最大值（任意类型） |
| COUNT(列名 / * / 1) | 计算某一列或者行的出现次数；其中1是代表行，1代表第一行；* 表示所有列（任意类型） |
注：聚合函数不能嵌套
示例：
# 求平均工资，最大最小值，总工资
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;
