MySQL常用函数(总结)详细版

1. 字符串函数

• CONCAT(str1, str2, ...)：将多个字符串连接成一个字符串。

SELECT CONCAT('Hello', ' ', 'World');

• LENGTH(str)：返回字符串的长度（字节数）。

SELECT LENGTH('Hello');

• SUBSTRING(str, pos, len)：从字符串 str 的 pos 位置开始，截取长度为 len 的子字符串。

SELECT SUBSTRING('Hello World', 7, 5); -- 返回 'World'

• REPLACE(str, from_str, to_str)：将字符串中的 from_str 替换为 to_str。

SELECT REPLACE('Hello World', 'World', 'MySQL');

• UPPER(str) / LOWER(str)：将字符串转换为大写或小写。

c

SELECT UPPER('hello'); -- 返回 'HELLO'

• INSTR(str, substr)：返回子字符串 substr 在字符串 str 中首次出现的位置（从1开始），如果没有找到则返回0。

SELECT INSTR('Hello World', 'World'); -- 返回7

• LPAD(str, len, padstr) / RPAD(str, len, padstr)：在字符串左侧或右侧用 padstr 填充，使字符串达到指定长度 len。

SELECT LPAD('123', 5, '0'); -- 返回 '00123'

• TRIM([remstr] FROM str)：移除字符串 str 左侧或右侧的指定字符 remstr（默认为空格）。

SELECT TRIM('  Hello  '); -- 返回 'Hello'

• REPEAT(str, count)：重复字符串 str，count 次。

SELECT REPEAT('abc', 3); -- 返回 'abcabcabc'

• REVERSE(str)：返回字符串 str 的逆序。

SELECT REVERSE('abc'); -- 返回 'cba'

2. 日期和时间函数

• NOW()：返回当前日期和时间。

SELECT NOW();-- '2024-08-05 17:42:56'

• CURDATE()：返回当前日期，不带时间部分。

SELECT CURDATE();-- '2024-08-05'

• DATE_FORMAT(date, format)：按照指定的格式返回日期值。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');-- '2024-08-05 17:45:07'

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');-- '2024-08-05'

• DATE_ADD(date, INTERVAL expr unit)：在指定日期上添加时间间隔。

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 当前日期加7天 '2024-08-12 17:46:29'

• DATEDIFF(date1, date2)：返回两个日期之间的天数差。

SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- 返回364

• YEAR(date) / MONTH(date) / DAY(date)：分别返回日期的年、月、日部分。

SELECT YEAR('2024-09-05'); -- 返回 2024
SELECT MONTH('2024-09-05'); -- 返回 9
SELECT DAY('2024-09-05'); -- 返回 5

• DAYNAME(date) / MONTHNAME(date)：返回日期对应的星期几或月份名称。

SELECT DAYNAME('2024-09-05'); -- 返回 'Thursday'
SELECT MONTHNAME('2024-09-05'); -- 返回 'September'

• WEEK(date) / WEEKDAY(date)：返回日期属于一年中的第几周（从0开始），WEEKDAY 返回该日期是星期几（0为星期一，6为星期日）。

SELECT WEEK('2024-09-05'); -- 返回 36（表示第36周）
SELECT WEEKDAY('2024-09-05'); -- 返回 3（表示星期四）

• TIME(date)：从日期或时间值中提取时间部分。

SELECT TIME('2024-09-05 12:30:45'); -- 返回 '12:30:45'

• TIMESTAMPDIFF(unit, datetime1, datetime2)：返回两个日期或时间的差值，单位可以是 SECOND, MINUTE, HOUR, DAY, MONTH, YEAR 等。

SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-12-31'); -- 返回 364

• STR_TO_DATE(str, format)：根据给定的格式将字符串转换为日期。

SELECT STR_TO_DATE('01-09-2024', '%d-%m-%Y'); -- 返回 '2024-09-01'

3. 数值函数

• ABS(x)：返回数值 x 的绝对值。

SELECT ABS(-5); -- 返回 5

• ROUND(x, d)：将数值 x 四舍五入到 d 位小数。

SELECT ROUND(123.4567, 2); -- 返回 123.46

• FLOOR(x) / CEIL(x)：返回小于或等于 x 的最大整数（向下取整）或大于或等于 x 的最小整数（向上取整）。

SELECT FLOOR(2.9); -- 返回 2
SELECT CEIL(2.1);  -- 返回 3

• MOD(x, y)：返回 x 除以 y 的余数。

SELECT MOD(10, 3); -- 返回 1

• POWER(x, y)：返回 x 的 y 次幂。

SELECT POWER(2, 3); -- 返回 8

• RAND()：返回一个 0 到 1 之间的随机浮点数。

SELECT RAND(); -- 可能返回例如 0.548973

• SIGN(x)：返回数值 x 的符号，1 表示正数，-1 表示负数，0 表示零。

SELECT SIGN(-10); -- 返回 -1

• SQRT(x)：返回数值 x 的平方根。

SELECT SQRT(16); -- 返回 4

• EXP(x)：返回数值 x 的自然指数（e^x）。

SELECT EXP(1); -- 返回 2.718281828

• LOG(x) / LOG10(x)：返回数值 x 的自然对数（以e为底）或以10为底的对数。

SELECT LOG(2.71828); -- 返回约 1
SELECT LOG10(1000); -- 返回 3

4. 聚合函数

• COUNT(expression)：返回某列中的记录数。

SELECT COUNT(*) FROM orders;

• SUM(expression)：返回某列中数值的总和。

SELECT SUM(order_amount) FROM orders;

• AVG(expression)：返回某列中数值的平均值。

SELECT AVG(salary) FROM employees;

• MAX(expression) / MIN(expression)：返回某列的最大值或最小值。

SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

5. 控制流函数

• IF(condition, true_value, false_value)：如果 condition 为真，返回 true_value，否则返回 false_value。

SELECT IF(1 > 0, 'Yes', 'No'); -- 返回 'Yes'

• CASE：用于条件判断，类似于多路选择。

SELECT 
  CASE 
    WHEN salary > 10000 THEN 'High'
    WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_range
FROM employees;

• COALESCE : 用于返回第一个非空（非 NULL）的值。它可以用于处理可能包含 NULL 值的列，确保在结果中不出现 NULL。

COALESCE(value1, value2, ..., valueN)
-- 1. 基本用法
SELECT COALESCE(NULL, NULL, 'First Non-NULL', 'Another Value') 
AS Result;-- 'First Non-NULL'

-- 2.处理列值
SELECT employee_id, COALESCE(phone_number, 'No Phone Number') AS phone
FROM employees;
-- 如果 phone_number 列的值是 NULL，则结果将是 'No Phone Number'。

-- 3.与其他函数配合使用
SELECT employee_id, COALESCE(start_date, CURRENT_DATE) AS start
FROM employees;
-- 如果 start_date 列的值是 NULL，则使用当前日期作为默认值。

6. 加密与解密函数

• MD5(str)：返回字符串 str 的 MD5 哈希值（128位）。

SELECT MD5('password');

• SHA1(str)：返回字符串 str 的 SHA-1 哈希值（160位）。

SELECT SHA1('password');

7. 类型转换函数

• CAST(expression AS type)：将某个值转换为指定的类型。

SELECT CAST('123' AS UNSIGNED); -- 将字符串'123'转换为无符号整数

• CONVERT(expression, type)：类似于 CAST，用于类型转换。

SELECT CONVERT('2024-01-01', DATE); -- 将字符串转换为日期类型

8. JSON函数

• JSON_EXTRACT(json_doc, path)：从 JSON 文档中提取指定路径的值。

SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name');

• JSON_OBJECT(key, value, ...)：创建一个 JSON 对象。

SELECT JSON_OBJECT('name', 'Alice', 'age', 30);

• JSON_ARRAY()：创建一个 JSON 数组。

SELECT JSON_ARRAY('apple', 'banana', 'cherry');

• JSON_CONTAINS(json_doc, val[, path])：检查 JSON 文档中是否包含指定的值。

SELECT JSON_CONTAINS('["apple", "banana"]', '"apple"');

• JSON_UNQUOTE(json_val)：移除 JSON 文本中的引号。

SELECT JSON_UNQUOTE('"Hello"'); -- 返回 'Hello'