以下为 SQL 中日期格式化、时间计算及条件逻辑的用法解析。

学习内容

1. 日期格式化
• DATE_FORMAT(date, format)：按指定格式显示日期。
• 格式符：%y（两位年）、%M（月名）、%D（带后缀的日）等。

2. 时间格式化

• TIME_FORMAT(time, format)：按指定格式显示时间。
• 格式符：%H（24 小时）、%i（分）、%S（秒）、%p（AM/PM）。

3. 时间与日期计算

• DATE_ADD(date, INTERVAL n unit)：添加时间间隔（如天、月、年），负值等效回溯。
• DATE_SUB(date, INTERVAL n unit)：减去时间间隔。
• DATEDIFF(date1, date2)：计算两日期天数差，较早日期视为小，输出正负值。
• TIME_TO_SEC(time)：将时间转为秒数，计算间隔时可相减。

1. 空值处理
• IFNULL(expr, replacement)：若 expr 为空，返回 replacement。
• COALESCE(expr1, expr2, ...)：返回首个非空值。
2. 条件逻辑
• IF(condition, true_value, false_value)：根据条件返回不同值。
• CASE ... WHEN ... THEN ... END：多条件分支，WHEN 无需逗号，支持 ELSE。

示例代码与讲解

1. 日期格式化

SELECT DATE_FORMAT(NOW(), '%y');

• 返回两位年份，如 '25'。

SELECT DATE_FORMAT(NOW(), '%M %y');

• 返回月名和两位年份，如 'April 25'。

SELECT DATE_FORMAT(NOW(), '%M %D %y');

• 返回月名、带后缀日和年份，如 'April 15th 25'。

2. 时间格式化

SELECT TIME_FORMAT(NOW(), '%H');

• 返回 24 小时格式，如 '15'。

SELECT TIME_FORMAT(NOW(), '%H:%i %S');

• 返回小时、分、秒，如 '15:02:23'。

SELECT TIME_FORMAT(NOW(), '%H %i %S %p');

• 返回带 AM/PM，如 '03 02 23 PM'。

3. 时间与日期计算

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

• 当前时间加 1 天。

SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH);

• 加 1 月。

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

• 加 1 年。

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

• 减 1 天，等效 DATE_SUB。

SELECT DATEDIFF('2002-04-19', '2001-06-19');

• 返回 304 天（正值）。

SELECT DATEDIFF('2001-06-19', '2002-04-19');

• 返回 -304 天（负值）。

SELECT TIME_TO_SEC('9:01');

• 返回 9:01 的秒数 32460。

SELECT TIME_TO_SEC('19:01') - TIME_TO_SEC('18:09');

• 返回时间差 3120 秒。

4. 空值处理

SELECT order_id, IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders;

• shipper_id 为空时返回 'Not assigned'。

SELECT order_id, COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders;

• 返回首个非空值：shipper_id、comments 或 'Not assigned'。

5. 条件逻辑

SELECT order_id, order_date,
    IF(YEAR(order_date) = YEAR(NOW()), 'Active', 'Archived') AS Category
FROM orders;

• 当前年订单标记 'Active'，否则 'Archived'。

SELECT order_id,
    CASE
        WHEN YEAR(order_date) = YEAR(NOW()) THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last year'
        WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Archived'
        ELSE '....'
    END AS Category
FROM orders;

• 多条件分类：当前年 'Active'，去年 'Last year'，更早 'Archived'。

作业

1. 空值处理

SELECT CONCAT(first_name, ' ', last_name) AS Costomers,
    COALESCE(phone, 'Unknown') AS phone
FROM customers;

• 合并姓名，电话为空时返回 'Unknown'。

2. IF 条件

SELECT product_id, name, COUNT(order_id) AS orders,
    IF(COUNT(order_id) > 1, 'Many times', 'Once') AS Frequery
FROM products
JOIN order_items USING (product_id)
GROUP BY product_id, name;

• 统计产品订购次数，>1 次为 'Many times'，否则 'Once'。

3. CASE 条件

SELECT CONCAT(first_name, ' ', last_name) AS Coustmers, points,
    CASE
        WHEN points < 1000 THEN 'C'
        WHEN points >= 1000 AND points < 2000 THEN 'B'
        WHEN points >= 2000 THEN 'A'
    END AS Category
FROM customers
ORDER BY points DESC;

• 根据积分分级： <1000 为 'C'，1000-1999 为 'B'，≥2000 为 'A'。

总结

本次解析了日期与时间格式化（DATE_FORMAT、TIME_FORMAT）、时间计算（DATE_ADD、DATEDIFF）、空值处理（IFNULL、COALESCE）及条件逻辑（IF、CASE）。基于 sql_store 数据库。后续将探讨视图与存储过程。