SQL 中,函数是一段可重用的代码块,可以接收输入参数并返回一个结果值。SQL 函数能够实现复杂的计算和逻辑控制,可以在查询语句中调用它们,从而简化查询和提高代码的可维护性。
一、常用字符串函数
1、字符串拼接:CONCAT 函数:用于将多个字符串合并成一个字符串。
示例:将字符串 "Hello" 和 "world" 合并成一个新字符串
SELECT CONCAT('Hello', ' ', 'world'); -- 返回值为 "Hello world"
任何字段与null结合后的结果都是null !!!
2、字符串大小写:LOWER/UPPER 函数:用于将字符串转换成小写/大写形式。
示例:将字符串 "Hello World" 转换成全小写形式
SELECT LOWER('Hello World'); -- 返回值为 "hello world"
3、字符串长度:LENGTH 函数:用于获取字符串的长度。
示例:获取字符串 "Hello World" 的长度
SELECT LENGTH('Hello World'); -- 返回值为 11
4、字符串截取:SUBSTRING 函数:用于从给定字符串中截取一个子串。
示例:将字符串 "Hello world" 的第2个字母截取出来。
SELECT SUBSTRING('Hello world', 2, 1); -- 返回值为 "e"
SUBSTRING 函数的第一个参数为源字符串,第二个参数为开始截取的位置,第三个参数为截取的长度。需要注意的是,SUBSTRING 函数的索引从 1 开始而不是从 0 开始。
如果省略第三个参数,则将从指定位置开始直到字符串的末尾进行截取。
5、字符串替换:使用 REPLACE 函数可以对字符串进行替换。
REPLACE(str, old_substring, new_substring)
其中,str 为要进行替换操作的源字符串;old_substring 是要被替换的子串;new_substring 是替换后的新子串。
以下是一个示例,用来将字符串 "Hello world" 中的 "world" 替换成 "SQL"。
SELECT REPLACE('Hello world', 'world', 'SQL'); -- 返回值为 "Hello SQL"
需要注意的是,如果源字符串中有多处符合要求的子串,REPLACE 函数会将它们全部替换掉。
6、重复字符串:REPEAT
以下是一个示例,用来将字符串 "Hello" 重复三次。
SELECT REPEAT('Hello', 3); -- 返回值为 "HelloHelloHello"
7、字符串反转:REVERSE
以下是一个示例,用来反转字符串 "Hello World"。
SELECT REVERSE('Hello World'); -- 返回值为 "dlroW olleH"
8、找出某一字符串第一次出现的位置:LOCATE
以下是一个示例,用来查找字符串 "World" 第一次出现在字符串 "Hello World" 的位置
SELECT LOCATE('World', 'Hello World'); -- 返回值为 7
需要注意的是,LOCATE函数的第一个参数为要查找的字符串,第二个参数为源字符串。
二、常用数值函数
1、ABS 函数:返回给定数值的绝对值。
SELECT ABS(-7); -- 返回值为 7
2、CEILING 函数:返回不小于给定数值的最小整数,向上取整。
SELECT CEILING(3.14159); -- 返回值为 4
3、FLOOR 函数:返回不大于给定数值的最大整数,向下取整。
SELECT FLOOR(3.14159); -- 返回值为 3
4、ROUND 函数:将给定数值四舍五入到指定的小数位数。
SELECT ROUND(3.14159, 2); -- 返回值为 3.14
5、MOD 函数:返回给定两个数值相除的余数。
SELECT MOD(10, 3); -- 返回值为 1
三、常用日期函数
1、CURRENT_TIMESTAMP():返回当前日期和时间。
SELECT CURRENT_TIMESTAMP();
2、DATE_ADD(datepart, number, date) :在给定日期上加上指定数量的时间单位。
SELECT DATEADD(day, 7, '2023-04-26');
-- 将给定日期加上 7 天,结果为 '2023-05-03'
3、DATE_DIFF(startdate, enddate):计算两个日期之间的时间差。
SELECT DATEDIFF('2023-04-20', '2023-04-26');
-- 计算两个日期之间的天数差,结果为 6
4、DATEPART(datepart, date):返回给定日期的指定部分(如年、月、日、小时、分钟等)。
SELECT DATEPART(year, '2023-04-26');
-- 返回给定日期的年份,结果为 2023
5、DAY(date):返回给定日期的日部分。
SELECT DAY('2023-04-26');
-- 返回给定日期的日部分,结果为 26
6、MONTH(date):返回给定日期的月部分。
SELECT MONTH('2023-04-26');
-- 返回给定日期的月份,结果为 4
7、YEAR(date):返回给定日期的年部分。
SELECT YEAR('2023-04-26');
-- 返回给定日期的年份,结果为 2023
8、TIMESTAMPDIFF(datepart, startdate, enddate):
该函数的第一个参数指定要计算的时间间隔单位(例如 day、hour、minute、second 等),第二个参数和第三个参数分别为两个时间戳。函数返回值为两个时间戳之间相差的完整时间间隔数量。
SELECT TIMESTAMPDIFF(day, '2022-05-01 08:00:00', '2022-05-07 18:30:00');
-- 输出结果为 6,表示 2022 年 5 月 1 日早上 8 点到 2022 年 5 月 7 日晚上 6 点半之间相差 6 天
这些函数可以组合使用,以实现特定的日期计算和格式化需求。
例如,我们可以使用 DATEADD、DAY 和 MONTH 函数结合使用,实现在给定日期上加上一个月并减去一天的操作:
SELECT DATEADD(day, -1, DATEADD(month, 1, '2023-04-26'));
-- 将给定日期加上一个月,并减去一天,结果为 '2023-05-25'
四、常用流程函数
1、IF 函数:根据指定的条件执行不同的操作。
IF(condition, true_value, false_value)
其中,condition 表示需要进行判断的表达式或值,true_value 表示在 condition 为真时要返回的结果,false_value 表示在 condition 为假时要返回的结果。
SELECT
IF(5 > 3, '5 is greater than 3', '5 is less than or equal to 3');
-- 返回结果:'5 is greater than 3'
2、IFNULL 函数:用于判断某个表达式是否为 NULL,如果是,则返回指定的值,否则返回该表达式本身。
IFNULL(value1,value2)
如果value1不为空,返回value1,否则返回value2
SELECT IFNULL(NULL, 'default value');
-- 返回结果:'default value'
3、 CASE 函数:类似于 switch 语句,可以根据不同的条件选择不同的操作。
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END
其中,expression 是需要进行判断的表达式或值,value1、value2 等表示不同的条件,result1、result2 等表示对应条件的操作结果, default_result 表示在所有条件都不满足时的默认操作结果。
SELECT CASE
WHEN 5 > 10 THEN '5 is greater than 10'
WHEN 5 < 10 THEN '5 is less than 10'
ELSE '5 is equal to 10'
END;
-- 返回结果:'5 is less than 10'
4、 COALESCE 函数:用于返回参数列表中第一个非空值。
COALESCE(value1, value2, ..., default_value)
其中, value1 、 value2 等表示需要进行判断的值, default_value 表示在所有值都为空时的默认返回值。
SELECT COALESCE(NULL, NULL, 'default value');
-- 返回结果:'default value'