SQL 入门 11：日期时间格式化、IF、CASE的使用

艺帆风顺 发布于 2025-04-18 33 次阅读

USE sql_store;

日期：2025 年 4 月 15 日。以下为 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, ...)：返回首个非空值。

  • 条件逻辑

    • 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;
    • 根据积分分级：

    总结

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