MySQL常用函数完全指南：提升你的数据处理效率

-- 字符串连接
SELECT CONCAT('Hello', ' ', 'World'); -- Hello World
SELECT CONCAT_WS('-', '2023', '12', '01'); -- 2023-12-01 (使用分隔符连接)

-- 大小写转换
SELECT UPPER('mysql'); -- MYSQL
SELECT LOWER('MySQL'); -- mysql

-- 去除空格
SELECT TRIM('  hello  '); -- 'hello'
SELECT LTRIM('  hello'); -- 'hello'
SELECT RTRIM('hello  '); -- 'hello'

-- 获取字符串长度
SELECT LENGTH('MySQL'); -- 5 (字节数)
SELECT CHAR_LENGTH('数据库'); -- 3 (字符数)

-- 字符串截取
SELECT SUBSTRING('MySQL Database', 1, 5); -- MySQL
SELECT LEFT('MySQL', 2); -- My
SELECT RIGHT('MySQL', 3); -- SQL

-- 查找位置
SELECT LOCATE('SQL', 'MySQL SQL'); -- 3
SELECT INSTR('MySQL', 'SQL'); -- 3

-- 替换字符串
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- Hello MySQL

-- 重复字符串
SELECT REPEAT('Ha', 3); -- HaHaHa

-- 反转字符串
SELECT REVERSE('ABCDE'); -- EDCBA

-- 格式化字符串
SELECT LPAD('5', 3, '0'); -- 005
SELECT RPAD('Name', 10, '*'); -- Name******

-- 四舍五入
SELECT ROUND(123.4567, 2); -- 123.46
SELECT ROUND(123.4567); -- 123

-- 向上/向下取整
SELECT CEIL(123.1); -- 124
SELECT FLOOR(123.9); -- 123

-- 绝对值
SELECT ABS(-123); -- 123

-- 取余数
SELECT MOD(10, 3); -- 1

-- 幂运算
SELECT POWER(2, 3); -- 8
SELECT SQRT(16); -- 4

-- 格式化数字
SELECT FORMAT(1234567.89, 2); -- 1,234,567.89

-- 截断小数
SELECT TRUNCATE(123.4567, 2); -- 123.45

-- 符号判断
SELECT SIGN(-10); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(10); -- 1

-- 随机数
SELECT RAND(); -- 0.0到1.0之间的随机数
SELECT RAND() * 100; -- 0到100之间的随机数

-- 获取当前日期时间
SELECT NOW(); -- 2023-12-01 15:30:45
SELECT CURDATE(); -- 2023-12-01
SELECT CURTIME(); -- 15:30:45

-- 系统时间戳
SELECT CURRENT_TIMESTAMP();
SELECT UNIX_TIMESTAMP(); -- 秒级时间戳
SELECT UNIX_TIMESTAMP(NOW()) * 1000; -- 毫秒级时间戳

-- 提取日期部分
SELECT DATE('2023-12-01 15:30:45'); -- 2023-12-01
SELECT TIME('2023-12-01 15:30:45'); -- 15:30:45
SELECT YEAR('2023-12-01'); -- 2023
SELECT MONTH('2023-12-01'); -- 12
SELECT DAY('2023-12-01'); -- 1

-- 日期加减
SELECT DATE_ADD('2023-12-01', INTERVAL 7 DAY); -- 2023-12-08
SELECT DATE_SUB('2023-12-01', INTERVAL 1 MONTH); -- 2023-11-01

-- 日期差值
SELECT DATEDIFF('2023-12-31', '2023-12-01'); -- 30
SELECT TIMEDIFF('15:30:45', '09:00:00'); -- 06:30:45

-- 格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2023-12-01 15:30:45
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Friday, December 01, 2023

-- 常用格式符号：
-- %Y 四位年份，%y 两位年份
-- %m 月份(01-12)，%c 月份(1-12)
-- %d 日期(01-31)，%e 日期(1-31)
-- %H 24小时制，%h 12小时制
-- %i 分钟，%s 秒

-- 基本聚合函数
SELECT 
    COUNT(*) AS total_rows, -- 行数统计
    COUNT(DISTINCT user_id) AS unique_users, -- 去重计数
    SUM(amount) AS total_amount, -- 求和
    AVG(amount) AS avg_amount, -- 平均值
    MIN(amount) AS min_amount, -- 最小值
    MAX(amount) AS max_amount, -- 最大值
    GROUP_CONCAT(product_name) AS all_products -- 连接字符串
FROM orders
GROUP BY category_id;

-- 条件聚合
SELECT 
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_amount,
    SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending_amount
FROM orders;

-- 统计示例：月度销售报告
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value,
    MAX(total_amount) AS max_order
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month DESC;

-- IF函数
SELECT 
    product_name,
    price,
    IF(price > 100, '高价', IF(price > 50, '中等', '低价')) AS price_level
FROM products;

-- CASE WHEN表达式
SELECT 
    user_id,
    order_amount,
    CASE 
        WHEN order_amount >= 1000 THEN 'VIP客户'
        WHEN order_amount >= 500 THEN '重要客户'
        WHEN order_amount >= 100 THEN '普通客户'
        ELSE '新客户'
    END AS customer_level
FROM orders;

-- NULL值处理
SELECT 
    COALESCE(NULL, NULL, 'default_value'); -- 返回第一个非NULL值
SELECT 
    IFNULL(NULL, 'replacement'); -- 如果为NULL则替换
SELECT 
    NULLIF(expr1, expr2); -- 两值相等返回NULL

-- 显式类型转换
SELECT CAST('123' AS UNSIGNED); -- 字符串转无符号整数
SELECT CONVERT('2023-12-01', DATE); -- 字符串转日期

-- 隐式转换示例
SELECT '100' + 200; -- 300 (字符串自动转数字)
SELECT CONCAT('ID:', 123); -- ID:123 (数字自动转字符串)

-- 数据类型检查
SELECT 
    column_name,
    CASE 
        WHEN ISNULL(column_name) THEN '为空'
        WHEN column_name = '' THEN '空字符串'
        ELSE '有值'
    END AS status
FROM table;

-- 排名函数
SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_dept,
    RANK() OVER (ORDER BY salary DESC) AS overall_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 累计计算
SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date) AS cumulative_sales,
    AVG(daily_sales) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_avg
FROM sales;

-- JSON处理
SELECT 
    JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John"
SELECT 
    JSON_SET('{"name": "John"}', '$.age', 30); -- 添加/修改属性
SELECT 
    JSON_ARRAY(1, 2, 3); -- [1, 2, 3]
SELECT 
    JSON_OBJECT('name', 'John', 'age', 30); -- {"name": "John", "age": 30}

-- 不推荐（索引失效）
SELECT * FROM orders WHERE DATE(order_date) = '2023-12-01';

-- 推荐（可使用索引）
SELECT * FROM orders WHERE order_date >= '2023-12-01' AND order_date < '2023-12-02';

-- 分析用户活跃度
SELECT 
    user_id,
    DATE(login_time) AS login_date,
    COUNT(*) AS login_count,
    TIMESTAMPDIFF(MINUTE, MIN(login_time), MAX(login_time)) AS session_duration,
    GROUP_CONCAT(DISTINCT DATE_FORMAT(login_time, '%H:00')) AS active_hours
FROM user_logs
WHERE login_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY user_id, DATE(login_time)
HAVING login_count >= 3
ORDER BY login_date DESC, login_count DESC;

-- 生成销售报表
SELECT 
    p.category,
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    COUNT(DISTINCT o.customer_id) AS customer_count,
    COUNT(o.order_id) AS order_count,
    SUM(o.quantity) AS total_quantity,
    SUM(o.amount) AS total_amount,
    ROUND(AVG(o.amount), 2) AS avg_amount,
    SUM(CASE WHEN o.payment_status = 'paid' THEN o.amount ELSE 0 END) AS paid_amount
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY p.category, DATE_FORMAT(o.order_date, '%Y-%m')
WITH ROLLUP; -- 添加小计行