常用字符串函数与日期函数

-- CONCAT：拼接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World

-- CONCAT_WS：指定分隔符拼接
SELECT CONCAT_WS('-', '2024', '11', '21');  -- 2024-11-21

-- 实战：拼接姓名
SELECT CONCAT(last_name, first_name) AS full_name FROM users;

-- 处理NULL
SELECT CONCAT('Hello', NULL);  -- NULL
SELECT CONCAT_WS(',', 'A', NULL, 'C');  -- A,C（忽略NULL）

-- SUBSTRING(str, pos, len)
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello（从位置1开始，长度5）
SELECT SUBSTRING('Hello World', 7);     -- World（从位置7到结尾）
SELECT SUBSTRING('Hello World', -5);    -- World（从右边第5个）

-- 实战：提取手机号后4位
SELECT SUBSTRING(phone, -4) FROM users;

-- LENGTH：字节长度
SELECT LENGTH('Hello');      -- 5
SELECT LENGTH('你好');       -- 6（UTF-8，每个汉字3字节）

-- CHAR_LENGTH：字符长度
SELECT CHAR_LENGTH('Hello'); -- 5
SELECT CHAR_LENGTH('你好');  -- 2

-- 实战：过滤长度
SELECT * FROM products WHERE CHAR_LENGTH(name) > 10;

SELECT UPPER('hello');  -- HELLO
SELECT LOWER('WORLD');  -- world

-- 实战：不区分大小写查询
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

SELECT TRIM('  Hello  ');          -- 'Hello'
SELECT LTRIM('  Hello  ');         -- 'Hello  '
SELECT RTRIM('  Hello  ');         -- '  Hello'
SELECT TRIM('x' FROM 'xxxHelloxxx');  -- 'Hello'

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

-- 实战：隐藏手机号中间4位
SELECT CONCAT(
    SUBSTRING(phone, 1, 3),
    '****',
    SUBSTRING(phone, -4)
) AS masked_phone
FROM users;

SELECT INSTR('Hello World', 'World');  -- 7
SELECT LOCATE('o', 'Hello World');     -- 5
SELECT LOCATE('o', 'Hello World', 6);  -- 8（从第6个位置开始找）

SELECT LEFT('Hello World', 5);   -- Hello
SELECT RIGHT('Hello World', 5);  -- World

SELECT NOW();       -- 2024-11-21 19:00:00（当前日期时间）
SELECT CURDATE();   -- 2024-11-21（当前日期）
SELECT CURTIME();   -- 19:00:00（当前时间）
SELECT SYSDATE();   -- 2024-11-21 19:00:00（系统时间）

-- DATE_FORMAT(date, format)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');              -- 2024-11-21
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');           -- 2024年11月21日
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');              -- 19:00:00
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');     -- 2024-11-21 19:00:00
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');         -- Thursday, November 21, 2024

SELECT STR_TO_DATE('2024-11-21', '%Y-%m-%d');                -- 2024-11-21
SELECT STR_TO_DATE('21/11/2024', '%d/%m/%Y');                -- 2024-11-21
SELECT STR_TO_DATE('2024年11月21日', '%Y年%m月%d日');        -- 2024-11-21

-- DATE_ADD(date, INTERVAL expr unit)
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);      -- 明天
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);    -- 下个月
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);     -- 明年
SELECT DATE_ADD(NOW(), INTERVAL -7 DAY);     -- 7天前

-- DATE_SUB
SELECT DATE_SUB(NOW(), INTERVAL 1 WEEK);     -- 1周前
SELECT DATE_SUB(NOW(), INTERVAL 30 DAY);     -- 30天前

-- 实战：查询最近7天的订单
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- DATEDIFF(date1, date2)：返回天数差
SELECT DATEDIFF('2024-11-21', '2024-11-01');   -- 20
SELECT DATEDIFF(NOW(), '2024-01-01');          -- 距离今年1月1日的天数

-- 实战：计算会员天数
SELECT name,
       DATEDIFF(NOW(), register_date) AS member_days
FROM users;

-- TIMESTAMPDIFF(unit, datetime1, datetime2)
SELECT TIMESTAMPDIFF(SECOND, '2024-11-21 10:00:00', '2024-11-21 11:00:00');  -- 3600
SELECT TIMESTAMPDIFF(MINUTE, '2024-11-21 10:00:00', '2024-11-21 11:00:00');  -- 60
SELECT TIMESTAMPDIFF(HOUR, '2024-11-21 10:00:00', '2024-11-21 11:00:00');    -- 1
SELECT TIMESTAMPDIFF(DAY, '2024-11-01', '2024-11-21');                       -- 20
SELECT TIMESTAMPDIFF(MONTH, '2024-01-01', '2024-11-21');                     -- 10
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2024-11-21');                      -- 4

SELECT EXTRACT(YEAR FROM NOW());    -- 2024
SELECT EXTRACT(MONTH FROM NOW());   -- 11
SELECT EXTRACT(DAY FROM NOW());     -- 21
SELECT EXTRACT(HOUR FROM NOW());    -- 19

-- 或使用专用函数
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());

SELECT DAYOFWEEK(NOW());   -- 1-7（周日=1）
SELECT WEEKDAY(NOW());     -- 0-6（周一=0）
SELECT DAYNAME(NOW());     -- Thursday

SELECT LAST_DAY('2024-11-21');  -- 2024-11-30
SELECT LAST_DAY(NOW());          -- 本月最后一天

SELECT CONCAT(
    'ORD',
    DATE_FORMAT(NOW(), '%Y%m%d'),
    LPAD(id, 6, '0')
) AS order_no
FROM orders;
-- 结果：ORD20241121000001

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

SELECT name,
       birthday,
       TIMESTAMPDIFF(YEAR, birthday, NOW()) AS age
FROM users;

-- 显示"3天前"、"1小时前"
SELECT
    CASE
        WHEN TIMESTAMPDIFF(MINUTE, created_at, NOW()) < 60
            THEN CONCAT(TIMESTAMPDIFF(MINUTE, created_at, NOW()), '分钟前')
        WHEN TIMESTAMPDIFF(HOUR, created_at, NOW()) < 24
            THEN CONCAT(TIMESTAMPDIFF(HOUR, created_at, NOW()), '小时前')
        WHEN TIMESTAMPDIFF(DAY, created_at, NOW()) < 30
            THEN CONCAT(TIMESTAMPDIFF(DAY, created_at, NOW()), '天前')
        ELSE DATE_FORMAT(created_at, '%Y-%m-%d')
    END AS time_display
FROM articles;