53 Calculating Dates and Times

1. DATE_ADD 增加时间（年、月、日）（数字正增负减）

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)
# 返回一天以后时间

2. DATE_SUB (数字正减负增)

SELECT DATE_SUB(NOW(), INTERVAL -2 YEAR)
# 返回两年后时间

3. DATEDIFF 时间差 return DAYS

SELECT DATEDIFF('2020-02-02', NOW())
# 前者减后者的天数 
# RETURN -30
# 那是哪一天呢？

• 只会按天数计算，即便设定了具体时间到分。
  2020-03-03 14:22
  依旧返回DAY

4. TIME_TO_SEC 从当日0时0分开始过了多少s

SELECT TIME_TO_SEC(NOW())
# 返回'53569'
# 现在几点呢？

SELECT TIME_TO_SEC('13:05') - TIME_TO_SEC('13:00')
# 返回300

54 The IFNULL and COALESCE Functions

• 通过IFNULL 将grid中的NULL值替代成指定内容

SELECT 
    order_id,
    IFNULL(shipper_id, 'NOT assigned')
FROM orders
# 将NULL 替换为'NOT assigned'

• 通过COALESCE 将返回一系列column 或value中第一个不为NULL的值

SELECT 
    order_id,
    COALESCE(shipper_id, comments, 'NOT assigned') AS shipper
FROM orders
# 将NULL 替换为comments中的value，若依然为NULL，返回'NOT assigned'

---

• 顾客名（姓+名），电话（NULL替换为Unknown）
• IFNULL

SELECT CONCAT(first_name, ' ', last_name) AS customer,
    IFNULL(phone, 'Unknown') AS phone
FROM customers

• COALESCE

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

55 The IF Function

• IF 当然就系 IF 啦
  IF(expression, value1, value2)
  满足条件，返回1，不满足，返回2

SELECT 
    order_id,
    order_date,
    IF(
        YEAR(order_date) = YEAR(NOW()),
        'Active',
        'Archived') AS category
FROM orders
# 若order年份等于现在年份，返回Active，否则返回Archived

• exercise

SELECT  
    product_id,
    name,
    COUNT(*) AS orders,
    IF (
        COUNT(order_id) > 1,
        'Many times',
        'Once') AS frequency
FROM products
LEFT JOIN order_items 
    USING (product_id)
GROUP BY product_id, name
# 需要两个column来确定一个组（非primary key col）
# 选择产品，名称，订单次数，频率

56 The CASE Operator

• 多种情况（cases）的条件创建中使用。IF表达中只能用一个expression
• WHEN expression1.. THEN value1 WHEN expression2.. THEN value2 ELSE value3

SELECT 
    order_id,
    order_date,
    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 'Future'
    END AS category
FROM orders

• 根据用户积分分类

SELECT 
    CONCAT(first_name, ' ', last_name) AS customers,
    points,
    CASE 
        WHEN points > 3000 THEN 'Gold'
        WHEN points >= 2000 THEN 'Silver'
        ELSE 'Bronze'
    END AS category
FROM customers
# 名称，积分，类别